Validating Zoning Schema Consistency Across City Portals

You run one ingestion pipeline against forty city and county portals, and this morning the overnight sync committed 39 jurisdictions cleanly while one — call it the City of Raleigh feed — dropped its pass rate from 99% to 4%. Nothing crashed. The loader ran, the run finished green, and the only signal is a quarantine directory suddenly full of attribute_violation records from a single source. A planner renamed ZONE_CODE to ZONING_CLASS and switched its values from R-1 to Residential_Single between quarterly releases, with no changelog. This page covers the narrow problem the parent schema validation and data quality checks gate defers: how to detect which portal drifted, keep per-city schema variants so a contract change in one jurisdiction is isolated, and verify cross-portal consistency before bad structure reaches the compliance database.

The hard part of multi-municipality ingestion is not validating one feed — it is that every portal is an independent, unversioned publisher. There is no shared schema, no deprecation notice, and no guarantee that the field that existed last quarter still exists this quarter. A single forced contract across all forty portals rejects good data from the 39 that did not change. The goal is a registry of per-jurisdiction schema variants, a fingerprint that detects drift the moment a portal changes, and a consistency gate that admits structurally sound records while quarantining only the jurisdiction that moved.

Diagnosing per-portal schema drift jump to heading

Drift surfaces as a localized collapse in pass rate, not a traceback. The fastest way to confirm it is to break the validation report down by source portal rather than looking at a single global number. A run-level pass rate of 96% hides a feed that went from 99% to 4%, because the other 39 portals mask it.

The signature to look for in the per-portal report:

• One source_portal shows a sudden spike in attribute_violation count while geometry violations stay flat — the structure changed, not the geometry.
• The failing records share an identical error path, e.g. every rejection is zoning_code: field required or zoning_code: string does not match regex. A genuine data-quality problem scatters across fields; a schema change concentrates on one.
• The portal’s column fingerprint no longer matches the one recorded at last successful run.

That last check is the deterministic one. Compute a stable fingerprint of each feed’s structure — sorted field names plus declared types — and compare it to the fingerprint stored from the last green run. When the hash changes, the schema changed, full stop; you do not have to infer it from failure counts.

import hashlib
import json
from typing import Iterable


def schema_fingerprint(field_types: dict[str, str]) -> str:
    """Stable hash of a feed's structure: sorted field names + declared types.

    Two runs of the same portal produce the same hash; any rename, type
    change, added or dropped column produces a different one.
    """
    normalized = sorted((name, dtype) for name, dtype in field_types.items())
    payload = json.dumps(normalized, separators=(",", ":"))
    return hashlib.sha256(payload.encode("utf-8")).hexdigest()[:16]


def detect_drift(portal_id: str, current: dict[str, str],
                 last_known: dict[str, str]) -> dict:
    """Compare current feed structure to the last green run for one portal."""
    cur_hash = schema_fingerprint(current)
    prev_hash = schema_fingerprint(last_known)
    added = sorted(set(current) - set(last_known))
    dropped = sorted(set(last_known) - set(current))
    retyped = sorted(
        f for f in set(current) & set(last_known)
        if current[f] != last_known[f]
    )
    return {
        "portal_id": portal_id,
        "drifted": cur_hash != prev_hash,
        "added_fields": added,
        "dropped_fields": dropped,
        "retyped_fields": retyped,
    }

This is upstream of the attribute contract: the fingerprint tells you the structure moved before a single record is validated, which is what lets you halt or branch that one portal instead of drowning its quarantine queue. Drift here is distinct from the geometry-side problem of silent reprojection covered in CRS alignment strategies — schema drift is structural, CRS drift is spatial, and a multi-portal pipeline has to watch for both independently.

Step-by-step implementation jump to heading

The fix is a configuration-driven registry that holds one schema variant per jurisdiction, a normalization pass that maps each portal’s vocabulary to the canonical model, and a consistency gate that selects the right variant at runtime. Each step addresses exactly one concern.

1. Register a schema variant per jurisdiction jump to heading

Do not hard-code field names. Each portal registers its field map, its date format, its declared CRS, and the schema fingerprint from its last green run. Adding a new city means adding a config entry, never editing pipeline code.

from dataclasses import dataclass, field


@dataclass(frozen=True)
class PortalSchema:
    portal_id: str
    # Maps this portal's raw field names -> canonical keys.
    field_map: dict[str, str]
    # Maps this portal's raw zoning codes -> canonical taxonomy codes.
    code_map: dict[str, str]
    date_format: str            # e.g. "%m/%d/%Y" — pinned per portal
    declared_crs: str           # e.g. "EPSG:2264"
    last_fingerprint: str       # structure hash from last green run
    variant_version: str        # bumped whenever this config changes


REGISTRY: dict[str, PortalSchema] = {
    "raleigh_nc": PortalSchema(
        portal_id="raleigh_nc",
        field_map={"ZONE_CODE": "zoning_code", "PARCEL": "parcel_id",
                   "EFF_DATE": "effective_date", "JURIS": "jurisdiction"},
        code_map={"R-1": "RES-SF", "R-4": "RES-MF", "OX": "MIX-OFF"},
        date_format="%m/%d/%Y",
        declared_crs="EPSG:2264",
        last_fingerprint="9f1c4a2b7e0d5631",
        variant_version="2025.10.0",
    ),
}

2. Normalize each portal to the canonical vocabulary before validation jump to heading

