Thursday, April 7, 2022

Anti-pattern: Using Multi-Active SAT to model data with multiple records for the same Business Key that arrive one Micro-batch

When using a Change Data Capture (CDC) tool it is possible to get multiple records for the same Business Key in a single micro-batch. Loading this data as-is will will result in multiple records for the same Business Key for the same LOAD_DATE. This is the incorrect loading pattern. A regular Satellite should have only record per Business Key per Load Date i.e. the BK + LOAD_DATE combination should be the Primary Key of the Satellite. Multi-active SAT is an exception.

However having multiple records for a single Business Key in a Micro-batch is not Multi-active. This is just incremental data where multiple increments were captured in a single Micro-batch. This needs to be captured in a regular Satellite. The correct way to add this data to the SAT is to increment the nanoseconds on the LOAD_DATE for each of the record per Business Key that is present in the Micro-batch at the time of the SAT Load. This will ensure the uniqueness of the BK + LOAD_DATE. 

BK + LOAD_DATE will still be the Primary Key


No comments:

Post a Comment