Predicate pushdown is a query optimization technique that moves filtering conditions from the query engine down to the data source itself. Rather than scanning entire tables and filtering afterwards, the source applies your WHERE predicates before sending data over the network. In Starburst, and other federated query engines like Trino, successful pushdown means you won’t see a ScanFilterProject operator for that clause when you run EXPLAIN, indicating the filter was applied at the source.
This optimization sits at the heart of modern data architectures. In federated query engines and data lakehouses, pushdown serves as a primary lever for reducing scan costs, network I/O, and end-to-end latency when reading from operational databases, data warehouses, search systems, and lakehouse tables. The technique extends beyond simple predicates too. Related pushdowns include projection (columns), aggregation, Top-N/Limit, join pushdown, and dereference pushdown for nested fields, though availability depends on each connector and source.
For data engineers working across cloud platforms, understanding predicate pushdown becomes essential as organizations scale their analytics operations and face mounting pressure to control costs while delivering faster insights.
The importance of predicate pushdown becomes crystal clear when you consider how modern data platforms charge for usage. Services like Amazon Athena bill by bytes processed, while BigQuery charges based on bytes scanned. Reducing scanned data through pushdown and partition pruning directly translates to lower costs and faster query response times.
Financial services organizations exemplify this need perfectly. FINRA’s case study highlights the challenge of high-volume trade analysis at scale, where regulatory analytics require scanning massive, multi-source datasets with strict access controls and narrow, high-selectivity filters for specific symbols, time windows, or accounts. In environments like this, pushdown and skipping become essential for maintaining both cost efficiency and SLA compliance.
How columnar formats amplify predicate benefits
Columnar file formats like Parquet and ORC expose statistics and optional indexes that make predicate pushdown even more powerful. These formats allow readers to skip entire files, stripes, row groups, or pages based on predicates. ORC implements this through SearchArgument functionality, while Parquet exposes page indexes and statistics for fine-grained skipping.
Lakehouse table formats like Delta and Iceberg leverage metadata and file-level statistics to skip files that cannot match filters, dramatically accelerating training-set curation, feature engineering, and operational dashboards. This file skipping capability becomes particularly valuable for companies like El Toro, which ingests and analyzes hundreds of billions of signals, where selective filters and metadata skipping prevent moving petabytes on each analytics run.
Dynamic filtering takes pushdown further
Modern federated engines add another layer through dynamic filtering. In federated data access architectures, dynamic filtering generates runtime predicates from the build side of joins and pushes them into scans, including ORC and Parquet readers. This reduces I/O dramatically for selective joins by filtering out irrelevant data before it crosses storage boundaries.
Technical hurdles that complicate predicate pushdown implementation
While predicate pushdown offers compelling benefits, implementing it successfully across diverse data sources presents significant challenges. The primary obstacle stems from the fact that pushdown support varies dramatically across connectors and sources.
Connector-specific limitations create complexity
Each data source exposes different APIs, type systems, and pushdown semantics, forcing connectors to implement careful subsets for correctness. For example, PostgreSQL and Redshift connectors avoid pushing range predicates on VARCHAR columns to preserve string-collation correctness. While equality operations (IN, =, !=) push down safely, range operations (>, <, BETWEEN) on text remain in the engine to avoid wrong results.
BigQuery presents a different tradeoff: the connector pushes predicates only for certain types and only when using the REST API path. The Storage API path prioritizes streaming performance but sacrifices those pushdown capabilities. Teams must choose between predicate efficiency and throughput based on their workload characteristics.
Query shape determines what gets pushed
The structure of your queries significantly impacts pushdown effectiveness. Functions applied to columns, non-sargable predicates, or complex expressions frequently block pushdown entirely. Additionally, query engines like Trino perform domain compaction on very large IN lists, which can change what ultimately gets pushed to the source. Configuration options like domain-compaction-threshold help tune this behavior, but teams need to understand these interactions to optimize effectively.
File format limitations reduce skipping effectiveness
Even with columnar formats, predicate effectiveness depends heavily on data layout quality. Parquet’s page index remains optional, and many older writers omit it entirely. Without page indexes or with poor statistics quality, file-level skipping becomes much less effective. ORC and Parquet statistics quality varies significantly based on the writer implementation and configuration.
Cross-catalog joins hit fundamental boundaries
Join pushdown generally works only for tables within the same catalog. Cross-source joins cannot be pushed entirely and must rely on engine-side shuffles, which limits the optimization potential for federated queries spanning multiple systems.
Getting started with effective predicate pushdown strategies
Successfully implementing predicate pushdown requires a systematic approach that addresses both technical requirements and operational constraints. The key lies in understanding your connector landscape and designing queries that maximize pushdown opportunities.
Writing queries that push down successfully
Start by crafting sargable predicates that avoid wrapping columns in functions. Instead of writing date_trunc(‘month’, order_ts) = ‘2026-03-01’, prefer explicit range predicates like order_ts >= TIMESTAMP ‘2026-03-01’ AND order_ts < TIMESTAMP ‘2026-04-01’. Check connector documentation to understand which predicates and types support pushdown for your specific sources.
For data warehouses with special collation behavior, prefer equality and IN operations on text columns while moving complex string comparisons to the engine when correctness might change. Understanding each connector’s specific caveats, such as text range pushdown restrictions, becomes essential for maintaining query correctness.
Validating pushdown effectiveness continuously
Use EXPLAIN plans to verify that pushdown occurred by confirming the absence of ScanFilterProject operators for your clauses. Build these verification steps into your pipeline tests for critical transformations to catch regressions early. When operators that should have been pushed appear in the execution plan, investigate connector limitations or query structure issues.
Optimizing data layout for maximum skipping
Ensure that Parquet and ORC writers record column statistics and, where possible, include Parquet page indexes. Optimizing Iceberg table performance through sorted tables can significantly improve predicate effectiveness. Compact small files and organize partitioning or clustering to maximize file and row-group pruning opportunities. For Delta and Iceberg tables, leverage metadata-level pruning along with clustering and Z-ordering where appropriate to improve skip rates.
When choosing an open table format, consider each format’s predicate pushdown capabilities. An Iceberg vs Delta Lake comparison reveals important differences in how each handles file skipping and metadata operations. What is Apache Iceberg explains the foundational concepts behind one of the leading formats for predicate optimization.
Leveraging advanced capabilities for complex scenarios
For heavy extracts from systems like Redshift, use connectors’ parallel paths such as UNLOAD to S3 to avoid single-threaded JDBC bottlenecks during ingestion. After parallel extraction, you can use CTAS operations to load data into Iceberg or Delta format for subsequent analytics.
Consider using cached views or materialized views to store results of expensive federated queries, then leverage table scan redirection to transparently route reads to faster lake-based copies. This approach proves particularly valuable when upstream systems impose throttling limits or when you need consistent ingestion performance.
Organizations implementing open data lakehouse architecture often benefit from Starburst’s Enterprise, which provides advanced predicate pushdown capabilities across diverse sources. For cloud-native deployments, Starburst Galaxy offers managed predicate optimization without infrastructure overhead.
Planning for connector variability and governance
Review the connector feature matrix and individual connector documentation when selecting sources for predicate-aware ingestion. For BigQuery workloads, carefully weigh REST versus Storage API tradeoffs based on your predicate selectivity and throughput requirements.
For organizations focused on AI and analytics solutions, predicate pushdown becomes essential for feature engineering and model training pipelines. Teams building data applications rely on pushdown optimization to maintain responsive user experiences.
Implement proper governance controls to ensure that pushed-down scans remain policy-compliant. Enforce RBAC and ABAC with row filters and column masks, and use service accounts for automated jobs to maintain security while enabling optimization.
