A row gets deleted at 14:02. The delete event reaches your warehouse. The row is gone. At 14:07, a Kafka consumer lag spike flushes an update for the same primary key that was committed at 13:58. Your incremental model merges it in. The row is back.
This is not a freshness bug. It is a precedence bug. The merge had no way to know that the incoming update was older than the delete already applied, because the only ordering signal it carried was event_time, and event_time is exactly the column that is not monotonic in a CDC stream.
This article is about the mechanics of that failure and the cheapest structural fix: carry a monotonic ordering key alongside event time, and make the merge conditional on it. Everything below is grounded in what the source systems actually document about ordering, and priced in the hours and schema changes the fix costs.
The failure shape, precisely
Debezium’s PostgreSQL connector produces a change event for every row-level insert, update, and delete it captures, and streams them to a Kafka topic per table (Debezium PostgreSQL connector documentation). Each event carries the row’s data and metadata, including the source’s log position. The connector records the WAL position for each event and, on restart, continues reading from where it last left off.
That last sentence is the guarantee you actually have: the connector resumes from a recorded position. It is not a guarantee that the events arrive at your warehouse in commit order. Between the connector and your merge, there is a Kafka topic with partitions, a sink connector with its own retry and batching behavior, and often a staging table or a dbt model with a filter. Any of those can reorder, delay, or replay a record relative to its neighbors.
PostgreSQL’s own logical replication documentation is explicit that the subscriber applies changes in the same order as the publisher, so transactional consistency is guaranteed within a single subscription (PostgreSQL logical replication). That is a strong guarantee at the replication layer. It says nothing about what happens after the events leave that layer and land in a table that is merged by a scheduled job.
So the failure is not that the source lost ordering. The failure is that the ordering information was dropped somewhere between the source and the merge, and the merge fell back to a timestamp that does not encode it.
Why the standard incremental filter cannot see this
The documented dbt incremental pattern filters on a cutoff taken from the target table:
select * from orders
where updated_at > (select max(updated_at) from {{ this }})That is the example from dbt’s own incremental models best-practices page. The page also documents the failure mode directly: late-arriving records can be filtered out by a max(updated_at) cutoff, and the recommended mitigation is a lookback window. It then states the limit of that mitigation plainly: with a 3-day lookback, a record that arrives 4 days late is still missed.
Read that carefully. The lookback window is a time filter. It widens the set of rows the model considers. It does not establish which of two rows for the same key is authoritative. If a delete for order_id = 42 was applied at commit position 5000, and an update for order_id = 42 with an earlier event timestamp arrives on a replay, a lookback window that includes both rows will happily merge the update over the delete. The row comes back. The window did its job; the merge did not.
This is the class of bug that a dbt maintainer discussion names directly: with CDC data arriving out of order, the latest batch by ETL time is not the most recent state in terms of event time (dbt discussion #10672). The discussion is about a proposed event_time and microbatch feature; it raises the problem, it does not claim a shipped fix for out-of-order CDC.
Two clocks, and only one of them orders writes
Separate the two timestamps that get conflated in every CDC-fed model:
- Event time — when the change happened in the source business process. This is the column your analysts filter on, and it is the column that is non-monotonic in the stream.
- Ordering position — when the change became durable and was read by the capture process. In PostgreSQL this is the WAL position (LSN). In Iceberg, the spec assigns a sequence number to every successful commit, and the relative age of data and delete files relies on that sequence number (Iceberg table spec).
The invariant you want is last writer by commit order wins. Event time answers “when did this happen.” Ordering position answers “which version is authoritative.” A merge keyed only on the business key, with no ordering comparison, cannot enforce the second invariant no matter how wide the lookback window is.
This is also why the fix is not “use a better timestamp.” There is no business timestamp that is guaranteed monotonic across a source system’s writes, a capture process’s restarts, and a replay. The ordering position is the only column that is monotonic by construction, because it is assigned by the system that serialized the writes.
The mechanics: an ordering column and a conditional merge
The structural fix has three parts. None of them is exotic; the cost is in the schema change and the backfill, not in the SQL.
1. Carry the ordering column into staging
Debezium’s PostgreSQL connector already emits the source log position in the event metadata. The first step is to project it into a column in the staging model, alongside the business columns and event_time. Call it commit_lsn or source_sequence; the name matters less than the fact that it is carried end to end and never dropped by a select * that gets rewritten.
If you are on Iceberg, the equivalent is the snapshot sequence number, which the spec assigns per commit. If you are on a warehouse that does not expose a source sequence, you need a monotonic surrogate assigned at ingest — a Kafka offset plus partition, or an ingest-time sequence from the sink. The point is that it must be assigned by the system that serialized the write, not by the row’s business content.
2. Keep the latest row per key by ordering column
Before the merge, deduplicate the incoming batch by business key, keeping the row with the highest ordering value. This handles the case where a single batch contains both an update and a delete for the same key. Without this step, the merge’s behavior depends on row order within the batch, which is not something you control.
3. Make the merge conditional on the ordering value
The merge predicate must include the ordering comparison, not just the key match. In pseudocode:
merge into target t
using incoming i
on t.order_id = i.order_id
when matched and i.commit_lsn > t.commit_lsn then update ...
when not matched then insert ...The i.commit_lsn > t.commit_lsn clause is what makes the merge replay-safe. Replaying an old window re-applies rows whose ordering value is lower than what is already stored; those rows lose the comparison and are ignored. The merge becomes idempotent by construction, not by hoping the replay window is narrow enough.
Note what this requires: the target table must store commit_lsn as a column, not just use it in the predicate. The comparison is against the stored value, so the column has to persist.
Deletes need a tombstone, not a physical delete
Here is the part that trips most implementations. If a delete physically removes the row from the target, there is nothing left to compare against. The next late-arriving update for that key finds no matching row, takes the when not matched branch, and inserts. The row is resurrected, and the conditional merge never gets a chance to reject it.
The fix is to represent deletes as tombstone rows: a row with the key, the ordering value, and a deleted_at or is_deleted flag, rather than a physical removal. The merge then compares ordering values against the tombstone. A late update with a lower ordering value loses the comparison and is ignored. A later legitimate re-insert with a higher ordering value wins, which is correct.
This is the same mechanism Iceberg uses at the file level: version 2 adds delete files that encode rows deleted in existing data files, so row-level deletes can be applied without rewriting the data files (Iceberg table spec). The delete is a record with an ordering relationship to the data, not an absence of a record. Your merge needs the same property at the row level.
The cost of tombstones is real: the target table grows with deleted rows, and every downstream query needs to filter on the flag. That is a maintenance tax you are choosing to pay in exchange for replay safety. The alternative — physical deletes and a merge that cannot see them — is cheaper to write and more expensive to debug at 2 a.m.
Backfill and replay: the same rule must hold
When you re-run a window — an Airflow backfill, a dbt --full-refresh, a manual replay of a Kafka partition — the same ordering rule must apply. If the replay path uses a different merge (for example, a delete+insert strategy that does not compare ordering values), the replay will re-apply stale rows and undo the protection the conditional merge provides in steady state.
This is where the lookback-window pattern and the ordering rule interact. The lookback window determines which rows the model considers. The ordering rule determines which of those rows wins. You need both, and they are not substitutes. A wide lookback with no ordering rule re-applies stale rows. A narrow lookback with an ordering rule still misses rows that arrive outside the window, but the rows it does see are merged correctly.
dbt’s documentation is honest about the drift that lookback windows cannot prevent: incremental models inevitably drift from the source data over time, and the documented reset is a periodic full refresh (dbt incremental models). A full refresh that rebuilds from the source with the ordering column intact will produce a correct table. A full refresh that rebuilds from a staging layer that dropped the ordering column will reproduce the bug at full scale.
Pricing the fix
The fix is not free, and the costs are specific:
- Schema change on every CDC-fed target table. One extra column (
commit_lsnor equivalent) plus, if you adopt tombstones, a delete flag. This is a migration on each table, not a one-time change. - A one-time backfill to populate the ordering column. Existing rows have no ordering value. You either backfill from the source’s log history (if it is still available) or accept that historical rows are treated as lowest-precedence until they are touched again. The second option is cheaper and leaves a known gap.
- A conditional merge predicate that must be kept in sync across every model that touches the table. This is the ongoing maintenance tax. Every new incremental model on a CDC-fed table needs the same predicate, and every model that is refactored needs it preserved. A model that drops the predicate silently reintroduces the bug.
- Tombstone storage and downstream filter changes. If you adopt tombstones, every downstream query that reads the table needs to filter on the delete flag. That is a change to every consumer, not just the merge.
Against that, the cost of not fixing it is a class of bug that is hard to detect and harder to attribute. A resurrected row looks like a data quality issue in a downstream report, not like a merge precedence bug. The time to diagnosis is measured in hours per incident, and the incidents recur every time a consumer lags or a partition is replayed.
What to check before the next replay
Before you run a backfill or a full refresh on a CDC-fed table, answer these questions:
- Does the staging model carry the ordering column? If it was dropped by a
selectthat lists columns explicitly, the merge cannot use it. - Does the target table store the ordering column? If it is only in the predicate and not persisted, the comparison has nothing to compare against on the next run.
- Are deletes represented as tombstones or physical deletes? If physical, a late update will resurrect the row and the conditional merge will not catch it.
- Does the replay path use the same merge as steady state? A backfill that uses
delete+insertorappendbypasses the ordering rule entirely. - Which tables are exposed? Any table fed by CDC where the merge key is the business key and the only ordering signal is
event_timeis a candidate for this failure. The list is usually longer than expected.
The detection method for resurrected rows is a comparison between the source’s current state and the target’s current state for a sample of keys, run after a replay. If a key exists in the target but not in the source, and the target row’s ordering value is lower than the source’s last delete position, you have found one. This is a diagnostic you can run on your own systems; it does not require a vendor feature.
The tradeoff, stated plainly
The conditional merge with an ordering column is more complex than the documented incremental pattern. It requires a schema change, a backfill, a predicate that must be maintained across models, and — if you adopt tombstones — changes to every downstream consumer. That is a real cost, paid in hours and in the ongoing attention of whoever owns the models.
The alternative is to accept that late-arriving updates can resurrect deleted rows, and to detect and correct them after the fact. That is cheaper to build and more expensive to operate, because the correction is manual and the detection is after the fact.
The choice depends on how often your CDC pipeline replays, how much consumer lag you tolerate, and whether a resurrected row in a downstream report is a nuisance or an incident. What is not a choice is whether the standard timestamp filter can prevent it. It cannot, because it filters on the one column that is not monotonic. The ordering column is the only signal that answers the question the merge is actually asking: which version is authoritative.
FAQ
Doesn’t PostgreSQL logical replication guarantee ordering?
It guarantees that the subscriber applies changes in the same order as the publisher, so transactional consistency is guaranteed within a single subscription (PostgreSQL documentation). That guarantee applies at the replication layer. It does not extend to what happens after the events are written to Kafka, consumed by a sink, staged, and merged by a scheduled job. The ordering information has to be carried through those layers, not assumed to survive them.
Can’t I just widen the lookback window?
A wider lookback window includes more rows in the merge. It does not change which row wins. If the merge has no ordering comparison, a stale update still overwrites a newer delete regardless of whether the window is 3 days or 30. The window controls coverage; the ordering rule controls precedence. dbt’s own documentation notes that a lookback window still misses records that arrive later than the window, so drift persists even with a wide window (dbt incremental models).
Does Iceberg solve this for me?
Iceberg’s spec assigns a sequence number to every successful commit and uses it to determine the relative age of data and delete files. That is the same ordering principle at the file level. It does not automatically make your row-level merge replay-safe if the merge is keyed on a business key and a non-monotonic timestamp. You still need to carry an ordering value into the merge predicate. Iceberg gives you the mechanism; it does not give you the guarantee for your specific merge.
What about dbt’s microbatch and event_time features?
The dbt discussion that proposes event_time and microbatch incremental models names the out-of-order CDC problem directly: the latest batch by ETL time is not the most recent state in terms of event time (dbt discussion #10672). The discussion is a proposal, not a shipped fix for this scenario. Treat it as evidence that the problem is recognized, not as a solution you can rely on today.
How do I know if my tables are affected?
Any table fed by CDC where the merge key is the business key and the only ordering signal is event_time is a candidate. The diagnostic is a comparison between the source’s current state and the target’s current state for a sample of keys after a replay. If a key exists in the target but not in the source, and the target row’s ordering value is lower than the source’s last delete position, the merge has resurrected a deleted row. Run this on your own systems; it does not require a vendor feature.