Building a Row Level Provenance Ledger for Real Time Feature Stores

The problem I tripped over: “Which rows actually trained my model?”

I was working on a real-time feature store pipeline (features computed continuously from incoming events). The model performance started drifting, and the usual suspects were there—data quality, upstream changes, delays—but nothing clearly explained which exact source rows were used when the model’s training job ran.

What I ended up building was a row-level provenance ledger for AI data governance: every transformed training feature row carries an immutable record of where it came from, down to the set of input event rows (or their stable hashes). This made debugging drift feel almost mechanical: I could trace any training example back to specific upstream data and transformation logic.

---

The niche piece: “Transformation fingerprint + source event set hash”

The key idea I used is surprisingly specific:

1. Transformation fingerprint: a deterministic hash of the transformation SQL logic.
2. Source event set hash: a deterministic hash of the IDs (or stable hashes) of the upstream source rows that contributed to a given output row.
3. Provenance ledger table: an append-only table that stores (output_row_id, transformation_fingerprint, source_event_set_hash, timestamps, counts, checksums).

If you have these two hashes, you can answer:

• “Was this output produced by the transformation code version I think it was?”
• “Which input rows were involved, even if raw data gets rewritten later?”

This approach is extremely useful for AI data governance because it turns “data lineage” from a vague concept into an auditable, testable artifact.

---

What the pipeline looks like (end-to-end)

For illustration, I used Postgres-style SQL (works the same conceptually in warehouses like BigQuery/Snowflake with minor syntax changes).

Assume we have:

1) Raw events

-- raw_events(event_id, user_id, event_time, event_type, payload)

2) A transformation: compute per-user rolling counts

Example feature row might represent:

• user_id
• window_start, window_end
• rolling_event_count derived from events in that window

3) A governance requirement

For each output feature row, store:

• Which transformation fingerprint generated it
• What set of input event_ids contributed

---

Step 1: Create a deterministic transformation fingerprint

I wanted a fingerprint that changes whenever the transformation logic changes. The simplest reliable version is to hash the transformation SQL string (the exact text you deployed).

Here’s a small Python utility I used during pipeline deployment to generate a fingerprint:

import hashlib

def fingerprint_sql(sql_text: str) -> str:
    normalized = "\n".join(line.rstrip() for line in sql_text.strip().splitlines())
    return hashlib.sha256(normalized.encode("utf-8")).hexdigest()

transformation_sql = """
SELECT
  user_id,
  window_start,
  window_end,
  COUNT(*) AS rolling_event_count
FROM feature_windows fw
JOIN raw_events re
  ON re.user_id = fw.user_id
 AND re.event_time >= fw.window_start
 AND re.event_time <  fw.window_end
WHERE re.event_type IN ('click','purchase')
GROUP BY user_id, window_start, window_end
"""

print(fingerprint_sql(transformation_sql))

Why this works: if the SQL changes, the hash changes. That’s governance-grade traceability: you can later match output rows to the transformation that produced them.

I stored this fingerprint alongside deployments.

---

Step 2: Compute a stable “source event set hash” per output row

The tricky part is the source event set. You can’t just store “some checksum” loosely—you need determinism.

My approach:

• For each output row, collect all contributing event_ids
• Sort them
• Concatenate them with a separator
• Hash the resulting string

That makes the hash independent of insertion order.

Here’s the SQL pattern (Postgres):

A governance ledger table

CREATE TABLE IF NOT EXISTS provenance_ledger (
  output_row_id           TEXT PRIMARY KEY,
  transformation_fingerprint TEXT NOT NULL,
  source_event_set_hash TEXT NOT NULL,
  source_event_count     BIGINT NOT NULL,
  source_event_checksum  TEXT NOT NULL,
  produced_at_utc         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Example transformation that writes both features and provenance

WITH params AS (
  -- In a real pipeline, this would be injected from deployment config
  SELECT
    'REPLACE_WITH_TRANSFORMATION_FINGERPRINT'::text AS transformation_fingerprint
),
base_events AS (
  SELECT
    re.event_id,
    re.user_id,
    re.event_time
  FROM raw_events re
  WHERE re.event_type IN ('click','purchase')
),
feature_windows AS (
  -- Example windows for each user; replace with your real logic
  SELECT
    u.user_id,
    (NOW() - INTERVAL '7 days')::timestamptz AS window_start,
    NOW()::timestamptz AS window_end
  FROM (SELECT DISTINCT user_id FROM raw_events) u
),
joined AS (
  SELECT
    fw.user_id,
    fw.window_start,
    fw.window_end,
    be.event_id
  FROM feature_windows fw
  JOIN base_events be
    ON be.user_id = fw.user_id
   AND be.event_time >= fw.window_start
   AND be.event_time <  fw.window_end
),
aggregated AS (
  SELECT
    -- output row identity: deterministic from feature keys
    md5(
      concat_ws('|',
        user_id::text,
        window_start::text,
        window_end::text
      )
    ) AS output_row_id,

    (SELECT transformation_fingerprint FROM params) AS transformation_fingerprint,

    -- count of contributing events
    COUNT(*)::bigint AS source_event_count,

    -- stable, sorted list -> hash
    md5(
      string_agg(event_id::text, ',' ORDER BY event_id)
    ) AS source_event_set_hash,

    -- extra checksum (optional): hash of concatenated ids with count included
    md5(
      concat_ws('|',
        COUNT(*)::text,
        string_agg(event_id::text, ',' ORDER BY event_id)
      )
    ) AS source_event_checksum,

    -- the actual feature
    COUNT(*)::bigint AS rolling_event_count
  FROM joined
  GROUP BY user_id, window_start, window_end
)
-- 1) write feature rows (example table omitted here)
-- 2) write governance ledger row for each output row
INSERT INTO provenance_ledger (
  output_row_id,
  transformation_fingerprint,
  source_event_set_hash,
  source_event_count,
  source_event_checksum
)
SELECT
  output_row_id,
  transformation_fingerprint,
  source_event_set_hash,
  source_event_count,
  source_event_checksum
