Data

Modeling Temporal Data - Applications

This blog series continues my thoughts on how to model temporal data. This entry is focused on the backend data model for applications.

Ideas From Prior Posts

The following recaps the relevant ideas from my first two entries in this series:

• Where appropriate, we want our data to have the benefits of bitemporal data
• Implementing bitemporal is challenging because support is uneven in standard databases and the databases designed to support it are too niche to adopt
• Event sourcing is an alternative model that can yield bitemporal data, but it too is very complex to implement
• Event sourcing also requires the replaying of history to get to a selected point-in-time state
• A key principle of Data Vault 2.0 is the ability to get point-in-time state by pulling a set of records associated with that point in time, without the need to replay history
• Another key principle of Data Vault 2.0 is using link tables (with versioning) to join records rather than using embedded foreign keys
• Document databases held the promise of simplifying data structures by reducing the number of tables, particularly by allowing one-to-many relationships to exist as embedded arrays of records rather than requiring joins to a different table
• Because document databases are schema on read vs schema on write, managing schema drift can be a nightmare; schemas for document databases are usually enforced in the application layer rather than the database layer, which is not best practice

The Lure of Document Databases

Much ink has been spilled about the rise of MongoDB. The lure of the document database is compelling: instead of storing across many different tables, document databases let you put all of the data associated with a particular subject into a single record. All CRUD (Create, Read, Update, Delete) impacts a single record in the database, greatly reducing the number of "tables" and simplifying database operations.

As you might infer from the above, modeling bitemporal data in a document database is MUCH simpler. The idea for Event Driven Changes below stems from the notion that (1) the individual record, versus the table, is paramount and (2) the single record holds all of the relevant information. Each record in the event table can "embed" all of the relevant entity data within the event record. Getting the state is a single pull of the event record that is active at the desired time x. Bitemporal joins across many tables are particularly tricky because they require joining not just on table keys but also on the time ranges for each of the two sets of bitemporal columns. The single record concept has no joins, no versioning, no obvious complications.

In practice, however, the limitations of document databases (see last bullet in the prior section) overwhelm the benefits. Which is why the design in the next section leverages these document database ideas but applies them to a traditional relational database. The mental model of the database implementation is:

• the data is conceptualized on an individual record level (i.e. Domain Driven Design aggregate level) rather than the table level
• the tables exist to hold the "shredded" version of the single document database record and are used to define and enforce the schema

Event Driven Changes

Transactions are the proper way for an application to update the backend database. For most applications, a single transaction tends to impact more than one database table. In insurance, this is particularly true as a transaction can simultaneously update the policy record, insured people / organization records, insured asset records, and so on. The typical bitemporal approach is to manage state table by table. I would instead suggest that state should be managed event by event.

An event triggers a single database transaction that can include one or more activities or "sub-transactions". When the single transaction is complete, a given set of database records has been changed from a "before-event-state" to an "after-event-state".

As in prior posts, my examples will be drawn from property and casualty (i.e. general) insurance since that domain is my expertise. A simple common insurance example: an insured purchases a new car and trades in their old car. The event is "buy a new car", which starts the single database transaction. The activities / sub-transactions are add new car and remove old car. The before-event-state is the policy with the old car and the after-event-state is the policy with the new car. The policy event record is linked to all of the relevant (policy, insured, asset, etc.) records associated with the event's after-event-state.

In this example, the bitemporal columns exist only on the policy event table. The database transaction creates the effective / expiration dates (actual history) and the recordStart / recordEnd dates (record history) on the new policy event record, and updates the recordEnd date for the prior "active" policy event record. A copy of the prior "active" policy event record should then be created with:

• the proper expiration date
• the recordStart date set equal to the recordEnd date that was used to update the original prior "active" policy event record
• the recordEnd date set to null (or 3999-01-01)

An example table that shows the prior active records along with the new active record:

What does this mean in practice? Every table (policy, insured, asset, etc.) in the data model has versioned records of its entity, but without temporal columns. For example, the vehicles table will contain multiple rows per vehicle, where the composite surrogate key is the vehicle_id plus the version_id. Every time a policy that has this vehicle is updated (via an event), the vehicle record can change. This versioning can be done in two ways:

• The vehicle gets a new version every time a policy event occurs on the policy to which the vehicle belongs
• The vehicle gets a new version only when a policy event occurs on the policy to which the vehicle belongs AND an attribute of the vehicle changes

The first method is easier to implement and reason about, primarily because each record can have a foreign key back to the policy event that created it. The state of the policy at time x is derived by finding all of the associated data (via policy event foreign keys in the various entity tables) that link back to the selected policy event record that is active at time x.

The second method significantly reduces the amount of data stored and is the preferred method, but requires a junction table because the joins become many-to-many (inspired by the Data Vault 2.0 link table concept). The state of the policy at time x is based on finding the relevant policy event record that is active at time x and then using the links to pull the rest of the associated data (borrowing the Data Vault 2.0 idea of getting point-in-time state by pulling a set of records). Unlike event sourcing, no replay is needed to obtain the point-in-time state.

Why Now?

I have not seen the above method for managing bitemporal data in a relational database written about elsewhere. I suspect partly because (1) bitemporal has been niche, and (2) hardware improvements and recent database features now make this method practical to implement:

• Databases can now enforce the event table rules rather than leaving them to application code. For example, if you choose to use PostgreSQL you can take advantage of range types, GiST exclusion constraints, and partial unique indexes. Other mainstream databases (Oracle / SQL Server) cover parts of these features.
• Storage costs are no longer a significant concern. Random I/O is no longer a penalty given the prevalence of NVMe SSDs and the wide availability of machines with large RAM. A caveat: hardware prices are rising today due to the demand surge from GenAI. Because storage, I/O, and RAM are "solved", immutable and / or denormalized data that was relatively expensive 30 years ago is much less of a concern.
• Public cloud and SaaS (Software as a Service) vendors allow users to take advantage of these database and hardware improvements without building their own data center.

This method is a composition of many different ideas developed over the last 30 years, including:

• Data Vault (2000) and Data Vault 2.0 (2013)
• Event sourcing (2005) and CQRS (late 2000s)
• The rise of MongoDB (early 2010s)
• Wider use of immutable data, including:
  • The emergence of git (late 2000s) and git concepts applied to data such as lakeFS and Project Nessie (2020s)
  • The creation of the Datomic database (2012)

When to Avoid?

This modeling technique should work well on data generated by humans. This technique is not suitable for use cases with high-volume, machine-generated data (logs, telematics, sensors).

Conclusion

Much has changed in the last 30 years and our data modeling techniques should take advantage of the improvements in modern hardware and databases. My proposed solution for bitemporal data is to:

• put all temporal columns on the event table(s)
• version all non-event tables without the temporal columns
• use link tables rather than foreign keys to join the event table(s) and the entity tables

The final post in the series will discuss how we can apply similar concepts to analytical data versus application data.