# Predicate Pushdown

Predicate pushdown is a query optimization that moves filtering closer to the data source so fewer records reach later processing stages.

Cloud concept. Updated 2026-10-29. Page: https://blowstack.com/tools/cloud-concepts-hub/predicate-pushdown

> A predicate is a condition that determines which rows qualify for processing, such as a date range or status comparison. Pushing supported predicates into a database, connector, or file reader can reduce data transfer, decoding, memory use, and downstream computation, improving ingestion and transformation runtime.

Practice exams covering Predicate Pushdown: [DEA-C01 AWS Certified Data Engineer - Associate](https://blowstack.com/practice-exams/aws-certified-data-engineer-associate).

## 6 key aspects of Predicate Pushdown

Effective predicate pushdown combines source-compatible filters, suitable storage layout, semantic correctness, and execution-plan validation to reduce unnecessary processing.

1. **Filtering near the source**: Move supported row conditions into the database, connector, or scan layer. Fewer records then need to cross the network or enter downstream transformations.
2. **Optimizer-visible expressions**: Use native SQL or DataFrame expressions that the engine can analyze and translate. Opaque user-defined functions, unsupported casts, and connector limitations can prevent pushdown.
3. **Storage-aware data skipping**: Columnar readers can use Parquet or ORC statistics to skip data regions that cannot satisfy a predicate. The benefit depends on metadata quality and how qualifying values are distributed.
4. **Complementary pruning techniques**: Combine predicate pushdown with partition pruning and column pruning. They reduce different dimensions of work: qualifying rows, selected partitions, and required columns.
5. **Correctness-preserving optimization**: Filters can move only where results remain equivalent. Join behavior, null handling, type conversions, and source comparison rules can constrain valid transformations.
6. **Measured runtime improvement**: Inspect scan plans and compare bytes read, transferred rows, source load, and job duration. A pushed predicate does not guarantee an index lookup, reduced physical reads, or a faster overall job.

## Cloud services for Predicate Pushdown

AWS Glue or Apache Spark on Amazon EMR can ingest filtered database records and write partitioned, columnar datasets to object storage. Amazon Athena and Amazon Redshift Spectrum can then apply query predicates while scanning those datasets, using supported metadata-based skipping and partition pruning to limit work. These benefits are not inherited automatically from the ingestion stage: each query engine independently plans its filters. Engineers should verify pushdown at each boundary and measure source load, scanned bytes, transferred rows, and downstream processing time.

- **[AWS Glue](https://blowstack.com/tools/cloud-services-hub/aws-glue)**: AWS Glue Spark jobs can benefit from supported DataFrame predicates pushed into data-source readers. For catalog-backed Amazon S3 sources, the push_down_predicate option filters partition metadata before files are read; this is partition pruning and should not be confused with filtering individual rows inside files.
- **[Amazon EMR](https://blowstack.com/tools/cloud-services-hub/amazon-emr)**: Apache Spark on Amazon EMR can push supported filters into Parquet, ORC, and JDBC data sources, depending on reader and connector capabilities. Inspect the physical plan, including pushed filters and partition filters, to distinguish source-side filtering from residual processing.
- **[Amazon Athena](https://blowstack.com/tools/cloud-services-hub/amazon-athena)**: Amazon Athena can use supported predicates and columnar-file metadata to avoid reading irrelevant data, alongside partition pruning. Federated-query predicate pushdown depends on the connector and source capabilities, so a WHERE clause alone does not establish that filtering occurred remotely.
- **[Amazon Redshift](https://blowstack.com/tools/cloud-services-hub/amazon-redshift)**: Amazon Redshift Spectrum can push supported filtering and other query operations into its external-data processing layer rather than returning all external rows to the Redshift cluster. Predicate compatibility, partitioning, file format, and data layout influence how much work is avoided.

## Predicate Pushdown exam questions

Real practice exam questions on Predicate Pushdown, with the correct answer and its explanation.

### Question 1 (DEA-C01)

**Question:**

A manufacturer runs a nightly AWS Glue Spark job that ingests historical equipment readings from Parquet files in Amazon S3. The files are not partitioned. Records are sorted by pressure_kpa within each file, and Parquet row-group statistics contain minimum and maximum values for this numeric column. Most row groups fall entirely outside the required range of 900 through 950 kPa.

The job reads the files into a DataFrame and uses a Python user-defined function (UDF) to retain readings in the required range. Profiling shows that the filter executes after the Parquet scan and that the scan reads row groups that cannot contain qualifying readings. Parquet filter pushdown is enabled in Spark.

Which change should a data engineer make to allow the source reader to skip these row groups?

**Correct answer:** Replace the Python UDF with a native DataFrame filter using (col('pressure_kpa') >= 900) & (col('pressure_kpa') <= 950) before downstream transformations.

**Explanation:** Spark can translate these supported native comparisons into predicates for the Parquet reader. The reader can use row-group minimum and maximum statistics to skip groups that cannot contain matching values, reducing unnecessary reads and decoding. An opaque Python UDF does not provide the reader with these comparisons.

## Practice Predicate Pushdown in a full exam

Questions on Predicate Pushdown in each practice exam, with every answer option and an explanation for each:

- [DEA-C01](https://blowstack.com/practice-exams/aws-certified-data-engineer-associate): 3 questions

## What is 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.

## Learn Predicate Pushdown in lessons

1 short lesson on Predicate Pushdown in 1 certification course.

- DEA-C01: Predicate Pushdown: filtering at the data source (Premium), https://app.blowstack.com/lessons/predicate-pushdown-filtering-at-the-data-source-dea-c01
