LabHub

Blog

DuckDB In-Memory Analytics and OLAP Guide

2026-03-15 · 28 min read · #database#duckdb#olap#analytics#in-memory#data-engineering#2026-03

한국어English日本語

DuckDB In-Memory Analytics Engine

1. When You Need DuckDB

There is a scenario you run into constantly in data analysis workflows: hitting a MemoryError while trying to read a multi-GB CSV file with pandas, or provisioning a ClickHouse cluster just to run a simple aggregation query. DuckDB fills exactly this gap.

DuckDB is an in-process OLAP database developed by Mark Raasveldt and Hannes Muehleisen at CWI (Centrum Wiskunde & Informatica) in the Netherlands. It was first introduced in the 2019 SIGMOD paper "DuckDB: an Embeddable Analytical Database", and its core idea is applying SQLite's embedded philosophy to analytical workloads.

The scenarios where DuckDB is a good fit are as follows.

Conversely, the cases where DuckDB is not a good fit are just as clear.

2. Architecture Deep Dive

2.1 Vectorized Execution Engine

The secret behind DuckDB's performance is its vectorized execution engine. Traditional row-based databases (for example PostgreSQL and MySQL) use the Volcano model and process one row at a time. That approach carries heavy function-call overhead, disrupts CPU branch prediction, and makes SIMD instructions hard to exploit.

DuckDB bundles 2048 values (the default) into a vector and processes them at once. This yields the following benefits.

2.2 Columnar Storage Layout

Internally DuckDB stores data column by column. Because analytical queries usually touch only a subset of all columns, not reading the unnecessary columns saves a great deal of I/O.

-- This query reads only the name and amount columns
-- A row-based DB has to read every column, but DuckDB touches only 2 columns
SELECT name, SUM(amount)
FROM sales
GROUP BY name;

Columnar storage also compresses well, because values of the same type sit next to each other. DuckDB automatically applies lightweight compression techniques internally (Run-Length Encoding, Dictionary Encoding, BitPacking, and so on) to reduce memory usage.

2.3 Morsel-Driven Parallelism

DuckDB splits data into small chunks called "morsels" (roughly 10,000 rows) and hands them out dynamically to several worker threads. Compared with static partitioning this approach is resilient to load skew, and performance improves in proportion to the core count.

-- Check and adjust the parallelism settings
SELECT current_setting('threads');

-- Set the thread count explicitly
SET threads TO 8;

-- Set the per-worker memory
SET memory_limit = '4GB';

2.4 Out-of-Core Processing

Out-of-core processing, introduced in DuckDB 0.8, spills intermediate results to disk while handling a dataset larger than available memory. It makes analysis beyond the memory limit possible.

-- Set the temp directory (the spill target)
SET temp_directory = '/tmp/duckdb_spill';

-- Set the memory limit (anything above this value spills to disk)
SET memory_limit = '2GB';

-- Run a large sort with out-of-core processing
SELECT *
FROM large_table
ORDER BY timestamp_col;

3. Installation and Basic Usage

3.1 Python Installation and Basic Queries

The most common environment for DuckDB is Python. It installs with a single pip line and has no external dependencies at all.

# Install
# pip install duckdb

import duckdb

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

# Run a basic query
result = con.execute("""
    SELECT
        range AS id,
        'user_' || range AS name,
        random() * 1000 AS score
    FROM range(1000000)
""").fetchdf()

print(f"Row count: {len(result)}")
print(result.head())

# Aggregation query
con.execute("""
    CREATE TABLE sales AS
    SELECT
        range AS id,
        CASE WHEN random() < 0.3 THEN 'electronics'
             WHEN random() < 0.6 THEN 'clothing'
             ELSE 'food' END AS category,
        (random() * 500)::INTEGER AS amount,
        DATE '2025-01-01' + INTERVAL (range % 365) DAY AS sale_date
    FROM range(10000000)
""")

# Monthly revenue aggregation by category
result = con.execute("""
    SELECT
        category,
        DATE_TRUNC('month', sale_date) AS month,
        COUNT(*) AS cnt,
        SUM(amount) AS total_amount,
        AVG(amount)::INTEGER AS avg_amount
    FROM sales
    GROUP BY category, DATE_TRUNC('month', sale_date)
    ORDER BY month, total_amount DESC
""").fetchdf()

print(result)

3.2 CLI Usage

DuckDB also ships a standalone CLI. It is extremely handy for data exploration and one-off analysis.

# Install (macOS)
brew install duckdb

# Start in in-memory mode
duckdb

# Start with a file-based database
duckdb my_analytics.duckdb

Basic usage examples in the CLI are as follows.

-- Create a table and insert data
CREATE TABLE metrics (
    timestamp TIMESTAMP,
    server_id VARCHAR,
    cpu_usage DOUBLE,
    memory_usage DOUBLE
);

INSERT INTO metrics
SELECT
    TIMESTAMP '2026-01-01' + INTERVAL (range * 60) SECOND,
    'server-' || (range % 10)::VARCHAR,
    50 + random() * 50,
    30 + random() * 60
FROM range(100000);

-- CPU usage statistics per server
SELECT
    server_id,
    MIN(cpu_usage)::DECIMAL(5,2) AS min_cpu,
    AVG(cpu_usage)::DECIMAL(5,2) AS avg_cpu,
    MAX(cpu_usage)::DECIMAL(5,2) AS max_cpu,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY cpu_usage)::DECIMAL(5,2) AS p95_cpu
FROM metrics
GROUP BY server_id
ORDER BY avg_cpu DESC;

