When using a system-versioned temporal table (new in SQL Server 2016), what are the query authoring and performance implications when this feature is used to handle Slowly Changing Dimensions in a large relational data warehouse?
For example, assume I have a 100,000-row
Customer dimension with a
Postal Code column and a multi-billion-row
Sales fact table with a
CustomerID foreign key column. And assume I want to query "Total 2014 sales by customer's postal code". Simplified DDL is like this (omitting many columns for clarity):
CREATE TABLE Customer ( CustomerID int identity (1,1) NOT NULL PRIMARY KEY CLUSTERED, PostalCode varchar(50) NOT NULL, 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); CREATE TABLE Sale ( SaleId int identity(1,1) NOT NULL PRIMARY KEY CLUSTERED, SaleDateTime datetime2 NOT NULL, CustomerId int NOT NULL FOREIGN KEY REFERENCES Customer(CustomerID), SaleAmount decimal(10,2) NOT NULL );
Where it gets interesting is that customers may have moved during the year so the same customer may have different postal codes. And it's even remotely possible that a customer moved away and then moved back, meaning there could be multiple history records for the same customer with the same postal code! My query of "sales by postal code" should be able to calculate correct results regardless of how customers' postal codes change over time.
I understand how to use temporal tables to query the customer dimension alone (e.g.
SELECT * FROM Customer FOR SYSTEM_TIME FROM '2014-1-1' TO '2015-1-1') but I'm not sure how to most accurately and efficiently join to the fact table.
Is this how I should query it?
SELECT c.PostalCode, sum(s.SaleAmount) SaleAmount FROM Customer c FOR SYSTEM_TIME FROM '2014-1-1' TO '2015-1-1' JOIN Sale s ON s.CustomerId = c.CustomerId WHERE s.SaleDateTime >= '2014-1-1' AND s.SaleDateTime < '2015-1-1' AND c.SysStartTime >= s.SaleDateTime AND c.SysEndTime < s.SaleDateTime GROUP BY c.PostalCode
And what are the performance considerations that I should watch out for when making queries like this?