How to Use DuckDB as the Engine in a GIS Pipeline
Problem statement
A GIS pipeline usually accretes: read a shapefile, filter it in pandas, join it, write a GeoPackage, read that back, aggregate it, write a CSV. Each stage materialises everything, each intermediate file has to be managed, and the whole thing runs at the speed of the slowest materialisation.
Using DuckDB as the pipeline's engine changes the structure rather than just the speed. The transformations become SQL views over the source files, nothing is materialised until the end, and the intermediate files disappear.
The scale difference is measurable: a 13,464,017-point join and aggregate ran in 61.8 s using 305 MB, against 117.5 s and 4,739 MB for the equivalent GeoPandas pipeline. But the more important change is that there is one artefact — the query — instead of six intermediate files.
Quick answer
Express the pipeline as chained SQL, materialise once:
import duckdb
def run_pipeline(config):
con = duckdb.connect(config["database"])
con.execute("install spatial; load spatial;")
con.execute("set enable_progress_bar = false")
con.execute(f"set memory_limit = '{config['memory_limit']}'")
con.execute(f"set temp_directory = '{config['temp_dir']}'")
con.execute(f"""
create or replace view raw as
select * from read_parquet('{config["input"]}')
""")
con.execute("""
create or replace view cleaned as
select * from raw
where lat between -90 and 90 and lon between -180 and 180
and lat is not null
""")
con.execute(f"""
create or replace view joined as
select c.*, a.name as area
from cleaned c
join st_read('{config["areas"]}') a
on st_intersects(st_point(c.lon, c.lat), a.geom)
""")
con.execute(f"""
copy (select area, count(*) as n from joined group by 1 order by n desc)
to '{config["output"]}' (format parquet)
""")Views cost nothing — they are query fragments. Only the final COPY touches the disk.
Step-by-step solution
1. Make the stages views, not tables
A view is a named query. Chaining views composes into one plan that the optimiser sees whole, so filters push down into the file readers and columns nobody uses are never read.
Materialising each stage as a table does the opposite: it forces every intermediate result into storage and hides the optimisation opportunities.
Use a real table only when a stage is expensive and reused several times downstream.
2. Keep the SQL in files, not in strings
Pipeline SQL grows. Keeping it in .sql files makes it reviewable, diffable, syntax-highlighted and testable:
pipeline/
01_raw.sql
02_cleaned.sql
03_joined.sql
04_summary.sql
Then the Python is a thin runner that reads, parameterises and executes them in order — which is also what makes the pipeline easy to run partially while developing.
3. Parameterise properly
String-formatting a path into SQL is fine for a trusted config and wrong for anything user-supplied. DuckDB supports parameters:
con.execute("select count(*) from read_parquet(?) where country = ?",
[path, country])For paths that must be interpolated — table names, file globs — validate them against an allowlist rather than trusting them.
4. Validate between stages
The advantage of a database engine is that assertions are queries:
def assert_rows(con, relation, minimum=1, maximum=None):
n = con.execute(f"select count(*) from {relation}").fetchone()[0]
if n < minimum or (maximum and n > maximum):
raise ValueError(f"{relation}: {n:,} rows, expected {minimum:,}–{maximum or '∞'}")
return nCheap checks worth running after every stage: row count within an expected range, no nulls in key columns, no duplicate keys, and — for spatial stages — the count of features that matched nothing.
5. Record what ran
A pipeline that cannot say what it did is not reproducible. Record the inputs, their sizes and modification times, the DuckDB and extension versions, the row counts at each stage, and the wall-clock time:
con.execute("""
create table if not exists _run_log (
run_id varchar, stage varchar, rows bigint,
seconds double, at timestamp)
""")Writing the log into the same database file keeps the provenance next to the output.
6. Make it idempotent
create or replace view and create or replace table are idempotent by construction. COPY ... TO overwrites. That means a rerun produces the same result and no duplicates, which is the property that makes a pipeline safe to retry.
The one thing to watch: a COPY ... TO into a partitioned directory needs overwrite_or_ignore, or a rerun fails on the existing directory.
Code examples
Example 1 — a runner that executes numbered SQL files
import glob
import os
import time
import uuid
import duckdb
class SqlPipeline:
"""Runs numbered .sql files in order, with logging and validation."""
def __init__(self, sql_dir, database=":memory:", settings=None, params=None):
self.sql_dir = sql_dir
self.params = params or {}
self.run_id = uuid.uuid4().hex[:8]
self.con = duckdb.connect(database)
self.con.execute("install spatial; load spatial;")
self.con.execute("set enable_progress_bar = false")
for key, value in (settings or {}).items():
self.con.execute(f"set {key} = '{value}'")
self.con.execute("""
create table if not exists _run_log (
run_id varchar, stage varchar, rows bigint,
seconds double, at timestamp)
""")
def run(self, upto=None):
files = sorted(glob.glob(os.path.join(self.sql_dir, "*.sql")))
for path in files:
stage = os.path.basename(path)
if upto and stage > upto:
break
sql = open(path, encoding="utf-8").read().format(**self.params)
started = time.perf_counter()
self.con.execute(sql)
elapsed = time.perf_counter() - started
rows = self._rows_of(stage)
self.con.execute(
"insert into _run_log values (?, ?, ?, ?, now())",
[self.run_id, stage, rows, elapsed])
print(f"{stage:24} {rows if rows is not None else '-':>12} {elapsed:6.2f}s")
return self
def _rows_of(self, stage):
relation = os.path.splitext(stage)[0].split("_", 1)[-1]
try:
return self.con.execute(f"select count(*) from {relation}").fetchone()[0]
except Exception:
return NoneExample 2 — validation as SQL
CHECKS = {
"no null geometry": "select count(*) from {r} where geom is null",
"no duplicate ids": ("select count(*) from (select id from {r} "
"group by 1 having count(*) > 1)"),
"coordinates in range": ("select count(*) from {r} "
"where lat not between -90 and 90 "
"or lon not between -180 and 180"),
"all rows matched an area": "select count(*) from {r} where area is null",
}
def validate(con, relation, checks=CHECKS, tolerate=None):
"""Every check returns a count of bad rows; zero is a pass."""
tolerate = tolerate or {}
failures = []
for name, template in checks.items():
bad = con.execute(template.format(r=relation)).fetchone()[0]
allowed = tolerate.get(name, 0)
status = "ok" if bad <= allowed else "FAIL"
print(f" {name:26} {bad:8,} {status}")
if bad > allowed:
failures.append(f"{name}: {bad:,} rows (allowed {allowed:,})")
if failures:
raise ValueError(f"{relation} failed validation:\n " + "\n ".join(failures))The tolerate argument is what makes this usable on real data. A spatial join legitimately leaves some rows unmatched — measured, 3.9% of a global point set fell outside every province polygon — so the check should record the number and fail only when it moves.
Example 3 — the pipeline's provenance record
def write_provenance(con, inputs, output, run_id):
"""What ran, against what, with which versions."""
import os
import datetime
import duckdb
rows = []
for path in inputs:
stat = os.stat(path)
rows.append((run_id, path, stat.st_size,
datetime.datetime.fromtimestamp(stat.st_mtime).isoformat()))
con.execute("""
create table if not exists _inputs (
run_id varchar, path varchar, bytes bigint, modified varchar)
""")
con.executemany("insert into _inputs values (?, ?, ?, ?)", rows)
versions = con.execute("""
select extension_name, extension_version from duckdb_extensions()
where loaded
""").fetchall()
print(f"run {run_id}: duckdb {duckdb.__version__}, "
f"{', '.join(f'{n} {v}' for n, v in versions)}")
print(f" {len(inputs)} inputs → {output}")Explanation
Why views beat intermediate files
Each materialised intermediate is a write, a read, a file to name, a file to clean up and a file that can go stale. Six stages means six of each.
Views collapse the chain into one plan. The optimiser can then push the final filter all the way down into the Parquet reader, so a query whose output is 251 rows may read a fraction of the input columns — measured elsewhere, as little as 0.06% of a file for a selective query.
The intermediate files were never the pipeline; they were an artefact of using tools that could not compose.
Why SQL in files is better than SQL in strings
A 60-line query inside a Python triple-quoted string is invisible to every tool: no syntax highlighting, no linting, awkward diffs, and no way to run it by hand while debugging.
The same query in a .sql file can be opened in any client, run against the database interactively, reviewed properly in a pull request, and formatted. The Python that runs it becomes a dozen lines that do not change.
Why validation belongs between stages
A pipeline that validates only its output tells you that something is wrong. A pipeline that validates between stages tells you where.
The checks are cheap because they are queries against data the engine already has open. Counting nulls in a key column of a 13-million-row view is a single scan of one column — measured at hundredths of a second on a columnar file.
Why idempotence is the property that makes it operable
Reruns happen: a stage fails, a machine restarts, somebody re-runs to check. If a rerun duplicates rows or fails on an existing file, every rerun becomes a manual cleanup.
create or replace and COPY ... TO with overwrite make the pipeline safe to run twice. That single property is what allows retries, and retries are what allow the pipeline to run unattended.
Edge cases or notes
- Views are re-evaluated on every reference. If a stage is expensive and used three times, materialise it as a table.
create or replace viewcannot change a view's column types while something depends on it; drop dependents first.COPY ... TOa partitioned directory needsoverwrite_or_ignore.- Set
memory_limitandtemp_directoryin the runner, not in each SQL file. - A DuckDB database file is a single-writer artefact — two pipeline runs must not share one.
- Keep the run log in the database so provenance travels with the output.
explaina slow stage before optimising it; the plan usually names the problem.- Pin the DuckDB version. Extension binaries are version-specific and plans can change.
Internal links
- DuckDB spatial explained: a spatial database that is just a file — the engine
- What is a GIS data pipeline — the wider pattern
- How to chain GIS processing steps — the GeoPandas version
- How to validate pipeline inputs and outputs — the validation stage
- Idempotency explained for GIS pipelines — why reruns must be safe
- How to record pipeline run metadata and lineage — the provenance table
- DuckDB's memory model: streaming, spilling and limits — the settings the runner applies
- Config versus code explained — where the paths live
FAQ
Should each pipeline stage be a table or a view?
A view, unless the stage is expensive and referenced several times. Views compose into one plan the optimiser can see whole; tables force materialisation at every step.
Where should the SQL live?
In numbered .sql files. They are reviewable, runnable by hand and diffable, and the Python runner stops changing once it works.
How do I validate between stages?
With queries returning a count of bad rows — nulls, duplicates, out-of-range coordinates, unmatched joins. Allow a tolerance where the data legitimately has one.
Is a DuckDB pipeline faster than a GeoPandas one?
At scale, and with much less memory. A 13.5-million-point join and aggregate ran in 61.8 s and 305 MB against 117.5 s and 4,739 MB.
How do I make it safe to rerun?
create or replace for views and tables, overwriting COPY ... TO for outputs, and overwrite_or_ignore for partitioned writes. Then a rerun is a no-op rather than a cleanup.
Can two pipeline runs share one database file?
No. DuckDB is single-writer. Give each run its own file, or use an in-memory database and write only the outputs.