DuckDB: In-Process Analytical Database for Data Science

DuckDB is an in-process analytical database designed specifically for OLAP (Online Analytical Processing) workloads. Unlike traditional databases such as PostgreSQL or MySQL that require a separate server, DuckDB runs directly within your application process, similar to SQLite but optimized for analytics. In this tutorial, we will explore how to use DuckDB for efficient and powerful data science workflows.

Why DuckDB?

Before diving into the technical details, let us understand why DuckDB has become a popular choice among data scientists:

• Serverless: No database server installation, configuration, or management needed
• Columnar storage: Data is stored per column, optimal for analytical queries
• Vectorized execution: Processes data in batches for maximum performance
• Query files directly: Can query CSV, Parquet, and JSON without importing first
• Python integration: Seamless Python API with Pandas and Polars
• Standard SQL: Supports feature-rich SQL including window functions and CTEs

Installation

Installing DuckDB is straightforward. Simply use pip:

pip install duckdb

To verify the installation:

import duckdb

print(duckdb.version)

If you want to use the DuckDB CLI, you can download the binary from the official website or use:

pip install duckdb[cli]

Getting Started with DuckDB

In-Memory Database

The simplest way to get started is with an in-memory database:

import duckdb

Create an in-memory connection
con = duckdb.connect()

Or explicitly
con = duckdb.connect(database=':memory:')

Run a simple query
result = con.sql("SELECT 42 AS answer, 'Hello DuckDB' AS message")
print(result.fetchall())
[(42, 'Hello DuckDB')]

Persistent Database

To store data permanently, provide a filename:

import duckdb

Create or open a persistent database
con = duckdb.connect('analytics.duckdb')

Create a table
con.sql("""
    CREATE TABLE IF NOT EXISTS sales (
        id INTEGER PRIMARY KEY,
        product VARCHAR,
        category VARCHAR,
        quantity INTEGER,
        price DECIMAL(10, 2),
        saledate DATE

    )
""")

Insert data
con.sql("""
    INSERT INTO sales VALUES
    (1, 'Laptop Pro', 'Electronics', 5, 1299.99, '2026-01-15'),
    (2, 'Wireless Mouse', 'Accessories', 50, 24.99, '2026-01-16'),
    (3, 'Mechanical Keyboard', 'Accessories', 30, 79.99, '2026-01-16'),
    (4, '27-inch Monitor', 'Electronics', 10, 449.99, '2026-01-17'),
    (5, 'Gaming Headset', 'Accessories', 25, 59.99, '2026-01-18')
""")

Query data
result = con.sql("SELECT  FROM sales WHERE category = 'Electronics'")

result.show()

import duckdb

Query CSV directly
result = duckdb.sql("""
    SELECT 
    FROM readcsvauto('data/transactions.csv')

    WHERE total > 1000
    ORDER BY transactiondate DESC
    LIMIT 10
""")
result.show()

With specific options
result = duckdb.sql("""
    SELECT 

    FROM readcsv('data/transactions.csv',

        delim=',',
        header=true,
        dateformat='%Y-%m-%d'
    )
""")

import duckdb

Query a Parquet file
result = duckdb.sql("""
    SELECT
        category,
        COUNT() AS transactioncount,
        SUM(total) AS totalrevenue,

        AVG(total) AS averageamount
    FROM readparquet('data/transactions2026.parquet')
    GROUP BY category
    ORDER BY totalrevenue DESC

""")
result.show()

Query multiple Parquet files with glob patterns
result = duckdb.sql("""
    SELECT 

    FROM readparquet('data/transactions.parquet')
""")

Query Parquet from URL (S3, HTTP)
result = duckdb.sql("""
    SELECT 

    FROM readparquet('https://example.com/data/sample.parquet')
    LIMIT 100
""")

import duckdb

Query a JSON file
result = duckdb.sql("""
    SELECT 
    FROM readjsonauto('data/activitylog.json')

    WHERE eventtype = 'purchase'
""")
result.show()

JSON Lines format
result = duckdb.sql("""
    SELECT 

    FROM readjsonauto('data/events.jsonl', format='newlinedelimited')

""")