-- Hourly anomaly detection (CPU 95% or higher)
SELECT
    DATE_TRUNC('hour', timestamp) AS hour,
    COUNT(*) AS anomaly_count,
    LIST(DISTINCT server_id) AS affected_servers
FROM metrics
WHERE cpu_usage > 95
GROUP BY DATE_TRUNC('hour', timestamp)
HAVING COUNT(*) > 5
ORDER BY anomaly_count DESC;

3.3 Node.js Integration

DuckDB can be used from Node.js as well. It is useful for handling analytical queries in serverless functions or API endpoints.

// npm install duckdb

const duckdb = require('duckdb')

// Create an in-memory database
const db = new duckdb.Database(':memory:')
const con = db.connect()

// Promise wrapper function
function query(sql) {
  return new Promise((resolve, reject) => {
    con.all(sql, (err, rows) => {
      if (err) reject(err)
      else resolve(rows)
    })
  })
}

async function main() {
  // Query the Parquet file directly
  const results = await query(`
    SELECT
      category,
      COUNT(*) as count,
      SUM(amount) as total
    FROM read_parquet('sales_data.parquet')
    GROUP BY category
    ORDER BY total DESC
    LIMIT 10
  `)

  console.log('Top categories:', results)

  // Load data from CSV, then analyze
  await query(`
    CREATE TABLE logs AS
    SELECT * FROM read_csv_auto('access_logs.csv')
  `)

  const hourlyStats = await query(`
    SELECT
      DATE_TRUNC('hour', timestamp) AS hour,
      COUNT(*) AS request_count,
      AVG(response_time_ms) AS avg_response_ms
    FROM logs
    GROUP BY DATE_TRUNC('hour', timestamp)
    ORDER BY hour
  `)

  console.log('Hourly stats:', hourlyStats)
}

main().catch(console.error)

4. Comparison: DuckDB vs Alternative Technologies

There are many tools for handling analytical workloads. You need an accurate understanding of each tool's characteristics in order to make the right choice.

4.1 Overall Comparison Table

ItemDuckDBSQLiteClickHousePolarsPandas
Design goalEmbedded OLAPEmbedded OLTPDistributed OLAP serverDataFrame analysisDataFrame analysis
Storage layoutColumnarRow-basedColumnarColumnarColumnar
Execution modelVectorizedRow at a timeVectorizedVectorizedMixed row/block
Query languageSQL (PostgreSQL compatible)SQL (own dialect)SQL (own dialect)Python API/SQLPython API
ConcurrencySingle writer/multiple readersSingle writer/multiple readersMultiple writers/readersNot applicableNot applicable
Server requiredNot required (in-process)Not required (in-process)Required (server process)Not required (library)Not required (library)
10GB CSV aggregationAbout 3 secondsAbout 60 seconds or moreAbout 1 secondAbout 5 secondsAbout 30 seconds (OOM risk)
Memory efficiencyHigh (out-of-core)ModerateVery highHighLow
ScalabilitySingle nodeSingle nodeHorizontal scaling (cluster)Single nodeSingle node
Installation difficultyVery easyBuilt inModerate (server setup)EasyVery easy
Parquet supportNativeNot supportedNativeNativeRequires pyarrow
Suitable data scaleMB to tens of GBKB to a few GBGB to PBMB to tens of GBMB to a few GB

4.2 Key Decision Criteria

When you should choose DuckDB:

When you should choose ClickHouse:

When you should choose Polars:

5. Querying Parquet, CSV, and JSON Directly

One of DuckDB's most powerful features is that it can query external files directly with SQL, without loading them into a table. This is called "zero-ETL" analysis.

5.1 Querying Parquet Files

Parquet is the format that pairs best with DuckDB. Because it is a columnar format it meshes naturally with DuckDB's columnar engine, and column pruning and row group filtering (predicate pushdown) are applied automatically.

import duckdb

con = duckdb.connect()

# Query a single Parquet file
result = con.execute("""
    SELECT
        customer_region,
        COUNT(*) AS order_count,
        SUM(total_amount) AS revenue,
        AVG(total_amount)::DECIMAL(10,2) AS avg_order_value
    FROM read_parquet('orders_2025.parquet')
    WHERE order_date >= '2025-06-01'
    GROUP BY customer_region
    ORDER BY revenue DESC
""").fetchdf()

# Query several Parquet files at once with a wildcard
result = con.execute("""
    SELECT
        filename,
        DATE_TRUNC('month', event_time) AS month,
        COUNT(*) AS event_count
    FROM read_parquet('events/year=2025/month=*/data_*.parquet',
                       filename=true,
                       hive_partitioning=true)
    GROUP BY filename, DATE_TRUNC('month', event_time)
""").fetchdf()

# Read only the Parquet metadata (does not read the file)
metadata = con.execute("""
    SELECT * FROM parquet_metadata('orders_2025.parquet')
""").fetchdf()

print(f"Row group count: {len(metadata)}")
print(f"Columns: {metadata['path_in_schema'].unique()}")

# Inspect the Parquet schema
schema = con.execute("""
    SELECT * FROM parquet_schema('orders_2025.parquet')
""").fetchdf()
print(schema)

5.2 Querying CSV Files

CSV files can also be queried right away through schema auto-detection.

# CSV auto-detection query
result = con.execute("""
    SELECT *
    FROM read_csv_auto('sales_data.csv')
    LIMIT 10
""").fetchdf()

