The two clocks your data needs

From System-Temporal-Merge to Bi-Temporal-Merge

This is part of our ongoing data series:

About six months ago, I started this data series. The intent has always been the same: share our journey, including the parts where we got it wrong.

I am part of a team of four - different backgrounds, different skills, each bringing a different lens to the same problem. That diversity is not incidental. It keeps us from settling too early on an answer and makes the work better.

We define our data model by doing: slicing, dicing, questioning, and questioning again. We iterate, try, retry, and only commit when something actually makes sense against real data. In most financial institutions, data models are inherited - systems that have been running for years, sometimes decades, where the cost of change is so high that “good enough” becomes permanent. We don’t have that constraint, and we don’t take it for granted.

We build systems that allow us to unify data, with bi-temporality at the heart of it, and backfilling as a first-class feature. Stay tuned - a blog dedicated entirely to backfilling is coming soon. In my opinion, it is the most underrated feature and skill in data engineering. At Opto, we don’t lose data, we repurpose it.

In Part 1, we shipped System-Temporal-Merge - a load mode that answers: what did our system know, and when?

For static records, that single dimension of time is sufficient. But some data doesn’t just arrive - it evolves. A company changes classification. A manager revises a fundraising target. And sometimes, we learn about that change days or weeks after it happened. System-Temporal-Merge has no way to represent that. It only knows one clock.

The problem with one clock

Let’s make this concrete.

Imagine Blue Owl Capital Corporation. It files a 10-Q in April 2016. You ingest that filing in April 2026. Your system correctly stamps system_valid_from = 2026-04-13. One version. One clock.

Now imagine a new filing arrives: an 8-A12B from July 2019. This changes what the company was - it became listed on the NYSE. That change didn’t happen in 2026 when you ingested it. It happened on July 18, 2019. If you only track system time, you have no way to represent the fact that this company was one thing from 2016 to 2019 - a private BDC - and a different thing from 2019 onwards. You’d have a list of filings, but no timeline of states.

Now take it further. What if the 2019 filing arrives after you’ve already ingested the 2021 8-K - the one where the company rebranded from Owl Rock to Blue Owl? Late-arriving data - something extremely common in private markets - would corrupt your timeline. System-Temporal-Merge would not know where to place it as there is no slot for “insert this into the past.”

You need a second clock.

Two clocks, two questions

Bi-temporal data tracks time across two independent dimensions:

Business time - when something actually happened in the real world. When a state became valid. When it ended.

System time - when our pipeline learned about it. When we first recorded it. When we corrected it.

Every row in a bi-temporal table carries four timestamps:

 Column              | Question it answers
---------------------+--------------------------------------------------------------------
 valid_from          | When did this state start being true in the real world?
 valid_to            | When did this state stop being true? (NULL = still current)
 system_valid_from   | When did our system first record this version?
 system_valid_to     | When was this version superseded by a correction? (NULL = current knowledge)

Business time is the story of the world. System time is the story of our knowledge about the world. They rarely align perfectly - and this can create look-ahead bias, audit failures, and backtest errors.

The algorithm: rebuild per key

When a new batch of records arrives, the bi-temporal-merge algorithm doesn’t surgically update individual rows. It rebuilds the full business timeline for every affected entity, because in practice, data does not arrive in chronological order - a filing from five years ago can land after a filing from last week. Rebuilding from the full append table - a table in which you can only add rows, never change or delete them - guarantees accuracy regardless of arrival order.

Here is the full sequence:

Step 1 - Append to the raw log. Every incoming record is written unconditionally to the append table. This includes a special type of row: a close marker. A close marker is a row with is_close_marker = TRUE and value_hash = 'closed'. It carries no data of its own - its only job is to create an explicit endpoint in the timeline for a given key.

Step 2 - Identify affected keys. From the current batch, extract the relevant set of key hashes. Only those keys will be rebuilt.

Step 3 - Rebuild the full timeline from the append table. Pull all records ever received for the affected keys - not just the new batch. Deduplicate by (key_hash, value_hash, valid_from). This collapses duplicate observations while preserving legitimately repeated states at different business dates.

