How to Implement the Versioned-Fact Pattern for Streaming Corrections

The versioned-fact pattern is the most useful tool for handling corrections from a streaming pipeline without forcing every downstream consumer to learn how the pipeline works internally. This walks through how to implement it on a target table, what the view looks like, how to handle compaction, and what breaks if you skip steps.

The use case: a streaming pipeline emits a windowed aggregate (sum of transactions per minute, count of events per user per hour, whatever). Late events arrive after the initial aggregate is emitted. You need a way to publish corrected aggregates without breaking dashboards or BI tools that already read the original numbers.

Photo by Seraphfim Gallery on Pexels

The basic schema

The target table has the window key, the value, an emitted_at timestamp, and a primary key built from (window_key, emitted_at) or some equivalent uniqueness constraint.

CREATE TABLE window_aggregates (
  window_key TEXT NOT NULL,
  window_start TIMESTAMPTZ NOT NULL,
  window_end TIMESTAMPTZ NOT NULL,
  value NUMERIC NOT NULL,
  emitted_at TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (window_key, window_start, emitted_at)
);

The original emit goes in with the time the window closed. Each correction goes in with a later emitted_at. The table holds the entire history.

The current-view layer

Downstream consumers should never query the raw table. They query a view that selects the latest version per window key.

CREATE VIEW window_aggregates_current AS
SELECT window_key, window_start, window_end, value
FROM (
  SELECT
    window_key,
    window_start,
    window_end,
    value,
    emitted_at,
    ROW_NUMBER() OVER (
      PARTITION BY window_key, window_start
      ORDER BY emitted_at DESC
    ) AS rn
  FROM window_aggregates
) ranked
WHERE rn = 1;

The view is the contract with downstream. Anything that needs "the current truth" reads from the view. Anything that needs historical reconstruction reads from the table.

The emit side

The streaming pipeline emits the original aggregate with emitted_at = window_end + grace_period. The correction job emits corrected aggregates with emitted_at = correction_job_start_time.

def emit_correction(window_key, window_start, corrected_value, batch_id):
    insert(
        window_aggregates,
        {
            "window_key": window_key,
            "window_start": window_start,
            "window_end": window_start + window_duration,
            "value": corrected_value,
            "emitted_at": batch_id,
        },
    )

batch_id is a deterministic timestamp tied to the correction batch (typically the cron schedule's wall-clock time, or the timestamp of the latest event in the batch). This makes re-runs idempotent: the same batch produces the same (window_key, window_start, emitted_at) and collides cleanly on insert.

Idempotent inserts

For PostgreSQL, INSERT ... ON CONFLICT DO UPDATE makes re-runs safe:

INSERT INTO window_aggregates (window_key, window_start, window_end, value, emitted_at)
VALUES (...)
ON CONFLICT (window_key, window_start, emitted_at)
DO UPDATE SET value = EXCLUDED.value, window_end = EXCLUDED.window_end;

For BigQuery or Snowflake, MERGE is the equivalent:

MERGE INTO window_aggregates t
USING corrections_batch c
ON t.window_key = c.window_key
   AND t.window_start = c.window_start
   AND t.emitted_at = c.emitted_at
WHEN MATCHED THEN UPDATE SET t.value = c.value, t.window_end = c.window_end
WHEN NOT MATCHED THEN INSERT (window_key, window_start, window_end, value, emitted_at)
                     VALUES (c.window_key, c.window_start, c.window_end, c.value, c.emitted_at);

Either pattern lets the correction job crash and restart without producing duplicate rows.

Compaction strategy

The versioned-fact table grows linearly with the number of corrections per window. After enough time, the table is mostly historical versions and the view is doing a lot of work to find the latest per window.

The compaction strategy: on a quarterly schedule, run a job that copies the latest version per window (the view's output) into a new table, deletes everything from the original, and writes the latest versions back. This collapses the table to "latest only" while preserving the schema.

For pipelines that need audit history, keep the pre-compaction table as a snapshot in cheap storage (object store, archive table). For pipelines that only care about the current value, drop it after compaction.

When the view stops being enough

For very high-cardinality tables, the window function in the view becomes a hot spot. Two mitigations:

The first is to materialize the view as a table that the correction job updates instead of computing on read. The downside is the materialization layer adds latency and complexity. The upside is read performance is fixed.

The second is to use a database feature like PostgreSQL's DISTINCT ON, which the planner can optimize better than ROW_NUMBER() in some cases. The query becomes:

SELECT DISTINCT ON (window_key, window_start)
  window_key, window_start, window_end, value
FROM window_aggregates
ORDER BY window_key, window_start, emitted_at DESC;

For tables in the hundreds of millions of rows, the DISTINCT ON pattern is often two to three times faster than the row-number version, depending on indexes.

Indexes

The primary key alone is usually enough for the OLTP write path. For the read path through the view, two indexes help:

A composite index on (window_key, window_start, emitted_at DESC) makes the latest-per-window lookup almost free.

A separate index on emitted_at makes the correction job's "find corrections newer than my last run" query fast without scanning the whole table.

Validation

After the pattern is in place, validate two things every day.

The reconciliation against the source system should show near-zero variance after the correction window has closed (typically the day after). If the variance is non-zero after corrections have had time to land, the late-event handling is incomplete and you have a producer regression or a side-output bug.

The compaction job's row counts should match the view's row counts at the moment of compaction. A mismatch means the table is missing windows or has phantom rows. Either is a bug in the emit logic.

What to avoid

Mutating the aggregate row in place. This makes the dashboard correct but destroys the audit trail and breaks idempotency for the correction job.

Using natural keys (window start time alone) as the primary key. This forces UPSERT semantics on every emit, which is harder to debug than INSERT with conflict handling.

Treating the latest-version view as a transient construct. It is the contract with downstream consumers; document it, version it, and treat changes to it like API changes.

How this fits into the larger pattern

This pattern is one piece of a larger late-data-handling design. The other pieces are watermarks, grace periods, side outputs, and reconciliation against the source system. All five together produce a streaming pipeline that downstream consumers can rely on.

The end-to-end design is covered in How to Handle Late-Arriving Data in a Streaming Integration Pipeline Without Corrupting Downstream Reports. The versioned-fact pattern is the last mile before the downstream dashboard; the article walks through everything upstream of it.

When to ask for help

If you are retrofitting this pattern onto an existing pipeline with downstream consumers already in production, the migration plan matters more than the implementation. You usually want to deploy the view alongside the old table, gradually move downstream consumers to the view, validate against historical reports, then turn on the correction job. Skipping the gradual migration is how you accidentally republish incorrect numbers to people who were not expecting them.

137Foundry's data integration service covers this kind of retrofit work, including the migration playbook and the reconciliation reporting that proves the change did not regress anything. The Apache Flink docs and the Iceberg spec are the canonical references for the underlying engine and storage-layer details. The Prometheus docs cover the metrics layer that makes the whole thing operationally observable.

Closing thought

The versioned-fact pattern is more bookkeeping than algorithm. The algorithmic insight is "do not mutate, append with a version." Everything else is execution detail. The pattern's value is that it lets the streaming pipeline correct itself without breaking the contract with every dashboard, BI tool, and report that already consumes its output. That contract is what makes the pipeline trustworthy.