Chapter 6 · Building the Pipeline

Loading Dimensions

Dimensions are loaded before facts, because facts look up their keys. This chapter builds the two that matter most: a generated date dimension, and a customer dimension that keeps history when customers move.

The date dimension is generated, not extracted

No source system has a "calendar table", and a warehouse needs every day, including days with no orders. load_dim_date generates 2010-01-01 to 2030-12-31 from a numbers sequence and inserts only the days that are missing, so it is safe to run on every load.

/* load_dim_date - generate the calendar 2010-01-01 .. 2030-12-31 (idempotent: only missing days are added).
   A date dimension is generated, not extracted. DateKey is the smart key yyyymmdd. */
SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @From date = '2010-01-01', @To date = '2030-12-31';

WITH n AS (
    SELECT TOP (DATEDIFF(day, @From, @To) + 1)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS i
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
),
d AS (
    SELECT DATEADD(day, i, @From) AS FullDate FROM n
)
INSERT INTO Dimension.Date (DateKey, FullDate, DayName, DayNumber, MonthName, MonthNumber, QuarterNumber, Year)
SELECT CONVERT(int, CONVERT(char(8), d.FullDate, 112)),
       d.FullDate,
       DATENAME(weekday, d.FullDate),
       DAY(d.FullDate),
       DATENAME(month, d.FullDate),
       MONTH(d.FullDate),
       DATEPART(quarter, d.FullDate),
       YEAR(d.FullDate)
FROM d
WHERE NOT EXISTS (SELECT 1 FROM Dimension.Date AS x WHERE x.FullDate = d.FullDate);

DECLARE @rows int = @@ROWCOUNT;
EXEC audit.usp_LogStep @DagRunID = N'{{ run_id }}', @TaskID = N'{{ ti.task_id }}',
     @RowsInserted = @rows, @Note = N'calendar 2010-2030';

SELECT COUNT(*) AS date_rows, MIN(FullDate) AS first_day, MAX(FullDate) AS last_day, @rows AS added_now
FROM Dimension.Date;

date_rows | first_day  | last_day   | added_now
7670      | 2010-01-01 | 2030-12-31 | 7670        ← run 01
7670      | 2010-01-01 | 2030-12-31 | 0           ← run 06: nothing to add

Slowly changing dimensions in one table

When a customer moves, what should the warehouse remember? The answer is a per-column decision:

Using Type 1 and Type 2 in the same dimension is common. Names are corrections, while addresses are real events in time.

How the load works

1. Hash the Type 2 columns. HASHBYTES('SHA2_256', CONCAT_WS('|', City, StateCode, StateName, PostalCode)) turns four comparisons into one, and the hash is stored on each version as RowHash.
2. Type 1 update. Overwrite names on every version of the customer where they differ.
3. Expire. For current versions whose hash differs: set ValidTo to the source change time and IsCurrent = 0.
4. Insert. Every staged customer with no current version, either brand new or just expired, gets a new current row.

Steps 2–4 run in one transaction (with XACT_ABORT ON), so a failure can never leave a customer with zero or two current versions.

/* load_dim_customer_scd2 - Dimension.Customer as a Slowly Changing Dimension.
   Type 1 (overwrite) : FirstName, LastName, FullName  - corrections, no history kept
   Type 2 (history)   : City, State, Statename, Zip     - a move creates a new version
   RowHash = SHA2_256 of the Type 2 columns, so one comparison finds every changed customer.
   A new version starts at the time the change happened in the SOURCE (SourceModifiedDate),
   not at load time, so orders placed after a move find the new version. */
SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @BatchID int = (SELECT BatchID FROM audit.BatchRun WHERE DagRunID = N'{{ run_id }}');
DECLARE @type1 int, @expired int, @inserted int, @new_customers int;

SELECT s.CustomerID, s.FirstName, s.LastName,
       CONCAT(s.FirstName, N' ', s.LastName) AS FullName,
       s.City, s.StateCode, LEFT(s.StateName, 20) AS StateName, s.PostalCode, s.SourceModifiedDate,
       HASHBYTES('SHA2_256', CONCAT_WS(N'|', s.City, s.StateCode, s.StateName, s.PostalCode)) AS RowHash
INTO #src
FROM stg.Customer AS s;

SELECT @new_customers = COUNT(*) FROM #src AS s
WHERE NOT EXISTS (SELECT 1 FROM Dimension.Customer AS d WHERE d.CustomerAlternateKey = s.CustomerID);

