Skip to content
SP.
EN DE
Back to Work
Reference design

Data pipelines with a recovery path

Make ingestion replayable and data quality visible before downstream consumers are affected.

  • Python
  • PostgreSQL
  • dbt
  • Terraform
  • Grafana
Freshness target
< 1 hr
Raw retention target
30 days
Quality gates
3

Design targets · not production measurements

The problem

Retries are only useful when they do not duplicate records. This reference architecture separates ingestion from transformation so a failed run has a predictable recovery path. Metrics describe proposed targets, not production measurements.

The system

Source records land in immutable raw storage. Validated records move through PostgreSQL and dbt to data products. Invalid records go to quarantine.

Immutable raw batches provide the replay boundary. Validate schema, uniqueness, and freshness before publishing a new dataset. Quarantine records that fail validation instead of silently dropping them.

Idempotency is a contract

Use a stable source identifier and version as the deduplication key. Persist checkpoints only after the destination transaction commits. If the worker crashes between commit and checkpoint, a retry must produce the same result.

INSERT INTO events (source_id, source_version, payload)
VALUES ($1, $2, $3)
ON CONFLICT (source_id, source_version) DO NOTHING;

This requires a unique constraint on (source_id, source_version). It assumes source versions are immutable; mutable upstream records need an explicit update policy.

Operability

Expose batch age, rejected-row count, and last successful publish time. Alert on consumer-facing freshness rather than every retry. A useful alert links to the affected batch and a documented replay procedure.

Trade-offs

Raw retention increases storage cost and needs a deletion policy. Batch processing accepts bounded latency in exchange for simpler recovery. Introduce streaming only when the consumer’s latency requirement justifies its operational cost.

Back to Work