Build a star schema in SQL with a type 2 customer dimension and data checks

This part adds the second step of the course's order pipeline, after module 2 cleaned the raw orders: the tables a dashboard, an analyst or an AI assistant will read. Everything in it comes from parts 1 to 3, in 1 script that builds the model from scratch, checks it and only then runs a report.

Build it: model the orders

Save this as model.sql, beside setup.sql and run_sql.py from part 1:

-- The shop's order model: 2 dimensions, 1 fact, the checks, then the report.

DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_date AS
SELECT CAST(date_format(day, 'yyyyMMdd') AS INT) AS date_key,
       day,
       date_format(day, 'EEEE') AS weekday,
       dayofweek(day) IN (1, 7) AS is_weekend,
       date_format(day, 'yyyy-MM') AS month
FROM (SELECT explode(sequence(DATE'2026-01-01', DATE'2027-12-31', INTERVAL 1 DAY)) AS day);

DROP TABLE IF EXISTS dim_customer;
CREATE TABLE dim_customer AS
SELECT xxhash64(customer_id, changed_at) AS customer_key,
       customer_id, name, country,
       CASE WHEN row_number() OVER w = 1 THEN TIMESTAMP'1900-01-01 00:00:00'
            ELSE changed_at END AS valid_from,
       coalesce(lead(changed_at) OVER w, TIMESTAMP'9999-12-31 00:00:00') AS valid_to,
       lead(changed_at) OVER w IS NULL AS is_current
FROM customer_changes
WINDOW w AS (PARTITION BY customer_id ORDER BY changed_at);

-- 1 row per order, with the key of the customer version that was true when the order was placed.
DROP TABLE IF EXISTS fact_orders;
CREATE TABLE fact_orders AS
SELECT o.order_id,
       c.customer_key,
       CAST(date_format(o.ordered_at, 'yyyyMMdd') AS INT) AS date_key,
       o.ordered_at,
       o.amount
FROM orders o
LEFT JOIN dim_customer c
  ON o.customer_id = c.customer_id
 AND o.ordered_at >= c.valid_from
 AND o.ordered_at < c.valid_to;

-- The checks: each stops the script with its message if the model is wrong.
SELECT assert_true(count(*) = count(DISTINCT customer_key), 'dim_customer has a repeated customer_key') AS unique_keys
FROM dim_customer;

SELECT assert_true(count(*) = count(DISTINCT order_id), 'an order matched more than 1 customer version') AS one_row_per_order,
       assert_true(count(*) = count(customer_key), 'an order matched no customer version') AS every_order_has_a_customer,
       assert_true((SELECT count(*) FROM orders) = count(*), 'fact_orders lost or gained orders') AS every_order_kept
FROM fact_orders;

SELECT assert_true(count(*) = 0, 'an order date is missing from dim_date') AS every_date_known
FROM fact_orders f LEFT ANTI JOIN dim_date d ON f.date_key = d.date_key;

-- The report: revenue by week day and by the country the customer lived in at the time.
SELECT d.weekday, c.country, count(*) AS orders, sum(f.amount) AS revenue
FROM fact_orders f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_customer c ON f.customer_key = c.customer_key
GROUP BY d.weekday, c.country
ORDER BY revenue DESC, weekday;

On your laptop, run python run_sql.py setup.sql model.sql. On Databricks, start a new notebook so nothing from the earlier parts is left in the session, run the statements of setup.sql in SQL cells, then each statement above in its own cell. The tables go in the catalog and schema the notebook is using, as Delta tables, since Delta is the default format on Databricks.

What it prints

Each check returns NULL, the sign it passed:

+-----------+
|unique_keys|
+-----------+
|NULL       |
+-----------+

+-----------------+--------------------------+----------------+
|one_row_per_order|every_order_has_a_customer|every_order_kept|
+-----------------+--------------------------+----------------+
|NULL             |NULL                      |NULL            |
+-----------------+--------------------------+----------------+

+----------------+
|every_date_known|
+----------------+
|NULL            |
+----------------+

Then the report:

+--------+-------+------+-------+
|weekday |country|orders|revenue|
+--------+-------+------+-------+
|Friday  |DE     |1     |60.00  |
|Friday  |PT     |1     |40.00  |
|Sunday  |ES     |1     |30.00  |
|Thursday|PT     |1     |25.50  |
|Monday  |IE     |1     |22.75  |
|Friday  |IE     |1     |18.25  |
|Sunday  |DE     |1     |15.00  |
|Thursday|IE     |1     |12.00  |
+--------+-------+------+-------+

Compare it with part 2's report. Ana's Thursday and Friday orders now count for PT, where she lived when she placed them, and her Sunday order for ES. Chen's 60.00 is there. Revenue adds up to 223.50, the same as the orders, because each order is counted once.

Why it is built this way

Dimensions first, then the fact. The fact table looks up each order's customer_key while it loads, so the report joins on 1 key with no date ranges. That is the type 2 design in full: the key of the version true at the time travels with the fact.

A left join, then a check. An inner join would drop an order that matches no version and leave no trace. The left join keeps it with an empty key, and every_order_has_a_customer stops the build. Make the first version start when it was saved, as in part 3's first attempt, and run the script again: it stops at the second check with [USER_RAISED_EXCEPTION] an order matched no customer version SQLSTATE: P0001, and the report never runs.

Each check tests 1 promise. Keys are unique, there is 1 row per order, no order is lost, every date is known. Each of these can break without an error, from a changed source or a changed join, and each one breaks every total after it. A failing check names the promise in its message, so whoever reads the failed run knows where to look.

It runs from the start every time. Each table is dropped and built again, so a second run gives the same tables as the first, with the same keys, since each key is a hash of the customer and the time the version began. A script that can run twice safely is idempotentIdempotent Pipelines and Exactly-OnceA pipeline step is idempotent when running it twice gives the same result as running it once. That is what makes retries safe, and it is how systems that deliver a message more than once still count it exactly once., and a scheduler can retry it after a failure without making a mess.

Where this goes next

Rebuilding everything works for 8 orders, not for 8 million arriving every hour. Module 4 changes the tables to Delta Lake and Apache Iceberg, so a load can MERGE only the new and changed rows into them and past versions of a table stay readable for a time. Module 7 moves this model into the shop's medallion pipeline, as its gold layer, and module 11 runs these checks as tests on every change to the code.