# Explicit schema (more stable on large files)
result = con.execute("""
    SELECT
        product_name,
        SUM(quantity) AS total_qty,
        SUM(price * quantity) AS total_revenue
    FROM read_csv('sales_data.csv',
        columns = {
            'order_id': 'INTEGER',
            'product_name': 'VARCHAR',
            'quantity': 'INTEGER',
            'price': 'DECIMAL(10,2)',
            'order_date': 'DATE'
        },
        dateformat = '%Y-%m-%d',
        header = true,
        delim = ','
    )
    GROUP BY product_name
    ORDER BY total_revenue DESC
    LIMIT 20
""").fetchdf()

# Read several CSV files at once with a glob pattern
result = con.execute("""
    SELECT COUNT(*) AS total_rows
    FROM read_csv_auto('logs/access_log_2025_*.csv')
""").fetchone()
print(f"Total log rows: {result[0]:,}")

5.3 Querying JSON Files

# Query a JSON file
result = con.execute("""
    SELECT
        json_extract_string(data, '$.user.name') AS user_name,
        json_extract_string(data, '$.event_type') AS event_type,
        json_extract(data, '$.metadata.duration')::INTEGER AS duration_ms
    FROM read_json_auto('events.json')
    WHERE json_extract_string(data, '$.event_type') = 'page_view'
""").fetchdf()

# Query an NDJSON (Newline Delimited JSON) file
result = con.execute("""
    SELECT
        timestamp,
        level,
        message,
        service
    FROM read_json_auto('application.ndjson', format='newline_delimited')
    WHERE level = 'ERROR'
    ORDER BY timestamp DESC
    LIMIT 100
""").fetchdf()

5.4 Multi-Format Joins

You can join files in different formats inside a single query. This is where DuckDB's real strength shows.

# Join Parquet order data + CSV customer data + JSON config data
result = con.execute("""
    SELECT
        c.customer_name,
        c.tier,
        COUNT(o.order_id) AS order_count,
        SUM(o.amount) AS total_spent,
        AVG(o.amount)::DECIMAL(10,2) AS avg_order
    FROM read_parquet('orders/*.parquet') o
    JOIN read_csv_auto('customers.csv') c
        ON o.customer_id = c.id
    WHERE o.order_date >= '2025-01-01'
    GROUP BY c.customer_name, c.tier
    HAVING COUNT(o.order_id) >= 5
    ORDER BY total_spent DESC
    LIMIT 20
""").fetchdf()

6. Python Integration: Working with pandas and polars

6.1 Zero-Copy Integration with pandas DataFrames

DuckDB can query a pandas DataFrame directly as if it were a SQL table. Zero-copy data transfer through Apache Arrow means even a large DataFrame can be analyzed immediately, without being copied.

import duckdb
import pandas as pd
import numpy as np

# Create pandas DataFrames
df_orders = pd.DataFrame({
    'order_id': range(1, 1000001),
    'customer_id': np.random.randint(1, 10001, 1000000),
    'amount': np.random.uniform(10, 500, 1000000).round(2),
    'category': np.random.choice(['electronics', 'clothing', 'food', 'books'], 1000000),
    'order_date': pd.date_range('2025-01-01', periods=1000000, freq='30s')
})

df_customers = pd.DataFrame({
    'id': range(1, 10001),
    'name': [f'Customer_{i}' for i in range(1, 10001)],
    'tier': np.random.choice(['gold', 'silver', 'bronze'], 10000)
})

# Query the DataFrames directly with SQL (the variable name becomes the table name)
result = duckdb.sql("""
    SELECT
        c.tier,
        o.category,
        COUNT(*) AS order_count,
        SUM(o.amount)::DECIMAL(12,2) AS total_revenue,
        AVG(o.amount)::DECIMAL(10,2) AS avg_order_value
    FROM df_orders o
    JOIN df_customers c ON o.customer_id = c.id
    GROUP BY c.tier, o.category
    ORDER BY c.tier, total_revenue DESC
""").fetchdf()

print(result)

6.2 Working with polars DataFrames

A polars DataFrame can be queried the same way. Because polars already uses the Arrow format, there is no data conversion overhead at all.

import duckdb
import polars as pl

# Create a polars DataFrame
df_events = pl.DataFrame({
    'event_id': range(1, 5000001),
    'user_id': [i % 100000 for i in range(1, 5000001)],
    'event_type': ['click', 'view', 'purchase', 'search'] * 1250000,
    'duration_ms': [abs(int(x)) for x in (100 + 50 * i % 7 for i in range(5000000))],
    'timestamp': pl.date_range(
        pl.datetime(2025, 1, 1),
        pl.datetime(2025, 12, 31),
        eager=True
    ).sample(5000000, with_replacement=True).sort()
})

# Query the polars DataFrame with DuckDB
result = duckdb.sql("""
    SELECT
        event_type,
        DATE_TRUNC('month', timestamp) AS month,
        COUNT(*) AS event_count,
        AVG(duration_ms)::INTEGER AS avg_duration,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration_ms)::INTEGER AS p95_duration
    FROM df_events
    GROUP BY event_type, DATE_TRUNC('month', timestamp)
    ORDER BY month, event_count DESC
""").fetchdf()

# Convert the result back to polars
result_pl = pl.from_pandas(result)
print(result_pl)

6.3 DuckDB vs pandas Performance

Let us measure the performance difference between pandas and DuckDB on the same task directly.

import duckdb
import pandas as pd
import numpy as np
import time

# Generate 10 million rows of test data
n_rows = 10_000_000
df = pd.DataFrame({
    'group_a': np.random.choice(['A', 'B', 'C', 'D', 'E'], n_rows),
    'group_b': np.random.randint(1, 101, n_rows),
    'value': np.random.uniform(0, 1000, n_rows)
})

