Data Warehouse Architecture Types for Real-Time Ingestion
Four warehouse architectures make different tradeoffs between freshness and consistency.
Real-time ingestion depends on more than moving database changes quickly. Lambda, Kappa, streaming-first, and hybrid data warehouse architectures make different tradeoffs between freshness, query performance, and governance. Log-based CDC makes sub-minute ingestion achievable, but that speed reaches the query layer only if the warehouse can sustain it. The right foundation is the one that preserves the latency the workload requires while meeting its operational constraints.
Why warehouse architecture determines whether real-time ingestion actually delivers real-time results
A team swaps its nightly batch jobs for CDC, but its dashboards still show data that is hours old. Changes leave the source database promptly, but the warehouse continues to process them on a batch schedule. The capture mechanism has changed without changing the path that makes those changes queryable.
The ingestion and serving layers are coupled, so their performance cannot be evaluated separately. A warehouse designed for periodic loads can squander the speed of a continuous change stream before queries reach it. CDC turns batch ETL into streaming ELT, but the results stay fresh only if downstream processing can also handle changes as they arrive. Choosing the architecture sets how fresh the system can keep its data, so teams need to make that choice before they build the pipeline.
What log-based CDC actually does, and why it is the ingestion mechanism that makes the rest of this possible
Log-based CDC reads the transaction log that an operational database maintains for replication and point-in-time recovery. PostgreSQL calls this the WAL, while MySQL uses the binlog. Reading the log avoids SELECT scans that compete with production traffic and places near-zero overhead on the operational database. It also captures inserts, updates, and deletes in commit order rather than reconstructing changes from periodic snapshots.
These traits make log-based CDC the foundation that real-time warehouse architectures share. Timestamp polling that uses updated_at adds read load, misses hard deletes, and can miss the states that fall between polling intervals. The log instead supplies an ordered history of mutations. That history gives downstream systems the changes they need to maintain current state, including the removal of rows that no longer exist.
Capture alone does not establish destination correctness. CDC pipelines deliver events at least once, so the sink needs deterministic primary keys and idempotent upserts to prevent redelivery from creating duplicates. Latency also needs to be measured from the moment the log is read through to queryable results. A tool can read changes quickly while it stages unmerged files in object storage, so how fresh the data is still depends on a downstream batch job.
The Lambda architecture and the latency cost of separating batch and streaming layers
Lambda architecture addresses query reliability by separating historical correctness from immediate freshness. Its speed layer ingests CDC events and makes them immediately queryable, while its batch layer periodically reprocesses history. The batch results overwrite the speed layer’s results, and a serving layer merges both. This gives the system a way to serve recent changes without abandoning historical recomputation.
The separation nevertheless creates a structural limit for real-time workloads. Sub-minute latency is achievable within the speed layer, but the batch layer follows a recomputation cycle that is often hourly or longer. When a query spans both layers, it can mix fresh changes with stale historical results, depending on which serving partitions it reaches.
Lambda also requires two codebases to express the same business logic, with separate failure modes and reconciliation work when results diverge. Schema changes add to the work because they have to pass through both processing paths. The complexity can be justified when historical correctness dominates, including regulatory restatements and complex aggregations over years of history. When sub-minute freshness is the primary constraint, however, the batch layer imposes a latency floor that the speed layer cannot eliminate.
The Kappa architecture and the demands of a single streaming layer
Kappa architecture removes the batch layer and treats a replayable event log as the system of record. To correct processing errors or recompute aggregations, the system replays that log from a known offset. The same CDC stream supports both live processing and historical recomputation, so business logic lives in one place. This removes Lambda’s dual-codebase problem, though the system still has to reconcile changes correctly.
Instead, correctness becomes the responsibility of the streaming layer and its destination. The warehouse must handle continuous upserts well independently of periodic bulk loads. It must also merge replayed events without duplicating rows when replay overlaps with live ingestion. At the same time, it must preserve query performance while writes continue, which a warehouse that serializes reads and writes cannot do.
Replay also depends on retaining the required history. DynamoDB Streams retains records for only a short window, while Kinesis Data Streams can retain them for far longer. How long history is kept shapes the architecture, so retention is more than a small configuration choice. Without a pre-built batch fallback, a processing error or schema mismatch may call for a full replay, which cannot run if the history it needs has expired.
Streaming-first warehouse architectures and the different aims of OLAP engines and lakehouses
Streaming-first destinations fall into two groups with different optimization targets. Distributed OLAP engines prioritize query throughput on petabyte-scale event streams, while lakehouse architectures aim to unify BI, data engineering, and AI on an open-format foundation. The choice depends on what the data must support after ingestion.
ClickHouse, Apache Pinot, and Apache Druid serve high-volume event workloads where query latency on fresh data is the main constraint. Their processing semantics still matter for CDC. Apache Druid’s documentation describes rollup as summarization or pre-aggregation, while Apache Pinot’s documentation states that partial upsert tables skip incoming events whose comparison value is lower than the latest available value. These behaviors matter when the workload depends on how individual changes are retained or applied. Governance, data sharing, and AI/ML are not these engines’ native focus, which can leave a gap beyond operational analytics.
Lakehouses address that broader workload mix through open table formats, scalable storage, and high-performance compute. Databricks positions Lakehouse//RT as supporting high query throughput directly on governed lakehouse tables without copying data into a separate serving layer. That approach can bring fresh queries and broader analytical work onto the same foundation. But that flexibility comes with upkeep, including work on table compaction, file sizes, and merge performance. Teams must manage those demands themselves or rely on managed services to handle them.
Cloud data warehouses as CDC destinations and the latency-governance tradeoff
Snowflake and Amazon Redshift emphasize governance and ecosystem integration, but both also provide streaming ingestion paths. They are not inherently incompatible with real-time requirements. Their suitability depends on the specific path used and whether its latency and throughput match the workload. A cloud warehouse receiving continuous changes is not necessarily making those changes continuously queryable.
Snowpipe Streaming provides Snowflake’s real-time ingestion path. Snowflake also offers Cortex AI functions for in-database capabilities including text generation, summarization, and classification, while Cortex ML Functions support forecasting and anomaly detection. Amazon Redshift’s documentation describes streaming ingestion from Amazon Kinesis Data Streams or Amazon Managed Streaming for Apache Kafka into materialized views without staging in S3. These capabilities let teams evaluate streaming ingestion within a governed warehouse rather than assume that a separate OLAP destination is mandatory.
The distinction between ingestion speed and query freshness still applies. A micro-batch interval or materialized view refresh cycle can determine when a query sees the latest changes, even when capture is continuous. Teams must verify a near-real-time label by checking end-to-end latency to see how fresh query results are. For workloads with compliance requirements, secure sharing across business units, or AI/ML access to dashboard data, the governance capabilities can justify the tradeoff.
Hybrid architectures and the case for separate operational and governed serving layers
A streaming-first engine handles fresh operational queries, while a cloud warehouse supports BI and AI workloads. This is a deliberate separation of concerns rather than an attempt to make one destination satisfy incompatible priorities. CDC makes the separation practical by allowing multiple consumers to share one capture stream.
That fan-out can feed a distributed OLAP engine, a cloud warehouse, and a search index or cache without rereading the source for every destination. Separate CDC pipelines would instead multiply replication load and create more points where schemas can diverge. A shared stream avoids that duplication, but each consumer still needs to process schema changes consistently. If they do not, one destination can stay fresh while another stalls.
Illustrative workloads show why this separation is useful. A retailer can use the same source changes for real-time personalization and weekly analyst reporting. A financial services workload can combine fraud detection queries with governed risk modeling without duplicating capture infrastructure. An AI agent can also get fresh, governed context from the tables that feed company dashboards.
Schema evolution as the production constraint that invalidates architecture decisions made on paper
An architecture that handles steady-state traffic can still fail when the source schema changes. CDC exposes those changes to consumers that may assume a fixed schema. If a publisher adds a column before its subscriber can accept it, replication of new rows can stop until the subscriber catches up. The result may appear as stale data in an otherwise apparently functioning query layer.
PostgreSQL logical replication makes this coordination explicit because it does not replicate DDL. A CREATE TABLE or ALTER TABLE on the publisher does not propagate to the subscriber. The safe practice is additive-only, subscriber-first change, with new columns added to the subscriber before the publisher. Renames and type changes require two-step “expand and contract” operations that tolerate both schemas during the transition.
Delivery state creates another operational obligation. MongoDB change streams deliver at least once by default, so an event can be redelivered if a consumer crashes after processing it but before acknowledging it. For exactly-once semantics, the system must explicitly keep track of consumer state.
A CDC layer that needs someone to step in for every schema change has a hidden batch dependency. Data keeps moving, but schema compatibility has to wait for a person. Automatic schema evolution must detect changes and propagate them without pipeline restarts or manual intervention for the architecture to operate as designed. Tool selection must therefore account for schema handling, destination correctness, and capture throughput.
How to match architecture to real-time ingestion requirements before building
Choosing an architecture starts with three questions: what query latency the serving layer needs, which governance and compliance constraints apply, and how often source schemas change. The same CDC layer must meet all of those needs. Query response time and data freshness also need distinct requirements, because a fast query can still return old data. This makes the serving workload the starting point rather than the advertised speed of an ingestion tool.
For sub-second queries on high-volume event streams where governance is not the main constraint, a distributed OLAP destination fits a Kappa-style single-stream design. Fan-out can supply secondary destinations when needed. For sub-minute freshness with governed access, data sharing, and AI/ML workloads, a cloud warehouse with a streaming ingestion path is the closer fit. When both operational speed and governed analytics are needed, a hybrid keeps those serving paths apart while they share capture. Lambda remains justified when historical recomputation correctness outweighs freshness, provided its batch-imposed latency floor is accepted explicitly.
Across these choices, automatic schema evolution, exactly-once semantics at the sink, and fan-out without multiplied source load are baseline CDC requirements. Building those capabilities in-house means assembling capture, consumer state, DDL propagation, and failure recovery into a reliable system. That is a multi-year build competing with the engineering work the architecture is intended to support. The foundation is ready to choose only when its serving behavior, recovery model, and schema handling all meet the same real-time requirement.
