Choosing an ingestion pattern
| Dimension | Batch | Change data capture | Streaming |
|---|---|---|---|
| Freshness | Minutes to hours, on a schedule. | Minutes, following source commits. | Seconds or continuous. |
| Best fit | Daily reporting, reconciliation and large extracts. | Operational sources where updates must not be missed. | Event-driven decisions, alerting or real-time user experiences. |
| Complexity | Low: repeatable jobs and clear windows. | Moderate: log reading, ordering and initial snapshots. | High: replay, event time, ordering and operational monitoring. |
| Typical failure mode | Window overruns and partial extracts. | Missed or duplicated changes after reconnects. | Late events and consumer lag. |
| Operational needs | Schedules, retries and reconciliation counts. | Offset tracking, snapshot strategy and idempotent merges. | Consumer monitoring, back-pressure and replay tooling. |
The four stages every pipeline shares
Whatever the tooling, a pipeline ingests from a source, validates what arrived, transforms it into something usable, and delivers it to a consumer with a freshness expectation attached. Naming those four stages makes ownership and acceptance criteria possible; skipping straight to tooling usually means nobody can say what the pipeline promises.
Batch, streaming and change data capture
Batch moves data on a schedule, which is simple to operate and appropriate when the decision is daily; choosing between the three patterns is the first architecture decision here. Streaming moves events as they occur and brings ordering, replay and operational complexity. Change data capture reads a source’s change log so updates arrive without full extracts. Each is a different answer to the same freshness question.
- Batch: scheduled, simple, cheap to reason about
- Streaming: event-time handling, replay and ordering designed up front
- CDC: incremental updates without repeatedly scanning the source
What production adds
A pipeline is in production when it is observable, replayable and owned. That means freshness and volume are monitored, failures raise an alert with a named responder, a failed run can be replayed without duplicating records, and schema changes are handled deliberately instead of breaking downstream models silently.
Idempotency is the property that makes replay safe: running the same load twice produces the same result. In practice that means writing with a merge on a stable key, or overwriting a whole partition such as one day, instead of appending rows every time the job runs.
Quality gates belong in the pipeline
Completeness, uniqueness, validity and reconciliation checks should run as part of the load rather than as an afterthought. Invalid records are quarantined with a reason and a review path, not dropped. The tests are the acceptance criteria for the data product, and they should be visible to the people who consume it.
Typical gates are row counts reconciled to the source, a uniqueness check on the business key, non-null checks on required fields, accepted values for status codes, and a freshness check that fails when the newest record is older than the agreed window.
Failure modes worth designing for
Late arrivals, duplicate events, partial writes, source schema changes, clock and time-zone differences, and upstream systems that quietly stop producing are all normal. Each needs a decision recorded before it happens in production: what the pipeline does, who is told, and how the data is corrected afterwards.
Illustrative example · hypothetical organisation
Worked example: an orders pipeline, end to end
Take a hypothetical order-to-cash pipeline feeding a daily sales dashboard.
Ingest reads new and changed orders from the ERP each night through change data capture and lands them unchanged. Validate checks that every order has a customer and a currency, that order IDs are unique and that the daily count sits within the expected range; failures go to a quarantine table with the reason attached.
Transform converts currencies at the daily rate and builds an order-line fact at one row per line. Deliver refreshes the dashboard dataset and shows the time of the last successful load beside the numbers.
The pipeline has one owner, an alert routed to that owner when the load misses 06:00, and a written procedure for re-running a day without duplicating orders.
Where people commonly go wrong
Two mistakes dominate. Treating the pipeline as finished when the first run succeeds, and encoding business logic in a place nobody can review. The first produces a fragile system with no operator; the second produces two definitions of the same measure. Both are avoided by deciding ownership and tests before the pipeline is built.
Questions and trade-offs
Question
Do we need streaming for everything?
No. Streaming earns its place only where the decision loses value at batch latency. For most reporting, a well-operated batch or CDC pipeline that reliably lands within its window is cheaper to build, easier to monitor and simpler to recover than an event pipeline built for a decision that runs daily.
Question
How do we handle late or duplicated data?
Design for it before production: keep enough source history to replay, make loads idempotent so a repeated run does not duplicate records, and decide the correction window with the data owner. Then test the correction path deliberately, because it will be used.
Question
What is the difference between a data pipeline and ETL?
ETL is one pattern a pipeline can follow: extract, transform, then load. A data pipeline is the broader term for any automated path that moves and prepares data, including ELT, streaming and change data capture. The production requirements are the same whichever pattern is used.
