Perform Data Audits in SQL Azure Using Temporal Tables

The support for Temporal Tables has now been extended to SQL Azure databases. With Temporal Tables, you can track changes made to your data and store the entire history either for analysis or for making the information actionable. For example, a compensation logic can trigger based on historical changes resulting from an exception scenario. The important aspect about Temporal Tables is that it can keep data stored over a timeline. Data in context of time can be leveraged in reporting facts valid for that specific period of time. It then becomes very easy for you to gain insights from data as it evolves over a specified period.

Auditing is probably the most significant use case for Temporal tables. Temporal tables are created by enabling system versioning on new or existing tables. The following SQL script creates a table that is temporal system-versioned:

Note that in addition to the regular fields for the Person?entity, there are three additional columns. The ValidFrom?and ValidTo?fields allow time-based tracking of any information updates on the Person table and the WITH?statement allows enabling the historical tracking of changes in the PersonHistory?table.

Create a Person record using the following statement-

Run multiple updates on the table to modify the address field. If you query the history table, you will see records for all the updates made:

You can enable system-versioning on existing tables by altering the schema and introducing the ValidFrom?and ValidTo?columns. The history table becomes the focal point for your auditing needs without requiring you to write any programming logic for the purpose. It then also becomes very easy to perform Point-in-Time analysis such as tracking trends or differences between two points in time of interest. The other popular use case that Temporal Tables enable you to perform is anomaly detection. For example, it can help you figure out a miss in your sales forecast. Temporal Tables are a powerful feature helping you tailor your auditing needs without having to write any code!

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Related Posts