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 the Supabase plugin (DB/Edge Functions/RLS).

When this fires

Any job that moves, transforms, or aggregates data: ingestion/ETL/ELT, scheduled aggregations, edge-function workers, pg_cron jobs, queue consumers, webhook processors, backfills, materialized-view refreshes.

Non-negotiables (the 5 that prevent silent corruption) [LOW freedom — run exactly]

1. Idempotency — running the same job twice must not change the result. Retries, at-least-once queues, and overlapping cron fires are guaranteed, not hypothetical.

• Use INSERT ... ON CONFLICT (natural_key) DO UPDATE (upsert), not blind INSERT.
• Derive a deterministic dedup key from the source event, not now() or a random id.
• For aggregates: recompute-and-replace a window, or use idempotent deltas — never count = count + 1 on a path that can retry.