Building ETL Chains for Vector Data

In short: design a vector chain as a sequence of steps with explicit data contracts — format, CRS, geometry type, validity guarantee — rather than as a script that happens to run in order. Each step should be independently re-runnable from its predecessor’s output, and the expensive validation should happen once, at the boundary where untrusted data enters, not repeatedly in every step that touches a geometry.

The default shape of a vector pipeline is a single long function: read the shapefile, reproject it, fix the geometries, join the attributes, write to PostGIS. It works until something in the middle fails, at which point the only recovery is to run the whole thing again — including the twenty minutes of reprojection that succeeded. Breaking it into a chain is not about elegance; it is about making the failure boundary the same size as the work that failed.

Prerequisites & Architecture Baseline#

• GDAL/OGR ≥ 3.6 and geopandas ≥ 0.14, pinned together — a GDAL change can alter how a driver reports geometry types
• An intermediate format that is cheap to write and cheap to read back: GeoPackage or GeoParquet, never shapefile
• Scratch storage with enough room for the largest intermediate, plus a cleanup step that is not optional
• A declared target CRS for the whole chain, chosen once rather than per step
• pyproj ≥ 3.6 for CRS handling, and a decision about axis order recorded in writing
• PostGIS ≥ 3.3 if the chain terminates in a database, with the target table's constraints known in advance

Core Principles#

1. Every step declares what it consumes and what it produces. Not informally, in a docstring, but as a small structure the step can assert against: format, CRS, geometry type, whether validity is guaranteed, whether the data is deduplicated. A chain of five steps has four handoffs, and every bug that costs a day lives at one of them — usually because two steps disagreed about whether geometries were already made valid.

2. Intermediates are files, not in-memory frames. Passing a GeoDataFrame between tasks means the whole chain lives or dies in one process, and a three-gigabyte extract lives in RAM for the duration. Writing each step’s output to GeoPackage or GeoParquet costs seconds and buys independent re-runnability, inspectability with ogrinfo, and a worker that can be replaced mid-chain.

3. Reproject once, at a known point. Reprojection is the most commonly duplicated step in vector chains: one step transforms to the target CRS, a later one transforms “to be safe”, and the second transform is a no-op that costs an hour on a large dataset — or worse, is not a no-op because the first step’s output was mislabelled. Put it in exactly one place and let the contract carry the CRS afterwards.

4. Validate at the boundary, not everywhere. ST_IsValid on every geometry in every step is expensive and mostly redundant. Validate once, when data crosses from outside your control into the chain, and record the guarantee in the contract. Steps downstream trust the contract; if they cannot, the contract is wrong and should be fixed rather than worked around with defensive re-validation.

5. Attribute joins are a separate step from geometry work. They fail differently — a missing key rather than a self-intersection — and they scale differently. Keeping them separate means a join failure does not force a re-run of the reprojection, and it makes the expensive geometry steps cacheable independently, which is what caching strategies for spatial tasks needs to be effective.

6. The chain’s shape should match the data’s natural partitions. If the source arrives per municipality, the chain should run per municipality rather than concatenating everything and splitting later. That preserves the ability to re-run one failing partition, and it keeps memory bounded by the largest partition rather than by the total — the same argument partitioning strategies for spatial workloads makes for raster.

Contracts also change how a chain is reviewed. Without them, reading a five-step pipeline means holding the whole data state in your head — has this been reprojected yet, is it valid here, are these deduplicated — and the answer is only discoverable by reading every preceding step. With them, each step’s requirements are local and legible, and a reviewer can check one handoff at a time. That is a small thing on a chain you wrote last week and an enormous one on a chain you inherited.

Production Implementation#

The chain below expresses its contracts as a small dataclass that every step asserts on entry and returns on exit. The assertion is cheap — it reads a header, not the features — and it turns a class of silent misalignment into an immediate, legible failure.

from __future__ import annotations

from dataclasses import dataclass, replace
from pathlib import Path

import fiona
import geopandas as gpd
from prefect import flow, task


@dataclass(frozen=True)
class VectorContract:
    """What a step promises about the file it produced."""

    path: Path
    crs: str                    # authority string, e.g. "EPSG:25832"
    geometry_type: str          # "MultiPolygon", "LineString", …
    validity_guaranteed: bool
    deduplicated: bool
    feature_count: int

    def assert_matches(self, *, crs: str | None = None,
                       requires_valid: bool = False) -> None:
        if crs is not None and self.crs != crs:
            raise ValueError(f"expected {crs}, upstream produced {self.crs}")
        if requires_valid and not self.validity_guaranteed:
            raise ValueError("this step requires repaired geometries upstream")


def describe(path: Path) -> tuple[str, str, int]:
    """Read the layer header only — no feature scan, no memory cost."""
    with fiona.open(path) as src:
        return (src.crs_wkt and src.crs.get("init", "").upper() or str(src.crs),
                src.schema["geometry"], len(src))


@task
def extract(source_url: str, out: Path) -> VectorContract:
    frame = gpd.read_file(source_url)
    # GeoPackage, never shapefile: no 10-character field names, no 2 GB limit,
    # no five-file bundle to keep together.
    frame.to_file(out, driver="GPKG", layer="features")
    crs, geom_type, count = describe(out)
    return VectorContract(out, crs, geom_type, False, False, count)


@task
def repair(upstream: VectorContract, out: Path) -> VectorContract:
    frame = gpd.read_file(upstream.path)
    # make_valid can change the geometry type (a bow-tie becomes a MultiPolygon),
    # so normalise afterwards rather than assuming it stayed put.
    frame["geometry"] = frame.geometry.make_valid()
    frame = frame[~frame.geometry.is_empty]
    frame.to_file(out, driver="GPKG", layer="features")
    crs, geom_type, count = describe(out)
    return replace(upstream, path=out, geometry_type=geom_type,
                   validity_guaranteed=True, feature_count=count)


