Projection Pushdown
Read only the columns the query needs. The cheapest optimisation a columnar format offers, and the one SELECT * throws away.
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 query needs two columns out of eight. Why did the reader fetch all eight, and what did that actually cost?
The same people as predicate pushdown, plus a privacy reviewer who would rather the analytics query never touched the email column at all. Projection is the one optimisation that is simultaneously a cost lever and a data-minimisation control (Data Minimization).
The unit here is the column chunk: the contiguous, independently addressable run of one column's values inside one row group. Column pruning is possible at all because a columnar file stores each column's values together and records where each chunk starts, so a reader can fetch byte ranges rather than files (Parquet Internals).
SELECT * in exploration, then SELECT * in the view built on top of the exploration, then SELECT * in the model built on the view. Nobody decided this; each step was a reasonable convenience, and the query at the end of the chain reads every column of every row it touches, forever, on a schedule.
The widest columns are almost never the ones anyone aggregates. A URL, a referrer, a user agent or a raw JSON payload can dominate the stored bytes of a table while appearing in no dashboard, and SELECT * fetches all of them (Why Analytical Data Compresses).
- The widest columns are almost never the ones anyone aggregates. A URL, a referrer, a user agent or a raw JSON payload can dominate the stored bytes of a table while appearing in no dashboard, and
SELECT *fetches all of them (Why Analytical Data Compresses). - A view defined with
SELECT *propagates that everywhere. A downstream model asking for two columns from the view still causes the underlying scan to read everything, unless the engine can push the projection through the view definition — and it often can, right up until a function or aDISTINCTblocks it. - A high-cardinality identifier column — an event id, a UUID — compresses to nearly nothing, meaning it stores at close to its raw width. It is dead weight in every scan that includes it and useful in almost none (Dictionary, Run-Length, Delta and Bit Packing).
- Selecting a struct or JSON column to reach one field inside it reads the whole nested value in most readers unless nested-field pruning is supported, which is not uniform (CSV, JSON and Their Limits).
- A row-oriented source cannot prune columns at read time at all. Projection over Avro or CSV means the reader still walks every field of every record and discards what it does not need (Parquet vs Avro).
- The BI tool sends
SELECT *and filters in its own layer, so the query the engine sees bears little resemblance to the tile the analyst configured (The Metrics Layer).
What is actually happening
- A columnar file stores values column by column and writes a footer describing where each column chunk of each row group begins and how long it is. A reader that knows which columns the query needs issues range requests for exactly those chunks and never transfers the rest (The Parquet Read Path, Row vs Column Storage).
- The planner performs the same kind of rewrite as with predicates: it computes the set of columns required by the whole plan — projections, predicates, join keys, grouping keys — and hands that set to the scan operator. What is not required is not requested.
- The saving is not proportional to the number of columns, and this is the part people get wrong. It is proportional to the encoded *width* of the columns you skipped, which depends on their type, their cardinality and how well they encode. Skipping two narrow low-cardinality columns saves almost nothing; skipping one wide string column can dominate (Dictionary, Run-Length, Delta and Bit Packing).
- Projection composes with predicate pushdown by multiplication, not addition: pruning selects which row groups are touched, projection selects which column chunks inside them are fetched, and the bytes read are the intersection (Predicate Pushdown).
- Column pruning also reduces work above the scan. Fewer columns means less decoding, narrower rows through every operator, less memory per batch, and less data crossing the exchange if the columns would have survived to a join (Vectorized Execution, The Shuffle).
- The mechanism has no analogue in a row store beyond covering indexes, which is exactly why analytical systems went columnar and why
SELECT *is a materially different act in an analytical query than in an operational one (Columnar Execution).
What the reader actually fetches
A row group in a columnar file is not one blob. It is a sequence of column chunks, each a contiguous byte range whose offset and length are recorded in the file footer. A reader that needs two of eight columns reads the footer, computes two byte ranges, and issues two range requests. The other six chunks are never transferred, never decompressed and never decoded.
The in-repo model makes the asymmetry concrete. Its synthetic clickstream has eight columns whose encoded widths differ by more than two orders of magnitude: an identifier that compresses not at all, two very wide string columns, a couple of two-byte categorical columns that dictionary-encode to almost nothing, and the revenue measure most dashboards actually want.
Under a one-day predicate the model reports 38,158,890 bytes for SELECT * and 2,038,355 bytes for SELECT country, revenue — same rows, same partition, same files. Two columns out of eight is not a quarter of the bytes, and the reason is entirely visible in the column list below: the three columns nobody aggregates account for the great majority of the width.
This is also why the advice is unusually unambiguous. There is no scenario where reading columns you will discard is the right choice; there is only a spectrum of how much it costs, and on a wide table it is the difference between a cheap query and an expensive one.
- part-0.parquet :: event_id~548k values · 1 file · skipped
- part-0.parquet :: event_time~548k values · 1 file · skipped
- part-0.parquet :: user_id~548k values · 1 file · skipped
- part-0.parquet :: country~548k values · 1 file · read
- part-0.parquet :: device~548k values · 1 file · skipped
- part-0.parquet :: url~548k values · 1 file · skipped
- part-0.parquet :: referrer~548k values · 1 file · skipped
- part-0.parquet :: revenue~548k values · 1 file · read
SIMULATED — from src/de/sim/layout.ts. Under the model's declared widths and encoding factors, the two projected columns account for roughly five percent of the row's encoded width; six of the eight chunks are never transferred. Change the projection to * in the model and the partition, the rows and the files are identical while the bytes are not.
Where projection stops working
Projection pushdown fails quietly in four situations, and only one of them is the query author's fault. The first is the famous one: SELECT *, usually inherited from a view rather than typed by the person running the query. The second is nested data — a struct, a map or a JSON blob where the query wants one field and the reader fetches the whole value.
The third is a format that cannot do it. Avro, CSV and JSON Lines are row-oriented; naming two columns tells the engine what to materialise, not the reader what to fetch, so the storage cost is unchanged. Teams who land raw data in Avro and query it directly often conclude that projection "does not help much" — and for that table they are right, for a reason that is about the format rather than about the idea (Parquet vs Avro).
The fourth is a blocking construct: an opaque function over the whole row, a SELECT * inside a DISTINCT or a set operation, or a downstream consumer that genuinely needs everything. In those cases the full column set is required and the plan is correct to fetch it.
The comparison below is deliberately mundane, because the fix is mundane. What makes it worth stating is how far the consequence travels: a view definition is written once and read by every model built on top of it, for years.
CREATE VIEW stg_events AS SELECT * FROM raw.events; then every downstream model selects the three columns it needs from stg_events.
CREATE VIEW stg_events AS SELECT event_date, event_time, user_id, country, revenue FROM raw.events; with the wide `url` and `referrer` columns exposed by a separate view for the small number of consumers that need them.
The engine can often push a downstream projection through a simple view, and often cannot — a DISTINCT, a set operation, a window function or an opaque expression in the view definition all block it. The explicit list makes the narrow read a property of the definition rather than a property of whether the optimiser managed it today. It also makes adding a wide column to the source a deliberate, reviewable act instead of a silent cost increase everywhere (Data Contracts).
1-- Reads every column chunk of every surviving row group.2SELECT * FROM events WHERE event_date = DATE '2024-03-14';3 4-- Reads two column chunks. Same rows, same files, same partition.5SELECT country, revenue FROM events WHERE event_date = DATE '2024-03-14';6 7-- Reads three: the join key is required even though it is not returned.8SELECT c.name, sum(e.revenue)9FROM events e JOIN customers c ON c.user_id = e.user_id10WHERE e.event_date = DATE '2024-03-14'11GROUP BY c.name;12 13-- May read the whole nested column to extract one field, depending on14-- whether this engine and reader support nested-field pruning.15SELECT payload.currency, sum(revenue)16FROM events17WHERE event_date = DATE '2024-03-14'18GROUP BY payload.currency;The third query is the one worth internalising: the required column set is everything the *plan* touches — projections, predicates, join keys and grouping keys — not everything the SELECT list returns.
Which columns actually cost you
The intuition to build is that a table's bytes are not evenly distributed across its columns, and the distribution is predictable from cardinality and type. A column with a handful of distinct values dictionary-encodes to a small code per row and then run-length encodes further if the data is sorted. A column with a distinct value per row cannot be encoded away by anything (Dictionary, Run-Length, Delta and Bit Packing).
That gives a rule that transfers even though no number does: the widest columns in an analytical table are usually free-text or identifiers, and they are usually the ones no aggregate touches. Which is another way of saying that the cost of SELECT * concentrates precisely in the columns nobody wanted.
The bars below are the in-repo model's relative widths, and they are the shape rather than the magnitude. Read them as an ordering: identifiers and free text dominate; timestamps and foreign keys are mid-weight; categorical dimensions and numeric measures are nearly free. If your table has a raw JSON payload column, it is almost certainly at the top of this chart.
The largest single contributor. Encoding helps a medium-cardinality string only modestly, and nothing about it is aggregatable.
Nulls are cheap to encode, but the non-null values are wide. Frequently included by SELECT * and used by nobody.
Unique values cannot be dictionary-encoded away, so this column stores at close to its raw width and appears in almost no query.
Mid-weight. event_time delta-encodes well when sorted within the partition and poorly when not (Clustering and Sort Order).
The column the dashboard exists for, and one of the cheapest in the table.
A few distinct values become a small dictionary code per row. Grouping keys are usually the cheapest columns you can read.
Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.
Relative and unitless, derived from the declared widths and encoding factors in src/de/sim/layout.ts. The teaching is the ordering, not the bars: cost concentrates in identifiers and free text, and the columns a dashboard actually aggregates are typically the cheapest in the table.
How to build it
Most important first.
- Name the columns. In every model, every view, every scheduled query and every extract. This is not a style preference in analytics — it is a change to how much data is physically read (SQL Transformations).
- Never define a view or a model with
SELECT *over a wide table. A view is a definition that outlives the person who wrote it, and its projection becomes the projection of everything built on it. - Put wide, rarely-queried payload columns in a separate table keyed to the fact, rather than carrying them inside it. This is the layout equivalent of naming your columns and works even for readers that cannot prune nested fields (Fact Tables).
- Order the model so the columns that are always needed and the columns that are rarely needed are separable — by table, or at minimum documented, so that a downstream author knows what a wide column costs (Dataset Documentation).
- Confirm in the plan that the scan lists the columns you expect. Where the engine prints the projected column list, a diff against expectations is a fast and durable regression test (Reading EXPLAIN ANALYZE).
- Treat column selection as an access-control decision as well as a cost one. A query that never projects the PII column cannot leak it, and a model that never selects it does not propagate it downstream (PII in Pipelines).
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.
- Projection pushdown never changes the result of the columns you did select. Like predicate pushdown, its absence is invisible in the output.
- Column pruning is guaranteed to be available in a columnar format with a footer; it is guaranteed to be *unavailable* in a row-oriented format, where the reader must walk every field regardless.
- Nested-field pruning inside a struct or a map is not guaranteed anywhere. Treat it as an engine and format capability to verify, not a property of columnar storage.
- Nothing guarantees the column set the planner derives is minimal. A blocking operator or an opaque function over the row can force the full set, correctly and quietly.
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 is a plan projection assertion on scheduled queries: capture the column list the scan reports and fail the build when it grows unexpectedly. It catches a
SELECT *reintroduced into a view and a new wide column silently joining every downstream read. - It misses the case where the projection is minimal and the columns are wrong — a query selecting exactly two columns, one of which is the wrong revenue field, is optimal and incorrect (Two Dashboards, Two Numbers).
- Pair it with a column-level lineage check: which downstream models actually consume each column. A column that no consumer reads and every scan fetches is pure cost and a candidate for a separate table (Column-Level Lineage).
- Projection is a read-path property and does not affect how fresh the data is.
- It changes what freshness you can afford. A dashboard query that reads two narrow columns can refresh far more often than the same query reading a wide payload, at the same budget (Cost vs Freshness).
- For a query with a strict latency target, projection is usually the largest single reduction available without changing the layout, because it requires no rewrite of any file.
- Adding a column to a wide table is free for queries that name their columns and immediately expensive for every query that does not.
SELECT *converts a harmless schema addition into a platform-wide cost increase (Schema Evolution). - Removing a column breaks
SELECT *consumers in a visible way and named-column consumers in a visible way too — this is one of the few schema changes that is loud in both directions (Breaking Schema Changes). - Reordering columns is safe for name-matched readers and catastrophic for position-matched ones, which is a reason to prefer formats and readers that match by name and to say so in the contract (Data Contracts).
- Nothing to recover: an over-projected query returned correct results and read too much. The fix is the recovery.
- Where the fix is splitting a wide column into a side table, that is a genuine table rewrite and a backfill, and it needs the usual staging-and-publish treatment (Atomic Publish).
- If a wide column was included in extracts that have already been distributed, the cost is not the only concern — a projection mistake that shipped PII downstream is a governance incident, and deleting the extract is harder than not creating it (Deletion Requests).
What can go wrong
SELECT *in a view definition, inherited by every model built on that view.- A wide JSON or struct column read whole to extract one field, because nested pruning is not supported by that reader.
- A row-oriented source where projection saves nothing at the storage layer, and the team assumes it did because the SQL names two columns (Avro).
- A BI tool issuing a much wider query than the tile it renders.
- A join that appears to need a column and does not — the join key is required, the payload columns of the build side are not, and an over-broad projection carries them through the exchange (Broadcast Joins).
- The mitigation failing: splitting a wide column into a side table and then finding that every query joins it back anyway, so the join cost replaced the scan cost (Star Schema).
- "
SELECT *is fine in analytics — the engine only reads what it needs." It reads what the *plan* needs, andSELECT *makes the plan need everything. This is the single most expensive piece of folklore in the field. - "Two of eight columns means a quarter of the bytes." Column widths differ by more than an order of magnitude within one table. The saving is proportional to encoded width, not to column count.
- "Compression makes the extra columns cheap." Compression helps low-cardinality columns enormously and unique identifier columns not at all, so the widest and least useful column is often the one that compresses worst (Why Analytical Data Compresses).
- "Projection is a micro-optimisation." It is frequently the largest single reduction available to a query author, and it is free — no rewrite, no new infrastructure, no layout change.
- "The BI tool handles this." The BI tool sends SQL. Look at what it actually sends before assuming it is narrow.
- Projection is a minimisation control that costs nothing. A pipeline that never selects the email column never copies it into the warehouse, never carries it into an extract and never has to delete it from one (Data Minimization).
SELECT *in an ingestion or staging model is how PII arrives in an analytical store that was never scoped for it, without any decision being recorded anywhere (PII in Pipelines).- Column-level access control is only meaningful if consumers project deliberately. A masked column that every query selects is a column every query is asking permission to see (Row and Column Security).
Operating it
- The projected column list in the plan, per scheduled query, tracked over time (Reading EXPLAIN ANALYZE).
- Bytes scanned per row returned. A query reading a large number of bytes per output row is either over-projecting or under-pruning, and the plan says which (Scan Cost).
- Per-column storage size for wide tables — which columns dominate the table is rarely what people assume, and it is directly queryable from format metadata.
- Column-level lineage coverage: which columns have a downstream consumer at all. The unread ones are the ones to move out of the hot table (Column-Level Lineage).
- At 10x rows, cost scales with the bytes you chose to read, so the two queries grow by the same multiple from very different bases — and the absolute gap between them widens accordingly.
- At 10x columns — a table that accumulated width over years, which is the normal direction of travel —
SELECT *consumers degrade continuously without any query being edited (Data Platform Anti-Patterns). - At high concurrency, projection is a memory story as well as an I/O one: wider batches per operator means fewer concurrent queries fit in the same cluster (Distributed Query Execution).
- Bytes fetched from storage, which for a projected scan is the sum of the encoded widths of the selected columns over the surviving rows — not a fraction based on column count.
- Decode CPU, which scales with the columns actually materialised and is the part people forget when they reason about scan cost purely in bytes.
- Exchange bytes, if the unnecessary columns would have survived to a join or a sort. Projection pushed below a join removes them from the network as well as from storage (The Shuffle).
- Interaction with pruning: the two multiply, so the worst case is a query with neither and the best case is far better than either alone (Predicate Pushdown).
- Naming columns everywhere costs some churn: every added column requires editing the models that should carry it. That churn is the mechanism by which somebody consciously decides a column belongs downstream, which is a feature rather than a tax (Data Contracts).
- Splitting wide payload columns into a side table buys much cheaper scans on the hot table and costs a join for the queries that need the payload. It is the right trade when the payload is read rarely and the fact is read constantly.
- Plan assertions in CI buy early detection and cost a coupling to your engine's plan output, which changes across versions.
Predicate and projection pushdown
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.
SELECT country, sum(revenue) FROM events WHERE event_date = '2026-08-25' GROUP BY country
SELECT country, sum(revenue) FROM events WHERE date(event_time) = '2026-08-25' GROUP BY country
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.
- FORMAT-SPECIFICColumn pruning is a property of columnar formats with a footer describing chunk offsets, such as Parquet and ORC. In Avro, CSV or JSON Lines the reader must traverse every field of every record, so naming columns reduces what is materialised and not what is read from storage.
- ENGINE-SPECIFICPruning of fields inside structs, maps and JSON columns varies widely between engines and readers. Some push a nested path into the reader; others fetch the whole nested value and project afterwards, which turns a narrow query into a wide scan.
- SIMULATEDThe byte figures come from
src/de/sim/layout.ts, whose per-column widths and encoding factors are declared in the file and pinned byscripts/de-sim.test.ts. They exist to show that the saving tracks encoded width rather than column count; your table's ratios will differ.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.