Step 4 - Apply LEAD to compute valid_to. Order records by (valid_from, value_hash) within each key and look ahead to the next event:

LEAD(valid_from) OVER (
    PARTITION BY key_hash
    ORDER BY valid_from, value_hash
) AS valid_to

Each row’s valid_to becomes the valid_from of the next event. The last row gets NULL - no subsequent event has closed it. Close marker rows participate in this calculation: they set the valid_to of the row before them, creating an explicit gap in the timeline.

Step 5 - Filter out close markers before inserting. Close markers exist only to shape the LEAD calculation. They are never written to the merge table:

WHERE is_close_marker = FALSE

This is what makes gaps possible. A key valid from 2020–2022, then again from 2024 onwards, would appear as two separate open periods - not one continuous one.

Step 6 - Close current system records and insert the rebuilt timeline. Set system_valid_to = NOW on all open rows for the affected keys. Then insert the rebuilt timeline with system_valid_from = NOW and system_valid_to = NULL. The previous understanding is preserved, now closed, and the new understanding is live.

A real example: Blue Owl Capital Corporation (OBDC)

OBDC started as a private BDC in 2016 and listed on the NYSE in July 2019. That transition is a real event, on a specific date, and our system could learn about it at any point.

The source delivers three filing events for CIK 0001655888:

 cik_padded  | valid_from  | form
-------------+-------------+----------
 0001655888  | 2016-04-01  | 10-Q       ← private BDC, operating
 0001655888  | 2019-07-18  | 8-A12B     ← registers securities, becomes listed
 0001655888  | 2021-05-19  | 8-K        ← rebrands to Blue Owl, still listed

After Bi-Temporal-Merge:

 cik_padded | valid_from  | valid_to   | name                           | classification | listing | system_valid_from   | system_valid_to
------------+-------------+------------+--------------------------------+----------------+---------+---------------------+----------------
 0001655888 | 2016-04-01  | 2019-07-18 | Owl Rock Capital Corporation   | BDC            |         | 2026-04-13 21:06    | NULL
 0001655888 | 2019-07-18  | 2021-05-19 | Owl Rock Capital Corporation   | BDC            | LISTED  | 2026-04-13 21:06    | NULL
 0001655888 | 2021-05-19  | NULL       | Blue Owl Capital Corporation   | BDC            | LISTED  | 2026-04-13 21:06    | NULL

As we have said throughout this series, the query pattern is just as important as how you store the data. The same table, without re-ingestion, answers all of these:

What is OBDC today?

WHERE system_valid_to IS NULL AND valid_to IS NULL

Was OBDC listed on January 1, 2020?

WHERE valid_from <= '2020-01-01'
  AND (valid_to > '2020-01-01' OR valid_to IS NULL)
  AND system_valid_to IS NULL

What did our system believe about OBDC on April 14, 2026?

WHERE system_valid_from <= '2026-04-14'
  AND (system_valid_to > '2026-04-14' OR system_valid_to IS NULL)

Full audit trail:

SELECT * FROM classification_timeline
WHERE cik_padded = '0001655888'
ORDER BY valid_from, system_valid_from

The audit trail is complete and the backfill is clean. No look-ahead bias or silent overwrite.

Why we keep going

Building the next generation of private markets data infrastructure is tedious, hard, and some days feels unachievable. There is so much unknown. And some days, what we thought we understood gets challenged all over again.

We persist because we believe we can play a unique role in shaping what private markets data looks like tomorrow. Private markets investing is at an inflection point, and the infrastructure being built today will define what questions can be asked in five years.

Bi-temporality is part of the answer. It is one layer of a foundation that also requires the right storage model, the right orchestration, the right approach to backfilling, and a team willing to rebuild when the first version is wrong.

Some days the work is slow, others we have to rebuild what we thought was done. But we don’t lose data, we repurpose it. And we don’t give up.

For disclaimers, visit https://www.optoinvest.com/disclaimers.