What is bitemporal data?

Bitemporal data models two independent timelines: when a fact was true, and when the database recorded it. A bitemporal table stores both, so no record is ever destroyed by a later correction.

Bitemporal modeling is useful when facts can be corrected retroactively, and you need to reconstruct both the corrected historical truth and the information the system had available at an earlier point. 

Here’s an example¹. An employee named Sally is part of a payroll cycle in 2021. Payroll runs on February 25 and pays Sally based on a monthly salary of $6,000. On March 15, HR sends a letter explaining that Sally received a raise to $6,500 on February 15. Ask what Sally's salary was on February 25, and there are two correct answers: $6,500 in valid time, because that was the real rate, and $6,000 in transaction time, because that is what the payroll system knew when it cut the check.

Bitemporal modeling can be implemented in conventional relational databases, and some databases, such as IBM Db2, provide native bitemporal support. If corrections never arrive late, one timeline or an audit log is usually enough.

Defining bitemporal data

In practice, a bitemporal table holds two rows describing the same real-world fact at the same time: one showing the value that's valid now, and another showing the value the database had on record before a correction arrived. Neither row is wrong; they answer different questions.

That second dimension makes bitemporal data different from a valid-time history. One valid-time history answers "what was true at that time." Bitemporal history adds a second coordinate, letting you also ask what the system believed about that valid-time history at some later recording time. For anyone who has to explain a decision made on incomplete information, those are two different questions with different answers.

A bitemporal model preserves a queryable record of the interval between when something becomes true and when it's entered into the system. Other approaches, such as event sourcing or a carefully designed immutable log, preserve related information, too, but usually require a replay or a derived snapshot to answer the same question.

Valid time and transaction time

Valid time is when a fact was true in the real world. Transaction time, often called system time, records when a particular version of a fact was current in the database, using timestamps that the temporal mechanism maintains rather than timestamps an application edits as ordinary business data. In a properly implemented system-time history, those timestamps are generated and preserved by the database itself, not treated as fields a later correction rewrites.

The terminology for these two temporal dimensions is inconsistent across the industry, which is a frequent source of confusion when comparing implementations:

• Valid time is also called effective time, application time, actual time, or state time.

• Transaction time is also called system time, record time, assertion time, or asserted time

SQL temporal terminology distinguishes application-time period tables from system-versioned tables. ISO/IEC 19075-2:2021, the guidance document accompanying the SQL standard itself (ISO/IEC 9075), covers both individually as well as the combined bitemporal case². In the broader temporal-database literature, the corresponding concepts are commonly called valid time and transaction time. The vocabulary differs by source, so when evaluating a temporal database, check which dimension a feature implements rather than what it is named.

A schema built on one conflated timestamp can't be repaired later without rebuilding the history you were trying to preserve. 

Deciding whether you need it

Most systems do not need bitemporal modeling, and adopting it if you don’t have to adds query complexity and storage cost without returning value. What matters is whether a wrong belief in the past had consequences for which you must now account. 

Work through these six criteria. The more of them that are true, the stronger the case for bitemporal modeling:

1. Regulatory or compliance obligation. Are you required to demonstrate what your organization knew and when it knew it? Finance, insurance, and healthcare frequently are.

2. Retroactive corrections. Do historical facts get amended after the fact? Backdated salary changes, amended trades, and corrected lab results all qualify.

3. Consequential action on the old value. Did your system do something irreversible with the pre-correction value, such as issuing a payment or approving an application?

4. Point-in-time reproducibility. Must you regenerate a report exactly as it appeared on a past date, including values later found to be wrong?

5. Late-arriving data. Does information flow in from upstream systems carrying its own timestamps, out of order?

6. System-of-record ownership. If corrections originate elsewhere, transaction time alone will misrepresent your historical queries.

A seventh question runs the other way and acts as a gate rather than a signal: what is your complexity budget? Bitemporal modeling increases version counts, query length, and schema-migration effort. Even with several yes answers above, that cost has to be justified. 

A past decision made on information that later changed calls for bitemporal modeling. Knowing what was true on a past date, without needing to know what you believed at the time, calls for one valid-time dimension. Current state alone needs neither.

Applying this framework usually narrows bitemporal modeling to a few tables rather than an entire schema, which is also the cheapest way to adopt it.

Bitemporal data versus an audit log

An audit log and a bitemporal model solve overlapping but different problems. An audit log records that data changed; a bitemporal model represents valid-time and system-time state explicitly, so both can be queried directly rather than reconstructed. The difference shows up in how you answer a past-state question, not in whether the underlying data was captured.

