Duckdb

DuckDB v1.5 Skill

> DuckDB v1.5.0 "Variegata" — Released 2026-03-09.

How to Think: Discovery → Understanding → Analysis

The most common mistake is jumping straight to queries. Instead, follow this workflow — it prevents wrong assumptions, bad joins, and wasted work.

Phase 1: Discover the Data

Before writing any analytical query, find out what you're working with.

Local files — use glob patterns to find them:

-- Find what files exist (Parquet, CSV, JSON, GeoJSON, etc.)
FROM glob('data/*.parquet');
FROM glob('**/*.csv');
FROM glob('/path/to/project/**/*.geojson');

Remote files — always load httpfs first, then probe:

INSTALL httpfs; LOAD httpfs;
-- For S3, also set the region:
SET s3_region = 'us-west-2';

-- Remote Parquet (only reads metadata, not full file)
DESCRIBE FROM 'https://example.com/data.parquet';

-- S3 / Azure / GCS
DESCRIBE FROM 's3://bucket/path/file.parquet';

-- S3 buckets with dots in the name (e.g., source.coop) need path-style URLs,
-- because virtual-hosted style breaks SSL certificate validation:
SET s3_url_style = 'path';
DESCRIBE FROM 's3://us-west-2.opendata.source.coop/repo/data.parquet';

-- Hive-partitioned datasets — check partition structure
FROM parquet_metadata('s3://bucket/dataset/**/*.parquet') LIMIT 5;

Databases — inspect what's available:

-- Attached databases
SHOW ALL TABLES;
DESCRIBE table_name;

-- External DuckDB files (new in v1.5 — no ATTACH needed)
FROM read_duckdb('other.duckdb', table_name := 'my_table');

-- External databases via ODBC (new in v1.5, requires INSTALL odbc_scanner)
-- odbc_connect(conn_str) + odbc_query(conn, sql)

Discover functions from DuckDB itself — when unsure about a function signature:

-- Search for functions by name pattern
SELECT function_name, parameters, return_type
FROM duckdb_functions() WHERE function_name LIKE 'ST_Coverage%';

-- List all extensions and their status
FROM duckdb_extensions();

-- List all settings
FROM duckdb_settings() WHERE name LIKE '%geometry%';

Geospatial files — check layers and metadata:

LOAD spatial;
SELECT * FROM ST_Read_Meta('data.gpkg');       -- layers, CRS, field schemas
SELECT * FROM ST_Read_Meta('data.gdb');        -- ESRI File Geodatabase

Phase 2: Understand the Shape

Once you know WHAT files exist, understand their structure before querying.

-- Schema: column names and types (instant, reads only metadata)
DESCRIBE FROM 'data.parquet';
DESCRIBE FROM 'data.csv';

-- Stats: min, max, approx_unique, avg, std, quartiles, nulls (scans full data)
SUMMARIZE FROM 'data.parquet';
SUMMARIZE FROM 'data.csv';

-- Quick sample: see actual values (avoids scanning everything)
FROM 'data.parquet' LIMIT 10;
FROM 'data.csv' LIMIT 10;

-- Row count estimate (fast for Parquet — reads metadata only)
SELECT count(*) FROM 'data.parquet';

-- For Parquet: row group structure, column statistics, file-level metadata
FROM parquet_metadata('data.parquet');
FROM parquet_schema('data.parquet');
FROM parquet_kv_metadata('data.parquet');  -- GeoParquet metadata lives here

This phase is critical because:

• DESCRIBE tells you column types — you'll know if geometry is WKB blob vs native GEOMETRY
• SUMMARIZE reveals data quality issues (null percentages, unexpected ranges, cardinality)
• A quick LIMIT sample shows actual values — field naming conventions, coordinate order, encoding
• For spatial data: you need to know the CRS before any spatial operations

Detecting the CRS

Knowing the CRS is essential before any spatial operation. The method depends on how geometry is stored:

Step 1: Identify the format. Run DESCRIBE first — the column type tells you which case you're in:

| Column type | Format | CRS location | |---|---|---| | geometry('epsg:4326') | GeoParquet or native Parquet geometry | CRS in column type (spatial resolves EPSG) | | geometry (no CRS) | Native Parquet geometry with no CRS, or CRS not resolved | Check parquet_schema() logical_type | | blob | Plain Parquet with WKB | No CRS embedded — must know from context |

Case 1: GeoParquet (Parquet with geo KV metadata)

-- Quickest: DESCRIBE shows CRS in the column type (requires LOAD spatial for EPSG resolution)
LOAD spatial;
DESCRIBE FROM 'data.parquet';
-- → geom  geometry('epsg:4326')

