DuckDB in Production: The Jobs It Genuinely Wins and the Single-Node Limits You Will Hit

The most useful thing I did for one client’s data platform was delete a Spark cluster. Their heaviest nightly job read 40 GB of Parquet from S3, joined it to two dimensions totaling 300 MB, aggregated, and wrote 6 GB back out. It ran on a ten-node EMR cluster in 22 minutes and cost about $18 a night in compute plus the operational tax of a cluster nobody wanted to own.

Rewritten as DuckDB in a single Fargate task with 16 GB of memory, it ran in 4 minutes and 40 seconds and cost roughly $0.11. Same output, byte for byte. The Spark version had existed for three years because in 2022 someone estimated the data would grow to terabytes. It grew to 40 GB.

I tell that story a lot, and then immediately follow it with the other one: the job I tried to move to DuckDB that needed a 28-column self-join on a 900 GB table and died repeatedly on spill-to-disk thrashing until I put it back on Spark. Both stories are the point. DuckDB has a real production envelope, it is much larger than most people assume, and it has a hard edge.

This is where that edge is, how to build inside it, and the things that break when you cross it. If you have not used DuckDB for anything yet, the local pipelines post covers the basics; this is about running it where a pager is attached.

The jobs DuckDB wins outright

Four workload shapes where I now default to DuckDB and have to be argued out of it.

Object-storage batch transforms under a few hundred GB. Read Parquet, join, aggregate, write Parquet. This is the bread and butter of most warehouses’ staging layer, and a single machine with 16 to 64 GB does it faster than a cluster because there is no shuffle over a network and no JVM to start.

Warehouse offload for cheap compute. Extract a slice from BigQuery or Snowflake once, do twenty analytical passes over it locally, write one result back. Every pass you do outside the warehouse is a pass you are not billed per-byte or per-credit for. On a table you would otherwise scan repeatedly, this is a 90 percent cost cut on that workload.

Data quality and reconciliation. Comparing two datasets row by row, checking distributions, validating referential integrity. These are heavily scan-bound and lightly memory-bound, which is DuckDB’s best case. I now run most pipeline assertions this way rather than as warehouse queries — the same pattern I described in Python data quality checks, just with a faster engine underneath.

Serving pre-computed aggregates. A read-only DuckDB file baked into a container image, queried by an API. No database server, no connection pool, single-digit millisecond queries on a few hundred MB. This is a genuinely underrated deployment pattern.

flowchart TD
  job["Batch job"] --> size{"Working set after filters"}
  size -->|"under ~200 GB"| duck["DuckDB, one container"]
  size -->|"over ~200 GB"| shape{"Shuffle-heavy?"}
  shape -->|"no, mostly scan and aggregate"| duckbig["DuckDB on a large instance"]
  shape -->|"yes, big joins or sorts"| spark["Spark or the warehouse"]
  duck --> writers{"Concurrent writers needed?"}
  writers -->|"yes"| nope["Not DuckDB. Use a warehouse."]
  writers -->|"no"| ship["Ship it"]

Working set after filtering, not raw table size, is the number that decides. Concurrency is a hard no rather than a threshold.

Memory is the entire constraint

DuckDB executes out-of-core: when an operator exceeds its memory budget it spills to a temp directory and keeps going. This is why “DuckDB can only handle data that fits in RAM” is wrong, and why “DuckDB handles any size data” is also wrong.

The useful framing is that DuckDB needs enough memory plus fast local disk to hold the working set of the largest blocking operator. A streaming aggregation over 500 GB with 200 distinct groups needs almost nothing. A hash join producing a 400 GB intermediate needs 400 GB of somewhere.

Configure both explicitly. The defaults are tuned for a laptop, not a container with a memory limit the kernel will enforce with an OOM kill.

-- Leave headroom below the container limit. DuckDB's accounting
-- does not cover every allocation, and an OOM kill gives you no
-- error message, just exit code 137.
set memory_limit = '12GB';        -- in a 16GB container
set threads = 4;                  -- match vCPU, not host cores

-- Spill target. Must be fast local disk with real capacity.
-- On Fargate this needs ephemeral storage sized deliberately.
set temp_directory = '/tmp/duckdb_spill';
set max_temp_directory_size = '80GB';

-- Watch what actually happened.
set enable_progress_bar = false;   -- noisy in container logs
pragma memory_limit;

The max_temp_directory_size setting matters more than it looks. Without it, a runaway join fills the container’s disk and you get a confusing write failure rather than a clear out-of-memory error. I set it to roughly five times the memory limit and treat hitting it as the signal that the job has outgrown one machine.

EXPLAIN ANALYZE tells you where memory went:

explain analyze
select
  o.customer_id,
  count(*)                    as orders,
  sum(o.amount_usd)           as revenue,
  max(o.order_ts)             as last_order
