Incremental vs Full ERP Sync Strategies

Every reconciliation feed pulled from an ERP faces the same fork: replay the entire dataset on each run, or fetch only what changed since the last watermark. Choose wrong and the cost is concrete — a full snapshot of a ten-million-row deduction ledger every fifteen minutes will saturate the source database, while a naive incremental pull silently misses hard-deleted records and slowly diverges from the system of record until a quarter-end accrual ties to nothing. This page is a step-by-step comparison of incremental (high-watermark and change-data-capture) sync against full-snapshot extraction for reconciliation feeds: how each behaves, when each is correct, and — most importantly — how to run one against the other so drift is detected before it reaches the general ledger. It is the decision-and-reconciliation companion to the POS & ERP sync patterns topic area, which frames where extracted ERP feeds meet the ledger inside the broader data ingestion normalization pipelines discipline.

When each strategy applies

The two strategies are not competitors so much as complements: incremental sync carries the steady-state load cheaply, and a periodic full sync is the audit that proves the incremental feed has not drifted. The comparison below is the decision surface.

Dimension Incremental (high-watermark / CDC) Full snapshot
What moves Only rows changed since the last watermark Every row in the source table, every run
Source load Low — indexed range scan on the watermark column High — full table scan, contends with OLTP
Latency Minutes, or seconds with log-based CDC Bounded by the whole-table extract time
Hard deletes Missed unless the source emits tombstones Detected implicitly — absent rows are gone
Correctness drift Accumulates silently between reconciliations Self-correcting — each run is ground truth
Idempotency need High — overlapping windows re-deliver rows Lower — each run replaces the prior set
Best fit High-volume, append-mostly ledgers and activity feeds Small dimensions, or the periodic drift audit
Watermark risk Clock skew and non-monotonic columns lose rows None — no watermark to trust

Incremental sync is the default for high-volume, mostly-append reconciliation sources: sales activity, accrual postings, deduction line items. Full snapshot is correct for small slowly-changing dimensions (vendor master, agreement headers) where a full scan is cheap, and — regardless of table size — as the scheduled reconciliation pass that catches everything the incremental feed missed. The rest of this page builds an incremental pull, then builds the full reconciliation that keeps it honest.

Prerequisites

Before implementing either strategy, confirm the following are in place:

  • A reliable change signal per source table. Either a monotonic high-watermark column (a database-assigned updated_at with server-side default, or a gap-free change sequence number) or a log-based CDC stream. A client-supplied timestamp is not a valid watermark — it is subject to application clock skew.
  • A stable natural key and a content hash. Every record must resolve to a stable business key (deduction id, accrual id) and a deterministic hash over its material fields, so an upsert can distinguish a genuine change from a redelivered no-op.
  • A watermark store. A small transactional table or key-value store that records the last successfully committed watermark per source, updated only after the batch is durably written downstream.
  • A tombstone or soft-delete convention. Either the source sets a deleted_at / is_deleted flag (soft delete), or the CDC stream emits delete events. Pure hard deletes with no signal are the one case incremental sync cannot see, and the reason the full reconciliation exists.
  • Python packages: pydantic>=2.6 for boundary validation and the standard-library decimal, hashlib, and datetime modules. Every monetary field uses decimal.Decimal; never float. A driver such as psycopg[binary]>=3.1 for the source pull is assumed but not shown.
  • Access role: read access to the source ERP tables (or its CDC topic) and read/write access to the canonical staging tables and the watermark store, typically the reconciliation_etl service role introduced in automating POS data extraction for CPG.

Step-by-step implementation

Step 1 — Pick a defensible high-watermark column

The watermark is the contract that makes an incremental pull correct. It must be monotonically non-decreasing and set by the source database, not the application. Prefer a gap-free change sequence number; where you must use a timestamp, use the database server clock (now() at write time), never a client-supplied value. Model the watermark explicitly so an invalid one is rejected before it drives a query.

python
from datetime import datetime, timezone
from pydantic import BaseModel, field_validator

class Watermark(BaseModel):
    source: str
    column: str            # e.g. "updated_at" or "change_seq"
    value: datetime        # last committed high-watermark, UTC

    @field_validator("value")
    @classmethod
    def must_be_utc(cls, v: datetime) -> datetime:
        if v.tzinfo is None or v.utcoffset() != timezone.utc.utcoffset(None):
            raise ValueError("watermark must be timezone-aware UTC")
        return v

Validation check: assert a naive (tzinfo is None) datetime raises ValidationError, and confirm the chosen column has a server-side default or trigger — query the source for count(*) where updated_at is null and require zero. A nullable watermark column loses every row it fails to stamp.

