Where Does DuckDB Help? A Complete Guide

DuckDB helps anywhere an analytical query fits on one machine, which, as we’ll see, covers most of the DuckDB use cases a data team runs day to day: ad hoc exploration, Parquet scans on object storage, notebook work, CI tests, and embedded analytics inside an application. DuckDB’s own benchmark report, published in October 2025 and run on DuckDB 1.4 LTS, pushed that ceiling further than most engineers expect: on a single AWS i8g.48xlarge instance (192 cores, 1.5TB RAM, 12 local NVMe disks), it completed all 22 TPC-H queries at scale factor 100,000 (100TB of source data), with a median runtime of 1.19 hours. Past that ceiling, options such as Apache Spark on Databricks, managed services like MotherDuck, or execution layers such as Yeedu that run inside a customer’s own cloud account take over. 

In this guide, we map the DuckDB use cases that make sense, the ones that don’t, and the point where a job outgrows a single process. DuckDB is an in-process, embedded analytical database: a single binary with no server to install, no daemon to babysit and no cluster to provision, queried directly from Python, R, Java, Node, Rust, the command line or a browser tab. Most of what follows, as we’ll show, is just that one architectural choice working out its consequences. 

DuckDB Architecture: Why a Single Binary Changes the Calculus 

A single decision drives everything else, and we’ll trace its consequences throughout this section: DuckDB runs as a library inside the host process instead of as a separate server, so the storage format, the execution engine and the transaction model all follow from that rather than from a cluster topology. DuckDB’s own documentation calls it “a relational (table-oriented) DBMS that supports SQL,” built for OLAP workloads through a columnar-vectorized query execution engine and shipped as a single-file database with no external dependencies. It scales, per that page, “from small, resource-constrained edge devices to large multi-terabyte memory servers with 100+ CPU cores,” which is vertical scaling, not the horizontal, add-more-machines scaling a cluster relies on. 

The project is MIT licensed, with intellectual property held by the independent, non-profit DuckDB Foundation rather than a single vendor. That governance detail stopped being theoretical in August 2026, when AWS announced it was acquiring DuckLabs, the company founded by DuckDB’s creators, while stating plainly that it was not acquiring the open source DuckDB project itself, which stays under the Foundation’s governance.

Columnar storage and compression 

Data on disk is organized by column rather than row, with each column compressed using a scheme chosen for its value distribution, part of the DuckDB use cases this format quietly supports, in our view, is portability: moving a dataset means copying one file, not restoring a cluster. 

Vectorized, push-based execution and the optimizer 

Queries run through a vectorized engine that processes batches of values per operator call instead of one row at a time, and operators push data to their parents rather than pulling it on demand, which keeps CPU caches warm across a pipeline. A query optimizer rewrites the plan before any of that runs, choosing join order and pushdown opportunities automatically. DuckDB schedules the resulting work across every available core in small batches, a scheduling style commonly described as morsel-driven parallelism, so one query keeps every core busy with no cluster coordinator involved, as we’ve observed. 

MVCC transactions and ACID guarantees 

DuckDB supports full ACID transactions with snapshot isolation, using a bulk-optimized MVCC design derived from the HyPer database, so changes made inside one transaction stay invisible to concurrent transactions until commit. Multiple threads inside one process can write concurrently as long as they don’t conflict, a property many DuckDB use cases rely on when a pipeline runs several concurrent writers, as we’ve seen, since optimistic concurrency control does the work a lock manager would otherwise do. 

Out-of-core operators for larger-than-memory workloads 

Grouping, joining, sorting and windowing operators spill to a temp directory on disk via radix partitioning when a working set exceeds memory, rather than failing outright, in both in-memory and persistent modes. Setting PRAGMA max_memory alongside a configured temp_directory makes that spilling predictable. DuckDB’s own troubleshooting docs are explicit that this isn’t a blanket guarantee: certain aggregates, PIVOT, and stacks of several blocking operators can still exhaust memory if there isn’t enough of it relative to the data, as we’ve seen.

The DuckDB Query Engine in Practice 

