Cross-Platform Schema Translation in Geospatial ETL Pipelines Jump to heading#

A geospatial dataset rarely stays in one platform. A county parcel layer arrives as an Esri file geodatabase, must land in a PostGIS warehouse, gets republished as a GeoPackage for field crews, and is archived as GeoParquet for analytics. Each hop carries a different type system, field-name length limit, geometry model, and null convention — and every uncontrolled hop is an opportunity for silent data loss. Cross-platform schema translation is the deterministic stage that maps a source platform model onto a target model under an explicit, version-controlled contract, so the same input always produces the same output and every deviation is logged. This stage sits inside Geospatial Schema Architecture & Standards Mapping, the discipline that governs how spatial datasets are ingested, normalized, validated, and published across heterogeneous systems.

This page covers the translation contract and the engine that enforces it: the mapping manifest, the preprocessing the engine assumes, precision guards, failure routing, lineage output, and CI gating. It does not re-derive the standards those mappings must satisfy — alignment with INSPIRE Directive Schema Compliance and FGDC Metadata Mapping is treated here as a set of target-model constraints the manifest references, not as the subject. Coordinate handling is delegated to CRS Normalization & Sync; this stage only verifies that geometries arrive in the declared target CRS.

The declarative mapping manifest Jump to heading#

Hardcoded translation logic — a Python function with a wall of if source_field == "...": branches — cannot be diffed, reviewed, or audited, and it breaks reproducibility the moment an upstream agency renames a column. The contract between platforms must instead live in a declarative manifest: a YAML or JSON file that names every source field, its target binding, the coercion to apply, the cardinality, and the numeric tolerance. The engine becomes a generic interpreter of that manifest, and the manifest becomes the version-controlled artifact that CI gates and audits read.

Every rule declares a cardinality that fixes its behaviour when the source value is absent or malformed. This is the single most important directive: it determines whether a missing value halts the record or is filled deterministically.

# schema_mapping.yaml — pinned engine: geopandas>=0.14, pyproj>=3.6, pydantic>=2.0
version: "1.4.0"
target_crs: "EPSG:3857"          # geometries must arrive in this CRS or be rejected
target_platform: "postgis"       # drives name-length and type-capability checks
translation_rules:
  - source_field: "parcel_id"
    target_field: "feature_identifier"
    dtype: "string"
    cardinality: "required"        # execution routes the record to quarantine if missing
    validation: "regex:^[A-Z]{2}-\\d{6}$"
  - source_field: "acreage"
    target_field: "area_hectares"
    dtype: "float32"
    cardinality: "optional"
    fallback: "null"
    unit_conversion: "acres_to_hectares"
    precision_tolerance: 0.001     # reject values that drift past 1 mm² after conversion
  - source_field: "zoning_code"
    target_field: "land_use_class"
    dtype: "string"
    cardinality: "optional"
    fallback: "UNKNOWN"            # documented deviation, written to the lineage record
    validation: "enum:RES,COM,IND,AGR,UNKNOWN"

The manifest carries its own semantic version, and that version is part of the contract: when an upstream schema changes, you bump the manifest rather than editing translation code. Treating mapping definitions as immutable, taggable artifacts is the subject of best practices for spatial data dictionary versioning, which this stage depends on to prevent mapping collisions when two agencies revise attribute definitions in the same release window.

Preprocessing requirements Jump to heading#

The translation engine assumes a flat, single-CRS contract on input. Three normalization steps must run before the rule loop, because each one changes the shape the rules are written against and folding them into the engine makes failures ambiguous.

Flatten nested attributes. GeoJSON and JSON sources frequently nest attributes under properties.attributes.* or repeat sub-records. The manifest addresses fields by flat dotted paths, so nested structures must be collapsed first; the deterministic flattening contract — including how list-valued properties are exploded or dropped — is covered by nested JSON/GeoJSON flattening. A record that reaches the engine still nested is a preprocessing bug, not a translation failure.

Resolve geometry dimensionality and validity. Source platforms disagree on whether Polygon and MultiPolygon are distinct types and whether Z/M coordinates are carried. Promote singletons to their multi-type when the target column is multi, drop or preserve Z per the manifest, and run make_valid so self-intersecting rings do not fail the target’s geometry constraint downstream.

Normalize the CRS once. The engine verifies the target CRS but does not repair it. Reproject every geometry to target_crs in preprocessing — delegating any non-trivial datum shift to the Datum Transformation Fallback Chains strategy — so that by the time a feature reaches the rule loop, a CRS mismatch is an assertion failure rather than an in-engine transformation.

# preprocess.py — geopandas>=0.14, pyproj>=3.6
import geopandas as gpd
from shapely import make_valid

