The Version Where the Warehouse Grew a Conscience

Through three versions, KC Star got very good at one thing and completely ignored another. It could invert a star schema, compute every measure, and store aggregates correctly. What it never once asked was whether the data going in deserved that much care. Every ProductPrice, every Discount, every sale was treated as gospel. V4 is the version where the warehouse grows a conscience — where it starts scoring the quality of its own data, tracking where facts came from, and measuring how long its own operations take.

The changelog title is “Data Governance & Quality with Enhanced Monitoring,” and this is the version I think of as the pivot from learning project to production posture. The math was done. V4 is about trust.

What V4 inherits, and adds

V4 keeps the whole V3 dual-table design: base measures in SalesFact, aggregates in SalesAggregationFact with their FactCount, SCD2 everywhere, the two triggers, the version hash. On top of that foundation it layers three governance capabilities:

• Data quality scoring — a DataQualityScore on every dimension and fact row, plus procedures to assess and monitor it.
• Lineage — a record of where each fact came from, implemented (revealingly) two different ways.
• Performance monitoring — timing of the warehouse's own operations, so slow work is visible rather than mysterious.

It also grows the vocabulary. New dimension types arrive — SaleType (back as a governance-tracked dimension), Region and Priority — and the lookup tables sprout hierarchy and categorisation columns (Category, IsHierarchical, ParentDimTypeId, SortOrder on dimensions; MeasureCategory, Unit, Precision, IsRollupable on measures). Two new derived measures, SaleCount (9) and AvgOrderValue (10), round out the set. The warehouse is starting to carry metadata about its data, not just the data.

Quality is opt-out

The single most characterful decision in V4 is how quality is scored: every row is assumed perfect and downgraded only on evidence.

DataQualityScore DECIMAL(3,2) DEFAULT 1.0

That default of 1.0 appears on every dimension and fact table. A row is born with a perfect score; something has to actively knock it down. When the SCD2 examples manually edit a value, they set DataQualityScore = 0.95 to simulate a slightly-suspect correction. The philosophy is optimistic — trust by default, flag exceptions — which is a defensible choice for a system where most data really is fine and you want the low scores to mean something. Part 2 is entirely about living with that decision.

Lineage, twice

The lineage story is where V4 gets genuinely interesting, and a little embarrassing. The warehouse tracks where facts come from in two incompatible ways, depending on which build of V4 you run:

• The root DatabaseV4.sql ships a normalized FactLineage table — a proper graph of ChildFactId → ParentFactId relationships — alongside a SchemaPerformanceMetrics table and a LogPerformanceMetric function.
• The version folder omits both tables and instead threads a denormalized DataLineage text column through SalesDim, populated by a TrackDataLineage procedure.

Same version, two philosophies: a lineage graph versus a lineage string. That is not a mistake I want to hide — it is a real fork in how I thought about the problem, and part 3 treats it as the case study it is. It is also the first strong instance of a pattern that recurs through the rest of the project: the root monolithic file and the version folder do not always contain the same objects, and the folder is the one I treat as ground truth.

Facts that explain themselves

The third governance thread is the quietest and the one I am fondest of. SalesFact.CalculationContext graduates from TEXT to JSONB, and the aggregation table gains statistical columns — MinValue, MaxValue, StdDev, TimeGranularity. A fact stops being just a number and starts carrying a structured record of the inputs and formula that produced it. Ask a V4 fact “how did you get that value?” and it can answer in queryable JSON. Part 4 is about a warehouse that shows its work.

And a way to run the thing

Governance needs somewhere to run. V4 is also the version whose docker stack I lean on — a compose file that stands up PostgreSQL and SQL Server with the schema auto-loaded through V4, pgAdmin pre-wired, one command to a working warehouse. The container init scripts load V1 through V4 specifically, which is why that story lives in this series. Part 5 is the dev environment.

Where the series goes

1. The version where the warehouse grew a conscience — this post.
2. A quality score on every row — the opt-out DataQualityScore, the assessment procedures, and finding suspect data.
3. Two ways to remember where a fact came from — the lineage graph versus the lineage string, and when each pays.
4. Facts that show their work — JSONB calculation context and statistical aggregation columns.
5. One compose file, four databases — the docker dev stack that runs V1 through V4.

Governance is the foundation the enterprise versions build on. Once the warehouse can trust its own data, V5 can start integrating other people's — a whole organizational hierarchy in a second star.