Update and add values in a materialized view using incremental load

[keenan]

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.

[RADSr · Accepted solution]

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.     

[keenan]

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

[RADSr]

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 ).

[keenan]

Ok perfect

Thanks for the help