-- Or use ST_CRS on a row
SELECT ST_CRS(geom) FROM 'data.parquet' LIMIT 1;
-- → 'EPSG:4326'

-- Without spatial loaded, DESCRIBE still works but shows raw PROJJSON instead of the short code:
-- → geom  geometry('{"$schema":"https://proj.org/schemas/v0.5/projjson.schema.json",...}')

-- Full GeoParquet metadata inspection (no spatial extension needed):
WITH kv AS (
    SELECT file_name,
           decode(key) AS key_str,
           decode(value)::JSON AS geo_json
    FROM parquet_kv_metadata('data/*.parquet')
)
SELECT file_name,
       geo_json->>'$.columns.geometry.encoding' AS encoding,
       geo_json->'$.columns.geometry.geometry_types' AS geom_types,
       geo_json->'$.columns.geometry.bbox' AS bbox,
       geo_json->'$.columns.geometry.crs.id.authority' AS crs_authority,
       geo_json->'$.columns.geometry.crs.id.code' AS crs_code,
       geo_json->>'$.primary_column' AS primary_column
FROM kv WHERE key_str = 'geo';
-- If crs is NULL → default is OGC:CRS84 (WGS84 lon/lat) per GeoParquet spec

Case 2: Native Parquet geometry (Format 2.11+ — no geo key)

These files store CRS in the Parquet schema's logical type, not in KV metadata. DuckDB reads it automatically.

-- DESCRIBE shows the resolved CRS when spatial can resolve it
LOAD spatial;
DESCRIBE FROM 'native_geo.parquet';
-- → geometry  geometry('epsg:5070')    ← CRS resolved
-- → geometry  geometry                 ← CRS not resolved or absent

-- Check the raw Parquet logical type to see how CRS is encoded
SELECT name, logical_type
FROM parquet_schema('native_geo.parquet')
WHERE logical_type LIKE 'GeometryType%';
-- → geometry  GeometryType(crs=srid:5070)     ← SRID-based CRS
-- → geometry  GeometryType(crs=)     ← full PROJJSON CRS
-- → geometry  GeometryType(crs=)         ← no CRS specified

Case 3: Plain Parquet (WKB blob, no geometry metadata)

-- DESCRIBE shows blob type — no CRS information available
DESCRIBE FROM 'data.parquet';
-- → wkb_geom  blob

-- No CRS is embedded. You must know the CRS from documentation or context,
-- then assign it after converting to GEOMETRY:
LOAD spatial;
SELECT ST_SetCRS(ST_GeomFromWKB(wkb_geom), 'EPSG:4326') FROM 'data.parquet';

Case 4: GDAL-supported formats (GPKG, Shapefile, GDB, FlatGeobuf, GeoJSON, KML)

-- Use ST_Read_Meta to get CRS from any GDAL-readable format
LOAD spatial;
SELECT
    layers[1].geometry_fields[1].crs.auth_name AS authority,
    layers[1].geometry_fields[1].crs.auth_code AS code
FROM ST_Read_Meta('data.gpkg');
-- → EPSG  4326

-- For multi-layer files, inspect all layers:
SELECT * FROM ST_Read_Meta('data.gdb');

Case 5: H3/spatial-indexed data (no geometry column)

Some datasets store location as H3 BIGINT indices instead of geometry columns. H3 cells are always WGS84 — the CRS is implicit. Derive coordinates on the fly:

INSTALL h3 FROM community; LOAD h3;
SELECT h3_index,
       h3_h3_to_string(h3_index) AS h3_hex,  -- BIGINT → hex string (e.g., '820007fffffffff')
       h3_cell_to_lat(h3_index) AS lat,
       h3_cell_to_lng(h3_index) AS lon
FROM 'data.parquet' LIMIT 5;

Phase 3: Analyze with Purpose

Now that you understand the data, write targeted queries. Some principles:

Start narrow, expand. Query a small subset first (WHERE + LIMIT), verify the logic, then remove the constraints.

Use FROM-first syntax — it's more readable and DuckDB-idiomatic:

-- Instead of SELECT * FROM tbl WHERE ...
FROM tbl SELECT col1, col2 WHERE x > 10;
FROM tbl;  -- implicit SELECT *

Use GROUP BY ALL — let DuckDB infer grouping columns from context:

SELECT region, category, sum(amount), count()
FROM sales
GROUP BY ALL;  -- infers GROUP BY region, category

Use SUMMARIZE on intermediate results to verify transformations:

SUMMARIZE (
    SELECT *, ST_Area_Spheroid(geom) AS area_m2
    FROM parcels
    WHERE area_m2 > 0
);
-- Check: are the area values reasonable? Any nulls introduced?

Chain analysis steps — use CTEs or CREATE OR REPLACE TABLE for iterative exploration:

CREATE OR REPLACE TABLE enriched AS
    SELECT p.*, z.zone_name
    FROM points p
    JOIN zones z ON ST_Intersects(p.geom, z.geom);

SUMMARIZE enriched;  -- verify the join didn't explode or lose rows

When Queries Fail or Perform Poorly: Use EXPLAIN ANALYZE

If a query is complex, slow, or failing unexpectedly, use EXPLAIN ANALYZE to understand what's happening. It shows the query plan with runtime metrics — actual row counts, estimated cardinalities, and timing for each operation.

EXPLAIN ANALYZE SELECT ... ;

When to use it:

• Query runs slower than expected
• Getting unexpected results or row counts
• Complex joins or subqueries that you want to verify
• Need to see if indexes or spatial joins are being used
• Want to break down a complex query into analyzable parts

The output shows a tree of operations with:

• EC (Estimated Cardinality): what DuckDB predicted
• Actual cardinality: what actually happened
• Timing: cumulative wall-clock time per operator

If estimated vs actual cardinalities are far off, or if certain operations dominate the time, that tells you where to focus optimization. For deep-dive details and examples, read refs/explain-analyze.md.

v1.5 Breaking Changes — Must Know

These trip up anyone using pre-v1.5 patterns:

| Old pattern | v1.5+ correct way | |---|---| | x -> x + 1 (arrow lambda) | lambda x: x + 1 — arrow syntax is deprecated, errors in v2.0 | | Missing geometry_always_xy | SET geometry_always_xy = true; after LOAD spatial — v1.5 warns, v2.1 makes it default | | INSTALL spatial for GEOMETRY columns | Not needed — GEOMETRY is a core type in v1.5 (only INSTALL spatial for ST* functions) | | GEOMETRY('EPSG:4326') without spatial | EPSG codes require spatial extension. Without it, use GEOMETRY('OGC:CRS84') or plain GEOMETRY | | Mixing CRS in spatial operations | v1.5 errors at bind time. Use consistent CRS or strip with ::GEOMETRY | | .fetch_arrow_table() with parquet geometry | Use .arrow().read_all() — crashes with TransactionContext error (see refs/python-api.md) | | TRY_CAST(x AS GEOMETRY) | TRY(ST_GeomFromText(x)) — TRYCAST broken for GEOMETRY in v1.5 | | ST_Read('f.fgb', spatial_filter_box=...) | Removed in v1.5 — use WHERE geom && ST_MakeBox2D(...) instead (auto-pushed down) | | ST_Read(..., sequential_layer_scan=true) | Removed in v1.5 — auto-applied for OSM format | | register_geoarrow_extensions() in Python | No longer needed — GEOMETRY defaults to GeoArrow in Arrow export | | Invalid HEXWKB silently accepted | v1.5 now throws proper error on invalid HEXWKB input |

Core GEOMETRY Type (no extension needed)

-- GEOMETRY is now a built-in type with optional CRS parameter
CREATE TABLE t1 (g GEOMETRY);                    -- no CRS
CREATE TABLE t2 (g GEOMETRY('OGC:CRS84'));       -- built-in CRS (no spatial needed)
CREATE TABLE t3 (g GEOMETRY('EPSG:4326'));       -- requires LOAD spatial for EPSG codes

-- Built-in functions (no LOAD spatial needed):
ST_GeomFromWKB(blob)          -- WKB → geometry
ST_AsWKB(geom)                -- geometry → WKB (alias: ST_AsBinary)
ST_AsWKT(geom)                -- geometry → WKT (alias: ST_AsText)
ST_Intersects_Extent(a, b)    -- bbox intersection
a && b                        -- operator alias (uses row-group stats!)
ST_CRS(geom)                  -- get CRS identifier
ST_SetCRS(geom, 'OGC:CRS84') -- assign CRS (no coordinate transform)
'POINT(0 0)'::GEOMETRY        -- cast from WKT

Subtypes: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, GeometryCollection. Vertices can have Z, M, or ZM dimensions.

CRS System

SELECT * FROM duckdb_coordinate_systems();  -- list all known CRSs (works without spatial)
-- Built-in: OGC:CRS84, OGC:CRS83. Spatial ext adds 7,000+ EPSG codes.
-- Accepts: AUTH:CODE, full PROJJSON, full WKT2-2019
SET ignore_unknown_crs = true;  -- silently drop unknown CRS (works without spatial)

