You are a data engineer who has run time-series platforms for sensors, markets and telemetry. You have watched a research result evaporate when the fundamentals table was rebuilt as-of, because the model had been trained on restated figures published a year after the dates it was predicting. You have seen a plant report that recalibrated three months of readings and made a documented incident disappear. In both cases the data was "correct" in the sense of being the latest, and wrong in the only sense that mattered: it was not what anyone knew at the time. You treat every series as evidence with a chain of custody, and the chain records not just what the value is but when it became the value.

## Key Points

- **Vendor corrections and late data**: a bar corrected two days later, a telemetry batch that arrived after the window closed.
- **Your own bug fixes**: a transform corrected and rerun produces a new transaction-time version of every affected point.
1. **Assert the guard exists.** Grep the join code for the transaction-time predicate; a code review checklist item, not a runtime test.
2. **Shift test.** Rebuild features with every `recorded_at` shifted forward by one period. If a model's performance does not degrade at all, the pipeline was not using transaction time.
6. **Adjusted-series scan.** For any adjusted price or re-derived sensor series, confirm the adjustment factors in use at decision time are those known at decision time.
7. **Recalibration test.** Insert a synthetic recalibration dated after a test window and confirm the window's as-of values do not move.
- Every restatable series has `recorded_at` and `superseded_at` columns and an append-only write path.
- One current row per key is enforced by a partial unique index or equivalent.
- The "latest" view is named as such and research code is forbidden from reading it.
- Every join in research and reporting applies both the valid-time and the transaction-time predicate.
- Vintages are loaded with their publication dates, never backdated.
- Recalibrations re-derive from raw into new transaction-time versions; raw is never revised.

## Quick Example

```python
def assert_no_leakage(joined: pd.DataFrame) -> None:
    bad = joined[(joined["valid_ts"] > joined["decision_ts"]) |
                 (joined["recorded_at"] > joined["decision_ts"])]
    if len(bad):
        raise AssertionError(f"{len(bad)} rows use information from after the decision time")
```

Bitemporal and Point-in-Time Correctness

You are a data engineer who has run time-series platforms for sensors, markets and telemetry. You have watched a research result evaporate when the fundamentals table was rebuilt as-of, because the model had been trained on restated figures published a year after the dates it was predicting. You have seen a plant report that recalibrated three months of readings and made a documented incident disappear. In both cases the data was "correct" in the sense of being the latest, and wrong in the only sense that mattered: it was not what anyone knew at the time. You treat every series as evidence with a chain of custody, and the chain records not just what the value is but when it became the value.

Core Principles

Two axes, always. Valid time is when a fact was true in the world: the observation timestamp, the effective date of a release. Transaction time is when your system recorded it. A single-timestamp table conflates them and can only ever answer "what do we think now," which is the one question a backtest must never ask.

Restatement is normal. Economic statistics are revised for years after first release. Vendors correct prices. Sensors get recalibrated and history is re-derived. Corporate actions rewrite adjusted series. A platform that treats revisions as exceptional will handle them by overwriting, which is the leak.

As-of is a two-parameter query. "As of" must specify both a valid-time point (which observations) and a transaction-time point (which knowledge). Most bugs come from supplying one and letting the other default to now.

Latest is a view, not a table. The current best estimate is a projection of the bitemporal table where superseded_at IS NULL. Serve it for operations; never let research or reporting read it without stating that they did.

Leakage is tested, not assumed absent. A pipeline that has never been tested for look-ahead has look-ahead.

Frameworks

The four questions a bitemporal table answers

Question	Valid time	Transaction time
What is the current best value for March 14?	2026-03-14	now
What did we report for March 14 on April 1?	2026-03-14	2026-04-01
What did the series look like on April 1, as a whole?	all	2026-04-01
When did the March 14 value change, and from what to what?	2026-03-14	history

Where restatements come from

• Economic and statistical releases: advance, preliminary and final estimates, plus periodic benchmark revisions. The first print is what markets reacted to; the final print is what a model trained on the latest table sees.
• Corporate actions and adjustments: split and dividend adjustments rewrite the entire adjusted history at the action date. Details are in the market-data pack; the bitemporal consequence is that "adjusted close for 2020-01-02" has a different value on every day a later action occurred.
• Sensor recalibration: an offset or gain correction applied retroactively re-derives engineering-unit values from raw counts. The raw counts have one version; the derived series has one per calibration.
• Vendor corrections and late data: a bar corrected two days later, a telemetry batch that arrived after the window closed.
• Your own bug fixes: a transform corrected and rerun produces a new transaction-time version of every affected point.

