Build a Data Warehouse From Scratch! dlt, dbt and MotherDuck Project

Build a data warehouse from scratch with dlt, MotherDuck and dbt: raw API data in, a tested star schema out. It runs from your laptop on MotherDuck's free plan, with no cloud subscription. The source is a small API for a made-up coffee shop that changes like a real one: orders move from placed to shipped, and some get refunded weeks later. dlt loads it into a raw schema incrementally, using updated_at as the cursor and a high watermark, so a re-run only pulls what changed and merges it in place. Then the part that breaks: an unpinned install pulls DuckDB 2.0, which MotherDuck doesn't support yet, and the fix is a version pin. In dbt, staging models rename and type the raw tables, and the marts build a star schema with one fact table at order-line grain plus customer and product dimensions. Last come the tests: unique, not_null, relationships, accepted_values and a unit test on the revenue rule. The relationships test fails on real data, with 684 order lines pointing at archived products the API never sent, $16,000 that would have gone missing from revenue by category, and the fix belongs in ingestion. Versions used (October 2026): dlt 1.31.0, DuckDB 1.5.5, dbt 2.0.9. ⏱️ Chapters 0:00 - Building a data warehouse from scratch 0:56 - The stack: dlt, MotherDuck and dbt 2:08 - Why a warehouse has three layers 2:51 - Setup: MotherDuck token and Python 5:43 - A source API that behaves like a real one 7:45 - Loading raw data with dlt 10:39 - Incremental loads and the high watermark 11:32 - The part that breaks: DuckDB 2.0 vs MotherDuck 12:58 - Staging models in dbt 15:28 - Building the star schema 17:21 - Testing the warehouse with dbt 18:31 - The test that fails on real data 19:24 - Fixing it at ingestion 21:02 - Wrap-up 🔗 Links MotherDuck: https://motherduck.com dlt: https://dlthub.com dbt: https://www.getdbt.com DuckDB: https://duckdb.org #endtoenddataengineeringproject #builddatawarehousefromscratch #dataengineeringproject #dataengineeringprojectforbeginners #datawarehouse #MotherDuck #dlt #dbt #DuckDB #starschema #dbttests #incrementalloading #datamodeling #dataengineering #dataengineeringportfolio