Fact design

Try it out loud on any table you are about to create. "One row is one..." If you get stuck, you have learned something important, and it is always one of three things:

• You need the word "and". "One row is one order and its payment" is two grains wearing one name. That table will double-count the moment an order gets a second attempt, which happens on the first day in production.
• You need the word "sometimes". "One row is one order, or sometimes a refund line" means the table's meaning depends on the row, so no measure on it is safe to sum. Split it.
• You can only describe the query. "One row is what the weekly revenue report needs" is not a grain, it is a screenshot. You do not have a fact table - you have a query result you are about to freeze, and it will be wrong the first time somebody asks a slightly different question. This is the most common way a warehouse fills up with tables nobody can reuse.

The pressure to build one big table is real and it always sounds reasonable: "analysts want one place to go". Here is the arithmetic that settles it. Take a single Bazaar order with 3 product lines and 2 payment attempts, and put both facts in one table:

• fact_order_item would contribute 3 rows, one per line. That is what was sold.
• fact_transaction would contribute 2 rows, one per attempt. That is what was paid.
• Together, at one grain, you get 3 x 2 = 6 rows, because there is no rule that pairs line 2 with attempt 1. Every line's revenue now appears twice, so revenue for that order is exactly 2x the truth.

Change the order to 4 lines and 3 attempts and the multiplier becomes 3x. The error is not a constant you could correct for; it varies row by row with how many times a customer's card was retried. That is why the answer is never "divide by the number of attempts".

And the third fact earns its place for a different reason. fact_cart holds demand that never became revenue - 2,229 carts against 1,434 sold lines. Delete it and "sales dropped" and "fewer people wanted to buy" become the same sentence, which is exactly the distinction session b8 needs to make.

Kimball's three fact types are really three different answers to "when does a row appear":

• Transaction fact - a row appears when something happens, and never changes again. All three of Bazaar's facts are this type, which is the type you should default to. Insert-only tables are the easiest thing in a warehouse to load, test and trust.
• Periodic snapshot - a row appears per entity per period whether anything happened or not: inventory on hand each night, account balance each month-end. The measures are usually semi-additive, and the table grows on a schedule rather than with the business.
• Accumulating snapshot - one row per thing that moves through a pipeline, updated in place as it hits each milestone. An order with placed_date, packed_date, shipped_date, delivered_date is the classic. It is the only fact type you routinely UPDATE, which is why it is also the one that needs the most care.

Factless facts are the fourth shape, and the one people are slowest to reach for: a fact table with dimension keys and no measures, recording that a combination of things occurred. A row per product viewed by a user, a row per promotion a merchant was eligible for. You count rows and you count absences - "which products were eligible for the sale and never sold" is a question only a factless fact can answer.

Bazaar has a near-miss worth noticing. fact_cart has measures, so it is a normal transaction fact, but a fact_cart_item at the grain "one product in one cart" would be almost factless: quantity, and nothing else. The capstone in b10 asks you to decide whether returns need one.

The test is mechanical: can I SUM this column across every dimension on the table and get a number a business person would accept? Run it column by column. Anything that fails is a ratio, a rate or a snapshot in disguise.

• Additive passes everywhere. SUM(net_amount) by merchant, by day, by product, by hour, all at once - all valid, all reconcilable to the same total.
• Semi-additive passes across some dimensions and fails across time. There is no semi-additive measure in Bazaar, which is lucky, because they are the ones that produce plausible nonsense: a year-end inventory figure that is twelve months of stock added together.
• Non-additive fails immediately, and the giveaway is always division. If a column was computed with a /, storing it lets someone average it later, and an average of averages is wrong by an amount nobody can predict.

So the rule in sql/11 is absolute: a ratio is stored as its numerator and its denominator, never as the ratio. The marts in b8 follow it too - mart_merchant_daily ships aov_numerator and aov_denominator rather than aov, precisely so that the AOV of a week is computable from the AOV of its days.

Every dimension key in sql/12_oltp_to_star.sql is resolved the same way, and it looks needlessly defensive until the first time it saves you:

• LEFT JOIN the dimension, so a lookup that misses returns NULL instead of removing the row.
• COALESCE(dim.key, -1), so that NULL becomes the unknown member session b6 put in every dimension.
• Never JOIN. An inner join drops the fact row when the lookup misses. The load succeeds, the row counts look plausible, and the money on that row is simply gone.

Think about which failure you would rather debug. With the unknown member, a merchant that failed to load shows up as an "unknown" row on every report, and somebody asks about it within a day. With an inner join, revenue is quietly lower than it should be, everybody assumes the market softened, and the reconciliation happens at quarter end if it happens at all.

This is also why validate_model.py asserts an unknown-member rate of 0.0% on all three facts as one of its 27 checks. A rate of zero is not the point - a rate you are measuring is the point.

A dimension is conformed when two or more facts use the identical dimension table, with the same keys and the same attribute meanings. Bazaar's dim_date, dim_time_of_day and dim_user are shared by all three facts; dim_merchant is shared by sales and carts.

Conformance is what makes "drilling across" possible, and drilling across is the legal way to answer a question that spans facts:

• Aggregate each fact separately to the shared grain. Revenue by date from fact_order_item, attempts by date from fact_transaction, carts by date from fact_cart.
• Then join the results on the conformed key. Three separate aggregations, one join, no fan-out possible, because each side already has exactly one row per date.

