Temporal Tables in a Real Database (Not a Toy Example)

Anyone who has spent time building Kimball-style data marts will find WideWorldImporters familiar almost immediately, and that's the point. Microsoft built it as the reference sample for SQL Server, and it comes in two halves.

The first half, WideWorldImporters, is the OLTP system. It's a wholesale distributor's operational database: customers place orders, the warehouse ships stock, invoices go out. The schemas are named for what they do, Sales, Purchasing, Warehouse, Application, and everything in it reflects the current state of the business, updated in place as transactions happen. Nothing unusual here for anyone who has worked anywhere near an OLTP system before.

The second half, WideWorldImportersDW, is the analytical warehouse built from it, and this is familiar territory that barely needs explaining. It's a conventional star schema. Fact.Sale, Fact.Order, Fact.Purchase, surrounded by conformed dimensions like Dimension.Customer and Dimension.StockItem. Surrogate keys, standard grain, SCD2 dimensions built the same way they get built everywhere else. An ETL layer (SSIS packages calling stored procedures in an Integration schema) periodically pulls changes out of the OLTP side and loads them into the warehouse. Ordinary, the kind of architecture that needs no more than a whiteboard sketch to explain.

Here's where it stops being ordinary, and it's worth pausing on, because it's the one piece of this system that won't map onto anything already built elsewhere.

Some tables on the OLTP side, Warehouse.StockItems among them, aren't plain tables. They're SQL Server system-versioned temporal tables, which means the database engine itself is tracking every change to every row, automatically, with no application code involved. Update a price, and the engine doesn't just overwrite it. It closes off the old row and silently files a copy of it into a shadow history table, on its own, every time.

This sounds like Type-2 slowly changing dimensions because it is exactly that, just enforced by the storage engine instead of by a hand-written ETL job. What actually matters is where that logic lives. With system-versioned tables, the database engine provides that same SCD2 logic natively and transparently, before the ETL layer ever sees the row.

This is worth exploring before we look at the warehouse load logic; the interesting problem in this system, which is the subject of the next article, only makes sense once we understand temporal tables and their limitations.

What System-Versioning Actually Does

Let's open the hood, because "the engine tracks changes automatically" is the kind of sentence that sounds fine right up until someone actually asks how it works.

A system-versioned temporal table is really two tables working together. There's the table already familiar from ordinary querying, Warehouse.StockItems, holding the current row for every stock item. And there's a second table sitting behind it, a history table, holding every row that used to be current and no longer is. Nobody creates the history table directly. SQL Server does, the moment versioning is turned on, and it keeps the two tables in sync without any code written to make that happen.

That two-table pairing shows up directly in the tree, as one node containing two tables.

Every current-table row carries two extra columns: ValidFrom and ValidTo, both datetime2, both maintained by SQL Server.

A row's ValidFrom is the moment it became current. Its ValidTo is either the moment it stopped being current, or, if it's still current right now, a sentinel value out at the end of time (WideWorldImporters, like most implementations, uses 9999-12-31).

When a row is updated, SQL Server doesn't touch it in place the way a plain table would. It closes the existing row (sets its ValidTo to right now) and copies that closed row into the history table, then writes the new values into a fresh row in the current table with a ValidFrom of right now and a ValidTo still sitting at the end of time. Delete a row, and the same closing-off happens, it just never gets a replacement. Either way, the row that used to be there doesn't vanish; it moves into the history table instead.

This is the same Type-2 SCD pattern already used in dimension tables: close off the old row, insert the new one, and stamp both with a validity window. The only difference is where it happens. Normally this logic runs in the mart, as the last step before loading a fact table, so that each fact row joins to the dimension version that was actually true at the time. Here, that same versioning happens one layer upstream, on the source system itself.

Which brings us to the query side, and the one syntax that actually matters here: FOR SYSTEM_TIME AS OF. This clause takes a single point-in-time value and returns each row exactly as it existed at that moment, pulling from the current table or the history table, whichever one actually holds that version.

The ValidTo on the first row and the ValidFrom on the second line up exactly: 2016-05-31 23:00:00. This is the mechanism working exactly the way just described. The old row closes at the exact instant the new one opens, with no gap and no overlap between them.

Answering a question like "what did this row look like at a given moment" against a hand-built dimension table would take real work. It would mean a UNION of the current table with the archive table, then a filter on WHERE @asof BETWEEN ValidFrom AND ValidTo, and a genuinely annoying amount of time spent getting the boundary condition right on that BETWEEN, since off-by-one errors on validity windows come up constantly in this kind of work. FOR SYSTEM_TIME AS OF is SQL Server doing that same union and that same boundary check invisibly, against both physical tables, every time. Put that question to the engine directly, and it locates the answer itself, whether it's sitting in the current table or the history table.

What Temporal Tables Don't Solve

So a temporal table can now answer what a row looked like at any given moment, and the engine finds it, current table or history table, without a line of union logic required. That's a genuinely useful capability, and stopping here would still leave a correct impression of what system-versioning does.

The shape of that question matters, however. It asks what a row looked like at a single point in time, and it returns a single answer: "What did this row look like at 2016-05-31 22:10:00." That isn't the question an ETL load actually needs answered.

An ETL load needs to ask something different: "what changed between the last time I ran and right now, and give me one row per change, so I can go version each of them into the warehouse." This is a range rather than a point-in-time question, and it needs a list of rows back, not just a single row.

