Predicate Pushdown: Filter Data at the Source
Predicate pushdown tells the database to filter data at the source, not after fetching it. This speeds up queries in data warehouses and lakehouses by reducing network traffic. The main footgun: not all data sources can execute all types of filters.
Why it exists
In distributed systems, compute and storage are often separate. Moving terabytes of data from a storage layer (like S3) to a compute cluster just to filter out 99% of it is a massive performance bottleneck. Predicate pushdown was developed to solve this by minimizing data transfer.
The mental model
Think of it like ordering from a library. Instead of asking a librarian to bring the entire science section to your desk for you to find one book, you give them a request slip with the exact title and author. The librarian (the data source) does the filtering, and you (the query engine) only receive the specific data you asked for.
How it works
When a query engine receives a query like SELECT * FROM sales WHERE region = 'West', its optimizer inspects the query. Instead of fetching the entire sales table, the optimizer rewrites the plan to "push down" the region = 'West' predicate to the storage layer itself. The storage engine, if it's smart enough, then reads and returns only the rows matching that condition. This means far less data travels over the network and needs to be processed by the engine.
When to use it
This technique is automatically applied by query optimizers and is most impactful in architectures where storage and compute are decoupled. Three key places are: first, data warehouses; second, Big Data systems like Apache Spark querying data lakes; and third, federated queries across different databases. It's fundamental for performant analytics at scale.
When not to use it
The benefits are limited if the underlying data source cannot execute the filter. For example, a simple CSV file on its own has no mechanism to pre-filter rows before being read. Similarly, if a predicate involves a complex user-defined function (UDF) that only the query engine understands, it cannot be pushed down. The optimization is only as smart as the data source.
One canonical example
An analyst queries a 10TB sales dataset stored as Parquet files in a data lake, partitioned by year and month. The query is SELECT AVG(sale_amount) FROM sales WHERE country = 'USA' AND year = 2023. A smart query engine pushes down both predicates. It can use the year = 2023 predicate to completely ignore reading data from any other year's directory (partition pruning), and it can ask the Parquet reader to use its internal metadata to only return rows where country = 'USA'. The engine might only read a few gigabytes instead of all 10 terabytes.
Interview question
Which factor would most directly prevent a query optimizer from successfully pushing down a predicate to the data source?
- a.The query is executed in a traditional monolithic database where compute and storage are on the same server.
- b.The predicate involves a user-defined function (UDF) that the data source does not recognize.Correct
- c.The dataset being queried is small enough that network transfer costs are negligible.
- d.The data is stored in a highly compressed, columnar format like Parquet.
Why? this is the answer
The card explicitly states that if a predicate involves a UDF only the query engine understands, it cannot be pushed down. While other options describe scenarios where pushdown might be less beneficial or relevant, they do not directly prevent the optimizer from attempting to push down a filter the source could handle.
Just read this? Test yourself on what you have been reading.
Read the original → dremio.com
- #databases
- #architecture
- #optimization
- #big data
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on databases — each one lists the topics its interview covers.
See open roles