Setting up a SQL Temporal Table — Campfire Analytics

Let’s Talk About Temporal Tables

If you are tired of time-consuming, resource-draining and ever-breaking queries that keep track of your data changes, you’re not alone.

Luckily, a tool was introduced in SQL server 2016 to deal with this: Temporal tables.

Also called system-versioned tables, this feature is designed to keep a log of modified and deleted data automatically and easily.

Let’s take a closer look!

How can we use Temporal tables?

The most common use cases for temporal tables include:

  • Slowly-changing dimensions
    • A clean approach to querying changes in data. This is ideal for businesses with processes based on stages, steps, or statuses such as mortgaging companies or subscription-based businesses.
  • Point-in-time analysis
    • Analyzing the state of the data at a particular point in time to examine it as it was initially reported, this is particularly the case with financial reports and statements.
  • Trend analysis
    • Examining data movements over time and calculating trends relative to data changes.
  • Audit
    • Creating an auditing path by keeping track of changes as well as the time they occurred which facilitate data forensics operations and the fulfillment of compliance standards.
  • Backup and recovery
    • Temporal tables could also be used as a method of retrieving data losses or fixing discrepancies with little to no downtime.

How do we implement it?

Let us start with the basics. System-versioned tables consist of the following two components:

  1. Temporal (current) table: These tables represent the main dataset which reflects the current state of the data, comprising only the most up-to-date values.
  2. Historical (history) table: These tables contain several rows per item as they automatically log all the changes in the current dataset.

Once the temporal table is created, a historical table will be systematically generated.

It’s time to create a temporal table!

Doing so may seem complicated [it isn’t, I promise], but follow along with the steps below and you will be well on your way to simplifying your versioning process!

Creating a new temporal table

Using mortgaging data as an example, let us create the “LoanData” table and set the SYSTEM_VERSIONING attribute to ON. One thing to keep in mind is that a primary key column must be assigned to the temporal table, though, no constraints - including a primary key, could be applied to the historical table.

Additionally, two columns of the datetime2 type must be created as these allow us to keep a log and easily query data modifications.

During this step, we will also populate the current table with two dummy loan numbers for demonstration purposes.

Click here to expand code block #1

CREATE TABLE [dbo].[LoanData]
(
  [LoanNumber] INT IDENTITY PRIMARY KEY,
  [CustomerID] NVARCHAR(6),
  [Stage] NVARCHAR(40),
  [LoanOfficerID] NVARCHAR(5),
  [UnderwritterID] NVARCHAR(5),
  [ProcessorID] NVARCHAR(5),
  [DateSubmitted] DATETIME,
  [DateReviewed] DATETIME,
  [DateProcessed] DATETIME,
  [DateUnderwritten] DATETIME,
  [DateSetteled] DATETIME,
  [LoanAmount] MONEY,
  [SysStartTime] DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
  [SysEndTime] DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH
(
  SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.LoanDataHistory)
)
GO

INSERT INTO [dbo].[LoanData]
([CustomerID],[Stage],[LoanOfficerID],[UnderwritterID],[ProcessorID],[DateSubmitted],[DateReviewed],[DateProcessed],[DateUnderwritten],[DateSetteled],[LoanAmount])
VALUES
('055123','Processed', '01193', '02044', '03019','2020-11-25','2020-11-28','2020-12-05',NULL,NULL,549000),
('049034','Submitted', '01125', '02014', '03019','2020-12-05',NULL,NULL,NULL,NULL,319000);
GO

SELECT * FROM [dbo].[LoanData];
SELECT * FROM [dbo].[LoanDataHistory];

As you can see in the results below, both tables contain the same schemas. The temporal table (top) displays the two rows we inserted while the historical table (bottom) remains empty as all values are still current and no changes have taken place yet.

Click here to expand code block #2

UPDATE [dbo].[LoanData]
SET DateUnderwritten = '2020-12-08', Stage = 'Underwritten'
WHERE LoanNumber = 1;
DELETE FROM [dbo].[LoanData]
WHERE LoanNumber = 2;
GO

SELECT * FROM [dbo].[LoanData];
SELECT * FROM [dbo].[LoanDataHistory];

The results show us that both changes have been properly logged in the historical table [LoanDataHistory], while the row associated with loan number 2 is removed from the current table.

Click here to expand code block #3

CREATE PROCEDURE [dbo].[LoanHistoryDelete] (@Id int)
AS
BEGIN
  DECLARE @sql VARCHAR(MAX);
  BEGIN TRANSACTION;
  ALTER TABLE [dbo].[LoanData] SET ( SYSTEM_VERSIONING = OFF );
  SET @sql = 'DELETE FROM [dbo].[LoanDataHistory] WITH (TABLOCKX) WHERE [LoanNumber] = ''' + CAST(@Id AS VARCHAR(40)) + '''';
  EXEC (@sql);
  ALTER TABLE [dbo].[LoanData] SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[LoanDataHistory]));
  COMMIT TRANSACTION;
END;

This procedure works by passing the loan number we need to delete - in this case 2, as a parameter which is then used in a delete statement we run for a short period during which the system versioning is turned OFF.

EXEC [dbo].[LoanHistoryDelete] 2;

Once executed the row is removed from the historical table and the stored procedure is kept for later use.

Click here to expand code block #4

CREATE TABLE dbo.PropertyDetails (
  LoanNumber INT NOT NULL PRIMARY KEY CLUSTERED,
  PropertyType NVARCHAR(50) NOT NULL,
  [Address] NVARCHAR(255) NOT NULL,
  [Address2] NVARCHAR(255) NULL,
  CityName Nvarchar(50) NOT NULL,
  [State] Nvarchar(2) NOT NULL,
  ZipCode Nvarchar(5) NOT NULL,
  ZipExtension Nvarchar(4) NULL
);

GO

INSERT INTO dbo.PropertyDetails (LoanNumber, PropertyType, [Address], [Address2], CityName, [State], ZipCode, ZipExtension)
VALUES
(1,'single family - detached','123, Main street',NULL,'Hillwood','CA','55555',NULL),
(2,'apartment','456, 1st street',NULL,'Tempoville','WA','77777','4321');

GO

ALTER TABLE dbo.PropertyDetails
ADD SysStartTime datetime2 GENERATED ALWAYS AS ROW START
DEFAULT SYSUTCDATETIME() NOT NULL,
SysEndTime datetime2 GENERATED ALWAYS AS ROW END
DEFAULT CONVERT (DATETIME2, '9999-12-31 23:59:59.9999999') NOT NULL,
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);

GO

ALTER TABLE dbo.PropertyDetails
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PropertyDetailsHistory));

GO

SELECT * FROM dbo.PropertyDetails;
SELECT * FROM dbo.PropertyDetailsHistory;

There you have it! The table becomes a system versioned table!

Conclusion

The temporal table is a very valuable tool that automates row-versioned processes, streamlines data archiving, and facilitates slowly changing dimension operations. In combination with how easy it is to implement; this feature is a keeper!

For more details, visit the Microsoft documentation or reach out to our team at info@campfireanalytics.com.

All the code used to create this example Temporal table is available here on Github.

Hamza Bensaid