You have 2 tables which are loaded by a Source Based Incremental Load.
“Customers” and “CustomerCategories”.
Let's say you add the “category” label from “CustomerCategories” to the “Customers“ table:
1 Load “CustomerCategories”
2 Load “Customers”
3 Lookup the “category” label and add it into Customers
4 Someone changes for instance the “category” label with ID =1 from "A" to "A+"
5 load “CustomerCategories” (modified record with ID = 1 gets loaded and updated)
6 Customers with Category ID 1 will not be changed in the way TX works now.
Is there a way to enable the “Customers” table to look at the changes in its adjacent tables, and take only those records which can be joined to the changed records and put them back in the raw step or just update them?
This way you would have a cascading update of your changes, and would allow for even faster processing.
2 options come into mind:
- Enable cascading update option on fields which alters the table
- Use the adjacent "CustomerCategories" Raw table to check which records match and reprocess those from "customers" valid table by adding them back to the "Customers" raw table