Azure Databricks - Materialized Views

Question

All,

Isnt there support for Materialized View in Delta Table. I tried to run the code in a notebook and I got the below error

grajee

%sql
CREATE MATERIALIZED VIEW dbsqlengine.mv1
AS SELECT *
FROM dbsqlengine.employees;

Must provide 'UP_TO_DATE SNAPSHOT' for 'CREATE MATERIALIZED VIEW' statement(line 1, pos 0)

Answer 1

Hello @Gopinath Rajee ,

Thanks for the question and using MS Q&A platform.

Unfortunately, you cannot CREATE MATERIALIZED VIEW directly in Azure Databricks Delta Tables. This is an excepted behaviour if you create materialized views on the delta tables.

The Delta change data feed represents row-level changes between versions of a Delta table. When enabled on a Delta table, the runtime records “change events” for all the data written into the table. This includes the row data along with metadata indicating whether the specified row was inserted, deleted, or updated.

Note: Delta change data feed is not enabled by default. The following use cases should drive when you enable the change data feed.

• Silver and Gold tables: Improve Delta performance by processing only row-level changes following initial MERGE, UPDATE, or DELETE operations to accelerate and simplify ETL and ELT operations.
• Materialized views: Create up-to-date, aggregated views of information for use in BI and analytics without having to reprocess the full underlying tables, instead updating only where changes have come through.
• Transmit changes: Send a change data feed to downstream systems such as Kafka or RDBMS that can use it to incrementally process in later stages of data pipelines.
• Audit trail table: Capture the change data feed as a Delta table provides perpetual storage and efficient query capability to see all changes over time, including when deletes occur and what updates were made.

Enable change data feed: You must explicitly enable the change data feed option using one of the following methods:

Read changes in batch queries: You can provide either version or timestamp for the start and end. The start and end versions and timestamps are inclusive in the queries. To read the changes from a particular start version to the latest version of the table, specify only the starting version or timestamp.

For more details, refer to Azure Databricks - Change data feed.

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators