Mehdi Akiki
Published on

Record-Level Provenance: Explain Where a Normalized Field Came From

Authors
  • Mehdi Akiki avatar
    Name
    Mehdi Akiki
    Twitter

Reference

When a normalized customer record says country = MA, I want to answer a simple question: where did this value come from?

“From the synchronization job” is not enough. The job may read three providers, normalize free text, prefer one source, and repair old records. The useful answer names the source observation, transformation rule, and selection decision.

I keep provenance at the field level for values that influence product behaviour.

A normalized value is a derived fact

Suppose two sources provide:

provider A: country = "Morocco"
provider B: country_code = "MA"

The product stores:

{ "customer_id": "c-17", "country": "MA" }

That result may be exact, but it was still derived. A useful record is:

{
  "entity_id": "c-17",
  "field": "country",
  "value_hash": "sha256:...",
  "source_observations": ["obs-a-203", "obs-b-991"],
  "transform": "country-normalizer@4",
  "decision": "prefer_verified_code@2",
  "produced_at": "2026-11-22T08:10:00Z"
}

The value and its evidence now evolve together.

Separate observation, transformation, and decision

I use three steps:

observation: source X reported raw value Y at version Z
transformation: rule R converted Y into candidate C
decision: policy P selected C among candidates

This is close to the useful core of the W3C PROV model: entities are used and generated by activities, with agents responsible where needed.

Keeping the steps separate answers different incidents:

source was wrong
normalizer was wrong
conflict policy selected the wrong source
correct value was later overwritten

One source = provider_a column cannot do this.

My compact lineage schema

I do not need a universal graph platform for every integration. A relational model can start with:

type Observation = {
  id: string;
  provider: string;
  sourceRecordId: string;
  sourceVersion: string;
  sourceField: string;
  rawValueHash: string;
  observedAt: string;
};

type Derivation = {
  id: string;
  outputEntityId: string;
  outputField: string;
  outputVersion: string;
  inputObservationIds: string[];
  transformVersion: string;
  decisionVersion?: string;
  status: "selected" | "rejected" | "superseded";
  producedAt: string;
};

I store stable references and hashes. Raw values remain in their governed source store unless audit requirements justify a protected copy.

Dataset lineage is not automatically field lineage

A job reading accounts and addresses and writing customers establishes a broad relationship. It does not prove that customers.country came from addresses.country_text.

OpenLineage now documents explicit dataset and field lineage edges to avoid inferring every input affects every output. The same principle applies at record level: record the edges actually used.

Otherwise a lineage graph becomes a dense map that answers nothing.

Conflicts need losing evidence too

If provider A says Morocco and provider B says France, storing only the winner hides the conflict.

I retain bounded decision evidence:

candidate A normalized to MA, trust tier 2, observed yesterday
candidate B normalized to FR, trust tier 1, observed today
policy selected B because verified registry outranks profile text
candidate A remains rejected, not deleted

When policy changes, I can identify affected records and recompute them. I do not need to replay every source payload blindly.

Provenance supports repair and replay

Suppose country-normalizer@4 incorrectly mapped one historical spelling. With derivation records I can query:

all selected fields produced by transform version 4
whose source field matched the affected rule
and whose current output still has that derivation

The repair writes a new output version and provenance edge. It does not rewrite the old evidence as if the mistake never existed.

This works well with idempotent jobs because a derivation has a stable identity based on output, inputs, and rule versions.

Provenance is not a copy of every payload

Copying whole requests into a lineage table creates privacy, retention, and security problems.

I classify fields:

EvidenceTypical storage
stable source identity/versionprovenance record
non-sensitive code or bounded valueoptional direct value
personal or secret raw valuesource reference plus hash
transformation configurationimmutable versioned artifact
decision explanationstructured reason code

Access control follows the sensitivity of the source. A hash can confirm equality; it does not make a low-entropy value anonymous.

Do not put provenance in application logs only

Logs expire, sample events, and are difficult to join reliably. I use traces for execution diagnosis and durable provenance for product explanation.

trace: why was this run slow or failed today?
provenance: why does this field have this value now?

A trace ID can connect both, but it is not the durable identity of the derivation.

Verify one record end to end

My test creates two source observations, runs normalization and conflict selection, then asserts:

  • the selected normalized value is correct;
  • both candidate observations remain addressable;
  • the exact transform and policy versions are stored;
  • the losing candidate has a reason;
  • retrying creates no duplicate active derivation;
  • changing the rule creates a new output version;
  • restricted raw values never enter ordinary logs;
  • deleting source data follows the retention policy without leaving broken anonymous references.

I also sample production outputs and reconstruct them from retained evidence. A provenance schema that is never exercised can quietly become incomplete.

Choose granularity from the question

Provenance can become more expensive than the data if every character transformation creates a node. I start from questions the product must answer:

Which provider supplied this decision-bearing field?
Which rule changed it?
Why did this candidate win?
Which outputs must be repaired after one rule defect?

One derivation record may cover a deterministic group of fields when they always share inputs and lifecycle. Identity, permissions, money, and conflict-selected fields usually deserve individual edges. I measure record volume and reconstruction success together; reducing storage while making repair impossible is false economy.

My design rule

I add field provenance where a value can affect money, authorization, identity, compliance, routing, or a hard-to-reverse decision. I may use coarser dataset lineage for disposable analytics projections.

The goal is not to record everything. It is to preserve enough evidence to explain and repair important facts. Once observation, transformation, and selection each have an identity, a normalized field stops being an unexplained number in the final table.