W4 Lab Brief — Ingestion Build (Graded)

Outcome: state the W4 repo contract, the grading gates in order, and which milestone each deliverable satisfies — before writing any pipeline code.

Pull a paginated local fixture (orders + customers generated by src/ingest/fixture.py, the deterministic seeded generator in the Fixture lesson) to local Parquet, then load into warehouse staging tables. Incremental by updated_at watermark; re-runs must be idempotent.

Repo contract (Tier-1 checks these paths)

• src/ingest/fixture.py — deterministic fixture generator (seed-pinned)
• src/ingest/fetch.py — paginated fetch with retries + backoff (ms 275)
• data/parquet/orders/*.parquet, data/parquet/customers/*.parquet
• src/ingest/load.py — Parquet -> staging (upsert on primary key in ONE transaction; advance ingest_watermark only on success) (ms 276)
• findings/01_ingestion.md — row counts, watermark value, re-run proof
• findings/02_failure_drill.md — kill-mid-transaction drill log (ms 277)

Milestone map

• ms 274 Repo setup + fixture landing: DDL runs cleanly, fixture counts recorded (README.md, warehouse/ddl/staging.sql).
• ms 275 Paginated fetch with retries: every page lands as Parquet; a simulated 500 on page 2 still completes via retry (src/ingest/fetch.py).
• ms 276 Incremental load with watermark: idempotent re-run, one-row upsert on mutated fixture row (src/ingest/load.py, findings/01_ingestion.md).
• ms 277 README + failure drill: fresh clone reaches green load (README.md, findings/02_failure_drill.md).

Grading

Structural gate (files present, non-stub) first, semantic gate (SQL parses, staging counts reconcile) second, write-up quality third.