Loading GeoJSON and Shapefiles into DuckDB with dbt

This page shows you how to read GeoJSON and Shapefile data into a dbt staging model with the DuckDB spatial extension’s GDAL-backed ST_Read function, normalize its coordinate reference system, and validate every geometry before it flows downstream.

When to use this approach

ST_Read turns any GDAL-supported vector file into a queryable table inside DuckDB, in-process, with no external service. That makes it the natural ingestion path when:

  • You develop or run CI on DuckDB. Reading files directly keeps local and CI runs self-contained; the extension setup itself is covered in DuckDB spatial extension integration and its dbt wiring in configuring the DuckDB spatial extension in dbt projects.
  • Your source of truth is a file, not a table. GeoJSON exports, Shapefile bundles from a GIS team, or open-data downloads land as files. ST_Read ingests them without a separate load step or an intermediate PostGIS instance.
  • You want CRS and validity handled at the staging boundary. DuckDB’s GEOMETRY type carries no SRID metadata, so reprojecting to a canonical frame and validating topology must happen explicitly on ingestion — exactly the staging discipline described in spatial reference system management.

If your production warehouse is PostGIS or a cloud engine, you can still ingest and validate on DuckDB in CI, then promote the same models — the loader below is deliberately warehouse-neutral downstream of staging.

The ingestion flow

Each file passes through the same four stages: GDAL reads it, you reproject to a canonical CRS, you repair or quarantine invalid geometry, and the result becomes a typed staging relation the rest of the DAG can trust.

Loading GeoJSON and Shapefiles into a dbt staging model via DuckDB ST_Read A left-to-right ingestion pipeline. Source files, a GeoJSON file and a Shapefile bundle of shp, dbf, shx and prj, flow into the DuckDB ST_Read GDAL reader, which emits a geom column plus attributes. The output is reprojected with ST_Transform to a canonical CRS because DuckDB geometry carries no SRID, then validated with ST_IsValid and repaired or quarantined with ST_MakeValid, and finally materialized as a typed dbt staging model that the rest of the DAG consumes. Source files parcels.geojsonroads.shp · .dbf.shx · .prj any GDAL driver ST_Read GDAL readergeom + attrs in-process Reproject ST_Transformto canonical CRS no SRID metadata Validate ST_IsValidST_MakeValid repair / quarantine stg model typed, cleangeometry Files become a trusted, canonical-CRS staging relation without leaving DuckDB Because DuckDB GEOMETRY has no SRID, the canonical frame is enforced by ST_Transform, not stored on the column

Prerequisites

  • dbt-duckdb 1.7+ with the spatial extension enabled, configured as in configuring the DuckDB spatial extension in dbt projects.
  • The spatial extension installed and loaded — it bundles GDAL and PROJ, which provide the file drivers and reprojection.
  • A canonical target CRS decided for the project (this guide uses EPSG:4326 for storage); the policy belongs in spatial reference system management.
  • File paths surfaced through env_var() so the same models run locally and in CI without editing paths into the SQL.
yaml
# profiles.yml — DuckDB target with the spatial extension
dbt_geospatial:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: "{{ env_var('DBT_DUCKDB_PATH', 'dev.duckdb') }}"
      extensions: [spatial]

Step-by-step instructions

1. Confirm the driver and read the source CRS

Before ingesting, check that GDAL exposes a driver for the format and read the file’s declared CRS — a Shapefile carries it in the sidecar .prj, GeoJSON in a crs member (or defaults to EPSG:4326). ST_Read_Meta reports it without loading the data.

sql
-- confirm a driver exists and inspect layer metadata (run in the DuckDB CLI)
LOAD spatial;
SELECT driver_short_name, layers[1].geometry_fields[1].crs.auth_name
     , layers[1].geometry_fields[1].crs.auth_code
FROM ST_Read_Meta('data/roads.shp');

Verify the query returns a non-null auth_name/auth_code (for example EPSG / 26910). If the CRS is null, the source is missing its .prj or crs member and you must supply the SRID by hand in the reproject step rather than trust auto-detection.

2. Ingest the file into a staging model

Point a staging model at ST_Read. GDAL detects the driver from the extension, returns a geom column plus every attribute field, and the model becomes an ordinary dbt relation. Parameterize the path so it is not hard-coded.

sql
-- models/staging/stg_roads_raw.sql
{{ config(materialized='view') }}

select
    fid,
    name        as road_name,
    class       as road_class,
    geom        as geometry_raw   -- native CRS as read from the file
from ST_Read('{{ env_var("DBT_ROADS_PATH", "data/roads.shp") }}')

Verify the read succeeded and geometry arrived, not just attributes:

bash
dbt build --select stg_roads_raw
# then, in the DuckDB CLI:
#   SELECT count(*) filter (where geometry_raw is not null) FROM stg_roads_raw;
# expect a non-zero count equal to the feature count of the source file

3. Reproject to the canonical CRS

DuckDB’s GEOMETRY type stores no SRID, so you cannot “stamp” a frame onto the column the way PostGIS does — you enforce the canonical frame by reprojecting. Pass the source and target CRS explicitly to ST_Transform, using the CRS you confirmed in step 1. Set always_xy so longitude/latitude axis order is unambiguous.

sql
-- models/staging/stg_roads.sql
{{ config(materialized='table') }}

with reprojected as (
    select
        fid,
        road_name,
        road_class,
        ST_Transform(
            geometry_raw,
            '{{ var("source_crs", "EPSG:26910") }}',  -- from ST_Read_Meta
            '{{ var("canonical_crs", "EPSG:4326") }}', -- project canonical frame
            always_xy := true
        ) as geometry
    from {{ ref('stg_roads_raw') }}
)

