Browse cheat sheets · Data Engineering
Incremental Load Patterns
Design safe incremental loads using watermarks, CDC, high-water marks, MERGE, idempotent writes, late-data handling, and replayable checkpoints without skipping or duplicating data.
On this page
- Problem
- Quick answer
- Code / commands
- 1. Full reload baseline
- 2. High-water mark using an increasing ID
- 3. Do not assume every ID is a valid high-water mark
- 4. Timestamp watermark
- 5. Timestamp + unique tie-breaker
- 6. The tie-breaker must match sort order
- 7. Do not use MAX(updated_at) alone without thinking about ties
- 8. Half-open batch windows
- 9. Half-open windows do not solve late arrivals
- 10. Overlap windows for late arrivals
- 11. Sliding overlap example
- 12. Persist checkpoint after success
- 13. Checkpoint should describe the completed scope
- 14. Keep checkpoint state durable
- 15. Use one checkpoint per logical stream
- 16. File-manifest incremental loading
- 17. Filename alone may not prove uniqueness
- 18. File-path partitioning can simplify incremental discovery
- 19. CDC pattern
- 20. CDC does not automatically mean streaming
- 21. Use source-native sequence/LSN/version when available
- 22. Do not transform source-native checkpoint into a weaker timestamp
- 23. Snapshot-diff pattern
- 24. Hash-diff pattern
- 25. Hash only relevant columns
- 26. Normalize values before hashing
- 27. MERGE for inserts and updates
- 28. Use explicit mappings for controlled MERGE
- 29. Update only when source is newer
- 30. Validate MERGE source uniqueness
- 31. Deterministically deduplicate incremental input
- 32. Incremental append for immutable events
- 33. Append still needs duplicate strategy
- 34. Deletes require explicit design
- 35. Soft-delete pattern
- 36. Avoid source-vs-target delete reconciliation on partial source extracts
- 37. Snapshot reconciliation can detect deletes
- 38. Incremental partition overwrite
- 39. Do not full-overwrite for a one-partition change
- 40. Incremental loads need idempotency
- 41. Append-only jobs need duplicate protection for retries
- 42. MERGE is not automatically idempotent
- 43. Checkpoint retries carefully
- 44. Two-phase-like mindset
- 45. Persist batch metadata
- 46. Reconcile row counts
- 47. Reconcile business totals too
- 48. Never advance checkpoint if blocking quality checks fail
- 49. Separate warning vs blocking quality checks
- 50. Late-arriving data pattern
- 51. Backfill must not corrupt live checkpoint
- 52. Backfill should be explicit
- 53. Backfill can reuse the same transformation logic
- 54. Protect against live/backfill write overlap
- 55. Staging before MERGE
- 56. Give staging data a batch ID
- 57. Do not truncate shared staging casually
- 58. Source updates can arrive out of order
- 59. Ingestion time is not source recency
- 60. CDC update ordering matters
- 61. Event-time incremental reads are risky for mutable entities
- 62. Created-at watermark fits append-only entities
- 63. Use source commit time when available
- 64. Clock skew can break timestamp watermarks
- 65. Avoid using current_timestamp() as source watermark
- 66. Capture batch cutoff before source read
- 67. Use lagged cutoffs when source commits settle late
- 68. Pagination and incremental load are different
- 69. Preserve pagination cursor until bounded extract completes
- 70. API incremental extraction example
- 71. API cursors can expire
- 72. Rate limits affect incremental load design
- 73. Incremental load should be resumable
- 74. Persist failure scope
- 75. Do not manually edit checkpoints casually
- 76. Checkpoint repair is a production change
- 77. Incremental loads need observability
- 78. Sudden zero changes may be suspicious
- 79. Sudden huge change volume may be suspicious
- 80. Checkpoint monotonicity
- 81. Keep checkpoint scope aligned with source filter
- 82. Schema drift can break incremental loads silently
- 83. Changing watermark field is a migration
- 84. MERGE key changes are dangerous
- 85. Incremental logic should be deterministic
- 86. SCD Type 1 incremental pattern
- 87. SCD Type 2 incremental pattern
- 88. SCD Type 2 requires duplicate/version discipline
- 89. Snapshot fact loads can use partition replacement
- 90. Audit fact loads often use append
- 91. Separate ingestion checkpoint from transformation checkpoint when needed
- 92. Layered checkpoints can improve recovery
- 93. Checkpoint progress is part of freshness
- 94. Incremental reads should be bounded when possible
- 95. Bounded extraction supports reproducible retries
- 96. Rebuild strategy should exist
- 97. Incremental is an optimization, not the source of truth
- 98. Test failure scenarios
- 99. Use a repeatable implementation checklist
- What the result means
- What to look for
- Rows are missing after a restart
- Duplicate rows appear
- Old values overwrite newer ones
- Deletes never reach target
- Incremental job suddenly reads everything
- Job reads zero rows unexpectedly
- Production notes
- Checkpoints are production data
- Idempotency is what makes retries safe
- Source timestamps are not always trustworthy
- Late data is normal in distributed systems
- Deletes need explicit semantics
- Backfills are part of the architecture
- Common mistakes
- 1. Using MAX(updated_at) as the only checkpoint
- 2. Advancing checkpoint before the write succeeds
- 3. Assuming MERGE makes every retry safe
- 4. Using created date for mutable records
- 5. Ignoring deletes
- 6. Treating ingestion time as source version
- 7. Manually editing watermarks without a replay plan
- 8. Letting backfills modify live checkpoints
- 9. Using file name alone as processed identity
- 10. Having no bounded retry scope
- Next step
- Related deep dives
Problem
A full reload is becoming too expensive, too slow, or too disruptive, so the pipeline must process only new or changed data.
The hard part is not reading fewer rows.
The real engineering problems are:
- choosing a reliable change boundary
- preventing skipped records
- preventing duplicate loads
- handling timestamp ties
- handling updates and deletes
- recovering after failure
- supporting late-arriving data
- coordinating retries and backfills
- keeping source and target state consistent
The goal is to make the incremental boundary explicit, durable, and replayable.
Quick answer
Choose the pattern based on what the source can reliably provide:
Source has immutable increasing ID
→ high-water mark
Source has reliable updated_at timestamp
→ timestamp watermark + deterministic tie-breaker
Source exposes CDC/change feed
→ consume change records with durable checkpoint
Source delivers files
→ file manifest / batch checkpoint
Source can return full snapshots only
→ snapshot comparison / hash-diff / staged MERGE
Target needs inserts + updates
→ MERGE / upsert
Deletes must propagate
→ CDC tombstones or explicit source-vs-target reconciliation
The most important rule:
advance the checkpoint only after
the target write and required validation succeed
Code / commands
1. Full reload baseline
Before introducing incremental complexity, verify whether a full rebuild is still practical.
Conceptual pattern:
read complete source
→ transform
→ validate
→ replace target
Full reload is often simpler and more reliable for:
- small datasets
- slowly growing dimensions
- low-frequency jobs
- rebuildable Gold tables
Do not implement CDC or watermark logic when a safe full rebuild is inexpensive.
2. High-water mark using an increasing ID
Suppose the source guarantees an immutable increasing key:
order_event_id
Persist:
last_successful_id = 450000
Next query:
SELECT
order_event_id,
order_id,
event_type,
event_time
FROM source_order_events
WHERE order_event_id > @last_successful_id
ORDER BY order_event_id;
After successful processing:
last_successful_id = max(order_event_id processed)
This is simple and robust when the source truly guarantees monotonic IDs.
3. Do not assume every ID is a valid high-water mark
A key may be unique but not ordered.
Examples:
random UUID
GUID
hash
external reference
Do not use:
id > last_id
unless the source guarantees meaningful ordering.
4. Timestamp watermark
Common pattern:
SELECT
order_id,
updated_at,
amount,
status
FROM source_orders
WHERE updated_at > @last_watermark;
This looks simple but can skip rows when multiple records share the same timestamp.
Use a tie-breaker.
5. Timestamp + unique tie-breaker
Persist:
last_updated_at
last_order_id
Query:
SELECT
order_id,
updated_at,
amount,
status
FROM source_orders
WHERE
updated_at > @last_updated_at
OR (
updated_at = @last_updated_at
AND order_id > @last_order_id
)
ORDER BY
updated_at,
order_id;
This gives deterministic continuation across timestamp ties.
6. The tie-breaker must match sort order
Ascending example:
updated_at ASC
order_id ASC
then continuation is:
updated_at > last_updated_at
OR
updated_at = last_updated_at AND order_id > last_order_id
If sort direction changes, predicate direction must also change.
7. Do not use MAX(updated_at) alone without thinking about ties
Failure example:
row A updated_at = 10:00:00
row B updated_at = 10:00:00
pipeline processes only A
checkpoint becomes 10:00:00
next query uses:
updated_at > 10:00:00
Row B is skipped.
Persist enough state to resume deterministically.
8. Half-open batch windows
Another pattern:
[start, end)
Query:
SELECT *
FROM source_orders
WHERE updated_at >= @start_time
AND updated_at < @end_time;
After success:
next start = previous end
This works well when:
- processing is time-window based
- source timestamps are reliable
- late arrivals are handled separately
9. Half-open windows do not solve late arrivals
Example:
event happened at 10:30
arrives in source at 12:15
If your 10:00–11:00 batch already finished, the record may be missed unless the source timestamp reflects arrival/update time or the pipeline intentionally re-reads overlap.
10. Overlap windows for late arrivals
Pattern:
last successful end = 12:00
next read starts at 11:45
Then:
re-read recent data
→ deduplicate/upsert
Overlap can improve resilience to delayed source updates.
It requires idempotent target writes.
Do not pick an arbitrary overlap duration; base it on observed source lateness.
11. Sliding overlap example
SELECT *
FROM source_orders
WHERE updated_at >= DATEADD(minute, -30, @last_successful_end)
AND updated_at < @current_end;
This is illustrative only.
The overlap length should come from source behavior and business SLA.
12. Persist checkpoint after success
Bad:
1. compute new watermark
2. save watermark
3. write target
4. target write fails
The next run may skip data.
Correct:
1. read bounded source
2. transform
3. validate
4. write target
5. reconcile
6. save checkpoint
13. Checkpoint should describe the completed scope
Useful fields:
pipeline_name
source_name
last_successful_watermark
last_successful_tiebreaker
batch_id
completed_at
row_count
status
This helps audit and replay.
14. Keep checkpoint state durable
Do not store critical watermark state only in:
notebook variable
local temp file
memory
Use durable storage such as:
- metadata table
- control table
- orchestrator state
- durable checkpoint storage
15. Use one checkpoint per logical stream
Bad:
all tenants share one watermark
if tenants progress independently.
Better:
pipeline + source + tenant
→ own checkpoint
only when separate progression is required.
16. File-manifest incremental loading
For file-based sources, track processed files.
Example metadata:
source_file
file_size
checksum
received_at
processed_at
batch_id
status
Then process only new/unprocessed files.
17. Filename alone may not prove uniqueness
A source may overwrite:
orders.csv
with new content.
Use stronger identity where available:
- source version
- checksum
- file modification metadata
- immutable delivery path
- manifest ID
18. File-path partitioning can simplify incremental discovery
Example:
/landing/orders/2026/10/04/*.parquet
If the source guarantees immutable date folders, the pipeline can discover new partitions.
Still track processed state; directory existence alone does not prove successful ingestion.
19. CDC pattern
CDC may emit:
INSERT
UPDATE
DELETE
with ordering/version information.
Conceptual event:
{
"operation": "UPDATE",
"orderId": "123",
"commitVersion": 9001,
"updatedAt": "2026-10-04T14:00:00Z"
}
Persist the CDC position/checkpoint after successful application.
20. CDC does not automatically mean streaming
You can consume CDC:
continuously
or:
every 5 minutes
hourly
The source change mechanism and processing cadence are separate decisions.
21. Use source-native sequence/LSN/version when available
A reliable source-native ordering token is often safer than application timestamps.
Examples conceptually:
LSN
commit version
change sequence number
offset
Use the source’s documented ordering semantics.
22. Do not transform source-native checkpoint into a weaker timestamp
If the source provides exact ordered CDC positions, keep them.
Converting:
LSN → timestamp
can lose ordering precision.
23. Snapshot-diff pattern
If the source exposes only current full state:
current snapshot
vs
prior trusted snapshot
Compare:
- new rows
- changed rows
- missing rows/deletes
This is more expensive but can support incremental target updates.
24. Hash-diff pattern
Compute a hash from business-relevant columns:
from pyspark.sql import functions as F
snapshot = snapshot.withColumn(
"row_hash",
F.sha2(
F.concat_ws(
"||",
F.coalesce(F.col("status"), F.lit("<NULL>")),
F.coalesce(F.col("amount").cast("string"), F.lit("<NULL>")),
F.coalesce(F.col("currency"), F.lit("<NULL>")),
),
256,
)
)
Compare hash by business key to detect changed rows.
Canonicalization matters.
25. Hash only relevant columns
Do not include volatile metadata such as:
ingestion timestamp
pipeline run ID
load timestamp
or every row will appear changed.
Hash only source/business fields that define change.
26. Normalize values before hashing
Potential instability:
1
1.0
1.00
or:
NULL
empty string
may represent different or identical business states depending on rules.
Canonicalize intentionally.
27. MERGE for inserts and updates
PySpark + Delta:
from delta.tables import DeltaTable
target = DeltaTable.forName(
spark,
"silver.orders",
)
(
target.alias("t")
.merge(
source.alias("s"),
"t.order_id = s.order_id",
)
.whenMatchedUpdateAll()
.whenNotMatchedInsertAll()
.execute()
)
Validate source uniqueness first.
28. Use explicit mappings for controlled MERGE
Safer for governed schemas:
(
target.alias("t")
.merge(
source.alias("s"),
"t.order_id = s.order_id",
)
.whenMatchedUpdate(
set={
"status": "s.status",
"amount": "s.amount",
"updated_at": "s.updated_at",
}
)
.whenNotMatchedInsert(
values={
"order_id": "s.order_id",
"status": "s.status",
"amount": "s.amount",
"updated_at": "s.updated_at",
}
)
.execute()
)
This reduces accidental propagation of unwanted columns.
29. Update only when source is newer
(
target.alias("t")
.merge(
source.alias("s"),
"t.order_id = s.order_id",
)
.whenMatchedUpdate(
condition="s.updated_at > t.updated_at",
set={
"status": "s.status",
"amount": "s.amount",
"updated_at": "s.updated_at",
},
)
.whenNotMatchedInsertAll()
.execute()
)
Use only if updated_at reliably represents source recency.
30. Validate MERGE source uniqueness
duplicates = (
source
.groupBy("order_id")
.count()
.filter(F.col("count") > 1)
)
if duplicates.limit(1).count() > 0:
raise ValueError(
"Incremental source contains duplicate order_id values"
)
Ambiguous source versions should be resolved before MERGE.
31. Deterministically deduplicate incremental input
Example:
from pyspark.sql.window import Window
w = (
Window
.partitionBy("order_id")
.orderBy(
F.col("updated_at").desc(),
F.col("source_version").desc(),
F.col("_ingested_at").desc(),
)
)
latest = (
source
.withColumn(
"_rn",
F.row_number().over(w),
)
.filter(F.col("_rn") == 1)
.drop("_rn")
)
Use reliable ordering columns.
32. Incremental append for immutable events
If data never changes:
(
events.write
.format("delta")
.mode("append")
.saveAsTable("bronze.events")
)
Append is simpler than MERGE when the source is truly immutable and duplicates are controlled.
33. Append still needs duplicate strategy
Retries may re-send the same event.
Use:
stable event_id
and downstream deduplication/idempotency where required.
34. Deletes require explicit design
If the source can delete rows, a timestamp-only “updated rows” query may never tell you what disappeared.
Options:
CDC delete/tombstone
soft-delete flag
source snapshot reconciliation
separate delete feed
Do not assume missing rows mean deletes unless the source contract says so.
35. Soft-delete pattern
Source:
is_deleted = 1
Then:
(
target.alias("t")
.merge(
source.alias("s"),
"t.order_id = s.order_id",
)
.whenMatchedDelete(
condition="s.is_deleted = true"
)
.whenMatchedUpdateAll()
.whenNotMatchedInsertAll()
.execute()
)
Only delete when the source explicitly communicates deletion semantics.
36. Avoid source-vs-target delete reconciliation on partial source extracts
Bad:
today's source has 1,000 rows
target has 1,000,000 rows
missing from today's source
→ delete from target
This is wrong unless today’s source is a complete authoritative snapshot.
37. Snapshot reconciliation can detect deletes
If the source snapshot is authoritative:
source current keys
vs
target current keys
Then target-only keys may be deletions.
Validate scope:
tenant
date
source system
before applying deletes.
38. Incremental partition overwrite
If data is naturally partitioned by date and one date is authoritative:
rebuild 2026-10-04 partition
→ replace only that partition
This can be simpler than row-level MERGE.
Use the platform’s safe partition-replacement mechanism.
39. Do not full-overwrite for a one-partition change
Bad:
read entire table
union replacement date
overwrite whole table
This increases cost and risk.
Use targeted replacement.
40. Incremental loads need idempotency
Desired property:
run same input twice
→ same final target state
Idempotency makes retries safer.
41. Append-only jobs need duplicate protection for retries
Possible approaches:
event_id uniqueness
batch_id + source key
manifest tracking
dedupe in Silver
Choose according to source semantics.
42. MERGE is not automatically idempotent
If source contains duplicates or unstable values, repeated MERGE can still produce incorrect results.
Idempotency depends on:
- stable keys
- deterministic source winner
- update rules
- target semantics
43. Checkpoint retries carefully
Scenario:
target write succeeds
checkpoint write fails
Next run may re-read the same data.
Your target write must tolerate replay.
This is another reason idempotency matters.
44. Two-phase-like mindset
A robust incremental pipeline often behaves like:
prepare bounded batch
→ write idempotently
→ validate/reconcile
→ commit checkpoint
Avoid treating checkpoint update as an unrelated afterthought.
45. Persist batch metadata
Useful control table fields:
batch_id
source_start
source_end
source_row_count
target_row_count
status
started_at
completed_at
checkpoint_before
checkpoint_after
code_version
This supports replay and audit.
46. Reconcile row counts
Example:
source rows read
source duplicates removed
invalid rows quarantined
rows inserted
rows updated
rows deleted
target rows in scope
Make the movement explainable.
47. Reconcile business totals too
For financial data:
count
sum(amount)
sum(fees)
currency totals
by batch/date/tenant.
Row count alone can miss duplicated monetary values.
48. Never advance checkpoint if blocking quality checks fail
Correct:
source read
→ transform
→ quality gate fails
→ batch marked failed
→ checkpoint unchanged
This preserves replayability.
49. Separate warning vs blocking quality checks
Example:
missing business key
→ blocking
unusual row-count variance
→ warning
Do not freeze every pipeline for every anomaly.
50. Late-arriving data pattern
Measure:
source_updated_at
vs
ingestion_time
If late arrivals are common:
- overlap reads
- use source-native CDC
- revisit watermark field
- schedule reconciliation
Do not ignore them.
51. Backfill must not corrupt live checkpoint
Keep:
live checkpoint
separate from:
backfill start/end
A historical replay should not move the live watermark backward.
52. Backfill should be explicit
Example:
backfill_start = 2026-09-01
backfill_end = 2026-09-08
Record scope and reason.
53. Backfill can reuse the same transformation logic
Ideal pattern:
same Silver transformation
different input boundary
Avoid maintaining one code path for live loads and another for historical repair unless necessary.
54. Protect against live/backfill write overlap
If both modify the same keys/partitions:
- coordinate schedules
- use version-aware MERGE
- isolate staging
- define winner rules
Do not assume table-level ACID alone resolves business correctness.
55. Staging before MERGE
Useful pattern:
extract incremental source
→ write staging table
→ validate
→ MERGE staging into target
Benefits:
- replayable batch
- easier diagnostics
- isolated source snapshot
56. Give staging data a batch ID
Example:
staging.orders_incremental
batch_id = 20261004_1400
This makes retries and investigations easier.
57. Do not truncate shared staging casually
If multiple runs can overlap, use:
batch-scoped staging
rather than one shared mutable staging area.
58. Source updates can arrive out of order
Example:
version 5 arrives
then version 4 arrives later
If possible, use:
source version / sequence
rather than arrival time to choose the winner.
59. Ingestion time is not source recency
_ingested_at answers:
when platform saw record
not:
which source version is newer
Use it only as a final tie-breaker when appropriate.
60. CDC update ordering matters
If the source emits:
LSN 100
LSN 101
LSN 102
apply/reconcile based on source order.
Do not sort only by business timestamp if CDC order is authoritative.
61. Event-time incremental reads are risky for mutable entities
If an order created yesterday is updated today but you filter only by:
created_at > yesterday
you miss the update.
Use:
updated_at
CDC
version
for mutable records.
62. Created-at watermark fits append-only entities
Good when:
records never change after creation
Examples may include immutable audit/event rows.
Still account for delayed creation visibility if source commit/replication lag exists.
63. Use source commit time when available
Application timestamps may be:
- user-supplied
- backdated
- timezone-shifted
Database/source commit metadata can be more reliable for incremental capture where supported.
64. Clock skew can break timestamp watermarks
Distributed systems may write timestamps from machines with different clocks.
If reliable ordering matters, prefer source-native sequence/version.
65. Avoid using current_timestamp() as source watermark
This records pipeline time, not necessarily source boundary.
Use a source-derived or explicitly bounded cutoff.
66. Capture batch cutoff before source read
Pattern:
end = source-safe cutoff
read [start, end)
This avoids a moving query window during the run.
67. Use lagged cutoffs when source commits settle late
Example concept:
current time - source settlement delay
can reduce reading partially committed/replicated data.
Only use if source behavior justifies it.
68. Pagination and incremental load are different
Pagination:
how to retrieve one bounded result set in pieces
Incremental loading:
which changes belong to the next pipeline run
A pipeline may need both.
69. Preserve pagination cursor until bounded extract completes
If an API incremental range returns multiple pages:
watermark window
→ page 1
→ page 2
→ page 3
Do not advance the durable source watermark after page 1.
Finish the entire bounded interval first.
70. API incremental extraction example
Conceptual request:
GET /orders?
updated_after=2026-10-04T10:00:00Z
&updated_before=2026-10-04T11:00:00Z
&cursor=...
Process all pages for the window.
Then commit:
watermark = 11:00
71. API cursors can expire
If a cursor expires mid-window, the client needs a restart strategy.
Options:
- restart bounded window idempotently
- use durable page token if provider supports it
- split into smaller windows
Do not checkpoint only an ephemeral cursor without understanding its lifecycle.
72. Rate limits affect incremental load design
Large backlogs can generate many API calls.
Respect:
429Retry-After- concurrency limits
Do not turn a catch-up run into a denial-of-service pattern.
73. Incremental load should be resumable
A failed batch should have a known recovery point.
Ask:
What exact source range will the retry read?
What target state already exists?
Can the same source be applied again safely?
74. Persist failure scope
Example:
batch_id
start watermark
end watermark
last completed page
target write status
quality status
This improves controlled recovery.
75. Do not manually edit checkpoints casually
Changing:
last_successful_watermark
can skip or duplicate data.
If manual repair is required:
- record original value
- define replay scope
- verify target idempotency
- document reason
76. Checkpoint repair is a production change
Treat it like:
- data correction
- migration
- operational runbook step
not a casual metadata update.
77. Incremental loads need observability
Track per run:
source rows
inserted rows
updated rows
deleted rows
duplicate rows
invalid rows
duration
checkpoint before/after
late rows
This helps detect silent drift.
78. Sudden zero changes may be suspicious
Possible causes:
source legitimately quiet
watermark too far ahead
filter bug
source outage
auth/API issue
timezone mistake
Use context before declaring success.
79. Sudden huge change volume may be suspicious
Possible causes:
watermark reset
source backfill
duplicate API pages
schema change
source bug
replayed CDC position
Investigate before blindly applying.
80. Checkpoint monotonicity
For monotonic sources:
new checkpoint
>=
old checkpoint
A backward movement should be intentional backfill/recovery, not accidental live-state change.
81. Keep checkpoint scope aligned with source filter
If checkpoint is per tenant:
tenant A watermark
tenant B watermark
then reads/writes must preserve that scope.
Do not update a global watermark from one tenant’s progress.
82. Schema drift can break incremental loads silently
Example:
updated_at renamed
The pipeline may:
- fail
- read zero rows
- parse nulls
Validate required watermark columns before processing.
83. Changing watermark field is a migration
Switching:
updated_at
→ source_version
requires a controlled cutover.
Define:
- old checkpoint
- new checkpoint starting position
- overlap
- validation
Do not change live logic without replay analysis.
84. MERGE key changes are dangerous
If target key changes from:
order_id
to:
merchant_id + order_id
existing target data and incremental logic must migrate consistently.
Otherwise updates can hit wrong rows or duplicate entities.
85. Incremental logic should be deterministic
Same:
source batch
+
code version
+
reference data/version
should produce the same target result where possible.
Avoid hidden current-time dependencies.
86. SCD Type 1 incremental pattern
For overwrite-current dimensions:
business key
→ MERGE latest source values into current row
No history preserved.
Good when historical attribute changes are not required.
87. SCD Type 2 incremental pattern
Conceptually:
changed attribute
→ expire current row
→ insert new version
Typical fields:
effective_from
effective_to
is_current
This is more complex than normal MERGE.
Use when historical state is a real requirement.
88. SCD Type 2 requires duplicate/version discipline
If multiple source changes for one key arrive in one batch, order them by reliable source change sequence.
Do not assume one change per key per batch.
89. Snapshot fact loads can use partition replacement
For a daily authoritative snapshot:
snapshot_date = 2026-10-04
replace only that day’s partition.
This may be simpler than row-level change detection.
90. Audit fact loads often use append
For immutable transaction/audit events:
append new rows
with stable event keys and duplicate protection.
91. Separate ingestion checkpoint from transformation checkpoint when needed
Example:
raw API extract completed through 11:00
Silver processing completed through 10:00
These are different stages.
A single watermark may hide where backlog exists.
92. Layered checkpoints can improve recovery
Example:
Bronze source position
Silver processed position
Gold published position
Useful in multi-stage platforms.
Do not create unnecessary checkpoint complexity for simple pipelines.
93. Checkpoint progress is part of freshness
If Bronze is current but Gold watermark is stale, the user-facing data is still stale.
Monitor end-to-end progression.
94. Incremental reads should be bounded when possible
Avoid:
updated_at > last_watermark
with no fixed upper bound on a long-running job.
Instead define:
start
end
at run start where source semantics allow.
This makes replay and reconciliation easier.
95. Bounded extraction supports reproducible retries
If batch is:
[10:00, 11:00)
retry the same scope.
Do not let retry drift to:
[10:00, current_time)
unless intentional.
96. Rebuild strategy should exist
Eventually you may need:
reprocess one day
reprocess one tenant
rebuild full Silver table
Design incremental logic so these are possible.
97. Incremental is an optimization, not the source of truth
The source of truth should still be clear:
source database
raw event log
Bronze layer
authoritative files
The checkpoint only tracks progress.
98. Test failure scenarios
At minimum test:
source returns duplicates
target write succeeds but checkpoint fails
checkpoint succeeds but downstream publish fails
late record arrives after watermark
delete appears
schema changes
backfill overlaps live run
99. Use a repeatable implementation checklist
Before production:
1. Define source-of-truth
2. Choose reliable change indicator
3. Define deterministic ordering
4. Define bounded read scope
5. Define duplicate/idempotency strategy
6. Define insert/update/delete semantics
7. Define late-data policy
8. Define durable checkpoint
9. Define quality/reconciliation gate
10. Define retry/backfill procedure
If any of these are unknown, the incremental design is incomplete.
What the result means
| Pattern | Best fit | Main risk |
|---|---|---|
| Increasing ID high-water mark | Immutable ordered events | Assumed ordering may be false |
| Timestamp watermark | Mutable records with reliable update time | Ties, late updates, clock issues |
| Timestamp + tie-breaker | Ordered mutable records | More checkpoint state |
| CDC sequence/LSN | Databases/change feeds | Source-specific semantics |
| File manifest | File ingestion | Replaced files / weak file identity |
| Snapshot diff | Full current-state source | Expensive comparison |
| Hash diff | Wide snapshots | Canonicalization mistakes |
| Partition replacement | Authoritative bounded slices | Wrong overwrite scope |
| MERGE/upsert | Inserts + updates | Duplicate source keys / concurrency |
| Overlap window | Late-arriving source changes | Requires idempotent writes |
The core principle is:
incremental loading is state management
not merely a filtered query.
What to look for
Rows are missing after a restart
Check:
- checkpoint advanced too early
- timestamp ties
- late-arriving rows
- non-deterministic ordering
- pagination checkpointing
Duplicate rows appear
Check:
- replay after checkpoint failure
- append-only retry
- missing event/business key
- source duplicate records
- MERGE source duplicates
Old values overwrite newer ones
Check:
- out-of-order source updates
- use of ingestion time instead of source version
- missing “source newer than target” condition
Deletes never reach target
Check whether the source exposes:
- delete flag
- tombstone
- CDC delete
- authoritative snapshot
A simple update watermark may not capture deletions.
Incremental job suddenly reads everything
Check:
- reset/missing checkpoint
- changed source filter
- timestamp type/timezone conversion
- watermark migration bug
Job reads zero rows unexpectedly
Check:
- watermark too far ahead
- source outage
- filter timezone
- missing/renamed watermark column
- wrong tenant/source scope
Production notes
Checkpoints are production data
Protect them with:
- durability
- auditability
- controlled updates
- backups where appropriate
Idempotency is what makes retries safe
Assume a batch may be replayed.
Design the target so replay does not corrupt state.
Source timestamps are not always trustworthy
Prefer source-native sequencing when available.
Late data is normal in distributed systems
Define how far back you may need to reread/reconcile based on evidence.
Deletes need explicit semantics
Do not infer deletion from absence unless the source is a complete authoritative snapshot.
Backfills are part of the architecture
Historical repair should not require ad-hoc checkpoint hacks.
Common mistakes
1. Using MAX(updated_at) as the only checkpoint
Timestamp ties can skip rows.
2. Advancing checkpoint before the write succeeds
This can permanently skip data.
3. Assuming MERGE makes every retry safe
Source uniqueness and deterministic update rules still matter.
4. Using created date for mutable records
Updates to old rows will be missed.
5. Ignoring deletes
Target state slowly diverges from source.
6. Treating ingestion time as source version
Late-arriving older data can overwrite newer state.
7. Manually editing watermarks without a replay plan
This creates silent gaps or duplicates.
8. Letting backfills modify live checkpoints
Historical repair should be isolated from live progress state.
9. Using file name alone as processed identity
Sources may overwrite or resend the same name.
10. Having no bounded retry scope
If a retry reads a different source interval than the failed run, reconciliation becomes difficult.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.