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