Implementing Slowly Changing Dimension (SCD) Type 2 for specific fields while performing Type 1 Upserts for Other fields

Nnaemezue Obi-Eyisi
3 min readSep 15, 2024

When working with Slowly Changing Dimensions (SCD), implementing Type 2 can be crucial to track historical data changes over time. This blog post will explain how to handle a Type 2 SCD for specific columns (e.g., field1) while performing Type 1 updates for others. Let's break this down step by step.

Step 1: Extract and Prepare Source Data

The first step is to extract the source data and set the default metadata fields needed for the SCD Type 2 implementation.

  • Current_flag = 1: Indicates the current active record.
  • Effective_fromDT = current_date: Marks when the current version became active.
  • Effective_ToDT = '12/31/9999': A placeholder future date to signify that this record is current until replaced by a new version.
SELECT *, 
1 AS Current_flag,
CURRENT_DATE AS Effective_fromDT,
'12/31/9999' AS Effective_ToDT
FROM source_table;

Step 2: End the Current Version for Records with Changes

Next, you need to end the current version of any record where the tracked field (e.g., field_1)…

--

--

Nnaemezue Obi-Eyisi

I am passionate about empowering, educating, and encouraging individuals pursuing a career in data engineering. Currently a Senior Data Engineer at Capgemini