def preprocess(gdf: gpd.GeoDataFrame, target_crs: str) -> gpd.GeoDataFrame:
    # 1. validity repair — self-intersections become valid multipolygons
    gdf = gdf.copy()
    gdf["geometry"] = gdf.geometry.apply(make_valid)
    # 2. CRS normalization — single source of truth before the engine runs
    if gdf.crs is None:
        raise ValueError("source GeoDataFrame has no CRS; resolve before translation")
    if gdf.crs.to_authority() != tuple(target_crs.split(":")):
        gdf = gdf.to_crs(target_crs)
    return gdf

Execution engine and precision guards Jump to heading#

The engine iterates the manifest rules against each preprocessed record. It must never raise an uncaught exception that aborts the batch: a single malformed record routes to quarantine and the run continues. Three error classes are caught explicitly — pyproj.exceptions.CRSError and ProjError from any residual coordinate work, pyarrow.lib.ArrowInvalid from type casts that the target column cannot hold, and the engine’s own ValueError for missing required fields — and each maps to a distinct, logged outcome rather than a stack trace.

# engine.py — pydantic>=2.0, pyproj>=3.6, pyarrow>=14
from typing import Any
import pyarrow as pa
from pyproj.exceptions import CRSError, ProjError
from pydantic import BaseModel, ValidationError

_CASTERS = {"string": str, "int32": int, "float32": float, "bool": bool}

class TranslationError(Exception):
    def __init__(self, code: str, field: str, detail: str):
        self.code, self.field, self.detail = code, field, detail
        super().__init__(f"{code}:{field}:{detail}")

def _coerce(rule: dict[str, Any], value: Any) -> Any:
    cast = _CASTERS[rule["dtype"]]
    try:
        out = cast(value)
    except (TypeError, ValueError) as exc:
        raise TranslationError("TYPE_CAST_FAILED", rule["source_field"], str(exc))
    if (conv := rule.get("unit_conversion")):
        out = _convert(out, conv)                       # named, table-driven conversion
    if (tol := rule.get("precision_tolerance")) is not None:
        clamped = round(out / tol) * tol
        if abs(clamped - out) > tol:                    # never silently round past tolerance
            raise TranslationError("TOLERANCE_EXCEEDED", rule["source_field"], f"|Δ|>{tol}")
        out = clamped
    return out

def translate_record(source: dict[str, Any], rules: list[dict]) -> dict[str, Any]:
    out: dict[str, Any] = {}
    for rule in rules:
        value = source.get(rule["source_field"])
        if value is None or value == "":
            if rule["cardinality"] == "required":
                raise TranslationError("REQUIRED_MISSING", rule["source_field"], "absent")
            out[rule["target_field"]] = None if rule.get("fallback") == "null" else rule.get("fallback")
            continue
        out[rule["target_field"]] = _coerce(rule, value)
    return out

The precision_tolerance guard is deliberately strict: a value is clamped to the tolerance grid only if it already lies within one tolerance unit of a grid point. Anything further is a TOLERANCE_EXCEEDED rejection, because silently rounding a coordinate or area that drifted beyond the contract is exactly the data degradation this stage exists to prevent. The CRS check is an assertion, not a transform — EPSG:3857-target geometries that arrive in EPSG:4326 are rejected with CRS_MISMATCH so a preprocessing gap is caught loudly rather than reprojected on the fly with surprise axis-order effects.

Failure modes and fallback routing Jump to heading#

Every record that does not pass cleanly terminates in exactly one defined outcome. The table below is the engine’s complete recovery contract; there is no implicit “skip” or “pass-through” branch.

The name-overflow row is enforced at manifest-load time rather than per record: if target_platform is shapefile and a target_field exceeds 10 characters, the engine refuses to start. Catching contract violations against the destination platform before the batch begins is cheaper and safer than discovering them mid-run.

Compliance reporting output Jump to heading#

The stage writes two streams, both append-only and machine-readable. The lineage stream records one NDJSON row per accepted record capturing what was decided; the rejection log records one row per quarantined record capturing why. Together they let an auditor reconstruct, for any output feature, exactly which manifest version and which rule produced each field — the evidence a government data team needs to defend a published dataset.

# lineage.py — one NDJSON row per record, append-only
import json, datetime as dt

def lineage_row(record_id: str, manifest_version: str, outcome: str,
                deviations: list[dict]) -> str:
    return json.dumps({
        "record_id": record_id,
        "manifest_version": manifest_version,     # ties output to an immutable contract
        "outcome": outcome,                       # PASS | QUARANTINE
        "deviations": deviations,                 # e.g. [{"field":"zoning_code","applied":"UNKNOWN"}]
        "target_crs": "EPSG:3857",
        "ts": dt.datetime.now(dt.timezone.utc).isoformat(),
    })

