Two Ways to Remember Where a Fact Came From

Data quality says how much to trust a fact. Lineage says where it came from. V4 adds both — and where quality got one clean implementation, lineage got two, and they do not agree. Depending on whether you run the root DatabaseV4.sql or the version folder, lineage is either a normalized graph or a denormalized string. That fork is not a bug I am hiding; it is a genuine design disagreement I had with myself, preserved in the repository, and it makes an unusually clean case study in when each shape of the same idea actually pays.

The graph: FactLineage

The root build models lineage as a proper relationship table:

CREATE TABLE FactLineage (
    LineageId SERIAL PRIMARY KEY,
    ChildFactId INT REFERENCES SalesFact(FactId),
    ParentFactId INT REFERENCES SalesFact(FactId),
    RelationshipType VARCHAR(50),
    CreatedDate TIMESTAMP
);

Each row is an edge: this fact was derived from that fact, via this kind of relationship. It is a directed graph over SalesFact, indexed both ways (IX_FactLineage_Child, IX_FactLineage_Parent) so you can walk it in either direction — ancestors of a fact, or descendants of one. An aggregate that rolled up ten base facts records ten edges; trace them and you have the exact provenance of the number.

This is lineage done “properly,” in the third-normal-form sense. It composes: because edges are first-class rows, you can query multi-hop derivation (a fact derived from a fact derived from a fact) with a recursive CTE, ask “what would break if this input changed,” and reconstruct the full derivation tree of any value. The root build pairs it with SchemaPerformanceMetrics and a LogPerformanceMetric function, so the governance layer is uniformly relational: lineage is edges, performance is timed rows.

The string: DataLineage

The version folder does something much cheaper. There is no FactLineage table. Instead, SalesDim carries a DataLineage text column, populated by a TrackDataLineage procedure:

-- folder build: lineage as a denormalized column
ALTER TABLE SalesDim ADD COLUMN DataLineage VARCHAR(500) DEFAULT 'System Generated';

A row's lineage is a string it carries around — 'System Generated', or a description of the process that produced it. No joins, no graph traversal; the provenance is right there on the row you are already reading. TrackDataLineage writes a human-readable account of where the row came from, and that is the whole mechanism.

This is lineage as annotation rather than structure. It does not compose — you cannot walk a VARCHAR(500) to find a fact's grandparents — but it answers the ninety-percent question ("where did this row come from?") in zero joins, and it survives being looked at by a human reading a single row.

Which is right? Both, for different questions

The honest answer is that they solve different problems, and V4 shipped both because I could not decide which problem mattered more.

The graph pays when lineage is a first-class query target. If you need impact analysis (“what depends on this input”), multi-hop provenance, or to reconstruct derivation trees, you need edges you can traverse. A string cannot do any of that. The FactLineage graph is the right structure for a governance team that audits how numbers are produced, not just that they were.

The string pays when lineage is context, not a query. If lineage exists mostly so that a human debugging one row can see where it came from, a VARCHAR(500) on the row is faster to read, faster to write, and impossible to get wrong with a bad join. It costs one column and composes with nothing, which is fine if you never needed it to compose.

The trap is choosing the graph reflexively because it is the “correct” answer, then discovering nobody ever writes the recursive CTE and the FactLineage table is just expensive plumbing that logs edges no query reads. I have shipped that mistake elsewhere. The string is the right default until you have a concrete traversal query that needs the graph — at which point the graph earns its complexity.

The uncomfortable part: they diverged

What I will not dress up is that these two implementations live under the same version number. The root DatabaseV4.sql has FactLineage and eleven tables; the folder build has nine tables and the DataLineage column instead. They are not the same schema. If you learned V4 from the folder and then read the root file, you would find a lineage table you had never heard of — and vice versa.

This is the first sharp instance of a pattern that runs through the rest of KC Star: the root monolithic file and the version folder drifted, and the CHANGELOG count tables track neither exactly. My rule, adopted here and held for the whole project, is that the folder split files are ground truth — they are the ones wired together by 09-setup-all.sql and actually exercised by the tests. The root files are alternate builds, sometimes ahead, sometimes behind, always worth reading but never authoritative. V4's lineage fork is where I learned to state that rule out loud, and V5's two implementations and V6's fictional changelog are where it becomes essential.

What lineage is for

Underneath the fork, both implementations serve the same governance goal that quality scoring started: a number in the warehouse should be able to account for itself. Quality says how much to trust it; lineage says where it came from; together they let you answer “should I believe this figure, and if not, where do I go to find out why.” A warehouse that can answer that is a warehouse you can put in front of an auditor.

The graph-versus-string question is really a question about how far you want that accountability to compose. V4 hedged, and preserving both answers turned out to be more instructive than picking one. The next post pushes accountability onto the fact itself: facts that show their work, where a value carries the JSON of its own calculation.