from read_parquet('s3://acme-lake/orders/dt=2026-05-*/*.parquet') o
where o.status = 'completed'
group by o.customer_id;
-- Look for HASH_GROUP_BY cardinality and any operator reporting
-- spill. A spilling GROUP BY usually means the grouping key is
-- higher cardinality than you thought.

The number I look for first is rows produced by the scan versus rows produced after the filter. If those are close, your Parquet predicate pushdown is not working and every downstream operator is carrying data it should never have seen.

Reading object storage properly

DuckDB’s httpfs extension reads S3, GCS, and Azure directly, with range requests and parallel prefetch. Done right, it skips whole files and whole row groups. Done wrong, it downloads everything.

install httpfs;
load httpfs;

-- Prefer the credential chain over hardcoded keys so the container
-- can use its task role.
create or replace secret s3_lake (
  type s3,
  provider credential_chain,
  region 'us-east-1'
);

-- hive_partitioning turns dt=... path segments into a real column,
-- which lets DuckDB skip files without opening them.
create or replace view orders as
select * from read_parquet(
  's3://acme-lake/orders/**/*.parquet',
  hive_partitioning = true,
  union_by_name = true          -- tolerate added columns over time
);

-- Now this reads only two days of files, and only three columns.
select customer_id, amount_usd, order_ts
from orders
where dt between '2026-05-08' and '2026-05-09'
  and status = 'completed';

Three things do the work there. Hive partitioning gives file-level skipping from the path. Naming columns instead of select * gives column-level skipping inside each file. And a filter on a column with Parquet row-group statistics gives row-group skipping. Drop any one and you pay for it in S3 GET requests and wall time.

The failure I hit most: union_by_name = true is necessary when schemas drift across partitions, and it also disables some pushdown optimizations because DuckDB must reconcile schemas. If your partitions have a stable schema, leave it off. If they do not, fix the upstream contract rather than paying this tax forever.

One writer, and what that means

A DuckDB database file supports one process writing at a time. Not one writer per table — one writer per file. Multiple readers are fine, and a reader can operate concurrently with a writer within a single process using MVCC, but two processes cannot both write.

This is not a limitation to work around. It is the boundary of the tool. Every attempt I have seen to build a shared, concurrently written DuckDB database has ended in a lock file, a retry loop, and eventually a Postgres migration.

The production shape that avoids the problem entirely: do not have a long-lived database file. Each job run creates an in-memory database, reads from object storage, writes to object storage, and exits.

"""Stateless DuckDB transform: in-memory DB, object storage in and
out, idempotent partition overwrite. No persistent .duckdb file,
so no lock contention and no state to back up."""

import duckdb
import os
from datetime import date

MEM_LIMIT = os.environ.get("DUCKDB_MEMORY_LIMIT", "12GB")
THREADS = int(os.environ.get("DUCKDB_THREADS", "4"))


def run(run_date: date) -> int:
    # ":memory:" — nothing persists, nothing locks.
    con = duckdb.connect(":memory:")
    con.execute(f"set memory_limit = '{MEM_LIMIT}'")
    con.execute(f"set threads = {THREADS}")
    con.execute("set temp_directory = '/tmp/duckdb_spill'")
    con.execute("set max_temp_directory_size = '60GB'")
    con.execute("install httpfs; load httpfs")
    con.execute("""
        create or replace secret s3_lake (
          type s3, provider credential_chain, region 'us-east-1'
        )
    """)

    dt = run_date.isoformat()

    # Write to a staging prefix first, then promote. A crashed job
    # never leaves a half-written partition where readers can see it.
    staging = f"s3://acme-lake/staging/daily_revenue/run={dt}"
    con.execute(f"""
        copy (
          select
            o.dt,
            c.country_code,
            count(*)                             as orders,
            sum(o.amount_usd)                    as revenue_usd,
            count(distinct o.customer_id)         as customers
          from read_parquet(
                 's3://acme-lake/orders/**/*.parquet',
                 hive_partitioning = true
               ) o
          -- Dimension is small; broadcast join happens implicitly.
          join read_parquet('s3://acme-lake/dim/customers/*.parquet') c
            on c.customer_id = o.customer_id
          where o.dt = '{dt}'
            and o.status = 'completed'
          group by o.dt, c.country_code
        )
        to '{staging}'
        (format parquet, compression zstd, partition_by (dt),
         overwrite_or_ignore true)
    """)

    rows = con.execute(
        f"select count(*) from read_parquet('{staging}/**/*.parquet')"
    ).fetchone()[0]
    if rows == 0:
        raise RuntimeError(f"no rows produced for {dt}; refusing to promote")

    con.close()
    return rows

