Predicate Pushdown

Predicate pushdown applies filtering as close to stored data as the execution engine and source support. Instead of reading an entire dataset into a transformation job and then discarding most rows, the job asks an earlier processing layer to eliminate irrelevant data. This can reduce network traffic, serialization, memory pressure, and the number of records entering expensive joins, aggregations, or shuffles.

Consider a daily ingestion job that needs only completed orders updated since its previous successful run. Reading every order through JDBC and filtering inside application code wastes transfer bandwidth and worker capacity. Executing a source query with conditions on status and update time lets the database return only qualifying rows. An appropriate index may also reduce database reads, but predicate pushdown and index access are separate optimizations: a database can apply a pushed filter while still scanning its entire table.

In distributed processing frameworks such as Apache Spark, use supported SQL expressions or DataFrame filters so the optimizer can inspect the conditions. A filter on a directly referenced timestamp column is generally easier to translate than a filter hidden inside a Python user-defined function. Writing a filter early makes intent clear, but its location in source code does not prove pushdown occurred. Spark uses lazy evaluation and may rearrange operations; inspect the physical execution plan to see what reached the scan.

For object storage, pushdown typically operates through the query engine's file reader rather than through the object storage service itself. Parquet and ORC metadata can let readers skip row groups or stripes whose statistics prove that no rows qualify. This does not mean that every nonmatching row can be excluded without reading data. If values are scattered across every row group, the reader may still inspect much of the dataset. Plain text formats generally offer fewer opportunities to skip data using metadata.

Partition pruning is related but distinct: it eliminates partitions based on partition values, while predicate pushdown can filter within the remaining data. Column pruning is also different because it removes unnecessary columns rather than rows. Combining these techniques often produces the best result. For example, restrict an orders dataset to one date partition, apply an amount condition through the file reader, and read only the columns required for the output.

Pushdown must preserve query semantics. Moving a filter across an outer join, aggregation, or operation involving null values can change results unless the optimizer applies valid transformation rules. Connectors also differ in supported operators, data types, and function translations. Unsupported conditions may remain as residual filters evaluated after reading, even when other conditions are pushed successfully.

Measure the actual improvement using execution plans, bytes read, rows returned, shuffle volume, and elapsed time. A pushed database predicate can reduce transfer while increasing source CPU consumption, especially without useful indexes or when many workers issue concurrent queries. For ingestion and transformation optimization, prefer reducing unnecessary work before adding workers, but validate source load, result correctness, and end-to-end runtime rather than assuming any filter automatically lowers scan cost.