# Group-by aggregation with pandas
start = time.time()
pandas_result = df.groupby(['group_a', 'group_b']).agg(
    count=('value', 'count'),
    sum_val=('value', 'sum'),
    mean_val=('value', 'mean'),
    std_val=('value', 'std')
).reset_index().sort_values('sum_val', ascending=False).head(20)
pandas_time = time.time() - start

# The same aggregation with DuckDB
start = time.time()
duckdb_result = duckdb.sql("""
    SELECT
        group_a,
        group_b,
        COUNT(*) AS count,
        SUM(value)::DECIMAL(15,2) AS sum_val,
        AVG(value)::DECIMAL(10,2) AS mean_val,
        STDDEV(value)::DECIMAL(10,2) AS std_val
    FROM df
    GROUP BY group_a, group_b
    ORDER BY sum_val DESC
    LIMIT 20
""").fetchdf()
duckdb_time = time.time() - start

print(f"pandas: {pandas_time:.3f}s")
print(f"DuckDB: {duckdb_time:.3f}s")
print(f"Speedup: {pandas_time / duckdb_time:.1f}x")
# DuckDB is typically 3-10x faster

7. Querying Remote Data Sources

7.1 Querying S3 Parquet Files Directly

With DuckDB's httpfs extension you can query Parquet files stored in S3 as if they were local files. According to the official DuckDB documentation (duckdb.org/docs/extensions/httpfs), this extension uses HTTP Range requests to download only the byte ranges it needs.

import duckdb

con = duckdb.connect()

# Install and load the httpfs extension
con.execute("INSTALL httpfs")
con.execute("LOAD httpfs")

# Configure AWS credentials
con.execute("""
    SET s3_region = 'ap-northeast-2';
    SET s3_access_key_id = 'YOUR_ACCESS_KEY';
    SET s3_secret_access_key = 'YOUR_SECRET_KEY';
""")

# Query the S3 Parquet files directly
result = con.execute("""
    SELECT
        DATE_TRUNC('day', event_time) AS day,
        event_type,
        COUNT(*) AS event_count
    FROM read_parquet('s3://my-data-lake/events/year=2025/month=12/*.parquet',
                       hive_partitioning=true)
    GROUP BY DATE_TRUNC('day', event_time), event_type
    ORDER BY day, event_count DESC
""").fetchdf()

print(result)

7.2 Querying Directly from an HTTP URL

You can also query a public dataset directly by URL.

con = duckdb.connect()
con.execute("INSTALL httpfs")
con.execute("LOAD httpfs")

# Query a CSV directly from an HTTP URL
result = con.execute("""
    SELECT
        Country,
        SUM(Confirmed) AS total_confirmed,
        SUM(Deaths) AS total_deaths,
        (SUM(Deaths)::FLOAT / NULLIF(SUM(Confirmed), 0) * 100)::DECIMAL(5,2) AS fatality_rate
    FROM read_csv_auto(
        'https://raw.githubusercontent.com/datasets/covid-19/main/data/countries-aggregated.csv'
    )
    WHERE Date >= '2023-01-01'
    GROUP BY Country
    ORDER BY total_confirmed DESC
    LIMIT 20
""").fetchdf()

print(result)

7.3 MotherDuck Cloud Integration

MotherDuck (motherduck.com) is DuckDB's cloud service, and it lets you wire local DuckDB and the cloud together in a hybrid setup. As introduced on the MotherDuck blog, it supports the pattern of starting locally and expanding into the cloud step by step.

import duckdb

# Connect to MotherDuck (an auth token is required)
con = duckdb.connect('md:my_database')

# Join local data with cloud data
result = con.execute("""
    SELECT
        local_table.id,
        cloud_table.aggregated_value
    FROM read_parquet('local_data.parquet') AS local_table
    JOIN my_database.cloud_table
        ON local_table.id = cloud_table.id
""").fetchdf()

8. Performance Benchmarks and Optimization Techniques

8.1 TPC-H Benchmark Results

In the TPC-H benchmark published in the DuckDB GitHub repository (github.com/duckdb/duckdb), DuckDB shows the following performance at Scale Factor 10 (about 10GB).

QueryDuckDB (s)SQLite (s)pandas (s)Notes
Q1 (pricing summary)0.428.512.3Simple aggregation, full scan
Q3 (shipping priority)0.845.2OOMJoin + aggregation
Q6 (revenue change)0.115.85.2Filter + aggregation
Q9 (product profitability)2.1120+OOMComplex multi-way join
Q18 (large volume customers)1.590+OOMSubquery + aggregation

Note: test environment - Apple M2 Pro, 16GB RAM, macOS. DuckDB 1.1, SQLite 3.45, pandas 2.2

8.2 Query Optimization Techniques

-- 1. Check the execution plan with EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT customer_id, SUM(amount)
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY customer_id
HAVING SUM(amount) > 10000;

-- 2. Enable profiling
PRAGMA enable_profiling;
PRAGMA profiling_output = '/tmp/duckdb_profile.json';
PRAGMA profiling_mode = 'detailed';

-- Check the profile after running the query
SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id;

-- 3. Check statistics
CALL pragma_storage_info('orders');

-- 4. Use indexes (DuckDB supports ART indexes)
-- Caution: DuckDB is an OLAP-optimized DB, so an index is not always beneficial
CREATE INDEX idx_orders_date ON orders(order_date);

-- 5. Export to partitioned Parquet (improves query performance)
COPY (
    SELECT * FROM orders
) TO 'orders_partitioned' (
    FORMAT PARQUET,
    PARTITION_BY (order_year, order_month),
    OVERWRITE_OR_IGNORE true
);