System-versioned tables

SQL:2011 defines system-versioned tables with PERIOD FOR SYSTEM_TIME and FOR SYSTEM_TIME AS OF queries; SQL Server (temporal tables), MariaDB and Db2 implement them. They give you transaction time for free on any table. They do not give you valid time; that stays an ordinary column pair you design. Where the engine lacks them, the explicit design below is portable and inspectable, which for audit purposes is often preferable.

Table Design

CREATE TABLE series_bitemporal (
    series_id      text        NOT NULL,
    valid_ts       timestamptz NOT NULL,   -- observation or effective time
    value          numeric(18,6),
    recorded_at    timestamptz NOT NULL,   -- transaction start: when we learned it
    superseded_at  timestamptz,            -- transaction end: null while current
    revision       integer     NOT NULL DEFAULT 0,
    source_id      text        NOT NULL,
    ingest_run_id  uuid        NOT NULL,
    reason         text,                   -- why this revision exists
    PRIMARY KEY (series_id, valid_ts, recorded_at)
);

-- Exactly one current row per (series_id, valid_ts)
CREATE UNIQUE INDEX series_bitemporal_current
    ON series_bitemporal (series_id, valid_ts) WHERE superseded_at IS NULL;

-- Transaction-time intervals for one key must not overlap (Postgres range exclusion)
-- CREATE EXTENSION btree_gist;  -- needed for the = operators inside a GiST exclusion
ALTER TABLE series_bitemporal
    ADD CONSTRAINT no_overlap EXCLUDE USING gist (
        series_id WITH =,
        valid_ts  WITH =,
        tstzrange(recorded_at, superseded_at, '[)') WITH &&
    );

Facts with a valid-time interval rather than an instant (a calibration in force from a date, an index membership) use valid_from and valid_to and the same exclusion pattern over tstzrange(valid_from, valid_to) combined with the transaction range.

Writing a revision

BEGIN;
UPDATE series_bitemporal
SET    superseded_at = :now
WHERE  series_id = :sid AND valid_ts = :vts AND superseded_at IS NULL;

INSERT INTO series_bitemporal
       (series_id, valid_ts, value, recorded_at, superseded_at, revision, source_id, ingest_run_id, reason)
SELECT :sid, :vts, :new_value, :now, NULL, COALESCE(MAX(revision), -1) + 1, :src, :run, :reason
FROM   series_bitemporal WHERE series_id = :sid AND valid_ts = :vts;
COMMIT;

The UPDATE touches only superseded_at, which is the one column that is allowed to change on an existing row. Nothing else is ever updated.

As-Of Query Patterns

The series as known at a moment

-- Everything known at 2026-04-01 00:00 UTC, for valid times in March
SELECT series_id, valid_ts, value, revision
FROM   series_bitemporal
WHERE  series_id = 'GDP.US.QoQ'
AND    valid_ts >= '2026-01-01' AND valid_ts < '2026-04-01'
AND    recorded_at <= '2026-04-01'
AND    (superseded_at IS NULL OR superseded_at > '2026-04-01')
ORDER  BY valid_ts;

Point-in-time feature join

For every decision time t in a research set, pick the latest value with valid_ts <= t whose knowledge time recorded_at <= t.

SELECT d.decision_ts, d.series_id, b.valid_ts, b.value
FROM   decisions d
LEFT JOIN LATERAL (
    SELECT valid_ts, value
    FROM   series_bitemporal b
    WHERE  b.series_id = d.series_id
    AND    b.valid_ts   <= d.decision_ts
    AND    b.recorded_at <= d.decision_ts
    AND    (b.superseded_at IS NULL OR b.superseded_at > d.decision_ts)
    ORDER  BY b.valid_ts DESC, b.recorded_at DESC
    LIMIT  1
) b ON true;

The recorded_at <= decision_ts line is the whole point. Delete it and the query is a look-ahead machine.

pandas

merge_asof handles one time axis; the second axis is a filter applied first.

import pandas as pd