Step 2 — Pull the incremental delta with an overlap guard

Query rows strictly greater than the last committed watermark, but subtract a small safety lag from the upper bound. In-flight transactions can commit a row with an updated_at earlier than a later-started transaction that has already been read — the classic read-skew gap. Overlapping the window by a lag interval and relying on idempotent upserts (Step 3) closes it without losing rows.

python
from datetime import timedelta

SAFETY_LAG = timedelta(seconds=30)   # cover commit-order skew

def build_delta_query(wm: Watermark, now: datetime) -> tuple[str, dict]:
    upper = now - SAFETY_LAG
    sql = (
        f"SELECT * FROM {wm.source} "
        f"WHERE {wm.column} > %(low)s AND {wm.column} <= %(high)s "
        f"ORDER BY {wm.column} ASC"
    )
    return sql, {"low": wm.value, "high": upper}

Validation check: run two consecutive pulls whose windows overlap by the safety lag and assert every row in the overlap appears in both result sets but produces zero net changes downstream after the upsert. Then assert the new watermark advances to upper, not to now.

Step 3 — Idempotent upsert keyed on a record hash

Overlapping windows and CDC at-least-once delivery both re-deliver rows. The upsert must be idempotent: keyed on the stable natural key, it applies a change only when the content hash differs, so a redelivered no-op touches nothing. Compute the hash over the canonical, decimal-normalized fields so equal business records always hash equally.

python
import hashlib
from decimal import Decimal, ROUND_HALF_UP
from pydantic import BaseModel

class DeductionRow(BaseModel):
    deduction_id: str
    vendor_id: str
    amount: Decimal
    updated_at: datetime
    is_deleted: bool = False

    def content_hash(self) -> str:
        amt = self.amount.quantize(Decimal("0.0001"), ROUND_HALF_UP)
        material = f"{self.deduction_id}|{self.vendor_id}|{amt}|{self.is_deleted}"
        return hashlib.sha256(material.encode("utf-8")).hexdigest()

UPSERT = """
INSERT INTO stg_deduction (deduction_id, vendor_id, amount, updated_at,
                           is_deleted, row_hash)
VALUES (%(deduction_id)s, %(vendor_id)s, %(amount)s, %(updated_at)s,
        %(is_deleted)s, %(row_hash)s)
ON CONFLICT (deduction_id) DO UPDATE
   SET vendor_id  = EXCLUDED.vendor_id,
       amount     = EXCLUDED.amount,
       updated_at = EXCLUDED.updated_at,
       is_deleted = EXCLUDED.is_deleted,
       row_hash   = EXCLUDED.row_hash
 WHERE stg_deduction.row_hash <> EXCLUDED.row_hash
"""

Validation check: upsert the same DeductionRow twice and assert the second call reports zero rows affected (the WHERE row_hash <> ... guard suppresses it). Then change amount by Decimal("0.01") and assert exactly one row is updated — proving the hash covers the decimal value at full precision.

Step 4 — Detect deletes the incremental pull misses

A row deleted at the source stops appearing in the delta — its updated_at never changes, so an increment-only feed keeps the stale copy forever. Handle the two delete conventions explicitly. For soft deletes, the is_deleted flag flips and flows through the normal delta as a content-hash change (Step 3), so mark the staging row deleted rather than removing it, preserving the audit trail. For CDC, translate delete events into the same tombstone.

python
def apply_tombstone(cur, deduction_id: str, deleted_at: datetime) -> None:
    # Soft delete: never physically remove — mark, keep lineage
    cur.execute(
        "UPDATE stg_deduction "
        "   SET is_deleted = TRUE, updated_at = %(ts)s "
        " WHERE deduction_id = %(id)s AND is_deleted = FALSE",
        {"id": deduction_id, "ts": deleted_at},
    )

def deletes_from_cdc(events: list[dict]) -> list[str]:
    return [e["key"]["deduction_id"] for e in events if e.get("op") == "d"]

Validation check: flip is_deleted to true at the source, run the delta, and assert the staging row is marked deleted (not absent) and excluded from active-accrual sums. Then confirm the gap: hard-delete a row directly (no flag, no CDC event), run the delta, and assert the stale row survives — this is the drift Step 5 is designed to catch.

Step 5 — Run a periodic full reconciliation to catch drift

Because incremental sync cannot see an unsignaled hard delete and can lose a row to watermark skew, schedule a periodic full snapshot whose only job is to reconcile, not to replace. Pull the complete set of source keys and hashes, diff against staging, and emit three sets: rows the incremental feed missed, rows it has stale, and rows present downstream but gone at the source (the missed hard deletes). Apply corrections through the same idempotent upsert and tombstone paths — never by truncating and reloading.

