Orchestrating Spatial ETL Pipelines

A spatial pipeline that runs correctly once from a notebook is not a pipeline — it is a demo. The moment the same logic must run every night against a moving archive of satellite scenes, recover cleanly from a rate-limited download halfway through, and never double-load a tile after a retry, the hard problem stops being the geometry and becomes the orchestration. For the GIS analysts and data engineers running production spatial systems, the orchestrator is what turns a collection of extract, clean, and load scripts into an auditable, restartable, observable data product.

Both the geospatial data ingestion and vector and raster cleaning references assume an orchestration layer underneath them — retries on the Extract step, checkpoints between stages, a scheduler that knows which windows have already been processed. This guide is that layer. It covers how to structure spatial ETL as directed acyclic graphs (DAGs), how to keep tasks idempotent when downloads fail mid-flight, how to partition raster and vector work so backfills are cheap, and how to choose between Airflow, Prefect, and Dagster for a workload defined by tiles, scenes, and coordinate systems rather than rows in a warehouse.

The Orchestrated Spatial Pipeline: A Reference Model

A spatial ETL DAG is not a linear script with a cron trigger bolted on. Each stage is a separately retryable, separately observable unit with an explicit contract for what it consumes and produces. The diagram below shows the canonical shape — the same five stages as any ingestion pipeline, but now every arrow is a durable handoff through object storage and every box is a task the scheduler can retry, skip, or backfill independently.

Orchestrated spatial ETL reference model A scheduler or sensor triggers a DAG of five tasks — Discover, Extract, Transform, Validate, Load — connected left to right. Each task persists its output to object storage before the next begins. The Extract task carries a retry loop; the Validate task branches failed records to a dead-letter store. Scheduler / Sensor Discover STAC · manifest Extract retry + backoff Transform CRS · repair Validate quality gates dead-letter store Load PostGIS · Parquet object storage — durable handoff between every task

Discover — Resolve which units of work exist for this run: STAC items in a time window, tiles intersecting an area of interest, or new files behind an ETag. Discovery emits a manifest, never the data itself, so the graph can be planned before a byte is downloaded. This is where detecting dataset changes with ETag and Last-Modified prevents re-processing unchanged sources.

Extract — Pull raw payloads. This is the only stage that should carry aggressive retries, because network failures are transient; a retry on bad data downstream just wastes compute. Extract writes raw bytes to object storage keyed by a stable identifier.

Transform — Normalize CRS, repair geometry, and align schemas against the raw bytes from the previous stage. Transform is stateless and deterministic so any single unit can be reprocessed in isolation during a backfill.

Validate — Run quality gates and route failures to a dead-letter store instead of crashing the run. A single malformed scene must not block the other 499.

Load — Write validated outputs to the analytical target — PostGIS, DuckDB, or partitioned GeoParquet — using upsert or atomic-replace semantics keyed on the same identifier used during Extract.

The three orchestrators covered here — Airflow, Prefect, and Dagster — express this model differently. Airflow encodes it as DAGs of operators on a fixed schedule; Prefect as Python-native flows with runtime fan-out; Dagster as partitioned, testable data assets. The orchestrator selection guide maps each to the spatial workloads it fits best.

Framework-Specific Orchestration Patterns

The five-stage model is universal; how you express it determines how cheap backfills are, how readable the DAG stays at fifty tasks, and how hard it is to test a single stage in isolation.

Airflow — Operator DAGs on a Fixed Schedule

Airflow remains the default for pipelines that run on a predictable cadence and lean on a broad operator ecosystem (S3, GCS, Postgres, HTTP). For spatial work the two recurring pitfalls are non-idempotent tasks that double-load tiles on retry, and the temptation to shuttle GeoDataFrames through XCom. Both are solved by treating object storage as the handoff medium: see writing idempotent spatial ETL tasks in Airflow and passing GeoDataFrames between Airflow tasks for the concrete patterns. The full DAG structure — scheduling, sensors, and pools sized for rate-limited APIs — is covered in building Airflow DAGs for spatial ETL.

Prefect — Python-Native Flows with Runtime Fan-Out

When the number of units of work is only known at runtime — the count of STAC items a query returns, the tiles intersecting a shifting area of interest — Prefect’s .map() fan-out expresses the pipeline more naturally than static Airflow tasks. Orchestrating spatial pipelines with Prefect shows the flow structure, while caching geospatial task results in Prefect and parallel tile downloads with Prefect task mapping cover the two features that matter most for spatial fan-out: content-addressed caching and bounded concurrent mapping.

Dagster — Partitioned, Testable Spatial Assets

Dagster models each output as a data asset with an explicit partitioning scheme, which maps almost perfectly onto spatial data indexed by tile or scene. Managing spatial assets with Dagster covers the asset graph; partitioning spatial assets by tile in Dagster and backfilling satellite scene partitions in Dagster show how partitioning turns a full-history reprocess into a targeted, parallelizable backfill instead of a monolithic re-run.

