Incremental Change Detection & Delta Loads Jump to heading#

Most geospatial pipelines are built to reprocess everything, because everything is what the source sends: a full extract, weekly, whether forty features changed or none did. Full reloads are simple and they are also the reason a pipeline that takes four hours cannot be run twice in a day, the reason a change report says “214,338 features updated” every week, and the reason a lineage manifest fills with events that record no change. This stage computes what actually changed and applies only that, inside Automated Attribute Transformation & ETL Workflows.

The scope boundary against neighbouring stages is worth fixing. Batch schema processing pipelines owns throughput and memory over a full delivery; this page owns not processing most of it. And change detection here is about the same feature changing between deliveries of the same layer — recognizing that a parcel was split into two is a question of identity, owned by matching features across vintages with stable identifiers.

Declarative Configuration Manifest Jump to heading#

# delta_policy.yaml — geopandas >=0.14, pyarrow >=14
policy_version: "1.1.0"           # MANDATORY
layer: "parcels"                  # MANDATORY
identity_column: "stable_id"      # MANDATORY: the key deltas are computed against
hash:
  geometry:
    method: wkb_precision         # MANDATORY: wkb_precision | none
    grid_size: 0.001              # MANDATORY for wkb_precision — 1 mm, in CRS units
    normalize_ring_order: true    # OPTIONAL: default true; see notes
  attributes:                     # MANDATORY: columns that participate in the content hash
    - land_use_code
    - ownership_status
    - area_m2
  exclude:                        # OPTIONAL: columns that must NOT trigger a change
    - last_exported_at            # source-side timestamp, changes every extract
    - internal_row_version
  null_token: "�NULL"        # MANDATORY: distinguishes null from an empty string in the hash
apply:
  mode: upsert                    # MANDATORY: upsert | replace_partition
  deletes: soft                   # MANDATORY: soft | hard — soft sets retired_at
  batch_size: 20000               # OPTIONAL
  require_delta_ratio_below: 0.35 # OPTIONAL: abort if more than 35 % of rows "changed"

require_delta_ratio_below is the single most valuable line in the manifest. When a source changes how it rounds coordinates or how it spells nulls, every feature’s hash changes and the pipeline concludes that the entire layer was rewritten — then dutifully rewrites it, filling the audit trail with a quarter of a million false change events. Aborting above a threshold turns that into a five-minute investigation.

Preprocessing Requirements Jump to heading#

Hashing happens after normalization, never before. The hash must be computed on the canonical form of the feature — after CRS normalization, after type coercion, after ring orientation. Hashing raw source values means every upstream formatting change is a data change.

Ring order and vertex start point are normalized. Two WKB encodings of the same polygon differ if the ring starts at a different vertex or runs the other way. Both happen routinely when a source re-exports from a different tool, and both produce a different hash for an identical shape. Normalizing orientation — the same rule as ring orientation and winding order — removes half the problem; rotating each ring to start at its lexicographically smallest vertex removes the other half.

The previous state is addressable. Delta detection compares against a stored hash per identity, not against the target table’s live contents. Keeping a small (stable_id, content_hash, first_seen, last_seen) table makes the comparison a hash join rather than a full read of the target.

Execution Engine & Precision Guards Jump to heading#

# etl/delta.py — shapely >=2.0, geopandas >=0.14 — Python 3.10+
import hashlib
import logging
from dataclasses import dataclass

from shapely import set_precision
from shapely.geometry.base import BaseGeometry

logger = logging.getLogger("etl.delta")
NULL_TOKEN = "\x00NULL"


@dataclass(frozen=True)
class Change:
    stable_id: str
    kind: str            # inserted | updated | unchanged | deleted
    old_hash: str | None
    new_hash: str | None


def canonical_geometry(geom: BaseGeometry, grid_size: float) -> bytes:
    """A byte form that is identical for geometrically identical shapes."""
    snapped = set_precision(geom, grid_size)
    normalized = snapped.normalize()      # shapely 2: canonical ring order and start vertex
    return normalized.wkb


def content_hash(row: dict, geom: BaseGeometry, columns: list[str], grid_size: float) -> str:
    digest = hashlib.sha256()
    digest.update(canonical_geometry(geom, grid_size))
    for column in columns:                 # fixed order: the manifest's order, not the frame's
        value = row.get(column)
        digest.update(b"\x1f")             # field separator: prevents "ab"+"c" == "a"+"bc"
        digest.update((NULL_TOKEN if value is None else str(value)).encode("utf-8"))
    return digest.hexdigest()