FOR SYSTEM_TIME AS OF cannot answer that second question, and this isn't a missing feature. FOR SYSTEM_TIME AS OF takes a single scalar timestamp and returns the rows valid at that instant, nothing more. It has no way to express "every change event since a given time." Enumerating changes still has to be built separately, just as it did before temporal tables existed.

The Kimball-flavored version of this distinction goes like this: FOR SYSTEM_TIME AS OF gives a snapshot query. What ETL needs is a change feed, something closer to genuine CDC, a list of discrete change events since a cutoff, not an as-of view of the world at one instant. Temporal tables provide the first one built in, for free, and provide nothing at all toward the second. That CDC layer still has to be built independently. The temporal table just makes that layer's job easier once built, because it provides a reliable, engine-maintained source to enumerate changes from.

This is the gap sitting between the OLTP side and the warehouse in WideWorldImporters. Closing it requires two pieces of logic, and both are necessary.

Integration.MigrateStagedStockItemData is a hand-rolled Type-2 loader. It reads whatever rows are sitting in a staging table and versions them into the dimension: close off the old row, insert the new one. This is ordinary work, the same pattern used in any Kimball warehouse.

Integration.GetStockItemUpdates is the piece that doesn't exist in a plain Kimball warehouse at all, because a plain Kimball warehouse has no temporal source system to ask. Its job is to find every change event on the OLTP side in the first place, before the loader ever has a row to work with. Without it, the loader has nothing to read.

That second piece is what this article looks at next.

The Two Pieces That Actually Bridge the Gap

Here's what that loader actually looks like:

WITH RowsToCloseOff AS
(
    SELECT s.[WWI Stock Item ID], MIN(s.[Valid From]) AS [Valid From]
    FROM Integration.StockItem_Staging AS s
    GROUP BY s.[WWI Stock Item ID]
)
UPDATE s
    SET s.[Valid To] = rtco.[Valid From]
FROM Dimension.[Stock Item] AS s
INNER JOIN RowsToCloseOff AS rtco
ON s.[WWI Stock Item ID] = rtco.[WWI Stock Item ID]
WHERE s.[Valid To] = @EndOfTime;

INSERT Dimension.[Stock Item] (...)
SELECT ...
FROM Integration.StockItem_Staging;

Closing off the current row and inserting the new one is the whole thing, and it's correct, ordinary, exactly what would have been written regardless.

This proc assumes the staging table already contains the right rows, one per change event, ready to be versioned. Something upstream has to populate it: finding every change on the OLTP side and delivering it in the shape this loader expects. That's the job of Integration.GetStockItemUpdates. Here's what it looks like:

DECLARE StockItemChangeList CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT c.StockItemID, c.ValidFrom
FROM Warehouse.StockItems_Archive AS c
WHERE c.ValidFrom > @LastCutoff AND c.ValidFrom <= @NewCutoff
UNION ALL
SELECT c.StockItemID, c.ValidFrom
FROM Warehouse.StockItems AS c
WHERE c.ValidFrom > @LastCutoff AND c.ValidFrom <= @NewCutoff
ORDER BY ValidFrom;

OPEN StockItemChangeList;
FETCH NEXT FROM StockItemChangeList INTO @StockItemID, @ValidFrom;

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT #StockItemChanges (...)
    SELECT ...
    FROM Warehouse.StockItems FOR SYSTEM_TIME AS OF @ValidFrom AS si
    ...
    WHERE si.StockItemID = @StockItemID;

    FETCH NEXT FROM StockItemChangeList INTO @StockItemID, @ValidFrom;
END;

This code does two things. First, it finds every ValidFrom timestamp across both the live table and the archive table that falls inside the cutoff window; this is the enumeration, the piece FOR SYSTEM_TIME AS OF was never going to provide on its own. Then, for every one of those change events, it opens a cursor and runs a FOR SYSTEM_TIME AS OF query, one row at a time, pulling that row's exact point-in-time snapshot.

Enumerate directly, since nothing built in does it automatically; then let FOR SYSTEM_TIME AS OF do the part it's actually good at. The result feeds a loader that looks exactly like familiar Type-2 code.

Running it against WideWorldImporters today correctly versions every stock item change into the warehouse, price included, with the right Valid From and Valid To on every row. Stopping here would already provide an accurate picture of how a real production system threads temporal history from an OLTP table into a Kimball star schema.

But that loop deserves a second look. One cursor, fetching one row at a time, running one query per row against a temporal table. On a table with a handful of changes since the last load, this never shows up as a problem. On a bulk load, or an initial historical backfill pulling years of change events at once, that loop is doing exactly as many round trips as there are rows to process. That's not a design flaw so much as a very specific, very understandable decision someone made when they hit a real limitation in the syntax, and it's worth understanding why that decision got made before making any changes to it.

That's where we're headed next.

Here's what's actually in place at this point. The source system versions itself, for free, the moment a row changes, with no application code involved. A query clause makes it possible to ask what anything looked like at any moment, without a single line of union or boundary-condition logic written by hand. On top of that sit two pieces of hand-written logic: one that would get written the same way regardless, and one that exists only because temporal tables solve the point-in-time question and leave the change-enumeration question entirely unaddressed.

None of this is a criticism of the design. It's an accurate description of where the free part of the mechanism ends and the part that still needs an engineer begins. That boundary is exactly where the next article picks up. Integration.GetStockItemUpdates, the piece doing the enumeration, solves a real problem the only way its author had available at the time, and that solution runs one round trip to the server per changed row. Ten changes means ten round trips, a cost too small to matter. Ten years of backfilled history means the same cost per row, at a scale large enough to matter a great deal. That cost is what the next article addresses.