This article could just as well be called creating a historical snapshot table. This type of table is also referenced as a dimension depending on what kind of data repository it’s located in. Personally, I prefer to keep a historical snapshot table in a normalized data store that contains history. This normalized data store is typically the first stopping point from the source system. It is useful because it keeps historical snapshots of what the data looked like in the source system at any point in time.
To get started, let’s create a history table we will use to store the historical values.
History Table for Person
From above, we see that we have 4 additional columns:
- Person_HistoryID – this is a surrogate key specific to our new table.
- ChkSum – contains a CHECKSUM of all the columns used compare data discrepencies.
- DateTime_From – the beginning date in which this record is effective.
- DateTime_To – the ending date in which this record is no longer effective.
First, let’s create our sample source table and populate it with some data
CREATE TABLE Person( PersonID int IDENTITY(1,1) NOT NULL, Title nvarchar(8) NULL, FirstName nvarchar(50) NOT NULL, MiddleName nvarchar(50) NULL, LastName nvarchar(50) NOT NULL, EmailAddress nvarchar(50) NULL, Phone nvarchar(25) NULL, ModifiedDate datetime NOT NULL ) SET IDENTITY_INSERT [dbo].[Person] ON INSERT [dbo].[Person] ([PersonID], [Title], [FirstName], [MiddleName], [LastName], [EmailAddress], [Phone], [ModifiedDate]) VALUES (1, N'Mr.', N'Gustavo', NULL, N'Achong', N'gustavo0@adventure-works.com', N'398-555-0132', CAST(0x000096560110E30E AS DateTime)) INSERT [dbo].[Person] ([PersonID], [Title], [FirstName], [MiddleName], [LastName], [EmailAddress], [Phone], [ModifiedDate]) VALUES (2, N'Ms.', N'Catherine', N'R.', N'Abel', N'catherine0@adventure-works.com', N'747-555-0171', CAST(0x000096560110E313 AS DateTime)) INSERT [dbo].[Person] ([PersonID], [Title], [FirstName], [MiddleName], [LastName], [EmailAddress], [Phone], [ModifiedDate]) VALUES (3, N'Ms.', N'Kim', NULL, N'Abercrombie', N'kim2@adventure-works.com', N'334-555-0137', CAST(0x000096560110E313 AS DateTime)) SET IDENTITY_INSERT [dbo].[Person] OFF
Next, let’s create our history table:
CREATE TABLE Person_History (
Person_HistoryID int IDENTITY(1,1) NOT NULL,
PersonID int NOT NULL,
Title nvarchar(8) NULL,
FirstName nvarchar(50) NOT NULL,
MiddleName nvarchar(50) NULL,
LastName nvarchar(50) NOT NULL,
EmailAddress nvarchar(50) NULL,
Phone nvarchar(25) NULL,
ModifiedDate datetime NULL,
ChkSum int NULL,
DateTime_From datetime NULL,
DateTime_To datetime NULL,
CONSTRAINT PK_Contact_ContactID PRIMARY KEY NONCLUSTERED (Person_HistoryID)
ON "PRIMARY",
CONSTRAINT AK_UC_PERSONID_DATETI_PERSON_H UNIQUE CLUSTERED (PersonID, DateTime_From)
)
Now that we have our tables created, let’s look at the script that will import the data and close out the old records. In the first step, we look for Person records that have changed. We do this by comparing the checksum of the active record stored in the history table with a checksum we dynamically calculate off of the source records. If the checksums do not match, then we close out the currently active record, making it inactive. We then insert a new record containing the new values and make it active.
DECLARE @PersonID_Updated table (PersonID int); DECLARE @DateTime_Inserted datetime; DECLARE @DateTime_Future datetime; SET @DateTime_Inserted = GETDATE(); SET @DateTime_Future = '2079-06-06'; /************************************ * close old record if it exists * and a value has been changed *************************************/ UPDATE ph SET DateTime_to = @DateTime_Inserted OUTPUT inserted.PersonID INTO @PersonID_Updated FROM Person_History ph JOIN Person p ON ph.PersonID = p.PersonID AND ph.chksum != CHECKSUM ( p.Title ,p.FirstName ,p.MiddleName ,p.LastName ,p.EmailAddress ,p.Phone ,p.ModifiedDate ) AND DateTime_to = @DateTime_Future /************************************ * insert any new or closed * out records *************************************/ INSERT INTO Person_History ( PersonID ,Title ,FirstName ,MiddleName ,LastName ,EmailAddress ,Phone ,ModifiedDate ,ChkSum ,DateTime_From ,DateTime_To ) SELECT PersonID ,Title ,FirstName ,MiddleName ,LastName ,EmailAddress ,Phone ,ModifiedDate ,ChkSum = CHECKSUM ( p.Title ,p.FirstName ,p.MiddleName ,p.LastName ,p.EmailAddress ,p.Phone ,p.ModifiedDate ) ,DateTime_From = @DateTime_Inserted ,DateTime_To = @DateTime_Future FROM Person p WHERE NOT EXISTS ( SELECT 1 FROM Person_History WHERE PersonID = p.PersonID ) OR EXISTS ( SELECT 1 FROM @PersonID_Updated WHERE PersonID = p.PersonID )