The DuckDB query engine reaches files where they already sit instead of demanding a load step first, which, to us, is what makes it feel less like a database and more like SQL bolted onto a filesystem. A query opens a Parquet file, a CSV, JSON, an Arrow stream, or a Pandas or Polars dataframe directly in the FROM clause. When it scans Parquet, it pushes predicates and the column projection into the scan itself, so a filtered, narrow query over a wide file reads only the row groups and columns it actually needs. 

As documented in the DuckDB 2.0 Python guide, replacement scans are the mechanism behind querying a dataframe by name: when a referenced table isn’t in the catalog, DuckDB substitutes a table function that reads the in-memory object directly, with zero-copy interchange against Arrow-backed dataframes.   

import duckdb, pandas 
my_df = pandas.DataFrame.from_dict({"a": [42]}) 
# No registration step: DuckDB finds my_df by variable name 
result = duckdb.sql("SELECT * FROM my_df").df() 

Object storage works through the same pattern. The httpfs extension lets a read_parquet() call address an S3, GCS or Azure path directly. As of DuckDB 1.5’s core extensions docs, separate extensions built on DuckDB’s general extension framework add postgres, mysql and sqlite support: an ATTACH 'host=... dbname=...' (TYPE postgres) statement makes external tables queryable like native ones, turning a one-off federation task into a single SQL statement rather than a job of its own. That reach, straight into object storage and straight into other databases, is where a lot of the DuckDB use cases in this guide earn their keep, well before anyone measures a speed difference.

Friendly SQL and Developer Ergonomics 

DuckDB’s SQL dialect adds shorthands on top of standard SQL that remove boilerplate, and, as of the DuckDB 2.0 docs, its dialect reference names them directly. GROUP BY ALL infers the grouping columns from the SELECT list instead of repeating them, ORDER BY ALL does the same for sorting, and SELECT * EXCLUDE (...) or SELECT * REPLACE (...) let a star expression drop or override specific columns.

-- GROUP BY ALL infers group columns from the SELECT list 
SELECT region, product, sum(revenue) 
FROM sales 
GROUP BY ALL; 
 
-- SELECT * EXCLUDE drops named columns from a star expansion 
SELECT * EXCLUDE (internal_id) FROM sales; 

The same docs cover trailing commas in column and struct lists, PIVOT/UNPIVOT, ASOF joins for time-series alignment, LATERAL and positional joins, and comprehensions over DuckDB’s native list and struct types. None of it changes what a query returns. It changes how long it takes to write one, which, we’ve noticed, compounds once a pipeline has forty of them.

DuckDB Use Cases 

DuckDB use cases cluster around one property: a single process, a single machine, and nothing to provision before the first query runs. Interactive analysis over a few hundred gigabytes on a laptop or one cloud VM is the obvious case, since the engine starts in milliseconds. Querying Parquet or Iceberg files on object storage without standing up anything is another; httpfs does the work a small Spark cluster would otherwise exist only to do. 

Local development and unit testing of pipeline logic in CI is a quieter use case that saves real money: a test suite spins up an in-memory database, runs the same SQL production runs, and tears it down, with no cluster minutes billed. Embedded analytics inside an application or a serverless function pushes the same idea further, with DuckDB shipping as a dependency rather than a service a request calls out to. 

Data science and notebook work sit here too, next to Pandas and Polars rather than instead of them. Projects already using dbt can point the dbt-duckdb adapter at a local file or at Parquet on object storage and run the modeling layer directly against DuckDB. DuckDB-Wasm compiles the same engine to WebAssembly, letting a browser tab query a dataset client-side with no backend service behind it. And a fair number of small, scheduled jobs, a nightly aggregation, a daily extract, a reconciliation report, run today, in our experience, on a cluster sized for jobs ten times their size, mostly because nobody has asked whether they need one.  

DuckDB for ETL and Data Engineering 

DuckDB for ETL work follows a plain read-transform-write pattern: read source files or tables, apply SQL, write the result back out, usually as partitioned Parquet that downstream tools can read without DuckDB involved at all. 

-- Partitioned Parquet write, Hive-style directories 
COPY orders TO 'orders' (FORMAT parquet, PARTITION_BY (year, month)); 
 
