Key generation and history preserving logs
Good day everyone.
I have job to load dimension data that has table comparison, history preserving and key generation transform to keep track of changed rows and it seems to work fine. The rare is the row data in the logs does not appear to match the real changed rows in target.
First I run the job and insert all the rows to the target (58901), then modified 40 rows on source and did run the job for second time and the key generation log displays in row count 80. However I verified on target that only 40 were inserted as expected.
I would like to know if I'm misunderstanding the logs or if my job definition has something that might be wrong.
Thanks in advance.
Dirk Venken replied
The log is correct. The History_Preserving transforms generates 2 rows for every updated input row, so it passes 80 rows: 40 inserts and 40 updates. Those 80 rows are fed into the Key_Generation. Obviously, keys are only generated for the inserts (the updated rows do have a key already ). Eventually 80 rows are written to the target table, again 40 updates, 40 inserts.