Choosing between a materialized view and a job-maintained Delta table for an expensive SQL query

[Hamü123]

I am relatively new to Databricks and do not yet have much experience with materialized views, Delta tables, incremental processing, or the computational costs associated with refreshing persisted results.

I currently have a Databricks SQL view that combines five data sources using joins, aggregations, and other transformations. Querying the view takes approximately three minutes.

Several downstream queries join against this view. Since a standard view stores only the query definition, the underlying query is executed again whenever the view is referenced. This significantly increases the runtime of the downstream queries.

The underlying data contains orders. New orders are added throughout the day, and recently created orders may also be updated. The result therefore needs to be refreshed regularly, probably one to three times per day.

After doing some research, I found the following possible approaches:

1. Keep the standard view, which always returns current data but is slow.

2. Create a materialized view and refresh it on a schedule.

3. Create a Delta table that is periodically rebuilt by a Databricks job.

4. Create a Delta table that is maintained incrementally using INSERT or MERGE.

My current understanding is that a materialized view and a job-maintained Delta table would both provide faster reads but would only contain data up to their most recent refresh. I would therefore need to balance data freshness against the computational cost of refreshing the result. So my question is:

• Which of these approaches would generally be most appropriate for this type of workload, and why?

I expect to encounter this situation regularly in my work, so I would like to understand the underlying concepts and trade-offs rather than only find a solution for this specific query.

[aayush_410]

• Databricks MVs aren't a dumb full-recompute-on-schedule object. They're backed by Delta under the hood, and refreshes use an incremental-refresh optimizer ("Enzyme") that tries to update only the changed rows in the result, not recompute everything. Check refresh history after setup — it tells you per-run whether it was Incremental or Complete.
• Your actual bottleneck right now is that a standard view re-runs the full 3-minute query on every downstream read. Switching to either an MV or a scheduled CTAS Delta table already fixes that pain point — 3 minutes, 1–3x/day, is cheap in absolute terms; it's just being paid too often.
• MV vs. scheduled CTAS Delta table: MV gets you incremental refresh for free if your join/aggregation shape qualifies, and falls back to a full refresh transparently if it doesn't — so it's strictly no worse than the CTAS approach, with less code to maintain.
• Manual MERGE-based incremental Delta table — save this for later. It's the cheapest option per-refresh at scale, but you own the "what changed" logic, and it gets tricky fast with aggregations: one updated order can affect an aggregate spanning other orders in the same group, so you often need to recompute the whole group, not just the touched row. Only worth the complexity if you confirm (via refresh history) that the MV keeps falling back to Complete refreshes and that cost is actually a problem.

[data_pulse]

Adding to the above, can do one small validation before deciding between MV and a custom Delta/MERGE solution.

Databricks now lets you check whether the MV query is structurally eligible for incremental refresh:

EXPLAIN CREATE MATERIALIZED VIEW mv_orders AS
SELECT ...

That output can tell you whether the query is incrementally refreshable and which part of the query may prevent it. check this.

Also do check the source Delta tables. For the best incremental refresh behaviour, Databricks recommends row tracking, deletion vectors, and change data feed where applicable. For example:

ALTER TABLE orders SET TBLPROPERTIES (
  delta.enableDeletionVectors = true,
  delta.enableRowTracking = true,
  delta.enableChangeDataFeed = true
)

Then create the MV and test a few realistic refreshes:

• small batch of new orders
• updates to recent orders
• a larger batch of changes

Under the default AUTO refresh policy, it does not guarantee every refresh will be incremental. It can still choose a full recompute if its cost model thinks that is cheaper.

For this workload, it gives you a clear plan after validating above:

• MV incremental most of the time → probably keep the MV.
• MV repeatedly falls back to full refresh and refresh cost matters → then consider custom incremental Delta/MERGE logic.

For this workload, where the same expensive transformation is reused by multiple downstream queries and freshness of 1-3 times per day is acceptable, MV is a good candidate to start. Measure actual refresh behaviour before going down the path of maintaining your own incremental logic.