Overview
If you have a single source table from which you are inputting the data into a dimension table then you have several options to use CDC (Change Data Capture) e.g. you can use timestamp of the source table to find out the changed record, or can create triggers on base table on DML operations to capture the changes. However, the problem comes when you are inputting from a query which uses several tables. How should we implement CDC for such cases?
There will be many possible solutions for it, I am listing one, that I have implemented before with success. To illustrate this I am using Oracle database SQL syntax.
Solution
Let us take a real life example, we are constructing a dimension table for “Sales Representative”
Typically, sales management systems, like Salesforce, are designed using a normalized database which means, the database have many tables for one object like sales representative, which may be called a user in the source system. The Rep/User will have a group, a territory, a region, an area which we can define in dimension table as hierarchy and a quota.
So we have designed the dimension table using the hierarchy, we ran initial data ingestion process using query as a data source. Now we will need to implement a CDC process so that any underline changes will be recorded.
Here we have two options:
Option A
We can do a full table compare with the query output and update any changed record. This can be a slow process if we have many columns to compare and many rows to check for.
Option B
Use hash value concept; we all know hash value is unique. We can use this feature in our design to compare records. By using hash value, we just have to compare one column to see if the record is changed, which saves a lot of processing time.
Step 1: Creating a Function which will return hash value
We will create a function which will return hash value. Idea is to use this function in underline SQL queries. This function will read value as a text and return a hash value for it..
FUNCTION salesrep_hashvalue (p_input_str VARCHAR2)
RETURN VARCHAR2 IS
l_str VARCHAR2(50);
BEGIN
l_str := dbms_obfuscation_toolkit.md5(input_string => p_input_str);
RETURN l_str;
END salesrep_hashvalue;
Step 2: Add a new column in the dimension table
To easily compare hash value of the record let us store that value along with the record.
Step 3: Write ETL code
For comparing/matching the record use primary key. If record does not exist then insert a new record otherwise use hash value column to compare with the query resulted hash value, if they are not same only then update the dimension record.
Conclusion
By using such a technique we can implement the CDC for multi table based dimension. This approach can also be used in your Master Data Management, and, can also be useD for maintaining data integrity across databases as per latest data compliance standards in GDPR.