Chapter 6: ETL Processing with SQL

📚 Chapter 6 – ETL Processing with SQL

1️⃣ Core ETL Pipeline Mechanics

  • Extraction, Transformation, and Loading (ETL) transitions messy transactional inputs into standardized analytical schemas.
[OLTP Sources] ---> (Extraction) ---> (Transformation / Cleansing) ---> (Loading) ---> [Data Warehouse]

Loading Strategies: Full vs. Incremental

  • Full Reload (“Flush and Fill”):

    • Clears existing warehouse records and completely repopulates target tables.

    • Highly effective for dimension tables or small data marts.

    • TRUNCATE TABLE performs significantly faster than DELETE because it deallocates data pages directly and resets auto-incrementing identity seeds to 1.

    • However, active foreign key constraints must be dropped prior to truncation and recreated after loading.

  • Incremental Synchronization:

    • Compares source transactional states against destination warehouse tables using modification flags (e.g., RowStatus), audit timestamps, or change-data statements.

    • Required when fact tables grow into millions of rows and complete rebuild windows exceed available batch processing timeframes.

2️⃣ Transformation Patterns in Transact-SQL

Data Reduction & Explicit Aliasing

  • Avoid SELECT *; explicitly declare required columns to minimize network I/O and server buffer consumption.

  • Standardize on modern left-hand column aliases ([DestinationColumn] = SourceColumn) to improve script legibility across complex source-to-target mappings.

Explicit Type Casting

  • Source data must be converted from disparate legacy types into clean, standardized warehouse formats using ANSI-compliant CAST() expressions:
SELECT 
    [AuthorId]   = CAST(au_id AS NCHAR(11)),
    [AuthorName] = CAST((au_fname + ' ' + au_lname) AS NVARCHAR(100)),
    [AuthorState]= CAST(state AS NCHAR(2))
FROM pubs.dbo.authors;

Natural-to-Surrogate Key Resolution

  • To load fact tables or dependent dimensions, destination surrogate keys must be resolved by joining against the business natural keys established in earlier loading phases:
SELECT
    [OrderNumber]   = CAST(s.ord_num AS NVARCHAR(50)),
    [OrderDateKey]  = d.DateKey,
    [TitleKey]      = t.TitleKey,
    [StoreKey]      = st.StoreKey,
    [SalesQuantity] = s.qty
FROM pubs.dbo.sales AS s
JOIN DWPubsSales.dbo.DimDates AS d
    ON s.ord_date = d.Date
JOIN DWPubsSales.dbo.DimTitles AS t
    ON s.title_id = t.TitleId
JOIN DWPubsSales.dbo.DimStores AS st
    ON s.stor_id = st.StoreId;

3️⃣ Handling Data Quality & Missing Attributes (Nulls)

Nulls in Numeric Facts vs. Dimensional References

  • **Numeric Measures: Null values represent missing or non-occurring data and must be preserved during numeric aggregations. Substituting zeroes for missing values mathematically skews downstream averages and variance calculations.

  • Foreign Key References: Never allow null foreign keys in a fact table. Null references cause aggregation joins to silently drop records in OLAP reporting environments.

Unknown Member Ingestion

  • To maintain referential integrity, populate dedicated default records inside dimensions to map missing, incomplete, or corrupted source values:
SET IDENTITY_INSERT DWPubsSales.dbo.DimDates ON;
GO

INSERT INTO DWPubsSales.dbo.DimDates (
    DateKey, [Date], DateName, [Month], MonthName, 
    [Quarter], QuarterName, [Year], YearName
)
VALUES 
(
    -1, '1900-01-01', 'Unknown Day', -1, 'Unknown Month', 
    -1, 'Unknown Quarter', -1, 'Unknown Year'
),
(
    -2, '1900-01-01', 'Corrupt Day', -2, 'Corrupt Month', 
    -2, 'Corrupt Quarter', -2, 'Corrupt Year'
);
GO

SET IDENTITY_INSERT DWPubsSales.dbo.DimDates OFF;
GO
  • When transforming source rows containing unassigned dates, use ISNULL() to direct orphan records to surrogate key -1 (Unknown) in the DimDates dimension.
SELECT
    [TitleId]          = CAST(ISNULL(title_id, -1) AS NVARCHAR(6)),
    [TitleName]        = CAST(ISNULL(title, 'Unknown') AS NVARCHAR(100)),
    [PublishedDateKey] = ISNULL(d.DateKey, -1)
FROM pubs.dbo.titles AS t
LEFT JOIN DWPubsSales.dbo.DimDates AS d
    ON t.pubdate = d.Date;

4️⃣ Programmability & Abstraction Layers

To isolate ETL ingestion pipelines from direct underlying table schema changes, abstract data access logic using database programmability objects:

  • Views (vEtl*): Saved virtual queries that mask source join complexity, filter invalid historical records, and expose standardized aliases without modifying base storage.

  • Stored Procedures (pEtl*): Procedural units that accept runtime execution parameters (e.g., passing a specific @BatchDate for incremental loads), manage transactions, and run data maintenance routines.

  • User-Defined Functions (fEtl*): Scalar transformation routines usable directly inside inline SELECT expressions (e.g., standardizing regional codes into state names).

References & Further Reading

Primary Course Text:

Root, R., & Mason, C. (2012). Chapter 5: Creating a Data Warehouse & Chapter 6: ETL Processing with SQL. In Pro SQL Server 2012 BI Solutions (pp. 161–251).

Transact-SQL Reference:

  • Ben-Gan, I. (2016). T-SQL Fundamentals (3rd ed.). Microsoft Press.

  • ETL Architecture Principles: Kimball, R., & Caserta, J. (2004). The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data. John Wiley & Sons.