A typical audit trail stores before-and-after values in a separate change table. Answering "what did this record look like on March 3, as we understood it on March 10" then means replaying the change log forward in application code, against whichever entries were written to derive a state the log doesn't store directly. A sufficiently detailed, correctly ordered audit log can reconstruct the same answer; the bitemporal table gives you that answer as a normal WHERE clause with four date predicates, without a replay step.

The distinction is less about what a system is called and more about what it guarantees. Audit logs are usually built for a person investigating an incident, one change at a time. A bitemporal model supports querying either timeline directly, at any point, without a replay step. If your compliance requirement is "show an auditor the trail of changes," a well-built audit log may suffice. If the requirement is "reproduce the March report exactly, on demand," a bitemporal model gives you that.

Bitemporal data vs. SCD Type 2

Slowly Changing Dimension Type 2 (SCD Type 2) is a warehouse technique for preserving successive versions of a dimension attribute, usually through effective-date ranges maintained by the ETL process. It typically captures something close to valid time alone; whether it also preserves the warehouse's prior belief through a correction, rather than restating history, depends on how the ETL pipeline is built.

The difference is what each pattern guarantees by default. SCD2 answers what a dimension value was, if the ETL preserves that; a bitemporal model answers what the warehouse reported it to be, because the second timeline is part of the design.

Whether bitemporal dimensions should replace SCD2 depends on a tradeoff: preserving prior belief through a correction, against the added join complexity across the star schema.

That selective approach preserves the audit capability where it's needed without imposing two-timeline joins across an entire analytical model.

Bitemporal data vs. cloud time travel

Snowflake Time Travel provides system-level historical snapshots of table data within a retention window. It’s useful for point-in-time recovery and historical querying, but it does not by itself provide an application/valid-time dimension. That's a different guarantee from that of a bitemporal model.

Snowflake Time Travel illustrates the boundary³. It lets you query, clone, and restore data as it existed at an earlier point, using an AT or BEFORE clause. Snowflake documents a standard retention period of one day across all accounts; on Enterprise Edition and higher, permanent databases, schemas, and tables can be configured up to 90 days, while transient and temporary objects remain capped at one day. Once the retention period elapses, permanent objects move into Fail-safe, and the data is no longer queryable through Time Travel.

Time Travel is an excellent safety net for a dropped table or a bad transformation. It isn't a substitute for bitemporal design in Snowflake, because it can't express that a fact became true before it was loaded, and its retention window is shorter than most compliance obligations. Teams that rely on Time Travel for audit requirements typically run into this issue once they need a report from outside the retention window.

Other warehouses' time-travel-style features are worth checking individually rather than assuming they behave the same way.

Inserts, updates, and deletes

A bitemporal history must preserve prior versions rather than destroying them, but the model doesn't specify a physical implementation for doing that. Some implementations close an existing version and insert a new one; others, such as SQL Server's system-versioned tables, allow ordinary UPDATE and DELETE statements while the database preserves the previous versions in a paired history table.

One common representation gives each row four dates in addition to its business columns: 

• A valid-from and valid-to pair describing real-world truth

• A recorded-from and recorded-to pair describing the period during which that version was considered current

This four-column method is a convenient way to reason about the model, but isn’t the only way to build it.

• Insert. Establish a new fact together with its valid period and its recording/system period.

• Correction or update. Preserve the previous recorded belief while adding the corrected interpretation. Closing the old version and adding a new one is the typical approach, though the number of rows this takes depends on the implementation and on whether the change splits an existing valid-time interval.

• Business-time change. Change when a fact is considered valid while preserving the system/record history of that change.

• Delete. The semantics depend on what's being deleted. Ending a fact's real-world validity is usually represented by closing its valid period while its belief history stays intact. A physical deletion from the current table is a different operation, and many implementations, including Db2's system-period tables, retain the deleted row in system-time history.

Remember that bitemporal tables track belief over time. Whatever the physical mechanics, the guarantee is the same: a version's content isn't edited after the fact; only the period it's considered current changes.

That non-destructive guarantee gives this approach both its data integrity benefits and its storage cost, which is why it's worth modeling row-growth implications before implementation. 

Correcting a historical record

A correction closes the belief period of the incorrect row and inserts a replacement row that shares the same valid range but a new recorded range. This is structurally different from an update, which changes what is true rather than what was believed.

