Accelerating Apache Spark Query Execution with Apache Iceberg Materialized Views and Automatic Query Rewrite

Data engineers and database administrators managing large-scale analytical workloads on data lakes have long faced a persistent challenge: balancing the high performance demands of business intelligence (BI) dashboards with the operational rigidity of legacy SQL queries. Often, these queries involve complex multi-table joins, resource-intensive aggregations, and window functions that consume significant compute resources and increase latency. Previously, addressing these performance bottlenecks required manual query refactoring, a process that carries inherent risks of introducing bugs or regressions into stable production pipelines.

With the recent integration of automatic query rewrite capabilities into Amazon EMR version 7.12.0 and AWS Glue 5.1, organizations can now optimize these high-cost analytical tasks without modifying the underlying SQL. This advancement represents a significant shift in data lake management, moving away from manual optimization toward an intelligent, metadata-driven architecture that transparently improves performance.

The Evolution of Query Optimization in Data Lakes

The history of query optimization in distributed environments like Apache Spark has largely focused on physical plan execution—the way data is scanned, shuffled, and aggregated. However, logical optimization, specifically the ability for a system to recognize that a query’s requirements have already been partially or fully satisfied by existing pre-computed datasets, has historically been limited.

Prior to the introduction of automatic query rewrite, developers relied on custom ETL pipelines, manual query modifications, or simple standard views. Each of these approaches carried distinct limitations. Standard views in AWS Glue, for example, do not store results and require re-computation upon every execution, offering no performance gain. Custom ETL pipelines and manual query rewrites provide stored results but introduce significant management overhead, requiring engineers to manually update queries whenever the data model changes. The new approach leverages the AWS Glue Data Catalog to store materialized views (MVs) that are automatically discovered and utilized by the Spark optimizer, effectively bridging the gap between performance and ease of maintenance.

How Automatic Query Rewrite Functions

The mechanism behind this technology is an intelligent interceptor within the Apache Spark optimizer. When a user submits a SQL query, the engine analyzes the logical plan and consults a metadata cache of available materialized views. The optimizer then determines if any portion of the incoming query matches the schema, filters, or aggregations stored within an existing MV.

This process is tiered, allowing for both exact matches and partial substitutions. If an MV contains a subset of the required data, the optimizer can rewrite the query plan to read from the MV while performing only the necessary delta computations. If no suitable MV is found, the query executes against the base tables as usual, ensuring that the system remains robust and that data accuracy is never compromised.

A critical component of this architecture is the handling of staleness. During the optimization phase, the system verifies the status of the materialized view. If the data is deemed stale, the optimizer bypasses the view entirely to ensure that the user receives the most current data, thereby maintaining strict data integrity for time-sensitive reports.

Strategic Implementation: A Three-Tiered Approach

To understand the practical application of this technology, one can analyze the optimization of a common TPC-DS benchmark query: identifying the "Top 100 preferred US customers by total store spending." This query requires joining a massive store_sales table with a customer dimension table, applying specific filters, and calculating a rank via a window function.

Engineers can approach the acceleration of this query through three distinct tiers of materialized views, each offering a unique trade-off between performance gain and storage cost:

1. Tier 1 (Single-table Pre-aggregate): By creating an MV that aggregates the store_sales table at the customer-surrogate-key grain, the system eliminates the need for full-table scans of the raw data. This approach offers the broadest reuse, as the same MV can support any subsequent query requiring per-customer metrics. In testing, this resulted in a ~5x performance boost.
2. Tier 2 (Pre-join with Selective Filtering): By incorporating the customer table join and a fixed filter (e.g., preferred_cust_flag = 'Y') into the MV, the workload is further reduced. This tier is highly effective for specific BI dashboards, yielding approximately 10x faster execution times.
3. Tier 3 (Exact-match Materialization): This is the most specialized tier, where the entire query body is pre-computed and stored. Because the system simply retrieves the final 100 rows, performance can increase by more than 20x. While this offers the highest speed, it is the least flexible, as it only benefits that specific query structure.

Quantitative Impacts and Performance Analysis

Data collected from TPC-DS 3 TB benchmarks highlights that while performance gains are substantial, the costs associated with view creation must be considered. While Tier 3 provides the fastest query execution, Tier 2 often proves to be the most "expensive" to create, as it requires the highest volume of data processing and joins during the materialization phase.

The findings suggest that organizations should adopt a balanced strategy: using Tier 1 for broad-use data foundations and Tier 3 for mission-critical, high-frequency executive dashboards.

Broader Industry Implications

The introduction of this capability signifies a maturation of data lake technologies. By moving query optimization into the metadata layer, AWS is effectively reducing the "technical debt" associated with big data environments. Industry observers note that this reduces the reliance on specialized database administrators to "tune" individual queries, allowing developers to focus on higher-level business logic rather than the minutiae of execution plans.

For ISVs and enterprises with legacy report suites, this technology is particularly transformative. Because the query rewrite happens transparently, organizations can modernize their backend data architecture—moving to Apache Iceberg and AWS Glue—without needing to touch thousands of existing, hard-coded SQL queries embedded in external applications.

Operational Prerequisites and Best Practices

To utilize this feature, users must enable the optimization by setting spark.sql.optimizer.answerQueriesWithMVs.enabled=true within their Spark session. Successful implementation requires careful monitoring of the query plan. Developers are encouraged to use the EXPLAIN command in Spark to verify that the query is indeed utilizing the materialized view rather than the base tables.

Common reasons for failure to rewrite include mismatched filter expressions, type mismatches between the MV and the base table, or the use of non-deterministic functions that prevent the optimizer from establishing a reliable equivalence. As the ecosystem continues to evolve, the integration of these automated workflows is expected to become the industry standard for managing the increasing complexity of cloud-native data lakehouses.

In conclusion, the ability to accelerate Spark queries through automatic, metadata-driven materialized views represents a significant leap forward in data platform efficiency. By providing a scalable, non-disruptive method for performance tuning, this feature empowers organizations to derive value from their data lakes faster, while simultaneously reducing the operational costs associated with manual query optimization.