Designing a solution for temporal data

Temporal data refers to data at specific points in time. Storing temporal data is required in situations like data auditing, forensic investigations, maintaining SCDs, point in time recoveries and so on. Azure SQL and SQL server provides a mechanism called Temporal tables to store temporal data.

Temporal tables are specialized tables that keep track of data changes over time. They track the history of data changes like what we had already seen in SCD tables, but in this case the system takes care of managing the time validity period of each row, instead of we having to do it manually. Hence these tables are also called as System-versioned temporal tables.

Note

Temporal table is a concept of Azure SQL database and SQL server. It was not available in Azure Synapse pools as of writing this book.

Let us look at an example of how to create Temporal tables in Azure SQL:

CREATE TABLE Customer
(
  [customerId] INT NOT NULL PRIMARY...