DuckDB — Fast In-Process Analytical SQL Database

What it is

DuckDB is an in-process analytical database designed for fast SQL queries over local data files. It reads CSV, Parquet, JSON, and Excel files directly without loading them into a separate database server. DuckDB embeds into your application like SQLite but is optimized for analytical queries (aggregations, joins, window functions) rather than transactional workloads.

It targets data scientists, analysts, and developers who work with local data files and want SQL query capabilities without setting up a database server. DuckDB runs in Python, R, Node.js, Java, Go, and as a standalone CLI.

How it saves time or tokens

DuckDB eliminates the load-transform-query cycle. Instead of importing a CSV into PostgreSQL to run SQL queries, query the CSV directly: SELECT * FROM 'data.csv' WHERE amount > 100. No schema definition, no import step, no server to start.

For data science workflows, DuckDB integrates with pandas DataFrames and Apache Arrow. Query DataFrames with SQL and get results back as DataFrames. This bridges the gap between SQL and Python data manipulation.

How to use

1. Install DuckDB: pip install duckdb for Python, brew install duckdb for CLI, or add the dependency to your project.
2. Query files directly with SQL: duckdb -c "SELECT * FROM 'sales.parquet' LIMIT 10". No database creation or table definition needed.
3. For persistent storage, create a database file: duckdb mydata.db. Tables and indexes persist between sessions.

Example

import duckdb

# Query a CSV file directly
result = duckdb.sql("""
    SELECT 
        category,
        count(*) as orders,
        sum(amount) as total_revenue,
        avg(amount) as avg_order
    FROM 'orders.csv'
    WHERE order_date >= '2026-01-01'
    GROUP BY category
    ORDER BY total_revenue DESC
""").fetchdf()  # Returns a pandas DataFrame

# Query a Parquet file from S3
result = duckdb.sql("""
    SELECT * FROM read_parquet('s3://my-bucket/data/*.parquet')
    WHERE region = 'US'
""")

# Query a pandas DataFrame with SQL
import pandas as pd
df = pd.read_csv('users.csv')
duckdb.sql("SELECT * FROM df WHERE age > 25").show()

Related on TokRepo

• AI tools for database — Database and analytical tools
• AI tools for research — Data analysis and research tools

Common pitfalls

• Trying to use DuckDB for concurrent transactional workloads. DuckDB supports a single writer at a time and is optimized for analytical queries. For multi-user OLTP workloads, use PostgreSQL or SQLite.
• Not leveraging Parquet format for large datasets. DuckDB queries Parquet files much faster than CSV because Parquet is columnar and compressed. Convert large CSV files to Parquet for repeated analysis.
• Assuming DuckDB needs a server. DuckDB runs entirely in-process. There is no server to start, no port to configure, and no connection string. It loads as a library in your application or runs as a CLI tool.