8.3 Memory Optimization Strategy

-- Check current memory usage
SELECT * FROM duckdb_memory();

-- Set the memory limit
SET memory_limit = '8GB';

-- Set the temp directory (spills to disk when memory is exceeded)
SET temp_directory = '/tmp/duckdb_temp';

-- Enable the progress bar (for monitoring long-running queries)
SET enable_progress_bar = true;
SET enable_progress_bar_print = true;

-- Save memory by dropping unnecessary columns
-- Bad: SELECT * loads every column into memory
-- Good: name only the columns you need
SELECT order_id, amount, order_date
FROM read_parquet('large_orders.parquet');

8.4 Large-Scale Data Processing Patterns

import duckdb

con = duckdb.connect()
con.execute("SET memory_limit = '4GB'")
con.execute("SET temp_directory = '/tmp/duckdb_spill'")

# Memory-efficient analysis through chunked processing
con.execute("""
    CREATE TABLE daily_summary AS
    SELECT
        DATE_TRUNC('day', event_time) AS day,
        event_type,
        COUNT(*) AS cnt,
        SUM(value) AS total_value
    FROM read_parquet('s3://data-lake/events/year=2025/**/*.parquet',
                       hive_partitioning=true)
    GROUP BY DATE_TRUNC('day', event_time), event_type
""")

# Export the result to Parquet
con.execute("""
    COPY daily_summary
    TO 'daily_summary.parquet' (FORMAT PARQUET, COMPRESSION ZSTD)
""")

# Release memory once processing is done
con.execute("DROP TABLE daily_summary")

9. The DuckDB Extension Ecosystem

DuckDB can be extended with functionality through its extension system. According to the DuckDB extension documentation (duckdb.org/docs/extensions/overview), the main extensions are as follows.

9.1 Main Extensions

ExtensionPurposeInstall command
httpfsRemote S3/HTTP file accessINSTALL httpfs
jsonEnhanced JSON handlingBuilt in
parquetParquet read/writeBuilt in
icuInternationalization/collation/localeINSTALL icu
ftsFull-Text SearchINSTALL fts
spatialSpatial data processing (similar to PostGIS)INSTALL spatial
postgres_scannerQuery PostgreSQL tables directlyINSTALL postgres_scanner
mysql_scannerQuery MySQL tables directlyINSTALL mysql_scanner
sqlite_scannerQuery SQLite files directlyINSTALL sqlite_scanner
excelRead Excel filesINSTALL excel

9.2 Direct PostgreSQL Integration

import duckdb

con = duckdb.connect()
con.execute("INSTALL postgres_scanner")
con.execute("LOAD postgres_scanner")

# Connect directly to PostgreSQL and query
con.execute("""
    CALL postgres_attach(
        'host=localhost port=5432 dbname=mydb user=analyst password=secret'
    )
""")

# Analyze PostgreSQL tables with DuckDB SQL
result = con.execute("""
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        status,
        COUNT(*) AS order_count,
        SUM(total_amount)::DECIMAL(15,2) AS revenue
    FROM postgres_scan('public', 'orders')
    WHERE created_at >= '2025-01-01'
    GROUP BY DATE_TRUNC('month', created_at), status
    ORDER BY month
""").fetchdf()

# Join local Parquet with PostgreSQL data
result = con.execute("""
    SELECT
        p.product_name,
        o.order_count,
        o.total_revenue
    FROM read_parquet('product_catalog.parquet') p
    JOIN (
        SELECT product_id, COUNT(*) AS order_count, SUM(amount) AS total_revenue
        FROM postgres_scan('public', 'order_items')
        GROUP BY product_id
    ) o ON p.id = o.product_id
    ORDER BY o.total_revenue DESC
    LIMIT 20
""").fetchdf()

print(result)

10. Operational Caveats

10.1 Concurrency Limits

DuckDB is an embedded database that runs inside a single process. As the SIGMOD 2019 paper states explicitly, OLTP-level concurrency is not a design goal.

Key constraints:

import duckdb

# Several processes connecting in read-only mode
con = duckdb.connect('analytics.duckdb', read_only=True)

# When writes are needed, only a single process may connect
con_write = duckdb.connect('analytics.duckdb', read_only=False)

10.2 Memory Management Strategy

DuckDB processes in memory by default, so memory management matters a great deal.

import duckdb
import os

# Allocate 70% of system memory to DuckDB (the rest is for the OS and other processes)
total_memory_gb = os.sysconf('SC_PAGE_SIZE') * os.sysconf('SC_PHYS_PAGES') / (1024**3)
duckdb_memory = int(total_memory_gb * 0.7)

con = duckdb.connect()
con.execute(f"SET memory_limit = '{duckdb_memory}GB'")
con.execute("SET temp_directory = '/tmp/duckdb_spill'")

# Monitor memory usage
def check_memory():
    result = con.execute("""
        SELECT
            tag,
            memory_usage_bytes / (1024*1024) AS memory_mb
        FROM duckdb_memory()
        ORDER BY memory_usage_bytes DESC
    """).fetchdf()
    print(result)
    return result

check_memory()

10.3 Data Durability and Backup

import duckdb
import shutil
from datetime import datetime

# Use a file-based database (to obtain durability)
con = duckdb.connect('production_analytics.duckdb')

# Check WAL (Write-Ahead Log) mode
# DuckDB uses its own WAL implementation
con.execute("""
    CREATE TABLE IF NOT EXISTS etl_log (
        run_id INTEGER,
        table_name VARCHAR,
        rows_processed BIGINT,
        started_at TIMESTAMP,
        completed_at TIMESTAMP,
        status VARCHAR
    )
""")

