11

Jan

Effortlessly Analyze Data History Using Temporal Tables

Real data sources are never static; critical business information changes over time and important decisions often rely on insights that analysts get from evolving data.

Users who track data history aim to answer fundamental questions: How did data look in a specific point in time in the past (yesterday, a month ago, a year ago, etc.)?, what changes have been made?, and when and what were the dominant trends in a specific period of time? Without proper support in the database, however, questions like these have never been easy to answer.

Temporal Tables are a new feature in SQL Server 2016, designed to be the ultimate productivity tool for developing or migrating applications that provide insights from historical data. Temporal Tables allow you to track the full history of changes without additional code and let you focus your data analysis on a specific point in time in a very simple and efficient way.

Getting started with Temporal Tables

Temporal Tables keep data closely related to time context so that stored facts can be interpreted as valid only within the specific period. They are also referred to as a system-versioned temporal table, because the period of validity for each row is automatically maintained by the system (i.e. database engine).

Depending on a scenario, you can either create new system-versioned temporal tables or extend existing ones with temporal attributes.

When the data in a temporal table is modified, version history is built automatically and transparently without additional action from the end user or application. This property makes Temporal Tables an obvious choice when adding data auditing to existing applications because it is not necessary to change how they interact with the database in order to modify or read the latest (actual) state of data.

With Temporal Tables, historical data is just one query away, which gives you very efficient point-in-time analysis without additional latency.

The diagram below depicts a typical user workflow with Temporal Tables:

To get started with Temporal Tables, download the AdventureWorks Database for SQL Server 2016 CTP3 with script samples and follow the instructions in the folder “Temporal.”

To learn the different ways to create Temporal Tables and work with them in your applications, check out Getting started with system-versioned Temporal Tables at MSDN.

Typical scenarios

Temporal Tables can be used in a wide set of scenarios when you need to track data history. We strongly recommend it in the following use cases due to the huge productivity benefits:

Data audit: Turn on system versioning for tables that store critical information where you need to track changes, when, and by which user. Using Temporal Tables will allow you to perform a data audit transparently (without breaking existing applications) and to perform efficient data forensics at any point in time.
Time travel: Use Temporal Tables to reconstruct the state of your data at any point in time in the past, compare data from multiple moments side-by-side, and perform trend analysis for important business indicators. Utilize new temporal querying capabilities to review business reports as they were in the past without changing the report. Zoom in for business analysis at a specific point in time naturally using the built-in querying capabilities.
Slowly changing dimensions: Keep a history of dimension attribute changes in the data warehouse model without additional ETL code.
Repair record-level corruptions: Perform the finest level of data repair in case of accidental corruption made either by a human or application. The affected portion of data can be restored to “last good known state” very precisely, without dealing with backups and introducing downtime to your application.

For detailed descriptions of supported scenarios, refer to Temporal Table usage scenarios on MSDN.

Effectiveness of using Temporal Tables

Temporal Tables are a huge productivity booster for SQL Server developers because they simplify every phase in the data lifecycle, from the object creation through schema evolution, data modification, data analysis to security. The image below summarizes all benefits for users.

Schema maintenance: It is very easy to create new Temporal Tables or extend existing non-Temporal Tables to become temporal system-versioned. SQL Server 2016 automatically creates an accompanying history table applying smart defaults to optimize for typical scenarios (change tracking, temporal querying, data cleanup, etc.). Period columns can be added to existing tables as hidden, which enables you to perform data audit and time travel without breaking existing applications. SQL Server 2016 also allows you to evolve table schema the same way you do with any non-Temporal Table (using ALTER TABLE from Transact-SQL scripts or SQL Server development tools for visual schema editing).
History tracking is completely transparent to running workloads. While users and applications modify the data in the regular way, history is automatically built behind the scenes.
Data analysis: SQL Server 2016 allows you to perform complex time travel querying very easily, using the FOR SYSTEM_TIME clause and hiding all complexity of matching current and historical data from you. The power of temporal querying becomes apparent in combination with views, especially in scenarios when you need to query complex database models including multiple Temporal Tables with foreign key relationships “as of” any point in time in the past.
Data protection: Temporal Tables ensure immutability of historical data, even for users with edit permissions, which makes Temporal Tables a very good candidate for data audit scenarios. Even system administrators must make a conscious choice to remove system-versioning before making any change to historical data. However, all changes to Temporal Table configuration, including state-of-system versioning, can be easily tracked using SQL Server Audit. For more details, check out Temporal Table Security on MSDN.

How Temporal Tables work

A Temporal Table is a configuration that includes two user tables: current, keeping actual (latest) row versions, and history, storing previous versions for every row in case it has ever changed or been deleted. Any operation that inserts new rows affects only the current table during which the database automatically records the period start time based on the begin time of the transaction. Additional overhead of the insert operation is negligible, compared to inserts for non-Temporal Tables.

During the update or delete operations, previous row versions are automatically moved to the history table with the end of period updated to the begin time of the transaction that initiated change in the current table. That operation is referred to as “system-versioning” and it occurs as part of the same transaction that modifies the current table.

System-versioning adds overhead to update/delete operations compared to the non-temporal case because it actually performs two operations. However, overhead is less than in any custom solution users build for temporal data handling (triggers, stored procedures, and application logic).

Querying of current data does not differ from the non-temporal case and it does not introduce any performance overhead either. There are several modes of querying historical data with the FOR SYSTEM_TIME clause. During the processing of query with FOR SYSTEM_TIME, SQL Server transparently adds a union between the current and history tables and propagates temporal predicates to filter data based on their period of validity.

The size of the history table depends on the application DML pattern (insert vs. update/delete) and period of time during which system-versioning was active, but in general, Temporal Tables tend to increase database size more than regular tables. Therefore, it is strongly recommended that you plan for retention of historical data and perform periodic data clean-ups.