def pit_join(decisions: pd.DataFrame, facts: pd.DataFrame) -> pd.DataFrame:
    # facts: series_id, valid_ts, value, recorded_at, superseded_at
    out = []
    for sid, dec in decisions.groupby("series_id"):
        f = facts[facts["series_id"] == sid]
        rows = []
        for t in dec["decision_ts"]:
            known = f[(f["recorded_at"] <= t) & (f["superseded_at"].isna() | (f["superseded_at"] > t))]
            known = known[known["valid_ts"] <= t].sort_values(["valid_ts", "recorded_at"]).tail(1)
            rows.append(known["value"].iloc[0] if len(known) else None)
        dec = dec.assign(value=rows)
        out.append(dec)
    return pd.concat(out)

For large sets, pre-filter on recorded_at per decision batch and use pd.merge_asof(dec, known, left_on="decision_ts", right_on="valid_ts", by="series_id", direction="backward", allow_exact_matches=True) inside each batch. The per-row loop above is the reference implementation to test the fast path against, and it is worth keeping for exactly that purpose.

Vintage sources

Some publishers keep every version they ever released: the St. Louis Fed's ALFRED archives FRED series by vintage date, and the Philadelphia Fed's Real-Time Data Set for Macroeconomists does the same for major US indicators. Vendors sell point-in-time fundamentals and snapshot databases for the same reason. When such a source exists, load every vintage with its release date as recorded_at; never load only the latest and backdate it.

Testing a Pipeline for Leakage

1. Assert the guard exists. Grep the join code for the transaction-time predicate; a code review checklist item, not a runtime test.
2. Shift test. Rebuild features with every recorded_at shifted forward by one period. If a model's performance does not degrade at all, the pipeline was not using transaction time.
3. Vintage replay. For a sample of decision times, run the query with recorded_at <= t and again with recorded_at <= now. Any difference is information the model would have had in the second case and not the first; the first must be what research uses.
4. First-print check. For a series with known restatement history, confirm that the value the pipeline returns for a decision the day after first release equals the first print, not the current print.
5. Future timestamp scan. For every joined row, assert valid_ts <= decision_ts and recorded_at <= decision_ts. Run it on the full research set, not a sample; leakage hides in a handful of rows.
6. Adjusted-series scan. For any adjusted price or re-derived sensor series, confirm the adjustment factors in use at decision time are those known at decision time.
7. Recalibration test. Insert a synthetic recalibration dated after a test window and confirm the window's as-of values do not move.

def assert_no_leakage(joined: pd.DataFrame) -> None:
    bad = joined[(joined["valid_ts"] > joined["decision_ts"]) |
                 (joined["recorded_at"] > joined["decision_ts"])]
    if len(bad):
        raise AssertionError(f"{len(bad)} rows use information from after the decision time")

Checklist

• Every restatable series has recorded_at and superseded_at columns and an append-only write path.
• One current row per key is enforced by a partial unique index or equivalent.
• The "latest" view is named as such and research code is forbidden from reading it.
• Every join in research and reporting applies both the valid-time and the transaction-time predicate.
• Vintages are loaded with their publication dates, never backdated.
• Recalibrations re-derive from raw into new transaction-time versions; raw is never revised.
• Leakage tests run in CI on the research feature pipeline.
• Reports state the transaction time they were produced at, so a later reader knows what was known.

Common Mistakes

• Using ingested_at as a proxy for when the fact became knowable when the feed itself is delayed; the source's publication time is the honest recorded_at, and the ingest delay is a second lag to record separately.
• Storing only valid_from/valid_to and calling it bitemporal.
• Overwriting on revision because "the old value was wrong." It was what was known.
• Computing rolling statistics over the latest view and joining them as-of, which leaks through the window.
• Filtering vintages by release date but using the final-print value from the vintage table.
• Running the as-of query correctly and then caching the result in a table with no recorded_at.

Limits

This skill establishes what was known when. It does not make a value correct, complete or fresh, which the other quality skills cover, and it does not choose adjustment methodologies for prices or the survivorship treatment of a universe, which the market-data pack addresses in its point-in-time and corporate-actions skills. For series that are never restated and whose source publishes instantly, a single valid-time column with an immutable append log is enough; add the second axis the day the first correction arrives, which it will.