duckdb

duckdb Best Practices

DuckDB is our go-to for analytical workloads. These rules ensure our DuckDB scripts are not only performant but also readable, reproducible, and easy to maintain. Adhere to these guidelines for all new and refactored DuckDB code.

1. Code Organization and Structure

1.1. Standardize Script Headers with Pragmas

Always start DuckDB scripts with a clear header that sets session-level pragmas, imports necessary extensions, and defines initial data sources. This ensures consistent execution environments.

❌ BAD: Inconsistent settings, implicit behavior.

-- analytics/daily_summary.sql
SELECT * FROM read_parquet('s3://my-bucket/data.parquet');

✅ GOOD: Explicitly configured environment.

-- analytics/daily_summary.sql
PRAGMA threads=4; -- Allocate 4 CPU threads for this session
PRAGMA memory_limit='8GB'; -- Limit memory usage to 8GB
INSTALL httpfs; -- Install S3/HTTP(S) extension
LOAD httpfs; -- Load the extension for use

-- Subsequent queries will benefit from these settings
SELECT
    date_trunc('day', event_timestamp) AS day,
    COUNT(*) AS daily_events
FROM read_parquet('s3://my-bucket/events_2024.parquet')
GROUP BY 1;

1.2. Structure Complex Logic with CTEs

Use Common Table Expressions (CTEs) to break down complex queries into logical, readable steps. This improves clarity, debugging, and often allows DuckDB's optimizer to produce better plans.

❌ BAD: Nested subqueries are hard to read and debug.

SELECT
    final.region,
    final.avg_sales
FROM (
    SELECT
        sub.region,
        AVG(sub.total_sales) AS avg_sales
    FROM (
        SELECT
            o.region,
            SUM(li.price * li.quantity) AS total_sales
        FROM orders o
        JOIN line_items li ON o.order_id = li.order_id
        WHERE o.order_date >= '2024-01-01'
        GROUP BY o.region
    ) sub
    GROUP BY sub.region
) final
WHERE final.avg_sales > 1000;

✅ GOOD: CTEs provide clear, modular steps.

WITH regional_sales AS (
    SELECT
        o.region,
        SUM(li.price * li.quantity) AS total_sales
    FROM orders o
    JOIN line_items li ON o.order_id = li.order_id
    WHERE o.order_date >= '2024-01-01'
    GROUP BY o.region
),
average_regional_sales AS (
    SELECT
        region,
        AVG(total_sales) AS avg_sales
    FROM regional_sales
    GROUP BY region
)
SELECT
    region,
    avg_sales
FROM average_regional_sales
WHERE avg_sales > 1000;