Notifications
Clear all

Point in time Queries on Raw Vault


Posts: 147
Topic starter
(@adarsh)
Estimable Member
Joined: 1 year ago

We are loading data from multiple source systems and each load is done after the end of day process completion of the source system. The Batch of data is marked with a "business date" but we are not storing this business date in all the raw vault tables as it is not provided as a data column in the source tables, we store the business date in the metrics table along with the batch load date. Now the user wants to query the raw vault for point in time/"as was" data, example, the CustomerSize column is "Small" in 31-dec-19 business date and it changed to "medium" on 29-Feb-20 business date, if anybody wants to know the customer size after 29 feb business date as of 31-dec-19 we need to show "Small" instead of "Medium"(which will be the latest record, load end date =high date).

How to model this? 

 

On 01 Jan 2020 the data is loaded for  31st Dec 2019

Hub Customer 

Customer Hkey,Customer Id

XXX1,ABC

Sat Customer

Customer Hkey,load date, load end date,Customer Size,HDIFF

XXX1,ABC,Small,01-jan-2020 0200,31-dec-9999 1259,XX1X

 

On 01 Mar 2020 the data is loaded for 29-Feb-20

Hub Customer 

Customer Hkey,Customer Id

XXX1,ABC

Sat Customer

Customer Hkey,load date, load end date,Customer Size,HDIFF

XXX1,ABC,Small,01-jan-2020 0200,01-mar-2020 0159,XX1X

XXX1,ABC,Medium,01-mar-2020 0200,31-dec-9999 1259,XX2X

Reply
1 Reply