MS-SQL Temporal Tables and Slowly Changing Dimensions: Patterns for Auditable History

Scenario: Customer Terms Changing Under Your Reports

You’re maintaining a SQL Server-based analytics platform feeding Power BI and Excel reports. Core entities like Customer, Product, and PriceAgreement live in a data warehouse on SQL Server.

The pain:

• Business users frequently renegotiate terms (discounts, tiers, account manager).
• Historical reports need to show what was known at the time, not the latest values.
• Today, your dimCustomer table is type-1 only: updates overwrite history.
• Audit teams periodically ask “who changed this, and when?”, and you end up trawling logs or backup copies.

You want:

• Auditable, row-level history of dimension changes.
• Clean integration with SCD patterns used in the warehouse.
• Simple querying for “as-of” analysis (e.g. “as of 2026-09-30, what was the discount?”).

We’ll focus on Customer as the core dimension and connect three layers:

1. Operational source: dbo.Customer in an OLTP-ish database.
2. Temporal table history: system-versioned history of that operational table.
3. Warehouse SCD: dw.dimCustomer with SCD Type 2 behaviour.

Temporal Tables in SQL Server: What They Actually Do

System-versioned temporal tables in SQL Server keep a full history of row versions, managed by the engine.

Key properties (current SQL Server behaviour):

• Each temporal table has:
  • A current table (the one you query normally).
  • A history table (managed by SQL Server).
• You must define two datetime2 columns in the current table:
  • ValidFrom (period start)
  • ValidTo (period end)
• A PERIOD FOR SYSTEM_TIME is declared over those columns.
• When you UPDATE or DELETE:
  • The old row is automatically copied into the history table.
  • ValidFrom/ValidTo are automatically maintained by SQL Server.
• You can query:
  • FOR SYSTEM_TIME AS OF @t – snapshot at a point in time.
  • FROM ... TO, BETWEEN ... AND, CONTAINED IN – intervals.

Important constraints:

• Temporal tables require datetime2 for the period columns.
• You cannot directly modify the history table while system versioning is ON.
• Primary keys and indexes are defined on the current table; you can index the history table separately.
• System-versioning can be turned OFF, but then history management is manual.

For our scenario, we’ll enable system versioning on the operational Customer table to get a reliable change log with minimal application changes.

Step 1: Turning Customer into a Temporal Table

Assume you currently have:

CREATE TABLE dbo.Customer
(
    CustomerID       int            NOT NULL PRIMARY KEY,
    CustomerCode     nvarchar(50)   NOT NULL,
    Name             nvarchar(200)  NOT NULL,
    DiscountPercent  decimal(5,2)   NOT NULL,
    AccountManager   nvarchar(100)  NULL,
    IsActive         bit            NOT NULL DEFAULT (1),
    -- no history columns yet
);

You want to convert this into a system-versioned temporal table without breaking existing code.

1. Add period columns

We add ValidFrom and ValidTo with defaults, then declare the period:

ALTER TABLE dbo.Customer
ADD ValidFrom datetime2(7) GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_Customer_ValidFrom DEFAULT SYSUTCDATETIME(),
    ValidTo   datetime2(7) GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DF_Customer_ValidTo   DEFAULT CONVERT(datetime2(7), '9999-12-31 23:59:59.9999999'),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

Notes:

• GENERATED ALWAYS AS ROW START/END ensures SQL Server manages these values.
• Using HIDDEN keeps existing SELECT * queries from suddenly returning extra columns, which is often important in legacy code.
• The far-future default for ValidTo is a common pattern; SQL Server will adjust it automatically when rows are updated.

2. Create a history table and enable system versioning

You can let SQL Server create the history table automatically, or specify your own. Explicit is usually better:

CREATE TABLE dbo.CustomerHistory
(
    CustomerID       int            NOT NULL,
    CustomerCode     nvarchar(50)   NOT NULL,
    Name             nvarchar(200)  NOT NULL,
    DiscountPercent  decimal(5,2)   NOT NULL,
    AccountManager   nvarchar(100)  NULL,
    IsActive         bit            NOT NULL,
    ValidFrom        datetime2(7)   NOT NULL,
    ValidTo          datetime2(7)   NOT NULL
);

ALTER TABLE dbo.Customer
SET (SYSTEM_VERSIONING = ON
    (HISTORY_TABLE = dbo.CustomerHistory,
     DATA_CONSISTENCY_CHECK = ON));

DATA_CONSISTENCY_CHECK = ON makes SQL Server validate that period values don’t overlap for the same primary key.

At this point:

• All future UPDATE and DELETE operations on dbo.Customer will write old versions into dbo.CustomerHistory.
• You can query as-of history with FOR SYSTEM_TIME AS OF.