The contract can only compare apples to apples if field names and codes are translated first. This is where the attribute normalization rules and the canonical zoning taxonomy mapping run — using the registered variant, not a global guess. Translate, then validate.

from datetime import datetime


def normalize_record(raw: dict, schema: PortalSchema) -> dict:
    """Translate one portal's raw record into the canonical shape."""
    out: dict = {}
    for raw_field, value in raw.items():
        if raw_field == "geometry":
            out["geometry"] = value
            continue
        canonical = schema.field_map.get(raw_field)
        if canonical is None:
            continue  # unmapped field — dropped, surfaced via drift report
        if canonical == "zoning_code":
            # Map the portal's local code to the canonical taxonomy.
            value = schema.code_map.get(value, value)
        if canonical == "effective_date" and value:
            # Pin the date format per portal; never trust a permissive parser.
            value = datetime.strptime(value, schema.date_format).date().isoformat()
        out[canonical] = value
    return out

3. Gate consistency and isolate the drifted portal jump to heading

Before normalizing a feed, fingerprint it and compare to the registry. If the structure moved, branch that portal to a hold state — do not run thousands of records through a contract you already know they will fail.

import logging

logger = logging.getLogger("portal_consistency")


def gate_portal(portal_id: str, current_field_types: dict[str, str]):
    """Decide whether a portal's feed is safe to ingest this run."""
    schema = REGISTRY.get(portal_id)
    if schema is None:
        logger.error("No registered schema variant for %s", portal_id)
        return "UNREGISTERED"

    cur = schema_fingerprint(current_field_types)
    if cur != schema.last_fingerprint:
        logger.error(
            "[%s] schema drift: fingerprint %s != registered %s",
            portal_id, cur, schema.last_fingerprint,
        )
        # Hold this portal; the other jurisdictions keep flowing.
        return "DRIFT_HOLD"

    return "OK"

A DRIFT_HOLD halts one jurisdiction and leaves the other 39 untouched — the isolation property that keeps a single rename from turning into a site-wide outage. Large county feeds that clear the gate still flow through the existing async batch processing engine; the consistency gate sits in front of it as a cheap structural pre-check.

Verification and testing jump to heading

A consistency gate is only trustworthy if you can prove it admits good data and isolates the drifted portal:

• Assert per-portal pass rates, not a global one. After a run, every registered portal should report its own pass rate; alert when any single portal drops more than a few points from its trailing average, even if the global rate looks healthy.
• Round-trip the fingerprint. Re-run the same feed twice and confirm schema_fingerprint returns an identical hash. A fingerprint that is unstable across runs (e.g. because field order is not sorted) will fire false drift alarms.
• Keep a golden fixture per jurisdiction. Maintain a small known-good sample for each portal and validate against it on every build. This catches the case where your registry edit, not the portal, introduced the inconsistency.
• Diff added/dropped/retyped fields. When detect_drift reports a change, confirm the reported added_fields and retyped_fields match the portal’s actual release notes (if any) before bumping the variant — a dropped_field on a key column is a different severity than an added optional one.

A clean multi-portal run reads one line per jurisdiction, with the drifted one standing out:

2026-06-26 02:14:01 | INFO  | [charlotte_nc]  fingerprint OK  pass_rate 0.991
2026-06-26 02:14:03 | INFO  | [durham_nc]     fingerprint OK  pass_rate 0.997
2026-06-26 02:14:05 | ERROR | [raleigh_nc]    schema drift: fingerprint a72e... != 9f1c...
2026-06-26 02:14:05 | ERROR | [raleigh_nc]    DRIFT_HOLD — retyped_fields=['zoning_code'] added=['ZONING_CLASS'] dropped=['ZONE_CODE']

Failure recovery jump to heading

When a portal trips DRIFT_HOLD, the goal is to keep the rest of the sync moving while the one jurisdiction is repaired, and to leave an audit trail of exactly what changed:

1. Hold the portal, release the rest. Only the drifted jurisdiction is withheld from the compliance database; the other portals commit normally. A single city’s rename never blocks a multi-jurisdiction sync.
2. Emit a drift remediation record. Capture the old and new fingerprints, the added/dropped/retyped fields, the variant version in force, and a sample of the now-failing records. This artifact is what downstream compliance framework integration consumes for lineage when an auditor asks why a parcel’s data is a day stale.
3. Serve a last-known-good snapshot. While the variant is being updated, let fallback routing logic substitute the previous green snapshot for the held jurisdiction so the map has no hole rather than missing data.
4. Update the variant, bump the version, re-fingerprint. Extend the held portal’s field_map/code_map, set last_fingerprint to the new hash, bump variant_version, and re-run only that portal. Distinguish a transient parse hiccup — which belongs in the scoped error handling and retry logic layer — from a true schema change that requires a variant bump.

Because the remediation record preserves both fingerprints and the failing sample, re-running after the variant update reproduces the exact input and confirms the fix lifted the pass rate back to baseline.

FAQ jump to heading

Related jump to heading

• Schema validation and data quality checks — the three-tier gate this page extends across many portals
• Zoning taxonomy mapping — normalizes each portal’s district codes before validation
• Attribute normalization rules — translates per-portal field names into the canonical shape
• CRS alignment strategies — the spatial counterpart to structural drift detection
• Fallback routing logic — serves a last-known-good snapshot while a portal is held
• Error handling and retry logic — separates transient parse failures from true schema drift

Up: Schema Validation & Data Quality Checks · Municipal Zoning Data Architecture & Compliance Frameworks