DuckDB Practical Uses, in depth: Parquet and CSV analysis, dataframes, lake queries, ETL, CI data tests, and where it stops fitting

Why it is fast: the design in three ideas

Columnar storage and execution. Analytical queries touch a few columns of many rows. Storing and processing each column contiguously means a query summing amount reads only that column, and values of one type sit together where they compress well. The same reasoning applies to Parquet, which is why DuckDB and Parquet fit so well; the columnar databases article covers the storage side in depth.

Vectorized execution. Instead of pulling one row at a time through an operator tree, DuckDB pushes batches (vectors) of values through each operator, amortising interpretation overhead and letting tight loops use CPU caches and SIMD. Pipelines run in parallel across all cores by default.

In-process. The engine is a library. There is no network hop and no serialisation between your program and the database, so DuckDB can read a pandas or Arrow object in your process memory directly. The cost of that design is the concurrency model described later: it is a database for one process at a time, not for a fleet of clients.

It also works out of core. A buffer manager caps memory at memory_limit (by default a large fraction of system RAM) and spills intermediate results such as big hash joins and sorts to a temporary directory, so queries over data larger than RAM complete instead of crashing, at the cost of disk I/O.

Use 1: querying Parquet and CSV in place

The most common use needs no loading step. A file path or glob is a table:

-- CLI or any client. Globs, hive-style partitions and schema union in one call.
SELECT event_type, count(*) AS n, approx_count_distinct(user_id) AS users
FROM read_parquet('events/year=*/month=*/*.parquet', hive_partitioning = true)
WHERE year = 2026 AND month = 9
GROUP BY ALL
ORDER BY n DESC;

-- CSV with type sniffing; inspect what it inferred before trusting it.
DESCRIBE SELECT * FROM read_csv('exports/orders_*.csv');

Two optimisations make this fast without any index. Projection pushdown reads only the referenced columns from each Parquet file. Filter pushdown uses partition values in directory names to skip whole files and Parquet row-group min/max statistics to skip blocks inside files. The filter on year and month above means most files are never opened. Pushdown only helps if the data is laid out for it: files sorted or partitioned by the columns you filter on have tight statistics, while randomly ordered files force full scans. See the Parquet format guide for row groups and encodings.

CSV has no statistics and needs type inference. The sniffer is good, but for production loads pin the schema with the columns option, so that a file where a numeric column suddenly contains N/A fails loudly instead of silently becoming text. GROUP BY ALL groups by every non-aggregated column, a small convenience that removes a common source of typos.

Use 2: a faster engine for your dataframes

In Python, DuckDB can query a pandas DataFrame, Polars DataFrame or Arrow table by its variable name (a feature called replacement scans), and return results in any of those formats. Arrow data is read without copying.

import duckdb, pandas as pd

orders = pd.read_parquet("orders.parquet")          # existing pandas code
con = duckdb.connect()                               # in-memory database

top = con.sql("""
    SELECT customer_id, sum(amount) AS revenue,
           rank() OVER (ORDER BY sum(amount) DESC) AS rnk
    FROM orders                                      -- the DataFrame, by name
    WHERE status = 'paid'
    GROUP BY customer_id
    QUALIFY rnk <= 100
""").df()                                            # back to pandas; .pl() gives Polars

This is worth doing when a pandas pipeline is slow because of large group-bys, joins or window functions, which DuckDB runs in parallel and out of core while pandas runs mostly single-threaded and entirely in memory. It is not worth doing for row-by-row Python logic, which no SQL engine accelerates. A cleaner pattern than loading into pandas first is to let DuckDB read the files directly and hand only the small result to pandas, so the large intermediate never exists as a Python object. For how Arrow makes these hand-offs cheap, see the Apache Arrow article.

Use 3: querying the lake directly

The httpfs extension reads over HTTP and S3-compatible APIs; the azure extension reads Azure Blob Storage through az:// or azure:// URLs and ADLS Gen2 through abfss://. Credentials belong in the secrets manager rather than in query text, and the credential-chain provider picks up the same identity your other cloud tools use.

INSTALL httpfs; LOAD httpfs;
CREATE SECRET lake_s3 (TYPE s3, PROVIDER credential_chain);   -- env, profile, instance role

SELECT count(*) FROM read_parquet('s3://acme-lake/events/2026/09/*/*.parquet');

INSTALL azure; LOAD azure;
CREATE SECRET lake_az (TYPE azure, PROVIDER credential_chain,
                       ACCOUNT_NAME 'acmelake');
SELECT * FROM 'abfss://events/2026/09/01/part-0.parquet' LIMIT 10;

Remote reads issue HTTP range requests, so pushdown matters even more: a well-partitioned lake lets a laptop answer questions over terabytes by fetching only megabytes. Badly laid out data (millions of tiny files, or unpartitioned files with no useful statistics) makes every query a crawl through object listings and footers. Data-transfer charges also apply when the laptop is outside the cloud region, so for regular heavy queries run DuckDB on a VM in the same region. DuckDB can also read table formats such as Iceberg and Delta through extensions; check the current extension documentation for which features (deletes, time travel, writes) are supported before relying on them, and see the Iceberg article for what those formats add over plain Parquet.

Use 4: an ETL step that writes partitioned Parquet

Many pipelines need a single-machine transformation: take raw CSV or JSON drops, clean and type them, deduplicate, and write analytics-ready Parquet. DuckDB does this in one statement with COPY ... TO, including hive-style partitioning:

