Dimensions, facts, snapshots

In a hand-loaded warehouse, a sequence is a perfectly reasonable surrogate key: the dimension row is inserted once, gets number 7, and keeps it forever because nothing ever drops the table. dbt does not work that way. materialized: table means drop and recreate in full, every run. So on Monday the sequence hands merchant M07 the number 7, on Tuesday the rows come back in a slightly different order and M07 gets 3 - and every fact row built before Tuesday now points at whichever merchant happens to hold 7 today. Nothing errors. The join still succeeds. The numbers are just attached to the wrong names.

Hashing the business key removes the problem rather than managing it. The key is a pure function of the input, so:

• It is identical on every run, which is what makes rebuilding the whole warehouse a safe operation instead of a coordinated migration.
• It is identical on every machine. Your dev target and production compute the same key from the same business key, so you can compare a dev fact table to a production one directly.
• It needs no state. There is no counter to protect, no gap to explain, and nothing that breaks when two threads build two models at once - which matters, because dbt runs four threads by default.
• It composes. fct_order_items keys on order_id plus line_no together, and the same macro handles it because it takes a list.

Two disciplines run through all six dimensions, and both of them are about the person writing the query rather than the person writing the model.

Flat and wide. dim_products carries merchant_name even though dim_merchants already has it. A normaliser would call that a duplicate; a dimensional modeller calls it one less join in every product question anybody ever asks - and one less join in every query an AI agent writes against your warehouse, which is a newer and increasingly load-bearing reason. Snowflaking saves storage that costs nothing and spends clarity that costs a great deal. The same instinct puts tenure_bucket in dim_users and daypart in dim_time_of_day: band it once in the model so eleven dashboards do not each write their own CASE and disagree at the boundaries.

Coalesce to the unknown member. Every dimension lookup in every fact is a LEFT JOIN followed by coalesce(dim.the_sk, '-1'). Read fct_order_items and you will see it four times. The reason is the failure mode you are choosing between:

• An inner join drops any fact row whose dimension is missing. Revenue silently disappears, the build stays green, and nobody notices until a reconciliation months later. This is the worst failure mode in the whole project, because the evidence of the problem is the thing that got deleted.
• A left join with no coalesce leaves a null key. Better - the row survives - but now every downstream aggregation has to remember to handle nulls, and half of them will not.
• A left join coalesced to '-1' keeps the row, keeps the money in the total, and makes the problem countable: select count(*) where merchant_sk = '-1'. A broken lookup becomes a number on a dashboard instead of an absence.

Then you test it. assert_no_fact_rows_lost_to_unknown_members is a singular test that fails the build if the unknown share of fct_order_items climbs above 1%. It currently reports 0.0%, and you will run that measurement in a minute.

The facts. Three of them, and the grain is declared in the first line of every file because it is the only thing about a fact table you cannot recover by reading the columns:

• fct_order_items - one row per product line on one order. 1,434 rows. The revenue fact, and where net_amount lives.
• fct_transactions - one row per payment attempt. 1,060 rows. An order appears several times here: a decline, a retry that succeeds, a refund later.
• fct_carts - one row per cart. 2,229 rows. Demand that never became revenue, which is what lets you tell "sales fell" apart from "people stopped wanting to buy".

Three grains means three tables. It does not mean three tables that can be joined together, which is session b5's fan-out guard and the reason int_payments_per_order exists. Notice also that the three day-grain marts join pre-aggregated sets rather than facts to facts.

The generated dimensions. dim_dates and dim_time_of_day have no source and no ref() at all. They are built from DuckDB's range():

• dim_dates walks range(date '2026-03-01', date '2026-10-01', interval 1 day) and produces 214 rows, deliberately wider than the 90 days of data. A calendar dimension that stops where the data stops guarantees a broken lookup the first day new data arrives, and a fact row falling to the unknown member on a date that plainly exists is a maddening bug to chase.
• dim_time_of_day walks range(0, 24) and produces 24 rows. It is separate from dim_dates on purpose: folding hours into the calendar would multiply it by 24 for no benefit, and "what are our peak hours" needs to group across all 90 days at once.

Never source a calendar from an application database. It will be incomplete, it will have somebody's fiscal calendar baked into it, and it will change without telling you. Generating it means it is correct by construction and reviewable in a diff.

snapshots/merchants_snapshot.sql is a SELECT wrapped in two Jinja tags and a config block. That is the entire artifact. The whole of type-2 maintenance is in four config values:

• unique_key='merchant_id' - the business key. This is how dbt decides that an incoming row and a stored row are the same merchant. Get this wrong and you get duplicate history instead of versioned history.
• strategy='check' - compare the current values of named columns against what is stored. The alternative is strategy: timestamp, which trusts an updated_at column from the source. Timestamp is cheaper and better when the source is trustworthy; check is what you use when it is not, which in practice is most of the time.
• check_cols=['merchant_tier', 'commission_pct', 'merchant_name'] - the columns whose change opens a new version. This is a modeling decision, not a technicality: anything not in this list is treated as type 1 and simply overwritten. Merchant country is not in the list, so a country correction rewrites history rather than versioning it - which is the right call for a data fix and the wrong call for a genuine relocation. You are choosing, whether you notice or not.
• target_schema='snapshots' - snapshots live in their own schema, away from the marts, because they are not something a dashboard should query casually.

Then dbt snapshot runs, and on every subsequent run dbt compares the incoming rows to the stored ones. A changed check_cols value means: set dbt_valid_to on the stored row to now, and insert a new row with dbt_valid_from set to now and dbt_valid_to null. It also maintains dbt_scd_id, a hash identifying the version, and dbt_updated_at. Four columns you never write.

This project deliberately ships both, and the split is worth copying:

• dim_merchants is the current-version view. Twelve rows, one per merchant, always the version in force, and it carries valid_from, valid_to and is_current so the shape is honest about being a slice of something with history. Every fact in the project joins here, with and dim_merchants.is_current on the join so the pattern still works when history arrives. The mart layer stays readable: twelve rows, no date-range logic in any analyst's query.
• merchants_snapshot is the history. It grows a row every time a tier or a rate changes and never loses one. It lives in its own schema and nobody queries it by accident.

So when do you join a fact to the snapshot instead of the dimension? The test is whether the question is as-of or as-is:

• Join the dimension for almost everything. "Revenue by merchant tier this quarter" means today's tiers, because the business asking is thinking about today's portfolio. This is the default and it should stay the default.
• Join the snapshot when the answer must match what was true at the time: a commission payout being recalculated for a closed month, an audit of what rate was in force on a date, a cohort analysis where the attribute is the cohort. The join carries a date predicate - ordered_at between dbt_valid_from and coalesce(dbt_valid_to, now()) - and returns exactly one version per fact row.

The trap worth naming: a dashboard that silently switches between the two produces two different answers to the same question and no way to tell which one you are looking at. Pick one per report, and put it in the title.

The hand-written version of this warehouse designed SCD2 properly. Its dim_merchants had valid_from, valid_to and is_current, the grain was right, and the documentation explained the pattern. What it did not have was anything that ran. Maintaining type-2 history by hand means a load that reads the current dimension, diffs it against the incoming rows, UPDATEs the expiring versions and INSERTs the new ones, in a transaction, idempotently, with a plan for the run that fails halfway. That is a genuinely difficult piece of software, and it is why hand-built warehouses so often ship the columns and never fill them. Every row stays version 1 forever and the structure becomes decoration.

dbt reduces that to a file with four config values. That is the single largest capability gap between the two versions of this warehouse, and it is worth being precise about it: everywhere else in this course dbt manages the same SQL more safely. Here it does something else.

What snapshots still will not do for you:

• They cannot recover history from before the first run. The snapshot starts the day you start it. If tiers changed last year, that is gone unless the source kept it.
• They only see what the source shows them. With strategy: check, two changes between runs look like one change. A snapshot run daily against an attribute that changes hourly records a daily approximation, and nothing warns you.
• Deletes are invisible by default. A merchant that disappears from the source keeps its open version forever unless you configure hard_deletes to track it.
• They do not decide what deserves history. check_cols is a business judgement about which attributes carry meaning over time. Nothing in dbt can make that call, and putting every column in the list produces a new version every time somebody fixes a typo.

Simulate a broken dimension load by hiding one merchant from dim_merchants, then inner-join the fact to it - which is what almost every hand-written star schema does by default. Compare the row count and revenue with the untouched fact table.

WITH dim_without_m07 AS (
    SELECT * FROM main_marts.dim_merchants WHERE merchant_id != 'M07'
)
SELECT (SELECT count(*) FROM main_marts.fct_order_items)                 AS fact_rows,
       count(*)                                                          AS rows_after_inner_join,
       (SELECT round(sum(net_amount), 2) FROM main_marts.fct_order_items) AS revenue_all_rows,
       round(sum(f.net_amount), 2)                                       AS revenue_after_inner_join
FROM main_marts.fct_order_items f
JOIN dim_without_m07 d ON d.merchant_sk = f.merchant_sk;

Now change JOIN to LEFT JOIN and run it again. The row count returns to 1,434 and the revenue returns to the full total, because the unmatched rows survive with a null key - which in the real model is coalesced to '-1' and counted. That two-word difference is the whole discipline.

dim_merchants carries the full type-2 column set. Look at the values it currently holds and you can see the difference between designing SCD2 and maintaining it, without needing the snapshot table at all.

SELECT merchant_id,
       merchant_name,
       merchant_tier,
       commission_pct,
       valid_from,
       valid_to,
       is_current
FROM main_marts.dim_merchants
ORDER BY merchant_id
LIMIT 6;

Those three columns are the shape of history with no history in them, which is exactly the state a hand-written warehouse tends to stop at. The snapshot is what fills them: run dbt snapshot on the cloned project, change a merchant tier in data/merchants.csv, reload and snapshot again, and snapshots.merchants_snapshot will hold two rows for that merchant with dbt_valid_to set on the first. That is your homework, and it is the most convincing five minutes in the whole project.

No SQL. This is the judgement call snapshots hand back to you, and getting it wrong is expensive in both directions.

Take one dimension in your own warehouse and sort its attributes into three piles:

• History matters, and somebody would notice. Anything that appears in a rate, a payout, a segment definition, a contract, or a regulatory report. Merchant tier and commission rate are here, which is why they are in this project's check_cols. So are pricing bands, credit ratings, account managers, and any status that gates entitlement.
• History does not matter. A shopper's city, a display name, a preferred language. Nobody has ever asked for the history of these, so they are type 1 and get overwritten. Putting them in check_cols generates a new version every time somebody fixes a spelling and makes the snapshot useless to read.
• You genuinely cannot tell. This is the interesting pile, and the answer is not to guess. Ask whoever owns the process whether a change is a correction or an event. Corrections are type 1: the old value was never true. Events are type 2: the old value was true and stopped being true. Country is the classic ambiguous one - a data fix versus an actual relocation - and the two want opposite treatment from the same column.
• The one to check yourself on: anything currently type 1 that somebody has already asked a historical question about, and got an answer from a rebuilt dimension. That answer was wrong, quietly, and nobody knows.

Bring the first pile to b10. Adding a snapshot to a subject area is part of the capstone, and having already decided which attributes deserve one is most of the work.