duckdb

DuckDB Analytical SQL AI Skill Guide

Overview & Engine Architecture

DuckDB is an embedded OLAP database: columnar storage/execution, vectorized operators, and direct scans of Parquet/CSV/JSON without a server. Agents write SQL against files or attached databases, push filters into scans, and export results via COPY or DataFrame APIs instead of reinventing aggregations in Python.

Parquet/CSV/HTTP
      -> DuckDB engine (in-process)
          -> SQL (joins, window, aggregate)
          -> result / COPY / DataFrame

When to use this skill

• Ad-hoc SQL on local or lake files
• Replacing heavy pandas groupbys with SQL
• Lightweight warehouses for notebooks and CI checks

Operational directives

1. Query files with read_parquet / read_csv_auto (or glob paths) before inventing loaders.
2. Create views for repeated logic; persist only when you need indexes/reuse.
3. Prefer COPY ... TO 'out.parquet' for large exports.
4. Set memory/thread limits for shared CI runners.
5. Never embed production secrets in attached remote extensions without review.

Query Parquet example

INSTALL httpfs; LOAD httpfs;  -- only if remote paths needed

SELECT
  customer_id,
  date_trunc('month', created_at) AS month,
  sum(amount) AS revenue,
  count(*) AS orders
FROM read_parquet('data/orders/*.parquet')
WHERE created_at >= DATE '2025-01-01'
GROUP BY 1, 2
ORDER BY revenue DESC;

Python usage

import duckdb

con = duckdb.connect("analytics.duckdb")
con.execute("CREATE OR REPLACE VIEW orders AS SELECT * FROM read_parquet('data/orders/*.parquet')")
df = con.execute("""
  SELECT customer_id, sum(amount) AS revenue
  FROM orders
  GROUP BY 1
  ORDER BY 2 DESC
  LIMIT 20
""").fetchdf()

Common failures

Symptom	Cause	Fix
Type mismatch on UNION	schema drift across files	union_by_name / cast
Slow scan	reading all columns	select subset; predicate early
OOM	huge hash join	filter; spill settings; sample
Lock error	second writer on same file DB	one writer; use read_only