I have developed an ETL to load data from oltp database to another database (let’s call it DWDb). The ETL stages data and loads it into the final table.
Every one hour I perform an incremental load by truncating staging table, loading the staging table with changes since last 7 days (as existing data can change), and then doing an update/insert on the final table based on the key.
When the reporting application fetches the data from the DWDb, and during that time if the incremental load is running, I am observing 2 issues:
-
blocking of either the etl or the reporting application query. Is there a solution like sp_rename or partition switching to handle this?
-
inconsistent data wherein some tables have latest data and some don’t yet have latest data. A possible solution for this is to use entire load inside a transaction?
What are my options to allow the reporting application to fetch consistent data across all tables, without blocking?
For a full load I understand there are solutions like partition switching, schema switching, table renaming to ensure minimum downtime. I’m looking for some pattern that is followed to handle this in an incremental load scenario.
9