Capstone Project: Employee Data Pipeline
Welcome to your first Capstone!
In this project, you act as the Lead Data Engineer for a massive IT services company in Hyderabad. The HR department has asked you to build an automated pipeline that tracks employee role changes and salary histories over time.
You must build a complete Slowly Changing Dimension (SCD) Type 2 pipeline using Snowflake's native automation tools.
Project Scope
- Setting up the Medallion Architecture (Bronze, Silver, Gold)
- Simulating Raw Ingestion
- Implementing SCD Type 2 logic via
MERGE - Automating the pipeline with Streams and Tasks
Step 1: Database and Medallion Layer Setup
We will organize our pipeline into three schemas: BRONZE (raw), SILVER (cleaned & historicized), and GOLD (analytics views).
Run the following code in your Snowflake worksheet.
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_HR_DB;
USE DATABASE BEEMS_HR_DB;
CREATE SCHEMA IF NOT EXISTS BRONZE;
CREATE SCHEMA IF NOT EXISTS SILVER;
CREATE SCHEMA IF NOT EXISTS GOLD;
Step 2: The Bronze Layer (Ingestion)
The Bronze layer receives data directly from the HR application.
-- 1. The Raw Ingestion Table
CREATE OR REPLACE TABLE BRONZE.RAW_EMPLOYEE_EVENTS (
EMP_ID INT,
FIRST_NAME STRING,
LAST_NAME STRING,
DEPARTMENT STRING,
ROLE STRING,
SALARY NUMBER,
EVENT_TIMESTAMP TIMESTAMP
);
-- 2. The Stream to track new events
CREATE OR REPLACE STREAM BRONZE.EMPLOYEE_EVENT_STREAM ON TABLE BRONZE.RAW_EMPLOYEE_EVENTS;
Step 3: The Silver Layer (SCD Type 2 Target)
The Silver layer is our master dimension table. It must track the history of an employee. If an employee gets promoted, we do not overwrite their old row. We mark the old row as inactive (IS_CURRENT = FALSE) and insert a new active row.
-- The SCD Type 2 Master Table
CREATE OR REPLACE TABLE SILVER.DIM_EMPLOYEE (
EMP_SURROGATE_KEY NUMBER AUTOINCREMENT START 1 INCREMENT 1,
EMP_ID INT,
FULL_NAME STRING,
DEPARTMENT STRING,
ROLE STRING,
SALARY NUMBER,
VALID_FROM TIMESTAMP,
VALID_TO TIMESTAMP,
IS_CURRENT BOOLEAN
);
-- Insert a baseline record for an existing employee
INSERT INTO SILVER.DIM_EMPLOYEE (EMP_ID, FULL_NAME, DEPARTMENT, ROLE, SALARY, VALID_FROM, VALID_TO, IS_CURRENT)
VALUES (101, 'Kavya Pillai', 'Engineering', 'Junior Developer', 600000, '2022-01-01', NULL, TRUE);
Step 4: Building the SCD Type 2 Merge Task
This is the most complex part of the pipeline. When a new event arrives in the stream, the MERGE statement must:
- Expire the existing active record (Set
VALID_TOandIS_CURRENT = FALSE). - Insert the new active record (Set
IS_CURRENT = TRUE).
Because an SCD Type 2 update requires two actions (an update and an insert) for the same source record, we must handle it carefully. We do this by feeding the stream into the MERGE statement and joining it twice (once for the update, once for the insert).
(To verify your identity before creating tasks, run SELECT CURRENT_USER();)
CREATE OR REPLACE TASK SILVER.PROCESS_HR_EVENTS
WAREHOUSE = COMPUTE_WH
SCHEDULE = '1 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('BRONZE.EMPLOYEE_EVENT_STREAM')
AS
MERGE INTO SILVER.DIM_EMPLOYEE target
USING (
-- We need to generate the "Expire" record and the "New" record
SELECT
EMP_ID, FIRST_NAME || ' ' || LAST_NAME AS FULL_NAME, DEPARTMENT, ROLE, SALARY, EVENT_TIMESTAMP,
'UPDATE' AS MERGE_ACTION
FROM BRONZE.EMPLOYEE_EVENT_STREAM
WHERE METADATA$ACTION = 'INSERT'
UNION ALL
SELECT
EMP_ID, FIRST_NAME || ' ' || LAST_NAME AS FULL_NAME, DEPARTMENT, ROLE, SALARY, EVENT_TIMESTAMP,
'INSERT' AS MERGE_ACTION
FROM BRONZE.EMPLOYEE_EVENT_STREAM
WHERE METADATA$ACTION = 'INSERT'
) source
ON target.EMP_ID = source.EMP_ID
AND target.IS_CURRENT = TRUE
AND source.MERGE_ACTION = 'UPDATE' -- Only match on the 'UPDATE' half
-- Expire the old record
WHEN MATCHED AND target.ROLE != source.ROLE OR target.SALARY != source.SALARY THEN
UPDATE SET
target.VALID_TO = source.EVENT_TIMESTAMP,
target.IS_CURRENT = FALSE
-- Insert the new record (or a brand new employee)
WHEN NOT MATCHED AND source.MERGE_ACTION = 'INSERT' THEN
INSERT (EMP_ID, FULL_NAME, DEPARTMENT, ROLE, SALARY, VALID_FROM, VALID_TO, IS_CURRENT)
VALUES (source.EMP_ID, source.FULL_NAME, source.DEPARTMENT, source.ROLE, source.SALARY, source.EVENT_TIMESTAMP, NULL, TRUE);
-- Turn on the pipeline
ALTER TASK SILVER.PROCESS_HR_EVENTS RESUME;
Step 5: Simulating the Flow
Let's simulate Kavya getting a promotion, and a new employee joining the company.
-- Kavya gets promoted to Senior Developer today
INSERT INTO BRONZE.RAW_EMPLOYEE_EVENTS
VALUES (101, 'Kavya', 'Pillai', 'Engineering', 'Senior Developer', 1200000, CURRENT_TIMESTAMP());
-- A new employee, Rohan, joins the sales team
INSERT INTO BRONZE.RAW_EMPLOYEE_EVENTS
VALUES (102, 'Rohan', 'Sharma', 'Sales', 'Account Executive', 800000, CURRENT_TIMESTAMP());
Wait 60 seconds for the task to execute. Or, trigger it manually:
EXECUTE TASK SILVER.PROCESS_HR_EVENTS;
Check the results:
SELECT * FROM SILVER.DIM_EMPLOYEE ORDER BY EMP_ID, VALID_FROM;
You should see two rows for Kavya. The Junior row is marked IS_CURRENT = FALSE, and the Senior row is marked TRUE. Rohan will have one row.
Step 6: The Gold Layer (Analytics)
Finally, we build a view in the Gold layer for the BI tool. Business analysts usually only care about the current state of the company.
CREATE OR REPLACE SECURE VIEW GOLD.CURRENT_EMPLOYEES AS
SELECT
EMP_ID,
FULL_NAME,
DEPARTMENT,
ROLE,
SALARY
FROM SILVER.DIM_EMPLOYEE
WHERE IS_CURRENT = TRUE;
SELECT * FROM GOLD.CURRENT_EMPLOYEES;
Summary
Congratulations! You have built a fully automated SCD Type 2 pipeline.
- Bronze: Handled raw ingestion and tracked changes with a Stream.
- Silver: Implemented complex history tracking using a
MERGEstatement orchestrated by a Task. - Gold: Created a clean, secure serving layer for BI analysts.