Generating Idempotent Keys for Shapefile Uploads

An idempotent key for a shapefile upload is a deterministic digest of the whole sidecar bundle — .shp, .shx, .dbf and every optional companion — not of the .shp alone. Read the components in a fixed order, stream them through a single SHA-256 hasher, prefix the digest with a namespace and an algorithm version, and truncate the result to a URL-safe string. Two uploads of byte-identical data then produce the same key, so the load task can check-and-skip instead of re-inserting geometries. Everything that makes this hard is specific to the format: the payload is several files, the operating system hands them to you in arbitrary order, and the .prj that decides how the coordinates are interpreted is optional.

When to Use This Pattern#

Reach for a bundle-level idempotency key when any of the following is true of your ingest path:

• The upload is retried by machinery you do not control — an orchestrator retry policy, an S3 event replay, a partner’s cron job that re-PUTs the same drop folder every night.
• The load target is append-only or upsert-by-surrogate-key, so a second run genuinely duplicates features rather than overwriting them.
• Bundles arrive at 100 MB or more, where re-running the load costs minutes of ogr2ogr time and a full index rebuild on the target table.
• Two different sources may publish the same extract — a regional and a national portal republishing an identical parcel dataset, where you want the second arrival to be a cheap no-op.

It is not the right tool when the source is a stream of individual features, when the payload is a single self-describing file (a GeoPackage or GeoParquet, where the file digest is the bundle digest), or when the target table is keyed on a stable natural identifier the publisher guarantees — in that last case an idempotent PostGIS upsert on the natural key is simpler and finer-grained.

Complete Working Example#

The generator below uses only the standard library, so it drops into a Prefect task, a Dagster op or a plain boto3 handler without adding a dependency. It hashes each component’s filename before its bytes, which is what makes an absent .prj produce a different key from a present one.

from __future__ import annotations

import base64
import hashlib
import pathlib
from typing import Iterable

# ESRI's mandatory trio plus the companions that change how the data is read.
BUNDLE_SUFFIXES: frozenset[str] = frozenset(
    {".shp", ".shx", ".dbf", ".prj", ".cpg", ".sbn", ".sbx", ".qix", ".fix"}
)

CHUNK_BYTES: int = 64 * 1024


def bundle_components(shp_path: pathlib.Path) -> list[pathlib.Path]:
    """Every sidecar belonging to `shp_path`, in a stable alphabetical order.

    Directory listings are ordered by the filesystem, not by the standard, so an
    unsorted glob yields a different digest on ext4 and on S3-backed FUSE mounts.
    """
    stem = shp_path.stem
    found = [
        p
        for p in shp_path.parent.glob(f"{stem}.*")
        if p.suffix.lower() in BUNDLE_SUFFIXES and p.is_file()
    ]
    if not any(p.suffix.lower() == ".shp" for p in found):
        raise FileNotFoundError(f"no .shp component for {shp_path}")
    return sorted(found, key=lambda p: p.name.lower())


def upload_key(
    shp_path: pathlib.Path,
    namespace: str = "parcels-ingest",
    algo_version: int = 1,
    key_bytes: int = 12,
) -> str:
    """Deterministic idempotency key for a whole shapefile bundle."""
    digest = hashlib.sha256()
    # The prefix scopes the key: staging and production never collide, and bumping
    # algo_version re-keys everything without invalidating the historical ledger.
    digest.update(f"{namespace}:v{algo_version}:".encode("utf-8"))

    for component in bundle_components(shp_path):
        # Hash the NAME as well as the bytes: dropping the .prj must change the key,
        # because the same coordinates mean something different without it.
        digest.update(component.name.lower().encode("utf-8"))
        digest.update(b"\x00")
        with component.open("rb") as fh:
            for chunk in iter(lambda: fh.read(CHUNK_BYTES), b""):
                digest.update(chunk)

    raw = digest.digest()[:key_bytes]
    return base64.urlsafe_b64encode(raw).decode("ascii").rstrip("=")

Wiring it into a load task is three lines: compute the key, ask the ledger whether it has been seen, and return early if it has.

from prefect import task, get_run_logger