def classify(previous: dict[str, str], current: dict[str, str]) -> list[Change]:
    changes: list[Change] = []
    for stable_id, new_hash in current.items():
        old_hash = previous.get(stable_id)
        if old_hash is None:
            changes.append(Change(stable_id, "inserted", None, new_hash))
        elif old_hash != new_hash:
            changes.append(Change(stable_id, "updated", old_hash, new_hash))
        else:
            changes.append(Change(stable_id, "unchanged", old_hash, new_hash))
    for stable_id, old_hash in previous.items():
        if stable_id not in current:
            changes.append(Change(stable_id, "deleted", old_hash, None))

    counts = {k: sum(1 for c in changes if c.kind == k)
              for k in ("inserted", "updated", "unchanged", "deleted")}
    logger.info("delta %s", counts)
    return changes

The field separator and the null token look like paranoia and are not. Without a separator, a feature with ("ab", "c") hashes identically to one with ("a", "bc") — rare, but real in address data where street names and unit designators abut. Without a distinct null token, a null and an empty string produce the same digest, so a source that starts exporting empty strings for missing values registers no change at all, which is precisely the change most worth catching.

normalize() is the shapely 2 operation that makes geometry hashing viable. Without it, hashing WKB detects re-exports rather than edits.

Failure Modes & Fallback Routing Jump to heading#

Delete handling deserves the caution. A source extract that failed halfway produces a delivery missing half the layer, and a hard-delete policy applied to that delivery removes half the target. Soft deletes plus the ratio ceiling make that recoverable: the features are marked retired, the run aborts, and reverting is an update rather than a restore.

Deciding What Participates in Identity Jump to heading#

Every column is in one of three states — part of the content hash, explicitly excluded, or undeclared — and the third state is the one that causes trouble. An undeclared column is a decision nobody made, and the two possible defaults are both wrong in some cases, so the manifest forces the choice.

Include a column when a change to it means the feature changed. Land-use code, ownership status, geometry, measured area: a consumer who cached this feature would want to know. These are the columns whose change is the point of publishing an update.

Exclude a column when it changes without the feature changing. Source-side export timestamps, internal row versions, ETL run identifiers, and any column your own pipeline computes deterministically from the others. Including an export timestamp is the classic way to build a delta pipeline that reports 100% change on every run — technically correct, operationally useless, and slower than the full reload it replaced.

Treat an undeclared column as a review finding rather than a silent default. When a delivery arrives with a column the manifest has never seen, the run should record it, warn, and use the previous behaviour — not quietly start hashing it, which would show every feature as changed, and not quietly ignore it, which would hide a real new attribute. Whether it joins the hash is a decision for whoever owns the schema.

Two subtleties recur often enough to be worth stating outright. Derived columns must not participate: if area_m2 is computed from the geometry inside your pipeline, hashing both means a change in the geometry is counted twice and a change in the area calculation looks like a data change across the whole layer. Hash the input, not the derivation. And an excluded column still gets written — exclusion is about identity, not about storage, so the current value of last_exported_at lands in the target on any row that updates for another reason, and simply never causes an update on its own.

A short audit keeps the three states honest between deliveries:

# etl/delta_audit.py — Python 3.10+
def audit_columns(delivery_columns: set[str], policy: dict) -> dict:
    """Every column must be in exactly one declared state."""
    included = set(policy["hash"]["attributes"])
    excluded = set(policy["hash"].get("exclude", []))
    structural = {policy["identity_column"], "geometry"}

    overlap = included & excluded
    if overlap:
        raise ValueError(f"columns both included and excluded: {sorted(overlap)}")

    missing = included - delivery_columns
    if missing:
        raise ValueError(f"hashed columns absent from this delivery: {sorted(missing)}")

    undeclared = delivery_columns - included - excluded - structural
    return {"undeclared": sorted(undeclared),
            "hashed": len(included), "ignored": len(excluded)}

The missing check is the important one and the easiest to leave out. A hashed column that vanishes from a delivery would otherwise be silently dropped from the digest, changing every feature’s hash — a full-layer false delta that looks exactly like the source having rewritten everything, with no indication of the real cause. Failing the run and naming the column turns a day of investigation into a line in the log.

Compliance Reporting Output Jump to heading#

# etl/delta_report.py — pyarrow >=14 — Python 3.10+
import pyarrow as pa

DELTA_EVENT_SCHEMA = pa.schema([
    ("run_id",         pa.string()),
    ("policy_version", pa.string()),
    ("layer",          pa.string()),
    ("stable_id",      pa.string()),
    ("kind",           pa.string()),      # inserted | updated | deleted (unchanged is not written)
    ("old_hash",       pa.string()),
    ("new_hash",       pa.string()),
    ("changed_fields", pa.string()),      # comma-separated, computed only for updates
    ("applied_at",     pa.timestamp("us", tz="UTC")),
])

