The Star Schema I Turned on Its Side

Every star schema I have ever worked with has the same failure mode: the business asks for a new dimension. Not a new value — a new kind of thing to slice by. In a classic Kimball design that means a new column or a new dimension table, a migration, a reload, and a quiet week of hoping nothing downstream noticed.

KC Star is my long-running answer to that itch: a PostgreSQL data warehouse where dimensions and measures are stored as rows, not columns. Adding a new dimension is an INSERT into a lookup table. No DDL, no migration, no reload. I call it an inverted star schema, and I versioned it explicitly — V1 through V9, each version a folder of numbered SQL files with its own README and changelog — so the design's evolution is itself a record of what worked and what didn't.

This series covers V1, the foundation. Later series walk the whole road: aggregations (V2–V3), data governance (V4), organizational hierarchies (V5), permissions (V6), batch processing at scale (V7), dependency-driven recalculation (V8), and the version I would actually deploy (V9 Simplified). The version boundaries are the story; I kept every one.

The inversion

A traditional sales fact table looks like this: one row per sale, one column per attribute.

KC Star keeps that table — Sales, fed by Product and Customer — but treats it as an input, not the warehouse. The warehouse is two tall, narrow tables:

• SalesDim — one row per (sale, dimension). A sale with seven dimensions becomes seven rows, each tagged with a DimTypeId pointing into a DimTypeLookup table.
• SalesFact — one row per (sale, measure). Each row carries a MeasureTypeId into MeasureTypeLookup, a computed FactValue, and a CalculationContext recording the inputs that produced it.

The pivot happens on read, in a view, with the MAX(CASE WHEN ...) trick every SQL developer eventually memorises. Storage is rows; presentation is columns.

What that buys:

• Schema evolution without DDL. A new dimension is a lookup row plus data rows. The tables never change shape.
• SCD2 for free, everywhere. Because every dimension value is its own row, versioning a value means expiring one row and inserting another. History is uniform across all dimensions — there is no “we only track history on these three columns” special-casing.
• Time travel. With effective/expiry timestamps on every dimension row and validity ranges on every fact row, reconstructing a sale as it looked on any date is a filter, not an archaeology project.
• Lineage. Every fact row records what it was computed from.

What it costs — and I will be honest about this throughout the series — is that every read is a pivot, every value is a VARCHAR(255) until proven numeric, and referential discipline moves from the type system into your triggers. V1 is where I found out exactly how much discipline that takes.

What V1 actually contains

V1 is ten SQL files, numbered so they run in order: database setup, lookup tables, traditional tables, the dimension table, the fact table, triggers, views, stored procedures, a setup-all script, and usage examples. The core objects:

CREATE TABLE SalesDim (
    DimSurrogateKey SERIAL PRIMARY KEY,
    SalesReferenceId INT,
    DimTypeId SMALLINT REFERENCES DimTypeLookup(DimTypeId),
    DimValue VARCHAR(255),
    EffectiveDate TIMESTAMP,
    ExpiryDate TIMESTAMP DEFAULT '9999-12-31 23:59:59',
    IsCurrent BOOLEAN DEFAULT TRUE,
    VersionHash BYTEA,
    CreatedDate TIMESTAMP
);

SalesFact mirrors it with MeasureTypeId, FactValue DECIMAL(18,4), and ValidFrom/ValidTo/IsCurrent for its own SCD2 window. A third small table, CurrentSalesVersion, caches a SHA-256 hash of each sale's current dimension state — the fingerprint that decides whether facts need recalculating. The far-future sentinel '9999-12-31 23:59:59' marks “current” rows; pgcrypto's digest() provides the hashing.

Automation is two triggers. TR_Sales_To_SalesDim fires on every insert into Sales and explodes the sale into seven dimension rows. TR_SalesDim_SCD2 fires when a dimension value is updated: it expires the old row, inserts the new one, recomputes the sale's hash, and — if the fingerprint changed — expires the old facts and calls CreateSalesFacts to write fresh ones.

The everyday experience is pleasantly boring:

INSERT INTO Sales (SaleType, ProductCount, ProductPrice, Discount, SaleDate, ProductId, CustomerId)
VALUES ('Online', 1, 1200.00, 100.00, '2023-05-04', 101, 201);

...and the warehouse populates itself. Then, the payoff query — the state of sale 4 as of a specific morning:

SELECT * FROM GetSalesStateAtTime(4, '2023-05-04 10:00:00'::TIMESTAMP);

That function filters dimension rows where EffectiveDate <= AsOfDate AND ExpiryDate > AsOfDate and fact rows by their validity window, pivots, and returns the sale exactly as it stood. No snapshot tables, no audit log reconstruction.

The honest ledger

V1 also carries the scars of being a first version, and I would rather show them than airbrush them:

• Eight measures are seeded; four are computed. MeasureTypeLookup defines TotalSale, NetRevenue, DiscountAmount and ProfitMargin — and also SaleTypeTotal, SaleTypeAverage, OverallTotal, OverallAverage. The CurrentSalesState view hardcodes NULL for the last four. They are a promissory note that V2 pays off.
• Facts are only created on the update path. The insert trigger writes dimensions but never calls CreateSalesFacts, so a freshly inserted sale shows NULL measures until something changes. The full autopsy is in part 3.
• Two hashes coexist. The insert trigger computes a quick hash from ProductId and CustomerId; the real CalculateVersionHash aggregates every current dimension. Part 4 untangles them.

None of these sank the design — they are exactly the kind of thing a versioned schema is for. V2 fixed some, V3 fixed more, and the fix history is legible in a way that a long-lived “v1 forever” schema never is.

Why bother, when columnar stores exist

Fair question. If you need petabyte scans, use a columnar engine. KC Star solves a different problem: an operational analytics store where the shape of the business changes faster than you can schedule migrations, where you must answer “what did this record look like in March, and why did the number change?” — and where the whole thing has to run on the PostgreSQL you already operate. Row-based dimensions make change cheap; SCD2-everywhere makes history cheap; hashing makes change detection cheap. That combination is the thesis this series tests.

Where the series goes

1. The star schema I turned on its side — this post.
2. Dimensions as rows, measures as rows — the lookup tables, the two tall tables, and the pivot-on-read views.
3. Two triggers run the whole warehouse — the insert and SCD2 triggers, their exact flow, and the gaps I shipped.
4. SCD2 with a SHA-256 fingerprint — version hashing, change detection, and time travel.
5. The same star in T-SQL — porting V1 to SQL Server, and what the port taught me about both engines.

After that, the V2 series picks up the four NULL measures and computes them — which is where the real trouble starts.