Query pruning cost savings: how partitioning turns a $5 query into 3 cents

Quick answer

Scan priced engines bill at roughly $5 to $6.25 per TB read, so cost is decided by how much data the engine can skip. Partitioning on a column used in filters lets it eliminate entire directories: a query over one day of a year of data reads 1/365th of the table. Clustering and sorting narrow row group statistics so the engine skips blocks within files. Together these routinely turn a full 1 TB scan costing $5 into a 6 GB scan costing about $0.03. The three rules are partition on your most common filter, keep files between 128 MB and 1 GB, and select columns explicitly instead of using star.

The billing model of a scan priced query engine is brutally simple: bytes read times a rate. It does not care how many rows you returned, how long the query ran, or how clever the SQL was. Which means every optimization that matters reduces to one question: can the engine prove it does not need to read this data?

The four levels of skipping

Level	Mechanism	Typical reduction
Partition pruning	Skip whole directories by partition key	10x to 500x
Column pruning	Read only referenced columns	3x to 20x
Row group skipping	Min and max statistics per block	2x to 50x
Page and dictionary skipping	Finer grained statistics	1.2x to 3x

These multiply. A table that is partitioned by day, stored columnar, and sorted on a secondary filter column can deliver a combined reduction of several hundred times against a naive full scan. That is the difference between a $5,000 monthly query bill and a $20 one.

Partitioning, done right

Partition on the column that appears in the WHERE clause of most queries, which for event data is almost always a date. A table of 365 daily partitions where the typical query asks for one day reads 0.27 percent of the data. At 1 TB total that is 2.7 GB, about 1.4 cents at $5 per TB, against $5 for a full scan.

The failure mode is over partitioning. Partitioning by hour and by customer id across 5,000 customers produces 43 million directories a year, most holding a few KB. The engine then spends its time listing and opening files, query planning takes longer than execution, and per request charges accumulate. A good target is partitions holding at least 1 GB of data each. If a partition key would produce partitions smaller than about 128 MB, it belongs in clustering rather than partitioning.

Partition scheme	Partitions/year	Avg partition size (1 TB table)	Verdict
By year	1	1 TB	Too coarse, no pruning
By month	12	83 GB	Good for long range queries
By day	365	2.7 GB	Usually right
By hour	8,760	114 MB	Borderline, only for very large tables
By hour and customer	43,800,000	~23 KB	Pathological

Clustering and sorting for the second dimension

You can only partition on one or two dimensions before the directory count explodes, but queries filter on more. Clustering, sorting, or z ordering data within partitions narrows the min and max statistics recorded per row group, so the engine can skip blocks without reading them. A table sorted by user id within each daily partition lets a query filtering on a single user skip perhaps 95 percent of row groups in that partition. Combined with the partition pruning above, 1 TB becomes 135 MB, well under a cent.

Maintaining sort order costs compute on write. On a warehouse with automatic clustering this appears as a serverless credit line; on a lake it is a periodic compaction job. Budget for it explicitly, because a clustering service left running on a high churn table can cost more than the queries it accelerates. Check the ratio: if clustering maintenance costs $400 a month and saves $200 in scans, turn it off.

The cheapest fix: stop selecting everything

Column pruning only works if the query names its columns. A single SELECT star against a 40 column table reads all 40, discarding 37. In dashboards and BI tools this is endemic, because the tool generates the query. On the 1 TB table above, the difference between selecting 3 columns and selecting all of them is roughly 13 GB versus 180 GB, $0.07 versus $0.90 per execution. Run that 3,000 times a month and it is $210 versus $2,700.

Equally, LIMIT does not reduce scan cost on most engines. A query with LIMIT 10 over an unpartitioned table still scans the table to find the rows. Analysts exploring data with LIMIT queries can generate enormous bills while believing they are being careful.

Measuring what you actually skip

Every scan priced engine reports bytes scanned per query. Pull that into a table alongside the logical size of the tables referenced, and compute a skip ratio. A query reading 900 GB of a 1 TB table has a skip ratio of 10 percent and is a target. Rank by total bytes scanned per month, not per query, so a cheap query running 50,000 times gets the attention it deserves. Also cap runaway spend with per query and per workgroup scan limits, which stop a malformed exploratory query from scanning 40 TB before anyone notices.

File layout, partitioning, and lifecycle rules all live in the Terraform that defines your lake, and C3X prices those resources from the resource catalog at review time. Pair this withcolumnar formats andstorage tiering for the complete picture.

FAQ

How much can partition pruning save?

Often 10x to 500x on bytes scanned. A table with 365 daily partitions where the typical query asks for one day reads about 0.27 percent of the data, so a 1 TB table yields a 2.7 GB scan costing roughly 1.4 cents instead of $5. Combined with columnar formats and sorting the reduction can exceed 1,000x.

Can you over partition a table?

Yes, and it is a common failure. Partitioning by hour and customer across 5,000 customers creates around 43 million directories a year holding a few KB each, so the engine spends its time listing and opening files while per request charges accumulate. Target partitions of at least 1 GB, and use clustering for finer dimensions.

Does SELECT star cost more money?

Substantially, on columnar storage. Naming three columns of a 40 column table might scan 13 GB where selecting everything scans 180 GB, about $0.07 versus $0.90 per execution at $5 per TB. Run 3,000 times a month that is $210 against $2,700, and BI tools generate star queries by default unless configured otherwise.

Does LIMIT reduce query cost?

Usually not. On most scan priced engines a query with LIMIT 10 over an unpartitioned table still scans the whole table to find matching rows, then discards the rest. Analysts exploring unfamiliar data with LIMIT queries often generate large bills while believing they are being careful, which is why scan limits are worth enforcing.

Is automatic clustering worth the cost?

Only when the scan savings exceed the maintenance cost. Clustering services consume compute on every write to keep sort order, which on a high churn table can exceed what the accelerated queries save. Compare the monthly clustering charge directly against the reduction in bytes scanned and turn it off where the ratio is unfavourable.

How do I find the worst queries?

Every scan priced engine reports bytes scanned per query. Compare that against the logical size of the referenced tables to get a skip ratio, then rank by total bytes scanned per month rather than per execution, so a cheap query running 50,000 times surfaces. Add per query and per workgroup scan limits to cap runaway exploratory queries.

What to do next

Design data layouts with cost in view. C3X prices your Terraform resources before deploy. Start with the quickstart.