# Backup (copy the DuckDB file directly)
def backup_database(db_path, backup_dir):
    timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
    backup_path = f"{backup_dir}/analytics_backup_{timestamp}.duckdb"

    # Copy after closing the write connection
    shutil.copy2(db_path, backup_path)
    print(f"Backup complete: {backup_path}")
    return backup_path

# Create a SQL dump with EXPORT DATABASE
con.execute("""
    EXPORT DATABASE '/tmp/analytics_export' (FORMAT PARQUET)
""")

11. Failure Cases and Response Strategies

11.1 Out-of-Memory (OOM) Problems

Symptom: during a large GROUP BY or JOIN the process gets killed, or an OutOfMemoryException is raised

import duckdb

con = duckdb.connect()

# Problem: a high-cardinality GROUP BY over 1 billion rows
# This query can use an excessive amount of memory
try:
    result = con.execute("""
        SELECT user_id, COUNT(DISTINCT session_id), AVG(duration)
        FROM read_parquet('huge_events_*.parquet')
        GROUP BY user_id
    """).fetchdf()
except duckdb.OutOfMemoryException:
    print("Out of memory!")

# Solution 1: enable out-of-core processing
con.execute("SET memory_limit = '4GB'")
con.execute("SET temp_directory = '/ssd/duckdb_temp'")  # SSD recommended

# Solution 2: staged processing (using partition pruning)
for month in range(1, 13):
    result = con.execute(f"""
        SELECT user_id, COUNT(DISTINCT session_id), AVG(duration)
        FROM read_parquet('events/month={month:02d}/*.parquet',
                           hive_partitioning=true)
        GROUP BY user_id
    """).fetchdf()
    result.to_parquet(f'partial_results/month_{month:02d}.parquet')

# Combine the partial results
final = con.execute("""
    SELECT user_id,
           SUM(count_distinct_session) AS total_sessions,
           AVG(avg_duration) AS overall_avg_duration
    FROM read_parquet('partial_results/month_*.parquet')
    GROUP BY user_id
""").fetchdf()

# Solution 3: use approximate aggregation (accuracy vs memory trade-off)
result = con.execute("""
    SELECT
        user_segment,
        APPROX_COUNT_DISTINCT(user_id) AS approx_users,
        APPROX_QUANTILE(amount, 0.5) AS median_amount,
        APPROX_QUANTILE(amount, 0.95) AS p95_amount
    FROM read_parquet('huge_events_*.parquet')
    GROUP BY user_segment
""").fetchdf()

11.2 Concurrent Write Conflicts

Symptom: when several processes try to write to the same .duckdb file at once, IOException: Could not set lock on file is raised

import duckdb
import time
import random

# Problem: multiple ETL jobs try to write to the same DB at once
# Solution: serialize writes through a single process

# Pattern 1: file lock + retry
def safe_write(db_path, query, max_retries=5):
    for attempt in range(max_retries):
        try:
            con = duckdb.connect(db_path)
            con.execute(query)
            con.close()
            return True
        except duckdb.IOException as e:
            if "lock" in str(e).lower():
                wait_time = (2 ** attempt) + random.uniform(0, 1)
                print(f"Lock conflict, retrying in {wait_time:.1f}s ({attempt + 1}/{max_retries})")
                time.sleep(wait_time)
            else:
                raise
    raise Exception("Exceeded the maximum retry count")

# Pattern 2: each process writes its own file and they are merged later
def write_to_individual_file(worker_id, data_path):
    output_path = f'worker_output/worker_{worker_id}.parquet'
    con = duckdb.connect()  # Process in memory
    con.execute(f"""
        COPY (
            SELECT * FROM read_parquet('{data_path}')
            WHERE some_condition
        ) TO '{output_path}' (FORMAT PARQUET)
    """)
    con.close()

# Merge step
def merge_results():
    con = duckdb.connect('final_analytics.duckdb')
    con.execute("""
        CREATE OR REPLACE TABLE results AS
        SELECT * FROM read_parquet('worker_output/worker_*.parquet')
    """)
    con.close()

11.3 Parquet Schema Mismatch

Symptom: reading several Parquet files together raises Binder Error: Types mismatch or a missing-column error

import duckdb

con = duckdb.connect()

# Problem: reading Parquet files with different schemas together
# The case where columns were added or types changed over time

# Solution 1: use the union_by_name option
result = con.execute("""
    SELECT *
    FROM read_parquet(
        'events/year=*/month=*/*.parquet',
        hive_partitioning = true,
        union_by_name = true  -- Merge automatically by column name
    )
    LIMIT 10
""").fetchdf()

# Solution 2: check the schema, then write a safe query
schema_info = con.execute("""
    SELECT file_name, name, type
    FROM parquet_schema('events/year=2025/month=01/*.parquet')
""").fetchdf()
print("Schema check:", schema_info)

# Solution 3: handle missing columns with COALESCE
result = con.execute("""
    SELECT
        event_id,
        event_type,
        COALESCE(new_column, 'default_value') AS new_column,
        timestamp
    FROM read_parquet(
        'events/**/*.parquet',
        union_by_name = true
    )
""").fetchdf()

12. Real-World Usage Patterns

12.1 ETL Pipeline

import duckdb
from datetime import datetime

