DuckDB Interview Questions: Parquet Pushdown, Query Plans, and Memory Limits

A DuckDB query can return the right answer while doing much more work than necessary. Two Parquet files may contain identical rows but offer very different opportunities to skip data. A small result does not prove a small scan, and a memory setting does not guarantee that the entire process stays below that number.

This guide follows an original, locally executed DuckDB 1.5.2 experiment. We compare two physical layouts, inspect their row-group statistics, and connect the plans to a careful memory diagnosis. Official facts are linked at first use; the dataset, observations, and interview exercises are our own. There are no employer-specific question or production benchmark claims.

Use Optimize a SQL query plan tree as a follow-up. The goal is to explain what the evidence supports before proposing an optimization.

What does Parquet pushdown actually mean?

Official behavior: DuckDB’s Parquet documentation describes projection and filter pushdown. Projection limits the columns needed by a query. A filter can be evaluated in the scan and may allow parts of a file to be skipped using available statistics.

These are related but different benefits. A query may avoid a wide text column even when its predicate overlaps every row group. Conversely, a selective predicate may exclude whole groups while still requiring several columns from the groups that remain. Say which reduction you expect and why the file layout permits it.

For the exercise, the predicate is id >= 1000 AND id < 2000. We count matching rows and sum an amount field. A deliberately unused payload column lets us inspect column selection separately from the range filter. The expected answer is easy to derive independently: one thousand IDs, with amounts cycling from zero through nine.

Build two files from the same logical data

We used a local Python connection, one DuckDB thread, and memory_limit='512MB'. The setting was read back as 488.2 MiB, reflecting the unit conversion. This small fixture is not an out-of-memory stress test.

The following SQL reproduces the dataset in a disposable working directory:

SET threads = 1;
SET memory_limit = '512MB';

CREATE TABLE events AS
SELECT i::BIGINT AS id,
       (i % 10)::INTEGER AS amount,
       repeat('x', 80) AS payload
FROM range(245760) AS t(i);

COPY (
  SELECT * FROM events ORDER BY id
) TO 'sorted.parquet'
(FORMAT PARQUET, ROW_GROUP_SIZE 122880,
 COMPRESSION ZSTD);

COPY (
  SELECT * FROM events ORDER BY hash(id)
) TO 'shuffled.parquet'
(FORMAT PARQUET, ROW_GROUP_SIZE 122880,
 COMPRESSION ZSTD);

The hash ordering supplies a deterministic permutation in the tested version, not a promise that every DuckDB release uses the same hash implementation. Each file contained two groups of 122,880 rows in our run. We also compared both directions with EXCEPT ALL; the multiset difference was empty. This establishes identical logical rows, including duplicate multiplicities, rather than relying only on equal file counts.

The files were different sizes: 288,787 bytes sorted and 853,893 bytes shuffled. That is another observed layout consequence for this synthetic data. The repeated payload is unusually compressible, so these byte counts should not be used to estimate a real customer dataset.

Predict the result before reading the plan

Run the same query against each file:

SELECT count(*) AS n, sum(amount) AS total
FROM read_parquet('sorted.parquet')
WHERE id >= 1000 AND id < 2000;

Replace only the filename for the second run. Both returned n = 1000 and total = 4500. The range is half-open, so ID 2000 is excluded. There are one hundred complete cycles of amounts zero through nine, each summing to 45.

That independent calculation matters. An optimization that accidentally changes the upper bound or filters on the wrong field may produce a faster query with a different meaning. Keep a correctness assertion beside the performance investigation, especially when rewriting joins or aggregations.

Next, ask what the result alone cannot tell you. It does not report how many row groups were examined, whether the payload was read, how many physical bytes came from disk, or how much work was satisfied from cache. Those questions require additional evidence.

Inspect the row-group ranges

Read the metadata for the filter column:

SELECT row_group_id, row_group_num_rows,
       stats_min_value, stats_max_value
FROM parquet_metadata('sorted.parquet')
WHERE path_in_schema = 'id'
ORDER BY row_group_id;

The original run produced these ranges:

Layout and group	Minimum–maximum ID
Sorted, group 0	0–122879
Sorted, group 1	122880–245759
Shuffled, group 0	0–245759
Shuffled, group 1	8–245758

Only the first sorted group overlaps [1000, 2000). Both shuffled groups overlap it. Therefore the statistics can rule out one entire sorted group, while they cannot rule out either shuffled group using this ID range alone.

This is a statement about pruning eligibility from the observed statistics. We did not instrument physical disk reads or report an exact number of skipped bytes. Min/max overlap also does not prove that a group contains a matching row; it only means the range cannot exclude the group. That distinction becomes clearer with sparse or irregular values.

Official context: DuckDB’s file-format performance guide discusses row-group sizing and parallelism. Our two-group fixture is designed for inspection, not to establish an optimal row-group size. Smaller groups can expose finer ranges but also change metadata and execution overhead; larger groups alter the available work units.

What did EXPLAIN and EXPLAIN ANALYZE show?

