Build The Silver Layer: Cleanse & Transform Data
The Silver Layer in a Medallion Architecture serves as the cleansed, standardized, and normalized data repository. Unlike the Bronze Layer—which ingests raw, un-validated data directly from source systems—the Silver Layer transforms raw inputs into reliable enterprise assets.
This chapter covers the end-to-end engineering process required to construct a Silver Layer: exploring data relationships, detecting underlying data quality flaws in the Bronze Layer, designing robust SQL data transformations, establishing structural metadata columns, and automating the ETL pipeline via T-SQL stored procedures.
1. The Silver Layer Architecture & Workflow
The Silver Layer acts as an intermediate refinement stage. It enforces data structure, referential integrity, domain constraints, and standard business definitions without altering the core granular grain of the records or creating analytical data models (which is deferred to the Gold Layer).
The 3-Step Iterative Engineering Process
Building the Silver Layer requires an iterative execution cycle across three distinct phases:
2. Data Exploration & Integration Mapping
Before writing transformation scripts, data engineers must explore source tables, assess data relationships, and draw a Data Integration Model. In Bronze, tables are ingested independently; in Silver, cross-table join keys and shared attributes must be aligned.
Note on Query Constraints: When exploring raw tables containing millions of rows, avoid unbound queries. Always constrain exploration using
SELECT TOP 1000 ...to prevent degrading production source performance.
3. Silver Layer Specifications & DDL Strategy
Silver Layer Technical Specifications
Metadata Columns Strategy
Metadata columns are operational fields appended to database tables by data engineers. They do not originate from source systems but are vital for pipeline auditing, data lineage, and incremental load tracking.
Real-Life Analogy: Metadata columns act like tracking labels on physical shipping containers. The cargo inside represents the source data, while the shipping label (timestamp, facility ID, package tracking number) tells the engineer exactly when the container arrived and where it came from.
Data Definition Language (DDL) Implementation
To maintain technical consistency, Silver tables adopt identical base definitions to Bronze tables, updated with explicit data types and appended metadata columns (prefixed with dwh_).
-- DDL Example: Silver Customer Info Table
IF OBJECT_ID('silver.crm_cust_info', 'U') IS NOT NULL
DROP TABLE silver.crm_cust_info;
GO
CREATE TABLE silver.crm_cust_info (
cst_id INT,
cst_key VARCHAR(50),
cst_firstname VARCHAR(50),
cst_lastname VARCHAR(50),
cst_marital_status VARCHAR(20),
cst_gndr VARCHAR(20),
cst_create_date DATE,
-- Audit Metadata Column
dwh_create_date DATETIME2 DEFAULT GETDATE()
);
GO
4. Cleansing Technique 1: Handling Duplicates & Primary Key Integrity
Primary keys in the Silver Layer must be unique and non-null. Bronze tables frequently ingest duplicate records caused by upstream system re-transmissions or historical updates.
1. Detecting Primary Key Quality Issues
-- Quality Audit: Detect Duplicates & Nulls in Customer Primary Key
SELECT
cst_id,
COUNT(*) AS record_count
FROM bronze.crm_cust_info
GROUP BY cst_id
HAVING COUNT(*) > 1 OR cst_id IS NULL;
2. Resolving Duplicates Using Window Functions
When duplicates exist, retain only the most recent record based on a operational timestamp (cst_create_date). Use the ROW_NUMBER() window function partitioned by the primary key and ordered descending by the creation date.
-- Transformation Logic: Deduplication via ROW_NUMBER()
WITH RankedCustomers AS (
SELECT
cst_id,
cst_key,
cst_firstname,
cst_lastname,
cst_marital_status,
cst_gndr,
cst_create_date,
ROW_NUMBER() OVER (
PARTITION BY cst_id
ORDER BY cst_create_date DESC
) AS row_num
FROM bronze.crm_cust_info
)
SELECT
cst_id,
cst_key,
cst_firstname,
cst_lastname,
cst_marital_status,
cst_gndr,
cst_create_date
FROM RankedCustomers
WHERE row_num = 1; -- Filter out stale historical duplicates
5. Cleansing Technique 2: String Trimming, Normalization & Missing Values
Raw text data often contains leading/trailing whitespace, inconsistent capitalizations, cryptic short codes, or missing values represented as raw NULLs.
1. Whitespace Auditing & Trimming
Unwanted spaces break JOIN conditions and string equality checks.
-- Quality Audit: Detect Leading or Trailing Whitespace
SELECT cst_firstname
FROM bronze.crm_cust_info
WHERE cst_firstname <> TRIM(cst_firstname);
2. Value Standardization & Missing Value Handling
Standardize abbreviated values into user-friendly descriptions (e.g., 'M' to 'Male', 'S' to 'Single') and replace raw missing NULLs with 'n/a' defaults.
-- Transformation Logic: String Trimming & Code Standardization
SELECT
cst_id,
cst_key,
TRIM(cst_firstname) AS cst_firstname,
TRIM(cst_lastname) AS cst_lastname,
-- Normalize Marital Status & Handle Missing Values
CASE
WHEN UPPER(TRIM(cst_marital_status)) = 'S' THEN 'Single'
WHEN UPPER(TRIM(cst_marital_status)) = 'M' THEN 'Married'
ELSE 'n/a'
END AS cst_marital_status,
-- Normalize Gender & Handle Missing Values
CASE
WHEN UPPER(TRIM(cst_gndr)) = 'F' THEN 'Female'
WHEN UPPER(TRIM(cst_gndr)) = 'M' THEN 'Male'
ELSE 'n/a'
END AS cst_gndr,
cst_create_date
FROM bronze.crm_cust_info;
6. Advanced Transformation: Fixing Overlapping Date Intervals
A critical data anomaly occurs in historical tracking tables where record effective dates overlap or contain invalid end dates.
The Problem: Overlapping Date Records
Consider raw product pricing history records in bronze.crm_prd_info:
Real-World Brainstorming: The Excel Mental Model
When dealing with complex date logic errors, visualize the timeline using an Excel spreadsheet model rather than writing complex SQL immediately.
T-SQL Implementation using LEAD()
-- Transformation Logic: Rebuilding Historical Date Ranges with LEAD()
SELECT
prd_id,
-- Extract category ID (First 5 Chars) & replace minus with underscore
REPLACE(SUBSTRING(prd_key, 1, 5), '-', '_') AS cat_id,
-- Extract clean product key (From char index 7 to end)
SUBSTRING(prd_key, 7, LEN(prd_key)) AS prd_key,
prd_nm,
ISNULL(prd_cost, 0) AS prd_cost,
CASE
WHEN UPPER(TRIM(prd_line)) = 'M' THEN 'Mountain'
WHEN UPPER(TRIM(prd_line)) = 'R' THEN 'Road'
WHEN UPPER(TRIM(prd_line)) = 'S' THEN 'Other Sales'
WHEN UPPER(TRIM(prd_line)) = 'T' THEN 'Touring'
ELSE 'n/a'
END AS prd_line,
CAST(prd_start_dt AS DATE) AS prd_start_dt,
-- Dynamically recalculate End Date to prevent gaps and overlaps
CAST(
DATEADD(DAY, -1,
LEAD(prd_start_dt) OVER (
PARTITION BY prd_key
ORDER BY prd_start_dt ASC
)
) AS DATE
) AS prd_end_dt
FROM bronze.crm_prd_info;
7. Advanced Transformation: Date Conversions & Calculation Enforcement
Transaction data frequently suffers from improper data types (e.g., dates stored as integer 20240101) and corrupted business metrics (e.g., sales != quantity * price).
1. Integer-to-Date Type Casting Pipeline
Casting an integer directly to a DATE type causes SQL runtime failures. You must execute a two-stage conversion pipeline: Integer Varchar Date.
-- Transformation Logic: Handling Invalid Integer Dates safely
CASE
WHEN sls_order_dt = 0 OR LEN(CAST(sls_order_dt AS VARCHAR)) <> 8 THEN NULL
ELSE CAST(CAST(sls_order_dt AS VARCHAR) AS DATE)
END AS sls_order_dt
2. Enforcing Mathematical Integrity in Business Metrics
When sales, quantity, or price values are negative, zero, or mathematically inconsistent, apply business domain recovery rules:
- If
salesis NULL, negative, zero, or inconsistent withquantity * price, recalculatesales = quantity * ABS(price). - If
priceis NULL or negative, recalculateprice = sales / NULLIF(quantity, 0)or applyABS(price).
-- Transformation Logic: Transactional Data Cleansing & Math Recovery
SELECT
sls_ord_num,
sls_prd_key,
sls_cust_id,
-- Safe Date Conversions
CASE
WHEN sls_order_dt = 0 OR LEN(CAST(sls_order_dt AS VARCHAR)) <> 8 THEN NULL
ELSE CAST(CAST(sls_order_dt AS VARCHAR) AS DATE)
END AS sls_order_dt,
CASE
WHEN sls_ship_dt = 0 OR LEN(CAST(sls_ship_dt AS VARCHAR)) <> 8 THEN NULL
ELSE CAST(CAST(sls_ship_dt AS VARCHAR) AS DATE)
END AS sls_ship_dt,
CASE
WHEN sls_due_dt = 0 OR LEN(CAST(sls_due_dt AS VARCHAR)) <> 8 THEN NULL
ELSE CAST(CAST(sls_due_dt AS VARCHAR) AS DATE)
END AS sls_due_dt,
-- Recalculate Sales Amount if corrupted or invalid
CASE
WHEN sls_sales IS NULL OR sls_sales <= 0 OR sls_sales <> sls_quantity * ABS(sls_price)
THEN sls_quantity * ABS(sls_price)
ELSE sls_sales
END AS sls_sales,
sls_quantity,
-- Derive Unit Price if missing/invalid, prevent Divide-By-Zero using NULLIF
CASE
WHEN sls_price IS NULL OR sls_price <= 0
THEN sls_sales / NULLIF(sls_quantity, 0)
ELSE sls_price
END AS sls_price
FROM bronze.crm_sales_details;
8. Source Integration Cleansing: ERP Data Standardization
ERP source data often contains system-specific formatting anomalies, such as added character prefixes or hyphenated keys.
1. Removing Unwanted Prefixes & Characters
-- ERP Customer ID Cleansing: Removing 'NAS' prefix dynamically
-- Raw Input: 'NAS10045' --> Transformed Output: '10045'
CASE
WHEN cid LIKE 'NAS%' THEN SUBSTRING(cid, 4, LEN(cid))
ELSE cid
END AS cid
-- ERP Location Cleansing: Removing Hyphens from Join Keys
-- Raw Input: '100-45' --> Transformed Output: '10045'
REPLACE(cid, '-', '') AS cid
2. Standardizing Demographic Attributes
-- ERP Customer Demographics Standardization
SELECT
CASE
WHEN cid LIKE 'NAS%' THEN SUBSTRING(cid, 4, LEN(cid))
ELSE cid
END AS cid,
-- Out-of-bounds Birthdate Handling (Future Birthdates reset to NULL)
CASE
WHEN bdate > GETDATE() THEN NULL
ELSE bdate
END AS bdate,
-- Gender Standardization
CASE
WHEN UPPER(TRIM(gen)) IN ('F', 'FEMALE') THEN 'Female'
WHEN UPPER(TRIM(gen)) IN ('M', 'MALE') THEN 'Male'
ELSE 'n/a'
END AS gen
FROM bronze.erp_cust_az12;
9. End-to-End Orchestration: The Automated Stored Procedure
To make the Silver Layer reproducible, wrap the ETL execution in a T-SQL stored procedure using the TRUNCATE-THEN-INSERT pattern within a structured TRY...CATCH block.
CREATE OR ALTER PROCEDURE silver.load_silver AS
BEGIN
SET NOCOUNT ON;
DECLARE @start_time DATETIME, @end_time DATETIME;
DECLARE @batch_start_time DATETIME, @batch_end_time DATETIME;
BEGIN TRY
SET @batch_start_time = GETDATE();
PRINT '================================================';
PRINT 'Starting Loading Silver Layer';
PRINT '================================================';
--------------------------------------------------------
-- Table 1: silver.crm_cust_info
--------------------------------------------------------
SET @start_time = GETDATE();
PRINT '>> Truncating and Loading: silver.crm_cust_info';
TRUNCATE TABLE silver.crm_cust_info;
INSERT INTO silver.crm_cust_info (
cst_id,
cst_key,
cst_firstname,
cst_lastname,
cst_marital_status,
cst_gndr,
cst_create_date
)
SELECT
cst_id,
cst_key,
TRIM(cst_firstname),
TRIM(cst_lastname),
CASE
WHEN UPPER(TRIM(cst_marital_status)) = 'S' THEN 'Single'
WHEN UPPER(TRIM(cst_marital_status)) = 'M' THEN 'Married'
ELSE 'n/a'
END,
CASE
WHEN UPPER(TRIM(cst_gndr)) = 'F' THEN 'Female'
WHEN UPPER(TRIM(cst_gndr)) = 'M' THEN 'Male'
ELSE 'n/a'
END,
cst_create_date
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY cst_id ORDER BY cst_create_date DESC) as flag_last
FROM bronze.crm_cust_info
WHERE cst_id IS NOT NULL
) t
WHERE flag_last = 1;
SET @end_time = GETDATE();
PRINT '>> Load Duration: ' + CAST(DATEDIFF(second, @start_time, @end_time) AS VARCHAR) + ' seconds';
PRINT '------------------------------------------------';
-- [Repeat TRUNCATE & INSERT process for remaining Silver tables...]
SET @batch_end_time = GETDATE();
PRINT '================================================';
PRINT 'Silver Layer Successfully Loaded';
PRINT 'Total Execution Time: ' + CAST(DATEDIFF(second, @batch_start_time, @batch_end_time) AS VARCHAR) + ' seconds';
PRINT '================================================';
END TRY
BEGIN CATCH
PRINT '================================================';
PRINT 'ERROR OCCURRED DURING SILVER LAYER LOAD';
PRINT 'Error Message: ' + ERROR_MESSAGE();
PRINT 'Error Number : ' + CAST(ERROR_NUMBER() AS VARCHAR);
PRINT 'Error State : ' + CAST(ERROR_STATE() AS VARCHAR);
PRINT '================================================';
END CATCH
END;
GO
10. Data Lineage, Auditing Checks & Git Versioning
1. Data Lineage Flow Diagram
Document operational data lineage to provide transparent architectural visibility across systems.
2. Post-Loading Validation Script Suite
Save continuous data quality assertions in a version-controlled test script repository (tests/quality_checks_silver.sql).
-- Silver Layer Quality Assurance Audit Suite
-- Check 1: Primary Key Uniqueness Assertion
SELECT cst_id, COUNT(*)
FROM silver.crm_cust_info
GROUP BY cst_id
HAVING COUNT(*) > 1;
-- Check 2: String Formatting Assertion (No leading/trailing spaces)
SELECT cst_firstname
FROM silver.crm_cust_info
WHERE cst_firstname <> TRIM(cst_firstname);
-- Check 3: Business Math Integrity Assertions
SELECT *
FROM silver.crm_sales_details
WHERE sls_sales <> sls_quantity * sls_price
OR sls_sales <= 0
OR sls_price <= 0;
-- Check 4: Historical Date Continuity Assertion
SELECT *
FROM silver.crm_prd_info
WHERE prd_end_dt < prd_start_dt;
Summary and Key Takeaways
- Refinement Layer Purpose: The Silver Layer cleanses, normalizes, and standardizes data without altering core granularity or creating dimensional analytical models.
- Audit Before Coding: Always run data quality audit queries (
HAVING COUNT(*) > 1,TRIM() checks, boundary tests) against Bronze inputs before building SQL transformation scripts. - Deduplication via Windowing: Use
ROW_NUMBER() OVER (PARTITION BY key ORDER BY timestamp DESC)to resolve duplicate primary keys and retain the latest record. - Dynamic End Dates via
LEAD(): Resolve overlapping date ranges and corrupt historical end dates by calculatingLEAD(start_date) - 1 DAY. - Safe Integer-to-Date Conversion: Cast integer date fields using a two-stage pipeline:
CAST(CAST(int_val AS VARCHAR) AS DATE). - Operational Metadata Columns: Add auditing metadata columns (e.g.,
dwh_create_date DATETIME2 DEFAULT GETDATE()) to trace data lineage and loading timestamps. - Enforce Business Math Integrity: Apply
CASE WHENbusiness logic combined withABS()andNULLIF()to recover corrupted metric fields (sales,quantity,price). - Reproducible ETL Orchestration: Package table transformations using the
TRUNCATE-THEN-INSERTpattern inside a T-SQL stored procedure with robustTRY...CATCHerror controls.