Does Lakeflow Connect guarantee no out-of-order records?

[yit337]

I use Lakeflow Connect to load data from my source databases to bronze tables. Then I have auto_cdc to track SCD2 changes in my silver tables. I use _commit_timestamp from the bronze CDF, as sequence_by property in auto_cdc in order to order the version of records. Is that enough, or shall I include business timestamp column to handle out-of-order events?

[pradeep_singh]

To process late arriving data correctly you would need a business column that identifies when the data was created/updated at the source and have that as the first column in your struct of sequence_by columns . 

A right sequence_by column/columns must be a monotonically increasing representation of the correct event order, with one distinct update per key at each sequencing value ._commit_timestamp alone is only enough if bronze commit time is the exact event order you want to preserve.

[Lu_Wang_ENB_DBX · Accepted solution]

Recommendation: use a business/effective timestamp in sequence_by if your source can emit late/backdated changes and you want SCD2 history to reflect source event time, not bronze arrival/commit time. If ties are possible, use a STRUCT for deterministic ordering, e.g. STRUCT(business_ts, _commit_timestamp). AUTO CDC uses SEQUENCE BY as the logical order of CDC events, handles out-of-order arrivals, and supports multi-column sequencing via STRUCT.

Options

1. Keep _commit_timestamp only — good if bronze commit order is already the business order you want. Databricks docs do show this pattern for Delta CDF examples.
2. Use business timestamp only — best if source events can arrive late and must be ordered by source/effective time, not ingestion time. AUTO CDC docs describe SEQUENCE BY as the logical order from the source data.
3. Use STRUCT(business_ts, _commit_timestamp) — best balance in practice: business time decides history, commit time breaks ties deterministically.

Summary: _commit_timestamp alone is enough when “arrival order = desired history order.” Otherwise include business timestamp.