Learn How To Implement SCD Types 1, 2, 3, 4, 5, And 6 With SQL Examples And Hints

You Should Know:

Slowly Changing Dimensions (SCDs) are crucial in data warehousing to manage historical data changes. Below are practical SQL examples and commands to implement SCD Types 1 to 6:

SCD Type 1: Overwrite

This type overwrites old data with new data, losing historical changes.

UPDATE employees
SET department = 'HR'
WHERE employee_id = 101;

SCD Type 2: Add New Row

This type adds a new row for each change, preserving history.

INSERT INTO employees (employee_id, name, department, start_date, end_date, is_current)
VALUES (101, 'John Doe', 'HR', '2023-01-01', '9999-12-31', 1);

SCD Type 3: Add New Column

This type adds a new column to track changes.

ALTER TABLE employees
ADD COLUMN previous_department VARCHAR(50);

UPDATE employees
SET previous_department = department,
department = 'Finance'
WHERE employee_id = 101;

SCD Type 4: Historical Table

This type uses a separate table to store historical data.

CREATE TABLE employees_history AS
SELECT * FROM employees
WHERE employee_id = 101;

UPDATE employees
SET department = 'IT'
WHERE employee_id = 101;

SCD Type 5: Hybrid Approach

This type combines SCD Type 1 and Type 4.

-- Update current table
UPDATE employees
SET department = 'Marketing'
WHERE employee_id = 101;

-- Insert into history table
INSERT INTO employees_history
SELECT * FROM employees
WHERE employee_id = 101;

SCD Type 6: Unified Approach

This type combines SCD Types 1, 2, and 3.

-- Add new row (Type 2)
INSERT INTO employees (employee_id, name, department, start_date, end_date, is_current)
VALUES (101, 'John Doe', 'Sales', '2023-01-01', '9999-12-31', 1);

-- Update previous row (Type 3)
UPDATE employees
SET end_date = '2023-12-31', is_current = 0
WHERE employee_id = 101 AND is_current = 1;

What Undercode Say:

Implementing SCDs is essential for maintaining data integrity and historical accuracy in data warehousing. The provided SQL commands and examples demonstrate how to handle different types of SCDs effectively. For further reading, you can explore more about SCDs and their applications in data engineering:

By mastering these techniques, you can ensure your data warehouse is robust and capable of handling complex data changes over time.

References:

Reported By: Abhishek Agrawal – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅

Join Our Cyber World:

💬 Whatsapp | 💬 Telegram

Listen to this Post