Here’s how to understand that, using the Sally payroll example. HR's first letter reported a February 15 raise to $6,500, and payroll recorded it on March 15, 2021. A second letter on April 5 reports that the figure was a typo and the raise was actually to $6,400. The second message shows that $6,500 was never true in the world, yet payroll believed it between March 15 and April 5 and issued money on that basis.

The correction is recorded in three steps:

1. Close the recorded range on the $6,500 row as of April 5. The row stays in the table.

2. Insert a new row with the same valid range starting February 15, the corrected value of $6,400, and a recorded range opening April 5.

3. Leave the $6,000 row covering valid time before February 15 untouched, because it was never wrong. The $6,000 row that had claimed validity from February 15 onward already had its recorded range closed back on March 15, when the first letter arrived, so it stays closed. Only one belief about late February is open at any recording time.

After the correction, the table supports both answers. A query as of today returns $6,400 for late February. A query reconstructing the March 20 view returns $6,500, which is what the March payroll run and any report generated that week would have used.

Here’s how to tell whether you have implemented a correction rather than an update: If the pre-correction value can no longer be retrieved by any query, you performed an update and lost the audit trail. Correction preserves both versions; only the belief window distinguishes them.

This is the operation the design is meant to support, but it’s also where an overly simple implementation is most likely to update the value in place, destroying the history the design is intended to preserve. 

Querying bitemporal data

Bitemporal queries answer four different questions. Naming them explicitly keeps you from answering the wrong one, which is the easiest mistake to make against a table with two timelines.

1. What is true now. Filter on today falling inside the valid range and on an open recorded range.

SELECT salary
FROM employee_salary
WHERE valid_from <= CURRENT_DATE
  AND (valid_to IS NULL OR valid_to > CURRENT_DATE)
  AND recorded_to IS NULL;

2. What was true on a past date, as we understand it today. Move the valid-time predicate to the target date and keep the recorded range open. This shows the bitemporal table using valid time as of the current recording time, producing the same result a uni-temporal table would provide and returning the corrected values. 

SELECT salary
FROM employee_salary
WHERE valid_from <= DATE '2021-02-25'
  AND (valid_to IS NULL OR valid_to > DATE '2021-02-25')
  AND recorded_to IS NULL;

3. What we believed on a past date about a past date. Both dimensions are pinned. This is the query that reproduces a historical report, and it requires a representation that preserves both valid-time and recording-time history. A bitemporal table is the most direct way to get that, though a sufficiently detailed event log can answer the same question with a replay.

SELECT salary
FROM employee_salary
WHERE valid_from <= DATE '2021-02-25'
  AND (valid_to IS NULL OR valid_to > DATE '2021-02-25')
  AND recorded_from <= DATE '2021-03-20'
  AND (recorded_to IS NULL OR recorded_to > DATE '2021-03-20');

The intervals are half-open, with [valid_from, valid_to), with the start included and the end excluded, which lets adjacent periods meet without overlapping. Getting this wrong is the most common off-by-one bug in temporal schemas. An open-ended range is represented as NULL. Some implementations use a far-future date, such as 9999-12-31, instead. This works just as well, but you must replace each IS NULL check above with a comparison to that date. 

4. What we believed at a past moment about a date that was still in the future. It uses the same approach as query 3, with the valid date later than the recording date. This lets you audit a scheduled change that was later revised. 

Putting these queries in views or a small access library, instead of rewriting them each time, helps prevent the mistake of leaving out the recorded-range condition and getting query 2 when you needed query 3. 

Bitemporal joins

Joining two bitemporal tables can require reconciling both valid-time and system/record-time intervals on each side, up to four temporal ranges in the fully bitemporal case, and the result is correct only for the intervals where the relevant ranges overlap. Single-table temporal queries pin a moment; joins must align two independent histories, so the more temporal dimensions that either side carries, the more overlap logic the join needs.

The complication is that a parent record and a child record change on different schedules. A product's price may change in the middle of a variant's lifespan. A customer's address may be corrected after an order referencing it was placed. A naive join on business keys returns a row for every combination of parent and child versions, most of which never coexisted.

Standard SQL provides limited relief here. SQL:2011 and its successors define period predicates and a temporal INNER JOIN, but most production bitemporal joins still need hand-coded interval-overlap logic across both dimensions. Allen's interval algebra enumerates thirteen possible relationships between two time periods, and most of them represent a partial overlap that the join has to resolve explicitly rather than ignore, which is why generic temporal join logic is hard to write once and reuse⁴.