import duckdb
import pandas as pd

Create a Pandas DataFrame
dfsales = pd.DataFrame({
    'product': ['Laptop', 'Mouse', 'Keyboard', 'Monitor', 'Headset'],
    'category': ['Electronics', 'Accessories', 'Accessories', 'Electronics', 'Accessories'],
    'price': [1299.99, 24.99, 79.99, 449.99, 59.99],
    'stock': [10, 100, 50, 20, 75]
})

Query the DataFrame directly with SQL
result = duckdb.sql("""
    SELECT
        category,
        COUNT() AS productcount,

        AVG(price) AS avgprice,
        SUM(stock) AS totalstock

    FROM dfsales
    GROUP BY category
""")

Convert result to DataFrame
dfresult = result.df()

print(dfresult)

Combine DataFrames and files
dfcustomers = pd.DataFrame({

    'id': [1, 2, 3],
    'name': ['Alice', 'Bob', 'Charlie'],
    'city': ['New York', 'Los Angeles', 'Chicago']
})

result = duckdb.sql("""
    SELECT
        c.name,
        c.city,
        t.

    FROM dfcustomers c
    JOIN readcsvauto('data/transactions.csv') t
        ON c.id = t.customerid

    ORDER BY t.total DESC
""")

import duckdb
import polars as pl

Create a Polars DataFrame
df = pl.DataFrame({
    'name': ['Product A', 'Product B', 'Product C', 'Product D'],
    'salesq1': [1500, 2300, 800, 3100],
    'salesq2': [1800, 2100, 1200, 2900],

    'salesq3': [2000, 2500, 1500, 3300],
    'salesq4': [2200, 2800, 1800, 3500]

})

Query Polars DataFrame with DuckDB
result = duckdb.sql("""
    SELECT
        name,
        salesq1 + salesq2 + salesq3 + salesq4 AS annualtotal,
        (salesq4 - salesq1)::FLOAT / salesq1  100 AS growthpercent
    FROM df
    ORDER BY annualtotal DESC

""")

Convert to Polars DataFrame
plresult = result.pl()
print(plresult)

import duckdb

con = duckdb.connect()

Create sample data
con.sql("""
    CREATE TABLE transactions AS
    SELECT  FROM (VALUES

        ('2026-01-01', 'Electronics', 5000),
        ('2026-01-02', 'Clothing', 1500),
        ('2026-01-03', 'Electronics', 3000),
        ('2026-01-04', 'Food', 500),
        ('2026-01-05', 'Clothing', 2000),
        ('2026-01-06', 'Electronics', 7000),
        ('2026-01-07', 'Food', 800),
        ('2026-01-08', 'Clothing', 1800),
        ('2026-01-09', 'Electronics', 4500),
        ('2026-01-10', 'Food', 600)
    ) AS t(transactiondate, category, total)
""")

Running total per category
result = con.sql("""
    SELECT
        transactiondate,

        category,
        total,
        SUM(total) OVER (
            PARTITION BY category
            ORDER BY transactiondate
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS runningtotal,

        ROWNUMBER() OVER (
            PARTITION BY category
            ORDER BY total DESC
        ) AS ranking,
        LAG(total) OVER (
            PARTITION BY category
            ORDER BY transactiondate

        ) AS previoustotal,
        total - COALESCE(LAG(total) OVER (
            PARTITION BY category
            ORDER BY transactiondate

        ), 0) AS difference
    FROM transactions
    ORDER BY category, transactiondate
""")
result.show()

Moving average
result = con.sql("""
    SELECT
        transactiondate,

        total,
        AVG(total) OVER (
            ORDER BY transactiondate
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS movingavg3day,
        NTILE(4) OVER (ORDER BY total) AS quartile
    FROM transactions
""")
result.show()

# ROLLUP for hierarchical subtotals
result = con.sql("""
    SELECT
        category,
        EXTRACT(MONTH FROM transactiondate::DATE) AS month,

        SUM(total) AS totalsales,
        COUNT() AS transactioncount

    FROM transactions
    GROUP BY ROLLUP (category, EXTRACT(MONTH FROM transactiondate::DATE))
    ORDER BY category NULLS LAST, month NULLS LAST
""")
result.show()

