Incremental Sync and Change Data Capture for EDC Pipelines

A global Phase III trial running across four hundred sites accumulates millions of ItemData values, and re-extracting all of them on every sync cycle stops being viable within the first month of enrollment: the full reload takes longer than the interval between runs, it saturates the vendor’s rate quota, and it forces a downstream reconciliation of the entire dataset just to discover which handful of fields a coordinator actually touched overnight. This page is part of Automated EDC Ingestion & Sync Pipelines, and it focuses on the discipline that replaces the full reload: capturing only what changed between one sync and the next, and doing so in a way that never silently drops a back-dated correction or double-counts a re-transmitted record. For clinical data managers, biotech and pharma ETL engineers, and regulatory reviewers, incremental sync is not merely a performance optimization — it is a data-integrity control, because a delta strategy that misses an edit produces an analysis dataset that no longer reflects the source of record, an ALCOA+ Complete and Consistent failure that an inspector will treat as a defect in validated software. The concrete cursor mechanics behind this discipline are worked end to end in Implementing Watermark Cursors for EDC Delta Sync in Python.

Delta Detection at a Glance #

Each cycle reads the persisted watermark, queries the source for records changed since that point minus a small overlap window, merges them idempotently on their natural key, and only then advances the watermark — with late-arriving edits caught by the overlap branch rather than lost.

Concepts and Prerequisites #

Incremental sync sits directly on top of the extraction transport, so the same standards knowledge that governs the rest of the ingestion path applies here. Engineers should be fluent in the CDISC Operational Data Model, because the CDISC ODM natural-key tuple is what makes an idempotent merge possible in the first place — without a stable business key you cannot distinguish an update from an insert, and every re-transmission becomes a duplicate. Familiarity with the audit-trail expectations of 21 CFR Part 11 is equally load-bearing, since the watermark itself becomes a regulated artifact: it is the record of exactly how far the pipeline has reconciled the source of record at any moment. This discipline consumes the transport guarantees built in Async Polling Strategies for EDC Updates and feeds the deterministic transformation stage documented in Pandas DataFrames for Clinical Data Cleaning.

There are three broadly distinct ways to detect what changed, and a mature pipeline usually combines them rather than betting on one:

The reference implementations below assume a pinned, version-controlled dependency set so that validated delta behavior is reproducible across IQ/OQ/PQ environments:

Two environment assumptions are non-negotiable. First, every change timestamp is normalized to UTC the instant it enters the pipeline, because an EDC tenant configured to a site’s local timezone will otherwise hand you a LastUpdatedDate that appears to move backwards across a daylight-saving boundary. Second, the watermark store is durable and transactional: a watermark that advances in memory but is lost on a crash re-reads a window that may already be closed at the source, and a watermark that advances before the merge commits skips records forever.

Implementation: Watermark Polling with an Overlap Window #

Deterministic incremental sync begins with a monotonic watermark and a deliberately conservative lower bound. Rather than querying strictly greater than the stored watermark, subtract a small overlap window so that any edit whose LastUpdatedDate was assigned during the previous run’s in-flight window is re-scanned. The overlap trades a little redundant reading for a strong guarantee that no record slips through the boundary — and because the merge is idempotent, re-reading a record that was already ingested is a harmless no-op.

# ALCOA+ requirement: Complete + Consistent — the overlap window guarantees no
# edit is skipped at the boundary, and the watermark advances only after a
# durable, committed merge so the pipeline's reconciliation point is provable.
from datetime import datetime, timedelta, timezone
import httpx
import structlog

log = structlog.get_logger("clinical_sync.cdc")


def poll_delta(client: httpx.Client, url: str, watermark: datetime,
               overlap: timedelta, study_oid: str, page_size: int = 1000):
    """Yield delta pages changed at or after (watermark - overlap), UTC-normalized."""
    lower_bound = (watermark - overlap).astimezone(timezone.utc)
    start_key = "0"
    while True:
        resp = client.get(url, params={
            "study": study_oid,
            "changedSince": lower_bound.isoformat(),   # inclusive lower bound
            "startkey": start_key,
            "count": page_size,
        })
        resp.raise_for_status()
        page = resp.json()["records"]
        if not page:
            return                                     # cycle done; do not advance yet
        log.info("delta_page", study=study_oid, lower_bound=lower_bound.isoformat(),
                 startkey=start_key, page_count=len(page))
        yield page
        start_key = page[-1]["_cursor"]                # vendor continuation token

The subtlety that separates a correct implementation from a subtly broken one is the inclusive lower bound. If the API treats changedSince as strictly greater-than, a record whose timestamp exactly equals the watermark from a prior run is never returned — and equal timestamps are common, because a coordinator saving a form writes many ItemData values in the same transaction with an identical LastUpdatedDate. Using an at-or-after bound plus the overlap window, and then deduplicating in the merge, is what closes that gap. The full treatment of equal-timestamp collisions and boundary inclusivity is worked in Implementing Watermark Cursors for EDC Delta Sync in Python.

Idempotent Merge, Soft-Deletes, and Delivery Semantics #

