Showing results for 
Search instead for 
Did you mean: 

Update and add values in a materialized view using incremental load


Hi all,

I have quite a big MV that's takes about and 1.5 hours to finish loading, I've tried using incremental load but it doesn't seem to update values only append. I want to know if there's a way to keep data up till a certain date, and everything after that essentially gets reloaded.

For example, I have data from 2018 till current day, but I only want records in 2023 to be updated or added, if I need to reload data from 2018 I can use a full load, but I want to use incremental load to only have an effect for 2023 data.



I have seen this before ( and in fact am about to implement a solution again ) 

Create a schema for your historic data and create a table w/in  ( I use < beginning of month ) but whatever works for you )

In your main schema, edit the data source to only bring in >= beginning of month

Then add a second data source querying the historic Incorta table.    Incorta queries are really fast so you'll cut down the load time by a factor of a lot.   

Because the source structure is identical you'll be doing essentially a UNION ALL w/disparate data sets and whatever formula columns you already have can remain intact and will continue working as before.

Schedule the main schema as a full load which will run quickly since it's only querying current month from the DB and the rest of the data from the historic schema.     


Thank for the response

Do you think this will work for a Materialized View, as the only way to load the historical data and current data is through a MV

It will work via a MV. 

The factor that drove me to using a multi-source Incorta table is that you cannot control the load order for MVs  ( they always load after tables in the schema have loaded ).


Ok perfect

Thanks for the help