COPY (
    SELECT * EXCLUDE (raw_ts),
           CAST(raw_ts AS TIMESTAMP)        AS ts,
           year(CAST(raw_ts AS TIMESTAMP))  AS year,
           month(CAST(raw_ts AS TIMESTAMP)) AS month
    FROM read_json_auto('landing/2026-09-*/*.json')
    QUALIFY row_number() OVER (PARTITION BY event_id ORDER BY raw_ts DESC) = 1
    ORDER BY ts
) TO 'curated/events'
  (FORMAT parquet, PARTITION_BY (year, month), COMPRESSION zstd, OVERWRITE_OR_IGNORE);

Sorting by ts before writing gives each row group a narrow time range, so downstream filters on time skip most of the data. The QUALIFY clause deduplicates on event_id keeping the latest record, the usual fix for at-least-once delivery. Two cautions: writing to object storage is not transactional, so write to a staging prefix and switch readers over only after success; and the overwrite option replaces files that collide but does not remove stale partitions from earlier runs, so define how reruns clean up before the first incident does it for you.

Use 5: federation and data tests

The postgres and sqlite extensions let DuckDB ATTACH another database and query its tables alongside files, which is convenient for joining a production lookup table with lake data, or for snapshotting tables into Parquet:

INSTALL postgres; LOAD postgres;
ATTACH 'dbname=shop host=replica.internal user=reporting' AS pg (TYPE postgres, READ_ONLY);

COPY (SELECT * FROM pg.public.customers) TO 'snapshots/customers.parquet' (FORMAT parquet);

SELECT c.segment, sum(e.amount) AS revenue
FROM read_parquet('curated/events/*/*/*.parquet') e
JOIN pg.public.customers c USING (customer_id)
GROUP BY ALL;

Point it at a read replica: a large scan through the attachment is a large scan on that Postgres server. Filters are pushed into Postgres where possible, but joins and aggregations run in DuckDB after the rows arrive.

The same ingredients make cheap data tests in CI. A pull request that changes a transformation can run it on a sample in seconds and assert invariants with plain SQL, failing the build when a query returns rows:

import duckdb, sys
con = duckdb.connect()
checks = {
    "duplicate event_id": "SELECT event_id FROM 'out/*.parquet' GROUP BY 1 HAVING count(*) > 1",
    "null customer":      "SELECT * FROM 'out/*.parquet' WHERE customer_id IS NULL",
    "future timestamps":  "SELECT * FROM 'out/*.parquet' WHERE ts > now() + INTERVAL 1 DAY",
}
failed = [name for name, q in checks.items() if con.sql(q + " LIMIT 1").fetchone()]
sys.exit(f"data checks failed: {failed}" if failed else 0)

Worked example: 40 GB of logs on a 16 GB laptop

An engineer has 40 GB of JSON request logs and needs latency percentiles per endpoint per day. pandas would need several times the data size in RAM. With DuckDB the plan has two steps. First, convert once to sorted, partitioned Parquet with the COPY pattern above, which typically shrinks text logs several-fold and makes later queries scan a fraction of the bytes. Second, query the Parquet:

SET memory_limit = '10GB';
SET temp_directory = '/fast_ssd/duck_tmp';
SET preserve_insertion_order = false;     -- lets large exports and copies use less memory

SELECT endpoint, CAST(ts AS DATE) AS day,
       quantile_cont(latency_ms, [0.5, 0.95, 0.99]) AS p50_p95_p99,
       count(*) AS requests
FROM read_parquet('logs_parquet/*/*/*.parquet', hive_partitioning = true)
GROUP BY ALL
ORDER BY day, requests DESC;

The memory limit leaves headroom for the operating system and the Python process; the temporary directory on a fast local disk absorbs any spill; and relaxing insertion order lets DuckDB stream results without buffering to preserve file order. If the query still exhausts memory, the usual culprit is a very high-cardinality aggregation or a join whose build side is huge; reduce the key cardinality, pre-aggregate per partition, or run the query partition by partition. Timing each step on a sample before running on everything tells you whether you have a minute-scale or an hour-scale job.

Limits, failure modes and trade-offs

• Concurrency. A database file can be opened read-write by one process, or read-only by several; it is not a multi-writer server. Two services writing the same file will fail to open it. For shared concurrent writes use a server database, or write Parquet and let readers query the files.
• Transactional workloads. Many small single-row inserts and updates are slow compared with Postgres or SQLite; append in batches. If you need an OLTP store, see OLTP versus OLAP for why that is a different design.
• Memory surprises. The default limit is relative to the whole machine; inside containers or alongside other heavy processes, set it explicitly and point spill at real disk.
• Small-file lakes. Object-store listing and footer reads dominate; compact files before blaming the engine.
• Version churn. Releases move fast and storage formats and extensions evolve. Pin the version in production, and prefer the long-term-support line (the 1.4 series was designated LTS) where stability matters; check the release calendar before upgrading.
• Scale ceiling. One machine is one machine. When data or concurrency outgrows a large VM, a distributed engine or warehouse is the right tool, and DuckDB remains useful at the edges for development and testing.

What to do next

1. Install DuckDB and point it at a Parquet or CSV file you already query with pandas; compare runtime and peak memory.
2. Rewrite your slowest pandas group-by or join as DuckDB SQL over the source files, returning only the result to pandas.
3. Convert raw CSV or JSON landing data to sorted, partitioned Parquet with COPY ... TO, and verify that filtered queries skip files.
4. Configure credentials with CREATE SECRET and the credential chain instead of keys in query text.
5. Add a CI job that runs SQL invariants over a sample of each transformation's output.
6. Set memory_limit and temp_directory explicitly wherever DuckDB runs in containers, and pin the version.