The most important data pipeline best practices come down to one goal: any run can fail, be retried, or be replayed for a past date without producing wrong or duplicated data. That means idempotent writes, partitioned outputs, deliberate retries, controlled schema evolution, and enough observability to know when something is late or wrong. This guide walks through each practice with concrete patterns you can apply to batch and streaming pipelines alike.
Why data pipelines break
Pipelines fail in predictable ways. A source API times out. A job is killed halfway through a write. Someone reruns yesterday's job to fix a bug and doubles the revenue numbers. An upstream team renames a column and every downstream model silently fills with nulls. A job keeps succeeding, but it has been processing an empty file for a week.
None of these are exotic. A reliable pipeline assumes they will happen and is designed so that recovery is routine: rerun the affected interval and move on.
1. Make every pipeline step idempotent
An idempotent step produces the same final state whether it runs once or many times for the same input. It is the foundation for everything else in this list, because retries, restarts, and backfills all rerun work. The general idea is covered in the idempotency guide; in data pipelines it usually looks like this:
- Scope each run to a fixed interval, such as one day or one hour, passed in as a parameter.
- Write results to a location derived from that interval.
- Replace that location atomically instead of appending to it.
Avoid blind appends
The classic bug is INSERT INTO target SELECT ... FROM source WHERE date = :run_date with no cleanup. The first run is correct; a retry doubles the rows. Replace the partition instead, either with delete-then-insert in one transaction, a MERGE on a natural key, or a partition overwrite in your table format.
-- Idempotent daily load: rerunning for the same date replaces, never duplicates.
BEGIN;
DELETE FROM analytics.daily_orders
WHERE order_date = DATE '2026-09-12';
INSERT INTO analytics.daily_orders (order_date, country, order_count, revenue)
SELECT
CAST(created_at AS DATE) AS order_date,
country,
COUNT(*) AS order_count,
SUM(amount) AS revenue
FROM raw.orders
WHERE created_at >= TIMESTAMP '2026-09-12 00:00:00'
AND created_at < TIMESTAMP '2026-09-13 00:00:00'
GROUP BY CAST(created_at AS DATE), country;
COMMIT;
In a real pipeline the date comes from the orchestrator as a parameter rather than being hard-coded. Note the half-open interval (>= start, < end), which avoids double-counting rows exactly at midnight.
2. Use partitioned writes
Partitioning output by date, or another natural unit, makes idempotency cheap. Each run owns exactly one partition, so rerunning it touches nothing else. Partitions also make queries faster through partition pruning, make retention easy (drop old partitions), and make backfills parallelizable. Table formats such as Apache Iceberg, Delta Lake, and Apache Hudi support atomic partition overwrites on object storage, which avoids readers seeing half-written data. The same approach applies to each layer of a data lake, from raw landing data to curated tables.
Choose partition granularity carefully. Too fine, such as per minute, creates huge numbers of small files; too coarse forces large rewrites for small corrections.
3. Design for backfills from day one
Backfills rerun a pipeline for historical intervals, for example after fixing a bug or adding a new column. They are safe only when steps are interval-scoped and idempotent. A few habits help:
- Never use the current time inside transformation logic; use the run's interval boundaries.
- Keep raw data immutable and retained long enough to reprocess from it.
- Limit backfill concurrency so it does not starve production runs or overload sources.
- Backfill downstream dependents too, in dependency order.
Orchestrators like Apache Airflow model runs as data intervals precisely so that backfills behave like normal runs.
4. Retry transient failures, not bad data
Retries fix transient problems: timeouts, throttling, a briefly unavailable warehouse. Use a limited number of attempts with exponential backoff and jitter so retries do not hammer a struggling system. Retries do not fix a malformed record or a logic error; those will fail every time. Route bad records to a quarantine table or dead-letter destination with the error attached, alert on it, and let the rest of the batch proceed if the business allows partial results.
| Failure type | Example | Right response |
|---|---|---|
| Transient | Network timeout, rate limit | Retry with backoff and jitter |
| Bad record | Unparseable row, invalid enum | Quarantine the record and alert |
| Upstream missing | Source file not yet delivered | Wait with a sensor or timeout, then alert |
| Logic bug | Wrong join produces duplicates | Fail loudly, fix, then backfill |
| Schema break | Column renamed upstream | Fail on contract check, coordinate the change |
5. Plan for schema evolution
Schemas change, and pipelines should notice rather than silently adapt in harmful ways.
- Validate incoming data against an expected schema at the boundary, and fail or quarantine on breaking changes.
- Prefer additive changes, such as new nullable columns, which downstream readers can ignore.
- Select explicit columns instead of
SELECT *in transformations, so new upstream columns do not change outputs unexpectedly. - Agree on data contracts with upstream producers for critical tables, including who approves breaking changes.
For streaming and change data capture sources, a schema registry with compatibility rules enforces this automatically. The CDC guide covers schema changes in that context.
6. Build observability into the pipeline
A job that succeeds is not the same as a job that produced correct data. Track signals at two levels:
- Pipeline health: run status, duration, retries, and queue or consumer lag.
- Data health: freshness (when the latest data arrived), volume (row counts versus a normal range), completeness (null rates in key columns), uniqueness of keys, and distribution shifts.
Emit metrics and structured logs with the run interval and dataset name so you can answer "which run produced this bad partition?" quickly. Automated checks such as dbt tests or dedicated data quality tools turn silent data bugs into alerts.
7. Define SLAs and ownership
Every important dataset should have an owner and an agreed expectation, for example "daily orders ready by 7:00 UTC with less than 0.1% null customer IDs". Measure against it: alert when freshness is at risk, not after the dashboard is already stale. Clear SLAs also help prioritize incidents. A late experimental table can wait; a late finance table cannot.
8. Control cost
Reliability and cost often align. Idempotent, partitioned pipelines let you reprocess one day instead of an entire table. Other cost practices:
- Process incrementally where possible instead of full rebuilds.
- Prune columns and partitions early so less data is scanned.
- Compact small files, which also speeds up reads.
- Right-size clusters and use autoscaling or auto-suspend for warehouses and compute.
- Tag jobs and datasets so spend can be attributed to owners.
Choosing between batch and streaming also affects cost: real-time processing is worth its extra complexity only when the business genuinely needs fresher data.
Data pipeline best practices checklist
| Practice | What good looks like |
|---|---|
| Idempotency | Rerunning any interval gives the same result |
| Partitioned writes | Each run replaces exactly one partition atomically |
| Backfills | Historical reruns use the same code path as daily runs |
| Retries | Bounded, with backoff; bad records are quarantined |
| Schema evolution | Changes are validated at the boundary and coordinated |
| Observability | Freshness, volume, and quality are monitored, not just job status |
| SLAs | Each critical dataset has an owner and a deadline |
| Cost | Incremental processing, pruning, and attributed spend |
Key takeaways
- Design every step to be safely rerunnable; idempotency makes retries and backfills routine.
- Scope runs to fixed intervals and overwrite matching partitions instead of appending.
- Retry transient errors with backoff, and quarantine bad records instead of retrying them.
- Validate schemas at the boundary and prefer additive changes.
- Monitor data freshness, volume, and quality alongside job success.
- Attach owners and SLAs to important datasets so incidents are prioritized correctly.
Frequently asked questions
What makes a data pipeline idempotent?
A pipeline is idempotent when running it multiple times for the same input produces the same output. In practice that means interval-scoped runs, deterministic output locations, and atomic overwrite or merge writes instead of appends. Any side effects, like sending notifications, need their own deduplication.
How do I backfill a data pipeline safely?
Make sure each step is idempotent and uses run parameters instead of the current time. Then run the affected intervals with limited concurrency, starting upstream and moving downstream. Validate a sample of backfilled partitions before running the full range.
How many times should a pipeline task retry?
There is no universal number, but a small bounded count with exponential backoff is typical for transient errors. Beyond that, fail and alert so a person can investigate. Endless retries hide real problems and can overload struggling upstream systems.
What should I monitor in a data pipeline?
Monitor job status and duration, but also data freshness, row volume, null rates in key columns, and uniqueness of primary keys. Those data-level checks catch the common case where a job succeeds but produces wrong or empty output. See data quality testing and monitoring for a deeper look.