Course contents

The Analytics Engineer’s Field Guidedbt 1: build the modelsStep 3: Modeling

Modeling

Part 2 · dbt · step 3

Modeling: shape the warehouse on purpose

refs wire models together. Modeling decides what the models should be. Facts, dimensions, and the patterns that keep three hundred tables navigable.

· · ·

Alice can now build models and wire them with ref(). The next question is bigger than any single model: when the owner of IsmaFrost, the pizzeria whose data Alice models, asks "revenue by pizza by month" or "what do regulars order on Fridays", what tables should exist to answer it? That's modeling. The standard answer is decades old, Ralph Kimball's dimensional modeling (the star schema), and it fits dbt perfectly.

The three layers, explained as a pizzeria

Alice has seen the three folders. Here's what each one is for, using a kitchen, because once you picture a pizzeria you stop guessing where a model belongs.

Staging: the prep station

A crate of tomatoes arrives, someone washes them. A sack of flour arrives, someone sifts it. One crate in, one prepped ingredient out. Nothing gets mixed, nothing gets invented.

That's staging. One model per raw table: rename columns, fix types, trim the stray spaces. No joins, ever.

Intermediate: what you make from the preps

Now the kitchen mixes preps together. Tomatoes and basil become sauce. Flour and yeast become dough. Nobody walks in and orders a bowl of sauce, the sauce exists because several pizzas need it.

That's intermediate. The joins and the business logic that more than one final table needs, written once and reused. No dashboard ever reads it directly. It's optional: a mart may join staging models itself, like fct_orders in step 2. Add an int_ model when several marts share the logic, or when one mart's SQL gets too long to read.

Marts: the menu

The margherita is sauce + dough + mozzarella. The marinara is sauce + dough, no cheese. Named, priced, on the menu, this is what people actually order.

That's a mart. The finished table a dashboard or a colleague reads. Notice both pizzas use the same sauce and the same dough, which is the entire reason the intermediate layer exists: make it once, use it twice.

supplier crates · raw

raw_tomatoes
raw_flour
raw_mozzarella

prep station · staging

stg_tomatoes
stg_flour
stg_mozzarellagoes straight to the margherita

components · intermediate

int_saucefrom stg_tomatoes
int_doughfrom stg_flour

the menu · marts

margheritasauce + dough + cheese
marinarasauce + dough, no cheese
one crate = one prep · sauce and dough are shared by both pizzas · only the margherita takes cheese

The same three layers, in dbt terms

Drop the pizza and it's three folders under models/, in that order. Raw data comes in messy on the left and leaves business-ready on the right. Each layer has one job:

staging
clean
→
intermediate
join & shape
→
marts
serve
Layer
What it's for
staging/
One model per source table, prefixed stg_. Rename columns, fix types, cast timestamps, no joins. This is the clean, predictable version of each raw table that everything else builds on.
intermediate/
Prefixed int_. The messy middle: join staging models together, apply business logic, build the reusable pieces that more than one mart needs. Nothing here is read by BI directly, it exists to keep marts simple.
marts/
The tables the business queries, prefixed fct_ or dim_ (fct_orders, dim_customer). One subject each, joined, and aggregated only when the question needs it. This is the public surface your dashboards and other teams depend on.
Why the split matters
When a number looks wrong, the layer tells you where to look: staging for a bad cast or rename, intermediate for wrong join logic, marts for the final shape. Skip the middle and your marts turn into 300-line queries nobody can debug.
· · ·

Dimensional modeling: facts and dimensions

The two kinds of table

Marts hold two kinds of table. One counts events, the other lists things.

A fact table is a log of events. A pizza sold at 19:42, a delivery finished, a payment taken. Every event gets its own row, so the table only ever grows, thousands of rows a week. Its columns are the numbers worth adding up (amount, quantity) plus short codes saying which pizza and which customer. Tall and thin.

A dimension table is a list of things. The pizzas on the menu, the customers, the days. One row per thing, and the list barely moves: twelve pizzas, a few thousand customers. Its columns describe them, like name, size, city. Short and wide.

The quick test
Ask why a new row would appear. Because something happened? Fact. Because something new exists? Dimension.

Neither is useful alone, and the reason is in the codes. The fact table doesn't store "Margherita", it stores pizza_key = 7, because repeating the name on two million rows is waste. The 7 is usually just the menu item's own id from the raw table, copied into both tables (more on keys under Grain). dim_pizza is what turns the 7 back into a margherita, 30cm, vegetarian, on the menu since March. Join the two and you get a readable answer.

So every real question is the same move: take a number from the fact table, group it by columns from the dimensions. "Revenue by pizza by month" sums amount_eur in the fact, split by a column in dim_pizza and one in dim_date. Facts hold what you measure, dimensions hold what you slice it by.