BEGIN TRANSACTION;

    -- 1. Type 1: overwrite names on every version of the customer
    UPDATE d
    SET d.FirstName = s.FirstName, d.LastName = s.LastName, d.FullName = s.FullName
    FROM Dimension.Customer AS d
    JOIN #src AS s ON s.CustomerID = d.CustomerAlternateKey
    WHERE d.FirstName <> s.FirstName OR d.LastName <> s.LastName;
    SET @type1 = @@ROWCOUNT;

    -- 2. Type 2, step 1: close the current version when a tracked column changed
    UPDATE d
    SET d.ValidTo = s.SourceModifiedDate, d.IsCurrent = 0
    FROM Dimension.Customer AS d
    JOIN #src AS s ON s.CustomerID = d.CustomerAlternateKey
    WHERE d.IsCurrent = 1 AND d.RowHash <> s.RowHash;
    SET @expired = @@ROWCOUNT;

    -- 3. Type 2, step 2: open a new current version for changed AND brand-new customers
    INSERT INTO Dimension.Customer
        (CustomerAlternateKey, FirstName, LastName, FullName, City, State, Statename, Zip,
         ValidFrom, ValidTo, IsCurrent, RowHash, LoadBatchID)
    SELECT s.CustomerID, s.FirstName, s.LastName, s.FullName, s.City, s.StateCode, s.StateName, s.PostalCode,
           CASE WHEN EXISTS (SELECT 1 FROM Dimension.Customer AS d WHERE d.CustomerAlternateKey = s.CustomerID)
                THEN s.SourceModifiedDate      -- a new version of a known customer
                ELSE '1900-01-01' END,         -- first version covers all history
           NULL, 1, s.RowHash, @BatchID
    FROM #src AS s
    WHERE NOT EXISTS (SELECT 1 FROM Dimension.Customer AS d
                      WHERE d.CustomerAlternateKey = s.CustomerID AND d.IsCurrent = 1);
    SET @inserted = @@ROWCOUNT;

COMMIT TRANSACTION;

DECLARE @read int = (SELECT COUNT(*) FROM #src), @updated int = @expired + @type1;
DECLARE @note nvarchar(400) = CONCAT(N'new customers ', @new_customers, N', new versions ', @inserted - @new_customers,
                                     N', expired ', @expired, N', type 1 updates ', @type1);
EXEC audit.usp_LogStep @DagRunID = N'{{ run_id }}', @TaskID = N'{{ ti.task_id }}',
     @RowsRead = @read, @RowsInserted = @inserted, @RowsUpdated = @updated, @Note = @note;

PRINT @note;
SELECT @new_customers AS new_customers, @inserted - @new_customers AS new_versions, @expired AS expired_versions,
       @type1 AS type1_updates,
       (SELECT COUNT(*) FROM Dimension.Customer WHERE CustomerKey <> -1) AS customer_rows,
       (SELECT COUNT(*) FROM Dimension.Customer WHERE CustomerKey <> -1 AND IsCurrent = 1) AS current_rows;

Effective dates come from the source, not the clock

The new version starts at SourceModifiedDate, the moment the address changed in AdventureWorks, rather than at SYSDATETIME() when the pipeline happened to run. The difference matters: the simulator moved the customer at 10:00:58 and placed orders at 10:01:01, and the pipeline ran a minute later. With load-time dating, those orders would be matched to the old Los Angeles version.

A customer's first version starts at 1900-01-01 instead of their creation date, so that every historical order, however old, finds a version.

The real change: Trevor Bryant moves to Seattle

The simulator DAG (Chapter 12) moved customer 11090 from Los Angeles to Seattle and placed three orders. Run 03's dimension task logged:

SQL> new customers 0, new versions 1, expired 1, type 1 updates 0
new_customers | new_versions | expired_versions | type1_updates | customer_rows | current_rows
0             | 1            | 1                | 0             | 7820          | 7819

CustomerKey | FullName      | City        | State | Zip   | ValidFrom               | ValidTo                 | IsCurrent | OrdersOnThisVersion
32          | Trevor Bryant | Los Angeles | CA    | 90012 | 1900-01-01 00:00:00.000 | 2026-10-20 10:00:58.940 | 0         | 3
7820        | Trevor Bryant | Seattle     | WA    | 98104 | 2026-10-20 10:00:58.940 | NULL                    | 1         | 4

The unknown member

A fact row should never be rejected just because its dimension row is missing. The deploy step inserts one row with CustomerKey = -1 (using IDENTITY_INSERT), and the fact load points orphans at it. The DQ gate then counts them (rule DQ01), so missing customers become a visible, fixable number instead of silently lost sales.

/* deploy 06 - "unknown" dimension member (idempotent)
   A fact row whose customer cannot be found points at CustomerKey -1 instead of failing the
   foreign key. The data quality gate counts these rows. */
SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM Dimension.Customer WHERE CustomerKey = -1)
BEGIN
    SET IDENTITY_INSERT Dimension.Customer ON;
    INSERT INTO Dimension.Customer
        (CustomerKey, CustomerAlternateKey, FirstName, LastName, FullName, City, State, Statename, Zip,
         ValidFrom, ValidTo, IsCurrent, RowHash, LoadBatchID)
    VALUES
        (-1, -1, N'Unknown', N'Unknown', N'Unknown', N'Unknown', 'NA', N'Unknown', '00000',
         '1900-01-01', NULL, 1, NULL, NULL);
    SET IDENTITY_INSERT Dimension.Customer OFF;
END;

SELECT CustomerKey, CustomerAlternateKey, FullName, City, State FROM Dimension.Customer WHERE CustomerKey = -1;