3. Querying as-of customer state

Example: what was the discount for CustomerID = 101 as of a specific date?

DECLARE @AsOf datetime2(7) = '2026-09-30T23:59:59.0000000';

SELECT CustomerID,
       CustomerCode,
       Name,
       DiscountPercent,
       AccountManager,
       IsActive
FROM dbo.Customer
FOR SYSTEM_TIME AS OF @AsOf
WHERE CustomerID = 101;

This query transparently reads from the current and history tables to return the correct version for that timestamp.

Step 2: Designing a Warehouse SCD Dimension on Top

Temporal tables give you operational history, but analytics systems usually want SCD Type 2 dimensions with:

• Stable surrogate keys.
• Fact tables pointing to the correct dimension version for the event date.
• Clear ValidFrom / ValidTo ranges and an IsCurrent flag.

We’ll build dw.dimCustomer as a Type 2 dimension sourced from dbo.Customer’s temporal history.

Target dimension structure

CREATE TABLE dw.dimCustomer
(
    CustomerSK       int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustomerID       int               NOT NULL,   -- business key
    CustomerCode     nvarchar(50)      NOT NULL,
    Name             nvarchar(200)     NOT NULL,
    DiscountPercent  decimal(5,2)      NOT NULL,
    AccountManager   nvarchar(100)     NULL,
    IsActive         bit               NOT NULL,

    ValidFrom        datetime2(7)      NOT NULL,
    ValidTo          datetime2(7)      NOT NULL,
    IsCurrent        bit               NOT NULL,

    CONSTRAINT UQ_dimCustomer_BK_Period
        UNIQUE (CustomerID, ValidFrom)
);

We’ll populate this from the temporal source by materialising all versions.

2.1 Initial load from temporal history

For the first full load, we can flatten the temporal history into SCD rows.

INSERT INTO dw.dimCustomer
    (CustomerID, CustomerCode, Name, DiscountPercent,
     AccountManager, IsActive,
     ValidFrom, ValidTo, IsCurrent)
SELECT c.CustomerID,
       c.CustomerCode,
       c.Name,
       c.DiscountPercent,
       c.AccountManager,
       c.IsActive,
       c.ValidFrom,
       c.ValidTo,
       CASE WHEN c.ValidTo = CONVERT(datetime2(7), '9999-12-31 23:59:59.9999999')
            THEN 1 ELSE 0 END AS IsCurrent
FROM dbo.Customer
FOR SYSTEM_TIME ALL AS c;

FOR SYSTEM_TIME ALL returns both current and historical versions. We use the far-future ValidTo to mark current rows.

This gives us a dimension row for every historical version of each customer.

2.2 Incremental SCD load from temporal changes

For daily or hourly ETL, you don’t want to reload everything. Instead, you want only rows that changed since the last ETL run.

One pragmatic pattern:

1. Track the last ETL run time in a control table.
2. Use FOR SYSTEM_TIME FROM @LastRun TO @ThisRun to get changed rows.

Control table:

CREATE TABLE dw.ETLRunLog
(
    RunID        int IDENTITY(1,1) PRIMARY KEY,
    ProcessName  nvarchar(100) NOT NULL,
    StartedAt    datetime2(7)  NOT NULL,
    CompletedAt  datetime2(7)  NULL
);

Incremental load procedure (simplified):

CREATE OR ALTER PROCEDURE dw.LoadDimCustomerIncremental
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @LastCompleted datetime2(7);
    DECLARE @ThisRunStart  datetime2(7) = SYSUTCDATETIME();

    SELECT @LastCompleted = MAX(CompletedAt)
    FROM dw.ETLRunLog
    WHERE ProcessName = N'LoadDimCustomerIncremental'
      AND CompletedAt IS NOT NULL;

    IF @LastCompleted IS NULL
    BEGIN
        -- No previous run: fall back to full load
        -- In practice, call the full-load procedure here.
        RETURN;
    END;

    -- Get all versions that changed in the window
    ;WITH Changed AS
    (
        SELECT c.CustomerID,
               c.CustomerCode,
               c.Name,
               c.DiscountPercent,
               c.AccountManager,
               c.IsActive,
               c.ValidFrom,
               c.ValidTo
        FROM dbo.Customer
        FOR SYSTEM_TIME FROM @LastCompleted TO @ThisRunStart AS c
    )
    -- Upsert into SCD dimension
    MERGE dw.dimCustomer AS target
    USING Changed AS src
        ON target.CustomerID = src.CustomerID
       AND target.ValidFrom = src.ValidFrom
    WHEN MATCHED THEN
        UPDATE SET
            target.CustomerCode    = src.CustomerCode,
            target.Name            = src.Name,
            target.DiscountPercent = src.DiscountPercent,
            target.AccountManager  = src.AccountManager,
            target.IsActive        = src.IsActive,
            target.ValidTo         = src.ValidTo,
            target.IsCurrent       = CASE WHEN src.ValidTo = CONVERT(datetime2(7), '9999-12-31 23:59:59.9999999')
                                          THEN 1 ELSE 0 END
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (CustomerID, CustomerCode, Name, DiscountPercent,
                AccountManager, IsActive,
                ValidFrom, ValidTo, IsCurrent)
        VALUES (src.CustomerID, src.CustomerCode, src.Name, src.DiscountPercent,
                src.AccountManager, src.IsActive,
                src.ValidFrom, src.ValidTo,
                CASE WHEN src.ValidTo = CONVERT(datetime2(7), '9999-12-31 23:59:59.9999999')
                     THEN 1 ELSE 0 END);

    INSERT INTO dw.ETLRunLog (ProcessName, StartedAt, CompletedAt)
    VALUES (N'LoadDimCustomerIncremental', @ThisRunStart, SYSUTCDATETIME());
