The Join-Aware Materialized View Query Rewrite Gap

Why Star Schema Breaks Most Cloud Warehouses? A look at why join-containing materialized views with transparent query rewrite are the rule — not the exception — in analytics, and where StarRocks, Databricks, Snowflake, BigQuery, and Redshift actually stand.

The Pattern Everyone Has, Few Engines Accelerate

If you run a data warehouse, you run star schema queries. The canonical shape looks like this:

SELECT
  d_d.fiscal_quarter,
  d_p.category,
  d_r.country,
  SUM(f.revenue)    AS revenue,
  COUNT(f.order_id) AS orders,
  MAX_BY(f.order_id, f.revenue, 3) AS top_orders_by_revenue
FROM fact_orders f
JOIN dim_product d_p  ON f.product_sk = d_p.product_sk
JOIN dim_region  d_r  ON f.region_sk  = d_r.region_sk
JOIN dim_date    d_d  ON f.date_sk    = d_d.date_sk
WHERE d_d.fiscal_year = 2025
GROUP BY 1, 2, 3;

The thing to notice: the GROUP BY columns are a mix of fact-side keys and dimension-side attributes. fiscal_quarter, category, and country live in dimension tables, not the fact table. The join is structurally inseparable from the aggregation.

This is not an edge case. It is what nearly every BI dashboard built on a dimensional model does. Which means a materialized view that can accelerate it must contain the join. A single-table MV on fact_orders alone can never pre-compute this result — it has none of the grouping attributes.

So when a warehouse advertises “materialized views with automatic query rewrite,” the question that actually matters is: can the rewrite route a query against the base tables through an MV that contains joins? Single-table-only rewrite handles only the degenerate case where every grouping attribute is already denormalized onto the fact table — a minority of real analytical queries.

Why This Was Historically Hard

Join-aware MV query rewrite is a genuinely difficult optimizer problem. It requires solving the SPJG (Select-Project-Join-Group-by) containment problem: given an arbitrary user query and a set of candidate MVs, the optimizer must prove that an MV’s result set contains what the query needs, and that a compensating query on top of the MV produces a correct answer.

For aggregations over joins, that means proving several things:

• Join graph subsumption — the MV’s join tree must subsume the query’s join tree.
• Grouping rollup — the MV’s GROUP BY must be at least as fine-grained as the query's, so the query can roll up further.
• Measure derivability — the query’s aggregates must be computable from the MV’s stored aggregates. SUM is re-summable. AVG requires storing SUM and COUNT separately. COUNT(DISTINCT ...) generally is not re-aggregatable at all.
• Filter compensation — predicates not baked into the MV must be pushable on top of the MV scan.

Oracle shipped this for data warehousing in the early 2000s. Most cloud-native warehouses skipped it for years — partly because the engineering is non-trivial, and partly because selling compute is easier than saving it. StarRocks was, for a long while, the standout open engine that implemented full SPJG rewrite in its asynchronous MV engine.

Where Each Engine Actually Sits

[embed]

A point worth making: BigQuery and Redshift both support join-containing MVs with transparent rewrite in production today. They tend to get left out of the Databricks-vs-Snowflake narrative, but on this specific capability they are ahead of both.

StarRocks implements join-aware rewrite in its async MV engine on the academic SPJG framework. It handles join graph subsumption, grouping rollup, partial rewrite, and candidate ranking across multiple MVs, with proper staleness checks before a rewrite is allowed. It has been production-hardened for several years based on a lot of feature requests and real-world use cases from Celonis. For general-purpose, join-aware query rewrite, it remains the most mature option in the open ecosystem. Databricks is catching up — /business-semantics/metric-views/materialization with [aggregated, unaggregated] types in the relaxed mode. Snowflake covers the capability space with two features, but neither delivers what Oracle had in 2003. Materialized Views support transparent query rewrite — the optimizer can automatically substitute the MV for a base-table query — and refresh incrementally via a serverless background service. But they are restricted to a single base table with no joins, and prohibit window functions, HAVING, ORDER BY, LIMIT, and UDFs. That single-table restriction is precisely the thing that makes them unable to accelerate star schema queries. Dynamic Tables support arbitrary joins, unions, and window functions with scheduled TARGET_LAG refresh — but the optimizer does not transparently rewrite queries to use them. You must query a dynamic table by name. The deeper reason is architectural. Snowflake’s MV maintenance was built around micro-partition delta tracking on a single base table: the background service knows exactly which micro-partitions changed and recomputes only those. Extending that to multi-table joins means tracking delta propagation across join inputs — a much harder invalidation problem. Dynamic Tables sidestep the join-expressiveness problem but pay for it by giving up transparent rewrite entirely.

For a classic star schema workload — fact joined to several dimensions, grouped by attributes from both sides — the engines that handle it transparently and in production today are StarRocks, BigQuery, Redshift, and legacy Oracle.

Databricks is actively building toward parity. Its aggregate-aware rewrite through Metric Views is the right design direction, but it is experimental and sacrifices staleness guarantees in its current mode.

Snowflake, despite its strengths elsewhere, has a real gap here: no single feature delivers join-aware MV with transparent rewrite. You design around it — usually by routing your semantic layer directly at named Dynamic Tables — rather than relying on the optimizer to do the work for you.

The lesson for anyone designing a serving layer: don’t take “supports materialized views with automatic query rewrite” at face value. Ask whether the rewrite survives a join. For dimensional analytics, that single question separates the engines that accelerate your real workload from the ones that only accelerate the demo.