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.