mart_funnel_daily in session b8 is that pattern written out: three CTEs, one per fact, each aggregated to one row per day, then joined. It is the single most reusable shape in dimensional modeling, and once you have seen it the temptation to join two facts directly disappears.

The failure mode on the other side is worth naming too. If sales says "merchant" and carts says "seller", and the two tables list slightly different merchants, then no amount of correct SQL will make the two facts comparable. Conformance is a naming and ownership discipline before it is a technical one, which is why session a5 treats it as a standard rather than a schema detail.

Press ▶ Run. The first run downloads the engine once, then caches it.

SELECT 'fact_order_item'  AS fact,
       'one product line on one order' AS grain_sentence,
       COUNT(*)                        AS rows,
       COUNT(DISTINCT order_id)        AS distinct_orders
FROM fact_order_item
UNION ALL
SELECT 'fact_transaction', 'one payment attempt',
       COUNT(*), COUNT(DISTINCT order_id) FROM fact_transaction
UNION ALL
SELECT 'fact_cart', 'one cart',
       COUNT(*), COUNT(DISTINCT order_id) FROM fact_cart;

SELECT 'net_revenue' AS measure, 'additive' AS additivity,
       ROUND(SUM(net_revenue), 2) AS value_summed
FROM v_sales_line WHERE is_paid = 1
UNION ALL
SELECT 'margin', 'additive', ROUND(SUM(margin), 2)
FROM v_sales_line WHERE is_paid = 1;

SELECT ROUND(SUM(net_revenue), 2)                            AS numerator_revenue,
       COUNT(DISTINCT order_id)                              AS denominator_orders,
       ROUND(SUM(net_revenue) / COUNT(DISTINCT order_id), 2) AS aov_divided_at_read_time,
       ROUND(AVG(net_revenue), 2)                            AS aov_if_you_average_lines,
       ROUND(100.0 * SUM(margin) / SUM(net_revenue), 2)      AS margin_pct
FROM v_sales_line
WHERE is_paid = 1;

116.93 against 80.68, from the same rows. The gap exists because the denominator changed silently from orders to lines. Notice that margin_pct of 46.89% is computed the same way, from two sums - it is never a stored column, and that is why it is still correct when you group by merchant, by month or by both.

SELECT COUNT(*)                 AS fact_rows,
       COUNT(DISTINCT order_id) AS distinct_orders,
       MAX(line_no)             AS most_lines_on_one_order
FROM fact_order_item;

-- the drill-down job: from a merchant total back to the individual orders
SELECT s.merchant_name, s.order_id,
       COUNT(*)                     AS lines_on_order,
       ROUND(SUM(s.net_revenue), 2) AS order_revenue
FROM v_sales_line s
WHERE s.is_paid = 1 AND s.merchant_name = 'Nimbus Audio'
GROUP BY s.merchant_name, s.order_id
ORDER BY order_revenue DESC
LIMIT 8;

1,434 rows and far fewer distinct orders is the whole reason COUNT(*) must never be called "orders". Session b9 shows an AI agent making exactly that mistake and reporting 481 orders for June instead of 327 - a query that runs, on a correct table, and is still wrong because nobody published the grain.

This is the argument for LEFT JOIN plus COALESCE, made as an experiment. Delete one merchant from the dimension - a stand-in for a load that half failed - then read the same revenue two ways.

-- Simulate a dimension row that failed to load. Foreign keys are switched off
-- for this one demo on purpose: in a real warehouse the load would have
-- inserted the fact and skipped the dimension, which is exactly this state.
PRAGMA foreign_keys = OFF;
DELETE FROM dim_merchant WHERE merchant_id = 'M07';

SELECT 'INNER JOIN - rows silently dropped' AS how,
       COUNT(*)                      AS fact_rows_kept,
       ROUND(SUM(f.net_amount), 2)   AS revenue_reported
FROM fact_order_item f
JOIN dim_merchant m ON m.merchant_sk = f.merchant_sk
WHERE f.is_paid = 1
UNION ALL
SELECT 'LEFT JOIN - every row survives', COUNT(*), ROUND(SUM(f.net_amount), 2)
FROM fact_order_item f
LEFT JOIN dim_merchant m ON m.merchant_sk = f.merchant_sk
WHERE f.is_paid = 1;

-- and the check that would have caught it: unknown members per fact
SELECT 'fact_order_item' AS fact,
       SUM(CASE WHEN merchant_sk = -1 THEN 1 ELSE 0 END) AS unknown_merchant,
       SUM(CASE WHEN product_sk  = -1 THEN 1 ELSE 0 END) AS unknown_product,
       SUM(CASE WHEN user_sk     = -1 THEN 1 ELSE 0 END) AS unknown_user,
       SUM(CASE WHEN date_key    = -1 THEN 1 ELSE 0 END) AS unknown_date
FROM fact_order_item;

The LEFT JOIN still reports 111,906.48. The inner join reports less, and nothing anywhere says so. Now change the load in your head: instead of the dimension row being missing, imagine the fact arrived with a merchant id the dimension has never seen. COALESCE(..., -1) is what turns that into a visible "unknown" row on a report rather than a silent subtraction from revenue, and the last query is the one-line audit that turns it into a number somebody watches.