@task(retries=3, retry_delay_seconds=[10, 60, 300])
def load_parcels(shp_path: pathlib.Path, conn) -> str:
    key = upload_key(shp_path)
    logger = get_run_logger()

    with conn.cursor() as cur:
        # The ledger insert is the guard: a unique index on upload_key means two
        # concurrent workers cannot both win the race.
        cur.execute(
            "INSERT INTO ingest_ledger (upload_key, source_path) VALUES (%s, %s) "
            "ON CONFLICT (upload_key) DO NOTHING RETURNING upload_key",
            (key, str(shp_path)),
        )
        claimed = cur.fetchone() is not None

    if not claimed:
        logger.info("bundle %s already ingested — skipping load", key)
        return key

    ogr2ogr_load(shp_path, target_srs="EPSG:3857", nodata=None)
    return key

Parameter & Option Reference#

Two suffixes deserve a decision rather than a default. .qix and .sbn are index sidecars: they are derived from the geometry and can be rebuilt, so including them means a re-indexed but otherwise identical bundle gets a new key. Include them if your loader trusts the shipped index; exclude them if you always rebuild. Record whichever choice you make in the algo_version, so a future change is visible in the ledger rather than silent.

Verification & Testing#

The property worth testing is stability, not the specific digest. Assert that shuffling the filesystem order, re-reading, and copying the bundle to another path all produce the same key — and that touching any byte of any component does not.

import shutil


def test_key_is_stable_and_sensitive(tmp_path, sample_bundle: pathlib.Path) -> None:
    first = upload_key(sample_bundle)

    # Same bytes in a different directory: the key must not move with the path.
    moved = tmp_path / "elsewhere"
    moved.mkdir()
    for p in bundle_components(sample_bundle):
        shutil.copy2(p, moved / p.name)
    assert upload_key(moved / sample_bundle.name) == first

    # Drop the .prj: the coordinates now mean something else, so the key must change.
    stripped = tmp_path / "stripped"
    stripped.mkdir()
    for p in bundle_components(sample_bundle):
        if p.suffix.lower() != ".prj":
            shutil.copy2(p, stripped / p.name)
    assert upload_key(stripped / sample_bundle.name) != first

Outside the test suite, two command-line checks catch the failures that matter in production. ogrinfo -so -al parcels.shp reports the feature count and the declared CRS, so you can confirm the bundle you hashed is the bundle you loaded. And a ledger query answers the operational question directly:

-- Bundles claimed more than once are impossible; bundles claimed and never
-- completed are the real failure mode worth alerting on.
SELECT upload_key, source_path, claimed_at, completed_at
FROM   ingest_ledger
WHERE  completed_at IS NULL
  AND  claimed_at < now() - interval '2 hours'
ORDER  BY claimed_at;

Common Pitfalls#

• Hashing only the .shp. Attribute-only corrections ship as a changed .dbf with an untouched .shp. Hash the .shp alone and a genuine attribute update looks like a duplicate and is silently skipped — the worst possible failure, because nothing errors.
• Globbing without sorting. Path.glob returns directory order. The same bundle then hashes differently on a developer laptop and on the S3-mounted worker, so every re-run looks new and every load duplicates.
• Letting the key depend on the path. Including the absolute path or the upload timestamp in the digest makes the key unique per delivery, which defeats the entire mechanism. Only bytes and component names belong in the hash.
• Claiming the ledger row after the load instead of before. Two workers that both start before either finishes will both load. The INSERT … ON CONFLICT DO NOTHING has to happen first, and the load has to be the thing that follows a successful claim — the same ordering dead-letter queues for failed geotasks rely on when they re-drive a payload.
• Treating a truncated digest as a checksum. 96 bits is ample for collision avoidance across an ingest ledger, but it is not an integrity check. If you need to prove the bytes did not rot in transit, keep the full digest in a second column.

Frequently Asked Questions#

Related#

• Idempotency keys in spatial ETL — the pattern this recipe implements
• Idempotent PostGIS upserts for feature loads — feature-level, rather than bundle-level, safety
• Storing failed geometries in a PostGIS dead-letter queue — where the bundles that never load end up
• Managing state for incremental shapefile updates — tracking what changed between deliveries

← Idempotency Keys in Spatial ETL