Cross-Cutting Concerns

Three concerns cut across every framework and must be designed at the pipeline level, not patched into individual tasks.

Idempotency and Exactly-Once Loads

Retries are a feature, which means every task will eventually run more than once. A pipeline is only safe if a second run produces the same state as the first. The universal pattern is to key each write on a stable identifier and use upsert semantics:

import geopandas as gpd
from sqlalchemy import create_engine, text

def upsert_tiles(gdf: gpd.GeoDataFrame, engine, table: str, key: str = "tile_id") -> int:
    """Atomically replace rows for the tiles present in this batch.

    Idempotent: re-running with the same batch leaves the table unchanged
    rather than appending duplicate geometries.
    """
    tile_ids = gdf[key].unique().tolist()
    with engine.begin() as conn:  # single transaction — delete + insert are atomic
        conn.execute(
            text(f"DELETE FROM {table} WHERE {key} = ANY(:ids)"),
            {"ids": tile_ids},
        )
        gdf.to_postgis(table, conn, if_exists="append", index=False)
    return len(gdf)

The same principle governs file outputs: write to a .part path and rename atomically only after validation, so an interrupted task never leaves a half-written GeoParquet that the next run mistakes for complete.

Scheduling, Backfills, and Incremental Windows

Spatial archives grow along a time axis — daily satellite passes, weekly portal refreshes — so the scheduler’s job is to track which windows have been processed and to make reprocessing a bounded operation. Scheduling and incremental spatial loads covers watermark tracking and catch-up runs; the key discipline is that a backfill of last month must cost exactly the compute of last month’s data, which only holds if the pipeline is partitioned rather than monolithic.

Load-Target Selection

The Load stage’s target shapes the whole pipeline: PostGIS gives you transactional upserts and a mature spatial index, while DuckDB’s spatial extension gives you columnar scan speed over GeoParquet with no server to operate. The PostGIS versus DuckDB comparison for analytical loads works through the decision, and loading GeoDataFrames into PostGIS with GeoPandas shows the write path in detail.

Validation and Quality Gates in a DAG

Orchestrated validation differs from script-level validation in one way: a failing record must not fail the run. Route it to a dead-letter store and let the graph continue, then alert on dead-letter volume rather than on individual failures.

import logging
import geopandas as gpd

logger = logging.getLogger(__name__)

def gate_and_split(
    gdf: gpd.GeoDataFrame,
    required: list[str],
) -> tuple[gpd.GeoDataFrame, gpd.GeoDataFrame]:
    """Split a batch into (passed, dead_letter) instead of raising.

    Lets an orchestrated task load the good rows and quarantine the rest,
    so one bad scene never blocks a 500-scene backfill.
    """
    import shapely

    valid_geom = shapely.is_valid(gdf.geometry.values) & ~gdf.geometry.isna().values
    has_cols = gdf[required].notna().all(axis=1).values if required else True
    keep = valid_geom & has_cols

    passed = gdf.loc[keep].copy()
    dead = gdf.loc[~keep].copy()
    if len(dead):
        logger.warning("Quarantined %d/%d features to dead-letter", len(dead), len(gdf))
    return passed, dead

The gate_and_split contract composes with the geometry checks from geometry repair with Shapely and GeoPandas — run the repair inside Transform, then split on the residual invalids here in Validate.

Failure-Mode Reference

Failure Mode Root Cause Mitigation Strategy
Duplicate geometries after a retry Task appends instead of upserting; retry re-runs the load Key writes on tile_id/scene_id; delete-then-insert in one transaction
Worker OOM passing data between tasks GeoDataFrame serialized through XCom or result store Persist to GeoParquet in object storage; pass only the URI
Backfill of one month re-runs full history Monolithic DAG with no partitioning Partition by date/tile so backfills target only affected partitions
Silent gap in a time series Scheduler has no record of which windows ran Track a watermark timestamp; a sensor or catch-up run fills gaps
Whole run fails on one corrupt scene Validation raises instead of quarantining Dead-letter the bad record; alert on dead-letter volume, not per-record

Production Integration Notes

Wire the orchestrator to the same object-storage layout the ingestion and cleaning workflows produce: partition GeoParquet by temporal granularity and a spatial index (H3 resolution 5 or quadkey depth 8) so both backfills and downstream analytical scans touch only the partitions they need. Size concurrency pools to the slowest external dependency — a rate-limited Overpass or STAC endpoint, not the worker count — so the orchestrator throttles OSM extraction and STAC catalog syncs within their rate envelopes rather than tripping HTTP 429s across a whole fan-out. Finally, emit structured logs at every task boundary with the partition key attached, so an operator can answer “did tile 85283473 load last night?” without reading a stack trace.