Running DuckDB Spatial Queries in CI

This guide runs real spatial SQL against real lakehouse data inside a continuous-integration pipeline, fast enough to gate every pull request, so a change that breaks a spatial query fails a build rather than a dashboard.

Context and prerequisites

Spatial correctness regressions are usually silent — a predicate that stops pruning, a layout change that breaks a join, a repair that alters a geometry — so a test that actually executes the queries is worth more than any amount of review. DuckDB makes this affordable: it starts in milliseconds, needs no cluster, and evaluates the same ST_* functions production uses. This recipe needs DuckDB 1.1+ with the spatial extension; the engine positioning is in DuckDB geospatial analytics.

What to test, and against what data

The middle tier is the one most platforms lack and the one that catches the most. Fixtures test the code; the sampled tier tests the table — that its schema still carries what queries assume, that its statistics still exist, that its CRS has not changed. Those are properties of production data that no fixture can express.

Complete working solution

# conftest.py — one connection per session, extensions baked into the image.
import duckdb, pytest

@pytest.fixture(scope="session")
def con():
    c = duckdb.connect()
    c.execute("LOAD spatial;")            # pre-installed in the CI image
    c.execute("SET memory_limit='2GB';")  # bounded, so a runaway test fails fast
    yield c
    c.close()

@pytest.fixture(scope="session")
def sample(con):
    """One partition of the real table, read directly from object storage."""
    con.execute("LOAD httpfs;")
    con.execute("CREATE OR REPLACE SECRET s (TYPE S3, PROVIDER credential_chain);")
    con.execute("""
        CREATE TABLE sample AS
        SELECT * FROM read_parquet('s3://lakehouse/telemetry/event_day=2026-03-11/*.parquet')
        LIMIT 200000
    """)
    return "sample"

# test_spatial_contract.py — properties of the table, not of the code.
def test_geometry_decodes(con, sample):
    bad = con.execute(f"""
        SELECT count(*) FROM {sample}
        WHERE TRY(ST_GeomFromWKB(geom_wkb)) IS NULL AND geom_wkb IS NOT NULL
    """).fetchone()[0]
    assert bad == 0, f"{bad} rows have undecodable geometry"

def test_bbox_covers_geometry(con, sample):
    violations = con.execute(f"""
        SELECT count(*) FROM {sample}
        WHERE NOT ST_Covers(
            ST_MakeEnvelope(bbox_min_x, bbox_min_y, bbox_max_x, bbox_max_y),
            ST_GeomFromWKB(geom_wkb))
    """).fetchone()[0]
    assert violations == 0, f"{violations} bounding boxes do not cover their geometry"

def test_coordinates_are_geographic(con, sample):
    outside = con.execute(f"""
        SELECT count(*) FROM {sample}
        WHERE abs(bbox_min_x) > 180 OR abs(bbox_max_x) > 180
           OR abs(bbox_min_y) > 90  OR abs(bbox_max_y) > 90
    """).fetchone()[0]
    assert outside == 0, "coordinates outside the declared geographic range"

def test_all_geometries_valid(con, sample):
    invalid = con.execute(f"""
        SELECT count(*) FROM {sample}
        WHERE NOT ST_IsValid(ST_GeomFromWKB(geom_wkb))
    """).fetchone()[0]
    assert invalid == 0, f"{invalid} invalid geometries reached the table"

Step-by-step walkthrough

1. Bake the extensions into the image. Installing spatial at test time adds a network dependency to every run and fails in air-gapped builds. Pre-installing removes both problems and shaves seconds off each run.

2. Bound the memory limit. A test that accidentally scans far more than intended should fail quickly rather than exhaust the runner. A limit well below the runner’s memory turns a runaway into a fast, clear failure.

3. Read one partition directly. The sample does not need to be representative in volume, only in shape. One day of one partition exercises every contract property and downloads a manageable amount.

4. Test properties, not values. Asserting that every bounding box covers its geometry is stable across data changes; asserting a specific row count is not, and will fail every day for reasons nobody cares about.

