Introduction to Bi-Temporal Modeling in Real Applications

Bi-temporal data modeling is a powerful technique for capturing changes to data–and entities–over time. Working with bi-temporal data is non-trivial and can take a bit of time to wrap your head around.

Having spent considerable time working with bi-temporal data in banking and high-stakes government systems where errors have tax implications for citizens and companies, I'd like to share some practical, hard-won insights.

Good material on bi-temporal data techniques is surprisingly hard to come by. Martin Fowler, Richard Snodgrass, Tom Johnston & Randall Weis (who built a consulting practice around the topic) are among the few notable exceptions.

I offer some insights that I have yet to see elsewhere, and I hope this material proves useful to system architects, software developers, and business analysts–whether new to the topic or looking to deepen your understanding.

Bitemporal modeling is useful when you need to ask question such as

• What actually happened to this entity at a given point in time and
• When did we get to know about it?

Capturing this level of temporal granularity is quite tricky for the uninitiated. If you start a job in finance, lawmaking, book-keeping, government or any other industry that requires a high degree of accuracy and auditing, then you will eventually need to know bi-temporal modeling in depth.

In bi-temporal modeling, we capture changes in two dimensions: valid time and recorded time. These dimensions often appear under different names such as business time and transaction time, or system time. Essentially, valid time represents our actual, linear history in the real world, and recorded time is when the change was captured in a specific system.

This affords some interesting properties, such as altering recorded history to make corrections and capturing the time we made the correction in our system.

Typical systems and capturing data changes

Before diving deeper into bi-temporal modeling, let’s take a step back and review the ‘typical’ approach.

Having worked in Information Management, Data & Analytics, and Systems Engineering service lines in Big4 Consulting and on the Data & Reporting team at a bank specializing in cross-border payments, I’ve noticed that companies across industries and teams commonly add “created at” and “updated at” columns to their tables. There’s nothing inherently wrong about that–but knowing when a record was created or last updated offers little in terms of audit trail or analytical capability.

Consider my example below, a table storing real estate entities with ownership information. It’s a deliberately simplified example but it serves the point well:

real_estates table

All we can assert is that this record was created on January 1st and updated a few months later. But, we don’t know what was updated or if any other updates were made during that time.

Now, you might say “okay, but some popular databases like postgres and MS-SQL Server now supports temporal tables,” and that’s true. That’ll help us answer questions like “who was the owner before fk_owner_3?”

Temporal tables will typically have columns such as ValidFrom and ValidTo, and with that our example becomes:

Using a temporal table is a step towards better traceability. But, here we’re still just tracking one time-aspect, and we can make no assertion as to when we knew this to be true.

Sidenote: I’ve found using tabular data to communicate temporal data is a shortcut to misunderstandings. We’ll later see how we can communicate temporal changes efficiently with others.

Bi-temporal modeling shapes how you need to think about designing your domain models.

Obviously, you'll need the time dimensions: valid and recorded, generally modeled as ValidFrom, ValidTo, RecordedFrom, and RecordedTo properties.

The RecordedTo property is somewhat special, and we’ll get into that later.

Keeping track of domain entities across time periods, you'll also need to have a unique business key, as opposed to just a unique row key–such as a primary key in a relation database. This unique business key must stay static throughout the entity’s lifetime, otherwise you'll lose traceability.

(There are alternatives to having a unique, static business key per entity. One alternative approach requires multiple tables that need to be kept in sync. I won’t cover that here.)

Furthermore, you need to be open-minded to how we store data in a relational database when venturing into the bi-temporal space. Getting comfortable having seemingly duplicate data in your tables is a must, and once simple queries now become more complex as accounting for the time aspect is necessary.

The normal paradigm we know where each row in the table represents different entities is swapped for multiple rows can represent the same entity but across time periods. Also, we will have rows in the database that are representing a recorded history we no longer believe to be true because it was corrected by another record for that time period.

Also, the ability to deep-clone an instance also helps keep things tidy–centralizing the copy logic rather than scattering it across the application every place an update may happen.

In short, a bi-temporal entity should at minimum have:

• Valid time aspect: signifying when the record had effect in the real world. Dates here can be retroactive, allowing us to create instances in the past. Proactive allowing future changes, and append which means to add to the latest active record.
• Recorded time aspect: capturing when the change was persisted in our system. This is ‘as of now’ only. We cannot retroactively or proactively capture a change, we can only capture changes in the moment.
• Business key: a stable, unique identifier for an entity across time periods–not to be confused with a table’s primary key in a relation database.
• Clone method: a way to copy all properties of an entity into a new instance, except for the id–we still need unique primary keys in the table.

The basics of bi-temporal data

Talking about bi-temporal data is difficult because there are four dates that need to interact with one another in very specific ways.

Beware: as noted earlier I’ve found that communicating these changes using tabular data, or along X and Y axes quickly becomes confusing. I cover how to communicate bi-temporal data effectively in the next section. So hang on for a second and just accept the madness for a little while.

Let’s revisit the real estates table, enabling bi-temporality, and there are quite a few things going on, that I’ll cover after the table:

We now have an additional row (bolded)–seemingly insignificant, yet profoundly meaningful. It represents what we once believed to be true, but no longer is.

This table tells the same story as before, now with the addition of a business key (bizz_key, shortened for brevity), recorded_from, and recorded_to. The columns that enable bi-temporality. The full history of real estate 123 is now captured.

From 2026–01–01 through 2026–05–10, we believed f_1 to be the perpetual owner of the property. For nine of those days–between 2026–05–01 and 2026–05–10–we were mistaken. We corrected the data retroactively on May 10th 2026, changing the owner to f_3 with a valid date from May 1st 2026.

I hope this example illustrates why tabular data makes reasoning about bi-temporality such as poor experience–and it’s worth noting that this example is pretty much as simple as it gets.

If you’re not convinced, then let me just capture a small change to our dear real estate 123, and you tell me how fast you noticed what the change was.

Communicating bi-temporal changes with your team.

Draw a timeline. It’s that simple.

Draw lines for valid periods. Use circles for recorded from and cross for recorded to, and above the timeline, write T0 (T zero) for the very first valid time marker, T1 for the next, and so on, and C(apture) for recorded time. Right arrow means infinity. Also, I add a record (R) marker next to each line to express the capture order.

For years, that has been my primary tool for communicating bi-temporal data, and with great success — everyone I work with tend to adopt the timeline approach.

Our table can be visualized as:

For whatever reason, timeline visualizations aren’t used in the material on bi-temporality–or at least I haven’t come across an author who uses them. The default seems to be tables and XY graphs. Neither are pleasant to work with.

If you don’t get up to whiteboard, share a drawing, or use any visual aid to communicate bi-temporal changes and challenges, then you’re just waiting for misunderstandings to happen.

Say we want to query “what did we know about real estate 123 on April 10th 2026?”. On the timeline, a point-in-time question is illustrated with ‘PiT’ and a line that goes down vertically crossing all records.

‘PiT’ crosses both R0 and R3, however, R3 has a recorded time later than PiT, so that’ll be discarded. The answer is that the system only new about R0 at that point in time, but we later found it to be untrue, since that record is bi-temporally deleted, as signified by the cross later (recorded_to is set) on the timeline for that record.

If the question however was “what was valid on April 10th 2026”, R3 would be the answer.

Next up

The next planned article covers different types of bi-temporal operations on an entity and how they’re performed. I’ll cover retroactive changes, which we got a brief glimpse of here, proactive changes, and appending history. Reach me at nicklas@mjukvare.com to book a consulting session on bi-temporal modeling.