DuckDB Extensions

DuckDB has an extension system that expands its functionality:

import duckdb

con = duckdb.connect()

View available extensions
con.sql("SELECT  FROM duckdbextensions()").show()


Install and load an extension
con.sql("INSTALL httpfs")
con.sql("LOAD httpfs")

Now you can query files from HTTP/S3
result = con.sql("""
    SELECT 
    FROM readparquet('https://example.com/data/sample.parquet')
    LIMIT 10
""")

Spatial format extension
con.sql("INSTALL spatial")
con.sql("LOAD spatial")

JSON extension
con.sql("INSTALL json")
con.sql("LOAD json")

Full-text search extension
con.sql("INSTALL fts")
con.sql("LOAD fts")

Performance Comparison: DuckDB vs SQLite vs Pandas

Here is a comparison of the characteristics of these three popular tools:

import duckdb
import pandas as pd
import sqlite3
import time

Create a large dataset for benchmarking
nrows = 1000000


duckdb.sql(f"""
    CREATE TABLE benchmark AS
    SELECT
        i AS id,
        'Product' || (i % 1000) AS product,
        'Category' || (i % 50) AS category,

        (random()  1000000)::INTEGER AS total,

        '2026-01-01'::DATE + (i % 365) AS transactiondate
    FROM generateseries(1, {nrows}) AS t(i)
""")

Benchmark DuckDB
start = time.time()
resultduck = duckdb.sql("""

    SELECT
        category,
        COUNT() AS cnt,
        SUM(total) AS sumtotal,
        AVG(total) AS avgtotal

    FROM benchmark
    GROUP BY category
    ORDER BY sumtotal DESC
""").df()
timeduck = time.time() - start

print(f"DuckDB: {timeduck:.4f} seconds")

Benchmark Pandas (equivalent)
df = duckdb.sql("SELECT  FROM benchmark").df()

start = time.time()
resultpandas = (

    df.groupby('category')
    .agg(cnt=('id', 'count'), sumtotal=('total', 'sum'), avgtotal=('total', 'mean'))

    .sortvalues('sumtotal', ascending=False)

    .resetindex()
)
timepandas = time.time() - start

print(f"Pandas: {timepandas:.4f} seconds")

import duckdb

con = duckdb.connect()

Simulation: create a large dataset and save as Parquet
con.sql("""
    COPY (
        SELECT
            i AS transactionid,

            'CUST-' || LPAD((i % 10000)::VARCHAR, 5, '0') AS customerid,
            CASE (i % 5)
                WHEN 0 THEN 'Electronics'
                WHEN 1 THEN 'Clothing'
                WHEN 2 THEN 'Food'
                WHEN 3 THEN 'Sports'
                WHEN 4 THEN 'Books'
            END AS category,
            CASE (i % 3)
                WHEN 0 THEN 'New York'
                WHEN 1 THEN 'Los Angeles'
                WHEN 2 THEN 'Chicago'
            END AS city,
            (random()  5000 + 50)::INTEGER AS total,
            '2025-01-01'::DATE + (i % 365) AS transactiondate

        FROM generateseries(1, 5000000) AS t(i)
    ) TO 'largetransactions.parquet' (FORMAT PARQUET)

""")

print("5 million row dataset created successfully!")

Analysis 1: Monthly sales trend by category
result = con.sql("""
    SELECT
        DATETRUNC('month', transactiondate) AS month,

        category,
        COUNT() AS transactioncount,
        SUM(total) AS totalsales,

        AVG(total)::INTEGER AS avgtransaction
    FROM readparquet('largetransactions.parquet')
    GROUP BY DATETRUNC('month', transactiondate), category
    ORDER BY month, totalsales DESC

""")
print("\n=== Monthly Sales Trend ===")
result.show()