Detecting a change is only half the problem; applying it exactly once is the other half. Because both watermark polling and webhook delivery are at-least-once in practice — a retried delta query re-reads its window, and an event bus redelivers on an unacknowledged message — the merge must be idempotent. The mechanism is an upsert keyed on the ODM natural-key tuple: the same logical record applied twice updates in place rather than inserting a duplicate. Achieving true exactly-once end-to-end would require a distributed transaction across the vendor API and your warehouse that no EDC exposes, so the honest and defensible design is at-least-once delivery made effectively-once by an idempotent sink.

Deletes need explicit handling because a delta query keyed on LastUpdatedDate returns rows that exist; it cannot return a row that was removed. If the source performs a hard delete, that record simply stops appearing and the watermark scan will never see it — a missed-delete failure that leaves a stale subject or visit in the warehouse indefinitely. Where the vendor exposes soft-deletes (a Deleted flag or a TransactionType="Remove" in ODM), the delta must carry and honor that tombstone.

# 21 CFR Part 11 §11.10(e): a removal is a change, not an absence — soft-delete
# tombstones are applied as attributable, timestamped state transitions, never
# as physical row deletions, preserving the reconstructable audit trail.
from sqlalchemy import text
from sqlalchemy.engine import Connection

NATURAL_KEY = ("study_oid", "site_ref", "subject_key",
               "event_oid", "form_oid", "item_group_oid", "item_oid")


def merge_record(conn: Connection, rec: dict) -> str:
    """Idempotent upsert on the ODM natural key; soft-delete sets a tombstone."""
    is_delete = rec.get("transaction_type") == "Remove"
    conn.execute(text("""
        INSERT INTO item_data
            (study_oid, site_ref, subject_key, event_oid, form_oid,
             item_group_oid, item_oid, value, is_deleted, source_ts, ingested_at)
        VALUES
            (:study_oid, :site_ref, :subject_key, :event_oid, :form_oid,
             :item_group_oid, :item_oid, :value, :is_deleted, :source_ts, now())
        ON CONFLICT (study_oid, site_ref, subject_key, event_oid,
                     form_oid, item_group_oid, item_oid)
        DO UPDATE SET
            value       = EXCLUDED.value,
            is_deleted  = EXCLUDED.is_deleted,
            source_ts   = EXCLUDED.source_ts,
            ingested_at = now()
        WHERE EXCLUDED.source_ts >= item_data.source_ts   -- reject stale replays
    """), {**rec, "is_deleted": is_delete})
    return "tombstone" if is_delete else "upsert"

The WHERE EXCLUDED.source_ts >= item_data.source_ts guard is what makes an out-of-order webhook safe: if a stale event for an already-superseded value is redelivered, the guard refuses to overwrite the newer value with the older one, so the merge is not only idempotent but monotonic per record. For hard-delete sources with no tombstone, the only reliable detection is a periodic full-key reconciliation — fetch the set of natural keys the source still holds for a subject and mark absent keys as deleted — which is expensive and therefore run on a slower cadence than the incremental poll.

Configuration and Parameterization #

Delta behavior must be data, not code, because the overlap window, the timestamp field name, and the reconciliation cadence differ per vendor and per study, and a change to any of them is a change-controlled event. Externalize the policy into a validated configuration file whose git SHA is stamped into every run’s audit header.

# config/incremental_sync.yaml — version-controlled; changes require a tracked CR.
# GxP: this file is a configuration item under change control; the overlap window
# and watermark field are validated parameters, not tunable production knobs.
onco_ph3_001:
  vendor: medidata_rave
  watermark_field: LastUpdatedDate          # source column driving the delta
  watermark_store: "cdc:onco_ph3_001:{site}"  # durable per-site cursor key
  overlap_window_minutes: 15                 # re-scan span for late/boundary edits
  timezone_source: "UTC"                      # normalize all source ts to this
  delivery_semantics: at_least_once
  soft_delete_field: TransactionType          # value "Remove" => tombstone
  full_key_reconcile_hours: 24                # cadence for hard-delete detection
  count_reconcile: true                       # assert ingested == source count

# pydantic enforces the schema so an invalid overlap or missing watermark field
# fails fast in CI, not silently in production during a database-lock window.
from pydantic import BaseModel, Field


class SyncPolicy(BaseModel):
    vendor: str
    watermark_field: str
    watermark_store: str
    overlap_window_minutes: int = Field(ge=1, le=1440)  # bounded; never zero
    timezone_source: str = "UTC"
    delivery_semantics: str = "at_least_once"
    soft_delete_field: str | None = None
    full_key_reconcile_hours: int = Field(default=24, ge=1)
    count_reconcile: bool = True

Two rules keep this auditable. The overlap window is bounded and never zero — a zero window reintroduces the boundary gap the whole design exists to close, so the schema rejects it. And the watermark store key embeds the study and site, so a shared worker fleet syncing many sites cannot cross-contaminate one site’s cursor with another’s, a lineage guarantee the wider clinical data architecture and EDC standards program depends on.

Testing and Validation #

A delta engine that has only been tested against a clean, forward-moving stream is not validated — the entire point of the design is the awkward cases, so those are what the OQ artifacts must exercise. Use a mock transport that serves scripted delta pages, including equal timestamps, a back-dated edit, and a redelivered duplicate, and capture the resulting logs as GxP test evidence.