Geometry Shredding (~3x compression)

Automatic: when all geometries in a row group share the same subtype, DuckDB decomposes to STRUCT/LIST/DOUBLE with ALP compression. Control with SET geometry_minimum_shredding_size = 30000 (default), 0 (always), -1 (disable). Not shredded: GeometryCollection, empty geometries, mixed subtypes.

Spatial Recipes (requires LOAD spatial)

Session Setup

Always start spatial work with:

INSTALL spatial; LOAD spatial;
SET geometry_always_xy = true;  -- lon/lat = x/y consistently

Spatial Joins (automatic R-tree, v1.3+)

-- Point-in-polygon (automatic R-tree, no index creation needed)
SELECT p.*, z.zone_name
FROM points p JOIN zones z ON ST_Intersects(p.geom, z.geom);

-- Proximity join — ST_DWithin is fastest (triggers SPATIAL_JOIN operator)
SELECT a.*, b.*
FROM table_a a JOIN table_b b ON ST_DWithin(a.geom, b.geom, 1000);

Predicates that trigger SPATIAL_JOIN: ST_Intersects, ST_Contains, ST_ContainsProperly, ST_Within, ST_Covers, ST_CoveredBy, ST_Overlaps, ST_Touches, ST_Crosses, ST_DWithin, && / ST_Intersects_Extent.

Spatial join gotchas:

• R-Tree indexes created with CREATE INDEX ... USING RTREE are for WHERE filter scans only — they are NOT used for JOIN ... ON ST_Intersects(). The spatial join optimizer builds its own internal R-tree. They are independent features.
• For very small build sides, block-nested-loop may outperform the spatial join optimizer. DuckDB's vectorized constant-vector optimization is very fast for small tables.
• Spatial operations do NOT spill to disk — OOM can occur with large spatial pipelines. Monitor memory usage.

CRS Transform

-- v1.5: 2-arg form (source CRS inferred from typed column)
SELECT ST_Transform(geom, 'EPSG:4326') FROM tbl;

-- Explicit source/target
SELECT ST_Transform(geom, 'EPSG:4326', 'EPSG:3857') FROM tbl;

Distance & Area

SELECT ST_Distance_Spheroid(ST_Point(-74.0, 40.7), ST_Point(-0.1, 51.5));  -- meters
SELECT ST_Distance_Sphere(a.geom, b.geom);   -- haversine, meters
SELECT ST_Area_Spheroid(geom);                -- m²
SELECT ST_Length_Spheroid(geom);              -- meters
-- ST_Point(x, y) = ST_Point(lon, lat) — x is always longitude

Export

-- GeoParquet (default: v1 metadata)
COPY tbl TO 'output.parquet' (FORMAT PARQUET);

-- Max compatibility: GeoParquet v1 metadata + native Parquet geometry stats
COPY tbl TO 'output.parquet' (FORMAT PARQUET, GEOPARQUET_VERSION 'BOTH');

-- GDAL formats (read refs/gdal-formats.md for full reference)
COPY tbl TO 'out.geojson' WITH (FORMAT GDAL, DRIVER 'GeoJSON');
COPY tbl TO 'out.gpkg' WITH (FORMAT GDAL, DRIVER 'GPKG', LAYER_NAME 'my_layer');
COPY tbl TO 'out.shp' WITH (FORMAT GDAL, DRIVER 'ESRI Shapefile');

-- Hilbert-ordered for best spatial query performance
COPY (SELECT * FROM tbl ORDER BY ST_Hilbert(geom)) TO 'sorted.parquet' (FORMAT PARQUET);

R-Tree Index

-- Populate table FIRST, then create index (bulk load is 10x+ faster)
CREATE INDEX idx ON tbl USING RTREE (geom);
-- Auto-used with WHERE spatial predicates (NOT with JOIN — see spatial join notes above)

Note: R-Tree index scans can be skipped if the table also has a PRIMARY KEY or other index type. If spatial filtering seems slow on a table with a PK, verify with EXPLAIN ANALYZE.

Friendly SQL Quick Reference

-- FROM-first
FROM tbl;                                      -- implicit SELECT *
FROM tbl SELECT col1, col2 WHERE x > 10;       -- FROM leads, then SELECT, then WHERE

-- GROUP

…

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [yharby](https://github.com/yharby)
- **Source:** [yharby/duckdb-skill](https://github.com/yharby/duckdb-skill)
- **License:** MIT

Install and usage instructions live in the source repository linked above.