Not writing unchanged rows is a deliberate asymmetry: the run summary records how many were unchanged, but the event store holds only events. Computing changed_fields for updates costs a second comparison pass and is worth it — it converts “this parcel was updated eleven times this year” into “its land-use code changed once and its area was recomputed ten times”, which is the difference between a useful history and a noisy one. These rows are the natural input to the lineage manifest.

CI Integration Jump to heading#

# tests/test_delta.py — pytest >=7, shapely >=2.0
def test_reexported_identical_layer_produces_no_changes(delivery):
    first = {r["stable_id"]: content_hash(r, r["geometry"], COLUMNS, 0.001) for r in delivery}
    reexported = reexport_with_different_ring_start(delivery)      # same shapes, new WKB
    second = {r["stable_id"]: content_hash(r, r["geometry"], COLUMNS, 0.001) for r in reexported}
    assert first == second


def test_one_centimetre_move_is_detected(delivery):
    moved = shift_one_feature(delivery, "PARCEL-14-023-9", dx=0.01)
    changes = classify(hashes(delivery), hashes(moved))
    assert [c.kind for c in changes if c.stable_id == "PARCEL-14-023-9"] == ["updated"]


def test_null_and_empty_string_hash_differently():
    # Negative control: the token exists precisely to keep these apart.
    a = content_hash({"land_use_code": None}, GEOM, ["land_use_code"], 0.001)
    b = content_hash({"land_use_code": ""}, GEOM, ["land_use_code"], 0.001)
    assert a != b


def test_apply_is_idempotent(target_db, delta):
    apply_delta(target_db, delta)
    snapshot = dump(target_db)
    apply_delta(target_db, delta)           # same delta, second time
    assert dump(target_db) == snapshot

The first two tests are a matched pair and should always be read together: one asserts that noise produces no change, the other that a real 1 cm move does. A hash tuned only for the first will pass by being insensitive, and only the second catches it.

Deeper Implementation Walkthroughs Jump to heading#

Computing geometry hashes for change detection works through canonicalization in detail — precision grids, ring normalization, multipart ordering, and what to do with curves and Z values. Applying delta loads to a PostGIS target idempotently covers the apply half: batched upserts, soft deletes, and making a re-run of the same delta a no-op.

Frequently Asked Questions Jump to heading#

Why not use the source’s own “last modified” column? Use it if it is trustworthy, and verify that it is. In practice, source-side timestamps are updated by any export, by bulk administrative operations, and sometimes by nothing at all when a change is applied directly to the database. A content hash is a statement about the content; a timestamp is a statement about someone’s process. Where a reliable timestamp exists, it is an excellent pre-filter — hash only the rows it flags — but the hash remains the arbiter.

How expensive is hashing a large layer? Cheaper than the alternative by a wide margin. SHA-256 over a canonical WKB runs at hundreds of megabytes per second, and for a two-million-feature parcel layer the hashing pass is typically a small fraction of the time the full downstream processing would take. The saving is not in the hashing; it is in the 99% of features that never enter validation, transformation or the write path.

Should geometry participate in the hash at all? Almost always yes, because a boundary correction is a change that consumers care about. The exception is a layer where geometry is generated downstream from attributes — a point layer geocoded from addresses — in which case hashing the generated geometry means a geocoder upgrade registers as a data change across the entire layer. There, set method: none and record why in the manifest.

Does a delta pipeline still need the full delivery? Yes, and that surprises people. Detecting deletions requires knowing which identities are absent, which means reading the whole delivery’s identity and hash columns even though only a fraction of the features are processed further. The saving is in transformation, validation and the write path, not in the read — so a source that could send a true incremental feed, with explicit insert, update and delete events, is still worth asking for. Until it does, reading identities cheaply is what makes the rest of the saving possible.

What happens when the hash definition itself changes? Every feature’s hash changes, so the run looks like a total rewrite. Bump policy_version, run once with the ratio ceiling disabled and with kind forced to a distinct value such as rehashed, and record that the run was a rehash rather than a change. Doing this without a marker is how an audit trail acquires a day on which everything appeared to change and nobody can say why.

Related Jump to heading#

• Automated Attribute Transformation & ETL Workflows — the parent section and the stages a delta lets you skip
• Computing Geometry Hashes for Change Detection — canonicalization in full detail
• Applying Delta Loads to a PostGIS Target Idempotently — batched upserts, soft deletes and safe re-runs
• Batch Schema Processing Pipelines — the full-delivery path this stage exists to avoid running
• Matching Features Across Vintages with Stable IDs — identity across deliveries, which a delta assumes has already been settled