Bitemporal data
Bitemporal data records both when a fact was true in the business and when a system recorded it. Valid time describes the business timeline, and transaction time describes the system timeline. Keeping both lets a lender distinguish what its records showed at the time from what later corrections say was true.
Example
For illustration, an applicant’s income is recorded as 3,000 on 1 March. On 20 March, it is corrected to 2,600, effective from 1 March, so a decision made on 10 March saw 3,000 even though the corrected figure applies from 1 March.
How business dates and recorded dates appear in a table
A bitemporal table has valid-from and valid-to dates for the period a value applies in the business. It also has recorded-from and recorded-to dates for the period that version of the value was in the system’s records.
In an illustration based on the income correction, the 2,600 row has a valid-from date of 1 March and a recorded-from date of 20 March. The earlier 3,000 row has a recorded-to date of 20 March, preserving what the system held before the correction. The valid-to date marks the end of the business period represented by each row.
What was known then and what is known now
One question is what the records showed on a particular date. The other is what the records now say was true in the business on that date. Those answers can differ when a fact is corrected later.
For example, a reviewer looking at a 10 March decision can establish that the recorded income was 3,000. Looking back after the correction, the records show that 2,600 was effective from 1 March. An auditor or model reviewer may ask both questions to distinguish the information available at the time from the later account of what was true.
Questions we get asked
When did SQL add support for bitemporal tables?
SQL:2011 added system-versioned and application-time period tables. Used together, they give bitemporal tables.
What does system-versioned mean in SQL?
System-versioned time records when the database held a version of a fact. It is the system timeline, also called transaction or load time.
Is application time the same as valid time?
Application time describes when a value applies in the business, which is also called valid or effective time. It is distinct from system-versioned time.
The article on lending data warehouse architecture explains how effective time and load time fit into warehouse layers that preserve data history and decision records.