SpatiaLite & GeoPackage Automation with Python

Detecting Schema Drift Between Sync Cycles

Record each layer's columns as (name, affinity) pairs from PRAGMA tableinfo, normalising the declared type to its SQLite affinity so that VARCHAR(32) and…

Record each layer’s columns as (name, affinity) pairs from PRAGMA table_info, normalising the declared type to its SQLite affinity so that VARCHAR(32) and TEXT compare equal. Store that signature in the run manifest and diff it against the previous one. A column that vanishes, appears, or changes affinity is a break in the contract with every consumer, and this catches it in one query per layer.

This page belongs to the Data Quality Gates & Monitoring guide.

Why This Matters

Consumers bind to columns. A mobile form reads inspector, a report sums area_m2, an export maps parcel_ref to a field in someone else’s system. When a column disappears or changes type, none of those fails at the container — they fail later, in the consumer, with an error that names the consumer’s code rather than the schema change that caused it.

The change itself is usually accidental. A source system renamed a field, an append promoted an integer column to real because one value was null, a new attribute arrived that nobody declared. All three are silent at the point they happen, and all three are one query apart from being obvious.

Prerequisites

Primary Method

python
# schema_signature.py — a comparable description of every layer's columns
import sqlite3

# SQLite resolves any declared type to one of five affinities. Comparing
# affinities rather than spellings means VARCHAR(32) == TEXT, INT == INTEGER.
def affinity(declared: str) -> str:
    d = (declared or "").upper()
    if "INT" in d:
        return "INTEGER"
    if any(k in d for k in ("CHAR", "CLOB", "TEXT")):
        return "TEXT"
    if "BLOB" in d or d == "":
        return "BLOB"
    if any(k in d for k in ("REAL", "FLOA", "DOUB")):
        return "REAL"
    return "NUMERIC"


def signature(path: str) -> dict[str, list[tuple[str, str]]]:
    """{layer: [(column, affinity), ...]} for every feature layer."""
    conn = sqlite3.connect(f"file:{path}?mode=ro", uri=True)
    try:
        layers = [r[0] for r in conn.execute(
            "SELECT table_name FROM gpkg_contents WHERE data_type = 'features'"
        )]
        return {
            layer: [
                (row[1], affinity(row[2]))
                for row in conn.execute(f'PRAGMA table_info("{layer}")')
            ]
            for layer in sorted(layers)
        }
    finally:
        conn.close()

Normalising to affinity is what keeps the check from crying wolf. SQLite’s type system resolves any declared type to one of five affinities, so a driver that writes VARCHAR(32) where the previous version wrote TEXT has changed nothing about behaviour — and a signature comparing raw strings would report it as drift on every build.

Why the signature compares affinity rather than the declared typeSQLite resolves every declared type to one of five affinities. VARCHAR(32), CHAR(8) and TEXT all resolve to TEXT, so a driver switching between them changes nothing. INT, INTEGER and BIGINT all resolve to INTEGER. Comparing the declared strings would report drift for these harmless differences, while comparing affinities reports it only when behaviour actually changes.declaredaffinityVARCHAR(32) · CHAR(8) · TEXT · CLOBTEXTINT · INTEGER · BIGINT · TINYINTINTEGERREAL · FLOAT · DOUBLEREALcomparing the left column reports drift that changes nothingcomparing the right column reports drift only when behaviour moves
A signature built on declared strings produces false drift on any driver upgrade; one built on affinity does not.

Step-by-Step Walkthrough

1. Classify the difference before reporting it

Not every change is a break. Adding a column is additive and most consumers tolerate it; removing one or changing its affinity is breaking, because something is reading it.

python
# schema_diff.py — classify, do not just report
def diff(now: dict, was: dict) -> dict[str, list[str]]:
    breaking, additive = [], []

    for layer, cols_was in was.items():
        cols_now = now.get(layer)
        if cols_now is None:
            breaking.append(f"{layer}: layer removed")
            continue

        by_name_was = dict(cols_was)
        by_name_now = dict(cols_now)

        for col in sorted(set(by_name_was) - set(by_name_now)):
            breaking.append(f"{layer}.{col}: column removed")
        for col in sorted(set(by_name_now) - set(by_name_was)):
            additive.append(f"{layer}.{col}: column added")
        for col in sorted(set(by_name_was) & set(by_name_now)):
            if by_name_was[col] != by_name_now[col]:
                breaking.append(
                    f"{layer}.{col}: {by_name_was[col]} -> {by_name_now[col]}"
                )

    for layer in sorted(set(now) - set(was)):
        additive.append(f"{layer}: layer added")

    return {"breaking": breaking, "additive": additive}

Splitting the two is what lets the gate block on one and record the other, which is the difference between a check that stays enabled and one that does not.

2. Store the signature in the manifest

python
manifest["schema"] = signature(container_path)

Keeping it in the same JSON as the counts means one artefact carries everything the next run needs, and a container copied to a device carries its own schema description with it.

3. Watch for the integer-to-real drift specifically

This one deserves its own mention because it is the most common and the most confusing. A pandas column of integers containing one missing value becomes float64, the driver maps that to REAL, and a column that was INTEGER yesterday is REAL today — with every existing value now stored as a float.

python
# The signature catches it as an affinity change on an existing column
# parcels.sensor_id: INTEGER -> REAL

The fix is upstream, in the append path, using a nullable integer type rather than letting the promotion happen — the mechanism is covered in How to Append a GeoDataFrame to a GeoPackage Layer.

