I have 2 type of tables to populate the data-warehouse with every day, lookup tables or configuration tables with few 100s records, and thats easy where i just truncate and refill the table.
but for transaction tables, that have many records, i usually increment, that is i run the ETL daily to add yesterdays records.
i have 2 problems that i face always
- when the job fails for any reason (i lose that Days transactions)
- when for any reason the job run twice or i run it twice (i get duplicates)
now i am trying to design a way where i over come these 2 problems as well as am trying to develop the ETL in such a way that it can auto fix it self incase any of these events occur.
i want it to check if there are missing days and run run the ETL for that day, and check if there are duplicates and delete them.
below are ways i though of 1. i take in the last 5 days regardless, every day the ETL runs, deletes the last 5 days and refill. 2. i check the destination tables if they have missing dates in the last month and then i query the source with the missing days.
keeping in mind that the source is a huge table in a production environment that i have to optimize my query to the maximum when requesting from it.