data-pipeline

# Data Pipeline Correctness **Degree of freedom: MIXED.** Layering and window design `[HIGH freedom]`; idempotency, atomic writes, overlap lock, and the DoD `[LOW freedom — run exactly]`. ## How to reason 1. **Observe** — job, retry path, write targets, schedule 2. **Interpret** — at-least-once vs atomic vs contract vs overlap 3. **Classify** — upsert / window-recompute / quarantine / lock / watermark 4. **Severity** — retry double-count outranks a missing metric ## Worked example > **Observe:** nightly `refresh_order_stats` does `count = count + 1`; cron overlapped twice; dashboard totals jumped. > **Interpret:** at-least-once delivery + non-idempotent delta. > **Classify:** recompute-and-replace the day window; `pg_try_advisory_lock`; persist a watermark. > **Verify:** re-run the same window → identical rows; overlap skipped; `pipeline_runs` recorded. ## Self-critique before reporting - **Idempotent** — same-window re-run proven identical, not assumed - **Atomic** — mid-fail leaves no half-written batch - **Locked** — the scheduled job has an overlap guard - **Right owner** — schema/constraints → `audit-db-schema`; post-hoc corruption hunt → `plan-data-integrity` > Pipelines fail silently: a retry double-counts, a partial write corrupts a table, a schema drift poisons a dashboard, and nobody notices until the numbers are wrong. This skill bakes correctness in at build time. It complements post-hoc data-integrity audit skills (which *detect* these after the fact) and...