Production Databricks Notes: short technical note
The plan for this note originally called it "sequence columns are business semantics, not just CDC
configuration". The shorter claim is the same one: SEQUENCE BY decides which version of a record
wins, and deciding which version of a record wins is a question about the business, not about the
pipeline.
It gets filled in with whatever timestamp was closest to hand. Here is what each of the usual
candidates actually asserts.
source_updated_at: when the source system recorded the change
The column the source database wrote when the row changed.
What it asserts: ordering follows the order events happened in the source system.
Where it breaks: clock skew across multiple source instances, a source that backfills historical
records with their original timestamps, and any source that writes this column at transaction start
rather than commit. In the backfill case, the "old" record is old and correctly loses,
which is right if you want state-as-of-source-time and wrong if you want last-write-wins.
ingested_at: when your pipeline received it
Stamped by ingestion.
What it asserts: ordering follows arrival at your system.
Where it breaks: it discards source ordering entirely. Two events that happened in a clear order
in the source, but travelled through different partitions or connectors, can arrive reversed. Using
arrival time then applies the older change last. This is the most common wrong answer, because it is
always available and always populated.
It is the right answer in one situation: when the source has no reliable ordering at all and you
have consciously decided that arrival order is your definition of truth. Write that down when you
choose it.
A monotonic sequence number from the source
A log sequence number, a transaction ID, a CDC connector's own counter.
What it asserts: ordering follows the source's own commit order.
This is usually the best available answer when the source provides one, because it is the source
system's own statement about what happened first, with no clock involved.
Where it breaks: the counter resets on failover or restore, or it is monotonic per-partition
rather than globally. Both produce ordering that is correct within a range and wrong across the
boundary, which is worse than being uniformly wrong because it survives testing.
A business-meaningful timestamp
effective_date, valid_from, transaction_date. A time that means something to the business,
independent of when any system recorded it.
What it asserts: ordering follows business chronology.
This is the right choice when the domain has genuine temporal semantics, and it is the one that most
often reveals that your Type 2 history and your business timeline are two different things. A
correction entered today for a transaction dated last month is a real scenario, and how you order it
determines whether your history reads as "what we knew" or "what was true". Both are legitimate.
They are not the same table.
Two constraints from the API
The sequencing column must be a sortable data type, and NULL sequencing values are not
supported.
A nullable sequence column is a latent failure rather than a style issue. It deserves an explicit
non-null expectation upstream of the CDC flow, so the failure arrives as a quality violation at
ingestion instead of as an unexplained ordering result in production.
Ties
If your sequence column has a resolution coarser than your event rate, it will tie. Second-resolution
timestamps on a source producing several changes per second per key tie routinely.
An unresolved tie is resolved arbitrarily, which means the same input can produce different output
on a replay. That defeats the idempotence the pipeline was built for.
The fix is a struct. The API orders by the first field, then by the second when the first ties.
sequence_by = struct("updated_at", "transaction_id")
Choose the tiebreaker with the same care as the primary column. A monotonic ID is a good tiebreaker.
A random UUID makes ties deterministic without making them meaningful, which is better than
arbitrary but is worth knowing you have done.
A knock-on effect worth planning for
For SCD Type 2 targets, the __START_AT and __END_AT columns must have the same data type as
the sequence column.
So the sequence column also types your validity interval. Choose a BIGINT log sequence number and
your history table's validity range is expressed in log sequence numbers, which are correct and
unreadable to anyone outside the data team. Choose a timestamp and it is readable, and it inherits
whatever clock problems that timestamp has.
There is no free option. Pick knowing which cost you are taking, and if the history table has
business consumers, weight readability higher than you would otherwise.
The question to ask
Take it to whoever owns the data:
"Two versions of this record disagree. Which one is correct, and how would you know?"
If the answer is "whichever happened later in the source system", you want a source sequence number
or a source timestamp. If it is "whichever we received most recently", ingestion time is honest. If
it is "whichever is effective for the date in question", you have a bitemporal problem and should
say so before building rather than after.
If the answer is a discussion, that is the finding, and it is the same signal as any other unanswered
question in the reliability contract.
Companion to the full article on designing an idempotent CDC pipeline with Lakeflow. API
constraints verified against Databricks documentation on 2026-07-29.