- design dimension table that includes the following columns:
- pk: unique identifier for each dimension record
- Natural Key (nk) : a set of attributes that uniquely identify a record typically buisness related information
- Hash Value: a hash of the natural key and other relevant attributes used to quickly detect change
- effective start date (start date): The date when the record become active
- effective end data (End Date): The date when the record is no longer active
- current flag: a flag indicating if the record is the current version
- initial load:
- load your initial dimension data into the dimension table
- set the start date to the inital load and set the end date a far future date (e.g., 9999โ12โ31)
- set the Current Flag to 'Y' for the initial version
- Hash Function:
- Create a Hash Function that takes the natural key and any other relevant attributes and produces a hash value
- change Detection:
- When new data arrives calculate the hash value for the new record using the hash function , compare the hash value of the existsing record in the dimension table with the same natural key , if the hash values are the same => no changes are detected if the hash value are diffiren , changes are detected , and you proceed with next steps
- update Current record:
- set the end date of the current version of the record in the dimension table to the date before the change. set the current flag of this record to 'N'
- Insert new Record:
- insert a new version of the current record with the updated attributes , the new hash value , new start date (the date of the change) , a fat-current END date , and a CurrentFlag of 'Y'
- Historical Queries: * when querying the dimension table , filter the data by the current Flag and the desired date range to get the correct version of the data at that point in time
-
identify:
- records to be inserted (from the incremental updates)
- record to be updated (in the existsing data)
-
if a record with existing primary key has got some fields changed it will have to effects:
- INSERT that record from incremental update to the main table
- Update (expire) the existsing reord of the main table
-
if a record with existing primary keys has got unchanged fields , it will have no effect
-
all new records will have only one effect they will be inserted in the main table
-
construct a merge key field : this additional key will be used in matching the records of incremental updates with exiting data
-
if a record in the incremental update can update the record of existing data , we'll generate two copies of that record
- one copy with original merge key will be used to update the existing data
- the other copy will have NULL as merge key , hence will not match the existing data and will get inserted
-
the mechanisme of introducing merge key in the intermediate dataframe will keep the original fields unchanged
-
if a record in the incremental update is not going to update the existing data , the merge key will be the same as the original merge key
-
to generate the records participating in the merge:
- select evryting from the incremental updates and add an additional field mergeKey (hash)
- join the incremental update with active and existing data on primary key
- if there is a change in the field values of incremental update , select the record with NULL as mergeKey
- union the above two dataframes.
- take advantage of the partition column in the filter if it doeas not change across records (boost performance)
- in a single batch if there are multiple updates , apply window function on the incremental batch to expire all the records , except for the most recent ones
- the merge keys can be a collection of multiple keys (like composite keys)
- can keep a list of fields over wich you decide if records have changed , don't consider a user record to be changed if his name updated but consider it to be changed if his address or date of birth has beeen updated