scd slow changing demension how can i detect changes?

Question

can i detect changes in my ODS tables before inserting it in dimension table in the DWH , i use sql and pentaho for data alimentation for information i use 4 tables to alimente my demension table ! so how can i detect changes in the 4 tables before using them ?

Answer 1

There two transformations steps that can help you comparing the content of two tables, Merge rows (diff) or Table compare.

You could keep a copy of the tables and each time you run your process compare the actual content with the content of the last copy, although that approach is not performance wise if the tables are too big.

Or if your database allows auditing of changes, you could activate that audit and just retrieve the rows your auditing say have been changed since last load.

There's also the option of using in the database a trigger that assures the modification date is updated each time a row is changed, so using the column where you store the modification change you can retrieve the rows changed.