Real-Time Data Warehouse Architecture Patterns

The three mechanisms compared on their own terms. The pipeline hadn't crashed. It had been polling the source database every hour with a query that checked for rows updated since the last sync, and a query like that has no way to see a row that no longer exists. The deletes simply vanished between source and destination, and nobody noticed until a billing audit caught the discrepancy.

Lambda architecture splits processing into a batch layer that recomputes historical data and a speed layer that handles recent events, which keeps query results accurate but forces every transformation to be written and maintained twice. Kappa architecture eliminates the batch layer entirely, treating the event log as the single source of truth and reprocessing history by replaying it through the same streaming pipeline, which reduces maintenance burden but demands that the streaming system can sustain reprocessing loads at scale. CDC streaming architecture, as this piece argues throughout, sits upstream of both: it determines what events are available at all, and log-based CDC is the only mechanism that supplies full-fidelity inserts, updates, and deletes with strict ordering and low source overhead, making it the necessary foundation whichever downstream pattern a team chooses. Lakehouse streaming, built on open table formats like Apache Iceberg, Delta Lake, or Apache Hudi with a fast columnar tier alongside them, is the pattern that has consolidated by 2026 for teams that need durable ACID semantics, time travel, and schema evolution at the storage layer while still serving sub-second queries; it depends on CDC streaming to feed its Bronze layer, and teams should choose it when they need governed historical data alongside live analytics rather than optimizing purely for query latency or pipeline simplicity.

That failure is structural, and it points to the central argument of this piece: the mechanism a team chooses to capture changes from a database determines what every later architectural decision is even allowed to accomplish. Log-based change data capture (CDC) is the only approach that delivers low overhead on the source system, full fidelity on every operation including deletes, and strict ordering of events, all at once. Every claim this piece makes about delivery guarantees, schema evolution, and multi-source topology downstream assumes that foundation is already in place.

It helps to weigh the three ways teams actually capture changes, on their own terms. Query-based CDC, the timestamp-polling approach, is simple to wire up, but it cannot reliably detect deletes: a team polling with something like WHERE updated_at > last_sync will quietly accumulate phantom rows in the warehouse, rows that were deleted at the source but never flagged for removal downstream. Log-based CDC instead reads the write-ahead log after changes have already been committed, requires no schema modifications on the source, captures every operation including deletes, preserves the order transactions occurred in, and places minimal load on the source database. The reason it works this way is structural: every database already writes its changes to a transaction log before it ever touches the actual table, so that log is authoritative, and reading it means reading the database's ground truth rather than inferring it from a snapshot comparison.

None of this makes log-based CDC easy. It demands elevated permissions on the source database and careful, ongoing management of log retention, and it is considerably more involved to stand up than a polling query a junior engineer could write in an afternoon. That operational weight is why it belongs at the foundation of the architecture instead of being bolted on after the fact. Everything that follows in this piece, the delivery guarantees, the schema handling, the multi-source topologies, assumes that complexity has already been absorbed once, correctly, rather than reinvented for every new pipeline.

How the three database engines expose their transaction logs

Postgres, MongoDB, and DynamoDB each expose their transaction logs through different mechanisms, and those differences are not a footnote, they are constraints that shape every decision a pipeline designer makes afterward.

Postgres handles this through logical replication, which decodes the write-ahead log into row-level change events, insert, update, delete, and ships them through a publish-subscribe model. It operates at the row level, works across major Postgres versions, and lets a team replicate a subset of tables rather than the whole database. Retention is what catches teams off guard. A replication slot is effectively a promise: Postgres will hold onto WAL data until the consumer confirms it has received it, and if that consumer disconnects or falls behind, the WAL cannot be recycled and starts filling disk. A setting called max_slot_wal_keep_size can cap how much WAL a lagging slot is allowed to pin, but absent that safeguard, an unmonitored replication slot is the single most common way a logical replication setup ends up taking down the Postgres database it was supposed to be reading from quietly. On top of that, schema changes do not flow through the WAL at all. DDL has to be applied manually on both the publisher and subscriber, and the order matters: additive changes like ADD COLUMN go to the subscriber first, while destructive changes like DROP COLUMN go to the publisher first. Get that order backward and the replication breaks in ways that are hard to diagnose after the fact.

