Four Data Pipeline Patterns and When Each One Breaks Down

Data pipeline patterns break down when what they rely on is no longer true. For batch ETL, ELT, streaming, and Lambda, the useful comparison is not which is best, but which condition makes each unsafe or too costly. Choosing well means identifying that condition before the architecture depends on it, then watching for evidence that it has changed.

Why pipeline pattern choice is an architectural bet, not a configuration decision

A pipeline’s structure determines where transformations happen, what can be recovered, and how much work correctness requires. Those decisions trade latency for simplicity, flexibility for cost, or throughput for correctness guarantees. Once other systems depend on the result, changing the pattern means changing load-bearing infrastructure.

A tool might fail, or the pipeline might keep operating after a premise behind its design becomes false. A faster refresh schedule cannot recover raw data that was never preserved, just as more compute cannot make two conflicting metric definitions agree. The architectural bet is that the workload will keep fitting the pattern’s constraints.

How the four patterns are actually structured and what each one optimizes for

Batch ETL extracts data, transforms it, and then loads the result. It prepares clean data before landing and processes work in scheduled batches, with freshness measured in minutes-to-hours. ELT reverses the last two steps, loading raw data before transforming it in the warehouse. That ordering preserves a raw layer against which transformation logic can run again.

Streaming processes events as they arrive, with latency measured in seconds or milliseconds. It serves workloads where a business outcome depends on real-time action, including fraud detection and live inventory. Stateful processing lets the system carry information from one event to the next for calculations such as running aggregations.

Lambda runs batch and streaming paths in parallel, then merges their results in a serving layer. Its structure combines a batch layer for accuracy with a speed layer for fresh results. Kappa is a related option that removes the separate batch path and replays past events through the same stream-processing logic. These structures occupy different positions in the trade-off among latency, processing complexity, and cost.

Where batch ETL breaks: the transformation-before-load assumption and what violates it

Batch ETL needs the transformation logic to be right before the original input is lost. In the arrangement at issue, raw data passes through source-side staging, is transformed, and lands only as processed output. If the load has already overwritten that staging and no raw layer was preserved, the transformed result is the only retained copy. If a bug turns up after that, running the fixed logic against the destination is not enough to fix it.

Recovery means extracting the data from the operational system again. But that system may have changed, been snapshotted differently, or become unavailable. A business-rule change can also cause failure. If the new rule needs information that the earlier transformation threw away, the stored output cannot provide it.

Batch ETL has a separate limit when the business begins demanding sub-minute freshness. The warning sign is a schedule compressed from daily to hourly to 15-minute batches, with each step adding fragility without removing the wait between runs. Hourly windows are too slow for fraud detection, live inventory, and customer-facing dashboards that need fresh data. At that point, the schedule is being asked to solve a structural latency problem.

Where ELT breaks: the re-run cost assumption and the idle-compute trap

ELT preserves the input needed to fix a faulty model because loading comes before transformation. That is a meaningful safety advantage over batch ETL without a preserved raw layer.

The hidden assumption becomes visible when a bug is discovered months after ingestion. Correcting it may require running dbt models across the full history of a large warehouse. Compute costs scale with the amount of data processed. On warehouse pricing models that charge for compute activation, the ability to start work as needed does not make repeated historical processing cheap.

Schema drift creates another route to failure. Raw ingestion can keep going when an upstream column is renamed or dropped without warning, breaking the dbt models that depend on it. ELT has preserved the data but not the agreement about its shape. The break may surface late if downstream models are not schema-tolerant and automated alerting is absent.

Growing dbt run times month-over-month are therefore a warning about both today’s schedule and the cost of future corrections. For workloads growing into the hundreds of terabytes, a columnar OLAP engine that separates compute from storage can change that cost structure. Keeping hot transformation intermediaries in a cache layer means they do not have to be read from object storage on every run. Where historical re-runs exceed the budget, a cache layer or incremental model strategy addresses the assumption that failed.

Where streaming breaks: schema drift and state management as the conditions that flip it from fast to fragile

Streaming removes the scheduled wait, but it makes correctness an ongoing obligation. Without a schema registry enforcing compatibility, a producer can rename a field, add a required column, or change a data type in ways that downstream consumers cannot handle. Events may appear to be flowing while data is dropped or misinterpreted.

Stateful processing introduces a different dependency. Sessionization, running aggregations, and windowed joins require the engine to retain information across events. When a node restarts, that state must be recovered correctly or the aggregate is wrong. Exactly-once semantics help with this, but they are hard to get right.

Apache Flink pairs with Kafka for stateful processing that supports exactly-once semantics and complex event-time windowing. Kafka 4.0 removed ZooKeeper and introduced KRaft as its sole metadata system, while the opt-in KIP-848 protocol greatly reduced stop-the-world consumer rebalances. These infrastructure advances matter, but they do not establish schema compatibility or prove that streaming is needed. Those remain architectural responsibilities.

The more counterintuitive failure is building a sound streaming system for a workload that never needed one. An infrequently refreshed dashboard receives no business benefit from data arriving between the moments when consumers query it. If no action depends on those intervening events, well-scheduled batch jobs could serve the same outcome with less operational overhead. The relevant test is the time available to act.

