Materialized views are one of the oldest tricks in database engineering. Oracle shipped them in the 1990s. Postgres has had them for over a decade. Every data warehouse worth its salt supports some flavor of pre-computed, persisted query results. There is nothing new about the idea of “compute it once, read it many times.”
So why is a decades-old technique suddenly showing up in nearly every conversation about query acceleration on the lakehouse?
The data lake’s original bargain was cheap but slow. What’s changing now is the demand to keep it cheap while making it fast too, without the old workaround of copying data into a purpose-built system to get there. Data volumes are growing faster than budgets, and that pressure is pushing the market in exactly the right direction.
Not every workload on the lakehouse needs to be sub-second; but a growing share of workloads do. Customer-facing dashboards where a slow load reads as a broken product, fraud detection that has to score a transaction before it clears, SREs analyzing logs to root cause a P1 outage. And now a new category is emerging on top of those: AI agents, looping through queries as part of a reasoning chain, where each query is a blocking step and latency compounds with every iteration. An agent that waits two seconds per lookup isn’t just slow, it’s ten times slower by the time it’s chained five queries together.
The market is responding accordingly. For years, sub-second serving on massive, real-time data was a problem only a handful of consumer tech giants had, and a handful of specialized systems were built to solve it. When the platform vendors who built their reputation on batch-scale lakehouse economics start shipping engines purpose-built for millisecond latency, it’s not an edge case anymore, it’s an expectation of any modern data platform.
Databricks’ recent Lakehouse//RT announcement, powered by its new Reyden engine, is the clearest signal yet. The pitch is millisecond queries directly on governed Delta and Iceberg tables, no separate serving layer, no data movement. It’s a serious engineering effort, and worth taking seriously, precisely because of how mainstream it signals this need has become.
But look closely at Databricks’ own documentation for Lakehouse//RT, and you’ll find this guidance: if your query involves joins or is getting complex or slow, consider using materialized views that pre-aggregate your data for faster latencies. That single line tells you almost everything about where this is heading. Even a purpose-built, ground-up real-time engine reaches for materialized views once query complexity or concurrency climbs high enough. The technique isn’t obsolete. It’s foundational. But it’s also not sufficient on its own, and this is where a lot of engineering teams are about to relearn a lesson the hard way.
Materialized views are a legitimate, often necessary tool. They’re also a tool that quietly accumulates cost and technical debt when they become the only tool in the box. Let’s walk through both sides honestly, then talk about what else belongs in your query acceleration toolkit.
What a materialized view actually buys you
A materialized view takes the result of a query, typically an aggregation, join, or filter, and persists it as physical data rather than recomputing it on every read. The next query against that view is a lookup against pre-computed results instead of a scan-and-compute operation against raw data.
The win is real and it’s substantial:
Predictable, low latency reads. Once the view is materialized, query time is largely decoupled from the complexity of the underlying computation. A join across three fact tables with a window function on top becomes a simple scan of a pre-joined, pre-aggregated table. This is the entire reason Databricks recommends them for the queries Reyden itself can’t hold sub-second on the underlying tables.
Offloaded compute from the query path. The expensive work, joins, aggregations, window functions, happens once at refresh time, not once per query. If you have a dashboard with a thousand concurrent viewers or an agent hitting the same aggregate query on a loop, you’ve moved a thousand redundant computations into one. Most modern implementations, Delta Live Tables, Snowflake dynamic tables, BigQuery, Postgres extensions, default to incremental refresh: computing only the delta since the last run instead of recomputing the full view from scratch. That’s the mechanism that actually makes this trade-off pay off at scale. Full recomputation on every refresh would eat most of the compute savings you’re trying to capture.
Compatibility with existing SQL semantics. Materialized views are supported broadly, understood by every data engineer, and require no new query language or indexing paradigm. If your team already knows SQL, they already know how to reason about materialized views.
A clean mental model for known, recurring query patterns. If you know your BI tool is going to run the same category of query, “revenue by region by day,” over and over, pre-computing that shape is a completely rational move.
Where it starts to hurt
The problems with materialized views don’t show up on day one. They show up on day two hundred, when the third team asks for a new slice of the same fact table, and you realize you’ve built four different views for four different query shapes against the same underlying data.
Combinatorial explosion of views. A materialized view is built for one query shape, one specific set of dimensions, one specific set of aggregations. The moment a stakeholder asks for the same metric sliced by a dimension you didn’t anticipate, you either eat a full table scan again or you build another view. Multiply this across a real enterprise’s worth of ad hoc analytical needs and you get view sprawl: dozens or hundreds of materialized views, each covering a narrow, brittle slice of query space.
Storage cost that compounds. Every materialized view is a physical copy of data, denormalized and pre-aggregated in a specific shape. Ten views means ten different derived datasets sitting on disk or in object storage, most of them overlapping in what they contain. This is exactly the storage space explosion that pre-aggregation has always risked, it’s just now happening on cloud storage bills instead of on-prem disk arrays.
Refresh complexity and staleness. Incremental refresh is what makes materialized views viable at scale, but it only works cleanly for a subset of queries. Simple filters and append-only aggregations incrementalize well. Joins, window functions, deduplication, and late-arriving or out-of-order data often don’t, either forcing a silent fallback to full recomputation on every refresh, quietly reintroducing the cost you thought you’d eliminated, or worse, producing incremental updates that are subtly wrong because the engine’s delta logic didn’t fully account for an upsert or a retraction. Get this wrong and you have silent staleness or silent inaccuracy: a dashboard confidently showing numbers that look current and aren’t. Get the refresh cadence wrong in the other direction and you’re burning compute keeping views fresh that barely anyone queries between refreshes.
Rigidity against new query patterns. This is the crux of the tech-debt problem. A materialized view answers the question it was built to answer, fast. It does nothing for the question nobody thought to ask yet. Analytic data products increasingly need to support ad hoc, exploratory, and agent-driven query patterns where the query shape isn’t known in advance. An agent reasoning over enterprise data isn’t going to limit itself to the twelve pre-aggregated shapes your data team anticipated six months ago. When the only lever you have is “build another view,” your data engineering team becomes a bottleneck for every new analytical question, and that’s the opposite of what an agentic data layer is supposed to enable.
Operational surface area. Every materialized view is another object to monitor, another refresh job that can fail, another thing that needs its own alerting and its own runbook when it silently falls behind. This is where the complexity Databricks promises to eliminate by unifying serving into the lakehouse quietly creeps back in, just one layer up, as a proliferating set of views instead of a proliferating set of pipelines.
The pattern across all of this: materialized views trade flexibility for speed on a known query shape. That’s a good trade when your query shapes are genuinely stable and well understood. It’s a bad trade when you’re trying to build a data product that needs to answer whatever gets asked of it, at sub-second latency, without your team pre-building an answer for every possible question.
The other half of the toolkit: indexing on open table formats
If materialized views solve speed by pre-computing the answer, indexing solves speed by making the scan itself dramatically cheaper, without giving up the ability to answer questions you didn’t anticipate. It’s worth separating two things that tend to get lumped together here. Most query engines, including, we’d assume, Reyden, ship with basic file-level pruning: min/max ranges, bloom filters, and clustering, all metadata-driven techniques that Iceberg and Delta support natively and that any engine reading those formats can take advantage of. That’s table stakes at this point, not a differentiator.
What’s genuinely new, and still underappreciated, is building an extensive indexing layer directly on top of Parquet files in an open table format, the kind of indexing that’s existed in traditional databases for decades, applied to a storage layer that was never designed with structures like inverted, range, vector, geospatial, text, or JSON indexes in mind. That’s a different engineering challenge than it sounds like at first, since it means indexing a wide range of data types on data files an engine doesn’t own and that other engines are reading concurrently, without breaking the openness that makes the table format worth using in the first place. This is the layer that determines whether you actually need a materialized view at all, or whether you needed one for every query shape, or just the handful that are genuinely aggregation-heavy at extreme scale.
A few forms worth knowing, roughly in order of how targeted they are:
Min/max and zone maps. The simplest form of pruning. Every Iceberg or Delta file (or column chunk within it) tracks the min and max value for each column. A query with a selective filter can skip entire files or row groups without reading them, no index structure required beyond metadata already written at ingest time.
Bloom filters. Probabilistic structures that let you cheaply answer “could this value possibly be in this file?” with no false negatives. Excellent for high-cardinality equality lookups, like filtering on a specific user ID or transaction ID, where min/max ranges aren’t selective enough to help. Widely supported at this point, effectively a baseline capability rather than something that sets an engine apart.
Sorted and Z-order/Hilbert-curve clustering. Physically co-locating rows that are likely to be queried together so that filters translate into contiguous, cheap scans instead of scattered reads across the whole table. This is a data layout optimization more than a traditional index, but it has the same effect: it reduces bytes scanned per query.
Beyond that baseline, a genuine indexing layer starts to look like what you’d expect from a purpose-built database, just built to operate on open Parquet files instead of a proprietary storage engine:
Inverted indexes. Map each distinct value in a column to the set of rows containing it, the same fundamental structure behind full-text search. Effective for filtering on low-to-medium cardinality categorical columns, and for multi-value fields where you need to check “does this row contain X” cheaply.
Range and sorted indexes. For numeric or time-based columns queried with range predicates, a sorted index lets you binary-search to the boundary of the range instead of scanning until you fall outside it.
Vector, geospatial, text, JSON, and other specialized indexes. A different category worth calling out on its own, because these aren’t really about pruning rows, they’re about making a fundamentally different kind of query possible at all. A vector index (HNSW and its relatives) answers “which embeddings are nearest this one in high-dimensional space.” A geospatial index answers “which points fall inside this polygon” or “what’s nearest this lat/long.” A text index answers “which documents contain this word or phrase.” A JSON index lets you filter or extract on a path buried inside a semi-structured blob without shredding the document into columns first.
None of these map cleanly onto a materialized view. A materialized view pre-computes an aggregate over a known grouping, but the unit of interest in these query types isn’t a group, it’s a single point of granularity decided at query time. You can’t pre-aggregate “nearest neighbor” the way you pre-aggregate “sum of revenue,” because the answer depends on which vector, which coordinate, or which search term the query happens to supply, not on a dimension combination you could have anticipated and rolled up in advance. A materialized view answering “what’s near this location” would need one row for every possible location someone might ever query, which isn’t pre-aggregation, it’s just storing the raw data again with extra steps. The index has to exist at the granularity of the individual word, vector, or coordinate, because that’s the granularity the question is actually asked at.
Across baseline pruning and this more purpose-built indexing layer, the common thread is that none of it changes what question you can ask. You get to keep ad hoc flexibility, whether that’s an arbitrary filter, a nearest-neighbor search, or a full-text match. What none of it does is help you when the bottleneck isn’t finding the right rows, it’s aggregating across millions of rows once you’ve found them. That’s a different problem, and it’s the one materialized views were built to solve. It’s also the problem the star-tree index was built to solve, without most of the downsides.
The star-tree index: a deep dive
The star-tree index, native to Apache Pinot, is worth understanding in detail because it sits deliberately in the gap between “index that speeds up scans” and “materialized view that pre-computes the answer.” It’s sometimes described as an intelligent materialized view, and that description is more literal than it sounds. It’s the right mental model, and the mechanism behind it is what makes the difference.
The problem it’s solving. Traditional single-column indexes (inverted, sorted, bitmap) speed up the filtering phase of a query, finding which rows match your predicates. But for aggregation-heavy queries, “give me total revenue by region by day for the last quarter,” the bottleneck usually isn’t finding the rows. It’s aggregating across however many millions of them survive the filter. No amount of filter-side indexing fixes an aggregation that has to touch every matching row. A fully materialized view fixes this by pre-computing the aggregate for every combination of dimensions you might query, but that means committing, in advance, to every dimension combination you’ll ever need, and paying the storage cost of each one.
How it actually works. A star-tree index is built over a configured set of dimension columns, in a specified split order, plus the metrics you want pre-aggregated. The build process projects the data down to just those dimensions, then for every unique combination of dimension values at each level, it pre-aggregates the configured metrics and writes those aggregates as compact, purpose-built documents, separate from the raw data. Those aggregated documents are then sorted by the same dimension split order and organized into a tree. Each node in the tree corresponds to a range of the sorted aggregate documents, and the tree is built recursively: any node with more than a configurable threshold of records is split into child nodes, one for each value of the next dimension in the split order.
The clever part is what happens at each split. Alongside the normal children, one for each concrete dimension value, the tree also builds a special “star node,” which aggregates across all values of that dimension rather than one specific value. This is what gives the structure its name and its flexibility: a query that doesn’t filter on a particular dimension can be routed through the star node for that dimension instead of forcing a fresh aggregation, without needing a separate materialized structure for every subset of dimensions you might or might not filter on.
What this buys you that a plain materialized view doesn’t. A conventional materialized view has to fully commit, at creation time, to exactly which dimension combinations it supports. Ask for a slice it wasn’t built for, and you’re back to scanning raw data. The star-tree index instead gives you a continuous, configurable trade-off between storage and query time: you choose how many dimensions to include in the split order and how deep the tree goes before falling back to raw aggregation, and Pinot fills in the rest through tree traversal. Because the aggregation is pre-computed at multiple levels of granularity rather than one fixed shape, a much wider range of queries against those dimensions gets served from the same structure, with latency that scales with the depth of the tree, not the number of rows scanned.
What it costs you. It’s not magic, and it’s not free. You’re still trading storage for speed, same as any pre-aggregation technique, just with a better dial to control the trade-off. Choosing a split order matters: dimensions higher in the order get more granular pre-aggregation, so the choice should reflect your actual query patterns. And the star-tree index, like any pre-aggregation, works on defined metrics and aggregation functions, it’s built for the class of query it’s designed for, not a general-purpose substitute for every kind of analytical workload. It’s also worth knowing that, like most pre-aggregation techniques, it’s best suited to append-heavy data; late-arriving events, out-of-order records, upserts, and deduplication are more naturally handled upstream or with other techniques, not something to lean on the star-tree index for.
The honest way to describe it: a star-tree index behaves like a family of materialized views, spanning many dimension-combination shapes at once, generated and maintained as a single structure instead of a management burden that grows one view at a time. It doesn’t eliminate the space-time trade-off inherent to pre-aggregation. It just gives you a much better position on that trade-off curve, without asking your data team to anticipate and hand-build every query shape in advance.
Complementary Solutions
None of this is an argument against materialized views. It’s an argument against a single-technique query acceleration strategy, whatever that technique happens to be. If materialized views are the only lever your team pulls to hit SLA, you will eventually be maintaining dozens of narrow, overlapping views, paying for the storage of all of them, babysitting refresh jobs, and still hitting a wall every time someone asks a question your views weren’t built to answer. That’s tech debt with a monthly cloud bill attached.
The mature answer is layered: indexes to make scans cheap for ad hoc queries, a purpose-built pre-aggregation structure like a star-tree index for the aggregation-heavy queries that need a hard upper bound on latency, and materialized views reserved for the specific, well-understood, high-value query shapes where full pre-computation genuinely is the right trade-off. That’s the difference between a data platform that can meet a growing set of SLAs on open table formats and one that’s quietly accumulating a maintenance burden it will have to pay down later, right around the time an agent starts asking it questions nobody anticipated.
| Indexes (Geo, vector, min-max, inverted, etc) | Star-tree Index (Configurable pre-aggregation) | Materialized Views (Full pre-computation) | |
|---|---|---|---|
| Best for | High selectivity query Ad-hoc scans | Aggregations needing a hard SLA | Known, high-value query shapes |
| Pro | Full query flexibility Cheap to build and store No refresh logic needed | Covers many dimension combos from one structure | Fastest possible reads Known SQL semantics |
| Con | Doesn’t help with aggregation over millions of rows No hard latency ceiling | Still a storage/speed trade Not ideal for late-arriving or upsert heavy data. | View sprawl at scale Rigid query shapes |
This is also why StarTree treats materialized views and indexing as complementary rather than an either/or choice. StarTree supports materialized views alongside its full indexing arsenal, including the star-tree index, all built directly on open table formats like Iceberg and Delta. The point isn’t to replace one technique with another, it’s to give a team the full toolkit on the data they already have, so the choice of which technique to reach for is driven by the query pattern, not by which capability happens to be missing.
The renaissance in materialized views is real. So is the reason dedicated indexing techniques exist. Teams that treat both as complementary, rather than picking one as a silver bullet, are the ones who’ll actually hit their SLAs without their infrastructure bill or their on-call rotation exploding six months from now.
