The brief
Daybreak's board meets Friday. They want a one-page quarterly pack: monthly revenue trend, refund rate by channel, weekend share of orders, top products. The catch you now understand: none of those come cleanly off the app database. So you will build the whole warehouse that makes them a five-line query each - source to staging (b2) to star with surrogate keys and a date dimension (b3-b4) to a slowly changing customer dimension (b5) to served answers (b7), with the loading and performance instincts from b6 and b8 baked in.
The whole pipeline on one strip 3 min live
Before you type, see the shape. Every stage below is one session's work; tonight you run them back to back. Read it left to right - that is the order your scripts will follow.
Raw source to a trusted staging layer ★ 12 min · everyone builds
Start where every warehouse starts: get clean, typed copies out of the raw source, and prove they are trustworthy before anything downstream touches them. This is b2, compressed into one script.
Build stg_customers, stg_orders (CAST the text date), and stg_order_items (with a computed line_amount).
Run the three quality gates: row-count reconciliation, a null scan on the key you will join on, and a freshness check against a fixed reference date.
End with a one-row summary so you can see at a glance that staging is sound.
CREATE TABLE stg_customers AS
SELECT customer_id, name, city, country,
CAST(signup_date AS DATE) AS signup_date, plan
FROM customers;
CREATE TABLE stg_orders AS
SELECT order_id, customer_id,
CAST(order_date AS DATE) AS order_date, status, channel
FROM orders;
CREATE TABLE stg_order_items AS
SELECT order_id, product_id, quantity, unit_price,
quantity * unit_price AS line_amount
FROM order_items;
SELECT
(SELECT count(*) FROM stg_orders) AS orders_staged,
(SELECT count(*) FROM orders) AS orders_source,
(SELECT count(*) FROM stg_orders WHERE customer_id IS NULL) AS null_keys,
(SELECT max(order_date) FROM stg_orders) AS latest_order,
(SELECT max(order_date) FROM stg_orders) >= DATE '2026-06-01' AS is_fresh;
Gates before glory. The temptation in a capstone is to race to the pretty chart. Real teams that skip the reconciliation row ship warehouses that quietly drop 3% of orders on a bad load and nobody notices for a month. The boring summary SELECT is the professional move.
Dimensions, a fact, and a history flourish ★ 15 min · everyone builds
Now the star. This script is long on purpose - every line is one you wrote in b3, b4, or b5. Because the database is fresh, it rebuilds staging in a few lines first, then adds the date dimension, two more dimensions with surrogate keys, the fact table, and one slowly changing dimension move on customer 2.
Rebuild the staging tables this box needs (fast - three CTAS).
Build dim_date with generate_series, then dim_customer and dim_product with row_number() surrogate keys.
Assemble fct_order_line by joining staging to the dimension keys.
Confirm the whole star with a row-count-per-table summary.
CREATE TABLE stg_customers AS
SELECT customer_id, name, city, country,
CAST(signup_date AS DATE) AS signup_date, plan FROM customers;
CREATE TABLE stg_orders AS
SELECT order_id, customer_id, CAST(order_date AS DATE) AS order_date,
status, channel FROM orders;
CREATE TABLE stg_order_items AS
SELECT order_id, product_id, quantity, unit_price,
quantity * unit_price AS line_amount FROM order_items;
CREATE TABLE dim_date AS
SELECT CAST(gs AS DATE) AS date_key,
EXTRACT(year FROM gs) AS year,
EXTRACT(month FROM gs) AS month,
strftime(gs, '%b') AS month_name,
EXTRACT(dow FROM gs) IN (0, 6) AS is_weekend
FROM generate_series(DATE '2025-11-01', DATE '2026-06-30', INTERVAL 1 DAY) t(gs);
CREATE TABLE dim_customer AS
SELECT row_number() OVER (ORDER BY customer_id) AS customer_key,
customer_id, name, city, country, plan FROM stg_customers;
CREATE TABLE dim_product AS
SELECT row_number() OVER (ORDER BY product_id) AS product_key,
product_id, name, category, price, roast FROM products;
CREATE TABLE fct_order_line AS
SELECT dc.customer_key, dp.product_key, o.order_date AS date_key,
o.order_id, oi.quantity, oi.line_amount AS line_revenue,
o.status, o.channel
FROM stg_orders o
JOIN stg_order_items oi USING (order_id)
JOIN dim_customer dc ON o.customer_id = dc.customer_id
JOIN dim_product dp ON oi.product_id = dp.product_id;
SELECT 'dim_date' AS tbl, count(*) AS rows FROM dim_date
UNION ALL SELECT 'dim_customer', count(*) FROM dim_customer
UNION ALL SELECT 'dim_product', count(*) FROM dim_product
UNION ALL SELECT 'fct_order_line', count(*) FROM fct_order_line;
Self-studyThe SCD flourish - preserve history on an upgrade4 min▶
The star above uses a plain dim_customer. In production you would run the b5 Type 2 pattern so an upgrade does not rewrite the past. Here it is on the pre-built scd star, standalone and re-runnable - Liam Ford (customer 2) moves Basic to Pro:
UPDATE dim_customer_scd
SET valid_to = DATE '2026-06-30', is_current = FALSE
WHERE customer_id = 2 AND is_current;
INSERT INTO dim_customer_scd
SELECT (SELECT max(customer_key) + 1 FROM dim_customer_scd),
customer_id, name, city, country, 'Pro',
DATE '2026-07-01', NULL, TRUE
FROM stg_customers WHERE customer_id = 2;
SELECT customer_id, plan, valid_from, valid_to, is_current
FROM dim_customer_scd WHERE customer_id = 2 ORDER BY valid_from;
The board pack, four answers and an export ★ 15 min · everyone builds
The payoff. These run against the pre-built star setup, so you can feel how a good model turns each board question into a short, obvious query. Four answers, then the export flourish from b9.
LiveAnswer 1 · Monthly revenue trend3 min▶
SELECT d.month_name,
ROUND(SUM(f.line_revenue), 0) AS revenue
FROM fct_order_line f
JOIN dim_date d ON f.date_key = d.date_key
WHERE f.status = 'completed'
GROUP BY d.month_name, d.month
ORDER BY d.month;
LiveAnswer 2 · Refund rate by channel3 min▶
SELECT channel,
count(*) AS order_lines,
ROUND(AVG(CASE WHEN status = 'refunded' THEN 1.0 ELSE 0 END) * 100, 1) AS refund_pct
FROM fct_order_line
GROUP BY channel
ORDER BY refund_pct DESC;
LiveAnswer 3 · Weekend share of orders3 min▶
SELECT CASE WHEN d.is_weekend THEN 'weekend' ELSE 'weekday' END AS day_type,
count(DISTINCT f.order_id) AS orders
FROM fct_order_line f
JOIN dim_date d ON f.date_key = d.date_key
GROUP BY day_type;
LiveAnswer 4 · Top products by revenue, and the export4 min▶
The top-products answer, then the b9 move: write the monthly trend to a Parquet file and read it straight back - the board pack as a portable artifact.
SELECT p.name,
ROUND(SUM(f.line_revenue), 0) AS revenue
FROM fct_order_line f
JOIN dim_product p ON f.product_key = p.product_key
WHERE f.status = 'completed'
GROUP BY p.name
ORDER BY revenue DESC
LIMIT 3;
COPY ( SELECT d.month_name, ROUND(SUM(f.line_revenue), 0) AS revenue FROM fct_order_line f JOIN dim_date d ON f.date_key = d.date_key WHERE f.status = 'completed' GROUP BY d.month_name, d.month ORDER BY d.month ) TO 'board_pack.parquet' (FORMAT PARQUET); SELECT * FROM 'board_pack.parquet';
What you can now do 5 min live
You built a warehouse. Not a toy - the same architecture, in the same order, that a data team ships. The leader track (a1-a6) is the executive twin of what your hands just did: now you can both build it and explain why it earns its keep.
LiveWhere to go next2 min▶
- learn-data-engineering-with-phoebe - orchestration, pipelines, and getting this to run on a schedule instead of by hand.
- learn-dataops-with-phoebe - running the warehouse in production: testing, CI, monitoring, incident response.
- learn-data-modeling-with-phoebe - deeper Kimball: bus matrix, bridge tables, advanced dimensional patterns.
Self-studyThe honest gap list - what this course did not cover2 min read▶
A warehouse in the wild has parts this course pointed at but did not build, by design:
- Orchestration - Airflow, Dagster, or dbt scheduling that runs your scripts nightly. That is the data-engineering course.
- Permissions and governance - who can read which mart, PII controls, audit. That is the governance bucket.
- BI tools - Tableau, Power BI, Looker sitting on your marts. Your job ends at a clean, documented mart; theirs begins there.
Knowing the edges of your job is itself a senior skill. You now know them.
Take it off the page ◐ 45-60 min
- Install DuckDB on your laptop (one binary,
duckdb.org) and rebuild tonight's capstone in a local.sqlfile - the exact same SQL runs. - Swap Daybreak for your own domain: list your world's six source tables, name the grain of your fact, sketch two dimensions and one date dimension.
- Write the one board question your team argues about most, and design the mart column that would end the argument.
- Re-run your model script twice and prove the row counts do not change - your idempotency check from b6.
- Pick your next course from the "where to go next" card and start it this week while the momentum is warm.
Official sources covered
The capstone mirrors the final projects of the courses this track drew on, run live on a real engine instead of a graded upload. Certificates and grading stay on the official platforms.
Three questions before you go 🎯 ◐ 90 seconds
1 · What is the correct build order for the pipeline you assembled tonight?
Data flows one way: raw source into typed staging, staging modeled into a star, the star sliced into marts, marts served to the board. Cleaning always comes before modeling.
2 · Why did Demo 2 rebuild the staging tables inside its own script?
Every Run resets the database, so a script must build everything it needs. That is the same idempotency property (b6) that lets a real pipeline safely re-run after a crash.
3 · The board pack defines revenue as completed orders only. Where should that definition live?
A metric defined once and reused is how two dashboards stop disagreeing - the one-truth lesson from leader session a3, enforced in the serving layer.