python
from decimal import Decimal

def reconcile(source_hashes: dict[str, str],
              staged_hashes: dict[str, str]) -> dict[str, set[str]]:
    src, stg = set(source_hashes), set(staged_hashes)
    missing_downstream = src - stg                       # never arrived
    orphaned_downstream = stg - src                      # missed hard deletes
    drifted = {k for k in src & stg
               if source_hashes[k] != staged_hashes[k]}  # stale content
    return {
        "missing_downstream": missing_downstream,
        "orphaned_downstream": orphaned_downstream,
        "drifted": drifted,
    }

def drift_rate(report: dict[str, set[str]], total: int) -> Decimal:
    bad = sum(len(v) for v in report.values())
    return (Decimal(bad) / Decimal(total)).quantize(Decimal("0.0001"))

Validation check: seed a staging table with one stale row (a missed hard delete) and one missing row, run reconcile, and assert the orphaned key appears in orphaned_downstream and the missing key in missing_downstream. Alert when drift_rate exceeds a threshold (for example Decimal("0.0010")) between two consecutive full passes — a rising drift rate is the leading indicator that the watermark or delete signal is broken.

Common failure modes and fixes

  1. Missed hard deletes on the incremental feed. A row deleted at the source with no soft-delete flag and no CDC event simply stops appearing in the delta, so the stale copy lives on and inflates downstream accrual sums indefinitely. Incremental sync structurally cannot see it. Fix it with the two-part defense above: prefer soft deletes or CDC tombstones (Step 4), and run the periodic full reconciliation (Step 5) as the backstop that finds orphaned_downstream keys and tombstones them.
  2. Watermark clock skew. A watermark driven by a client-supplied or per-node timestamp advances non-monotonically when application clocks disagree, so rows written with an earlier stamp than the committed watermark are skipped forever. Use a database-server clock or a gap-free sequence for the watermark (Step 1), and overlap the read window by a safety lag (Step 2) so commit-order skew cannot open a gap.
  3. Non-idempotent upserts creating duplicates. A plain INSERT, or an upsert keyed on a surrogate load id instead of the natural key, turns every overlapping window and every CDC redelivery into a duplicate row — and duplicated deduction lines double-count against the ledger. Key the upsert on the stable business key and gate the update on a content-hash mismatch (Step 3) so redeliveries are exact no-ops.
  4. Full refresh clobbering in-flight data. Implementing the full pass as TRUNCATE + reload leaves a window where staging is empty or partial; a reconciliation query running in that window ties to nothing, and a crash mid-load leaves the feed truncated. Never truncate. Run the full pass as a read-only diff (Step 5) and apply only the corrective deltas through the same idempotent upsert, so the table is always complete and readable.
  5. Decimal precision loss in the hash or the amount. If the content hash is computed over a float amount, or amounts are cast through float on the way in, 19.99 may hash as 19.989999...; equal business records then hash unequally and thrash the upsert, or unequal ones collide. Construct every Decimal from a str, quantize with ROUND_HALF_UP before hashing (Step 3), and keep the money in decimal.Decimal end to end.

Operational checklist

Frequently asked questions

When is a full snapshot actually cheaper than an incremental pull? For small slowly-changing dimensions — vendor master, agreement headers — a full scan is trivial and avoids the watermark and tombstone machinery entirely, so full sync is simpler and just as correct. It also becomes cheaper in effect for any table when you count the cost of undetected drift: the periodic full pass is what makes an incremental feed trustworthy, so it is not optional even on large tables, only less frequent.

Can incremental sync ever be fully correct on its own? Only if the source guarantees a signal for every change including deletes — a monotonic database-assigned watermark plus soft-delete flags, or a log-based CDC stream that emits insert, update, and delete events. Absent a delete signal, an increment-only feed will miss hard deletes by construction, which is why the periodic full reconciliation exists as a backstop rather than a nicety.

Why gate the upsert on a content hash instead of just always updating? Overlapping windows and at-least-once CDC delivery re-present rows that have not changed. Always-updating churns the table, fires triggers and change events downstream, and obscures which rows genuinely moved. Gating on a hash mismatch makes a redelivered no-op cost nothing and makes the update count a true measure of real change.

How do I keep the full reconciliation from disrupting live reconciliation reads? Never implement it as truncate-and-reload, which exposes an empty or partial window. Run it as a read-only diff of source keys and hashes against staging, then apply only the corrective deltas through the same idempotent upsert and tombstone paths the incremental feed uses. Staging stays complete and queryable throughout.

Up one level: POS & ERP Sync Patterns

Related pages

Back to