Complete toolkit for DuckDB 1.5.3, an in-process SQL OLAP database management system. Covers SQL queries, data import/export (CSV, JSON, Parquet), Python API (Relations, UDFs, DB-API), nested types, window functions, and extensions. Use when writing SQL queries against local files or embedded databases, performing analytical data processing in Python, ingesting data from CSV/JSON/Parquet, or building data pipelines with DuckDB.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
Complete toolkit for DuckDB 1.5.3, an in-process SQL OLAP database management system. Covers SQL queries, data import/export (CSV, JSON, Parquet), Python API (Relations, UDFs, DB-API), nested types, window functions, and extensions. Use when writing SQL queries against local files or embedded databases, performing analytical data processing in Python, ingesting data from CSV/JSON/Parquet, or building data pipelines with DuckDB.
DuckDB 1.5.3
Overview
DuckDB is an in-process SQL OLAP database management system designed for analytical queries. It runs embedded within applications — no server process required. DuckDB reads directly from files (CSV, JSON, Parquet) and in-memory data structures (Pandas DataFrames, Polars DataFrames, PyArrow tables), making it ideal for data analysis, ETL pipelines, and local data exploration.
Key capabilities:
SQL-first: PostgreSQL-compatible SQL dialect with extensions for analytical workloads
Zero-copy data ingestion: Query files directly without loading into memory
Nested data types: ARRAY, LIST, MAP, STRUCT, UNION, VARIANT
Extension ecosystem: httpfs, spatial, json, arrow, and community extensions
When to Use
Writing analytical SQL queries against local files (CSV, JSON, Parquet) without a database server
Processing Pandas/Polars/Arrow data with SQL in Python
Building ETL pipelines that read/write CSV, JSON, or Parquet files
Creating user-defined functions (UDFs) bridging Python libraries and SQL
Performing window functions, QUALIFY clauses, or CTE-based analytical queries
Working with nested/semi-structured data (JSON, structs, arrays)
Setting up persistent embedded databases with duckdb.connect("file.db")
Core Concepts
In-Process Architecture
DuckDB runs inside the application process. No network connection or server daemon is needed. Queries execute on data that lives in the same process or on local disk.
Lazy Evaluation with Relations
The Python API returns Relation objects — symbolic representations of queries. No data is fetched until an output method (.fetchall(), .df(), .show()) is called.
import duckdb
# Query is not executed yet
rel = duckdb.sql("SELECT * FROM 'large_file.parquet' WHERE value > 100")
# Data is fetched here
result = rel.fetchall()
Direct File Queries
DuckDB queries files directly without explicit import:
duckdb.sql("SELECT * FROM 'data.csv'")
duckdb.sql("SELECT * FROM 'data.parquet'")
duckdb.sql("SELECT * FROM read_json('data.json')")
Connection Modes
Mode
Usage
Persistence
duckdb.sql()
Global in-memory database
None (ephemeral)
duckdb.connect()
New in-memory connection
None (ephemeral)
duckdb.connect("file.db")
Persistent file-based database
Stored on disk
For production packages, always create explicit connections instead of using duckdb.sql() to avoid shared global state issues across threads.
Thread Safety
duckdb.sql() and the global connection are not thread-safe. Each thread must create its own connection:
# Safe — each thread gets its own connection
con = duckdb.connect()
con.sql("SELECT 1").fetchall()
# Unsafe — uses shared global connection
duckdb.sql("SELECT 1").fetchall()
import duckdb
# Query a CSV file directly
result = duckdb.sql("SELECT name, AVG(score) AS avg_score FROM 'students.csv' GROUP BY name")
result.show()
Working with Pandas DataFrames
import duckdb
import pandas as pd
df = pd.DataFrame({"name": ["Alice", "Bob"], "score": [95, 82]})
result = duckdb.sql("SELECT * FROM df WHERE score > 80").df()
Persistent Database with Schema
import duckdb
with duckdb.connect("analytics.db") as con:
con.sql("CREATE TABLE events (id INTEGER, type VARCHAR, ts TIMESTAMP)")
con.sql("INSERT INTO events VALUES (1, 'click', CURRENT_TIMESTAMP)")
con.sql("SELECT * FROM events").show()
