Size the query, not the dataset, before you rent a cluster

A worksheet for deciding whether analytics fits one machine: bytes scanned after pruning, peak operator memory, concurrency and refresh window, with SQL to measure them.

Object storage Pruning One machine Result tables BI and notebooks Cluster partitions, columns spills to local disk past the thresholds
Pruned files feed one machine that writes result tables for BI, with a single branch to a cluster past the thresholds.
On this page
  1. Scan, memory, overlap and the refresh clock
  2. Measuring the first two on your own tables
  3. The worksheet, filled in for Coppergate
  4. When the lookback grows
  5. What you give up by staying on one box

Total data size is the first number quoted in any sizing conversation, and it says almost nothing about the hardware a query needs. “We have 2 TB of events” describes a storage bill. No query reads 2 TB. It reads the partitions its date filter keeps and the columns it names, and Parquet lets an engine skip the rest without downloading it.

So I size the query. For the heaviest few queries a team runs, I want four numbers, each compared against a line written down in advance. I’d bet most analytics workloads fit on one large machine with room left over, and the worksheet makes the ones that don’t easy to name.

Scan, memory, overlap and the refresh clock

The first is scanned GB per query after pruning: the compressed bytes in the files and columns the query touches. Divide by the read rate you measure from your bucket and you have a floor on runtime.

The second is the peak memory of the largest operator, usually one hash aggregate or the build side of one join. Scans stream, so the rest of the plan is small next to it.

Third, how many heavy queries run at the same moment. Dashboards that read a small result table don’t count. The job that builds the table does.

Last is the refresh window, meaning how stale the answer may be. A two-minute query is fine hourly and useless behind a filter someone is dragging.

Here’s the worksheet. The lines are mine, set for refresh jobs that write result tables, and they get stricter for anything a person queries live.

Number Line on one machine Coppergate today
Scanned GB after pruning Scanned GB ÷ read rate fits the latency budget 38 GB ÷ 2.7 GB/s = 14 s
Peak memory, largest operator Under 75% of RAM 21 GB of 64 GB (33%)
Concurrent heavy queries Peak memory × concurrency under 75% of RAM 1 refresh job
Refresh window Runtime under a quarter of the window 14 s of a 15 minute window

Coppergate Media doesn’t exist. I invented it to fill in the last column.

Measuring the first two on your own tables

DuckDB can read Parquet footers without reading data, so the first number costs a metadata query. This assumes files laid out as event_date=YYYY-MM-DD/part-*.parquet.

-- 1. Compressed bytes one query scans after partition and column pruning
SELECT
    count(DISTINCT file_name)                    AS files_read,
    round(sum(total_compressed_size) / 1e9, 1)   AS scanned_gb,
    round(sum(total_uncompressed_size) / 1e9, 1) AS decoded_gb
FROM parquet_metadata('s3://coppergate-lake/events/*/*.parquet')
WHERE path_in_schema IN ('account_id', 'title_id', 'region',
                         'device_type', 'watch_seconds', 'completed')
  AND CAST(regexp_extract(file_name, 'event_date=(\d{4}-\d{2}-\d{2})', 1) AS DATE)
      >= current_date - 90;

-- 2. Rough peak memory of the largest aggregate: groups x bytes per group
SELECT
    count(*)                       AS group_count,
    round(count(*) * 96 / 1e9, 1)  AS est_peak_gb
FROM (
    SELECT DISTINCT account_id, title_id, region, device_type
    FROM read_parquet('s3://coppergate-lake/events/*/*.parquet',
                      hive_partitioning = true)
    WHERE event_date >= current_date - 90
);

-- 3. Check the estimate: cap memory just above it, then rerun the real query
SET memory_limit = '24GB';

The 96 is my guess at bytes per group for four keys, two aggregate states and hash table overhead. Use your own arithmetic. For a join, count the rows on the smaller side and multiply by the width of the columns you carry through.

Step three keeps the estimate honest. DuckDB spills large aggregates and joins to its temp directory when it hits the limit, so an underestimate shows up as a query that still finishes but takes several times longer.

For an Iceberg table, measure the files that iceberg_metadata() in DuckDB’s Iceberg extension lists for the current snapshot. A glob over the bucket would also count files that only old snapshots reference, and Iceberg keeps those around until you expire them.

The worksheet, filled in for Coppergate

Coppergate holds 2.1 TB of viewing events. Its heaviest dashboard query looks back 90 days and reads six columns, which comes to 38 GB scanned. The big operator groups by account, title, region and device type, about 220 million groups, and peaks at 21 GB. On one 64 GB machine in the same region as the bucket, reading at 2.7 GB/s, the scan alone takes 14 seconds. I’ll treat that floor as the runtime.

Nobody waits 14 seconds on a dashboard, so the query runs every 15 minutes and writes a result table. BI and notebooks read that. This is why the concurrency row says 1: twenty people opening the dashboard are twenty reads of a few megabytes.

When the lookback grows

Then someone asks for more history. Take the pessimistic case, where scanned bytes and group count both grow in step with the lookback.

At about 205 days, peak memory reaches 48 GB, which is 75% of the 64 GB box. The answer is a bigger machine. A cluster doesn’t enter into it yet.

At two years the query scans 308 GB and peaks near 170 GB. On a 256 GB machine that’s 66% of RAM and roughly two minutes of runtime, comfortably inside a quarter of an hourly window.

The memory line on that 256 GB machine sits at 192 GB, and the query crosses it at roughly 820 days. Past that point I’d stop resizing. You can let it spill, and for a nightly job I would. For something refreshed hourly, a plan that depends on local disk is one busy day from missing its window, and that’s where distributing the aggregate starts to pay for its operational cost.

So Coppergate’s line reads: one machine until the lookback passes two and a quarter years.

What you give up by staying on one box

A single node is a single point of failure. For a refresh job that writes result tables, I accept that. If the machine dies, the dashboard is one cycle stale and the job reruns elsewhere, because the data lives in object storage and the machine holds nothing. For anything users query live, I wouldn’t.

Concurrency is the row I’d expect to break first. Ten analysts running ad hoc 20 GB aggregates at once is 200 GB of demand, and no pruning helps with that. A large, unpredictable third number can justify a cluster by itself.

DuckDB reached 1.0 last June with a stable storage format, so I’m comfortable putting a production refresh job on it. I’d fill the worksheet in again each time a new dashboard gets its own refresh job, since every one of those adds to the third row.