LabHub

Blog

DuckDB OLAP Analytics and Data Lake Guide

2026-03-08 · 22 min read · #database#duckdb#olap#analytics#data-lake#parquet

한국어English日本語

DuckDB Analytics

1. Introduction

The data analysis landscape is changing fast. In the past, running a single analytical query meant spinning up a Spark cluster, loading the data into BigQuery, or provisioning a Redshift instance. But in an era where a single laptop has more than 32GB of RAM and SSD speeds reach 3GB/s, people started asking: "do we really need a distributed system just to run analytics?"

DuckDB is the most persuasive answer to that question. Started by Mark Raasveldt and Hannes Muehleisen at CWI (Centrum Wiskunde and Informatica) in the Netherlands, the project applies SQLite's embedded philosophy to the OLAP world as an in-process analytical database. With no separate server process, a single pip install duckdb lets you aggregate Parquet files of hundreds of millions of rows in sub-second time.

The scenarios where you should choose DuckDB are as follows.

The cases where DuckDB is not a good fit are equally clear. For OLTP workloads that need concurrent writes, for service backends with hundreds of simultaneous connections, and for petabyte-scale distributed processing, tools such as PostgreSQL, ClickHouse, and Spark are the better choice.

2. Understanding the DuckDB Architecture

DuckDB's remarkable performance comes from the synergy of three core design principles: columnar storage, a vectorized execution engine, and morsel-driven parallelism (Morsel-Driven Parallelism).

In-Process Architecture

Like SQLite, DuckDB runs directly inside the application process with no separate server daemon. There is no network communication overhead at all, and no runtime dependencies. It loads as a library in a range of languages including Python, R, Java, and Node.js.

Columnar Storage

A traditional row-oriented database stores all the column values belonging to a single row in a contiguous block. When an analytical query needs only some of the columns, the rest of the data has to be read too, which wastes a lot of I/O.

DuckDB stores data column by column, so it scans only the columns the query needs. Because values of the same type sit next to one another, the compression ratio improves dramatically as well.

Row-oriented (SQLite/PostgreSQL):
┌──────┬─────────┬─────────┬──────────┐
│ id   │ name    │ country │ revenue  │  -- every column of row 1
├──────┼─────────┼─────────┼──────────┤
│ id   │ name    │ country │ revenue  │  -- every column of row 2
└──────┴─────────┴─────────┴──────────┘
  ↑ even for SELECT country, SUM(revenue)
    the id and name column data is read unnecessarily

Columnar (DuckDB):
┌────────────────────────┐
│ id:      1, 2, 3, ...  │  -- column block 1 (skipped)
├────────────────────────┤
│ name:    A, B, C, ...  │  -- column block 2 (skipped)
├────────────────────────┤
│ country: KR, US, ...   │  -- column block 3 (scanned!)
├────────────────────────┤
│ revenue: 100, 200, ... │  -- column block 4 (scanned!)
└────────────────────────┘
  ↑ only the needed columns are read from disk
    CPU cache efficiency is maximized

3. The Vectorized Execution Engine

The core mechanism behind DuckDB's performance is its vectorized execution engine (Vectorized Execution Engine). Understanding how it differs from the traditional Volcano (iterator) model makes it clear why DuckDB is fast.

Volcano Model vs Vectorized Model

In the traditional Volcano model, each operator in the query tree passes one row at a time up to the operator above it. Every row processed brings a virtual function call and a branch misprediction, so the function call overhead ends up costing more than the actual computation.

The vectorized model processes 1,024 to 2,048 values (a vector) as a bundle. The function call overhead is amortized across the vector, and the CPU's SIMD instructions and cache lines can be used to the full.

