topic Update and add values in a materialized view using incremental load in Data & Schema Discussions

Update and add values in a materialized view using incremental load

keenan — Tue, 07 Mar 2023 05:46:21 GMT

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.

Re: Update and add values in a materialized view using incremental load

RADSr — Tue, 07 Mar 2023 22:26:57 GMT

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.

Re: Update and add values in a materialized view using incremental load

keenan — Wed, 08 Mar 2023 06:07:40 GMT

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

Re: Update and add values in a materialized view using incremental load

RADSr — Wed, 08 Mar 2023 14:21:47 GMT

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

Re: Update and add values in a materialized view using incremental load

keenan — Thu, 09 Mar 2023 11:06:42 GMT

Ok perfect

Thanks for the help