Update the SCD_Carrier Dimension

To see how Type 2 SCDs work, imagine that British Airways have changed their name to New British Airways. (I simulated this by executing the follwing update to the Flightdata table : update "public"."flightdata" set "UNIQUE_CARRIER_NAME" = 'New British Airways' where "UNIQUE_CARRIER" = 'BA';)

Now I will run the workflow to load the SCD_Carrier again and see what happens.  To check the result, I execute the following query against the SCD_Carrier table :  SELECT * FROM "public"."SCD_carrier" WHERE "UNIQUE_CARRIER" = 'BA' . Below you can see a screenshot of the result.

Note that:

• there are now 2 records with the same UNIQUE_CARRIER value BA.

• the 2 records have a different Surrogate Key (244 and 488)

• the new record has the new name (New British Airways), the highest of the two Surrogate Key values and the Valid flag set to 'Y'

To put things together, we will also have to make changes to the way we load our Fact Table. We will see this on the next page