MongoDB's Change Streams work differently, tapping the oplog, MongoDB's internal replication journal, which adds close to zero overhead to the source. Since version 3.6, a global logical clock orders every change across a sharded cluster, so consumers always see changes arrive in the order they were actually applied, even when the data spans multiple shards. Change Streams only surface changes once they've been committed to a majority of replica set members, so durability holds even through a primary election. Change Streams require a replica set or sharded cluster to function, because a standalone MongoDB instance never maintains an oplog and cannot serve as a CDC source.

DynamoDB Streams capture a time-ordered sequence of item-level modifications, recording every change made to a data item once streaming is enabled. The hard constraint here is retention: the stream only holds events for a limited window, and a consumer that falls behind past that window loses the data permanently, with no way to recover it after the fact. Because DynamoDB is schemaless, there's no DDL event to even react to when the source structure changes. Adding a new attribute at the source means the relational target's structure has to be updated by hand to avoid silently breaking the replication downstream.

Retention windows, DDL behavior, and cluster prerequisites differ across every engine a pipeline might touch, and a design that ignores any one of those three dimensions will eventually hit a wall it didn't plan for.

What are the differences between Lambda, Kappa, CDC streaming, and lakehouse streaming patterns, and when should each one be used?

By 2026, the dominant shape of a real-time warehouse has settled into two tiers: an open table format layer that holds durable, governed data, and a fast columnar store that serves low-latency queries on top of it. CDC is what feeds the boundary between the operational databases and that first tier.

The open table format layer, built on Apache Iceberg, Delta Lake, or Apache Hudi, is where everything durable lives. It gives the warehouse ACID transaction semantics, time travel across historical states, and schema evolution support built directly into the storage layer itself. Sitting beside it is a fast columnar tier, typically ClickHouse or Druid, built to answer sub-second queries for product analytics and live dashboards where the open format's query latency is too high for the use case. In practice, the logical data warehouse in 2026 combines a cloud columnar warehouse, lakehouse tables in these open formats, and federated queries that reach directly into operational systems, all governed through one shared semantic and metadata layer.

The medallion framework gives this lakehouse an operational shape. Raw CDC events land first in a Bronze layer, preserving the full before and after state and the operation type exactly as the source emitted them, with nothing cleaned or reshaped yet. From there, a Silver layer deduplicates records, normalizes types, and aligns schemas across sources. Finally, a Gold layer aggregates that cleaned data into business-ready tables that power dashboards and machine learning feature stores.

What makes Bronze-layer ingestion tractable at all is that a standardized CDC event is self-describing: it carries the before state, the after state, the operation type (insert, update, or delete), and metadata that includes a timestamp, a transaction ID, and the source position in the log. A pipeline doesn't need external context to know what an event means, because the event already contains everything required to apply it correctly.

Streaming ingestion through message buses like Apache Kafka or Amazon Kinesis is what turns this architecture from a batch process into a live one, cutting data latency from hours down to seconds. Fraud detection is the clearest example: transaction patterns get analyzed as they happen rather than reviewed the next morning in a batch job, catching fraud in progress instead of writing an incident report about it afterward.

Delivery guarantees designed in from the start

Picture a pipeline running smoothly for weeks, until a single node fails mid-stream. Without the right design in place, the system recovers by replaying from an earlier offset, and every event between that offset and the failure point gets delivered twice: counts inflate, aggregations corrupt, and updates that should have applied once get applied again. This is the default behavior of a stream processor that was never built with failure in mind, and it appears during the first real incident a pipeline faces in production.

Checkpointing is the mechanism that prevents it. The system periodically saves its processing state to durable storage, HDFS or S3 being the common choices, and when a failure occurs, it restores from the latest checkpoint and replays only the events that weren't yet processed. Apache Flink provides exactly-once processing guarantees this way, managing state through embedded key-value stores with checkpoints written out to durable storage at regular intervals. The mechanics matter less than the posture behind them: a pipeline has to be built from day one assuming it will fail, rather than treated as reliable until an incident proves otherwise.

Dead-letter handling follows the same logic applied to bad data instead of node failures. A record that fails validation should route to a dead-letter table instead of getting dropped silently or halting the whole pipeline. That preserves every record the pipeline ever saw while keeping malformed data from reaching anyone downstream.

