Browse cheat sheets · Data Engineering

Technology: Data EngineeringCategory: Data IntegrationDifficulty: IntermediateUpdated

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
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Full reload baseline
  5. 2. High-water mark using an increasing ID
  6. 3. Do not assume every ID is a valid high-water mark
  7. 4. Timestamp watermark
  8. 5. Timestamp + unique tie-breaker
  9. 6. The tie-breaker must match sort order
  10. 7. Do not use MAX(updated_at) alone without thinking about ties
  11. 8. Half-open batch windows
  12. 9. Half-open windows do not solve late arrivals
  13. 10. Overlap windows for late arrivals
  14. 11. Sliding overlap example
  15. 12. Persist checkpoint after success
  16. 13. Checkpoint should describe the completed scope
  17. 14. Keep checkpoint state durable
  18. 15. Use one checkpoint per logical stream
  19. 16. File-manifest incremental loading
  20. 17. Filename alone may not prove uniqueness
  21. 18. File-path partitioning can simplify incremental discovery
  22. 19. CDC pattern
  23. 20. CDC does not automatically mean streaming
  24. 21. Use source-native sequence/LSN/version when available
  25. 22. Do not transform source-native checkpoint into a weaker timestamp
  26. 23. Snapshot-diff pattern
  27. 24. Hash-diff pattern
  28. 25. Hash only relevant columns
  29. 26. Normalize values before hashing
  30. 27. MERGE for inserts and updates
  31. 28. Use explicit mappings for controlled MERGE
  32. 29. Update only when source is newer
  33. 30. Validate MERGE source uniqueness
  34. 31. Deterministically deduplicate incremental input
  35. 32. Incremental append for immutable events
  36. 33. Append still needs duplicate strategy
  37. 34. Deletes require explicit design
  38. 35. Soft-delete pattern
  39. 36. Avoid source-vs-target delete reconciliation on partial source extracts
  40. 37. Snapshot reconciliation can detect deletes
  41. 38. Incremental partition overwrite
  42. 39. Do not full-overwrite for a one-partition change
  43. 40. Incremental loads need idempotency
  44. 41. Append-only jobs need duplicate protection for retries
  45. 42. MERGE is not automatically idempotent
  46. 43. Checkpoint retries carefully
  47. 44. Two-phase-like mindset
  48. 45. Persist batch metadata
  49. 46. Reconcile row counts
  50. 47. Reconcile business totals too
  51. 48. Never advance checkpoint if blocking quality checks fail
  52. 49. Separate warning vs blocking quality checks
  53. 50. Late-arriving data pattern
  54. 51. Backfill must not corrupt live checkpoint
  55. 52. Backfill should be explicit
  56. 53. Backfill can reuse the same transformation logic
  57. 54. Protect against live/backfill write overlap
  58. 55. Staging before MERGE
  59. 56. Give staging data a batch ID
  60. 57. Do not truncate shared staging casually
  61. 58. Source updates can arrive out of order
  62. 59. Ingestion time is not source recency
  63. 60. CDC update ordering matters
  64. 61. Event-time incremental reads are risky for mutable entities
  65. 62. Created-at watermark fits append-only entities
  66. 63. Use source commit time when available
  67. 64. Clock skew can break timestamp watermarks
  68. 65. Avoid using current_timestamp() as source watermark
  69. 66. Capture batch cutoff before source read
  70. 67. Use lagged cutoffs when source commits settle late
  71. 68. Pagination and incremental load are different
  72. 69. Preserve pagination cursor until bounded extract completes
  73. 70. API incremental extraction example
  74. 71. API cursors can expire
  75. 72. Rate limits affect incremental load design
  76. 73. Incremental load should be resumable
  77. 74. Persist failure scope
  78. 75. Do not manually edit checkpoints casually
  79. 76. Checkpoint repair is a production change
  80. 77. Incremental loads need observability
  81. 78. Sudden zero changes may be suspicious
  82. 79. Sudden huge change volume may be suspicious
  83. 80. Checkpoint monotonicity
  84. 81. Keep checkpoint scope aligned with source filter
  85. 82. Schema drift can break incremental loads silently
  86. 83. Changing watermark field is a migration
  87. 84. MERGE key changes are dangerous
  88. 85. Incremental logic should be deterministic
  89. 86. SCD Type 1 incremental pattern
  90. 87. SCD Type 2 incremental pattern
  91. 88. SCD Type 2 requires duplicate/version discipline
  92. 89. Snapshot fact loads can use partition replacement
  93. 90. Audit fact loads often use append
  94. 91. Separate ingestion checkpoint from transformation checkpoint when needed
  95. 92. Layered checkpoints can improve recovery
  96. 93. Checkpoint progress is part of freshness
  97. 94. Incremental reads should be bounded when possible
  98. 95. Bounded extraction supports reproducible retries
  99. 96. Rebuild strategy should exist
  100. 97. Incremental is an optimization, not the source of truth
  101. 98. Test failure scenarios
  102. 99. Use a repeatable implementation checklist
  103. What the result means
  104. What to look for
  105. Rows are missing after a restart
  106. Duplicate rows appear
  107. Old values overwrite newer ones
  108. Deletes never reach target
  109. Incremental job suddenly reads everything
  110. Job reads zero rows unexpectedly
  111. Production notes
  112. Checkpoints are production data
  113. Idempotency is what makes retries safe
  114. Source timestamps are not always trustworthy
  115. Late data is normal in distributed systems
  116. Deletes need explicit semantics
  117. Backfills are part of the architecture
  118. Common mistakes
  119. 1. Using MAX(updated_at) as the only checkpoint
  120. 2. Advancing checkpoint before the write succeeds
  121. 3. Assuming MERGE makes every retry safe
  122. 4. Using created date for mutable records
  123. 5. Ignoring deletes
  124. 6. Treating ingestion time as source version
  125. 7. Manually editing watermarks without a replay plan
  126. 8. Letting backfills modify live checkpoints
  127. 9. Using file name alone as processed identity
  128. 10. Having no bounded retry scope
  129. Next step
  130. 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:

  • 429
  • Retry-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.

Next step

Use the source capability and failure symptom to choose the next action.

  • Immutable ordered events → use a source sequence/high-water mark.
  • Reliable updated timestamp → use timestamp + deterministic tie-breaker.
  • Source provides CDC/LSN/version → prefer the native change position.
  • File source → use a manifest with durable file identity.
  • Full snapshots only → use staged snapshot diff/hash comparison.
  • Need inserts and updates → use deterministic MERGE/upsert.
  • Need deletes → add tombstone/CDC/snapshot reconciliation semantics.
  • Late rows are being missed → add overlap or move to a stronger source sequence.
  • Retries create duplicates → strengthen idempotency and key handling.
  • Backfill is required → use explicit historical scope without changing live checkpoint.
  • Incremental logic is becoming too complex → reassess whether a bounded full/partition rebuild is simpler and safer.

← All cheat sheets