Skip to main content
To homepage To homepage
Question

prepare incremental on views

  • April 5, 2026
  • 4 replies
  • 28 views
rsanchez
Starter

Incremental Load Strategy for Data Warehouse Layer (Prepare Views → DW
 

Hi everyone,
We’re working on a project for a TPA that provides property and casualty services to smaller P&C companies. The architecture connects to multiple disparate sources that ultimately feed into a standardized data warehouse.
Here’s our current setup:
Architecture:
We’re creating a raw schema in Prepare for each data source, then using views to abstract the transformation logic. The DW maps from those views. So the flow is: Source → Ingest → Prepare (raw) → Prepare (views) → DW.
What’s working:
Incremental loads are running well from Source to Ingest and from Ingest to Prepare raw — no issues there.
The challenge:
The DW layer currently runs as a full load every time. We’re moving roughly 200M rows per company. Load times are about 1:45 hours, which is manageable, but we’d like to bring that down with incremental loads. It would also reduce the vCore demand — we’re currently on 8 vCores Premium Series with memory-optimized configuration.
The question:
Is it possible to implement incremental loads on the DW layer when the source for those tables is Prepare views (rather than direct table mappings)? If so, what’s the recommended approach?
Any guidance or experience with this pattern would be greatly appreciated.

Ray

4 replies

Thomas Lind
Community Manager
Forum|alt.badge.img+5
  • Thomas Lind
  • Community Manager
  • April 7, 2026

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?

    rsanchez
    Starter
    • rsanchez
    • Author
    • Starter
    • April 7, 2026

    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

    Ray
    rory.smith
    TimeXtender Xpert
    Forum|alt.badge.img+8
    • rory.smith
    • TimeXtender Xpert
    • April 8, 2026

    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.

    rsanchez
    Starter
    • rsanchez
    • Author
    • Starter
    • April 8, 2026

    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.

    Ray
    Terms and ConditionsCookie settingsAccessibility statement