Data Pipeline

Data Pipeline

A pipeline is a promise about rows: this many, this fresh, at this grain, keyed like this. Write the promise down first, then make the job keep it even when it runs twice.

Standing reference: references/data-quality-checklist.md.

Process

1. Write the contract before the job. Source, grain ("one row per order per status change"), primary key, expected volume per run, freshness target, and what a null means in each nullable column. A pipeline with no declared grain accumulates duplicates that nobody notices for a quarter, and by then three dashboards depend on the inflated number.

2. Make the write idempotent. Re-running the same input must produce the same output. Two shapes work: merge on the key, or delete-and-insert a bounded partition. Append into a table with no dedupe key is a duplicate factory; the first retry proves it.

3. Decide the lateness rule out loud. What is the watermark, how late may a record arrive and still be processed, and what happens to one that arrives later. Dropping late records is a legitimate choice. Dropping them without writing that down is a defect that surfaces during someone else's incident.

4. Handle duplicates at the source of truth, not downstream. Deduplicating in every consuming query means every future consumer must remember to do it, and one will not.

5. Build incremental, prove it against a full recompute. Run the incremental logic over one historical partition and compare row counts and one summed measure against the complete rebuild of that partition. Equality is the evidence. Without it, the incremental path is a guess that will diverge slowly.

6. Test at the boundaries. Freshness, volume against a trailing band, uniqueness on the key, referential integrity on join keys, nullability on required columns. Tests belong where data enters your ownership and where it leaves it, not on every intermediate step.

7. Alert on the data, not the task. A DAG that exits zero while writing zero rows is the failure that costs a week, and a task-failure alert never fires for it. Alert on max timestamp and on row count.

8. Report cost. Bytes scanned, slot or warehouse seconds, storage delta. Correct and over budget is not done.

Backfills

A backfill is a production change to historical data. Treat it like a deployment.

• Bound it: one partition first, then a range, never the whole table in one statement.
• Write it so it can be stopped and resumed. A backfill that must complete or be restarted from zero will be restarted from zero, at the worst time.
• Compare the backfilled partition against the old values before overwriting the rest, and keep the comparison.
• Never delete raw history to fix a derived error. Fix forward in the derived layer; raw is the audit trail.

Schema changes

• Additive first: add the new column, populate it, migrate readers, then remove the old one. A rename in one step breaks every reader between deploy and deploy.
• State what happens to readers during the change, including the ones you do not own.
• A destructive change (drop, type narrowing, retention reduction) is irreversible. It escalates; it does not get scheduled for a Friday.

Output

## Pipeline: <name>

- **Contract:** grain <one row per X> · key <cols> · freshness <target> · volume <expected>
- **Idempotency:** <merge | delete-insert> — ran twice, row counts <n> and <n>
- **Late data:** watermark <n>, outside it <dropped | reprocessed>, counted as <metric>
- **Incremental proof:** partition <p>, incremental <n> rows / <sum>, full recompute <n> / <sum>
- **Tests:** <checks, where they run, what they page>
- **Cost:** <bytes scanned / slot seconds / storage delta>
- **Rollback:** <how to undo, and what is lost>

Verification

• The job ran twice on identical input; both row counts are quoted and equal.
• The incremental partition matches the full recompute; both numbers quoted.
• Quality tests were run against deliberately corrupted input and failed as designed. A test never seen red proves nothing here either.
• The freshness alert was triggered by holding data back, not assumed to work.
• Cost is an absolute number.

Red flags