Implementing SCDs As Temporal Tables

Under no circumstances should you use temporal tables in your data warehouse.

Microsoft introduced the concept of a temporal table with Sequel server 2016. I resisted implementing temporal tables in the data warehouse for a very long time for two reasons.

1. I thought temporal tables were for OLAP systems. I did not see an EDW use case.

2. I did not like the idea of giving up control of marking and retiring records to the system. I do not even like to write triggers so you could imagine how I felt about automagically populated effective to and from dates.

However, I was finally forced to take a look at temporal tables for managing SCDs. Then I was forced to work with the bloody things and I’m here to tell you, temporal tables are the devil’s work. They should not be implemented in an enterprise grade EDW.

At first blush, these things seem like mana from heaven.

• Implementing your SCDs as temporal tables DRASTICALLY reduces the amount of code you have to write to process an SCD.

• The primary key on the dimension doesn’t change.

These things are the serpent telling you to eat the forbidden fruit.

The truth is, from an operational perspective, these things are problematic in the extreme.

1. Microsoft itself list off a big ole pile of limitations.

2. Temporal tables use a special SQL syntax that isn’t intuitive.

3. Temporal queries don’t work over Linked Servers.

4. You can’t run TRUNCATE on temporal tables.

5. You can’t just drop a temporal table.

6. You can’t design temporal tables IN SSMS.

7. Loading historical data manually is task intensive.

Take a pass on temporal tables. Zero ten do not recommend.