Hi Dharinip,
How are you doing today?, As per my understanding, Right now, Materialized Views in Databricks always do a full refresh, so they can’t be updated incrementally like your streaming tables. Since your Iron, Bronze, and Silver layers are already handling changes incrementally with SCD Type 2, the best way to optimize your Gold layer is to replace the Materialized View with a Delta table that updates incrementally. Instead of refreshing everything, you can use a MERGE INTO statement to update only the new or changed records from the Silver layer. This way, your Gold table will always stay up to date without the heavy full refresh. Materialized Views are great for static or pre-aggregated data, but for your case, an incremental Delta table will work much better. Let me know if you need help setting it up!
Regards,
Brahma
