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.