A handful of operational habits reduce how often any of this gets tested. Start with a small number of high-value tables rather than replicating an entire schema on day one, since a smaller surface recovers faster and debugs more easily when something does go wrong. Run CDC workloads against a read replica rather than the primary writer, so the capture process has zero impact on production traffic. Automate restarts so that a network blip resumes from the last checkpoint on its own, without someone having to restart a job by hand.

None of this is free, and not every pipeline needs the full version of it. A reporting table that's fine running five minutes behind doesn't need exactly-once semantics or sub-second latency, and building that apparatus anyway is wasted engineering effort. The guarantee should match the requirement, not exceed it by default. For teams that would rather not build and operate this fault-tolerance layer themselves, managed CDC platforms exist specifically to absorb that operational weight, which is often the more defensible choice than maintaining bespoke checkpointing logic for a handful of pipelines. Artie, a CDC-as-a-service platform that streams row-level changes from operational databases to cloud data warehouses with sub-minute latency and no Kafka infrastructure required, is one concrete example of that approach.

Schema evolution as the most common production failure mode

Ask anyone who has run a CDC pipeline in production long enough what actually brings it down: the answer is rarely the network or the infrastructure. It's an unannounced schema change on the source system that the pipeline's deserialization logic was never built to parse.

The failure is quiet by nature. A source system adds a field, changes a column's type, or drops a nested attribute somewhere in its structure; the pipeline then either throws a deserialization exception or, worse, silently loads malformed data without raising any error. Downstream consumers end up reading corrupted or incomplete records, often with no alert telling anyone it happened until a report looks wrong weeks later.

Three categories of schema change call for three different responses. Added columns should flow into the target automatically, or be explicitly ignored, but never cause a silent failure. Removed columns should trigger an alert rather than disappearing quietly, since dropping a column from the target without warning anyone corrupts every downstream join that depended on it. Type changes should get cast automatically where that's safe, and quarantined where it isn't, rather than coerced blindly into whatever type the pipeline expects.

Schema registries and formal contracts help enforce this discipline, but only when both the producer and the consumer actually commit to enforcing them, which rarely happens across organizational boundaries. Internal pipelines need a versioning policy regardless of whether a formal registry exists to back it up. The best-practice posture is to track schema changes in a data catalog, apply backward-compatible changes automatically, and treat anything breaking as a versioned migration rather than an ad hoc patch, which is what prevents silent schema drift from corrupting everything downstream of it.

This is where the Postgres constraint from earlier becomes directly relevant. Since DDL never travels through the WAL, schema changes have to be applied manually on both sides, subscriber first, and automatic schema evolution logic inside the pipeline itself is the only reliable backstop when that manual process gets missed or done out of order.

The medallion architecture gives this problem a natural home. A pipeline that handles schema evolution correctly treats the Bronze layer as the contract boundary: raw events land there with their complete before and after structure intact, and all the versioning logic lives in the Silver-layer transformations instead. A breaking change upstream degrades gracefully at Silver rather than propagating forward and corrupting the Gold tables that dashboards and executives actually look at.

Fan-in architectures and multi-source topologies

Most of what this piece has described so far assumes one source feeding one destination. Production systems rarely look that clean. Fan-in topologies, where many operational databases feed a single warehouse, and fan-out topologies, where one source feeds several destinations, take every problem already discussed and multiply it by the number of systems involved.

Multiple source databases may use overlapping table names with completely different schemas behind them, and without namespace isolation at the destination, writes from two unrelated sources collide. A schema change on one source has to be contained so it doesn't block ingestion from every other source feeding the same warehouse, since failure isolation stops being optional once multiple systems share one pipeline. And the strict ordering guarantee that log-based CDC provides within a single source's transaction log doesn't extend across sources: two events from different databases that happen to touch related data in the warehouse carry no relative ordering relationship at all, and any cross-source join built in the warehouse has to be designed with that gap in mind.

Fan-out introduces a different kind of complexity. Delivering the same change event to Snowflake, BigQuery, and ClickHouse at once means each destination's own apply semantics, how it handles upserts, how it enforces constraints, how it resolves conflicting writes, has to be handled on its own terms rather than assumed to behave like the others. An event format that lands cleanly in one system can require translation before it behaves correctly in another, and treating all three destinations as interchangeable endpoints is how fan-out pipelines quietly diverge from one another over time, each one drifting slightly from what the source database actually recorded.