A data agent successfully queries a large export, then runs out of memory while handing the result to the charting code. Switching the query engine did not fix the final materialization step.

The decision is not simply whether pandas or DuckDB can read a large CSV. Ask what must be held at once, what intermediate state the calculation creates, and how much data the next step actually needs.

This guide is for developers building recurring local reports with agents. It compares a few specific execution patterns rather than declaring a universal winner.

Methodology and selection criteria

We reviewed the pandas scaling guide, DuckDB workload-tuning documentation and its pandas export reference. The examples are proposed design and review exercises. We did not benchmark either tool, measure memory savings or process a production dataset.

pandas describes its analytics structures as in-memory and notes that intermediate copies can make even datasets smaller than available memory difficult. Its scaling guide recommends loading less data and explains that chunking works best when chunks require little coordination. pandas scaling guide.

Our selection criteria are therefore the operation's state, the size of the output, the environment's memory and scratch-disk limits, and whether the final answer can be reconciled independently.

Define the report before selecting the engine

Imagine a fictional event export. The user wants daily totals for a handful of event types, not every original event loaded into a notebook.

Write down the requested output columns, reporting timezone, inclusion rules and expected output grain. Estimate whether the final result should be tens of rows, thousands of rows, or almost as large as the source. Do not confuse a convenient preview of the first few rows with a complete report.

Also identify the operations. Selecting fields and transforming each row are different from computing a global sort, deduplicating across files or joining two large datasets. The latter can require shared state that a simple independent-chunk loop does not supply.

Before optimizing execution, settle the parsing and missing-value rules described in our CSV reconciliation guide. A smaller wrong answer is not an improvement.

Chunking needs a correct merge rule

For our daily-total example, a chunked approach can accumulate partial totals and combine them by day. The agent must still define exactly what those partial states contain and ensure the accumulated state stays manageable.

Averages expose a common mistake. Suppose the first chunk contains values 10 and 20, while the second contains only 90. Their means are 15 and 90. Averaging those means yields 52.5, but the mean of all three original values is 40.

The correct merge for this simple example preserves the sum and count: 120 divided by 3. This is manually checked arithmetic on invented values, not an observed library failure.

Other operations need other state. Summing per-chunk distinct counts double-counts values present in several chunks. A global median cannot generally be recovered from the chunk medians alone. Ask the agent to explain the merge rule before accepting the word streaming as a memory strategy.

For large or complicated shared state, consider a query engine rather than building an increasingly fragile custom reducer.

DuckDB moves some pressure to disk, not into nowhere

DuckDB documents larger-than-memory processing through disk spilling, including grouping, joining, sorting and windowing. Its tuning guide also lists limitations: combinations of blocking operators and some aggregate states can still produce out-of-memory failures. DuckDB workload tuning.

For our fictional report, that makes DuckDB worth evaluating when the work naturally fits a query and cannot be handled with small independent chunks. It is not permission to ignore the execution environment.

Allocate an approved scratch location and check available disk capacity. Decide what happens if scratch space fills or the process exceeds its time budget. Preserve the inputs and emit a diagnostic; do not silently sample rows or drop fields to make the operation finish.

If the report includes a join, inspect its intended relationship before adding resources. Our join-cardinality guide addresses accidental row multiplication, which can harm both memory use and the meaning of the result.

Keep the final materialization small on purpose

DuckDB's df() method converts a query result into a pandas DataFrame. That is a useful integration point, but it changes where the result lives. DuckDB export to pandas.

Our inference from that conversion and pandas' in-memory model is straightforward: a query engine that processes the source successfully does not guarantee that the complete returned table will fit in a DataFrame.

For a small daily summary, aggregate before conversion and hand only the summary to plotting code. If the user needs a full row-level export, deliver it through an appropriate file-output workflow instead of loading it all solely to write it again. Verify that the chosen export path itself does not introduce an unnecessary full copy.

A LIMIT can be useful for exploration, but it changes the result. Label a preview as a preview and never present a truncated output as the complete requested dataset.

Limitations and the proposed acceptance run

There is no single CSV-size threshold that selects the right tool. Data types, distinct-key counts, operations and simultaneous workloads all matter. Disk-backed execution also has storage and performance costs. This article offers no numerical speed or cost advantage.

On a controlled fixture, compare the final row count and totals against a hand-checkable answer. Include uneven chunks, repeated keys across chunk boundaries and a result that is deliberately larger than the reporting use case needs.

Then record peak resource observations from an actual trial before scaling up: memory, scratch use, runtime and final output size. If those measurements are unavailable, mark them as unknown rather than estimating a production guarantee from documentation.

Browse the skills directory for implementation help, but ask each workflow where it materializes the data. The best choice is the one that computes the full intended answer within an understood resource plan and delivers only what the next step needs.