Capstone: the whole pipeline

Daybreak's warehouse (built in the sibling course) needs a reliable feed. That is what you deliver today. The brief is simple to state and real to build: take raw source data and produce a clean, served table the warehouse can load - through the same stages a petabyte pipeline uses, just at a scale that runs in a browser. Three demos take you across the whole lifecycle, each self-contained because the database is fresh every run.

Everything you learned as a separate session is one stage of a single pipeline. Here is the whole thing, with the session that taught each stage marked. Today's demos walk it left to right, from Daybreak's source to the served table the warehouse picks up. This is a doing session - read the map once, then build.

The ingestion and storage stages. Extract three source tables - orders, order_items, customers - and write each to a Parquet file in the landing zone, then read them back with a counts summary to prove the data now lives outside the source system.

-- EXTRACT + LAND: pull three source tables, write each to Parquet
COPY (SELECT * FROM orders)      TO 'orders.parquet'      (FORMAT PARQUET);
COPY (SELECT * FROM order_items) TO 'order_items.parquet' (FORMAT PARQUET);
COPY (SELECT * FROM customers)   TO 'customers.parquet'   (FORMAT PARQUET);

-- read the landing zone back with a counts summary
SELECT 'orders'      AS table_name, count(*) AS rows FROM 'orders.parquet'
UNION ALL
SELECT 'order_items', count(*)                FROM 'order_items.parquet'
UNION ALL
SELECT 'customers',   count(*)                FROM 'customers.parquet'
ORDER BY table_name;

The transform stage, hardened with b9's reliability seams. The database is fresh, so this box re-extracts quickly first, then parses dates defensively, keeps only completed orders, joins to items for revenue, and builds an idempotent daily table. Run the whole box twice - the counts stay identical, proving the write is safe to repeat.

-- re-extract (fresh DB): land orders + items
COPY (SELECT * FROM orders)      TO 'orders.parquet'      (FORMAT PARQUET);
COPY (SELECT * FROM order_items) TO 'order_items.parquet' (FORMAT PARQUET);

-- defensive clean: parse dates, drop unparseable rows, keep completed
CREATE OR REPLACE TABLE clean_orders AS
SELECT order_id, customer_id,
       TRY_CAST(order_date AS DATE) AS order_date,
       status, channel
FROM 'orders.parquet'
WHERE TRY_CAST(order_date AS DATE) IS NOT NULL
  AND status = 'completed';

-- join to items for revenue, aggregate to a daily table (idempotent)
CREATE OR REPLACE TABLE daily_revenue AS
SELECT o.order_date                                AS day,
       round(sum(oi.quantity * oi.unit_price), 2)  AS revenue,
       count(DISTINCT o.order_id)                  AS orders
FROM clean_orders o
JOIN 'order_items.parquet' oi ON o.order_id = oi.order_id
GROUP BY o.order_date;

-- run twice - these numbers do not change
SELECT count(*) AS days, round(sum(revenue), 2) AS total_revenue FROM daily_revenue;

The serving stage and the handoff. Build the final clean served table - daily revenue by channel - then write it as a partitioned Parquet dataset (one folder per month) and read one partition back. This is exactly the output learn-data-warehouse's loader picks up. The DE job ends here.

-- SERVE: build the final clean table the warehouse will load
CREATE OR REPLACE TABLE served AS
SELECT CAST(o.order_date AS DATE)                         AS day,
       strftime(CAST(o.order_date AS DATE), '%Y-%m')      AS month,
       o.channel,
       round(sum(oi.quantity * oi.unit_price), 2)         AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'completed'
GROUP BY day, month, o.channel;

-- write March's partition to its own Parquet file (hand-partitioned by month)
COPY (SELECT * FROM served WHERE month = '2026-03')
  TO 'served_2026_03.parquet' (FORMAT PARQUET);

-- read that partition back - the slice the warehouse loader picks up
SELECT * FROM 'served_2026_03.parquet'
ORDER BY day, channel;

You built the data engineering lifecycle end to end, on a real engine, against a source you did not control. That is the job. Here is what you now hold, how it pairs with the executive a-track twin, and honestly where this course stops.

• Install real DuckDB (duckdb.org) and rebuild the capstone on your laptop - the same three scripts, running natively.
• Point the served Parquet dataset at a real object store (an S3 or GCS bucket) instead of a local folder, and read a partition back from there.
• Swap Daybreak for a source of your own: a CSV export, an app database dump. Run it through extract, land, transform defensively, serve.
• Add a schema check to Demo 2 that fails loudly if the source drops a key column, then hand the whole thing to a teammate to run.
• Write the one-paragraph runbook a DataOps engineer would need to schedule your pipeline: what it reads, what it writes, and how to tell it succeeded.

This capstone assembles the working core of the DeepLearning.AI Data Engineering Professional Certificate (Joe Reis) and Reis & Housley's Fundamentals of Data Engineering into one end-to-end build. Certificates, cloud labs, and videos stay on the official platforms. This page covers: