CostGENERALFORMAT-SPECIFICENGINE-SPECIFICSIMULATED

Scan Cost

What a query actually has to read, and why column selection and partition pruning are the two cheapest fixes in the entire domain.

What actually happensHow to build itCan I trust it?▶ Run the lab

Who needs this, what one row is, and why the obvious build breaks

Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.

The question

Two queries return the same three numbers. One reads a single day of two columns and the other reads a year of everything. What in the SQL decides which one you wrote?

Who needs this

Anyone who issues a query and expects to be told the truth about what it did — analysts, BI tools, scheduled extracts, reverse-ETL jobs and notebooks. None of them see bytes; they see a result and a duration. The platform's job is to make the difference between the two queries visible *before* the month ends, because the SQL that reads everything looks exactly as reasonable as the SQL that reads almost nothing (Who Actually Consumes This Data).

What one row is

The unit here is the column chunk within a partition — the smallest thing a columnar reader can decide to fetch or skip. Not a row, not a table, not a file: a reader that wants two columns from one day fetches the chunks for those two columns in the files belonging to that day, and everything else in the dataset is never touched. Every scan optimisation is an argument about how few of those chunks the reader can be persuaded to open (Parquet Internals).

The obvious build

Write SELECT * FROM events WHERE date(event_time) = '2025-03-04', look at the result, and move on. It is correct, it is readable, it is what every tutorial shows, and on a small table it is genuinely the right thing to write. The cost of this habit is invisible until the table is large, and by then it is written into several hundred saved queries and a dozen dashboards.

Why it breaks

SELECT * fetches the widest columns in the table — a URL, a referrer, a serialised payload — for every row that matches, even though the aggregate at the end mentions two of them. The reader has no way to know that the projection was accidental (Projection Pushdown).

How it breaks with real data
  • SELECT * fetches the widest columns in the table — a URL, a referrer, a serialised payload — for every row that matches, even though the aggregate at the end mentions two of them. The reader has no way to know that the projection was accidental (Projection Pushdown).
  • Wrapping the timestamp in a function — date(event_time) = ... — means the planner cannot map the predicate onto the partition values. It reads every partition, produces the correct answer, and looks identical to the version that prunes (Partition Pruning).
  • A BETWEEN on a column that is not the partition key prunes nothing at the partition level. It may still skip row groups if the data happens to be sorted, and it will not if the data happens not to be (Clustering and Sort Order).
  • A view flattens a join across three tables, and a dashboard filters the view. The filter is applied after the join in the plan, so all three tables are read in full to produce a hundred rows (Predicate Pushdown).
  • A SELECT DISTINCT or an ORDER BY on a high-cardinality column turns a scan into a scan plus a full shuffle, and the shuffle is charged separately from the read (The Shuffle).
  • Somebody partitions by a high-cardinality key to make pruning better. Bytes scanned fall and the query gets slower, because the reader now opens hundreds of thousands of tiny objects to assemble the same rows (Partition Cardinality).
Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

  • A columnar reader decides what to fetch in three passes, each one cheaper than the one after it. First it eliminates whole partitions using directory or manifest values. Then it eliminates whole files or row groups using the min/max statistics in the footer. Only then does it fetch the column chunks it still needs (The Parquet Read Path).
  • Those three filters compose multiplicatively. Skipping most of the partitions and most of the columns is not "a bit less work" than doing neither — the reader ends up touching the intersection of two small sets, which is far smaller than either (Partition Pruning, Projection Pushdown).
  • Partition elimination happens in the planner, before any data is read, and it depends entirely on the predicate being expressible in terms of the partition column *as stored*. Any transformation applied to that column in the WHERE clause — a cast, a date function, a concatenation, a join to derive it — takes the predicate out of the planner's reach (Query Optimizers).
  • Row-group elimination depends on the min/max statistics being *selective*, which depends on how the data is ordered within the file. Sorted by the filtered column, the statistics are tight and most groups are skipped; unsorted, every group's range spans the whole domain and none can be skipped, even though the statistics are present and correct (Clustering and Sort Order).
  • Column pruning is the only one of the three that never fails and never depends on the data. If a column is not named, its bytes are not fetched. It is the single cheapest optimisation available anywhere in this domain and it is defeated by one character (Columnar Execution).
  • None of this applies to a row-oriented format. Reading one field of an Avro or CSV record still means walking every record — which is why the format choice decides whether scan optimisation is available at all (Parquet vs Avro).

Two queries that answer the same question

The pair below returns the same three numbers. They differ in two places — the projection list and whether the date predicate is wrapped in a function — and those two places decide, between them, almost the whole cost of the query.

The first version is what a person writes when they are exploring, and exploring is exactly when it should be written. The problem is that exploratory SQL gets saved, scheduled, embedded in a dashboard and copied into four other queries, and at no point does anything object.

What makes the second version cheap is not cleverness. It is that both of the reader's elimination mechanisms are left switched on: the projection tells the format which column chunks to skip, and the predicate on the stored partition column tells the planner which directories to skip. Turn either one off and the other still works; turn both off and the query reads the table.

What each version leaves the reader able to skip
Predicate the planner cannot see through
The partition column is derived at query time from another column. The planner has a predicate over an expression, not over a partition value, so it plans a read of every partition and applies the filter afterwards. The result is correct. The plan says it will touch the whole table, and nobody reads the plan.
Predicate over the stored partition column
The comparison is between a stored column and a literal, which the planner can evaluate against the partition metadata. It eliminates the directories that cannot match before opening a single file, and then the projection list eliminates the column chunks it does not need inside the ones that remain.

Elimination is a planning-time operation over metadata. It requires a predicate expressed in terms the metadata is stored in. Any transformation of the partition column inside the WHERE clause moves the predicate out of that space, and there is no error, no warning and no difference in the result — only a difference in how much was read to produce it (The Planner: Enumerating Ways to Answer).

The same result, two very different reads
1-- Reads every column, every partition.
2-- date(event_time) is a function call, so the planner cannot match it
3-- against the partition values and eliminates nothing.
4SELECT
5  country,
6  count(*)          AS events,
7  sum(revenue)      AS revenue
8FROM events
9WHERE date(event_time) = DATE '2025-03-04'
10GROUP BY country;
11 
12-- Same answer. Two column chunks, one partition.
13-- event_date is the stored partition column, compared to a literal,
14-- so elimination happens in the planner before any data is fetched.
15SELECT
16  country,
17  count(*)          AS events,
18  sum(revenue)      AS revenue
19FROM events
20WHERE event_date = DATE '2025-03-04'
21GROUP BY country;

Neither query says SELECT *, and the first one still reads every column — because count(*) and a GROUP BY do not restrict the projection when the predicate forces a full read of every partition. The projection saving is real but it is applied to a row set that was never narrowed.

The two cheapest fixes there are, and how they compound

SIMULATEDPartition, file and column counts are outputs of the declared model in src/de/sim/layout.ts over a synthetic dataset whose schema and column widths are written down in that file. They are not measurements of any real system, and a real dataset with different widths and different sort order will eliminate differently.

Column selection and partition pruning are the highest-return changes in this domain, and the reason is that they multiply. A reader that skips most of the partitions and most of the columns touches the intersection of two small sets — which is much smaller than either restriction alone would suggest, and much smaller than people expect when they reason about them one at a time.

The layout below is the modelled clickstream from src/de/sim/layout.ts: a year of daily partitions over a synthetic dataset of two hundred million rows and eight declared columns, with the widest column being a URL that almost no analytical query needs. The query is the second one above. The model reports partitions read, files read, rows scanned, bytes scanned and metadata requests, and the test suite in scripts/de-sim.test.ts pins the compounding property directly.

The same model makes the two failure cases concrete. Wrap the date predicate in a function and the partitions-read count goes from one to three hundred and sixty-five while the query text barely changes. Partition by user_id instead of by date and the bytes fall while the file count explodes into the millions, at which point the model warns that the cost has moved into per-request metadata work rather than into reading data — which is the same warning a request-priced object store gives you in the form of a bill.

A year of daily partitions, read by a one-day query projecting two columns
SELECT country, sum(revenue) FROM events WHERE event_date = DATE '2025-03-04' GROUP BY country
  • events/event_date=2025-03-02/one day of the modelled dataset · 3 files · skipped
  • events/event_date=2025-03-03/one day of the modelled dataset · 3 files · skipped
  • events/event_date=2025-03-04/one day of the modelled dataset · 3 files · read
  • events/event_date=2025-03-05/one day of the modelled dataset · 3 files · skipped
  • events/… 361 further daily partitions …the rest of the year · 3 files · skipped
1 of 5 shown paths are read.

Two independent restrictions apply here — one of three hundred and sixty-five partitions, and two of eight columns. They compose, and that composition is what the model's test asserts. Change the predicate to date(event_time) = … and the first restriction disappears while the SQL still looks correct.

Where scan cost actually hides

Ask a team which of their queries is most expensive and they will name a dashboard. The heaviest readers on most platforms are not dashboards: they are scheduled things that nobody classifies as queries — a nightly export to a vendor, a reverse-ETL sync, a materialisation job, a model training extract, a data quality suite that counts nulls across every column of every table.

These share three properties that make them expensive and invisible at the same time. They run on a schedule, so their cost is multiplied by frequency. They are usually full-table readers, because incremental extraction requires a watermark that someone has to design. And they are attributed to a service account rather than to a person, so they sit in an unlabelled block on every cost report.

The weights below order the readers by how much they typically contribute, and the notes name what changes each one. The point of the ordering is where the human sits: the analyst writing ad-hoc SQL is real, is worth teaching, and is usually not the largest line — which matters, because cost programmes that begin by policing analysts spend their credibility on the wrong target.

  • Every one of these is attributable to a dataset, which is why dataset-level attribution finds them and person-level attribution does not (Cost Attribution).
  • The fix for the top three rows is the same fix — read only what changed — and it is a correctness problem before it is a cost one (Incremental Processing).
  • A quality suite is worth its scan cost when its checks are ones a consumer would notice failing, and is pure overhead when it asserts properties nobody depends on (Data Tests).
Who is actually reading the bytes
Scheduled extracts and reverse-ETL syncs

Full-table reads multiplied by their schedule, attributed to a service account. Moved by giving each one an incremental contract — a watermark column and an explicit projection — rather than by making the table smaller.

Dashboard refreshes against wide tables or views

Multiplied by the number of tiles and the auto-refresh interval. Moved by putting a narrow pre-aggregated model between the tool and the raw layer, which caps what the generated SQL can read.

Nightly transformation jobs re-reading history

Charged as reads even though nobody thinks of a transformation as a query. Moved by making the model incremental; unaffected by anything done to the consumer side.

Data quality suites scanning every column

A null-rate check over every column of every table is a full scan of the platform on a schedule. Moved by scoping checks to columns a consumer actually depends on.

Ad-hoc analyst queries

Individually careless and collectively modest, because humans do not run queries on a five-minute schedule. Moved by defaults and by review of anything that gets saved, not by policing exploration.

Notebook and training extracts

Wide by nature — a feature extract legitimately wants many columns. Moved by materialising the extract once and reusing it rather than by narrowing it.

Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.

Relative weights for a modelled platform, published to establish the ordering rather than to predict a share. The transferable claim is that scheduled non-human readers usually sit above human ones, and that this is invisible on any report that groups by user.

Product detail — verify current documentation

Every major analytical platform exposes some form of query history with the bytes or slots a statement consumed, and every one names it differently and retains it for a different period. Two things are worth confirming in current documentation before building on it: how long the history is retained, since it must outlive your longest-period consumer to be useful for retirement decisions, and whether the recorded figure is bytes read from storage or bytes billed, which differ when caching or result reuse is involved.

How to build it

Most important first.

  • Name your columns. Always, including in views, including in CREATE TABLE AS, and especially in anything a BI tool generates against. This is the cheapest change with the largest effect in the module (Projection Pushdown).
  • Keep the partition predicate in terms of the stored column. Filter event_date = '2025-03-04' rather than date(event_time) = '2025-03-04'; store the partition column explicitly if the natural one needs deriving (Partitioning).
  • Partition on the predicate that nearly every query carries — usually date — and use clustering or sort order for the second-most-common one. Partitioning is for elimination; sorting is for statistics (The Partitioning Decision).
  • Read the plan before optimising. Every engine will tell you how many partitions and files it intends to touch, and that number is the measurement that matters; runtime is a noisy proxy for it (The Planner: Enumerating Ways to Answer).
  • Put a narrow serving table between wide raw data and repeat consumers. A dashboard that reads a pre-aggregated model at the grain it displays cannot over-scan, whatever SQL the tool generates (Data Marts, Model Layering).
  • Treat scheduled extracts as first-class consumers. They are usually the largest readers in the platform and the least examined, because nobody thinks of a nightly export as a query (Incremental Extraction).

What this actually promises

Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.

  • Column pruning is guaranteed by the format for any columnar file: an unnamed column's bytes are not fetched. This is a structural property, not an optimisation the planner may decline (Parquet).
  • Partition pruning is not guaranteed. It happens when the planner can match the predicate to the partition values, and there is no promise anywhere that it will — which is why a query that stops pruning after a refactor raises no error (Query Optimizers).
  • Row-group skipping is guaranteed to be *correct* and not guaranteed to be *effective*. Statistics never cause wrong results; they simply fail to eliminate anything when the data is unsorted (Parquet Internals).
  • Nothing guarantees that two runs of the same query read the same amount. The table grows, files are compacted, clustering degrades under appends, and the plan changes with statistics (File Compaction).

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this
  • The check is a plan assertion on the heaviest queries: for each scheduled query, record the partitions the planner intends to read and assert it is far below the total. It catches the whole class of regressions where a refactor, a view change or a new derived predicate silently disables pruning.
  • It misses anything unscheduled — the ad-hoc query a human writes once and runs across a year of data — and it misses over-projection entirely, since reading two columns from one partition and eight columns from one partition both look like one partition.
  • Add a second assertion on the projected column count against the columns the query actually references in its output and predicates. That catches SELECT * behind a view, which is the form that survives review because the outer query looks narrow (Data Tests).