@task
def reproject(upstream: VectorContract, target_crs: str, out: Path) -> VectorContract:
    upstream.assert_matches(requires_valid=True)      # repair BEFORE transform
    if upstream.crs == target_crs:
        return upstream                                # the no-op that must not cost an hour
    frame = gpd.read_file(upstream.path).to_crs(target_crs)
    frame.to_file(out, driver="GPKG", layer="features")
    return replace(upstream, path=out, crs=target_crs)


@flow
def vector_chain(source_url: str, scratch: Path, target_crs: str = "EPSG:25832") -> VectorContract:
    raw = extract(source_url, scratch / "01_raw.gpkg")
    clean = repair(raw, scratch / "02_valid.gpkg")
    projected = reproject(clean, target_crs, scratch / "03_projected.gpkg")
    projected.assert_matches(crs=target_crs, requires_valid=True)
    return projected

Step-by-Step Walkthrough#

1. Extract into a real container format. GeoPackage or GeoParquet, not shapefile: no ten-character field-name truncation, no 2 GB ceiling, no multi-file bundle whose parts can be separated. The extract step’s only job is to get bytes onto disk in a format the rest of the chain can trust.
2. Repair before you transform. make_valid on a geometry in its source CRS is well-defined; the same repair after a transform operates on coordinates that a projection may have already distorted near the source’s edges. Order matters, and this order is the one that produces fewer surprises.
3. Re-describe after repair. make_valid can turn a Polygon into a MultiPolygon or a GeometryCollection. Reading the header again rather than assuming the type is unchanged is what stops a load step failing on a typed PostGIS column three steps later.
4. Make the no-op reprojection actually free. The if upstream.crs == target_crs: return upstream line is the single highest-value branch in the chain. Without it, a source that already publishes in the target CRS still pays for a full transform of every vertex.
5. Assert the contract on entry, not on exit. A step that checks its own output is checking its own code; a step that checks its input is checking the handoff, which is where the bugs are.
6. Keep intermediates until the chain succeeds. 01_raw.gpkg through 03_projected.gpkg cost disk and save hours: when the load fails, the projected file is right there and the re-run starts from step four.
7. Clean up explicitly, in a step that runs on success only. Cleaning up in a finally deletes the evidence of exactly the run you most want to inspect.

Edge Cases & Failure Recovery#

A source that changes its geometry type between deliveries. A layer that has always been Polygon arrives one week as MultiPolygon, and the load step fails on a typed column. The contract catches this at the handoff, and the useful response is usually to normalise everything to multi-part at the repair step, which is lossless and removes the whole category.

Mixed geometry types in one layer. GeoPackage permits it; PostGIS typed columns do not, and neither does a GeoDataFrame with a single declared type. Split by type into separate outputs, or promote everything to the most general type in the chain. Deciding this in the contract is much cheaper than discovering it in the load.

Attribute encoding that survives the geometry. A .dbf in Latin-1 read as UTF-8 produces mojibake in place names while every geometry is perfect, so nothing fails and the error ships. Set the encoding explicitly on the extract, and assert on a known place name in the test fixture — this is one of the very few spatial bugs that a byte-level check catches and a geometry check never will.

A join that silently drops features. An inner join on a key with mismatched types — integer municipality codes against zero-padded strings — quietly halves the dataset. Assert the feature count against the contract after every join, and treat any unexplained drop as a failure rather than as data quality.

A step that succeeds on empty input. A filter that removes every feature, or an extract whose bounding box missed the data, produces a valid file with zero features — and every subsequent step processes it perfectly. The chain completes, the load writes nothing, and the table is simply not updated. Assert a plausible minimum feature count in the contract for any step that should not be able to empty the dataset; “greater than zero” catches most of it and costs one comparison.

Geometry that is valid but absurd. make_valid will happily repair a polygon whose coordinates are in the wrong units into a valid polygon of the wrong size. Validity is a topological property, not a semantic one, and a chain that only checks validity will pass data whose area is off by a factor of a million. A cheap bounds assertion against the target CRS’s expected range catches this, and belongs in the same place as CRS validation.

Scratch storage filling mid-chain. Intermediates are cheap until they are not: five steps over a 4 GB source can need 20 GB. Size scratch from the largest expected source times the number of retained steps, and fail the flow early with a clear message rather than at whichever step happens to write the last byte.

Recovery from any of these follows the same shape, which is worth stating once rather than per case: identify the earliest step whose output is wrong, delete that intermediate and everything after it, fix the step, and re-run from there. Because the intermediates are files with predictable names, that is three shell commands rather than a code change, and because each step asserts its input, a re-run that starts from the wrong place fails immediately instead of producing something plausible.

Configuration Reference#

The one setting that resists a default is where to put the chain’s partition boundary. Running one chain per source file is simplest and gives the finest re-run granularity, but a thousand small files means a thousand flow runs and an orchestrator busier bookkeeping than working. Running one chain over everything is the opposite failure. The practical answer is usually to group by whatever the source’s own natural unit is — municipality, map sheet, delivery batch — and accept a range of partition sizes rather than trying to equalise them, which is a substantial job in its own right.

Frequently Asked Questions#

Related#

• How to chain GDAL tasks in Prefect — the orchestrator-level wiring
• Streaming large GeoPackage loads into PostGIS — the load step at scale
• Validating coordinate systems before ETL — the boundary check the contract depends on
• Repairing invalid geometries before load — what the repair step actually does
• Caching strategies for spatial tasks — making the intermediates reusable across runs

← Spatial Task Design & Dependency Mapping