Introduction
Materialized Views (MVs) in Databricks SQL allow precomputed query results to be stored and automatically refreshed. However, when using Unity Catalog, you may encounter issues preventing the creation or usage of Materialized Views.
🚨 Common issues when using Materialized Views with Unity Catalog:
- Cannot create a Materialized View in Unity Catalog.
- Error: “Materialized Views are not supported in Unity Catalog.”
- Refresh of Materialized View fails with permission errors.
- Existing Materialized Views stop working after enabling Unity Catalog.
This guide explains why Materialized Views may not work in Unity Catalog, troubleshooting steps, and best practices for handling this limitation.
1. Check If Materialized Views Are Supported in Unity Catalog
Symptoms:
- Error: “Materialized Views are not supported in Unity Catalog.”
- Cannot create a Materialized View in Unity Catalog using SQL.
Causes:
- Unity Catalog does not currently support Materialized Views.
- Materialized Views are only supported in Hive Metastore (legacy Databricks workspaces).
Fix:
✅ Use Delta Live Tables (DLT) as an alternative to Materialized Views.
- DLT pipelines provide similar functionality for maintaining fresh query results.
- Create a DLT pipeline instead of a Materialized View:
CREATE LIVE TABLE my_dlt_table AS SELECT * FROM my_catalog.my_schema.source_table;
✅ Use standard Delta tables with scheduled updates if MVs are unavailable:
CREATE TABLE my_catalog.my_schema.cached_table AS
SELECT * FROM my_catalog.my_schema.source_table;
- Then, schedule a refresh using Databricks Jobs.
2. Cannot Refresh Materialized Views in Unity Catalog
Symptoms:
- Materialized View refresh fails in Unity Catalog.
- Permission errors when running
ALTER MATERIALIZED VIEW ... REFRESH
.
Causes:
- Unity Catalog does not support automated refresh of Materialized Views.
- The underlying tables have restricted access in Unity Catalog.
Fix:
✅ Manually refresh Materialized Views using a SQL job:
INSERT OVERWRITE my_catalog.my_schema.mv_table
SELECT * FROM my_catalog.my_schema.source_table;
✅ Ensure correct permissions for Materialized View refresh:
GRANT SELECT, INSERT, DELETE ON TABLE my_catalog.my_schema.mv_table TO `user@example.com`;
✅ Consider using Delta Live Tables (DLT) or Materialized Views outside Unity Catalog.
3. Cannot Query Existing Materialized Views After Migrating to Unity Catalog
Symptoms:
- Queries on Materialized Views return errors after enabling Unity Catalog.
- Materialized Views appear missing from
SHOW TABLES
.
Causes:
- Materialized Views are not automatically migrated to Unity Catalog.
- Databricks SQL does not recognize Materialized Views under Unity Catalog.
Fix:
✅ Recreate Materialized Views as Delta tables in Unity Catalog:
CREATE TABLE my_catalog.my_schema.mv_table AS
SELECT * FROM my_catalog.my_schema.source_table;
✅ Manually export and recreate the Materialized View data:
databricks fs cp dbfs:/mnt/hive_metastore/mv_table.parquet dbfs:/mnt/unity_catalog/mv_table.parquet
✅ Check if legacy Materialized Views still exist in Hive Metastore:
SHOW TABLES IN hive_metastore.default;
4. Workarounds for Materialized Views in Unity Catalog
Since Materialized Views are not fully supported in Unity Catalog, use these alternatives:
✅ 1. Delta Live Tables (DLT) as a Replacement for Materialized Views
- DLT automatically refreshes query results, like a Materialized View.
- Example:
CREATE LIVE TABLE my_dlt_table AS
SELECT * FROM my_catalog.my_schema.source_table;
✅ 2. Scheduled Query Jobs for Data Caching
- Store precomputed query results in a Delta table and refresh it periodically.
- Example:
CREATE OR REPLACE TABLE my_catalog.my_schema.cached_table AS
SELECT * FROM my_catalog.my_schema.source_table;
- Use Databricks Jobs to refresh the table at scheduled intervals.
✅ 3. Use Databricks SQL Warehouses for Query Caching
- Enable Query Caching in SQL Warehouses to improve performance.
- Reduce load on source tables by caching results in Databricks SQL.
5. Step-by-Step Troubleshooting Guide
Step 1: Check If Materialized Views Are Supported
SHOW CREATE TABLE my_catalog.my_schema.mv_table;
- If Unity Catalog is enabled, Materialized Views will not be supported.
Step 2: Verify If the Materialized View Exists in Hive Metastore
SHOW TABLES IN hive_metastore.default;
- If it exists in Hive Metastore but not in Unity Catalog, you need to migrate it.
Step 3: Convert Materialized Views to Delta Tables
CREATE TABLE my_catalog.my_schema.cached_table AS
SELECT * FROM my_catalog.my_schema.source_table;
- Use a scheduled job to refresh the table periodically.
Step 4: Use Delta Live Tables for Automatic Refreshing
CREATE LIVE TABLE my_dlt_table AS
SELECT * FROM my_catalog.my_schema.source_table;
- This automates updates like a Materialized View.
6. Best Practices for Using Unity Catalog Without Materialized Views
✅ Use Delta Live Tables for Real-Time Updates
- DLT automatically refreshes query results.
- Works like Materialized Views but fully supports Unity Catalog.
✅ Schedule Data Refresh Jobs
- If DLT is not an option, create a scheduled job to update a Delta table.
✅ Enable Query Caching in Databricks SQL
- Reduces compute costs by caching query results in SQL Warehouses.
✅ Use Materialized Views Only in Hive Metastore Workspaces
- If Materialized Views are required, keep them in a legacy Hive Metastore instead of Unity Catalog.
7. Conclusion
💡 Unity Catalog does not currently support Materialized Views. To work around this:
✅ Use Delta Live Tables (DLT) for automated refresh.
✅ Create Delta tables with scheduled refresh jobs instead of MVs.
✅ Enable Query Caching in Databricks SQL Warehouses.
✅ If necessary, keep Materialized Views in Hive Metastore instead of Unity Catalog.
By using these alternatives, you can achieve the same functionality as Materialized Views in Unity Catalog without running into compatibility issues.