In reading order.
What if dimensions and measures were rows instead of columns? Introducing KC Star, a PostgreSQL data warehouse I evolved through nine versions, starting with the inverted foundation - SCD2 history, SHA-256 fingerprints and time travel included.
The heart of KC Star is two tall, narrow tables and two lookup tables. Here is how a sale becomes seven dimension rows and a handful of measure rows, and how a view pivots them back into something you can read.
KC Star V1 has no ETL job and no application code. Two PostgreSQL triggers explode sales into dimension rows and version them on change - and the gaps between what they do and what I claimed they do are the most instructive part of V1.
KC Star decides whether to rebuild a sale's measures by hashing its dimension state and comparing. Here is how the version hash works, why two of them coexist in V1, and how the same timestamps that version history also power time travel.
I ported KC Star V1 to SQL Server to prove the inverted star schema was an idea, not a PostgreSQL trick. HASHBYTES for digest, cursors for FOR EACH ROW, expire-then-insert instead of MERGE - the port taught me as much about both engines as the original did.