Instead, simplify the problem before joining. Create a snapshot of each table at the needed points in time, then join those snapshots like ordinary tables. Another option is to use bitemporal storage only for tables that really need it, so most joins involve only one temporal table. 

Build vs. buy

Build on a relational database when bitemporal modeling applies to a few tables. Look at a platform with native bitemporal support, including relational systems that already have it, when the requirement spans most of the schema, and the per-table implementation cost dominates.

The build path requires range types, exclusion constraints, and carefully written insert and correction procedures on a conventional relational database. Here’s an example⁵ that shows the technique on PostgreSQL: Range types hold the effective and asserted periods in two columns instead of four⁶; infinity values represent open-ended periods, and GiST indexes with exclusion constraints prevent overlaps. It's a small community project rather than a supported product, so treat it as a reference implementation to learn from and check its current state before depending on it.

IBM Db2 has native bitemporal tables: a CREATE TABLE statement can declare both a SYSTEM_TIME period and a BUSINESS_TIME period on the same table, with the database maintaining the system period and a linked history table capturing prior versions. Outside the relational world, MarkLogic stores valid and system timestamps on JSON or XML documents⁷, and XTDB 2.x treats valid time as a first-class, user-editable dimension alongside a read-only system-time timeline, explicitly supporting a value scheduled to become valid at a future date⁸.

However, a database that gives you queryable history is not necessarily bitemporal. Datomic, for example, provides transaction time⁹; every datum records the transaction that added it, giving you a full audit history. Getting valid time requires modeling a domain-time attribute yourself, which means the second dimension is application logic rather than a database guarantee. Verify which dimensions a system implements natively before treating temporality as solved.

Native support in PostgreSQL, SQL Server, Snowflake, and Db2

Native support varies by database. 

• IBM Db2 provides native bitemporal tables combining system-time and business-time periods in one table.

• PostgreSQL provides native application-time features but not system-time versioning. 

SQL Server provides system-versioned temporal tables but leaves application/valid time to the application schema.

PostgreSQL's temporal tables documentation describes application time as tracking the history of a thing in the world and system time as tracking the history of the database itself, noting that a database doing both is bitemporal¹⁰. The application-time half, with range and multirange columns, temporal primary keys declared with WITHOUT OVERLAPS, and temporal foreign keys declared with PERIOD, is shipped as a native feature in PostgreSQL 18.0. System time is not currently supported; triggers or external extensions are recommended to emulate it. Feature support like this changes between releases, so check current documentation before finalizing a design that depends on it.

SQL Server's temporal tables implement the opposite half¹¹. A system-versioned temporal table pairs a current table with a history table, and the engine maintains two user-named period columns on insert, update, and delete. Microsoft names them ValidFrom and ValidTo. Querying uses the FOR SYSTEM_TIME clause with sub-clauses including AS OF, BETWEEN, and CONTAINED IN. Despite that naming, these columns track transaction time; the engine records when rows changed in the database, not when facts became true in the world.

Limits of bitemporal modeling

Bitemporal modeling preserves a faithful record of what your database was told. It does not make what your database was told correct. It captures a bad correction just as well as a good one.

Bitemporal systems at scale accumulate the same data-quality problems any database does. Changes get partially undone through manual intervention that bypasses the correction procedure, leaving rows whose belief periods don't reconcile. Values flip back and forth between two states, producing long chains of rows that describe noise rather than meaningful history. Records get created by processes that no longer exist, and the temporal metadata around them preserves that confusion rather than resolving it.

Getting value from bitemporal data therefore depends on disciplined write paths. Every mutation must go through the correction and update procedures rather than direct SQL, or the guarantee the model is supposed to provide stops holding. Data governance is a prerequisite for bitemporal modeling.

Temporal primary and foreign keys

A temporal primary key combines the business identifier with the time range, so multiple rows can share an identifier as long as their time ranges do not overlap. Referential integrity works the same way, with an added containment requirement.

In a conventional table, one entity means one row, enforced by a unique primary key. In a bitemporal table, one entity means many rows, one per state per belief period, so the key must include the temporal columns. PostgreSQL expresses this with PRIMARY KEY (product_no, valid_at WITHOUT OVERLAPS), which permits repeated business keys while forbidding overlapping periods for the same key.

Temporal foreign keys are harder, because a child row's reference may span several parent versions. PostgreSQL's documentation states that a temporal reference does not need to be satisfied by one row in the referenced table, and the combination of all matching rows is tested against the referencing row's period¹². A product variant valid across a price change still satisfies its foreign key, because the product existed continuously across that variant's history even though it did so as two rows.