def daily_etl_pipeline(date_str):
    """Daily ETL pipeline: S3 source -> transform -> save as Parquet"""
    con = duckdb.connect()
    con.execute("INSTALL httpfs; LOAD httpfs;")
    con.execute("""
        SET s3_region = 'ap-northeast-2';
        SET s3_access_key_id = 'YOUR_KEY';
        SET s3_secret_access_key = 'YOUR_SECRET';
    """)

    # Step 1: read the raw data + cleansing
    con.execute(f"""
        CREATE TEMP TABLE raw_events AS
        SELECT
            event_id,
            LOWER(TRIM(event_type)) AS event_type,
            user_id,
            COALESCE(amount, 0) AS amount,
            TRY_CAST(timestamp AS TIMESTAMP) AS event_time,
            properties
        FROM read_parquet(
            's3://raw-data/events/date={date_str}/*.parquet'
        )
        WHERE TRY_CAST(timestamp AS TIMESTAMP) IS NOT NULL
          AND user_id IS NOT NULL
    """)

    # Step 2: map user segments
    con.execute("""
        CREATE TEMP TABLE enriched_events AS
        SELECT
            e.*,
            u.segment,
            u.registration_date,
            DATEDIFF('day', u.registration_date, e.event_time) AS days_since_registration
        FROM raw_events e
        LEFT JOIN read_parquet('s3://dim-data/users/latest/*.parquet') u
            ON e.user_id = u.user_id
    """)

    # Step 3: build the aggregate table
    con.execute(f"""
        COPY (
            SELECT
                DATE_TRUNC('hour', event_time) AS hour,
                segment,
                event_type,
                COUNT(*) AS event_count,
                COUNT(DISTINCT user_id) AS unique_users,
                SUM(amount) AS total_amount,
                AVG(amount)::DECIMAL(10,2) AS avg_amount
            FROM enriched_events
            GROUP BY
                DATE_TRUNC('hour', event_time),
                segment,
                event_type
        ) TO 's3://processed-data/hourly_summary/date={date_str}/data.parquet'
        (FORMAT PARQUET, COMPRESSION ZSTD)
    """)

    row_count = con.execute("SELECT COUNT(*) FROM enriched_events").fetchone()[0]
    print(f"[{date_str}] ETL complete: {row_count:,} rows processed")
    con.close()
    return row_count

# Run
daily_etl_pipeline('2025-12-15')

12.2 Data Quality Validation

import duckdb

def validate_data_quality(parquet_path):
    """A collection of data quality validation queries"""
    con = duckdb.connect()

    # Basic statistics
    stats = con.execute(f"""
        SELECT
            COUNT(*) AS total_rows,
            COUNT(DISTINCT user_id) AS unique_users,
            MIN(event_time) AS earliest_event,
            MAX(event_time) AS latest_event,
            COUNT(*) FILTER (WHERE user_id IS NULL) AS null_user_count,
            COUNT(*) FILTER (WHERE amount < 0) AS negative_amount_count,
            COUNT(*) FILTER (WHERE event_type NOT IN
                ('click', 'view', 'purchase', 'search')) AS unknown_event_count
        FROM read_parquet('{parquet_path}')
    """).fetchdf()

    print("=== Basic statistics ===")
    print(stats.to_string(index=False))

    # Duplicate check
    duplicates = con.execute(f"""
        SELECT
            event_id,
            COUNT(*) AS dup_count
        FROM read_parquet('{parquet_path}')
        GROUP BY event_id
        HAVING COUNT(*) > 1
        ORDER BY dup_count DESC
        LIMIT 10
    """).fetchdf()

    if len(duplicates) > 0:
        print(f"\n[WARN] Duplicate events found: {len(duplicates)}")
        print(duplicates)
    else:
        print("\n[PASS] No duplicate events")

    # Time continuity check (detect empty hour buckets)
    gaps = con.execute(f"""
        WITH hourly AS (
            SELECT DATE_TRUNC('hour', event_time) AS hour, COUNT(*) AS cnt
            FROM read_parquet('{parquet_path}')
            GROUP BY DATE_TRUNC('hour', event_time)
        ),
        expected_hours AS (
            SELECT UNNEST(generate_series(
                (SELECT MIN(hour) FROM hourly),
                (SELECT MAX(hour) FROM hourly),
                INTERVAL 1 HOUR
            )) AS hour
        )
        SELECT e.hour AS missing_hour
        FROM expected_hours e
        LEFT JOIN hourly h ON e.hour = h.hour
        WHERE h.hour IS NULL
        ORDER BY e.hour
    """).fetchdf()

    if len(gaps) > 0:
        print(f"\n[WARN] Hours with missing data: {len(gaps)}")
        print(gaps.head(10))
    else:
        print("\n[PASS] Time continuity is intact")

    con.close()

validate_data_quality('events/year=2025/month=12/*.parquet')

12.3 Aggregate Views for BI Dashboards

import duckdb

