Data Integration Challenge – Identifying changes from a table by a Scratch

In scenarios when a table in the staging area or in the data warehouse needs to be queried to find the changed records (inserted or updated), we can use the Scratch table design. Scratch table is a temporary table that can be designed to hold the changes happening against a table, once the changes are noted by the required application or process then the Scratch table can be cleaned-off.

The process to capture the changes and the clean up would be designed as part of ETL process. The scenarios where to use this concept and the steps to use the Scratch table is discussed below:

Steps to use Scratch table

1. Create a Scratch table ‘S’ of structure to hold the Primary Key column value from the table ‘T’ that needs to the monitored for changes
2. 
   
3. In the ETL process that loads the table ‘T’ add the logic in such way that while inserting or updating a record into table ‘T’ we insert the Primary Key column values of the record into the Scratch table ‘S’
4. 
   
5. If required while inserting the record into the Scratch table ‘S’ have a flag column that says ‘Insert’ or ‘Update’
6. 
   
7. Any process that needs to find the changes would join the Scratch table ‘S’ and the table ‘T’ to pull the changed records, if it just needs the key directly access ‘S’
8. Once the changes have been pulled and processed, have a process that would clean up the Scratch table
9. 
   
10. We can also bind the Scratch table ‘S’ to be always loaded to the memory for higher performance
11. 
    

When to use Scratch table

1. When we have a persistent staging area, using Scratch table would be ideal choice to move the changes to the data warehouse
2. 
   
3. When the base table ‘T’ is really huge and only few changes happen
4. 
   
5. When the changes (or the Primary Key values) in table ‘T’ are required by multiple processes
6. 
   
7. When the changes in table ‘T’ is to be joined with other tables i.e., now the Scratch Table ‘S’ can be used as the driving table in joins with other tables which would give better performance since the Scratch table would be thinner with few records
8. 
   

Alternate Option: Having a flag or a timestamp column in the table ‘T’ and having an index on it. Having an index on Timestamp is costly and a bit map index on the flag column may be seen as an option, but the aspect of updating the column during updates, huge volume and in scenarios of joining with other tables this would be a disadvantage, have seen Scratch table to be a best option. Let me know the other options you have used to handle such situations…

To add more variety to your thoughts, you can read it More Data Integration Challenge