-- Derive partition columns on the fly, then append to existing directories 
COPY (SELECT *, year(ts) AS year, month(ts) AS month FROM services) 
TO 'out' (PARTITION_BY (year, month), APPEND); 

Incremental loads filter the source on a watermark column and append new partitions, but idempotency is on the pipeline author, not the engine: rerunning a job without checking what already landed duplicates rows. A failed job is a failed process, so there’s no partial-cluster recovery to reason about, which, we find, actually simplifies wiring retries and observability into Airflow or Prefect. What a single process doesn’t give back is task-level retry inside the job itself; if it dies mid-run, the whole run restarts. 

In our experience, DuckDB for data engineering mostly changes where the feedback loop lives rather than what the pipeline logic looks like. A transformation tested against a local Parquet sample runs the identical SQL in CI and production, because it’s the same library in all three places rather than a thin client talking to a different service each time.

DuckDB Benefits 

In our experience, DuckDB benefits follow mechanically from running in-process rather than as a server. There is no cluster to start before a query runs, so there is no startup latency and no idle compute billed while nothing executes. One dependency replaces what would otherwise be a cluster manager, a scheduler and a set of worker images, which reduces the number of moving parts a pipeline depends on. A database is a single file, so moving one, backing one up, or attaching it to a bug report is a file copy rather than a restore procedure. The engine is also available inside a test process itself, so the same SQL that runs in CI runs in production without a separate client library or a different code path in between, as we’ve found. 

DuckDB Limitations 

DuckDB limitations follow directly from being a single-node, OLAP-shaped engine rather than an OLTP store or a streaming system, and DuckDB’s own docs state most of them without hedging. “DuckDB is a single-node database system, hence it makes use of vertical scalability,” per its FAQ. 

Single node, single writer 

Capacity tops out at whatever one machine provides. Multiple threads in one process can write concurrently absent a conflict, but writing to the native database file from multiple separate processes isn’t supported; multi-process write access needs the separate Quack client-server protocol, at which point it’s no longer purely embedded. 

No server, users or roles 

There’s no built-in authentication layer, no user accounts and no role-based access control, because there’s no server listening on a network port to protect. Access control has to come from the filesystem, the object store’s IAM policy, or a layer built on top. 

Bounded concurrency 

In our experience, a single process serving many simultaneous analysts doesn’t behave like a warehouse built for exactly that. DuckDB’s docs also warn specifically against read-write workloads on network-attached storage, calling such setups often slow and result in spurious failures. 

Memory limits despite spilling 

Out-of-core spilling covers grouping, joining, sorting and windowing, not everything, as we’ve mentioned. Certain aggregates, PIVOT, and queries stacking several blocking operators can still exhaust memory, and a large join on an undersized machine will spill hard enough to erase any edge over a distributed engine. 

No cluster-level fault tolerance 

There’s no lineage-based recomputation of a lost partition and no cluster manager to restart a failed task on another node, because there’s no other node. A failed run restarts from the top, a different failure model from a distributed engine’s per-task retry.

Sizing DuckDB and Choosing the Right Engine 

In our experience, sizing a job for DuckDB starts with one cloud instance and asks whether the working set, not the raw source size, fits inside its memory and local NVMe. According to DuckDB’s October 2025 benchmark report, run on DuckDB 1.4 LTS, one i8g.48xlarge, 192 cores and 1.5TB of RAM with 12 local NVMe disks in RAID-0, processed a 100TB TPC-H dataset down to a roughly 27TB database file, spilling about 7TB to disk, and still finished all 22 queries with a median runtime of 1.19 hours. That is the ceiling a single machine can reach today, not a figure to plan a typical job around, and it’s a useful gut check on how much DuckDB architecture decisions have widened what “one machine” means. 

We think a shorter checklist travels better than the benchmark. Does the working set, after filtering and projection, fit inside the instance’s RAM with headroom to spill? How many people or jobs need concurrent access, one at a time or dozens at once? How long can the job run, and what happens if the process dies mid-way through? One machine, one writer, a tolerable restart, points at DuckDB. A no to any of those points at a distributed engine.  

