A data pipeline moves data from a system that produces it to a system that consumes it, transforming it along the way. The definition is trivial; the engineering is not. What makes pipelines fail in production is rarely the transformation itself - it is the implicit assumptions around ordering, timing, retries, and re-runs.
Batch versus streaming
A batch pipeline reads a bounded slice of data on a schedule - “everything that changed yesterday”, “the full table, every hour”. A streaming pipeline processes records continuously as they are produced, typically through a log like Kafka.
| Property | Batch | Streaming |
|---|---|---|
| Latency | Minutes to days | Seconds to minutes |
| Re-processing | Natural - re-run the slice | Harder - replay the log or re-run a batch twin |
| Ordering | Per-slice, usually irrelevant | Per-partition; global order is not guaranteed |
| Failure blast radius | One run fails; next run catches up | Lag accumulates in the log until consumers recover |
| Cost profile | Pay for compute when it runs | Cluster and consumers run continuously |
The honest framing is not “streaming is newer and better”. Streaming pays for low latency with operational complexity that never goes away: consumer lag monitoring, schema evolution in flight, and the need to reason about event time versus processing time. If the business question is “what happened yesterday”, a batch job that runs at 06:00 is cheaper to build, cheaper to run, and far easier to re-run when it breaks. Choose streaming when consumers actually act on data within minutes - fraud checks, live personalisation, alerting - not because the architecture diagram looks more modern.
ETL versus ELT
ETL transforms data in flight and loads clean results. ELT loads raw data first and transforms inside the destination, which is the default pattern for cloud warehouses because their compute is cheap and separable from storage.
- ETL still makes sense when the destination cannot transform well (loading into an operational database), when data must be masked or filtered before it may land anywhere (regulatory constraints), or when the raw volume is too large to store twice.
- ELT keeps the raw copy, which is the property that matters: when the transformation is wrong, you fix the transformation and re-run it - the source data is still there. In ETL, a bug in the transform layer can destroy information before anyone notices.
The trade-off ELT hides: raw data is a liability as well as an asset. It contains everything the source contained, including personal data you may not be licensed to store. Loading raw first and transforming second does not remove the governance question; it just moves it.
The properties that decide whether a pipeline survives production
Idempotent writes. A pipeline that runs twice for the same input must produce the same
final state - inserts keyed on a natural key, INSERT ... ON CONFLICT or MERGE upserts, or
overwrite-by-partition where each run replaces its own output slice. Without this, every
retry creates duplicates, and every duplicate is a data-quality incident.
Re-runnable for an arbitrary date (backfill). You will re-run the pipeline for last Tuesday - when it breaks, when the source data arrives late, when the transformation logic is fixed. If the job only understands “yesterday” it cannot backfill, and fixing a bad week becomes manual SQL. Parameterise the date window from day one.
Explicit late-data handling. Sources lie about time. Events arrive after their slice was processed; upstream systems restate history. Decide whether a re-run replaces the slice (idempotent overwrite) or appends (in which case consumers must deduplicate), and write it down - “the pipeline” does not answer this, the contract does.
Bounded blast radius. A pipeline should validate input before writing output. A job that truncates the target table and then discovers the source extract is empty has just turned a source outage into a destination outage.
Failure modes and diagnostics
- Silent partial loads. The run “succeeds” with half the rows because the source paged badly or filtered wrongly. Diagnose with row-count and checksum comparisons per slice, not job status - green does not mean complete.
- Duplicate accumulation. Non-idempotent writes plus retries. Shows up as slowly inflating aggregates; find it by counting rows grouped by natural key.
- Schema drift at the source. A renamed column or changed type upstream breaks the
extraction silently if the job selects
*or tolerates missing fields. Detect with explicit schemas and contract tests, not by waiting for the transform to error. - Ordering violations in streaming. Events for the same entity processed out of order because the partitioning key changed or a retry re-queued an old record. Key partitions by entity id and treat ordering as a property of the key, not the topic.
- Backfill stampedes. Re-running six months of history against a source that rate-limits or a destination that locks. Backfills need their own throttling and, usually, a separate schedule from the daily run.
What this is not for
A pipeline moves and reshapes data; it is not a home for business logic that needs unit tests, versioning, and instant rollback - that logic belongs in the transformation layer or the application, where it can be reviewed and released independently. Nor is a pipeline an integration API: if a downstream system needs data in seconds with a defined SLA, that is a service endpoint, not a scheduled job.