How to choose the right incremental key for late data
The right incremental key is the one that matches how the source system actually changes, not the one that looks neat in a sample extract. If there’s no trustworthy modified timestamp, you usually need a hybrid approach: a watermark for most rows, a re-read window for late updates and back-dated transactions, and CDC only where the source can genuinely support it.
That sounds simple until you meet ERP data in the wild. A sales order can be entered today, dated last month, amended tomorrow, and posted through a batch job that stamps nothing useful. If you pick the wrong key, your incremental load will look fine in test and quietly miss real changes in production.
Start with the change pattern, not the column
How do you choose the right incremental key when the source system has late updates, back-dated transactions, and no reliable modified timestamp? You start by asking which event in that table is most stable, then you prove it against the messy cases.
For operational systems, there are usually four candidates:
| Candidate | Works when | Fails when | |---|---|---| | Transaction date/time | Business facts are immutable after posting | Back-dated entries, corrections, reversals | | Insert or create time | Rows are only ever appended | Updates happen later, or imports backfill history | | Monotonic ID | IDs are assigned in strict order | IDs are preallocated, batched, merged, or imported | | CDC metadata | The source emits reliable change events | The system has no real CDC, or the connector lies |
A lot of teams reach for the highest number they can find, then call it a watermark. That works until someone posts a credit note for a transaction from six weeks ago, or imports a month of missed orders after a warehouse outage. In Australia, that kind of back-dated correction is common in ERP and wholesale flows where finance, fulfilment, and customer service all touch the same record.
If you’re modernising ERP data into Databricks warehouses and Power BI reporting, this is the first decision that determines whether the whole lakehouse stays trustworthy. The analytics layer can only be as current as the change rule underneath it.
Key takeaway: The best incremental key is not “the newest column”, it’s the one that survives corrections, imports, and back-dated business events without silently dropping rows.
When there’s no trustworthy modified timestamp, use the least-wrong clock
How do you choose the right incremental key when the source system has late updates, back-dated transactions, and no reliable modified timestamp? You usually choose the timestamp that represents business truth, then add overlap to catch late arrivals.
Here’s the practical order of preference:
- CDC metadata, if the source truly emits before/after images or reliable change events.
- Insert time, if rows are append-only and updates are rare or impossible.
- Business event time, if the table is transaction-led and corrections are handled by new rows rather than updates.
- ID order, only if you have evidence the ID is strictly increasing and assigned at write time.
- A composite rule, when no single field is safe enough.
That last point matters. In messy ERP data, the answer is often not “pick one key”. It is “use a cutoff key plus a lookback window, then deduplicate by business key and latest known state”.
For example, if invoice lines can be posted late, a transaction date alone is not enough. You might extract all rows where posting_date >= last_successful_run - 7 days, then merge on invoice_line_id and keep the latest version by source sequence or load timestamp. The overlap absorbs late updates. The merge prevents duplicates.
This is where a fractional CTO or data lead earns their keep. The decision is not just technical, it is about what failure mode you can live with. Missing one late adjustment on a low-value row may be acceptable. Missing a back-dated credit that changes revenue recognition is not.
The cutoff is a risk decision, not a pure data decision
How do you choose the right incremental key when the source system has late updates, back-dated transactions, and no reliable modified timestamp? You choose the cutoff by measuring the delay distribution, then setting the window wider than the 95th or 99th percentile of late arrival, depending on how expensive misses are.
That sounds abstract, so make it concrete:
- If 95% of updates arrive within 2 days, a 3-day lookback may be enough for low-risk reporting.
- If 99% arrive within 7 days, a 10-day lookback is a safer starting point.
- If corrections can land months later, a fixed lookback alone is not enough. You need a different design, usually CDC or periodic reconciliation.
The real question is not “how far back can we re-read forever?” It is “how much duplicate processing can we afford to avoid one missed change?”
A simple way to size it:
| Change pattern | Suggested cutoff approach | Why | |---|---|---| | Mostly same-day updates | 1 to 2 days overlap | Cheap, catches minor lag | | Weekly back-dated corrections | 7 to 14 days overlap | Covers normal business rework | | Irregular imports and manual fixes | Re-read by business period, not days | Corrections cluster around closed periods | | Unbounded historical edits | CDC or full refresh by partition | Watermark alone will drift out of trust |
If you are dealing with B2B ordering data, this is especially relevant. Orders get amended after submission, delivery dates move, and finance may reopen a period. A flat “modified timestamp > last run” rule often misses the exact records stakeholders care about most.
For a related problem, How to Tell Extraction Failure Cause: API vs Schema is useful when the pipeline suddenly starts failing and you need to know whether the source changed or the extractor did.
Watermark, CDC, or full refresh, choose the least fragile option
The practical way to choose between a watermark, CDC, or full refresh is to ask three questions: can the source emit changes reliably, can you tolerate misses, and can you afford reprocessing.
Use a watermark when:
- the source has a stable ordering field,
- late data is bounded,
- and you can tolerate a small overlap window.
This is the common path for reporting tables, order headers, and event logs where the business process is fairly disciplined. It is also the cheapest to run.
Use CDC when:
- updates and deletes matter,
- the source can emit row-level change events,
- and you need a defensible audit trail.
CDC is the right answer when the business cares about exact state transitions, not just the latest snapshot. It is usually the better fit for finance-adjacent tables, inventory movements, and customer master data, provided the source system and connector actually support it. If you are building against ERP APIs, How to Design API Extractors for ERP Rate Limits is the companion problem, because rate limits often decide whether CDC-like polling is even practical.
Use a full refresh when:
- the table is small enough,
- the data is unstable,
- or the source cannot prove change order.
Full refresh is not a failure. It is a deliberate choice when the cost of wrong incrementals is higher than the cost of reloading. For some dimension tables, daily or hourly full refreshes are simpler and safer than pretending a bad key is good enough.
A good rule: if you cannot explain how deletes, back-dated updates, and duplicate imports are handled, you do not have a real incremental strategy yet.
The source quirks that make a key look safe, then break it
A key can look reliable in testing and fail in production because test data is usually too clean. The source system quirks that cause trouble are the ones people only notice after go-live.
Common traps:
- Batch imports that stamp every row with the same create time.
- Manual corrections that update history without touching the modified timestamp.
- Time zone drift between application time, database time, and API payload time.
- Preallocated IDs that are assigned before the row is actually committed.
- Reprocessing jobs that replay old transactions with new ingestion timestamps.
- Soft deletes where the row still exists but the business meaning is gone.
- Period-end adjustments that back-date entries into closed periods.
These are the reasons an incremental key can pass a month of testing and still fail when finance closes the quarter. The sample set usually contains current records, not the ugly edge cases.
If you are working with Australia-based businesses, watch especially for systems that mix local business time with UTC API timestamps. A record created at 11:30 pm AEST can slide across a date boundary once it lands in storage, and that is enough to break a date-based watermark if no one notices the conversion rule.
How much risk are you taking with a “mostly works” key?
You are accepting silent data loss risk every time you choose a key that cannot represent all changes. The size of that risk depends on what the source can do behind your back.
A “mostly works” key is acceptable when:
- the missed rows are low value,
- the downstream use is exploratory,
- and there is a reconciliation step that catches drift.
It is not acceptable when:
- the table drives revenue, invoicing, inventory, or compliance,
- late changes are common,
- or stakeholders make decisions off near-real-time numbers.
The uncomfortable truth is that many incremental loads fail quietly. The pipeline succeeds, the row counts look plausible, and the one back-dated credit note that matters never lands. That is why row counts alone are a weak control.
A better control set is:
- compare source and target counts by business date,
- track max business date and max load date,
- hash or aggregate by business key,
- and reconcile a sample of late-arriving records each run.
That is the difference between “it seems fine” and “we can defend this load”.
Put proof around the key before you trust it
How do you choose the right incremental key when the source system has late updates, back-dated transactions, and no reliable modified timestamp? You prove it with monitoring, not faith.
At minimum, put these checks in place:
- Lag histogram: measure how late updates arrive, by day and by source object.
- Late-arrival count: count rows that fall inside the overlap window after the first load.
- Duplicate match rate: how many rows are being reprocessed and merged.
- Unexpected backfill alert: flag rows whose business date is older than your normal window.
- Source-to-target reconciliation: compare counts and totals by period, not just in aggregate.
- Schema drift watch: if the source adds a field or changes semantics, confirm the key still behaves.
If the overlap window keeps growing, that is a signal, not noise. It means the source process is changing, and your incremental assumption may no longer hold.
For lakehouse teams, this is where Databricks jobs and Power BI semantic models need to agree on freshness rules. If the model says “today’s numbers” but the load only catches 80% of late changes, the dashboard will be wrong in a way that looks authoritative. That is the worst kind of failure.
A workable decision path
If you need a practical sequence, use this:
- Identify the business event that defines truth for the table.
- Test whether the source field for that event is stable under corrections and back-dates.
- If not, look for CDC or a reliable insert sequence.
- If neither exists, use a watermark with a measured overlap window.
- Deduplicate on a stable business key.
- Reconcile by period and alert on drift.
- Revisit the rule when the source process changes.
That is the real answer to how do you choose the right incremental key when the source system has late updates, back-dated transactions, and no reliable modified timestamp? You do not pick a single magic column and walk away. You choose the least fragile rule, then build checks around its known failure modes.
Common questions
Should I use transaction time or insert time as the cutoff?
Use transaction time if the business cares about when the event happened, but only if back-dated entries are bounded and you can re-read an overlap window. Use insert time if the table is append-only and late corrections are rare.
How far back should my incremental re-read window go?
Set it from observed lateness, not guesswork. Start with the 95th or 99th percentile of late arrivals, then widen it if the cost of missing a row is higher than the cost of reprocessing a few duplicates.
When is CDC better than a watermark?
CDC is better when updates and deletes matter and the source can emit reliable change events. If the source cannot prove change order, a watermark with reconciliation is usually safer than pretending you have CDC.
What if the key works in testing but fails later?
Assume the test data was too clean. Check for batch imports, manual corrections, time zone shifts, and soft deletes, then validate against real late-arriving records from production before you trust the load.
Make the load defensible, then make it boring
If the source is messy enough that the incremental key is a judgement call, treat it like one. Measure lateness, set the overlap, reconcile the totals, and keep a rollback path for the rare table that needs CDC or a full refresh.
If you want help designing the rule and the checks around it, our Data Analytics & Lakehouses work is built for exactly this kind of ERP and operational data problem. Book a call, and we can map the source behaviour to the right incremental strategy before the pipeline starts quietly losing trust.