def create_dashboard_views(db_path):
    """Create pre-aggregated views for a BI dashboard"""
    con = duckdb.connect(db_path)

    # Daily KPI view
    con.execute("""
        CREATE OR REPLACE VIEW v_daily_kpi AS
        SELECT
            DATE_TRUNC('day', order_time) AS date,
            COUNT(DISTINCT customer_id) AS dau,
            COUNT(*) AS total_orders,
            SUM(amount)::DECIMAL(15,2) AS gmv,
            AVG(amount)::DECIMAL(10,2) AS aov,
            COUNT(*) FILTER (WHERE is_first_order) AS new_customer_orders,
            COUNT(*) FILTER (WHERE is_refunded) AS refund_count,
            (COUNT(*) FILTER (WHERE is_refunded)::FLOAT /
             NULLIF(COUNT(*), 0) * 100)::DECIMAL(5,2) AS refund_rate
        FROM orders
        GROUP BY DATE_TRUNC('day', order_time)
        ORDER BY date
    """)

    # Cohort analysis view
    con.execute("""
        CREATE OR REPLACE VIEW v_cohort_retention AS
        WITH first_purchase AS (
            SELECT
                customer_id,
                DATE_TRUNC('month', MIN(order_time)) AS cohort_month
            FROM orders
            GROUP BY customer_id
        ),
        monthly_activity AS (
            SELECT
                customer_id,
                DATE_TRUNC('month', order_time) AS activity_month
            FROM orders
            GROUP BY customer_id, DATE_TRUNC('month', order_time)
        )
        SELECT
            fp.cohort_month,
            DATEDIFF('month', fp.cohort_month, ma.activity_month) AS months_since_first,
            COUNT(DISTINCT ma.customer_id) AS active_users,
            COUNT(DISTINCT ma.customer_id)::FLOAT /
                NULLIF(COUNT(DISTINCT fp.customer_id) FILTER (
                    WHERE DATEDIFF('month', fp.cohort_month, ma.activity_month) = 0
                ), 0) AS retention_rate
        FROM first_purchase fp
        JOIN monthly_activity ma ON fp.customer_id = ma.customer_id
        GROUP BY fp.cohort_month,
                 DATEDIFF('month', fp.cohort_month, ma.activity_month)
        ORDER BY fp.cohort_month, months_since_first
    """)

    # Funnel analysis view
    con.execute("""
        CREATE OR REPLACE VIEW v_conversion_funnel AS
        WITH step1 AS (
            SELECT DISTINCT user_id, DATE_TRUNC('day', event_time) AS day
            FROM events WHERE event_type = 'page_view'
        ),
        step2 AS (
            SELECT DISTINCT user_id, DATE_TRUNC('day', event_time) AS day
            FROM events WHERE event_type = 'add_to_cart'
        ),
        step3 AS (
            SELECT DISTINCT user_id, DATE_TRUNC('day', event_time) AS day
            FROM events WHERE event_type = 'checkout'
        ),
        step4 AS (
            SELECT DISTINCT user_id, DATE_TRUNC('day', event_time) AS day
            FROM events WHERE event_type = 'purchase'
        )
        SELECT
            s1.day,
            COUNT(DISTINCT s1.user_id) AS page_view_users,
            COUNT(DISTINCT s2.user_id) AS add_to_cart_users,
            COUNT(DISTINCT s3.user_id) AS checkout_users,
            COUNT(DISTINCT s4.user_id) AS purchase_users,
            (COUNT(DISTINCT s4.user_id)::FLOAT /
             NULLIF(COUNT(DISTINCT s1.user_id), 0) * 100)::DECIMAL(5,2) AS overall_conversion_rate
        FROM step1 s1
        LEFT JOIN step2 s2 ON s1.user_id = s2.user_id AND s1.day = s2.day
        LEFT JOIN step3 s3 ON s2.user_id = s3.user_id AND s2.day = s3.day
        LEFT JOIN step4 s4 ON s3.user_id = s4.user_id AND s3.day = s4.day
        GROUP BY s1.day
        ORDER BY s1.day
    """)

    print("Dashboard views created")
    con.close()

create_dashboard_views('analytics.duckdb')

13. Operations Checklist

13.1 Pre-Adoption Checklist

13.2 Monitoring Items in Production

import duckdb

con = duckdb.connect('analytics.duckdb')

# 1. Monitor memory usage
memory_info = con.execute("""
    SELECT
        tag,
        (memory_usage_bytes / 1024 / 1024)::INTEGER AS usage_mb
    FROM duckdb_memory()
    WHERE memory_usage_bytes > 0
    ORDER BY memory_usage_bytes DESC
""").fetchdf()
print("Memory usage:", memory_info.to_string(index=False))

# 2. Check table sizes
table_sizes = con.execute("""
    SELECT
        table_name,
        estimated_size / 1024 / 1024 AS estimated_size_mb,
        column_count,
        index_count
    FROM duckdb_tables()
    ORDER BY estimated_size DESC
""").fetchdf()
print("Table sizes:", table_sizes.to_string(index=False))

# 3. Check running queries (DuckDB 1.1+)
# DuckDB is single-process, so a long-running query can block other work
# Progress can be checked with enable_progress_bar

# 4. Check the database file size
import os
db_size = os.path.getsize('analytics.duckdb') / (1024 * 1024)
print(f"DB file size: {db_size:.1f} MB")

con.close()

13.3 Performance Optimization Checklist

14. Closing Thoughts

DuckDB is a practical answer to the question "does analysis really require a distributed system?" The combination of a vectorized execution engine, columnar storage, and morsel-driven parallelism delivers remarkable analytical performance even on a single machine.

Key takeaways:

  1. Installation and usage are extremely simple: one line, pip install duckdb, is all it takes
  2. Everything is queried with SQL: Parquet, CSV, JSON, and even a pandas DataFrame can all be analyzed with a single SQL statement
  3. The performance is astonishing: on TPC-H it is 50-100x faster than SQLite and 3-10x faster than pandas
  4. You have to understand the constraints: no concurrent writes, limited to a single node, unsuitable for OLTP

DuckDB is not an all-purpose tool. But for local analysis, ETL pipelines, data quality validation, and rapid prototyping, it is currently the most efficient option available. It is fair to say it is a tool that absolutely belongs in the toolbox of data engineers and analysts.

References

Comments

No comments yet.

Sign in to leave a comment