The constraint to design around is containment. A child record's valid range must be fully covered by the union of its parent's valid ranges, or the reference is incomplete for part of the timeline. Enforcing this in a database without native temporal foreign keys means writing the check yourself.

Key design is worth settling before loading data, because changing a temporal primary key later means rewriting every row it constrains.

Performance at scale

Bitemporal query cost grows along two axes at once: more versions per entity, and more predicates per query. Planning for both early is cheaper than retrofitting.

Three mechanisms explain why. 

1. Row multiplication: A change that adds a new version rather than overwriting in place means a table's growth rate is tied to its correction and update rate, not just its entity count. 

2. Index behavior: Range predicates need GiST or similar index support rather than standard B-tree indexes, which is why PostgreSQL backs temporal primary keys with GiST and requires the btree_gist extension for the non-temporal key parts. 

3. Query type: A query that filters on both temporal dimensions has more predicates to satisfy than a single-timeline query, and how well those predicates get pushed down depends on the specific engine and schema. This is worth checking with EXPLAIN on your own workload rather than assuming.

Here are ways to fix that, with the most effective ones first.

• Restrict bitemporal storage to the tables that need it.

• Plan archiving from the start. Decide early how far back the history must remain online, and move older records out rather than letting the table grow without bound.

• Index the recorded dimension for selectivity, typically as the leading column of a composite index, so queries that need only current belief can eliminate superseded versions cheaply. Predicate order in the WHERE clause does not affect this; index design does.

• Index both dimensions deliberately rather than relying on the primary key index to serve every type of access.

• Create as-of snapshots for reports that repeatedly use the same points in time.

Performance work on bitemporal tables is mostly a schema-scope problem rather than a tuning problem, so the decisions that matter are made during design.

Migrating to bitemporal

Retrofitting bitemporal modeling onto a live current-state system is difficult because the history you want to preserve was never recorded. The migration recovers what it can and starts a clean timeline from the cutover.

If an audit log exists, some of that history can be replayed into the new structure, subject to whatever gaps and manual interventions the log contains.

If it’s something you need to do, here are the steps:

1. Scope narrowly. Identify only the tables where retroactive corrections occur, rather than converting the schema wholesale.

2. Seed valid time from existing data. Any effective-date or start-date column already in the schema becomes the valid-time dimension.

3. Open the transaction-time dimension at cutover. Set recorded-from to the migration date for all existing rows, and accept that belief history begins there.

4. Backfill from the audit trail where one exists, treating recovered history as best-effort rather than authoritative.

5. Route all writes through correction and update procedures before the cutover, not after, so the new timeline is trustworthy from its first row.

The expectation to set with stakeholders is that a migration produces full bitemporal capability going forward and partial capability backward. If a compliance requirement covers periods before the cutover, the migration doesn't satisfy it.

Recommendation

Use bitemporal modeling when you need to preserve both valid-time history and the system's historical knowledge of that history, not only when a past decision had consequences, though that's the clearest case. Use one temporal dimension when only one of those two questions matters, and use time-travel or versioning features alone when the requirement is just recovery from accidental changes.

Bitemporal modeling most often fails when it's scoped to the whole schema instead of the few tables that need it, and its query and migration costs become clear. Scope it to where corrections happen, route every write through explicit correction procedures, and standardize the types of queries from the beginning. 

Bi-Temporal data and Aerospike

For teams choosing to build bitemporal modeling on a conventional database rather than a platform with native bitemporal support, the engine underneath matters as much as the schema. Row multiplication makes this costly because every correction adds a version rather than overwriting one, so the database has to hold performance steady as row counts climb into the billions, not just as data volume grows. 

This is where Aerospike is built to help. Performance stays stable as storage fills and systems age, rather than degrading as version history accumulates, and horizontal scaling is simpler when a table's growth is tied to its correction rate rather than its entity count. Flexible, scalable eviction and retention policies make it practical to age out or archive old recorded-time versions without a manual cleanup job, and collection data types and secondary indexes give teams room to model valid-time and recorded-time ranges rather than working around a rigid schema. 

For bitemporal systems that span regions or feed downstream audit and reporting pipelines, Cross Datacenter Replication and change data capture support let historical versions go where they're needed without adding a separate replication layer. The result is a foundation suited for the two-timeline model, on an engine whose latency and availability hold steady as the version history, and the business relying on it, keeps growing.