END;

This pattern:

• Uses temporal history as the change feed.
• Keeps the SCD dimension aligned to the same validity windows as the source.

You still need to think carefully about late-arriving facts and how your fact-load logic chooses dimension versions (usually based on event date vs. ValidFrom/ValidTo). But the dimension itself is now auditable and complete.

Temporal vs SCD: When to Use Which

You now have two layers of history:

• Temporal history in dbo.Customer / dbo.CustomerHistory.
• SCD Type 2 history in dw.dimCustomer.

They serve different purposes.

Temporal tables are best for

• Operational audit: who changed what, when, at row level.
• As-of queries for source-of-truth systems.
• ETL inputs: deriving change sets without triggers or change tables.

SCD dimensions are best for

• Analytics modelling: stable surrogate keys for facts.
• Time-travel reporting: facts linked to the correct dimension version for the event date.
• Downstream tools: Power BI / SSAS often expect SCD-style dimensions.

Practical pattern for our scenario:

1. Use temporal tables on key operational entities (Customer, PriceAgreement, etc.).
2. Drive SCD Type 2 dimensions from temporal history.
3. Use temporal queries for investigations and audits; use SCD dimensions for everyday analytics.

Common Pitfalls and How to Avoid Them

1. Assuming temporal tables equal full audit

Temporal tables track row versions, not:

• Who made the change (no built-in user information).
• Why the change was made.

If you need user attribution:

• Combine temporal tables with application-side logging (e.g. write user IDs to a separate audit table).
• Or use Extended Events / server-side auditing for specific operations.

2. Joining facts to the wrong dimension version

With SCD Type 2, your fact load must pick the correct version based on event date.

Example pattern for a sales fact load:

INSERT INTO dw.factSales
    (SaleDate, CustomerSK, Amount)
SELECT f.SaleDate,
       dc.CustomerSK,
       f.Amount
FROM staging.Sales AS f
JOIN dw.dimCustomer AS dc
  ON dc.CustomerID = f.CustomerID
 AND f.SaleDate >= dc.ValidFrom
 AND f.SaleDate <  dc.ValidTo;

This ensures each sale points to the customer version that was valid on SaleDate.

3. Over-indexing the history table

Temporal history tables can grow quickly. Over-indexing them:

• Increases write overhead for every update/delete.
• Can hurt OLTP performance.

Practical approach:

• Index the history table on the primary key + period columns if you run frequent temporal queries.
• Avoid wide nonclustered indexes unless you have specific query needs.

Example minimal index:

CREATE CLUSTERED INDEX CX_CustomerHistory_PK_Period
ON dbo.CustomerHistory (CustomerID, ValidFrom);

4. Turning off system versioning without a plan

When you disable system versioning:

ALTER TABLE dbo.Customer
SET (SYSTEM_VERSIONING = OFF);

• No new history rows are recorded.
• The history table becomes a regular table; you can modify it directly.

If you must turn it off:

• Archive or snapshot the history table first if you care about those rows.
• Document the gap period clearly; reports relying on history may need adjustments.

Practical Takeaway: Make Temporal the Source, SCD the Interface

For analytics systems that need auditable history, MS-SQL temporal tables and Slowly Changing Dimensions work best as a layered pattern:

• Use system-versioned temporal tables on operational sources to capture clean, engine-managed history.
• Use that temporal history to feed SCD Type 2 dimensions in the warehouse, with clear validity ranges and surrogate keys.
• Keep fact load logic explicit about which dimension version to use, based on event dates.

If your reports are currently showing “latest-only” values and you’re losing historical context, starting with temporal tables on your core entities and building SCD dimensions on top is a concrete, implementable way to restore trust in your numbers.