Where DuckDB stops helping is the mirror of where it helps: working sets that genuinely exceed what one economical machine can hold, multi-stage jobs with heavy shuffle between stages, concurrency across an entire team, streaming ingestion at real scale, and distributed machine learning built around a cluster’s combined memory rather than one process’s. Databricks describes the mechanism that buys those properties: a driver coordinates independent executor processes across a pluggable cluster manager, and a shuffle writes map output to local disk so it can be served to other executors after the producing task has finished. Fault tolerance there is lineage-based, recomputing a lost partition on another node, with a configurable per-task retry count before the whole job gives up; none of that exists inside a single DuckDB process, because none of it needs to when the job fits on one machine. Databricks prices that cluster time by the Databricks Unit, billed per second on top of underlying cloud infrastructure cost except on serverless compute, where that infrastructure cost is bundled into the per-second rate, which is a different economic model from a single DuckDB process entirely. Polars takes a related but distinct approach to the larger-than-memory problem on one node: as of Polars 2.0, the Lazy API’s default collection engine processes data in streaming batches, a different mechanism from DuckDB’s operator-level spilling aimed at a similar ceiling.

 Open Table Formats, DuckLake, and the Managed Options 

DuckLake and the SQL catalog pattern 

DuckLake is described on its own project page as an open lakehouse format built on SQL and Parquet: table metadata lives in a catalog database, which can itself be DuckDB, Postgres, SQLite or another engine, while the actual data stays in Parquet files. The ducklake extension lets DuckDB read and write DuckLake tables directly, and the project is MIT licensed and under active development. 

Iceberg and Delta extensions 

DuckDB’s Iceberg extension sits in the secondary support tier on DuckDB’s extension-overview page, and other current docs describe its behavior as experimental. iceberg_scan reads a table straight from storage with no catalog attached, but write support, covering INSERT, UPDATE, DELETE and MERGE INTO, only exists once a REST catalog is attached, since committing new metadata needs somewhere to commit it to. Writes are also limited to positional deletes, a merge-on-read strategy; tables requiring copy-on-write aren’t supported, and an operation against one fails outright. DuckDB also ships a Delta extension that reads and writes Delta Lake tables; as with Iceberg, the current docs are the place to check which operations are supported before depending on either in a production write path. None of this, in our view, turns DuckDB into a lakehouse platform; it reads and writes the tables one already has. 

MotherDuck and DuckDB-Wasm 

MotherDuck exists as a managed, cloud-hosted version of DuckDB, adding per-user isolated compute and a feature it calls Dual Execution that can route part of a query to local DuckDB and part to the cloud. MotherDuck’s pricing page lists compute billed by instance size per second: the metered Pulse tier runs $0.60 per CU-hour, Standard $2.40/hour, Jumbo $4.80/hour, Mega $12/hour and Giga $24/hour, with storage billed separately at $0.04/GB per month; its Business plan adds a $250/org/month base fee on top of usage. Those are list rates taken from MotherDuck’s pricing page as of November 2026, not a benchmarked total cost of ownership, and the page gives no comparison to a self-hosted DuckDB instance of equivalent size. DuckDB-Wasm is the browser-side counterpart: the same engine compiled to WebAssembly, running inside a tab for client-side dashboards with no backend query service behind them. 

Yeedu: Running Existing Spark Jobs Without a Distributed Shuffle 

Yeedu is a re-engineered Apache Spark execution layer that runs inside a customer’s own cloud account or on-premises servers, adopted job by job: the catalog, notebooks, dashboards and orchestration DAGs around a job that hasn’t moved stay exactly where they are. Its notebooks support Python, PySpark, SQL, Pandas and DuckDB as workload options. One feature, Yeedu mode, runs an entire Spark job on a single VM instead of a driver plus a fleet of workers, removing the distributed shuffle for jobs whose data fits that one machine, the same single-machine argument we make about DuckDB in this guide, applied to Spark code that is already written; no speedup or cost figure is published for it, and it’s scoped to jobs that fit one VM. On pricing, Yeedu sells a fixed annual licence with unlimited usage rather than metering by the hour, a structurally different model from consumption billing, not a cheaper one in the abstract. 

