How does AUTO CDC resolve same-timestamp tie in SEQUENCE BY?

[Khasim_1]

We are using AUTO CDC (APPLY CHANGES INTO) to process change data capture events from an upstream source (via Autoloader ingesting CDC files landed in cloud storage) into our Silver layer tables. Our source occasionally delivers out-of-order events — i.e., an UPDATE event can arrive before its corresponding INSERT, or a later-timestamped DELETE can arrive before an earlier UPDATE due to upstream retry/replay logic. > > We are using the SEQUENCE BY clause with our event timestamp column, but we occasionally still see records where the final state in the target table doesn't reflect the "true" latest state — we suspect this happens when duplicate timestamps exist across different event types (INSERT/UPDATE/DELETE) for the same key.

1. When multiple CDC events for the same key have identical SEQUENCE BY timestamps but different operation types (e.g., UPDATE and DELETE with the same timestamp), how does AUTO CDC resolve the tie internally? Is there a documented precedence order (e.g., DELETE always wins over UPDATE at the same timestamp)?
2. Is there a way to specify a secondary sequencing column (similar to a tie-breaker in a ROW_NUMBER() window function) to deterministically resolve same-timestamp conflicts in APPLY CHANGES INTO?
3. What's the recommended approach for handling truly out-of-order arrival (not just same-timestamp ties) — should we be doing pre-deduplication/sorting in a Bronze-to-Silver staging step before applying AUTO CDC, or does the framework already handle this natively as long as SEQUENCE BY is correctly set? > > Would appreciate any real-world experiences or documentation pointers on handling these edge cases reliably in production CDC pipelines.

[Brahmareddy]

Hi @Khasim_1, I would suggest not relying only on the timestamp if duplicate timestamps are possible. AUTO CDC can handle out-of-order events using SEQUENCE BY, but the sequencing should be deterministic.

You could try SEQUENCE BY STRUCT(event_timestamp, event_sequence_id) so the second field acts as a tie-breaker. If the source does not provide a reliable sequence or event ID, a small Bronze staging step to resolve those conflicts before AUTO CDC may be safer.

Hope this helps! 

Regards,
Brahma

[data_pulse]

@Khasim_1 

I tested this with a small AUTO CDC pipeline and SEQUENCE BY STRUCT() works as expected for same-timestamp ties.

Eg: In below data

Event_ts  seq_id operation
12:00     seq=1  INSERT
12:10     seq=1  UPDATE
12:10     seq=2  DELETE

with sequence_by=struct("event_ts", "seq_id"), the second field is used as the tie-breaker, so (12:10, 2) is considered later than (12:10, 1).

In my test with SCD Type 1, the final target row was removed because the latest logical event was the DELETE. The source CDC table still retained all events, the target represented only the latest state.

Summary:

• Out-of-order arrival -> AUTO CDC handles this through SEQUENCE BY.
• Same timestamp ties -> use STRUCT(timestamp, secondary_sequence).
• No reliable secondary sequence from source -> add a pre-processing step: staging/dedup before AUTO CDC.

I’d also avoid assuming that AUTO CDC gives DELETE or UPDATE any priority when their sequence values are identical. Use an explicit tie-breaker so the event order is deterministic.

[ivanvyd]

@Khasim_1 on your third question, check delete-tombstone retention alongside the sequencing change. The default is two days. The create_auto_cdc_flow reference gives this instruction:

Set pipelines.cdc.tombstoneGCThresholdInSeconds to a value that exceeds the maximum expected delay between event arrival and pipeline execution.

This is a target table property. I’d include outage recovery and replay backlogs when estimating that delay, not just normal processing time.

The AUTO CDC examples show the relevant behavior for userId = 123:

Event	sequenceNum
Initial insert	1
Delete	6
Update arriving out of order	5

With delete handling configured, the documented results are:

• SCD1: user 123 remains absent.

• SCD2: the history contains versions with __START_AT / __END_AT values of 1 / 5 and 5 / 6. Neither is active, so the earlier update does not undo the later delete.

That example demonstrates event ordering, not what happens after tombstone expiry. For your pipeline, I'd test the same sequence across separate batches within the configured retention window. This checks late-arrival handling alongside the tie-breaking fix.

[Louis_Frolio]

Hello @Khasim_1, I took a look at both internal and external documentation and here is what I found.

You're in good hands in this thread already. @Brahmareddy's STRUCT suggestion and @data_pulse's test both line up with the documented behavior, and @ivanvyd's tombstone reminder is an important one for late deletes.

Ties on the sequence value

AUTO CDC handles out-of-order arrival just fine, but it can't figure out the intended order when two events for the same key share the same sequence value. There's no documented rule that makes DELETE beat UPDATE, or vice versa, on an exact tie. So when timestamps collide, the final state in Silver isn't something you can count on.

The fix is a deterministic tie-breaker:

SEQUENCE BY STRUCT(event_timestamp, source_sequence)

The first field is compared first, then the second breaks ties. The tie-breaker has to reflect the source's real ordering, though:

• Best option: a source-provided value like an LSN, commit version, transaction sequence, or event ID.
• Don't use file arrival time or _metadata columns. Auto Loader doesn't guarantee file processing order, so pre-sorting by arrival won't help either.
• If your business rule is "DELETE wins on a tie," encode it explicitly in the secondary sequence value (for example, a CASE ranking on operation type) and document why it's there.

Where a staging step helps

For distinct sequence values, you don't need to pre-sort. AUTO CDC handles that. A Bronze staging step is still useful for dropping exact replay duplicates, filtering NULL sequence values (they aren't supported), and catching genuine conflicts. If the source can send two truly different events for the same key with the same composite sequence, it hasn't given you enough information to know the right answer, so resolve or reject those in staging.

Tombstone retention

As Ivan pointed out, deleted rows are kept as tombstones so late events reconcile correctly, and the default retention is two days. Set pipelines.cdc.tombstoneGCThresholdInSeconds on the target to exceed your worst-case delay, including replays and outage recovery. A late UPDATE arriving after cleanup may not reconcile the way you expect.

Testing it

Send the same key through separate batches in a few orders: INSERT, UPDATE, DELETE; then DELETE, UPDATE; then two events sharing a timestamp. The result should be identical every time the composite sequence defines a clear order.

At the end of the day, your pipeline is probably doing exactly what it's told. The sequence key just isn't unique enough yet.

References:

• AUTO CDC APIs (sequencing with multiple columns): https://docs.databricks.com/aws/en/ldp/cdc
• create_auto_cdc_flow reference: https://docs.databricks.com/aws/en/ldp/developer/ldp-python-ref-apply-changes
• AUTO CDC INTO reference: https://docs.databricks.com/aws/en/ldp/developer/ldp-sql-ref-apply-changes-into
• Auto Loader out-of-order data: https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/auto-loader/#handle-out-of-order-d...

Hope this helps.

Regards, Louis.