fct_order_lines
  • order_line_idkey
  • pizza_key→ dim
  • customer_key→ dim
  • amount_eurmeasure
  • quantitymeasure
tall & thin · one row per order line
pizza_key
dim_pizza
  • pizza_keykey
  • name · size · base · is_veggie
  • price_eur · on_menu_since …
short & wide · one row per menu item
the fact counts what happened · the dimension describes which pizza, who, when

The star schema

Put the fact in the middle, the dimensions around it, and the pizzeria's whole reporting fits one picture:

fct_order_lines 1 row = 1 line on an order dim_date day, month, match night… dim_customer name, neighbourhood… dim_pizza name, size, base… dim_channel dine-in, delivery fct_order_lines 1 row = 1 line on an order dim_date day, month, match night… dim_customer name, neighbourhood… dim_pizza name, size, base… dim_channel dine-in, delivery

Why this shape wins: every question is one predictable join away. "Margherita sales on football match nights" is the fact joined to dim_pizza and dim_date. Sums stay fast, because the fact holds only numbers and keys. And the owner can read it without a map. Marts are where facts and dimensions live, named fct_* and dim_* so intent shows in the file tree.

dim_date has no raw table: you generate it, one row per day, with columns like month and weekday (step 6's dbt_utils.date_spine makes the rows).

Build each dimension once and ref() it from every fact. When fct_deliveries arrives, it joins the same dim_customer as fct_order_lines. That shared dimension is a conformed dimension. It's why "revenue by neighbourhood" and "delivery time by neighbourhood" agree on what a neighbourhood is.

Grain: what one row means

Before writing a fact model, finish this sentence: one row equals one ______. That's the grain, and you pick it before you write any SQL.

Take one real order: Marco buys two margheritas and one marinara. Three plausible grains, three different tables, all correct:

One row = one…
Marco's order becomes
Rows
pizza sold
margherita, margherita, marinara
3
line on the order
margherita ×2, marinara ×1
2
order
1 order, 3 pizzas, €27
1

Same event, same money, three answers. That's why nobody can read your fact table without being told the grain first, and why you write it in the model's description.

Why it matters: the fan-out

Take a line in fct_order_lines for one margherita, €9, and join it to a toppings table. A margherita has four toppings, so the line is duplicated four times, and its €9 gets counted four times too:

before the join, grain respected
1 margherita row€9
revenue€9 ✓
→
join toppings
after, grain broken
4 rows: tomato, basil…€9 ×4
revenue€36 ✗

Two lines of defence, and both take a minute:

  • Write the grain down, in the model's description, so the next person joins carefully.
  • Test it, with the data_tests: [unique] from step 2 on the column that identifies one row. The day a join starts duplicating, the test fails, not the dashboard.

That column is a key. If the raw data already has one id per row, like an order line id, use it. If one row is defined by several columns, you make a surrogate key: an id built by hashing those columns together. Step 6 shows the one-liner, dbt_utils.generate_surrogate_key. You don't need it to follow this lesson.

· · ·

The two shapes you'll probably use

The transaction fact

One row per event, written when the event happens and never touched again. A pizza sold, a payment taken. This is the default fact table and it covers nearly everything you'll build. Its columns are only three things:

fct_order_lines · 1 row = 1 order line
order_line_idthe key, one per row
pizza_key, customer_key, date_keywho / what / when
amount_eur, quantitythe numbers you add up
a key, some links to dimensions, some numbers · nothing else

date_key is often just the order date, which joins to dim_date. Rows only ever get appended, so you'll later build it incrementally (step 4: append if lines are never re-sent, else the default merge).

One Big Table: the flat version

The owner's tablet dashboard doesn't want four tables and three joins. It wants one wide table with everything already spelled out. That's an OBT, One Big Table: you take the star and flatten it.

the star · 4 tables
fct_order_lineskeys + numbers
dim_pizzaname, size, base
dim_customername, neighbourhood
dim_dateday, month
→
join the dimensions in
the OBT · 1 table
order_line_id1001
pizza_name, size'margherita', 30cm
customer_name, neighbourhood'Marco', 'Trastevere'
order_date, amount_eur2024-05-25, 9.00

Nothing wrong with it, as long as it comes second. Build the star, then build the OBT on top of it, as one more model that ref()s the fact and the dimensions. The star stays the source of truth, and one dbt run rebuilds the OBT.

Why not build only the OBT? Because it holds only the columns today's dashboard needs. When the owner asks "sales by base, thin or thick?", base isn't in it, and you rework the big table under pressure. In the star, dim_pizza already has base: the answer is one join away.

Where this fits the layers
Staging cleans the crates. Intermediate makes the sauce. Marts hold the facts and dimensions, grain declared, key tested. Still open: what happens to a dimension when a price changes?
· · ·

Slowly changing dimensions: Type 1 vs Type 2, and snapshots

Read this section, don't run it yet
Your trial has no menu or order-line tables. The capstone, the IsmaFrost case study that ends the guide, builds all of it.

In June the margherita goes from €9 to €10. The till (the cash register) overwrites the price in raw.menu_items. What should the menu dimension do? Here it's called dim_menu_item, as in the capstone. It's dim_pizza plus drinks and desserts.

  • Type 1: overwrite. The dimension is a plain model reading the current source. One row per dish, always today's value. Simple, and history is gone.
  • Type 2: keep history. One row per version of the dish, each with the dates it was valid. May's orders still meet the €9 margherita.
The one-question rule
Would last month's report need last month's value? Yes → Type 2, built from a snapshot. No → Type 1, a plain model. A typo fixed in a customer's name: Type 1. A price, a VAT rate, a delivery zone: Type 2.

dbt builds Type 2 for you with a snapshot. Since dbt 1.9 it's a few lines of YAML:

# snapshots/snap_menu_items.yml
snapshots:
  - name: snap_menu_items
    relation: source('raw', 'menu_items')   # YAML fields take ref()/source() bare
    config:
      schema: snapshots
      unique_key: item_id
      strategy: timestamp         # a change = a newer updated_at
      updated_at: updated_at        # setting: your column (e.g. modified_ts)

No reliable updated_at? Use strategy: check with check_cols: [price_eur] instead. Older projects write snapshots as a {% snapshot %} block in a .sql file, the only form before dbt 1.9. dbt creates the snapshots schema for you. With dbt's default naming rule (step 5), Alice's lands in USERLAND.DBT_ALICE_SNAPSHOTS.

dbt snapshot (or dbt build) compares the source with what it captured last time. It adds dbt_scd_id (one id per version), dbt_updated_at, and the two below: a changed row gets closed and a new version opens:

item_id, name
price_eur
dbt_valid_from
dbt_valid_to
7 margherita
9.00
2024-01-10
2024-06-01
7 margherita
10.00
2024-06-01
null (current)

dim_menu_item is then a plain model on top of ref('snap_menu_items'): ref() works on a snapshot as on a model. It has one row per version, so item_id isn't unique any more. Put the unique test on dbt_scd_id instead.

When the fact is built, each line picks the version valid when the order happened. The range includes dbt_valid_from and excludes dbt_valid_to, so an order placed exactly at the change matches one version, not two. The current version's dbt_valid_to is null, so coalesce swaps in a far-future date. The fact then keeps that version's key (the capstone builds one, menu_item_key):

-- models/marts/fct_order_lines.sql (excerpt)
-- int_order_lines: order lines joined to their order's ordered_at timestamp
FROM {{ ref('int_order_lines') }} ol
JOIN {{ ref('dim_menu_item') }} m
  ON  ol.item_id = m.item_id
  AND ol.ordered_at >= m.dbt_valid_from
  AND ol.ordered_at <  coalesce(m.dbt_valid_to, '9999-12-31'::timestamp)
A snapshot can't be rebuilt
History starts at the first dbt snapshot and grows only on a schedule. A price that changes twice between two runs keeps only its last value. No rebuild, not even --full-refresh, recreates what was never captured. So schedule snapshots and never drop one casually.

And always join a Type 2 dimension on the date range. Without it, every version matches and your rows multiply: the fan-out again.

Lesson quiz

  1. Marco orders two margheritas and one marinara. The fact table's grain is 'one row = one line on the order'. How many rows does this order add?

    1. One: the order, with its €27 total
    2. Three: the order plus one row per dish
    3. Two: margherita ×2 and marinara ×1
    4. Three: one row per pizza sold
  2. The margherita goes from €9 to €10 in June. May's revenue report must still show May's sales at €9. How should dim_menu_item be built?

    1. As an incremental model on raw.menu_items that appends only newly added dishes
    2. Straight from raw.menu_items, overwriting the old price on every run (Type 1)
    3. From a dbt snapshot that keeps each price version with its valid dates (Type 2)
    4. From raw.menu_items with a nightly full refresh, so the price is always current
  3. The owner's dashboard reads an OBT built on top of the star. Now he asks for sales by pizza base, thin or thick, which the OBT doesn't carry. What's the quickest correct fix?

    1. Add base from dim_pizza to the OBT model and rerun it
    2. Build a second OBT for base next to the first one
    3. Rebuild the OBT straight from raw orders, adding base
    4. Add base to the fact table, so the OBT picks it up