HOW TO HANDLE HISTORICAL DATA IN DATA WAREHOUSE IMPLEMENTATION

Slowly Changing Dimensions (SCD) are a common pattern in data warehousing, particularly in scenarios where data is changing slowly over time. In this blog post, we will discuss the best practices for implementing SCD in a Big Data scenario using SQL code, and we will use a real-world finance example. We will also use AWS Redshift as the data warehouse platform.

Example Use Case:

We will consider a scenario where a financial services firm is maintaining a data warehouse to store information about their clients, including their account information, transaction history, and personal details. As new clients are added, existing clients may change their personal details or account information, and some may even close their accounts. To ensure that the data in the data warehouse is accurate and up-to-date, we need to implement SCD.

Best Practices:

There are several best practices to follow when implementing SCD in a data warehouse:

1. Identify the Slowly Changing Dimensions:
2. First, identify the dimensions that are slowly changing. In our example, the account information and personal details of clients are slowly changing dimensions.
3. Choose the SCD Type:
4. Next, choose the SCD type to use. There are three SCD types:

• Type 1: Overwrite old data with new data. This is appropriate when historical data is not important and only the most recent data is needed.
• Type 2: Add new rows for new data. This is appropriate when it is important to maintain historical data and track changes over time.
• Type 3: Add columns for new data. This is appropriate when only a few attributes of a dimension change over time and it is not important to maintain a full history of changes.

In our finance example, we will use SCD type 2 to track changes over time.

1. Add SCD Columns:
2. To implement SCD type 2, add columns to the dimension table to track changes. These columns include:

• Effective Date: The date when a record became effective.
• Expiry Date: The date when a record expired or was updated.
• Current Flag: A flag indicating whether a record is the current record.

1. Create SCD SQL Code:
2. Finally, create SQL code to implement SCD type 2. The SQL code should:

• Insert new rows for new data.
• Update the expiry date for old records.
• Set the current flag to 1 for the most recent record and 0 for old records.

-- Insert new rows into the current dimension table and the historical dimension table

INSERT INTO clients_dim (

?client_id,

?client_name,

?account_number,

?address,

?city,

?state,

?country,

?effective_date,

?expiry_date,

?current_flag

)

SELECT

?new.client_id,

?new.client_name,

?new.account_number,

?new.address,

?new.city,

?new.state,

?new.country,

?CURRENT_DATE,

?'9999-12-31',

?1

FROM

?new_clients_stg new

WHERE NOT EXISTS (

?SELECT 1

?FROM clients_dim old

?WHERE old.client_id = new.client_id

?AND old.current_flag = 1

);

INSERT INTO clients_dim_hist (

?client_id,

?client_name,

?account_number,

?address,

?city,

?state,

?country,

?effective_date,

?expiry_date,

?current_flag,

?version_number,

?end_date

)

SELECT

?old.client_id,

?old.client_name,

?old.account_number,

?old.address,

?old.city,

?old.state,

?old.country,

?old.effective_date,

?CURRENT_DATE - 1,

?old.current_flag,

?old.version_number + 1,

?'9999-12-31'

FROM

?clients_dim old

WHERE old.client_id = new.client_id

AND old.current_flag = 1;

-- Update the end date of the previous version of the record in the historical dimension table

UPDATE clients_dim_hist

SET end_date = CURRENT_DATE - 1

WHERE client_id = new.client_id

AND end_date = '9999-12-31';