Definition
- Projection pushdown – also called column pruning, it is an optimization where the Catalyst Optimizer determines the minimal set of columns the query needs and tells the data source to read only those.
- Columnar benefit – in formats like Parquet and ORC each column is stored separately, so skipping a column means never reading its bytes from disk.
- Whole-plan analysis – Catalyst does not look only at the final SELECT; it collects every column referenced in projections, filters, joins, group-by, and sorts to compute the minimal set.
- Composability – projection pushdown and predicate pushdown work together: one skips columns, the other skips rows, and combining them can reduce a scan to a tiny fraction of the file.
The transactions ledger at the National Bank of India has a hundred fields per record, but a report only needs the account id, the amount, and the date. Without column pruning, the clerk photocopies every field of every record and you discard ninety-seven columns yourself. With projection pushdown, you tell the clerk "only copy these three fields," and because the ledger is stored field by field in separate books, the clerk simply never opens the other ninety-seven books.
Understanding the Concepts
What is projection pushdown?
Projection pushdown, also called column pruning, tells the data source to read only the columns the query needs. In columnar formats where each column is stored in its own contiguous region of the file, this means the reader skips entire column chunks.
from pyspark.sql import SparkSession, functions as F
spark = SparkSession.builder.appName("ColumnPruning").getOrCreate()
# This table has 100 columns, but the query needs only 3
txn = spark.read.parquet("/lake/silver/transactions")
result = txn.select("account_id", "amount", "txn_date") \
.filter(F.col("amount") > 10000) \
.groupBy("account_id") \
.agg(F.sum("amount").alias("total"))
result.explain(True)
== Physical Plan ==
*(2) HashAggregate(keys=[account_id], functions=[sum(amount)])
+- *(1) FileScan parquet
ReadSchema: struct<account_id:bigint,amount:decimal(18,2),txn_date:date>
PushedFilters: [IsNotNull(amount), GreaterThan(amount,10000)]
The ReadSchema lists only three columns. The other ninety-seven are never read from disk.
How does Catalyst determine the needed columns?
Catalyst analyzes the entire logical plan, not just the final SELECT. It walks every operator and collects every column that influences the result.
# Even though we select all columns initially, Catalyst prunes
# based on what actually reaches the output
wide_df = txn.select("*") # all 100 columns
# Downstream: only account_id and amount are used
result = wide_df.groupBy("account_id").agg(
F.sum("amount").alias("total")
)
result.explain(True)
== Physical Plan ==
*(2) HashAggregate(keys=[account_id], functions=[sum(amount)])
+- *(1) FileScan parquet
ReadSchema: struct<account_id:bigint,amount:decimal(18,2)>
Despite the initial select("*"), only two columns are read because only two survive to the output.
How does column pruning work in Parquet?
Parquet stores data in row groups, and within each row group, columns are stored in separate column chunks. To read a column, the reader seeks to its chunk and reads just that.
# A 100-column Parquet file might look like this on disk:
# Row Group 1:
# Column chunk: account_id (2 MB)
# Column chunk: amount (3 MB)
# Column chunk: txn_date (1 MB)
# Column chunk: branch_code (1 MB)
# ... 96 more column chunks (150 MB total)
#
# Reading 3 columns: 6 MB read, 150 MB skipped
The I/O savings are proportional to the fraction of columns skipped, which for wide tables is enormous.
How does projection pushdown compose with predicate pushdown?
Predicate pushdown skips rows; projection pushdown skips columns. Together they reduce a scan to a tiny fraction of the file.
# Both pushdowns active: skip columns AND skip rows
result = txn.select("account_id", "amount") \
.filter(F.col("txn_date") >= "2026-10-01") \
.filter(F.col("amount") > 50000)
result.explain(True)
== Physical Plan ==
*(1) FileScan parquet
ReadSchema: struct<account_id:bigint,amount:decimal(18,2),txn_date:date>
PushedFilters: [GreaterThan(amount,50000), GreaterThanOrEqual(txn_date,2026-10-01)]
PartitionFilters: [txn_date >= 2026-10-01]
Projection pruned the columns. Predicate pushdown and partition pruning pruned the rows. The scan reads a fraction of the total data.
What happens with row-based formats?
Row-based formats like CSV and JSON interleave fields on each line. The reader must read the entire line to find any field, so column pruning cannot avoid reading unwanted fields from disk.
# CSV: must read every field on every line, then discard unwanted columns
csv_df = spark.read.csv("/lake/landing/transactions.csv", header=True)
result = csv_df.select("account_id", "amount")
# The reader still reads all fields per line
# Column pruning happens AFTER the read, not during it
This is a key reason why analytical data is stored in columnar formats like Parquet.
How does nested column pruning work?
Modern Spark (3.1+) supports pruning individual fields from nested structs.
# Schema: account_id, details: struct<name, address, phone, email>
result = txn.select("account_id", "details.name")
result.explain(True)
== Physical Plan ==
*(1) FileScan parquet
ReadSchema: struct<account_id:bigint,details:struct<name:string>>
Only the name field within the struct is read. The sibling fields address, phone, and email are skipped. This requires spark.sql.optimizer.nestedSchemaPruning.enabled to be true (default in Spark 3.x).
How do you verify column pruning is happening?
Call explain(True) and check the ReadSchema in the FileScan node. It lists only the pruned columns.
result.explain(True)
# Look for ReadSchema in the Physical Plan
# If it lists fewer columns than the source table, pruning is active
# For JDBC, check the generated SQL query:
# Without pruning: SELECT * FROM transactions
# With pruning: SELECT account_id, amount FROM transactions
What should a senior engineer know?
- Projection pushdown is automatic in Catalyst for DataFrames and Spark SQL. RDD code does not benefit.
- The savings are most dramatic on wide tables in columnar formats.
- Always store analytical data in Parquet or ORC to benefit from both column and row pruning.
- Use
explain(True)to verify the ReadSchema before and after optimization. - Nested column pruning is a newer feature; check your Spark version supports it.
# Senior optimization: combine column pruning, predicate pushdown,
# and partition pruning for maximum reduction
result = (
spark.read.parquet("/lake/silver/transactions")
.filter(F.col("txn_date") >= "2026-10-01") # partition pruning
.filter(F.col("amount") > 50000) # predicate pushdown
.select("account_id", "amount") # column pruning
.groupBy("account_id")
.agg(F.sum("amount").alias("high_value_total"))
)
What are common interview follow-up questions?
- Does column pruning work with CSV? It discards columns after reading, not during. The reader still reads the full line.
- What if I use
select("*")? Catalyst still prunes based on downstream usage, not the initial select. - Does column pruning reduce network transfer? Yes, in scans from remote storage like S3, fewer columns means fewer bytes transferred.
Visual Overview
Columnar File Layout and Pruning ───────────────────────────────── Parquet File (100 columns) ┌────────────────────────────────────────────┐ │ Row Group 1 │ │ ┌────────────┐ ┌────────┐ ┌──────────┐ │ │ │ account_id │ │ amount │ │ txn_date │ │ │ │ (2 MB) │ │ (3 MB) │ │ (1 MB) │ │ │ └────────────┘ └────────┘ └──────────┘ │ │ ┌──────────────────────────────────────┐ │ │ │ 97 more column chunks (150 MB) │ │ │ │ ← SKIPPED by projection pushdown │ │ │ └──────────────────────────────────────┘ │ └────────────────────────────────────────────┘ Query needs: account_id, amount, txn_date Bytes read: 6 MB (3 columns) Bytes skipped: 150 MB (97 columns)
Key Takeaway
Projection pushdown, also called column pruning, is the optimization where Catalyst figures out the minimal set of columns the query actually needs and tells the data source to read only those. In columnar formats like Parquet, each column is stored in its own region of the file, so reading three columns of a hundred-column table reads roughly three percent of the bytes and never seeks to the other regions.
The part interviewers probe is how Catalyst decides the needed set. It does not look only at the final SELECT – it analyzes the whole logical plan and takes the union of every column that influences the result: projections, filters, join keys, group-by, aggregations, and sorts. So even if you start with select("*") in an intermediate step, if only five columns survive to the output and conditions, Catalyst prunes down to those five.
At the National Bank of India our transactions table had around a hundred columns, and most reports needed only a handful like account id, amount, and date. Because we stored the data in Parquet, projection pushdown meant those reports read a few percent of the bytes instead of the whole table. Combined with predicate pushdown skipping irrelevant rows and partition pruning skipping irrelevant months, a scan collapsed to a tiny fraction of the file.
The caveat is that row-based formats like CSV and JSON cannot truly prune columns because fields are interleaved per line, which is one of the key reasons analytical data should be stored in columnar formats.
Remember one sentence: projection pushdown tells the reader to skip unneeded columns at the storage level, and Catalyst computes the minimal column set from the entire plan, not just the final SELECT.
---