Data pipelines that survive production: idempotency, retries, and the boring stuff

Data pipelines are the unglamorous plumbing of every data-driven product. They extract, transform, and load data from source systems into your data warehouse. And they break. Constantly.

APIs timeout. Schemas change. Networks flap. Your pipeline will encounter all of these in production. The question isn't whether it will fail, but how gracefully it recovers.

Idempotency: the foundation

Idempotency means running the same operation multiple times has the same effect as running it once. This is crucial for data pipelines because failures are inevitable and you'll need to retry.

Without idempotency, a failed pipeline that retries will create duplicate data. With idempotency, retries are safe.

We achieve idempotency through:

• Upserts instead of inserts (update if exists, insert if not)
• Partitioning by date (overwrite today's partition, don't append)
• Idempotency keys for API calls (pass a unique ID, API returns same result for same ID)
• Checkpointing (record progress, resume from last checkpoint on retry)

If your pipeline isn't idempotent, you don't have a pipeline. You have a time bomb.

Retry strategies that work

Naive retries (immediate retry on failure) amplify problems. If an API is down, hammering it with retries makes it worse.

We use exponential backoff with jitter:

• First retry: wait 1 second
• Second retry: wait 2 seconds
• Third retry: wait 4 seconds
• Add random jitter (±20%) to avoid thundering herd
• Max retries: 5, then alert and escalate

We also classify errors:

• Transient errors (network timeout, 503): retry with backoff
• Permanent errors (400 bad request, 404 not found): don't retry, log and alert
• Rate limit errors (429): respect Retry-After header, exponential backoff
• Unknown errors: retry once, then escalate

Schema evolution

Source systems change. APIs add fields, remove fields, change data types. Your pipeline will break unless it handles schema evolution gracefully.

We use schema registries and versioned schemas:

• Store schema versions in a registry (Avro, Protobuf, or JSON Schema)
• Tag each data record with its schema version
• Transform data to the latest schema on read, not on write
• Maintain backward compatibility (new fields are optional, old fields aren't removed)

When a source API changes, we don't break the pipeline. We add a new schema version and a transformation layer. Old data continues to work.

Observability: knowing when things break

Silent failures are the worst failures. Your pipeline needs to tell you when something's wrong.

We instrument every pipeline with:

• Success rate: percentage of successful runs (alert if < 95%)
• Latency: time from start to finish (alert if 2x baseline)
• Data volume: rows processed (alert if 50% deviation from expected)
• Data quality: null rates, schema violations, business rule failures
• Freshness: time since last successful run (alert if stale)

We also build dead-letter queues: records that fail validation go to a separate table for manual review. This prevents one bad record from breaking the entire pipeline.

Testing pipelines

Data pipelines are hard to test because they depend on external systems. We use:

• Unit tests: test transformation logic with mock data
• Integration tests: test against a staging environment with real (but sanitized) data
• Contract tests: verify API responses match expected schemas
• Chaos tests: inject failures (API timeouts, malformed data) and verify recovery
• Regression tests: replay historical data and verify outputs match expected

The boring stuff that matters

Production data pipelines are mostly boring engineering:

• Logging: structured logs with correlation IDs (trace a record through the pipeline)
• Metrics: Prometheus/Grafana dashboards showing pipeline health
• Alerting: PagerDuty alerts for critical failures, Slack for warnings
• Documentation: runbooks for common failures and recovery procedures
• On-call rotation: someone gets paged when the pipeline breaks at 3am

None of this is exciting. But it's the difference between a pipeline that runs for years and one that needs constant babysitting.

The reality

Data pipelines are 80% error handling and 20% happy path. The exciting part is the transformation logic. The hard part is making it survive production.

Build for failure. Assume everything will break. And make sure your pipeline recovers gracefully when it does.