# OQ artifact: proves the merge is idempotent under replay and that a back-dated
# edit inside the overlap window is captured — the two properties an inspector
# will re-derive from the reconciliation log.
import httpx
from mypipeline.cdc import poll_delta, merge_record
from datetime import datetime, timedelta, timezone


def test_replay_is_idempotent_and_captures_late_edit(sqlite_conn):
    wm = datetime(2026, 7, 14, 12, 0, tzinfo=timezone.utc)
    pages = iter([
        [{"_cursor": "a", "subject_key": "S-001", "value": "72",
          "source_ts": "2026-07-14T11:58:00Z", **_key()}],   # inside overlap, late
        [],                                                    # end of stream
    ])

    def handler(request: httpx.Request) -> httpx.Response:
        return httpx.Response(200, json={"records": next(pages, [])})

    client = httpx.Client(transport=httpx.MockTransport(handler))
    for page in poll_delta(client, "https://edc.example/delta", wm,
                           overlap=timedelta(minutes=15), study_oid="ONCO_PH3_001"):
        for rec in page:
            merge_record(sqlite_conn, rec)
            merge_record(sqlite_conn, rec)      # replay the SAME record

    rows = sqlite_conn.execute("SELECT count(*) FROM item_data").fetchone()[0]
    assert rows == 1                            # replay updated in place, no dupe

Mock fixtures should cover at minimum: a clean forward delta, two records sharing an identical LastUpdatedDate, a back-dated edit falling inside the overlap window, a stale out-of-order webhook rejected by the monotonic guard, a soft-delete tombstone, and an empty page that must not advance the watermark. Each test’s structured log output is retained as objective OQ evidence, and the suite runs as a compliance gate in CI so a regression in delta correctness blocks deployment.

Production Gotchas and Failure Modes #

• Equal timestamps at the watermark boundary. A form save writes many ItemData rows with an identical LastUpdatedDate; a strictly-greater-than query on the next run skips every row that shares the watermark’s exact value. Remediation: use an inclusive lower bound plus the overlap window and deduplicate on the natural key, or key the cursor on a (timestamp, id) composite as shown in the companion cursor guide.
• Timezone drift on the source timestamp. An EDC tenant set to site-local time returns a LastUpdatedDate that appears to jump backward at a DST transition, corrupting the monotonic assumption. Remediation: normalize every change timestamp to UTC at ingress and store the watermark in UTC only; never compare a naive local timestamp to a stored one.
• Watermark regression on partial failure. Advancing the watermark before the merge is durably committed — or persisting an in-memory maximum after a crash mid-page — moves the cursor past records that were never written, dropping them permanently (Complete violation). Remediation: advance the watermark only inside the same transaction that commits the merged rows, or after the sink acknowledges, never before.
• Missed hard-deletes. A LastUpdatedDate delta cannot observe a physically removed row, so a hard-deleted subject lingers in the warehouse forever. Remediation: prefer soft-delete tombstones where the vendor supports them, and run a slower full-key reconciliation to catch removals the incremental poll cannot see.
• Overlap window narrower than the source’s edit latency. If back-dated corrections routinely land more than overlap_window_minutes below the watermark, the overlap re-scan misses them. Remediation: size the window from observed late-arrival latency plus a safety margin, and alert whenever a reconciled count mismatch reveals an edit that arrived below the window.

Compliance Checklist #

• Every record carries its source-system change timestamp and the extraction principal (ALCOA+ Attributable).
• All change timestamps are normalized to UTC at ingress before any comparison (Consistent).
• The watermark is stored durably and advanced only after a committed, idempotent merge (Complete).
• The delta lower bound is inclusive and reduced by a bounded, non-zero overlap window (Complete).
• The merge is idempotent on the ODM natural key and monotonic per record via a source_ts guard (Accurate).
• Soft-delete tombstones are applied as attributable state transitions, never physical row deletions.
• A full-key reconciliation runs on a defined cadence to detect hard-deletes the poll cannot observe.
• Ingested record counts are reconciled against the source count each cycle and mismatches are logged.
• Every watermark advance and late-arrival flag emits a timestamped, attributable audit log line.
• The sync configuration file is under version control and its git SHA is recorded in the run’s audit header (change-management trigger).
• Delivery semantics (at-least-once) and the effectively-once sink guarantee are documented in the validation package.
• OQ test artifacts demonstrate correct behavior for equal timestamps, back-dated edits, stale replays, and soft-deletes.

Related #

• Implementing Watermark Cursors for EDC Delta Sync in Python — the composite-cursor mechanics that resolve equal-timestamp and boundary collisions.
• Async Polling Strategies for EDC Updates — the transport layer whose polling cadence this delta engine rides on.
• Pandas DataFrames for Clinical Data Cleaning — the deterministic transformation stage downstream of incremental ingestion.
• Handling API Rate Limits in Clinical Sync — pacing the delta queries so incremental polling stays inside vendor quota.
• Parent: Automated EDC Ingestion & Sync Pipelines