Four kinds of drift and what each does to a consumerA removed column breaks any consumer that reads it, immediately and loudly. A changed affinity breaks consumers that expect a type, often quietly — a report that summed integers now sums floats. An added column is usually harmless and worth recording. A removed layer breaks everything that used it. Only the first, second and fourth should block publication.column removedbreaking — every consumer reading it fails, loudlyaffinity changedbreaking — often quietly, which is worselayer removedbreaking — everything that used it stopscolumn or layer addedadditive — record it, do not block
The second row is the one worth designing for: it is the only kind that consumers absorb without complaining.

4. Wire it into the gate

python
# In the relative band of the gate
from schema_signature import signature
from schema_diff import diff

result = diff(signature(new_container), previous_manifest["schema"])

for line in result["additive"]:
    print(f"NOTE  {line}")
for line in result["breaking"]:
    print(f"DRIFT {line}", file=sys.stderr)

sys.exit(1 if result["breaking"] else 0)

5. Record an intentional change deliberately

Schemas do change on purpose. The gate should require that to be stated rather than inferred.

bash
# A planned schema change passes with a recorded reason
GATE_OVERRIDE="added inspector_id per ticket FIELD-482" ./gate.sh built.gpkg out.gpkg prev.json

The reason lands in the manifest, so the next run’s comparison has context and a future question about when a column appeared has an answer in the artefact.

Verification

python
# The diff must classify each kind correctly
BEFORE = {"parcels": [("fid", "INTEGER"), ("parcel_ref", "TEXT"),
                      ("sensor_id", "INTEGER"), ("geom", "BLOB")]}

# removed column -> breaking
assert diff({"parcels": [("fid", "INTEGER"), ("parcel_ref", "TEXT"),
                         ("geom", "BLOB")]}, BEFORE)["breaking"]

# affinity change -> breaking
assert diff({"parcels": [("fid", "INTEGER"), ("parcel_ref", "TEXT"),
                         ("sensor_id", "REAL"), ("geom", "BLOB")]},
            BEFORE)["breaking"]

# added column -> additive only
r = diff({"parcels": BEFORE["parcels"] + [("inspector", "TEXT")]}, BEFORE)
assert not r["breaking"] and r["additive"]

# a spelling change that does not move the affinity -> nothing at all
assert affinity("VARCHAR(32)") == affinity("TEXT")
print("schema diff verified")

And confirm the signature is stable across a rebuild of the same data, which is the property that makes the comparison meaningful at all:

bash
# Build the same source twice; the schema signatures must be identical
python3 manifest.py build_a.gpkg | jq -S .schema > a.json
python3 manifest.py build_b.gpkg | jq -S .schema > b.json
diff a.json b.json || { echo "signature is not stable across rebuilds"; exit 1; }
Two properties the signature must haveThe signature must be stable: building the same source twice must produce identical signatures, or every run reports drift and the check becomes noise. It must also be sensitive: a genuine column removal or affinity change must produce a difference. A signature that fails the first property gets ignored; one that fails the second catches nothing.stablesame source, same signatureno drift from a rebuildwithout it, the check is noiseand gets ignoredsensitivea real change showsremoval, affinity, layerwithout it, the check is decorativeand catches nothingthe affinity normalisation is what buys the left box without losing the right one
Both properties are testable in a few lines, and a signature failing either is worse than no check.

Alternative Approaches or Edge Cases

Including nullability and defaults. PRAGMA table_info also reports notnull and dflt_value, and a constraint quietly disappearing is a real break. Adding them widens the signature and makes it slightly more sensitive to driver differences — worth doing where the constraints matter, and worth measuring for stability first.

Comparing against a declared contract. Rather than comparing run to run, compare against a checked-in schema declaration. That catches drift on the very first run and makes the expected schema reviewable, at the cost of a file that must be maintained deliberately. For a container consumed by external parties, it is the stronger option.

Geometry column type. gpkg_geometry_columns records the declared geometry type and dimensionality, and a layer changing from POLYGON to MULTIPOLYGON is a schema change no table_info signature sees. Include those columns in the signature where geometry typing matters to consumers.

Troubleshooting

The signature changes on every build

Cause: Column ordering, or declared-type spelling varying between driver versions. Fix: The signature is a list in table_info order, which is stable for a given container but can differ between build paths — sort by name if the ordering itself is not part of the contract, and confirm the affinity normalisation is being applied.

A removed column is reported as removed and added

Cause: It was renamed. table_info has no notion of a rename, so it presents as one removal and one addition. Fix: Nothing to fix in the check — but the report is clearer if the diff notes when a removal and an addition share an affinity, which is a plausible rename worth flagging as such.

The check misses a type change

Cause: The two declared types resolve to the same affinity — INT to BIGINT, for instance. Fix: That is intended: SQLite treats them identically, and no consumer reading through SQLite will see a difference. If a consumer reads the declared string, include it as a second, advisory-only field.

Frequently Asked Questions

Is a column addition ever breaking?

Occasionally. A consumer that does SELECT * and positionally unpacks the result breaks when a column appears in the middle, and a strict schema validator on the other side may reject an unexpected field. Both are consumer fragilities rather than container problems, but if you know a consumer behaves that way, treat additions as breaking for that container specifically.

Should the geometry column be in the signature?

Yes, and its declared type belongs there too — from gpkg_geometry_columns rather than table_info, which only reports it as a BLOB. A layer changing from single-part to multi-part geometry is exactly the kind of change that breaks a downstream consumer while leaving the container perfectly valid.

How does this interact with a schema that legitimately evolves?

It makes each evolution explicit, which is the point. A planned addition passes as additive with no ceremony; a planned removal needs an override with a reason, which takes ten seconds and leaves a record. The friction is proportional to how disruptive the change is for consumers, which is roughly the right shape.