Backdated changes in data history in DWH

Abbreviations

• DWH: Data Warehouse (probably trivial when you were interested in reading so far 😉)
• KF: Point in time from when the record is deemed known
• sKF: smaller VF, i. e. the VF of the record known next earlier to the reference (new) record
• bKF: bigger VF, i. e. the VF of the record known next later to the reference (new) record
• KT: Point in time when the record ceases to be valid
• RP: Reference point in time
• HP: High-point-in-time, i. e. an arbitrary point in time high enough not to get mixed up in operation, usually the maximum point in time a database can represent. It is a convenience replacement for SQL null value to ease querying.
• BK: Business key, i. e. not the technical key of this DWH but maybe the technical key of the delivering system or the real identifying key of the business entity. May be composed.
• DR: Delete record, i. e. a record delivered to make cease the validity of a record of the same BK existing in the target.
• IR: Insert record, i. e. a record delivered to create valid record (anew)
• CR: Change record, i. e. a record delivered to create a new version of a record of the same BK existing in the target.

Premises

• No parallel processing of data or alternatively processing of the data with repeatable read isolation to prevent lost updates.
• VT is meant exclusively, i. e. the valid-to boundary does not belong the interval defined by VF and VT.
• As backdated changes are permitted an CR or DR without existing valid

Directions

• Attribute sKF and bKF (e. g. with lead/lag functions) to the new record
• Distinguish the following cases:
• neither sKF nor bKF have been found
• DR: Into the target insert the new record with KF = RP and KT = RP.
• IR/CR: Into the target insert the new record with KF = RP and KT = HD.
• sKF but no bKF has been found
• DR: In the target update the record of BK with KF = sKF such that KT = RP.
• IR/CR:
• In the target update the record of BK with KF = sKF such that KT = RP.
• Into the target insert the new record with KF = RP and KT = HD.
• bKF but no sKF has been found
• DR: Into the target  insert the new record with KF = RP and KT = RP.
• IR/CR: Into the target  insert the new record with KF = RP and KT = bKF.
• sKF and bKF have been found
• DR: In the target update the record of BK with KF = sKF such that KT = RP.
• IR/CR:
• In the target update the record of BK with KF = sKF such that KT = RP.
• Into the target insert the new record with KF = RP and KT = bKF.

Trivia

I very much prefer the term "know" over "valid" as usually the business history of data is built by the primary system and delivered as a payload attribute (opposed to the key attribute(s)). The time axis that the DWH is to build when keeping the history of data is the one of when the characteristics of data of a specific business key gets known by the DWH and when this knowledge gets erased. E. g. at 2001-01-01 a customer registers ergo business validity is from 2001-01-01. This record gets processed and delivered to the DWH at 2001-01-02 ergo the known from is this point in time. The customer thinks twice and quits its business relation ship the next day after registration i. e. at 2001-01-02. This fact gets known by the DWH the next day again i. e. at 2001-01-03.
Delivery 1 at 2001-01-02 to the DWH:

OPERATION	VALID_FROM	VALID_TO	BK	EMAIL
Insert	2001-01-01	{null}	XYZ	foo@b.ar

DWH state at end of 2001-01-02:

KNOWN_FROM	KNOWN_TO	VALID_FROM	VALID_TO	BK	EMAIL
2001-01-02	{null}	2001-01-01	{null}	XYZ	foo@b.ar

Delivery 2 at 2001-01-03 to the DWH:

OPERATION	VALID_FROM	VALID_TO	BK	EMAIL
Delete	2001-01-01	2001-01-02	XYZ	foo@b.ar

DWH state at end of 2001-01-03:

KNOWN_FROM	KNOWN_TO	VALID_FROM	VALID_TO	BK	EMAIL
2001-01-02	2001-01-03	2001-01-01	{null}	XYZ	foo@b.ar
2001-01-03	{null}	2001-01-01	2001-01-02	XYZ	foo@b.ar