Forum Discussion
Materialized View Optimal Refresh - Always Doing Full Refresh
Hi EricCarlson ,
what you are seeing is expected. Your materialized view only refreshes incrementally for new rows. When existing rows are updated, Fabric cannot detect those changes with the current setup, so it performs a full refresh.To make updates refresh incrementally, the view must include Delta’s change-tracking metadata. If you don’t want those fields exposed, use a two-step approach: a staging view with change tracking, and a clean view for reporting.This ensures incremental refresh works for both new and updated rows.
Thank you.
Thanks everyone for your quick responses.
I did change my code to use "saveAsTable" and that might have been the fix. I did not change anything else, nor add any other columns to my select statement, and I was finally able to see an incremental refresh.
One thing I noticed through, is that if there were updated rows in the source data, it always did a full refresh. If there was just new rows added, it would do the incremental refresh.
Is this expected behavior? If so I was wondering if there are any workarounds or different architectures I could consider. I am sourcing data from cosmos which IS doing to be updated, and since I am going to be dealing with 10's millions of rows, but only a few thousand daily adds/updates, I don't want a full refresh on my materalized views. I really just want to 'upsert' the new/changed data into the materialized view.
I thought about just appending all inserts/updates into my source table, so it's always NEW rows, to make the materalized view work. But I have not done this before, I am concerned with an ever growing rate of data in this source table, and then i have to only pull the most recent record for each unique ID out of this source, to put into my materalized view. It sounds complicated, hopefully there is a better way.