Building Data Pipelines That Don't Break at 3 AM

Data pipelines that run fine in development have a habit of breaking in production — at 3 AM, when no one's watching. The difference between a pipeline that's reliable and one that isn't comes down to a few architectural and operational choices.

Here's what we've learned building and rescuing data pipelines for enterprises.

Idempotency and exactly-once semantics

Pipelines will be retried. Jobs will run twice. If your pipeline isn't idempotent, you'll get duplicate data, double-counted metrics, and corrupted state. Design every stage so that re-running it with the same inputs produces the same outputs and leaves the system in the same state.

Use deterministic keys (e.g. event_id, or composite of natural keys) for deduplication. Write in upsert mode where possible. For batch loads, use partition overwrites or merge operations that are safe to repeat.

Orchestration that handles failure

Cron is not orchestration. You need a system that understands dependencies, retries with backoff, and can skip or rerun downstream when upstream fails. Tools like Airflow, Dagster, or Prefect give you DAGs, retry policies, and observability.

Define clear boundaries for retry: transient failures (network, throttling) should retry; logic errors should fail fast and alert. Never retry indefinitely without human visibility.

Observability and alerting

If you don't know a pipeline failed until a user reports wrong numbers, you've already lost. Instrument pipeline runs: record start, end, row counts, and key metrics. Surface them in a dashboard and set alerts on failure, latency, and data freshness.

• Alert on job failure and on SLA breach (e.g. data not updated within N hours).
• Track data quality checks: null rates, value distributions, row count deltas. Anomalies often indicate upstream or logic issues.
• Log enough context to debug: job run ID, input partitions, and error details. Correlate with orchestration and source system logs.

Backpressure and backfill

Pipelines that pull large volumes can overwhelm sources or downstream systems. Use bounded parallelism, chunking, and rate limiting. Design backfills to run in chunks so you can resume from the last successful chunk instead of restarting from scratch.

Document and test backfill procedures. When something goes wrong, you'll need to backfill; having a runbook prevents panic and mistakes.

Environment and testing

Pipeline code should be tested like application code. Unit test transformations with fixed inputs and expected outputs. Use a staging environment that mirrors production schema and (anonymized) data shape. Run critical pipelines in staging on a schedule before promoting to production.

Treat pipeline changes as deployments: version them, review them, and have a rollback plan. Data pipelines are production systems.