1

I work for a finance company. We need to track exact value dimensions at the time of the transaction. We try to load data incrementally into the warehouse ~ 15 mins, and in this period, we could see a dimension with the exact business key change multiple times (multiple records are collected). Usually, we write scripts to pick the latest of all the changes in 15 min window. But in our case, I want all those changes to be loaded into dimension table. How can this be implemented?

EDIT:

Examples in same Batch:

Business Key, Name, email (scd 2), Created_at
1, xyz,  xyz@gmail.com, 1/1/21 10:00 AM
1, xyz,  abc@gmail.com, 1/1/21 10:05 AM


Expected changes in dimension

SK, BK, Name, Email, Effective_date, Expiration_date, Current

1, 1, efg@gmail.com,  01/01/1900 0:00 AM, 1/1/21 9:59 AM, N
--- New changes from batch ------
2, 1, xyz@gmail.com,  01/01/2021 10:00 AM, 01/01/2021 10:05 AM, N
3, 1, abc@gmail.com,  01/01/2021 10:05 AM, 12/31/9999 00:00 AM, Y
  • 1
    I’m not sure what answer you are looking for other than you need to capture all the changes made to the source tables for your dimensions? Are you asking how to technically implement this? If you are, please provide more information e.g. what your source DBMS is, how you are currently identifying and extracting changed records, etc – NickW Aug 13 '21 at 07:53
  • @NickW I am looking for kind of sql query I need to implement in order to push all the changes to SCD2 while running each batch. –  Aug 16 '21 at 14:46
  • Are you asking how to to identify and select all the changes in your source system, or how to update the same dimension record multiple times in the same batch, or both - or something completely different? It might be helpful if you provided (in your question, not as a comment) some sample data and what result you are trying to achieve with it in a dimension table (keep it simple - it doesn't need to be a real-world example as long as it demonstrates the issue) – NickW Aug 16 '21 at 16:28
  • how to update the same dimension record multiple times in same batch. I will add some sample data. –  Aug 16 '21 at 16:31
  • Please also tag your question with the DBMS you are using as the answer may well be DBMS-specific – NickW Aug 16 '21 at 16:38
  • @NickW I have added the example. –  Aug 16 '21 at 16:42

0 Answers0