Prompt and Scope
You own a local analytics task. DuckDB reads Parquet files and runs multi-table JOINs, GROUP BY operations, and window functions. As the data grows, the task either reports Out of Memory or creates many temporary files and times out. Explain your diagnosis order, parameter changes, SQL rewrites, and verification plan.
This scenario fits data-engineering, analytics-engineering, and embedded OLAP interviews. The answer should be evidence-led; “add memory” or “buy a larger machine” is not a diagnosis.
What the Interviewer Evaluates
- Whether you distinguish streaming execution from operators that retain large state.
- Whether you use plans, runtime profiles, and memory signals instead of guessing.
- Whether you understand threads, memory limits, spill directories, and insertion-order preservation.
- Whether you check temporary disk capacity, permissions, types, indexes, and JOIN-result explosion.
- Whether correctness, reproducibility, and regression performance close the tuning loop.
Clarifying Questions Before Answering
- Does the failure occur during scanning, JOIN, aggregation, sorting, or a window? Is it a DuckDB error or an operating-system kill?
- What are the DuckDB version, thread count,
memory_limit, temporary-directory path, and available disk space? - Is the input Parquet/CSV, what are the column types and partition layout, and can predicates be pushed down?
- Does the query contain high-cardinality GROUP BY, exact DISTINCT, wide JOINs, ORDER BY, windows,
list/string_agg, or PIVOT? - Can the result be processed in batches, pre-aggregated, approximated, or returned without preserving input order?
30-Second Answer Framework
I first identify the operator and resource that are exhausted, then establish a baseline with EXPLAIN ANALYZE, memory snapshots, and temporary-directory metrics. If a high-cardinality aggregate, JOIN, sort, or window creates blocking state, I reduce scanned rows and columns and fix filter or join conditions before lowering concurrency, setting a safe memory limit, and validating a spill directory. Finally, I compare row counts, key uniqueness, aggregates, and latency on fixed and full inputs so the optimization is both faster and semantically safe.
Step-by-Step Deep Dive
1. Separate memory failure from temporary-disk failure
Record the error text, process exit reason, peak RSS, DuckDB version, thread count, and query fingerprint. DuckDB reserves part of available memory as a limit, but an operating-system OOM, container cap, unwritable temporary directory, or full disk can look similar. Verify cgroup/container limits, capacity, and permissions before changing SQL.
2. Find the blocking operator in the plan
Use EXPLAIN to inspect join order and predicate pushdown, then EXPLAIN ANALYZE for actual rows, timing, and runtime state. Scans usually process chunks, while GROUP BY, JOIN, ORDER BY, windows, and exact DISTINCT retain hash tables, sort buffers, or frames. If a wrong join key multiplies rows, fix its semantics before discussing memory.
3. Reduce the working set before tuning resources
Read only needed columns, add partition and time filters early, and avoid materializing a full wide relation inside a subquery. Pre-aggregate reusable expensive facts by partition, and split an obvious many-to-many JOIN into steps with uniqueness checks. Do not replace an exact high-cardinality statistic with an approximation without an explicit error budget.
4. Set and verify threads, memory, and spilling
More threads can make several operators hold state simultaneously, so lower threads on a constrained host. Keep memory_limit below the container budget to leave system headroom; simply raising it can turn a DuckDB error into an OS kill. For spilling, use a writable, fast local disk with known capacity. A controlled experiment can use:
SET threads = 4;
SET memory_limit = '4GB';
SET temp_directory = '/var/tmp/duckdb_swap';
SET preserve_insertion_order = false;
EXPLAIN ANALYZE
SELECT customer_id, date_trunc('day', event_time) AS day, sum(amount) AS total
FROM read_parquet('events/*.parquet')
WHERE event_time >= DATE '2026-01-01'
GROUP BY customer_id, day;Disable preserve_insertion_order only when the business result does not depend on input order. Indexes and some intermediate states are not necessarily governed by the buffer manager, so memory_limit is not a universal hard guard.
5. Identify spill behavior and operator limits
Spilling supports many large GROUP BY, JOIN, sort, and window workloads, but it adds I/O. Chained blocking operators, huge list aggregates, string_agg, some holistic aggregates, and PIVOT may still require large indivisible state. If the temporary directory grows unexpectedly, inspect temp_directory, max_temp_directory_size, disk throughput, and cleanup. When spilling cannot help, return to batching or rewrite the SQL shape.
6. Close with result and performance regressions
Use a fixed input snapshot to compare total rows, primary-key sets, NULL distribution, group counts, checksums, and sample details before and after. Record peak memory, temporary bytes, scanned bytes, runtime, and failure rate. Test boundary dates, empty partitions, duplicate keys, and extreme cardinalities separately.
High-Quality Sample Answer
I classify the incident as operator state, configuration capacity, or external environment. First I preserve the version, query, input snapshot, container memory, and temporary-disk evidence, then use EXPLAIN ANALYZE to locate the peak. For a high-cardinality GROUP BY, an incorrect many-to-many JOIN, a sort, or a window, I check cardinality and predicate pushdown, reduce columns and rows, and pre-aggregate when appropriate; I do not hide join explosion by increasing memory_limit.
Next I lower thread concurrency to a measured safe level, leave system headroom below memory_limit, and put temp_directory on a disk with explicit capacity and permissions. I disable preserve_insertion_order only when order is not contractual. I measure peak memory, spill bytes, and runtime, and check that temporary usage stays within quota. For list, very large string, or PIVOT states that cannot be split effectively, I use staged results or reconsider the query shape.
Finally, I compare row counts, key uniqueness, aggregate checksums, boundary partitions, and NULL behavior on fixed and full data before release. This proves both that the OOM is gone and that the result semantics were preserved.
Common Mistakes
Only increasing memory_limit
Without checking the container cap, system headroom, and non-buffer-managed memory, the failure may move from DuckDB to the operating system.
Assuming every operator can spill
Confirm the specific operator and version. Some list, string, holistic aggregates, and PIVOT states still need indivisible memory.
Ignoring JOIN cardinality and predicate pushdown
Non-unique keys or late filters can create orders of magnitude more intermediate data; settings cannot repair a wrong query shape.
Checking success without result regression
Changing order preservation, splitting aggregates, or using approximate functions can change semantics. Compare fixed inputs and business checks.
Follow-Ups and Responses
Follow-up 1: Why can fewer threads help?
Concurrent operators can retain state and buffers at the same time. Fewer threads reduce the peak but usually reduce throughput, so choose from measured memory and completion-time curves.
Follow-up 2: Why can OOM persist when the temporary disk has space?
Not every state can be partitioned and spilled. Indivisible aggregates, oversized join state, or directory permissions and quotas can still fail; combine the plan, limits, and logs.
Follow-up 3: When may insertion-order preservation be disabled?
Only when the result and downstream consumers do not treat input order as a contract. Regression-test duplicate keys, ordering, and LIMIT behavior afterward.
Follow-up 4: How do you prove tuning preserved results?
Run the same input snapshot and compare row counts, primary-key sets, group counts, numeric checksums, NULL distributions, and boundary partitions. For approximate aggregates, state the error budget and obtain business approval.