Analysis 2: Top customers per city
result = con.sql("""
    WITH customerspending AS (
        SELECT
            customerid,

            city,
            SUM(total) AS totalspent,
            COUNT() AS frequency,
            AVG(total)::INTEGER AS avgspent

        FROM readparquet('largetransactions.parquet')

        GROUP BY customerid, city
    ),
    ranked AS (
        SELECT
            ,

            ROWNUMBER() OVER (

                PARTITION BY city
                ORDER BY totalspent DESC
            ) AS ranking
        FROM customerspending

    )
    SELECT city, customerid, totalspent, frequency, avgspent
    FROM ranked
    WHERE ranking <= 5
    ORDER BY city, ranking
""")
print("\n=== Top 5 Customers per City ===")
result.show()

Analysis 3: Sales distribution with percentiles
result = con.sql("""
    SELECT
        category,
        MIN(total) AS mintotal,

        PERCENTILECONT(0.25) WITHIN GROUP (ORDER BY total)::INTEGER AS p25,
        PERCENTILECONT(0.50) WITHIN GROUP (ORDER BY total)::INTEGER AS median,

        PERCENTILECONT(0.75) WITHIN GROUP (ORDER BY total)::INTEGER AS p75,
        MAX(total) AS maxtotal,

        STDDEV(total)::INTEGER AS stddev
    FROM readparquet('largetransactions.parquet')
    GROUP BY category
    ORDER BY median DESC
""")
print("\n=== Sales Distribution by Category ===")
result.show()

Analysis 4: Cohort analysis - customer retention
result = con.sql("""
    WITH firstpurchase AS (

        SELECT
            customerid,
            MIN(transactiondate) AS firstdate
        FROM readparquet('largetransactions.parquet')
        GROUP BY customerid

    ),
    cohortdata AS (
        SELECT
            DATETRUNC('month', fp.firstdate) AS cohortmonth,

            DATEDIFF('month', fp.firstdate, t.transactiondate) AS monthnumber,
            COUNT(DISTINCT t.customerid) AS customercount
        FROM readparquet('largetransactions.parquet') t
        JOIN firstpurchase fp ON t.customerid = fp.customerid

        GROUP BY cohortmonth, monthnumber

    )
    SELECT
        cohortmonth,
        monthnumber,

        customercount
    FROM cohortdata

    WHERE monthnumber <= 6
    ORDER BY cohortmonth, monthnumber
    LIMIT 30
""")
print("\n=== Cohort Analysis ===")
result.show()

Export analysis results
con.sql("""
    COPY (
        SELECT
            city,
            category,
            DATETRUNC('month', transactiondate) AS month,
            SUM(total) AS totalsales,

            COUNT() AS transactioncount
        FROM readparquet('largetransactions.parquet')
        GROUP BY city, category, DATETRUNC('month', transactiondate)
    ) TO 'salessummary.csv' (HEADER, DELIMITER ',')

""")
print("\nAnalysis results exported to CSV successfully!")

# Convert CSV to Parquet for better performance
duckdb.sql("""
    COPY (SELECT  FROM readcsvauto('largedata.csv'))
    TO 'largedata.parquet' (FORMAT PARQUET, COMPRESSION ZSTD)

""")

# Use relations for lazy evaluation
rel = duckdb.sql("SELECT  FROM readparquet('data.parquet')")
filtered = rel.filter("total > 1000")
grouped = filtered.aggregate("category, SUM(total) AS total")
Query is only executed when .show() or .df() is called
grouped.show()

3. Configure Memory and Threads

import duckdb

con = duckdb.connect()
con.sql("SET memorylimit = '4GB'")

con.sql("SET threads = 4")
con.sql("SET tempdirectory = '/tmp/duckdbtemp'")

con.sql("""
    EXPLAIN ANALYZE
    SELECT category, SUM(total)
    FROM readparquet('data.parquet')
    GROUP BY category
""").show()

Conclusion

DuckDB is an incredibly powerful tool for data scientists. Its ability to query files directly, seamless integration with the Python ecosystem, and outstanding performance for analytical workloads make it an ideal choice for:

• Initial data exploration (EDA) with SQL
• Analyzing large datasets without database infrastructure
• Prototyping data pipelines
• Replacing Pandas for datasets that do not fit in memory
• Simple ETL without complex setup

With DuckDB, you get the power of analytical SQL equivalent to a data warehouse, right on your laptop without needing any server setup.