select * from reprojected

Verify the coordinates now fall in the canonical frame’s expected range — for EPSG:4326, longitudes within ±180 and latitudes within ±90:

sql
-- expect zero rows out of range
SELECT count(*) AS out_of_range
FROM {{ ref('stg_roads') }}
WHERE ST_X(ST_Centroid(geometry)) NOT BETWEEN -180 AND 180
   OR ST_Y(ST_Centroid(geometry)) NOT BETWEEN -90 AND 90;

4. Validate and repair geometry

File sources routinely contain self-intersections, unclosed rings, or collapsed polygons that will break every downstream join. Repair what is fixable with ST_MakeValid and route the rest to a quarantine so a bad feature fails visibly rather than silently.

sql
-- add to stg_roads.sql: repair invalid geometry inline
select
    fid,
    road_name,
    road_class,
    case
        when ST_IsValid(geometry) then geometry
        else ST_MakeValid(geometry)
    end as geometry
from reprojected
where geometry is not null

Verify with a dbt generic test so an invalid geometry fails the build, not the map:

yaml
# models/staging/_staging.yml
version: 2
models:
  - name: stg_roads
    columns:
      - name: geometry
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "ST_IsValid(geometry)"

5. Expose it as a documented dbt source or model

Declare the file-backed input so downstream models reference it by name and its provenance is documented alongside the rest of the DAG. With dbt-duckdb you can attach the reader to a source’s external_location, keeping ST_Read out of every consuming model.

yaml
# models/staging/_sources.yml
version: 2
sources:
  - name: gis_files
    meta:
      external_location: "ST_Read('{{ env_var('DBT_ROADS_PATH', 'data/roads.shp') }}')"
    tables:
      - name: roads
        description: "Road centerlines ingested from a Shapefile via DuckDB ST_Read"

Verify the source resolves and downstream selects compile:

bash
dbt compile --select source:gis_files.roads+
# confirms the ST_Read external_location is wired and consuming models build

Configuration reference

Option Where Purpose Notes
extensions: [spatial] profiles.yml Load GDAL/PROJ into DuckDB Required before any ST_Read call
ST_Read(path) staging model Read a vector file to rows Driver auto-detected; returns geom + attributes
layer := ST_Read arg Pick a layer in a multi-layer file GeoPackage/GeoJSON collections may hold several
ST_Read_Meta(path) ad-hoc Inspect layers and CRS Read the .prj/crs before trusting auto-detect
ST_Transform(g, from, to, always_xy) staging model Reproject to canonical CRS DuckDB GEOMETRY has no SRID; frame is enforced here
var('canonical_crs') dbt_project.yml Project storage CRS Keep it in one place, not per model
external_location source meta Wire a file as a dbt source Keeps ST_Read out of consuming models

Gotchas & edge cases

  • Shapefiles are a bundle, not one file. A .shp needs its .dbf, .shx, and — for CRS — its .prj sibling in the same directory. Ship them together, or ST_Read fails or loses the CRS.
  • No SRID on the column. DuckDB GEOMETRY does not store an SRID, so there is nothing to assert with an ST_SRID test the way there is in PostGIS. Enforce the canonical frame by reprojecting in staging and documenting the CRS in the model’s YAML.
  • Missing .prj means guessed CRS. When the source CRS is absent, GDAL cannot report it and auto-transform will be wrong. Read ST_Read_Meta first and supply the source CRS explicitly to ST_Transform.
  • Axis-order surprises. Some CRS definitions declare latitude before longitude. Pass always_xy := true so ST_Transform treats coordinates as longitude/latitude and points do not land in the wrong hemisphere.
  • Attribute name collisions. GDAL truncates Shapefile field names to ten characters, so two source columns can collapse to the same name. Alias explicitly in the staging select rather than select *.
  • Large files as views re-read on every query. A view over ST_Read re-parses the file each time it is queried. Materialize the cleaned staging model as a table so the GDAL read happens once.

FAQ

Does DuckDB store an SRID on the geometry like PostGIS?

No. DuckDB’s GEOMETRY type carries no SRID metadata, so you cannot stamp a frame onto the column or assert it with an ST_SRID test. Instead, enforce a canonical coordinate frame by reprojecting with ST_Transform in the staging layer and documenting the target CRS in the model’s YAML. Every downstream model then relies on the convention that staging output is already in the canonical CRS.

How does ST_Read know which driver to use for a file?

ST_Read uses GDAL, which detects the format from the file’s extension and contents, so a .geojson uses the GeoJSON driver and a .shp uses the Shapefile driver automatically. You can inspect available drivers with ST_Drivers() and constrain the choice with the allowed_drivers option if a file’s extension is ambiguous.

Why did my reprojected coordinates end up in the wrong place?

Two common causes: the source CRS was wrong or missing because the Shapefile lacked its .prj, or the axis order was flipped. Read ST_Read_Meta to confirm the true source CRS, pass it explicitly to ST_Transform rather than trusting auto-detection, and set always_xy := true so coordinates are treated as longitude then latitude.

Should I load the file in a view or a table?

Materialize the cleaned staging model as a table. A view over ST_Read re-parses the source file on every query, which is slow for large files and re-does the GDAL read and reprojection each time. A table runs the read, reprojection, and validation once, and downstream models query the cheap materialized result.

How do I keep the file path out of my SQL?

Surface the path through dbt’s env_var() so the same model runs locally and in CI without edits, and optionally attach ST_Read to a source’s external_location in _sources.yml. Consuming models then reference the source by name, the path lives in one place, and the ingestion detail is documented alongside the rest of the DAG.

Up: Part of DuckDB Spatial Extension Integration.