End-to-end ELT pipeline

Goal

Assemble a complete analytics pipeline from open parts — DuckDB as the warehouse, dbt for transforms (seeded from the jaffle shop project), a real ingestion job, and an orchestrator — treating every stage as versioned, tested code. It proves the concept note's whole arc (ingest → ELT → dimensional model → orchestration → quality) as one working system you can backfill and break on purpose.

Subject: full brief & instructions

Practices

• Data engineering — ELT, dimensional modeling, orchestration, and data quality, exercised end to end.
• Databases and other storage systems — the OLTP source and the columnar warehouse, on the two sides of the pipeline.
• CI/CD & delivery engineering — the same pipeline discipline, applied to SQL.

Milestones

1. Warehouse up — DuckDB + dbt, jaffle shop's raw data loaded as seeds, one staging model built. Shippable: dbt build succeeds and you can query a mart.
2. Staging → marts, documented — clean layer separation (staging views, marts tables), sources declared, dbt docs generate showing the lineage graph.
3. Tests that catch real breakage — uniqueness, not-null, referential and freshness tests; then corrupt the raw data deliberately. Shippable: the broken load fails loudly instead of shipping a wrong number.
4. Dimensional model — a proper star: an orders fact at line-item grain plus customer and date dimensions, the customer dimension as slowly-changing type 2 with validity ranges. Shippable: answer "what did we know then" with a query.
5. Real ingestion — replace the seeds: a Postgres instance (or a public API) as source, an incremental, idempotent load job into the raw layer. Shippable: run it twice, get no duplicates — the replayability the concept note demands.
6. Orchestration — wire ingestion + dbt into Dagster or Airflow as a DAG with retries and a schedule. Shippable: backfill three "missed" days with one command.
7. CI for the pipeline — on every PR: compile, build against a scratch schema, run all tests. Shippable: a red check on a PR that would have broken a mart.

Stretch goals

• Land the raw layer as Parquet on object storage under an open table format (Iceberg or Delta) and point the warehouse at it — the lakehouse move.
• Swap ingestion to CDC with Debezium from the Postgres source.
• Point the same dbt project at a free-tier cloud warehouse (BigQuery) and compare cost mechanics with the local run.

Related

• End-to-end ELT pipeline — subject — the standalone work statement: dataset framing, required layers and tests, star schema, and CI gates.
• Data engineering — the concept note this drills; its Practice section cites this exercise.
• Databases and other storage systems — engine internals on both ends of the pipe.
• Build your own database — the storage-engine other half of the theme.
• Message brokers & event streaming — where the streaming stretch goals lead.