prepare incremental on views

Hi ​@rsanchez 

I have a old guide

https://legacysupport.timextender.com/hc/en-us/articles/115001490606-Create-an-incremental-view

It is what I suggested before, but I would probably use the DW_TimeStamp field instead of adding the timestamp field to be used for this.

The DW table needs to run with incremental load as well, the only benefit of adding the above to the views, is that it only retrieves the new data with the view.

BTW, why do you have views instead of normal tables and how do they look in the raw state?

Yes, we do have normal tables at the MDW level where we have the standardized data platform and models, but we are abstracting them form all the business logic we need to perform for the different TPAs so they can fit into the Standard MDW.  So basically it, Source → Ingest (DLAKE) → Incremental to Raw (prepare) → prepare views to apply rules and transformations → Map to MDW corresponding Dim or Fact table.  

I will be checking the article, thanks

Hi,

while views can be faster than physical tables in specific situations, if you are trying to speed up load - I expect there will be parts of your transformations that are better served by regular tables (like incremental load situations).

If you are really getting extra speed from Premium Memory Optimized vs Premium hardware, you might see that you can drop to Premium if you turn some of your views into tables. But that would require some metrics checking and having a look at the query store.

At the moment views are working fine, and the load time is manageable. We already downloaded into premium and still running in 2 hours or less despite the fact that we are not loading incremental from the view to the DW.  We are onboarding another source, completely different, and thanks to the views the transformation to the standard is pretty straightforward. We just need to find a working solution to incremental load to the DW. We already though about using the _I table to use the last incremental date as a filter. If that works, we will probably end up loading in less than an hour.

​@rsanchez is your issue resolved? If so please help us by marking a best answer above. If you have any follow up questions please let us know. Thanks

Hi, not yet. We are evaluating the incremental view, but we are still validating the model.  Once validated we can implement the incremental view and see if it is what we are looking for.  That is why we don't have a better answer yet.

Ok, This was the outcome of the testing efforts. We are loading data into the raw section, we create a view for business logic and complex transformations, we drag that view into the prepare MDW where we materialize that table. We exposed the updatedate from raw and configure de mdw to be incremental using the updatedate. The result was that on most tables data werent updated correctly. There were discrepancies on the mdw. Should test now using the dw_timestamp in lieu of the actual updatedate  of the source contained in raw? 

Hi ​@rsanchez 

Does the view contain dates with a higher value than before the last execution, then it should work. The main reason to use DW_TimeStamp is that the date will be updated every time the row is updated or a new row is added in the source table used for the view. With updatedate, this change supposedly would come from the data source.

We will be trying this week with the dw_Timestamp and another idea we have.  If make it work, I will post it so everyone can benefit from that. Lets see :)

Okay, I think we got this to work. This is what we did:

We ingest the data normally and then move it incrementally to the Prepare Instance (Raw/Staging) using the UpdateTime field from the source. From there, we created the “Contract View,” which contains the mappings and business rules for the materialized tables in the Prepare MDW.

The trick was that, instead of exposing only the UpdateTime from the main table, we created a computed field that selects the MAX(UpdateTime) across all the tables involved in the Contract View. This way, if any of those tables has an UpdateTime greater than the last run, the computed field gets that value. Since it meets the incremental cutoff criteria, TX triggers the incremental load of the materialized table based on the view.

We tested this across several tables, and so far, it’s working like a charm. We’ve already reduced the total load time from 12 hours to 8.5 hours, and that’s without optimizing all of our data domains and pipelines yet.