Oracle to Azure Synapse Data Warehouse Migration Validation

Advantage – This is the easiest way to handle the Slowly Changing Dimension problem as there is no requirement to keep track of the old/historical records (data).

Disadvantages – By applying this methodology, it is not possible to keep track back in the history of changes made periodically for a record, for example in the below example the company/organization will not be able to know if James was in New York earlier.

Example:

Source Table: Customer

After Migration:  

Let’s assume customer James has moved from New York City to California, in this case, SCD Type 1 will overwrite the existing record with the newly updated record as shown in the below dimension table. 

 Target Table:  Customer_Dim 

Validation to be performed:

Count Check – Validate that the count of updated records in the target table matches with the updated count’s records in the source table.

Duplicate check – Validate that the uniqueness of key columns in the target(dimension) is maintained.

Transformation Check – Validate that the transformation logic applied to any individual columns is transformed correctly and the records are getting loaded as per the mapping document.

Referential Integrity Check – Validate that the parent table (Primary Key column) has the same number of counts as the child table (Foreign Key column).

When to use SCD Type 1:  

A Slowly Changing Dimension Type 1 should be used only when it is not necessary for the data warehouse to keep track of historical changes.

SCD Type 2, is used to keep the history of old data by adding a new record in the target table if any record was updated in the source table. Therefore, both the original and the new record will be present, and the new record gets its own primary key.

Advantages – SCD type 2 allows us to accurately keep all historical information.  

Disadvantage – This will cause the size of the table to grow fast. In cases where the number of rows for the table is very high to start with, storage and performance can become a concern.

Example:

Source Table: Customer  

After Migration:  

Let’s assume customer James has moved from New York City to California and from California moved to Bloomberg in this case, SCD Type 2 will add a new record with the existing record as shown in the below dimension table.

Target Table: Customer_Dim  

Validation to be done: 

Delta Check – Verify whether the historical data is preserved, and whether new records are being created or not.

Duplicate Check – Verify the uniqueness of records (Minus Query).

Referential Integrity Check – Validate that the parent table (Primary Key column) has the same number of counts as the child table (Foreign Key column)

When to use SCD Type 2: 

SCD Type 2 should be used when it is necessary for the data warehouse to track the historical changes.

In Type-3 Slowly Changing Dimension, a few technical columns such as Original_City, Current_City, and Effective_Date are added as new attributes to store the changed values and their cities.

Attribute Original_City indicates the original city (before update/changed city) of the customer, while attribute Current_City indicates the present city (latest update/changed city) of the customer. There will also be a column that indicates when the current value becomes active.

Advantages:

• • • SCD Type 3, does not increase the size of the table since new information is updated in an individual attribute. 
    • This allows us to keep some part of history.

Note: SCD Type 3 is minimally used in an organization.  

Disadvantages – Type 3 will not be able to keep all historical changes where an attribute is changed more than once. For example, if James later moves to Texas on December 31, 2010, the California information will get lost.

Example:  

Source Table: Customer  

If any updates or changes are made in the source table, then the SCD Type 3 table will capture the records as shown below.  

Let’s assume, James moved from New York to California, the original information gets updated, and the records will get populated as the below table (assuming the effective date of change is January 01, 2010). 

 Target Table: Customer_Dim  

Validation to be done:

fdkghdfjghjdfhgjhdfjhgjdfs

When to use SCD Type 3:

Type 3 Slowly Changing Dimension should only be used when it is necessary for the data warehouse to track historical changes, and when such changes will only occur a finite(limited) number of times.