5. Use TRY around decoding. A malformed geometry should produce a counted failure rather than an exception that aborts the test and hides how many others are affected.

Common errors and fixes

Testing that pruning still works

def test_scoped_query_prunes(con, sample_path):
    plan = con.execute(f"""
        EXPLAIN ANALYZE
        SELECT count(*) FROM read_parquet('{sample_path}')
        WHERE bbox_min_x >= 13.0 AND bbox_max_x <= 13.8
          AND bbox_min_y >= 52.3 AND bbox_max_y <= 52.7
    """).fetchall()
    text = "\n".join(row[1] for row in plan)
    scanned, total = parse_row_groups(text)
    assert scanned / total < 0.10, (
        f"pruning regressed: {scanned}/{total} row groups scanned")

This is the assertion worth having above all others, because the regression it catches is invisible in every other kind of test: the results are identical, no error is raised, and the only symptom is cost. A schema change that pushes the bounding-box columns past the statistics limit, or a write path that stops sorting, produces exactly this and nothing else notices.

Run it against a fixed extract stored with the tests rather than against live data, so the assertion measures the code’s effect rather than the day’s data. Refresh the extract deliberately, as a reviewed change, when the table’s shape genuinely changes.

Keeping the Suite Fast

A spatial CI suite that takes ten minutes will be skipped under deadline pressure, and a skipped test catches nothing. Four practices keep it under a minute.

The last practice is the one that most often goes wrong. A test that fetches a result set into Python and compares it row by row transfers and materialises data the assertion did not need; the same test expressed as a count(*) of violations returns one integer and runs in a fraction of the time. Push the comparison into the query.

The second practice creates a tension worth naming: a checked-in extract is fast and can go stale, while reading live data is current and slow. The resolution is the tiering described earlier — the per-commit tier uses the extract, the per-pull-request tier reads one live partition, and the extract is refreshed as a deliberate, reviewed change when the table’s shape moves.

What This Cannot Test

Being explicit about the boundary avoids false confidence.

A single-node engine cannot test distributed behaviour: shuffle correctness, skew, broadcast thresholds and partitioner behaviour are properties of the cluster runtime and need a test there. What it can test is that the SQL is semantically correct, which is usually where the bugs are.

It cannot test concurrency: resource-group behaviour, admission control and multi-user isolation belong to the serving engine. A CI suite that passes says nothing about whether the platform holds up under twenty simultaneous users.

And it cannot test the full data volume, so a query that is correct on a sample and pathological on the whole table — a join whose fan-out explodes at scale, an aggregation whose cardinality exceeds memory — will pass. The nightly full-extract tier exists precisely to narrow that gap, and it narrows rather than closes it.

Within those limits, the coverage is substantial and the cost is close to zero, which is an unusually good trade for a test suite. The failures it catches — a broken predicate, a lost sort order, a changed coordinate system, an invalid geometry reaching the table — account for most spatial incidents, and all four are silent everywhere else.

Wiring It Into the Pipeline

Where the checks run matters as much as what they check, because a gate that runs after a deployment protects nothing.

The fixture tier belongs in the ordinary unit-test job, running on every push, with no credentials and no network. Treating it as an ordinary test suite rather than as something special keeps it maintained.

The contract tier belongs in the pull-request job, with read-only credentials scoped to the sampled table. It is the tier that needs the most care around access: a CI runner with broad read access to a governed spatial platform is a security surface, so scope the role to exactly the tables the tests read and nothing else.

The nightly tier belongs on a schedule with its results published to the same metrics table the observability layer uses, rather than as a build that fails. Its purpose is trend detection — pruning ratios and timings drifting over weeks — and a failing nightly build tends to be muted while a trend on a dashboard gets reviewed.

One last piece is worth adding: run the contract tier against the table the change targets, resolved from the diff. A change touching one pipeline should test that pipeline’s table rather than every table on the platform, which keeps the pull-request job fast and keeps its failures relevant to the change under review. Scoping the run to the change also keeps the failure attributable, which is what makes a red build get fixed rather than retried. A relevant failure gets fixed; an unattributable one gets retried.