03-06-2023 09:45 PM - edited 03-06-2023 09:46 PM
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.
Solved! Go to Solution.
03-07-2023 02:26 PM
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.
03-07-2023 10:06 PM - edited 03-07-2023 10:07 PM
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
03-08-2023 06:21 AM
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 ).
03-09-2023 03:06 AM
Thanks for the help