Conclusion 

In our experience, most analytical work a data team runs in a given week fits on one machine, and treating that as the default rather than the exception changes how a job gets built, tested and billed. Keeping data in open formats like Parquet, Iceberg or Delta keeps the engine a choice instead of a lock-in, since the same files are readable by DuckDB, Spark or whatever replaces either in five years. Our advice: size the job before picking the engine, and measure the actual job rather than a published benchmark, because a benchmark describes someone else’s hardware and someone else’s query shape. 

• DuckDB is an in-process, embedded OLAP engine: one binary, no server, queried from Python, R, Java, Node, Rust, the CLI or a browser. 
• Its architecture, columnar storage, vectorized push-based execution, MVCC and ACID transactions, out-of-core spilling, buys speed and simplicity on one machine, not horizontal scale. 
• It reads Parquet, CSV, JSON, Arrow, Pandas and Polars dataframes in place, with pushdown and zero-copy interchange. 
• Limitations are real and undramatic: single writer, no server-side auth, bounded concurrency, no cluster-level fault tolerance. 
• DuckLake, the Iceberg extension and the Delta extension keep DuckDB talking to lakehouse tables without becoming a lakehouse platform. 
• Past one machine’s economical ceiling, a distributed engine such as Spark, or a managed layer such as MotherDuck, takes over; a Spark job that already fits one VM can also run there through a single-VM execution layer such as Yeedu, with no shuffle to pay for. 

FAQs 

What is DuckDB used for? 

In our view, DuckDB is used for analytical SQL queries that fit on one machine: interactive data exploration, querying Parquet and CSV files on a laptop or in object storage, embedded analytics inside an application, and pipeline development and testing in CI. It’s an in-process database, so, in our experience, it’s used as a library inside another program at least as often as a standalone tool. 

Does Yeedu support DuckDB alongside Spark? 

Yes. Yeedu’s notebooks support Python, PySpark, SQL, Pandas and DuckDB as workload options. Yeedu itself is a re-engineered Spark execution layer that runs inside a customer’s own cloud account; DuckDB is listed as one of the supported notebook languages and workloads, and no separate claim is published about Yeedu accelerating or optimizing DuckDB queries specifically. 

Is DuckDB good for ETL? 

In our experience, DuckDB is good for ETL jobs that read source files, transform them with SQL, and write the result back out as Parquet, all inside one process with no cluster to provision. It’s a weaker fit once a pipeline needs task-level retry on partial failure or has to span more data than one machine can hold. 

What are the limitations of DuckDB? 

DuckDB’s limitations start with being single-node: capacity is bounded by one machine’s cores, memory and disk, and only one process can write its native database file at a time. There’s no built-in server, user accounts or role-based access control, concurrency across many simultaneous analysts is limited, and a failed run restarts from the top rather than resuming a lost task on another node. 

Can DuckDB handle data larger than memory? 

Yes, within limits. DuckDB has out-of-core support for grouping, joining, sorting and windowing, spilling to a configured temp directory via radix partitioning instead of failing, in both in-memory and persistent modes, as we’ve noted. Its own docs are explicit that certain aggregates, PIVOT, and stacks of multiple blocking operators can still run out of memory if there isn’t enough relative to the data. 

Can DuckDB query Parquet files on S3 directly? 

Yes. The httpfs extension lets a read_parquet() call address an S3, GCS or Azure path directly, with predicate and projection pushdown into the scan so a filtered query reads only the columns and row groups it needs, with no separate download or cluster job first, as we’ve seen. 

Is DuckDB a replacement for Apache Spark? 

Not generally, in our view; the two solve differently shaped problems. Spark coordinates independent executor processes across a cluster with lineage-based fault tolerance, a mechanism a single DuckDB process doesn’t have. Some teams run existing Spark jobs on an execution layer, such as Yeedu, that puts the whole job on one VM when the data fits, borrowing DuckDB’s single-machine argument without actually being DuckDB.