How to Determine if Materialized View is Performing Full or Incremental Refresh?

[guiferviz]

I'm currently testing materialized views and I need some help understanding the refresh behavior. Specifically, I want to know if my materialized view is querying the full table (performing a full refresh) or just doing an incremental refresh.

From some tests I did on a medium-sized table (similar to the sample table created in this guide), I observed the following:

• When I add new rows, the refresh takes about 10 seconds.
• A full refresh takes about 1 minute.
• When I delete some rows, the refresh also takes about 1 minute.

Based on these observations, I suppose that a delete operation triggers a full refresh. However, apart from the execution time, I have nothing else to validate my theory.

Does anyone have any tips or methods for identifying whether a materialized view is doing a full refresh or an incremental refresh in Databricks? Thanks!

[Alberto_Umana]

Hi @guiferviz,

To determine the type of refresh used, you can query the Delta Live Tables event log. Look for the event_type called planning_information to see the technique used for the refresh. The techniques include:

• FULL_RECOMPUTE: Indicates a full refresh.
• ROW_BASED or PARTITION_OVERWRITE: Indicates an incremental refresh.

You can use the following SQL query to check the refresh type

SELECT timestamp, message

FROM event_log(TABLE(<fully-qualified-table-name>))

WHERE event_type = 'planning_information'

ORDER BY timestamp DESC;

Replace <fully-qualified-table-name> with the fully qualified name of your materialized view, including the catalog and schema.