22-25 April 2026 
SQLBits 2012
Temporal Snapshot Fact Table
Snapshots without snapshots...is that possible? Take a "Classic" snapshot fact table, add some temporal data theory and you'll get a new fact table than can store snapshot data without doing snapshots. A life saver when you have a lot of data.
You are designing a BI Solution and your customer ask you to keep a snapshot of the
status of all their documents (orders, insurances, contracts, bills...whatever
the word "document" may mean) for all the days of the year. They have millions of documents
and they want to have in their Data Warehouse all the data they have gathered
right from the very first operating day.
If you have 1 million of documents (on average) and you have to keep a snapshot of them for each one of the 365 days in a year, and you have 10 year of history, you're going to have a 3 billions table just to start with. That's a very big and challenging number, and you may have not the option to buy a Parallel Data Warehouse.
In this session, we'll see how we can turn the usual snapshot tables into temporal table so that we can store time intervals in order to avoid data duplication, while keeping the Data Warehouse design usable by Analysis Services (that doesn't know what an interval is) and optimizing it to have very good performance even on standard hardware.
The explained technique is a result of several month of research and has been applied to the Data Warehouse of an insurance company where we had to deal with two times the number said before.
If you have 1 million of documents (on average) and you have to keep a snapshot of them for each one of the 365 days in a year, and you have 10 year of history, you're going to have a 3 billions table just to start with. That's a very big and challenging number, and you may have not the option to buy a Parallel Data Warehouse.
In this session, we'll see how we can turn the usual snapshot tables into temporal table so that we can store time intervals in order to avoid data duplication, while keeping the Data Warehouse design usable by Analysis Services (that doesn't know what an interval is) and optimizing it to have very good performance even on standard hardware.
The explained technique is a result of several month of research and has been applied to the Data Warehouse of an insurance company where we had to deal with two times the number said before.
Speakers
Davide Mauri's previous sessions
The 10 things every developer must absolutely know about Azure SQL
The engine powering Azure SQL and SQL Server databases has been around for 30 years and has evolved a lot during that time. In this session we'll see many of this "little" but yet incredibly useful features and knowledge bits, with a lot of demos and a lot of lateral thinking.
My application is fully concurrent and async. What about the database?
Let's look at how concurrency works inside the database. If you want to create a truly scalable solution, this knowledge is needed as much as knowledge about threads and synchronization mechanisms in your favorite language.
Jamstack Foundations: Building Scalable API from the Ground Up
In this session, we'll see an end-to-end demo of how we can build a Jamstack site in Azure, using Azure Static Web Apps and Azure Functions, and taking advantage of well-known and less known features of Azure SQL that will make you – the developer – productive and efficient as never before. From Graph Models to JSON, from REST to GraphQL, this code-heavy demo packed session will show all you need to build end-to-end modern, scalable, solutions. CI/CD included.
Practical Azure SQL for the Modern Developer
Come learn how to take advantage of everything Azure SQL can offer to a developer
Temporal Snapshot Fact Table
Snapshots without snapshots...is that possible? Take a "Classic" snapshot fact table, add some temporal data theory and you'll get a new fact table than can store snapshot data without doing snapshots. A life saver when you have a lot of data.