| Topic | View | Materialized View |
|---|---|---|
| What it is | A stored query; results are computed each time you query it | A precomputed table of the query’s results, stored on disk |
| Performance | Same as running the underlying SELECT every time | Fast to read (results already computed) |
| Freshness | Always current (reads base tables live) | Can be stale until it’s refreshed (on a schedule or on demand) |
| Storage cost | ~None (just metadata) | Consumes storage (it’s physically materialized) |
| Maintenance | No refresh needed | Needs refresh/maintenance; refresh costs compute |
| Good for | Convenience, security (row/column masking via views), reuse of logic | Dashboards/BI and heavy aggregates that repeat often |
| Failure surface | Same as base query | Adds refresh jobs, dependencies, and staleness management |
Databricks-specific notes (Unity Catalog / DBSQL)
- A materialized view (MV) is stored as a Delta table and maintained by Databricks; you can schedule it or refresh manually.
- Typical syntax:
-- Logical view CREATE OR REPLACE VIEW prod.dw.v_sales AS SELECT d.date_key, SUM(amount) AS revenue FROM prod.dw.fact_sales f JOIN prod.dw.dim_date d USING(date_key) GROUP BY d.date_key; -- Materialized view (precompute + keep it fresh) CREATE OR REPLACE MATERIALIZED VIEW prod.dw.mv_daily_sales SCHEDULE EVERY 15 MINUTES AS SELECT date_key, SUM(amount) AS revenue FROM prod.dw.fact_sales GROUP BY date_key; -- On-demand refresh (if needed) REFRESH MATERIALIZED VIEW prod.dw.mv_daily_sales; - Privileges: readers need
SELECTon the view/MV; to rebuild an MV you also need theREFRESHprivilege (and of courseUSE CATALOG/SCHEMAon the container). - Trade-off: MVs buy latency at the cost of storage + refresh compute and possible staleness. Views are always up-to-date but can be slower.
Quick “when to use which”
- Use a view when you want a reusable, secure, always-fresh SQL abstraction.
- Use a materialized view when many users hit the same heavy query (e.g., daily revenue by product) and you want sub-second BI with predictable cost.