┌─────────────────────────────────────────────────────┐
│             DuckDB vectorized execution engine       │
├─────────────────────────────────────────────────────┤
│                                                     │
│  ┌─────────────┐                                    │
│  │  SQL Parser  │  SQL text → AST                   │
│  └──────┬──────┘                                    │
│         ▼                                           │
│  ┌─────────────┐                                    │
│  │  Binder     │  resolve table/column names         │
│  └──────┬──────┘                                    │
│         ▼                                           │
│  ┌─────────────────┐                                │
│  │ Query Optimizer │  join order, filter pushdown     │
│  │  (Cost-based)   │  subquery flattening            │
│  └──────┬──────────┘                                │
│         ▼                                           │
│  ┌─────────────────────────────────────┐            │
│  │    Physical Plan (Pipeline-based)   │            │
│  │  ┌──────┐  ┌──────┐  ┌──────────┐  │            │
│  │  │Scan  │→ │Filter│→ │HashAggr  │  │            │
│  │  │(Vec) │  │(Vec) │  │  (Vec)   │  │            │
│  │  └──────┘  └──────┘  └──────────┘  │            │
│  │     ↑ data passed in vectors (1024 rows) │        │
│  └─────────────────────────────────────┘            │
│         ▼                                           │
│  ┌─────────────────────────────────────┐            │
│  │  Morsel-Driven Parallelism          │            │
│  │  ┌────────┐ ┌────────┐ ┌────────┐  │            │
│  │  │Thread 1│ │Thread 2│ │Thread N│  │            │
│  │  │Morsel A│ │Morsel B│ │Morsel C│  │            │
│  │  └────────┘ └────────┘ └────────┘  │            │
│  │  each thread handles its own data chunk │         │
│  └─────────────────────────────────────┘            │
│         ▼                                           │
│  ┌─────────────┐                                    │
│  │   Result    │  vector → DataFrame / Arrow Table  │
│  └─────────────┘                                    │
└─────────────────────────────────────────────────────┘

Morsel-Driven Parallelism

DuckDB splits table data into chunks called "morsels" (roughly 100,000 rows each). Each worker thread takes one morsel independently, processes it, and picks up the next one when it finishes. This work-stealing approach balances the load across threads naturally and is far more flexible than conventional partition-based parallelism.

4. Installation and Environment Setup

One of DuckDB's biggest advantages is how simple installation is. It has no external dependencies at all, and binaries are provided per platform.

CLI Installation

# macOS (Homebrew)
brew install duckdb

# Linux (apt)
sudo apt-get install duckdb
# Or download the binary directly
wget https://github.com/duckdb/duckdb/releases/latest/download/duckdb_cli-linux-amd64.zip
unzip duckdb_cli-linux-amd64.zip
chmod +x duckdb
sudo mv duckdb /usr/local/bin/

# Check the version
duckdb --version

Installing the Python Package

# pip
pip install duckdb

# conda
conda install -c conda-forge python-duckdb

# Install a specific version (for example, 1.2.x)
pip install duckdb==1.2.1

Confirming Basic Usage

-- Launch the DuckDB CLI
-- In-memory mode (data is discarded when the session ends)
duckdb

-- File-based persistent mode
duckdb my_analytics.db

-- Basic query test
SELECT version();
-- v1.2.1

-- Check system information
SELECT * FROM duckdb_settings()
WHERE name IN ('threads', 'memory_limit', 'temp_directory');

5. Analytical SQL Queries in Practice

DuckDB supports PostgreSQL-compatible SQL and offers a range of SQL extensions geared towards analytics.

Querying Files Directly

One of DuckDB's most powerful features is that you can query a file directly without a CREATE TABLE.

-- Query a CSV file directly
SELECT
    product_category,
    COUNT(*) AS order_count,
    ROUND(AVG(amount), 2) AS avg_amount,
    SUM(amount) AS total_revenue
FROM read_csv('sales_2025.csv', auto_detect=true)
WHERE order_date >= '2025-01-01'
GROUP BY product_category
ORDER BY total_revenue DESC
LIMIT 10;

-- Query several CSV files at once with a glob pattern
SELECT
    filename,
    COUNT(*) AS row_count
