Query pushdown is a query optimization technique that moves operations like filters, aggregations, and projections from your query engine down into the source systems where data actually lives. Instead of pulling entire tables across the network just to filter them later, pushdown ensures operations execute as close to the data as possible, dramatically reducing the amount of data scanned, transferred, and processed by your query engine.
In today’s data architectures, pushdown serves dual purposes. Teams use it for exploratory data federation, querying across multiple sources without moving data, and for ingestion patterns that materialize results into target tables while minimizing load on source systems. With Starburst, any successful federated query can become a materialized view in object storage, transforming pushdown-optimized reads into persisted data assets that power downstream analytics and AI workloads.
The challenges teams face implementing query pushdown are real and costly. Unpredictable performance across different connectors, spiraling egress charges, API limits that throttle critical jobs, and observability gaps make it impossible to verify what’s actually being optimized. This is where Starburst’s comprehensive approach to pushdown optimization becomes invaluable, providing enterprise-grade connectors, intelligent cost controls, and the tooling needed to operationalize pushdown across your entire data ecosystem.
Query pushdown has become critical as organizations scale their data operations because it directly addresses the three biggest bottlenecks in modern analytics:
- Network transfer costs
- Compute resource waste
- Time to insight
When your marketing team needs to analyze customer behavior across CRM data in Salesforce, transaction records in PostgreSQL, and web analytics in BigQuery, pushdown ensures you’re only moving the specific rows and columns needed rather than entire datasets.
The economics of selective data movement
Let’s say you’re joining customer data from a 50-million-row table in Snowflake with a much smaller dimension table in your lakehouse. Without pushdown, you’d transfer all 50 million rows across the network, then filter and aggregate in your query engine. With effective pushdown, filters and aggregations execute within Snowflake, and only the pre-aggregated results cross the network boundary. This vastly reduces data transfer costs by orders of magnitude, especially when working across cloud providers or regions.
Organizations processing massive volumes see this impact clearly. For example, Going processes over 50 petabytes annually and delivers timely price alerts using Starburst Galaxy, with pushdown optimization playing a crucial role in maintaining both performance and cost efficiency at scale.
Enabling rapid data product development
Meanwhile, companies like Kovi have used Starburst with Iceberg to speed ad-hoc queries and ETL while cutting compute costs. The approach means writing federated SQL that selects only the data needed using pushdown, then materializes results using CREATE TABLE AS SELECT or materialized views. This approach enables teams to create and manage data products without the traditional overhead of complex ETL pipelines.
Cross-border analytics and data sovereignty
For organizations with data residency requirements, pushdown becomes even more critical. Rather than moving entire datasets across borders or cloud regions, capabilities like Starburst Stargate allow AI workloads and analytics queries to operate without mass data relocation, enabling ingestion and materialization only of permitted, policy-filtered subsets per region.
The technical realities that complicate query pushdown
Despite its clear benefits, implementing effective query pushdown requires a strategic approach. Understanding these obstacles helps you plan realistic timelines and architect solutions that actually work in production.
Inconsistent pushdown capabilities across connectors
Typically, the biggest challenge teams face is that pushdown support varies dramatically between different data sources. Whether a filter, aggregation, or join can be pushed down depends entirely on the specific connector. Some connectors restrict certain predicate types, meaning string operations or range queries that work perfectly with one source might not push down to another. This inconsistency makes it difficult to predict query performance and costs when working across heterogeneous environments.
You might write SQL expecting consistent behavior across your PostgreSQL and Oracle sources, only to discover that complex WHERE clauses push down to PostgreSQL but execute locally when querying Oracle, completely changing your resource requirements and execution time.
How Starburst helps manage this unpredictability and cross-cloud complexity
Starburst addresses this by disabling join pushdown by default to help avoid unexpected warehouse compute charges. This exemplifies a broader challenge, including join operations in remote warehouses can drive surprising costs, and cross-region or cross-cloud data egress creates additional charges that are difficult to predict and budget for.
This ensures that teams do not discover these cost implications after they’ve already committed to an architecture. Other technologies can cause seemingly efficient federated queries to trigger thousands of dollars in warehouse compute charges or egress fees, especially when BigQuery or Snowflake data transfer costs compound across regions.
Why these challenges persist in other modern architectures
These pushdown challenges persist because they reflect fundamental tensions in modern data architecture. The fragmentation of data platforms and protocols means that connectors need to map very different capabilities, each with unique limits for predicates, aggregates, and joins. This prevents teams from implementing uniform ingestion policies across their data ecosystem.
Legacy and enterprise sources compound this complexity. Systems like Oracle, Teradata, Db2, SAP, and Salesforce expose varied SQL dialects, query planners, and transactional constraints. Each requires specialized connector logic, and pushdown support remains uneven across this landscape.
Governance and data sovereignty requirements add another layer of complexity. Cross-cloud and cross-region data movement triggers residency and privacy controls, requiring fine-grained policies across many systems. Even correct pushdown can create compliance issues when filtered data crosses jurisdictional boundaries unexpectedly.
Finally, cloud cost models tied to scanned bytes and egress create moving targets for optimization. Pricing policies change regularly, and what appears to be an efficient pushdown strategy today might become expensive tomorrow as providers adjust their transfer and compute pricing.
Your path to production-ready query pushdown
Getting started with query pushdown successfully requires focusing on verification first, then scaling systematically as you understand the behavior patterns of your specific sources and workloads.
Starburst is designed specifically to work within this framework.
Start with pushdown verification
Before committing to any ingestion architecture, establish a practice of using EXPLAIN statements to confirm which operations actually push down. Simple AND-ed predicates typically push down reliably, while complex expressions often don’t. Build this verification into your development process so teams can iterate on SQL to maximize pushdown before deploying production jobs.
Consider operationalizing this verification in your CI/CD pipeline. If a critical ingestion query suddenly loses pushdown capability due to connector updates or source changes, you want to know before it impacts production performance and costs.
Leverage enterprise-grade connectors with intelligent defaults
Starburst’s enterprise connector portfolio includes purpose-built connectors for systems like Snowflake, BigQuery, Teradata, and Salesforce that are specifically optimized for pushdown scenarios. These aren’t just enhanced versions of open-source connectors; they include parallel read capabilities, managed statistics, and intelligent cost controls.
For example, the Snowflake connector uses Arrow-based parallel reads and disables join pushdown by default to help control warehouse compute costs. The BigQuery connector leverages the BigQuery Storage API and supports multiple pushdown operations to reduce CPU usage and improve performance.
The Starburst Enterprise provides additional capabilities for organizations that need enterprise-grade security, compliance, and support for their pushdown implementations.
Build materialized views for repeatability
Rather than running federated queries repeatedly, use Galaxy materialized views to persist the results of pushdown-optimized queries in object storage. This pattern lets you pay the cost of source-side processing once, then serve multiple analytics and AI workloads from the materialized results.
Set up scheduled refreshes to keep derived tables current, and consider incremental refresh capabilities where supported to reduce processing overhead for frequently updated sources. This approach aligns with modern data transformation pipelines that prioritize efficiency and automation.
Implement intelligent performance optimizations
Enable dynamic filtering for selective joins to reduce fact-side scans during ingestion operations. This runtime optimization can dramatically reduce the amount of data processed even when explicit predicates don’t push down effectively.
For large extraction jobs, configure fault-tolerant execution to handle worker failures during long-running ingestion processes. Verify that your connectors support retry policies before enabling this capability.
Organizations can also benefit from optimizing Iceberg table performance through techniques like sorted tables, which complement pushdown strategies for open data lakehouse architecture.
Monitor and respect source system limits
For SaaS sources, monitor API consumption through system tables so you can track quota usage and plan extraction schedules accordingly. For OLTP systems, schedule extraction windows, throttle parallelism settings, and consider read replicas to minimize contention with production workloads.
Configure parallel read settings like connection counts for high-throughput scenarios, but balance this against source system capacity.
Apply governance before ingestion
Rather than moving data first and applying policies later, use row filters and column masking in your ingestion SQL so only compliant subsets are materialized. This approach ensures that your pushdown optimization doesn’t accidentally create compliance issues by moving sensitive data across boundaries.
Implement audit trails for refresh jobs and access patterns so you can demonstrate that your ingestion processes respect data governance requirements. This is particularly important for organizations in financial services data analytics or healthcare data analytics where regulatory compliance is critical.
Organizations looking to modernize legacy systems can leverage data migration solutions that incorporate pushdown optimization from the start. Whether you’re implementing Hadoop modernization or migrating to modern data architecture, starting with pushdown-aware design patterns helps ensure your new architecture delivers the performance and cost benefits you expect.
The key to success with query pushdown lies in treating it as an operational discipline rather than just a technical capability. Start small with well-understood sources, verify pushdown behavior systematically, and scale your approach as you build expertise with the specific patterns and limitations of your data ecosystem. With the right foundation and tooling, including solutions for building data applications and data for artificial intelligence, query pushdown transforms from a complex technical challenge into a reliable mechanism for efficient, cost-effective data ingestion that scales with your organization’s needs.