FROM aggregated
ON CONFLICT (output_row_id) DO NOTHING;

What each block does (and why)

• params: holds the transformation fingerprint value so it’s recorded per provenance row.
• base_events: filters only event types relevant to the feature.
• feature_windows: defines the time windows used for aggregation.
• joined: links events to windows and keeps event_id for provenance.
• aggregated:
  • builds an output_row_id based on feature keys
  • computes source_event_count
  • computes source_event_set_hash via sorted string_agg
  • computes the actual feature (COUNT(*))
• INSERT ... ON CONFLICT: provenance rows are append-only logically; re-runs won’t overwrite.

---

Step 3: Use the ledger for real AI data governance checks

Once the ledger exists, drift debugging becomes governance-driven:

A) Check transformation code consistency for the training dataset

If a training job is expected to use one transformation fingerprint, enforce it.

-- Example: verify all provenance rows used by a given training run
-- training_run_provenance(training_run_id, output_row_id)
SELECT
  transformation_fingerprint,
  COUNT(*) AS rows
FROM training_run_provenance trp
JOIN provenance_ledger pl
  ON pl.output_row_id = trp.output_row_id
WHERE trp.training_run_id = 'RUN_2026_11_01'
GROUP BY transformation_fingerprint;

If you see more than one fingerprint, you found a silent governance failure.

B) Spot “phantom” input changes that don’t look like volume changes

Sometimes raw data changes in content but maintains similar counts. The source hash catches it.

-- Compare expected vs actual source event set hash for a single output row
SELECT
  pl.source_event_count,
  pl.source_event_set_hash,
  pl.source_event_checksum,
  pl.produced_at_utc
FROM provenance_ledger pl
WHERE pl.output_row_id = 'SOME_OUTPUT_ROW_ID';

If counts match but hashes differ, the contributing rows changed.

C) Data observability gate before model training

I used this as a pre-train gate in practice: training continues only if source hashes match the previously observed baseline window.

-- Example gate: only allow outputs whose source hashes match the last "good" run
WITH last_good AS (
  SELECT trp.output_row_id, pl.source_event_set_hash
  FROM training_run_provenance trp
  JOIN training_run_metadata trm
    ON trm.training_run_id = trp.training_run_id
  JOIN provenance_ledger pl
    ON pl.output_row_id = trp.output_row_id
  WHERE trm.is_good_run = TRUE
),
current_run AS (
  SELECT trp.output_row_id, pl.source_event_set_hash
  FROM training_run_provenance trp
  JOIN provenance_ledger pl
    ON pl.output_row_id = trp.output_row_id
  WHERE trp.training_run_id = 'RUN_2026_11_01'
)
SELECT COUNT(*) AS mismatched_rows
FROM current_run c
LEFT JOIN last_good lg
  ON lg.output_row_id = c.output_row_id
WHERE lg.source_event_set_hash IS NULL
   OR lg.source_event_set_hash <> c.source_event_set_hash;

A non-zero mismatch count fails the governance gate.

---

Step 4: Performance note (what I learned the hard way)

At first, I tried storing the entire sorted list of contributing event IDs. It was expensive (space + computation). Hashing was the win:

• Store a hash + counts + checksum
• Keep the event IDs accessible in raw history (or a lookup table) if deeper forensic analysis is needed
• Use hashes to quickly detect change; only retrieve IDs for investigation

This made the governance ledger cheap enough to run continuously.

---

Conclusions

Building a row-level provenance ledger using a transformation fingerprint plus a stable source event set hash gave me a practical, auditable method for AI data governance in real-time pipelines. I stopped guessing why training drift happened and started proving it: every feature row could be traced back to the exact transformation logic and the exact set of contributing source events (at least via deterministic hashes), with governance checks becoming simple, reproducible SQL queries.