Two production habits in there worth stealing. Staging then promoting means a failed run cannot be observed as a partial partition. And the row-count assertion before promotion catches the single most common silent failure in object-storage pipelines, which is a job that succeeds against zero input files because an upstream path changed.

Packaging and orchestration

DuckDB in production is a container, and a small one. A slim Python base plus duckdb and the extensions you need lands around 200 MB, starts in under a second, and has no cluster to provision. That startup profile is what makes it viable for jobs running every few minutes, where a Spark or Glue cold start is a meaningful fraction of the cycle — the same cold-start math I worked through in Glue vs EMR.

Pin the DuckDB version in the image. DuckDB’s storage format and occasionally its SQL behavior change between minor versions, and “it worked yesterday” after an unpinned rebuild is a bad afternoon. Pre-install extensions at image build time too; downloading httpfs on every cold start is both slow and a dependency on DuckDB’s extension endpoint being up.

For orchestration, treat it as any other containerized task. Airflow’s Kubernetes or ECS operators, a Dagster op, a Cloud Run job. Nothing special is required, which is the point. Size the task memory deliberately rather than accepting a default, and set the DuckDB memory limit from the container limit via an environment variable so the two cannot drift apart.

Where teams get this wrong

Sizing by raw table size instead of working set. A 2 TB table filtered to one day and three columns is a 4 GB job. Estimate the bytes DuckDB will actually materialize, not the bytes in the lake.

No memory limit set, running in a container. DuckDB defaults to a fraction of host memory, which inside a container may be far above the cgroup limit. The result is an OOM kill with exit code 137 and no log line explaining it. Always set memory_limit explicitly, below the container limit.

Building a shared persistent database file. Two writers will not work, ever, and the workarounds are worse than the migration. Persistent DuckDB files are for single-writer or read-only serving.

select * from a lake path. It defeats column pruning and turns a 3 GB read into 200 GB. This is the same mistake it is on BigQuery, with the same cause; see the SQL anti-patterns post.

Unpinned DuckDB version in the image. Minor releases have changed default behaviors and the on-disk format. Pin it, and upgrade deliberately with a test run.

Writing directly to the final partition path. A crash halfway through leaves a partition that readers will happily consume. Stage and promote, or write to a table format with atomic commits.

FAQ

How large a dataset can DuckDB actually handle?

On a 64 GB machine with fast NVMe, I have run scan-and-aggregate jobs over several terabytes of Parquet successfully, because the working set stayed small. On the same machine, a join producing a 300 GB intermediate failed. The limit is the largest blocking operator’s memory need, not the input size.

Is DuckDB safe for production, or is it a prototyping tool?

It is safe for the workloads described above. It has an extensive test suite, a stable release cadence, and I have had fewer production incidents from DuckDB than from any cluster-based engine I have operated. The risk is choosing it for a workload outside its envelope, not the software.

Can several jobs read the same DuckDB file at once?

Yes, in read-only mode. Open it with read_only=True and any number of processes can query it concurrently. This is the basis of the baked-into-a-container serving pattern. What you cannot do is have a second process write while others read.

Does DuckDB replace my warehouse?

No. It has no multi-user access control worth relying on, no concurrency for writes, no built-in governance, and no shared metadata layer. It replaces specific compute in your pipeline. The warehouse stays as the serving and governance layer.

How do I handle late-arriving data with idempotent writes?

Overwrite whole partitions rather than merging rows. Recompute the affected day from source and replace the partition atomically. If you genuinely need row-level merges over object storage, you want a table format with a transaction log — see Delta Lake vs Iceberg.

Should I use dbt with DuckDB?

Yes, and it works well. The dbt-duckdb adapter is mature enough for production, and it gives you the testing and documentation layer DuckDB lacks. My testing approach carries over unchanged from dbt testing strategy.

What this means for your pipelines

Audit your cluster jobs by working-set size, not by history. My experience across several platforms is that somewhere between a third and two thirds of “big data” jobs are processing under 100 GB of relevant bytes, and every one of those is a candidate for a single container that costs two percent as much and starts in a second. The savings are real, but the operational simplification is the bigger win: there is no cluster to patch, scale, or explain to a new hire.

Build them stateless. In-memory database, object storage in and out, explicit memory limits derived from the container limit, staging-then-promote writes, and a row-count assertion before anything is published. That shape has no lock contention, no state to back up, and no partial-write failure mode, which covers the three ways I have seen DuckDB pipelines actually break in production.

And know your exit condition before you start. Write down the working-set size and the largest join intermediate you expect. When a job starts spilling tens of gigabytes routinely, or when it needs concurrent writers, stop optimizing and move it. DuckDB is excellent precisely because it does not try to be a distributed system, and the moment you need one, no amount of tuning will make it into one.