End-to-end ELT pipeline — subject — Library

Brief

You are the sole data engineer for a small e-commerce shop (the "jaffle shop": customers place orders of one or more items and pay for them). Analytics today is a spreadsheet. Build the whole pipeline — ingestion, warehouse, modeled marts, tests, orchestration, CI — as versioned code on your laptop, sized so every run finishes in seconds but shaped exactly like the production article. Everything must be reproducible from a fresh clone: git clone → documented bootstrap → working warehouse.

Instructions

Stack (fixed)

Warehouse: DuckDB, one .duckdb file (git-ignored).
Transforms: dbt (dbt-core + dbt-duckdb), one project, all models SQL.
Ingestion: a Python job you write.
Orchestrator: Dagster (or Airflow — pick one and stay with it).
CI: GitHub Actions (or equivalent) on every pull request.

Dataset

Start from the jaffle shop's raw CSVs (customers, orders, payments) loaded as dbt seeds — the training wheels. From the ingestion stage onward the seeds are replaced by a source you control: a small generator script that emits order/payment events with timestamps into Postgres (or SQLite) — including out-of-order and late-arriving events, which the pipeline must survive.

Layering

Three dbt layers, cleanly separated, no layer-skipping:

staging/ — one view per source table (stg_customers, stg_orders, stg_payments): rename, cast, deduplicate; no joins, no business logic. Sources declared in YAML with freshness config.
marts/ — tables. Business logic lives here only.
Every model and column of the marts documented in YAML; dbt docs generate must show an unbroken lineage graph source → staging → marts.

Star schema (the marts layer)

fct_order_items — the fact, at order-line grain: one row per item on an order, with surrogate key, foreign keys to the dimensions, order timestamp, quantity, unit price, amount paid.
dim_customers — slowly-changing type 2: one row per customer version, with valid_from, valid_to, is_current; a customer changing address/segment closes the old row and opens a new one (dbt snapshots are the natural mechanism).
dim_date — a generated calendar dimension (day, week, month, quarter, weekday/weekend).
An example mart query answering "what did we know then": revenue by customer segment as of a past date, joining the fact to the dimension version valid at that time.

Tests

Generic: every primary key unique + not_null; every foreign key a relationships test to its dimension; an accepted_values test on order status.
Freshness: source freshness thresholds that fail when the generator hasn't delivered recently.
At least one singular test — a business rule written as SQL returning offending rows, e.g. "no order's payments may total more than its order amount" or "every current SCD2 customer has exactly one is_current row".
Prove the net works: a documented "chaos" script corrupts the raw layer (duplicate keys, null ids, orphan orders) and the build must fail loudly on each corruption.

Ingestion (replacing the seeds)

Incremental extraction from the source by cursor (updated-at watermark), landing into a raw schema with load metadata (_loaded_at, batch id).
Idempotent: keyed merge/upsert, never blind append — re-running any batch, or overlapping windows, produces zero duplicates.
Late data: events arriving with old timestamps land correctly and downstream incremental models pick them up (a lookback window on the incremental materialization).

Orchestration & backfills

One DAG: generator/ingestion → dbt build (staging → marts, tests inline), with retries and a daily schedule.
Backfill semantics: runs are parameterized by logical date/partition; a single CLI command backfills a named 3-day gap, re-running ingestion and transforms for just those partitions, idempotently.

CI

On every pull request, a pipeline that: installs pinned dependencies, dbt deps + dbt parse/compile, then builds the whole project into a scratch (throwaway) schema/database with all tests against a small fixture dataset. Red check blocks merge. Add a lint step (sqlfluff) if time allows.

Constraints

Laptop-only and free: no cloud warehouse, no paid orchestrator (cloud is the stretch goal in the exercise note).
Raw data is immutable once landed — transforms never UPDATE the raw layer.
Every artifact (models, tests, DAG, generator, chaos script, CI config) is in git; the README's bootstrap section is the only setup path anyone needs.

Acceptance

Mapped to the exercise's milestones:

Warehouse up — fresh clone + bootstrap: dbt build is green; a mart query returns rows from a DuckDB CLI.
Layers & docs — no mart references a source directly (staging-only refs); dbt docs generate renders full lineage; every mart column carries a description.
Tests — full generic + singular suite green on clean data; each chaos-script corruption makes dbt build fail with the failing test named.
Star schema — fct_order_items grain verified (row count = total line items, unique surrogate key); changing a customer's segment in the source and re-running yields two versions with contiguous validity ranges and one is_current; the "as-of" query returns different segments for different as-of dates.
Ingestion — run the load twice back-to-back: row counts identical, zero duplicates. Inject a late event (timestamp three days old): after the next run it appears in the correct partitions downstream.
Orchestration — the DAG runs green on schedule; one command backfills a 3-day hole, and re-running that same backfill changes nothing (idempotent).
CI — open a PR that removes a not_null constraint's underlying guarantee (or breaks a model): the check goes red; revert, it goes green — screenshot or link the runs.

End-to-end ELT pipeline — the exercise note this is the subject of.
jaffle_shop (dbt-labs) — the seed project the dataset framing starts from.

End-to-end ELT pipeline — subject