Where Lambda breaks: two codebases, two failure surfaces, and the reconciliation correctness trap

Lambda seems to offer both speed and accuracy without having to choose between them. The cost is that two codebases must independently implement the same business logic. A null-handling difference, an edge case in a calculation, or a timezone assumption can make their outputs disagree silently.

A metric-definition change makes the risk concrete. If the batch code receives the new calculation while the speed layer retains the old one, a dashboard metric can read differently depending on which layer supplies it. The serving layer must then reconcile a difference in meaning between results that persists as fresh data arrives.

Reconciliation thus becomes a third place where correctness bugs can arise. The phrase “the batch will catch up” is a warning when it explains away recurring discrepancies. The failure condition is the inability to keep both implementations and their reconciliation aligned as rules change.

Kappa reduces that burden by replaying history through one processing codebase, giving current and historical work consistent semantics. But it needs reliable stream storage that keeps data for a long time, such as Kafka with long retention windows. It may also be less efficient for the complex batch analytics that justified Lambda’s batch layer. A well-scheduled micro-batch ELT is another alternative where the actual latency requirement allows it.

The real-time OLAP layer that sits beneath pattern choice: what runs the queries when the pipeline delivers the data

Freshness and query latency are independent problems. A streaming pipeline can deliver data in milliseconds while a serving layer takes seconds to answer a query. If each query scans object storage, that read cost stays the same even when ingestion is faster. The pipeline has completed its job, but the user still waits.

Traditional cloud data warehouses handle batch BI well, but they were not architected for sub-second, high-concurrency workloads. Teams that need both streaming ingest and fast query serving often need a separate real-time OLAP layer built for that work. Its hot data belongs in memory or a fast cache tier to avoid a cold read from S3 on demand. Otherwise, repeated analytical queries make both cost and latency harder to control.

The engine still needs to fit the workload. Apache Pinot is engineered for user-facing analytics with strict sub-second SLAs over massive real-time event streams. Apache Druid is optimized for operational time-series analytics and rapid aggregation of event-driven data. Neither description makes the engines interchangeable, because user-facing query serving and operational aggregation place different demands on the serving layer.

Schema design also differs from OLTP. One Big Table makes sense in cloud columnar warehouses where storage is cheap and joins on huge tables are expensive, because the engine reads only the columns it needs. Apache Iceberg adds ACID transactions, time travel, and schema evolution to Parquet files in object storage. Schema changes do not require rewriting the data, although row-level updates may rewrite files depending on the write strategy. Those storage capabilities complement serving choices, which remain necessary.

How agentic workloads stress-test every pattern assumption simultaneously

AI agents apply pressure to these assumptions more frequently than human analysts do. An analyst may run a query and wait, while an agent issues queries in parallel, explores schemas, and probes the same table dozens of times within a task. Per-query pricing turns that exploration into a compounding cost. Repeated access also exposes stale data and inconsistent results more often.

Staleness becomes especially consequential when a query leads directly to action. A human analyst may know that inventory reflects last night’s batch, while an agent acting on it may make a decision that fresher data would have prevented. A silently dropped or renamed field creates a related risk. An agent’s SQL may return wrong results rather than an error, without the agent recognizing a need to check.

Anthropic introduced the Model Context Protocol (MCP) in late 2024 as a mechanism for connecting AI systems with external tools and data sources. Its broad adoption makes the database interface both an agent-facing API and an internal analyst tool. That shift raises the stakes of the same pipeline weaknesses, including costly re-runs, streaming schema drift, and Lambda reconciliation gaps. Agents do not remove those weaknesses by querying through a standard interface.

Autonomous access therefore needs explicit accountability. Agents need isolated, read-only compute over shared data, with every query logged in full. Sandboxed branches let them try things out with real data without touching production. These controls address access and auditability, while the pipeline and serving layers remain responsible for freshness, correctness, and query cost.

A decision framework for choosing the right pattern and recognizing when to switch

Start by asking how low the latency needs to be for the business outcome. If action can wait minutes-to-hours, batch ETL or ELT fits, and streaming adds overhead without a return. If action must happen in sub-seconds, the architecture needs streaming to deliver data in time and a serving layer that can answer quickly. A shrinking batch window without a latency SLA, or an infrequently refreshed dashboard fed by streaming, signals a mismatch.

Next, ask what happens when transformation logic is wrong. Check whether the original input is still there and whether the compute budget can cover a full historical re-run. ELT is safer when replay is affordable, while unbounded re-run costs point toward caching or incremental models. Warning signs include dbt runs that take longer and source-schema changes that break downstream models without alerts.

Then ask how much operational complexity the team can sustain. For streaming, that includes schema compatibility, state recovery, and event throughput. For Lambda, it includes changing two implementations together and explaining every discrepancy without assuming that time will resolve it. If the workload does not need both paths, micro-batch ELT or Kappa with long-retention storage may fit better.

Last, ask if AI agents will query the workload. If they will, test the choice against parallel exploration, repeated reads, and full query logging before traffic scales. Under that load, per-query pricing and S3-routed reads can make the system too costly or slow. Use these answers to set the conditions for switching patterns before those conditions become incidents.