Minimum lineage fields are record_id, manifest_version, outcome, the list of applied deviations (each naming the field and the fallback used), and a UTC timestamp. The rejection log adds the outcome code from the failure table and the raw source value, so a TYPE_CAST_FAILED row can be triaged without re-running the batch. Any record whose outcome is not PASS is reportable, and the deviation list is what distinguishes a clean translation from one that quietly substituted defaults.

CI integration Jump to heading#

The manifest is code, so it is gated at commit time before it can ever touch production data. The CI job lints the YAML, validates it against the engine’s JSON Schema, and runs the engine against golden round-trip fixtures — a known source record with its expected target output — so a manifest edit that changes an existing mapping is caught as a diff in the fixture result.

# .github/workflows/validate-translation.yml
name: Validate Schema Translation
on: [push, pull_request]
jobs:
  validate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.11"
      - name: Install dependencies
        run: pip install "geopandas>=0.14" "pyproj>=3.6" "pydantic>=2.0" "pyarrow>=14" "pyyaml>=6.0" "jsonschema>=4" "yamllint>=1.35" "pytest>=7"
      - name: Lint manifest
        run: yamllint -d relaxed config/schema_mapping.yaml
      - name: Validate manifest against JSON Schema
        run: python -m pipeline.validate_manifest config/schema_mapping.yaml schemas/translation_v1.json
      - name: Run golden round-trip fixtures
        run: pytest tests/test_translation.py -v --tb=short

A useful pre-commit guard pairs with the CI job: a pytest fixture that loads the manifest, asserts every optional rule declares a fallback, and asserts no target_field exceeds the limit for the declared target_platform. Once deployed, a scheduled job hashes the upstream source schema and compares field cardinality against the manifest; an unexpected new or dropped field halts the ETL run and pages the on-call engineer rather than letting an unmapped column flow through. That drift-detection wrapper reuses the same retry and circuit-breaker patterns described in Error Handling & Retry Logic.

Frequently Asked Questions Jump to heading#

Why verify the CRS in the engine instead of just reprojecting whatever arrives? Because reprojecting inside the translation loop hides a preprocessing failure and risks surprise axis-order inversion. The engine’s job is to prove the contract holds, so an EPSG:3857-target feature arriving in EPSG:4326 is a CRS_MISMATCH rejection that points back at the preprocessing stage. Reprojection — including any datum shift — belongs upstream in CRS Normalization & Sync, where a missing transformation grid can be handled with a documented fallback chain rather than silently swallowed mid-translation.

What is the difference between a required quarantine and an optional fallback? A required field with no value quarantines the entire record — it cannot be published with a guessed identifier. An optional field with no value substitutes the declared fallback and the record continues, but the substitution is written to the lineage deviations list. The two are not interchangeable: cardinality is the single directive that decides whether a gap is fatal or recoverable, which is why it is mandatory on every rule.

How should target name-length limits be handled when going to Shapefile? Validate them at manifest-load time, not per record. Shapefile DBF caps field names at 10 characters and PostGIS at 63; the engine reads target_platform and refuses to start if any target_field overflows the limit, raising NAME_TRUNCATION_RISK. Catching it once against the destination platform is far cheaper than discovering truncated, colliding column names after a multi-hour batch.

Can one manifest target multiple platforms at once? Keep one manifest per target platform and share a common include for the field-mapping core. A single manifest cannot simultaneously satisfy a 10-character Shapefile limit and a GeoParquet logical-type binding, and pretending it can pushes platform-specific decisions into the engine. Version each platform manifest independently per best practices for spatial data dictionary versioning so a PostGIS change does not force a Shapefile re-release.

Deeper implementation walkthroughs Jump to heading#

Focused builds for the hardest parts of this stage live in the implementation guides under this topic. For keeping mapping definitions diffable, taggable, and safe to evolve across releases, work through best practices for spatial data dictionary versioning. When the target model is a regulated schema, the manifest’s validation and cardinality rules must encode the controlled vocabularies described in how to map INSPIRE Annex III to local PostgreSQL schemas and the crosswalk in converting FGDC CSDGM to ISO 19115 automatically.

Related Jump to heading#

• INSPIRE Directive Schema Compliance — the Annex II/III target-model constraints a translation manifest must satisfy for European data infrastructure
• FGDC Metadata Mapping — federal metadata field requirements that drive controlled vocabularies in the manifest
• Local Government Data Dictionaries — municipal source schemas that most often feed cross-platform translation
• Field Renaming & Type Coercion Rules — the casting and renaming primitives the engine applies per rule
• CRS Normalization & Sync — the upstream stage that guarantees geometries arrive in the declared target CRS