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_tomatoesraw_flourraw_mozzarellaprep station · staging
stg_tomatoesstg_flourstg_mozzarellagoes straight to the margheritacomponents · intermediate
int_saucefrom stg_tomatoesint_doughfrom stg_flourthe menu · marts
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/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/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/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.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.
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
pizza_keydim_pizza- pizza_keykey
- name · size · base · is_veggie
- price_eur · on_menu_since …
The star schema
Put the fact in the middle, the dimensions around it, and the pizzeria's whole reporting fits one picture:
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:
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:
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_. 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:
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.
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.
Slowly changing dimensions: Type 1 vs Type 2, and snapshots
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.
dbt builds Type 2 for you with a snapshot. Since dbt 1.9 it's a few lines of YAML:
# snapshots/snap_ snapshots: - name: snap_menu_ items. yml menu_ relation: source(items 'raw', 'menu_ # YAML fields take ref(items') )/ 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)source( ) bare
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:
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/ -- int_fct_ order_ lines. sql (excerpt) order_ FROM {{ ref(lines: order lines joined to their order's ordered_at timestamp 'int_ ol JOIN {{ ref(order_ lines') }} 'dim_ m ON ol.item_id = m.item_id AND ol.menu_ item') }} ordered_ AND ol.at >= m. dbt_ valid_ from ordered_ at < coalesce( m. dbt_ valid_ to, '9999-12-31'::timestamp)
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.