Freshness
  • Scan cost and freshness pull against each other through file size. Landing data more frequently makes it visible sooner and produces more, smaller files, which raises the metadata cost of every subsequent read (File Size and the Small-Files Problem).
  • Compaction resolves that tension but does not remove it: it is a rewrite that must run somewhere, and until it has run the recent partitions are the badly-laid-out ones — so the freshest data is reliably the most expensive to query.
  • Pre-aggregated serving tables cut scan cost sharply and add a hop, so the consumer reads something that is one refresh interval staler than the raw layer. That is the trade being made, and it should be stated to the consumer rather than discovered (Cost vs Freshness).
When the schema or meaning changes
  • Adding a column to a wide table raises the cost of every SELECT * reader at once. Nothing in the change looks like a cost decision, and no consumer is notified (Schema Evolution).
  • Renaming or retyping the partition column breaks pruning for every reader whose predicate referenced the old form. The queries keep working, which is what makes it expensive rather than loud (Breaking Schema Changes).
  • Changing the partition key is a rewrite plus a silent behaviour change for every existing reader. Treat it as a migration with a consumer list, not as a layout tweak (Impact Analysis).
How to re-run this safely
  • Scan cost is fully reversible: a narrower projection or a fixed predicate takes effect on the next execution and nothing has to be rebuilt. This is the most forgiving cost driver there is.
  • Layout changes are not reversible in the same way — repartitioning writes the dataset again, and reverting writes it a third time. Decide once, with the query log in front of you (Physical Data Layout).
  • When a heavy query has already run and the concern is repetition rather than the single execution, the fix is at the scheduler: reduce the frequency, or make the extract incremental so subsequent runs read only what changed (Incremental Processing).

What can go wrong

Failure modes
  • A predicate that stops pruning after a harmless-looking refactor, with no error and no alert — the characteristic failure of this lesson.
  • A view that hides a SELECT * under a narrow-looking outer query, so review sees five columns and the engine reads forty.
  • Partition cardinality raised to improve pruning, moving the cost from bytes to metadata requests where no bytes-based report can see it (Partition Cardinality).
  • A serving table built to reduce scans that is itself rebuilt in full every night, so the read saving is paid for twice over in write cost (Compute Waste).
  • The mitigation failing: a plan assertion that runs against a small test dataset, where every plan touches one partition and the assertion passes unconditionally.
  • A cost control that caps bytes per query, which turns an expensive query into a failed query and pushes the analyst to run it in four pieces (Data Platform Anti-Patterns).
Misreads
  • "The query is fast, so it is cheap." Parallelism buys speed by using more machines on the same bytes. A wide unpruned scan can return quickly and still be the largest reader in the platform (Distributed Data Processing).
  • "SELECT * is fine, the engine only reads what it needs." That is true of the *projection the planner derives*, and SELECT * derives every column. There is nothing left to prune.
  • "We partitioned the table, so queries prune." Only queries whose predicate matches the partition key prune. A date filter on a country-partitioned table eliminates nothing (Partitioning).
  • "More partitions means better pruning." Up to a point, then the file count and the planner's listing work overtake the saving, and the curve turns (Partition Cardinality).
  • "The BI tool generates efficient SQL." It generates SQL against whatever you exposed. Expose a wide raw table and it will read a wide raw table, every refresh, for every user (Data Marts).

Operating it

How you see it in production
  • Bytes scanned per query, ranked, with the caller and the query text. The distribution is long-tailed almost everywhere, and the head is usually scheduled rather than human (Scan Cost).
  • Partitions read divided by partitions available, per scheduled query, tracked over time. A step towards one is a pruning regression (Partition Pruning).
  • Columns projected versus columns in the table, per query. The SELECT * detector, and the only one that catches it behind a view.
  • Files read per query alongside bytes read. A high ratio of files to bytes is the small-file pathology showing up as a scan problem (File Size and the Small-Files Problem).