Official distinction: EXPLAIN ANALYZE executes the query and reports runtime information. Plain EXPLAIN describes the planned execution without supplying the same observed runtime evidence.

In our plain plan, the Parquet scan contained the ID filter and projected amount. The unused payload did not appear as a projected output column. The ID field remained necessary to evaluate the predicate even though it was not passed onward as the aggregate’s value column.

The plain plan estimated approximately 49,152 rows at the scan output. The analyzed run reported 1,000 output rows there. That difference is useful evidence about the estimate for this predicate; it is not a correctness failure. The final aggregate produced one row containing the count and sum.

Do not read the 1,000 scan-output rows as “only 1,000 physical rows were touched.” Likewise, Total Files Read: 1 does not imply that every byte in the file was read. Operator output cardinality, file participation, group pruning, and physical I/O measure different things.

The saved local profiles include timings, but this article does not claim a speedup ratio from them. A tiny single run is sensitive to cache state, startup, scheduling, and timer resolution. The defensible result is that the files have identical data, different range statistics, and plans that place the predicate in the scan.

How would you design a fair performance follow-up?

Keep the query semantics and logical data fixed, and vary one layout choice. Record the DuckDB version, thread count, memory settings, file sizes, compression, and row-group metadata. If you compare elapsed time, define whether the test measures a warm cache, a cold cache, or an operational workload where both occur.

Run enough repetitions to describe variability rather than selecting the best number. Include result verification after each change. If remote storage is involved, separate network request latency and transferred bytes from local decoding and aggregation work. A local Parquet experiment cannot establish behavior for a remote object store it never contacted.

An original interview extension is to filter on amount instead of id. Because amount cycles through ten values, sorting by ID may not create useful amount ranges. Predict the metadata effect before running it. This tests whether you understand why the first layout helped, rather than assuming sorted data is universally faster for every predicate.

Another extension is a query that needs the payload. The column-selection benefit changes, even if the ID range is identical. The correct explanation ties the plan to the requested columns and predicate, not to the filename or a general claim that Parquet is fast.

Why can memory pressure persist despite a limit?

Official guidance: DuckDB’s out-of-memory performance guide explains that some allocations fall outside the buffer manager governed by the memory limit. It discusses reducing threads and leaving room outside that budget. Therefore a configured limit should not be described as a hard ceiling on total process memory.

The troubleshooting guide also distinguishes a DuckDB out-of-memory exception from an operating-system termination. DuckDB supports spilling, but that does not mean every query and allocation can always complete using disk instead of memory.

For an original diagnostic scenario, suppose a range query succeeds but a later join and aggregation fails. First identify whether the engine reported an allocation failure or the host killed the process. Then inspect the plan and intermediate cardinalities. A many-to-many join can expand the working set far beyond the number of source rows you expected.

Do not immediately increase the memory setting. Check the container or machine budget, other allocations in the host application, concurrent work, and the size of the result you are materializing in Python. Fetching a very large result into another object can create a separate memory burden even if the SQL operators themselves complete.

Reduce the right working set without changing the answer

A useful repair might select fewer columns, apply a valid filter earlier, or aggregate before a join. Each has a semantic condition. Pre-aggregating orders by customer can be correct for a customer-level total, but it can destroy information needed for an order-level filter or a distinct-count calculation.

Write down the grain on each side of a join. If one customer has three events and four tags, joining both collections directly may create twelve combinations. A final DISTINCT is not a reliable explanation of the intended metric and may add more work. Determine whether the query needs combinations, existence, or separate aggregates.

Treat memory configuration as a measured follow-up to that reasoning. Reducing concurrency can trade throughput for a smaller concurrent working set. Spilling requires usable temporary storage and has a performance cost. Neither is a substitute for fixing unintended row multiplication.

Our fixture did not trigger an OOM, test spilling, or benchmark these repairs. They are documentation-supported diagnostic guidance and original follow-up scenarios. Keeping that limit visible lets readers reuse the executed Parquet evidence without mistaking it for a broad memory-performance certification.

Five practice questions to connect plans and correctness

Practice question	Review goal
Optimize a SQL query plan tree	Explain the evidence behind a rewrite
Reason About Composite Join Keys and Predicate Placement	Preserve semantics while filtering
Illustrate SQL Join Results with Duplicate Keys	Predict intermediate row counts
Design and Implement a Mini SQL Query Engine	Connect operators to execution
Find the Most Frequent IP Addresses in a File Too Large for Memory	Bound memory with an explicit strategy

Continue with the query-plan exercise. For each proposed improvement, state the unchanged answer, the expected reduction in work, and the observation that would confirm or reject your explanation.

Sources and Further Reading

• DuckDB: Reading and writing Parquet — projection and filter pushdown.
• DuckDB: File-format performance — layout and row groups.
• DuckDB: EXPLAIN ANALYZE — runtime plan evidence.
• DuckDB: Out-of-memory issues — memory budgeting and configuration.
• DuckDB: Out-of-memory errors — error categories and limitations.