Dimension design

Every dimension gets an integer key that means nothing: merchant_sk, product_sk, user_sk. The real business identifier stays on the row as an ordinary column you can still read and still join on: merchant_id = 'M07', product_id = 1001. Two keys, two jobs.

The failure it prevents: history that cannot change without breaking old facts. If fact_order_item stored merchant_id = 'M07' directly, then there is exactly one row in the world describing M07, and the moment its tier or commission changes you have to choose between overwriting the past or refusing the change. With a surrogate key, M07 can have as many versions as it needs and every fact keeps pointing at the version that was true when it happened. Rule 3 is only possible because rule 1 came first.

• Meaningless on purpose. A surrogate key must carry no information - no embedded date, no source-system prefix, no check digit. The instant it means something, somebody parses it, and now you cannot change it.
• Narrow and integer. Fact tables are the big tables, and every dimension key is a column on every fact row. An integer beats a 36-character UUID on scan cost, join cost and storage, at hundreds of millions of rows.
• Keep the business key visible. A dimension whose natural key is hidden is unusable for reconciliation. When finance asks why M07's number differs from their ledger, you need to be able to say WHERE merchant_id = 'M07' out loud.
• The business key is not unique on a type-2 dimension. dim_merchant.merchant_id deliberately has no UNIQUE constraint, because versions are rows. Only (merchant_id, is_current = 1) is unique, which is why sql/10 indexes exactly that pair.

dim_product carries merchant_name and the merchant's category as columns, even though dim_merchant already has both. In session b2 that duplication was a textbook third-normal-form violation and the lab scored it as a bug. Here it is the product. The reversal is deliberate, and knowing why it reverses is the difference between following a rule and understanding one.

• In OLTP, duplication is a bug because the table is written to constantly and two copies will eventually disagree. The update anomaly is real and it costs money.
• In a dimension, duplication is safe because the table is written by exactly one process, on a schedule, from one source of truth. There is no second writer to disagree with. The anomaly needs two writers, and a load job is one.
• And the duplication buys something concrete: it removes a join from every single analyst query, and from every query an AI agent generates. "Revenue by merchant name" is one join instead of two. Snowflaking - normalizing the dimension back out into dim_product plus dim_merchant plus dim_category - would add a hop per attribute, and every hop is a chance to pick the wrong key or drop rows.
• Where snowflaking is defensible: a genuinely enormous dimension with a huge, rarely-used attribute group hanging off it, or an attribute set governed by a different team on a different cadence. Both are real, both are rare, and both should be argued rather than assumed.

