Working with Temporal Data, Track data changes through time

SQL server 2016 introduces out of box support for working with temporal data. Custom implementations were possible in previous versions, however manageability was an issue. One use case I’ve seen for temporal tables in the field was tracking employee records. The business needs a way to trend employment status i.e.‘Terminated’, or ‘Active’. We will briefly examine a few ways to solve this problem before divulging into temporal tables.

1. Implement a slowly changing dimension design (SCD)
Slowly changing dimension are most frequently used in data warehouse design. A SCD type 2 would meet the trending requirements described above. A type two change tracks changes by inserting a new row anytime an update occurs and then modifies a predefined date column. Here’s how the table structure might look:

temp1

When the business wants to query only active employees they filter EmploymentStatus to ‘Active’ and the Valid_To clause to NULL. SCD design patterns provide an excellent base for analytical applications, however a couple drawbacks:

It takes a skilled database developer to implement and support SCD
Requires a surrogate key to maintain referential integrity

2.DML Triggers
Anytime a row is modified ala update, delete, or insert we have the option to fire an action to perform an audit action. For example, when Matt was terminated on the 16th and his record changed from ‘Active’ to ‘Terminated’. The change can be logged to an audit table using an AFTER UPDATE trigger. Here’s the T-SQL implementation:

CREATE TABLE dbo.Employee(EmployeeID INT,FirstName VARCHAR(20),LastName VARCHAR(25),EmploymentStatus VARCHAR(10))
GO
INSERT INTO dbo.Employee (EmployeeID,FirstName,LastName,EmploymentStatus)
VALUES 
(12345,	'Matt'	 ,  'Sharkey',	'Active'), 
(55123,	'Gus'	 ,  'Johnson',	'Active'),
(33612,	'Juilia' ,  'Smith',	'Active')

CREATE TABLE dbo.AuditTable (EmployeeID INT,
							 EmploymentStatusOld VARCHAR(10),
							 EmploymentStatusNew VARCHAR(10),
							 DateModified DATETIME)

GO

CREATE TRIGGER dbo.AuditEmployeeStatus
ON dbo.Employee
AFTER UPDATE
AS
 INSERT INTO dbo.AuditTable (EmployeeID,EmploymentStatusOld,EmploymentStatusNew,DateModified)
 SELECT d.EmployeeID, d.EmploymentStatus, I.EmploymentStatus,GETDATE()
 FROM deleted AS d
 JOIN Inserted AS I
 ON I.EmployeeID = d.EmployeeID

When we run the update the trigger fires and logs it to our audit table

Temp2

Triggers are quick and dirty but face problems at scale. One can improve performance and manageability of the audit by processing record updates through stored procedures. The trigger above could be written as:

CREATE PROCEDURE dbo.Mod_Log_EmployeeStatus
@employeeID INT,
@EmploymentStatus Varchar(10)
AS
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO dbo.AuditTable
SELECT employeeID,EmploymentStatus, @EmploymentStatus,GETDATE()
FROM dbo.employee
WHERE EmployeeID = @employeeID

UPDATE dbo.Employee
SET EmploymentStatus  = @EmploymentStatus
WHERE employeeID = @employeeID
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK
END CATCH

Let’s see what happens after we drop the trigger and then re-hire Matt.

Temp3

Auditing is maintained without the overhead and idiosyncrasies associated with triggers.

3.Temporal Tables
Implementing change tracking using temporal tables gives the business stronger analytical potential then the previous solutions. The theory of temporal data has deep roots. Interested readers should google the famous white paper “Maintaining knowledge about temporal intervals” by James Allen and the wikepedia on Interval algebra http://bit.ly/2fpZuam

We need to add a few things to our table to make it work including a Primary Key, two DateTime2 columns and a WITH SYSTEM_VERSIONING = ON clause.

CREATE TABLE dbo.Employee   
(    
  EmployeeID INT PRIMARY KEY CLUSTERED ,  
  FirstName VARCHAR(20),
  LastName VARCHAR(25),
  EmploymentStatus VARCHAR(10)
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START  
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END  
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)  
 )    
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

The employee audit table was automatically created in the background.

Temp4

Now let’s import our records into the newly defined table and recreate an update procedure. Notice we were able to reduce the amount of code per the versioning is handled by the system internally.

INSERT INTO dbo.Employee (EmployeeID,FirstName,LastName,EmploymentStatus)
VALUES 
(12345,'Matt'	, 'Sharkey','Active'), 
(55123,'Gus'	, 'Johnson','Active'),
(33612,'Juilia' , 'Smith','Active')


CREATE PROCEDURE dbo.Mod_Log_EmployeeStatus
@employeeID INT,
@EmploymentStatus Varchar(10)
AS
UPDATE dbo.Employee
SET EmploymentStatus  = @EmploymentStatus
WHERE employeeID = @employeeID

Let's go ahead and terminate Matt, then check the history using special temporal syntax.

temp5

We see the ability to traverse the table at a specific point in time is possible.

Let’s check the entire history using FOR SYSTEM_TIME ALL, this simply returns all current records and all changes since inception.

temp6

Now, imagine an errant transaction was ran against the table. Let’s go ahead and forget to add a WHERE clause to the employee table to illustrate:

temp7

We can fix the mess up using the BETWEEN operation or an AS OF operation. Set our system time to right before the transaction occurred and we are restored to the correct state of the data.

temp8