What changes at 10x and 100x
  • At 10x rows, projection and pruning move from optimisations to preconditions — the unpruned version of the query stops completing within any interval a consumer will wait (An Index Scan Is Not Automatically Faster).
  • At 10x columns, projection dominates and pruning matters less. Wide tables punish SELECT * in proportion to their width, and the widest columns are usually the least queried ones (Why Analytical Data Compresses).
  • At 10x consumers, the same tables are scanned by ten times as many readers, so a single narrow serving model repays itself ten times over. This is the case where building a data mart is a cost decision rather than a modelling one (Data Marts).
  • At 100x partitions, the planner's own work of listing and eliminating becomes measurable, and elimination that used to be free starts to cost (Partition Cardinality).
What drives cost here
  • Bytes fetched from storage, which is the product of rows surviving partition and row-group elimination and the width of the projected columns. Both factors are decided in the query text.
  • Metadata and object requests, counted separately from bytes and driven by file count rather than data size — the reason a query that reads almost nothing can still be slow and chargeable.
  • Shuffle bytes for anything that sorts, distincts or joins on a high-cardinality key, which is charged on top of the read and is not reduced by projection.
  • Repetition: the same scan on a schedule is the scan multiplied by the schedule, and a small careless query on a short interval outweighs a large careful one that runs weekly.
What this approach costs
  • Naming columns makes queries longer and breaks when a column is renamed — which is the point, because a break at the query is cheaper than a silent widening. SELECT * in exploratory work is fine; the rule is about anything that runs more than once.
  • Pre-aggregated serving tables cut scan cost and add a model to maintain, a refresh to schedule and a grain that constrains the questions the consumer can ask afterwards (Grain: What Does One Row Represent?).
  • Optimising layout for the dominant query pattern makes every other pattern worse. There is one partition key, and choosing it for date means user-level lookups scan broadly (The Partitioning Decision).

Cost lab — the metered dimensions

Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.

Cost lab — the metered dimensions
Data platforms are billed on bytes scanned, requests made, and time a cluster was awake. This lab moves the first two and shows nothing in currency.
Layout
Bytes scanned / query
36.4 MBsim
Bytes scanned / day
7.1 GBsim
Requests / day
19Ksim
Against the tidy layout
19xsim
Bytes scanned per day, against a date-partitioned, compacted, narrowly-projected read18.7x
Metered dimensionThis layoutMoved by
Bytes scanned7.1 GBsim/dayPartition pruning and column projection, multiplied together. `SELECT *` alone defeats the cheaper of the two.
Requests19Ksim/dayFile count. A partition written 96 times holds 96 objects until something compacts them, and every one is listed and opened.
Cluster timenot modelledOwned by Observability & Performance. A second latency model here would disagree with that one, and one of the two would be wrong on any given page.
SELECT * defeats column pruning entirely, which is the single cheapest optimisation a columnar format offers.
Each partition is written 96 times, so it holds 96 files where 1 would do. Compaction rewrites them into fewer, larger files — at the cost of the rewrite itself.
The largest cost reduction available to most platforms is not a cheaper engine. It is a layout that lets the engine skip, and a query that does not ask for columns nobody reads.
SIMULATEDCLOUD-SPECIFICWhich dimensions are metered, and at what rate, differs by provider and by product. What transfers is that a layout decision moves a metered quantity by orders of magnitude — so this lab reports the quantities and never a price.

Where this applies

Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.

  • GENERALPartition elimination, statistics-based skipping and column projection exist in every columnar analytical engine. The names differ and the plan output differs, but a reader that can skip is a reader that must be given a predicate it can use.
  • FORMAT-SPECIFICAll of this depends on a columnar, self-describing format with per-chunk statistics — Parquet or ORC. Against CSV or Avro, projection saves parsing but not reading, because a row-oriented reader must walk every record to reach one field.
  • ENGINE-SPECIFICWhich predicates a planner can push into the scan varies: some engines see through simple casts, some do not, and support for pushing a predicate through a join or a view differs sharply. The rule that survives every engine is to check the plan rather than assume.
  • SIMULATEDThe partition and column counts quoted in the layout section come from the model in src/de/sim/layout.ts over a declared synthetic clickstream, not from any measurement. They exist to make the compounding concrete; the dataset and its column widths are written down in that file and can be inspected.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databasequery-planner-internalspartitioning-and-shardingshould-i-add-an-index
Backendserialization-cost
Observabilityscan-vs-index-performancedisk-io-performanceprofiling-basics
Cloudstorage-fundamentalsobject-storagecost-per-service
Domains that do not exist yet
  • — Distributed Systems owns why the elimination decision has to be made by a planner with a global view rather than by each worker independently.