Building a Star Schema Load

Why the fact table loads last

A star schema splits your data into one narrow fact table of measurements (the numbers you sum, like amount or revenue) surrounded by dim tables that describe them. The fact does not store the messy business keys from your source (email, sku). It stores a surrogate key: a small integer like customer_key that the warehouse itself mints. That keeps the fact narrow, decouples it from source systems that recycle or reformat their keys, and lets a customer change their email without rewriting a billion fact rows.

That design dictates a strict load order. Surrogate keys are born in the dimension, so:

1. Load every dimension first. Each new row gets a surrogate key.
2. Load the fact second. For each staging row, look up the surrogate key by joining on the natural key.

Reverse that and step 2 has nothing to point at. The lookup join is the whole game: it swaps a source email for a warehouse customer_key. You never type a surrogate key literally in the fact insert. You always fetch one.

Schema

• customer_key
• email
• name

• sale_id
• customer_key
• amount

• fact_salesn-1dim_customer— lookup join on email -> customer_key

How the surrogate key gets minted

In SQLite, a column declared INTEGER PRIMARY KEY is an alias for the row's rowid, so it auto-assigns a sequential integer whenever you omit it from the insert.

-- 1. dimension first: omit customer_key so it auto-assigns; keep the natural key
INSERT INTO dim_customer (email, name)
SELECT DISTINCT email, name FROM stg_customers;

-- 2. fact second: join staging's natural key to the dim to fetch the surrogate key
INSERT INTO fact_sales (customer_key, amount)
SELECT dc.customer_key, s.amount
FROM stg_sales s
JOIN dim_customer dc ON dc.email = s.email;

With customers Ann (a@x.com) and Bob (b@x.com), dim_customer gets customer_key 1 and 2. A sale for a@x.com lands in fact_sales with customer_key = 1. The fact never sees the email again.

Pitfalls

• Deduplicate on the key alone, not the whole row. SELECT DISTINCT email, name dedupes distinct pairs. If a@x.com arrives once as Ann and once as Anne, you get two dimension rows for one customer, and the lookup join fans out: one sale becomes two fact rows and your totals inflate. Dedup by the natural key so each customer maps to exactly one surrogate key. GROUP BY email collapses every row for one email into a single dimension row.
• Inner join silently drops orphans. A fact row whose natural key has no dimension match vanishes from an inner join. That is often the correct rule, but verify the dropped count is zero rather than trusting it. An orphan fact is a load bug, not a data feature.
• Re-runnability. Lead with DELETE FROM each target so a second run reproduces the same counts instead of doubling the fact. Delete the fact first, then the dimensions, because the fact references them.

Interview nuance: each fact row must match exactly one dimension row, and that guarantee lives in the dimension, not the fact query. If a dimension holds duplicate natural keys, the inner join becomes a fan-out that multiplies your measures, so the correctness of SUM(revenue) depends on the dimension's natural key being unique. Interviewers probe exactly this by asking what happens when a dimension load forgets to dedupe.