FROM read_csv('logs/access_*.csv', auto_detect=true, filename=true)
GROUP BY filename
ORDER BY row_count DESC;

-- Query a JSON file
SELECT
    json_extract_string(data, '$.user.name') AS user_name,
    json_extract(data, '$.events') AS events
FROM read_json('user_activity.json', auto_detect=true);

-- Query a Parquet file (the fastest format)
SELECT
    region,
    YEAR(event_timestamp) AS event_year,
    COUNT(*) AS event_count
FROM read_parquet('events/*.parquet')
GROUP BY region, event_year
ORDER BY event_count DESC;

Using Window Functions

-- Moving average and ranking over sales data
WITH daily_sales AS (
    SELECT
        sale_date,
        product_id,
        SUM(amount) AS daily_amount
    FROM read_parquet('sales_data.parquet')
    GROUP BY sale_date, product_id
)
SELECT
    sale_date,
    product_id,
    daily_amount,
    -- 7-day moving average
    ROUND(AVG(daily_amount) OVER (
        PARTITION BY product_id
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_7d,
    -- Sales rank per product (per day)
    RANK() OVER (
        PARTITION BY sale_date
        ORDER BY daily_amount DESC
    ) AS daily_rank,
    -- Change versus the previous day
    ROUND(
        (daily_amount - LAG(daily_amount) OVER (
            PARTITION BY product_id ORDER BY sale_date
        )) * 100.0 / NULLIF(LAG(daily_amount) OVER (
            PARTITION BY product_id ORDER BY sale_date
        ), 0), 2
    ) AS pct_change
FROM daily_sales
ORDER BY sale_date DESC, daily_rank
LIMIT 20;

PIVOT and UNPIVOT

-- Monthly sales pivot table by category
PIVOT (
    SELECT
        MONTHNAME(order_date) AS month,
        category,
        SUM(revenue) AS total
    FROM read_parquet('orders.parquet')
    WHERE YEAR(order_date) = 2025
    GROUP BY month, category
)
ON category
USING SUM(total)
GROUP BY month
ORDER BY month;

6. Python Integration

DuckDB's Python integration goes beyond a simple DB driver: it offers the distinctive ability to query Pandas and Polars DataFrames directly with SQL.

Querying a Pandas DataFrame Directly

import duckdb
import pandas as pd

# Create a Pandas DataFrame
sales_df = pd.DataFrame({
    'product': ['A', 'B', 'C', 'A', 'B', 'C'] * 1000,
    'region': ['Seoul', 'Busan', 'Seoul', 'Busan', 'Seoul', 'Busan'] * 1000,
    'amount': [100, 200, 150, 300, 250, 180] * 1000,
    'date': pd.date_range('2025-01-01', periods=6000, freq='h')
})

# Query the DataFrame directly with SQL (the variable name is the table name)
result = duckdb.sql("""
    SELECT
        product,
        region,
        COUNT(*) AS order_count,
        ROUND(AVG(amount), 2) AS avg_amount,
        SUM(amount) AS total_revenue,
        MIN(date) AS first_order,
        MAX(date) AS last_order
    FROM sales_df
    GROUP BY product, region
    ORDER BY total_revenue DESC
""").df()  # return the result as a Pandas DataFrame again

print(result)

# Join a large Parquet file with a DataFrame
products_df = pd.DataFrame({
    'product': ['A', 'B', 'C'],
    'category': ['Electronics', 'Clothing', 'Food'],
    'margin_pct': [0.15, 0.40, 0.25]
})

enriched = duckdb.sql("""
    SELECT
        s.product,
        p.category,
        SUM(s.amount) AS revenue,
        ROUND(SUM(s.amount) * p.margin_pct, 2) AS estimated_profit
    FROM sales_df s
    JOIN products_df p ON s.product = p.product
    GROUP BY s.product, p.category, p.margin_pct
    ORDER BY estimated_profit DESC
""").df()

print(enriched)

Persistent Databases and the Relation API

import duckdb

# Connect to a persistent database
con = duckdb.connect('analytics.db')

# Create the table and load data from Parquet
con.execute("""
    CREATE TABLE IF NOT EXISTS events AS
    SELECT * FROM read_parquet('raw_events/*.parquet');
""")

# Relation API (method chaining)
result = (
    con.table('events')
    .filter("event_type = 'purchase'")
    .aggregate("product_id, COUNT(*) AS cnt, SUM(amount) AS total")
    .order("total DESC")
    .limit(10)
    .df()
)

# Export the result to Parquet
con.execute("""
    COPY (
        SELECT product_id, COUNT(*) AS cnt, SUM(amount) AS total
        FROM events
        WHERE event_type = 'purchase'
        GROUP BY product_id
        ORDER BY total DESC
    ) TO 'top_products.parquet' (FORMAT PARQUET, COMPRESSION ZSTD);
""")

con.close()

Apache Arrow Integration

import duckdb
import pyarrow as pa
import pyarrow.parquet as pq

# Query an Arrow Table directly
arrow_table = pq.read_table('large_dataset.parquet')

result = duckdb.sql("""
    SELECT
        category,
        COUNT(*) AS cnt,
        APPROX_QUANTILE(value, 0.95) AS p95
    FROM arrow_table
    GROUP BY category
""").arrow()  # return the result as an Arrow Table (zero copy)

# Arrow's zero-copy handoff maximizes memory efficiency
print(f"Result rows: {result.num_rows}")

7. Data Lake Integration (Parquet, S3, Delta Lake)

One of the key reasons DuckDB has grown explosively in the data engineering world is that it can query a data lake directly, without complex infrastructure.

Querying S3 Parquet Files Directly

-- Install and load the httpfs extension
INSTALL httpfs;
LOAD httpfs;

-- AWS S3 authentication setup (the Secrets approach - recommended)
CREATE SECRET my_s3_secret (
    TYPE S3,
    KEY_ID 'AKIAIOSFODNN7EXAMPLE',
    SECRET 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY',
    REGION 'ap-northeast-2'
);

-- Query Parquet files on S3 directly
SELECT
    event_date,
    user_segment,
    COUNT(DISTINCT user_id) AS dau,
    COUNT(*) AS total_events,
    ROUND(AVG(session_duration_sec), 1) AS avg_session_sec
FROM read_parquet('s3://my-data-lake/events/year=2025/month=12/*.parquet')
GROUP BY event_date, user_segment
ORDER BY event_date DESC, dau DESC;

-- Query hive-partitioned data (partition pruning applied automatically)
SELECT *
FROM read_parquet(
    's3://my-data-lake/events/**/*.parquet',
    hive_partitioning=true
)
WHERE year = 2025 AND month = 12
  AND event_type = 'purchase';

-- Save the query result back to S3 as Parquet
COPY (
    SELECT
        date_trunc('hour', event_timestamp) AS hour,
        COUNT(*) AS event_count,
        COUNT(DISTINCT user_id) AS unique_users
    FROM read_parquet('s3://my-data-lake/raw_events/**/*.parquet',
                      hive_partitioning=true)
    WHERE year = 2025
    GROUP BY hour
)
TO 's3://my-data-lake/aggregated/hourly_summary.parquet'
(FORMAT PARQUET, COMPRESSION ZSTD);

Reading Delta Lake

-- Install the Delta Lake extension
INSTALL delta;
LOAD delta;

-- Read a Delta Lake table
SELECT *
FROM delta_scan('s3://my-data-lake/delta_tables/user_events/')
WHERE event_date >= '2025-12-01'
LIMIT 100;

GCS (Google Cloud Storage) Integration

-- GCS authentication setup
CREATE SECRET gcs_secret (
    TYPE GCS,
    KEY_ID 'my-gcs-key-id',
    SECRET 'my-gcs-secret'
);

-- Query GCS Parquet files
SELECT COUNT(*), SUM(revenue)
FROM read_parquet('gs://my-bucket/analytics/sales_*.parquet');

8. Comparing Analytics Tools

To understand DuckDB's position accurately, comparing it with competing tools is essential.

Comparison itemDuckDBSQLiteClickHouseBigQuery
ArchitectureIn-process, columnarIn-process, row-orientedClient-server, columnarServerless, columnar
Best-fit workloadLocal/embedded OLAPLocal/embedded OLTPDistributed real-time OLAPLarge-scale cloud analytics
Installation difficultyA single pip installBundled with the OSServer installation requiredNo installation (SaaS)
Concurrent usersSingle to a fewSingle to a fewHundreds to thousandsThousands+
Maximum data scaleTens to hundreds of GB (single node)A few GBTB to PB (cluster)PB+
Write concurrencySingle WriterSingle Writer (Readers concurrent in WAL mode)Multiple WritersMultiple Writers
Native Parquet supportBuilt inNot supportedPossible via MATERIALIZED VIEWNative
Direct S3 queryinghttpfs extensionNot supporteds3 table functionNative (External Table)
Cost modelFree (open source, MIT)Free (public domain)Free (open source) / paid in the cloudCharged per query ($5/TB scanned)
Python integrationSQL directly over DataFramesDB-API basicsclickhouse-drivergoogle-cloud-bigquery
Real-time ingestionUnsuitableUnsuitableOptimized (MergeTree)Streaming Insert
TransactionsACID (single Writer)ACIDLimited ACIDFull ACID

Recommendations by Use Case

9. Making Use of Extensions

DuckDB follows a design that keeps the core light and lets you add what you need selectively through extensions.

The Main Extensions

-- Check the installed extensions
SELECT * FROM duckdb_extensions() WHERE installed = true;

-- Install the core extensions
INSTALL httpfs;    -- S3/HTTP remote file access
INSTALL json;      -- JSON parsing (auto-loaded)
INSTALL parquet;   -- Parquet support (auto-loaded)
INSTALL spatial;   -- geospatial data processing (ST_* functions)
INSTALL icu;       -- internationalized collation/comparison
INSTALL fts;       -- Full-Text Search
INSTALL delta;     -- reading Delta Lake tables
INSTALL excel;     -- reading/writing Excel files
INSTALL sqlite;    -- reading SQLite databases directly
INSTALL postgres;  -- querying PostgreSQL directly (FDW)
INSTALL mysql;     -- querying MySQL directly

-- Load the extensions
LOAD httpfs;
LOAD spatial;

A Practical Spatial Extension Example

INSTALL spatial;
LOAD spatial;

-- Analyze orders within a 1km radius of Seoul-area stores
SELECT
    store_name,
    COUNT(*) AS nearby_orders,
    SUM(order_amount) AS nearby_revenue
FROM orders o
JOIN stores s ON o.store_id = s.id
WHERE ST_DWithin(
    ST_Point(o.longitude, o.latitude),
    ST_Point(s.longitude, s.latitude),
    0.01  -- about 1km (WGS84 approximation)
)
GROUP BY store_name
ORDER BY nearby_revenue DESC;

Querying PostgreSQL Directly (the postgres extension)

INSTALL postgres;
LOAD postgres;

-- Connect directly to PostgreSQL and query
ATTACH 'dbname=mydb user=analyst host=pg-server.internal' AS pg (TYPE POSTGRES);

-- Join a PostgreSQL table with a local Parquet file
SELECT
    p.customer_id,
    p.customer_name,
    l.total_purchases,
    l.last_purchase_date
FROM pg.public.customers p
JOIN (
    SELECT
        customer_id,
        COUNT(*) AS total_purchases,
        MAX(purchase_date) AS last_purchase_date
    FROM read_parquet('s3://data-lake/purchases/**/*.parquet')
    GROUP BY customer_id
) l ON p.customer_id = l.customer_id
WHERE l.total_purchases > 100
ORDER BY l.total_purchases DESC;

10. MotherDuck: DuckDB in the Cloud

MotherDuck is a serverless cloud analytics service run by a company founded by one of DuckDB's co-creators. It keeps DuckDB's strengths while adding the scalability and collaboration features of the cloud.

Key Characteristics

Connecting to MotherDuck

import duckdb

# Connect with a MotherDuck token
con = duckdb.connect('md:my_database?motherduck_token=eyJhb...')

# Hybrid query over local and cloud data
con.execute("""
    SELECT
        cloud_table.user_id,
        local_file.purchase_amount
    FROM my_database.users AS cloud_table
    JOIN read_parquet('local_data/purchases.parquet') AS local_file
        ON cloud_table.user_id = local_file.user_id
    WHERE cloud_table.signup_date >= '2025-01-01'
""")

result = con.fetchdf()
print(result)

11. Performance Optimization Tips

DuckDB performs well even with default settings, but tuning it to the characteristics of your workload can gain you additional performance.

Memory and Thread Settings

-- Check the current settings
SELECT name, value, description
FROM duckdb_settings()
WHERE name IN ('memory_limit', 'threads', 'temp_directory');

-- Set the memory limit (default: about 80% of system RAM)
SET memory_limit = '16GB';

-- Adjust the thread count (default: number of CPU cores)
-- Lower it when running alongside other work
SET threads = 4;

-- Set the disk spill directory (used when memory is exceeded)
SET temp_directory = '/fast-ssd/duckdb_temp';

Query Optimization Principles

-- 1. Check the query plan with EXPLAIN
EXPLAIN
SELECT region, SUM(amount) AS total
FROM read_parquet('large_sales.parquet')
WHERE sale_date >= '2025-01-01'
GROUP BY region;

-- 2. Profile the actual execution with EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT region, SUM(amount) AS total
FROM read_parquet('large_sales.parquet')
WHERE sale_date >= '2025-01-01'
GROUP BY region;

-- 3. Take advantage of Parquet filter pushdown
--    the WHERE condition is matched against the Row Group statistics
--    of the Parquet file, so unnecessary data is never read
SELECT COUNT(*)
FROM read_parquet('partitioned_data/**/*.parquet',
                  hive_partitioning=true)
WHERE year = 2025 AND month = 12;
-- → files where year != 2025 are never even opened (partition pruning)

-- 4. Use appropriate data types
-- use ENUM instead of VARCHAR to optimize low-cardinality columns
CREATE TYPE status_enum AS ENUM ('active', 'inactive', 'pending');

-- 5. Reuse connections
-- frequent connect/disconnect causes cache loss
-- keep a single connection open and run queries on it

Optimizing Parquet Files

-- Produce an optimized Parquet file
-- ZSTD compression + an appropriate Row Group size
COPY (
    SELECT * FROM large_table
    ORDER BY partition_key, timestamp  -- sort to maximize the compression ratio
) TO 'optimized_output.parquet'
(FORMAT PARQUET,
 COMPRESSION ZSTD,
 ROW_GROUP_SIZE 122880);  -- about 120K rows per group

-- Export partitioned Parquet
COPY (SELECT * FROM events)
TO 'output_dir' (FORMAT PARQUET,
                  PARTITION_BY (year, month),
                  COMPRESSION ZSTD);

12. Troubleshooting

Common Errors and How to Fix Them

Out of Memory errors are the problem that comes up most often.

-- Symptom: "Out of Memory Error: could not allocate block of size ..."

-- Fix 1: raise the memory limit
SET memory_limit = '32GB';

-- Fix 2: enable disk spill
SET temp_directory = '/tmp/duckdb_spill';

-- Fix 3: split the query into chunks
-- process partition by partition instead of aggregating the whole table
CREATE TABLE monthly_agg AS
SELECT * FROM (
    SELECT month, SUM(amount) AS total
    FROM read_parquet('data/year=2025/month=01/*.parquet')
    GROUP BY month
    UNION ALL
    SELECT month, SUM(amount) AS total
    FROM read_parquet('data/year=2025/month=02/*.parquet')
    GROUP BY month
    -- ... and so on
);

S3 connection errors also come up frequently.

-- Symptom: "HTTP Error: Unable to connect to URL"

-- Fix 1: check the credentials
SELECT * FROM duckdb_secrets();

-- Fix 2: state the region explicitly
CREATE OR REPLACE SECRET (
    TYPE S3,
    REGION 'ap-northeast-2',
    KEY_ID 'AKIA...',
    SECRET '...'
);

-- Fix 3: custom endpoint (MinIO, R2, and so on)
CREATE SECRET (
    TYPE S3,
    KEY_ID 'minioadmin',
    SECRET 'minioadmin',
    ENDPOINT 'localhost:9000',
    USE_SSL false,
    URL_STYLE 'path'
);

File lock conflicts occur in multi-process environments.

-- Symptom: "Could not set lock on file ... database is locked"

-- Cause: another process has the same DB file open
-- DuckDB allows only a single Writer

-- Fix 1: set access_mode to read_only (when you only need to read)
-- in Python:
-- con = duckdb.connect('my.db', read_only=True)

-- Fix 2: query Parquet directly in in-memory mode
-- this avoids the DB file lock entirely
-- con = duckdb.connect()  -- in-memory
-- con.sql("SELECT * FROM read_parquet('data.parquet')")

13. Production Operating Patterns

These are patterns for using DuckDB safely in production.

Pattern 1: The Transformation Layer of an ETL Pipeline

import duckdb
from datetime import datetime

def daily_aggregation_job():
    """Use DuckDB as the T (Transform) stage of ETL"""
    con = duckdb.connect()

    today = datetime.now().strftime('%Y-%m-%d')

    # Read the source data from S3 → transform → save the result to S3
    con.execute(f"""
        INSTALL httpfs; LOAD httpfs;
        CREATE SECRET (TYPE S3, REGION 'ap-northeast-2');

        COPY (
            SELECT
                date_trunc('hour', event_ts) AS hour,
                event_type,
                COUNT(*) AS cnt,
                COUNT(DISTINCT user_id) AS unique_users,
                APPROX_COUNT_DISTINCT(session_id) AS approx_sessions,
                PERCENTILE_CONT(0.50) WITHIN GROUP
                    (ORDER BY duration_ms) AS p50_duration,
                PERCENTILE_CONT(0.99) WITHIN GROUP
                    (ORDER BY duration_ms) AS p99_duration
            FROM read_parquet(
                's3://raw-data/events/dt={today}/**/*.parquet'
            )
            GROUP BY hour, event_type
        )
        TO 's3://processed-data/hourly_agg/dt={today}/result.parquet'
        (FORMAT PARQUET, COMPRESSION ZSTD);
    """)

    con.close()
    print(f"Daily aggregation completed for {today}")

Pattern 2: CI/CD Data Quality Checks

import duckdb
import sys

def validate_data_quality(parquet_path: str) -> bool:
    """Verify data quality automatically in a CI/CD pipeline"""
    con = duckdb.connect()
    checks_passed = True

    # Check 1: minimum row count
    row_count = con.execute(f"""
        SELECT COUNT(*) FROM read_parquet('{parquet_path}')
    """).fetchone()[0]

    if row_count < 1000:
        print(f"FAIL: Row count {row_count} below minimum 1000")
        checks_passed = False

    # Check 2: NULL ratio
    null_check = con.execute(f"""
        SELECT
            ROUND(COUNT(*) FILTER (WHERE user_id IS NULL)
                  * 100.0 / COUNT(*), 2) AS null_pct
        FROM read_parquet('{parquet_path}')
    """).fetchone()[0]

    if null_check > 5.0:
        print(f"FAIL: user_id NULL ratio {null_check}% exceeds 5%")
        checks_passed = False

    # Check 3: value range
    range_check = con.execute(f"""
        SELECT
            MIN(amount) AS min_amt,
            MAX(amount) AS max_amt
        FROM read_parquet('{parquet_path}')
    """).fetchone()

    if range_check[0] < 0:
        print(f"FAIL: Negative amount detected: {range_check[0]}")
        checks_passed = False

    con.close()
    return checks_passed

if __name__ == '__main__':
    path = sys.argv[1]
    if not validate_data_quality(path):
        sys.exit(1)
    print("All data quality checks passed")

Pattern 3: Embedded Analytics Inside a Microservice

By embedding DuckDB inside an API server, you can return fast aggregate responses without calling out to an external analytics service.

from fastapi import FastAPI
import duckdb

app = FastAPI()

# Connect only once at application startup (connection reuse)
analytics_con = duckdb.connect('analytics_cache.db', read_only=True)

@app.get("/api/dashboard/summary")
async def dashboard_summary(date_from: str, date_to: str):
    result = analytics_con.execute("""
        SELECT
            product_category,
            COUNT(*) AS orders,
            ROUND(SUM(revenue), 2) AS total_revenue,
            ROUND(AVG(revenue), 2) AS avg_order_value
        FROM sales
        WHERE order_date BETWEEN ? AND ?
        GROUP BY product_category
        ORDER BY total_revenue DESC
    """, [date_from, date_to]).df()

    return result.to_dict(orient='records')

14. Failure Cases and Responses

Here are the failure cases teams commonly hit when adopting DuckDB in practice, and how to respond to them.

Failure 1: Applying DuckDB to an OLTP Workload

Symptom: DuckDB was applied to a service backend with frequent row-level reads and writes, such as user authentication and order processing, and single-row lookups turned out abnormally slow while lock conflicts happened often on concurrent writes.

Cause: DuckDB is a columnar OLAP engine. For single-row point queries, the overhead of columnar storage works against you. On top of that, the single-Writer constraint makes concurrent writes fundamentally impossible.

Response: use PostgreSQL or MySQL for OLTP workloads and keep DuckDB as a separate analytics-only layer. Adopt a two-track structure in which the OLTP database's data is exported to Parquet periodically and analyzed with DuckDB.

Failure 2: Handling a Dataset Larger Than Memory Unguarded

Symptom: running a GROUP BY aggregation over a 100GB Parquet file on a 32GB RAM server got the process killed by OOM.

Cause: DuckDB does provide spill to disk, but if temp_directory is not configured or there is not enough temporary disk space, it tries to do the work in memory alone.

Response: temp_directory must be set to an SSD path with enough space. Also set memory_limit lower than system RAM (around 70~80%) to protect the memory of the OS and other processes.

Failure 3: Sharing a Single DB File Across Concurrent Services

Symptom: when several microservices accessed the same DuckDB file at once, "database is locked" errors happened constantly.

Cause: only one process can access DuckDB as a Writer. When several processes try to write at the same time, lock conflicts occur.

Response: restrict write work to a single process and use read_only mode for read-only access. Alternatively, switch to a structure where each service queries Parquet files directly in in-memory mode. If concurrent multi-user access is a hard requirement, consider moving to MotherDuck or ClickHouse.

Failure 4: Using It for Real-Time Streaming Ingestion

Symptom: INSERTing tens of thousands of events per second from Kafka into DuckDB in real time made write performance degrade sharply.

Cause: DuckDB is optimized for analytical queries (reads) and is not suited to row-level real-time INSERTs. Columnar storage is efficient for batch writes but carries heavy overhead for single-row INSERTs.

Response: handle real-time ingestion with Kafka + ClickHouse or Kafka + S3 (Parquet), and use DuckDB solely to analyze the Parquet files once they have landed.

15. When Not to Use DuckDB

Recognizing clearly where DuckDB is a poor fit is the first step to choosing the right tool.

16. References

Comments

No comments yet.

Sign in to leave a comment