Exactly-Once Delivery Edge Cases in CDC-to-Warehouse Pipelines
Coordinating three layers prevents duplicate rows and schema errors in live data pipelines.
Exactly-once delivery in CDC-to-warehouse pipelines depends on coordinated recovery, schema interpretation, and sink writes. Automatic schema evolution for live CDC pipelines detects source schema changes, pauses affected table streams, updates the target, and resumes row delivery. That guards against errors in the data’s structure, while durable deduplication, idempotent writes, and ordering controls guard against the other failures that make analytics wrong without any sign of trouble.
Why exactly-once is a property of a system, not a single component
A source log, a transport layer, and a warehouse sink each provide a different part of the correctness contract. Postgres WAL records changes before they are applied, but it does not control how often downstream consumers read them. A transport can retry a message that already arrived. The warehouse then applies what it receives unless its writer recognizes repeated work. No component establishes the complete guarantee on its own.
CDC services built for this work, like Artie, manage exactly-once semantics across these three layers so that consumer code does not have to. The distinction is between a message arriving once and its effect being applied once. Crashes and network partitions can make the former impossible to establish, while idempotent application can protect the latter.
How the WAL-offset commit cycle creates the primary duplicate window
In Postgres logical replication, a subscriber pulls change messages, writes them downstream, and advances its acknowledged log position. That position is expressed as an LSN, or Log Sequence Number, with confirmed_flush_lsn recording confirmed consumption. These operations are separate. They cannot form one atomic commit across Postgres and the downstream system, so a batch can reach the warehouse before the source knows it has been consumed.
Some messages have been written, but the acknowledged position still precedes them. If the consumer crashes, Postgres replays messages from the retained restart position when it reconnects, including changes already applied downstream. For a high-throughput database, one reconnect can produce tens of thousands of duplicate deliveries. A slow sink or interrupted acknowledgment extends exposure to the same gap.
Offset advancement also allows Postgres to reclaim WAL storage. If a consumer disappears and its slot remains frozen, retained WAL can fill the primary’s disk. Managed replication platforms, including Artie, handle offset advancement and slot retention automatically, coordinating recovery so that replays do not become duplicate effects. This makes the gap a managed recovery condition.
What happens when a consumer crashes mid-flush
A consumer reads a batch and sends some rows to the warehouse, then crashes before advancing its LSN. On restart, Postgres sends those rows again. An append-only sink accepts them as new records, even though their source events have not changed. The pipeline may report no application error. A payment pipeline summing transaction events would double-count repeated payments, while a duplicated “user promoted to admin” audit event would muddy the change timeline.
The defense begins with a stable message identity. A commit LSN combined with a per-transaction message index, commit_idx, distinguishes messages within the log while remaining unchanged across replays. Backfill rows need a separate identity because they come from table reads. Using the backfill’s ID with the source row’s primary key keeps the same row from being processed more than once in that backfill run. The key must describe the work.
Deduplication state must also survive the consumer’s crash. In-memory tracking disappears at precisely the moment it is needed for recovery. A durable filter can spot work already applied, while offset advancement waits until the data has been delivered or saved to durable internal storage. Deferral protects against loss. The idempotency layer contains the replay that safe acknowledgment ordering deliberately permits.
How do you handle upstream schema changes without stopping your CDC pipeline?
Automatic schema evolution handles supported upstream changes by keeping target migration in step with row delivery for the tables that changed. It does not require pretending that delivery can continue through an incompatible schema. Instead, the system detects the change, pauses the affected stream, applies the target migration, and resumes. The pause protects interpretation as well as delivery. Changes that are incompatible with downstream consumers still require coordinated consumer upgrades or a versioned topic with the new schema.
Postgres logical replication does not replicate DDL. ADD COLUMN, ALTER TABLE, and DROP COLUMN do not propagate through the replication slot as subscriber schema migrations. In native logical replication, the subscriber schema must be updated before the publisher change. Otherwise, a subsequent event involving the new column can fail to apply and block the stream. Automatic schema evolution must supply this coordination rather than assume that the replication slot provides it.
These failures are not always obvious from delivery metrics. A pipeline can look active and have normal-looking latency even when the structure of the warehouse data is wrong. Replica identity creates a related risk. The PostgreSQL 18 documentation states that tables without an appropriate replica identity cannot support published UPDATE or DELETE operations, and attempts produce an error on the publisher. REPLICA IDENTITY FULL gives those operations the before-image they need, but writes more data because it logs the whole old row on every update.
Primary keys and replica identity must be validated at connection time, and schema propagation must be coordinated before affected row delivery resumes. A delivery-layer idempotency key cannot detect that a message was interpreted against the wrong schema. Receiving a change once is insufficient if its meaning is lost.
Network partitions during warehouse commits and the two-generals problem at the sink
A connection can fail after the warehouse commits a batch but before the writer receives acknowledgment. From the writer’s position, that outcome looks like a batch that never reached the warehouse. The write may also have partially succeeded. The writer has to query the sink to know which state it faces, and recovery may already have caused a retry. This acknowledgment uncertainty is the practical two-generals problem at the sink.
Retrying guards against loss but can repeat a write that already worked. Idempotent sink application is therefore required. A MERGE or UPSERT keyed on stable row identity can make repeated application produce the same result as one application. Snowflake, BigQuery, Redshift, and Databricks support MERGE, but the writer must use the appropriate atomic semantics.
The resulting contract is at-least-once transport with exactly-once effects at the sink. That distinction belongs in any audit of delivery claims. AWS DMS’s CDC task documentation explains how CDC works and where it can start but gives no delivery guarantee for that path. That omission leaves the end-to-end contract unspecified for teams evaluating continuous replication.
Fan-in architectures and the merge conflict problem across tables
Fan-in combines changes from multiple tables or databases at one warehouse destination. Even if each table delivers every change once, parallel streams can arrive in the wrong dependency order. An ORDER_ITEMS row may arrive before the ORDER row it points to. Where foreign keys are enforced, the child insert fails. Otherwise, the warehouse temporarily contains a child without its parent, distorting join-based analytics until the parent arrives.
This is not an inherent limitation of native Postgres ordering. The PostgreSQL 18 documentation guarantees transactional consistency for publications within a single subscription, with the subscriber applying data in publisher order. Order across tables is lost when a tool between the source and sink splits changes into separate streams or topics for each table. Fan-in across databases has another boundary to handle because the databases may have no shared transaction order. Both cases can leave the data inconsistent for a time even when nothing crashes.
The available defenses serve different consistency needs. Global sequencing imposes order at a cost, eventual convergence accepts temporary inconsistency, and dependency-aware application controls when related rows are written. A unified delivery layer with primary-key-aware deduplication also avoids conflicting parallel work on the same entity. But deduplicating a primary key does not put parents and children from different tables in order. The architecture must specify both its duplicate handling and the consistency it promises while streams converge.
Source-specific guarantees that change the failure surface for DynamoDB Streams and MongoDB change streams
DynamoDB Streams guarantees that each record appears once within the stream and that changes to a given item appear in modification order. Those guarantees stop at the consumer boundary. A Lambda function crash mid-batch or a stream error can cause repeated delivery, so the handler still needs idempotency. The recommended pattern uses the sequence number as a stable key stored in DynamoDB, with a TTL longer than the maximum retry window.
Deduplication cannot bring back changes lost when retention runs out. DynamoDB Streams has a 24-hour retention limit, and ordinary replay cannot bring back changes that are no longer available after that limit. A 20-hour consumer lag is therefore a crisis. Switching to Kinesis Data Streams for DynamoDB extends retention but gives up the native per-item ordering guarantee. Changes to the same item can span shards, arrive out of order, and appear more than once.
MongoDB change streams offer a high-level API over the oplog for a collection, database, or deployment. Their recovery boundary is the resume token. If a consumer loses its token or retains one that is stale, recovery may replay earlier changes or miss changes, depending on oplog retention. An exactly-once claim must name the source-specific position, ordering, and retention conditions on which it depends.
Why Postgres HA failover is an exactly-once gap most teams discover too late
Even planned Postgres failover can interrupt CDC when the promoted standby lacks usable replication slot state. Postgres 17 introduced logical replication failover with slot synchronization to promotion candidates. Eligibility still has conditions. The standby slot must have synced = true, temporary = false, and invalidation_reason IS NULL, and the standby needs WAL history back to the slot’s restart_lsn. A new or restarted standby can lack that history.
If the promoted standby cannot provide the slot, connector reinitialization typically requires a full snapshot reload. Changes between the last successfully replicated LSN and the new consistent snapshot then need reconciliation. Otherwise, the warehouse can remain inconsistent despite a successful database promotion. The operational defense is recent slot advancement, persistent monitored subscriber connections, and standby eligibility checks, with failover behavior tested before a production event. Connection health alone is not proof that the required slot state and WAL history are available.
What a production-grade defense against each failure mode looks like in practice
A team assessing a replication system should include how it recovers in the contract it expects by default. Crash-safe deduplication requires stable message identities and durable state, with acknowledgment deferred until delivery or durable persistence. Sink writes need idempotent MERGE or UPSERT logic that uses source primary keys and log position in place of append-only INSERT behavior. These are complementary decisions. Safe acknowledgment prevents loss, while durable deduplication and sink idempotency prevent recovery from changing the result.
Schema handling needs an equally explicit contract. Automatic schema evolution must detect changes and propagate target migrations before resuming affected row delivery, while connection-time validation exposes missing primary keys or replica identity. For fan-in, tracking in-flight primary keys prevents a second worker from dispatching another change for an entity before the first worker confirms delivery. A unified delivery layer handles deduplication in the same way across sources. Cross-table dependencies still need the ordering or eventual-convergence policy appropriate to the workload.
Retention and failover require continuous operational controls. Consumer lag alerts protect the DynamoDB retention boundary, while max_slot_wal_keep_size limits Postgres slot retention exposure. Persistent connections and slot advancement support standby readiness, with eligibility monitoring covering the conditions that connection activity cannot establish alone. Once the source history needed for recovery is gone, retry logic cannot bring it back.
Artie implements these defenses with exactly-once semantics and automatic schema evolution as defaults across sources including Postgres, MySQL, MongoDB, and DynamoDB. Its destinations include Snowflake, BigQuery, Redshift, and Databricks. As a passthrough replication service that stores no customer data itself, it implements sink idempotency without requiring teams to maintain separate state infrastructure, with HIPAA and SOC 2 frameworks incorporated into the design. It is one managed implementation of the engineering decisions described here. The evaluation criterion remains the same for managed services and custom systems, namely a defined recovery path for every boundary where delivery, interpretation, or ordering can fail.