dim_date is the one dimension you always generate and never source. No application owns the calendar, so no application can hand it to you. Generate it, cover a range wider than your data (Bazaar's data runs 2026-04-01 to 2026-06-29; the dimension runs 2026-03-01 to 2026-09-30, 215 rows including an unknown member), and put every calendar attribute anyone will ever group by into columns: year, quarter, month, month name, day of month, day of week, day name, week of year, and a weekend flag. That last column is why two dashboards can no longer disagree about whether Saturday is a weekend day.

date_key is a readable integer, 20260610 rather than a meaningless sequence, which is the one sanctioned exception to "surrogate keys mean nothing". It earns the exception because it makes a fact table scannable by eye and partitionable by prefix, and because the calendar is the one thing that genuinely never gets restated.

dim_time_of_day is kept separate from dim_date, and that is the whole point. Folding hours into the date dimension would multiply it by 24 for no benefit. Keeping them apart means "when is our peak hour?" groups across all 90 days at once, on 24 rows, instead of needing an expression over 2,160 date-hour combinations. Bazaar's answer, which b8 chases properly: 21:00 leads with 96 attempts, then 19:00 with 94, then 13:00 and 20:00 tied at 89.

Two dimensions get slowly-changing type 2 treatment, and the choice is driven entirely by whether a real person would ever notice the difference.

• dim_merchant: tier and commission_pct. Bazaar's take rate is a contractual number that changes. A sale in April must report April's tier and April's commission, not today's. Get this wrong and the marketplace restates its own historic take every time it renegotiates a contract - which is a finance conversation you cannot win.
• dim_product: list_price and status. A product's list price moves and its status flips between active and out of stock. Session b8 explains half a revenue drop using product status at the time, which is only possible if that status was versioned rather than overwritten.
• dim_user: type 1, on purpose. Nobody has ever asked for the history of a shopper's city. Type 2 here would double the row count for a question no one asks, and slow down every join for nothing.

The mechanics are three columns. valid_from and valid_to bound the version, is_current flags the live one, and the current row uses valid_to = '9999-12-31' rather than NULL so that a date-range predicate never has to special-case it. A fact stores the *_sk of the version that was true when the event happened, so the fact never needs updating again.

Every attribute column in every Bazaar dimension is NOT NULL DEFAULT 'unknown', and every dimension has a member at key -1 whose attributes are all the string 'unknown'. Two devices, one purpose.

• A NULL attribute silently drops rows from every inner join and every predicate. WHERE city <> 'Singapore' excludes the NULL cities. So does WHERE city = 'Singapore'. So does a GROUP BY that a BI tool wrote for you. The row does not error, it does not warn, it just is not there. The string 'unknown' is a value, so it groups, filters and totals like any other.
• A missing key does the same thing, worse. If a fact's merchant_sk is NULL and the query inner-joins to the dimension, the whole fact row vanishes - money included. Pointing it at -1 instead keeps the row, keeps the revenue in the total, and puts it in a bucket literally labelled "unknown" so somebody notices.
• That is why the load uses LEFT JOIN plus COALESCE to -1 for every dimension lookup, never an inner join. Silently-missing revenue is the worst failure mode in this whole course, and an inner join in a fact load is how you get it.

The arithmetic of the failure is what makes it dangerous. Four percent of rows missing is not a number anyone spots in a trend chart, and it is a big enough number to change a decision. The demo below reproduces the whole thing in twelve rows of SQL, and it goes from 400 to 300 to 200 and back to 400.

Copying source columns into a dimension is transport, not modeling. The columns that make a dimension worth building are the ones that do not exist anywhere in the source:

• price_tier on dim_product: budget / mid / premium. The source has a number. The business thinks in bands. Defining the band once in the dimension is what stops three dashboards from disagreeing about what "premium" means.
• tenure_bucket on dim_user: new / 3-12m / 1y+. The source has a signup timestamp. Every analyst who bands it writes a slightly different CASE expression and gets a slightly different answer.
• daypart on dim_time_of_day: overnight / morning / lunch / afternoon / evening / late. Derived from nothing at all - it is pure business vocabulary, and it is the word a person says out loud instead of "hours 11 through 13".
• method_family on dim_payment_method: card / wallet / bank. Grouping by the raw source string card_visa is not the grouping anyone wants, and the alternative is a CASE statement that differs per analyst.

All four share one property: they turn a decision that used to be made in every query into a decision made once, in one place, reviewable in a pull request. That is the actual product of dimension design. The columns are cheap; the agreement is the asset.

Tiny Fern (M05) renegotiates: bronze at 18% becomes gold at 13%, effective 2026-06-16. Apply it the type-2 way - close the old version, open a new one - then check what happens to the sales that already happened.

-- close the current version at the last day it was true
UPDATE dim_merchant
   SET valid_to = '2026-06-15', is_current = 0
 WHERE merchant_id = 'M05' AND is_current = 1;

-- open the new version; it gets a brand new surrogate key
INSERT INTO dim_merchant (merchant_id, merchant_name, category, country, tier,
                          commission_pct, joined_date, valid_from, valid_to, is_current)
SELECT merchant_id, merchant_name, category, country, 'gold', 0.13, joined_date,
       '2026-06-16', '9999-12-31', 1
FROM dim_merchant
WHERE merchant_id = 'M05' AND is_current = 0;

SELECT merchant_sk, merchant_id, merchant_name, tier, commission_pct,
       valid_from, valid_to, is_current
FROM dim_merchant
WHERE merchant_id = 'M05'
ORDER BY valid_from;

-- and the sales that already happened: untouched, still bronze, still 18%
SELECT m.tier         AS tier_on_the_fact,
       m.commission_pct AS rate_used,
       COUNT(*)       AS lines,
       ROUND(SUM(f.net_amount), 2)       AS net_revenue,
       ROUND(SUM(f.commission_amount), 2) AS commission
FROM fact_order_item f
JOIN dim_merchant m ON m.merchant_sk = f.merchant_sk
WHERE m.merchant_id = 'M05'
GROUP BY m.tier, m.commission_pct;

That second result is the payoff. No fact row was updated, no historic commission was restated, and "revenue by merchant tier" for April still says bronze. Try the same thing with a type-1 dimension - just UPDATE dim_merchant SET tier = 'gold' - and every April sale silently becomes a gold-tier sale, at a commission rate that did not exist in April.

Four fact rows, 400 dollars, one dimension with a NULL in it. Watch the total fall twice and then come back.

CREATE TABLE dim_channel_bad  (channel_sk INTEGER PRIMARY KEY, channel TEXT);
INSERT INTO dim_channel_bad  VALUES (1, 'paid_search'), (2, 'organic'), (3, NULL);

CREATE TABLE dim_channel_good (channel_sk INTEGER PRIMARY KEY, channel TEXT NOT NULL);
INSERT INTO dim_channel_good VALUES (-1, 'unknown'), (1, 'paid_search'),
                                    (2, 'organic'), (3, 'unknown');

CREATE TABLE fact_tiny (order_id INTEGER, channel_sk INTEGER, net_amount REAL);
INSERT INTO fact_tiny VALUES (1, 1, 100), (2, 2, 100), (3, 3, 100), (4, -1, 100);

SELECT 'the fact table itself' AS measured, COUNT(*) AS rows_counted,
       SUM(net_amount) AS revenue FROM fact_tiny
UNION ALL
SELECT 'inner join to the NULL dimension', COUNT(*), SUM(f.net_amount)
  FROM fact_tiny f JOIN dim_channel_bad d ON d.channel_sk = f.channel_sk
UNION ALL
SELECT 'and now filter by channel', COUNT(*), SUM(f.net_amount)
  FROM fact_tiny f JOIN dim_channel_bad d ON d.channel_sk = f.channel_sk
 WHERE d.channel <> 'nothing at all'
UNION ALL
SELECT 'inner join to the unknown-member dimension', COUNT(*), SUM(f.net_amount)
  FROM fact_tiny f JOIN dim_channel_good d ON d.channel_sk = f.channel_sk;

-- the same check, on the real thing
SELECT 'fact_order_item' AS fact_table, COUNT(*) AS fact_rows,
       SUM(CASE WHEN date_key = -1 OR user_sk = -1 OR merchant_sk = -1
                  OR product_sk = -1 THEN 1 ELSE 0 END) AS rows_on_unknown,
       ROUND(SUM(CASE WHEN date_key = -1 OR user_sk = -1 OR merchant_sk = -1
                        OR product_sk = -1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS pct_unknown
FROM fact_order_item
UNION ALL
SELECT 'fact_transaction', COUNT(*),
       SUM(CASE WHEN date_key = -1 OR user_sk = -1 OR payment_method_key = -1 THEN 1 ELSE 0 END),
       ROUND(SUM(CASE WHEN date_key = -1 OR user_sk = -1 OR payment_method_key = -1
                      THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1)
FROM fact_transaction
UNION ALL
SELECT 'fact_cart', COUNT(*),
       SUM(CASE WHEN date_key = -1 OR user_sk = -1 OR merchant_sk = -1 THEN 1 ELSE 0 END),
       ROUND(SUM(CASE WHEN date_key = -1 OR user_sk = -1 OR merchant_sk = -1
                      THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1)
FROM fact_cart;

The third row is the cruel one: a perfectly ordinary filter, which has nothing to say about NULLs, quietly halves the total. A predicate on a NULL evaluates to NULL, and NULL is not true, so the row is gone. Replace the NULL with the string 'unknown' and every one of those queries returns 400.

SELECT t.hour_label, t.daypart, t.is_business_hour,
       COUNT(*)           AS attempts,
       SUM(f.is_approved) AS approvals,
       SUM(f.is_declined) AS declines
FROM fact_transaction f
JOIN dim_time_of_day t ON t.time_key = f.time_key
GROUP BY t.hour_label, t.daypart, t.is_business_hour
ORDER BY attempts DESC
LIMIT 6;

No SQL. Pick the dimension table your organisation uses most - customer, product, store, account - and put the four rules to it in order.

• Rule 1: is the key a surrogate, or is the business identifier doing double duty? If a report ever needs to restate history, that answer decides whether you can.
• Rule 2: how many joins does your most-asked question need? Every join beyond fact-to-dimension is an attribute somebody chose not to flatten.
• Rule 3: name the attributes on it that change. For each one, ask whether a person would notice if last quarter's report changed when it changed. Every yes is a type-2 candidate; every no should stay type 1.
• Rule 4: run a NULL count on every attribute column, and check whether an unknown member exists at all. Then check whether your fact loads use inner joins to this dimension. That combination is the 4% story in the callout above.

Bring the audit to b7. The facts you design there will point at exactly these dimensions, and a fact table can only be as trustworthy as the dimensions it joins to.