Newest first, grouped by year. Search or filter by topic.
KC Star V6 decides access by comparing a single integer. An employee's highest LevelOrder against the level being requested - Global sees everything below it, an employee sees only themselves. Coarse, cascading, and a deliberate trade against per-object ACLs.
KC Star V6 layers role-based access control onto the org hierarchy - eight permission types, five hierarchy levels, and grants that expire. Kicking off the series where the warehouse learns that not everyone should see every number.
KC Star V5's third star is keyed by a polymorphic ReferenceId and a hierarchy-level string, fed by real-time triggers, and tracked by an update log with a PENDING to COMPLETED status - a queue that isn't actually a queue. Closing the V5 series.
KC Star V5 exists in two materially different builds under one version number - the folder tells a batch/SCD2 story, the root file tells a real-time story, and they don't contain the same objects. What I learned from letting variants drift instead of forcing them to agree.
KC Star V5 climbs the org chart in a single view - employee to office to region to organisation - and a stored procedure that materialises the rollups. Commission analysis and quote-to-delivery ratios fall out of the same hierarchy.
KC Star V5 pretends one PostgreSQL instance is two databases, joined by a single un-enforced integer. CrossDatabaseFact carries two reference keys at once, and eight windowed measures value a single sale at every altitude of the org chart.
KC Star V5 gives the sales star a sibling: a full organizational hierarchy — organisation, regions, offices, personnel — with its own dimension rows, its own fact rows, and its own SCD2. Kicking off the series on the version where one star became three.
KC Star's dev stack stands up PostgreSQL and SQL Server with the schema auto-loaded through V4, pgAdmin pre-wired to the servers, and one command to a working warehouse. Here is how the compose file, the init scripts, and the pgAdmin auto-registration fit together.
A stored number that can't explain itself is a number you can only trust. KC Star V4 promotes CalculationContext to JSONB and adds statistical columns to aggregates, so every fact carries a queryable record of the inputs and formula that produced it.
KC Star V4 tracks lineage two incompatible ways depending on which build you run - a normalized FactLineage graph in the root file, a denormalized DataLineage string in the version folder. The fork is real, and it is a clean case study in when a graph pays and when a string is enough.
KC Star V4 puts a DataQualityScore on every dimension and fact, defaulting to a perfect 1.0 and downgrading only on evidence. Here is the opt-out philosophy, the assessment procedures behind it, and the one query that finds every row the warehouse doesn't quite trust.
Through V3, KC Star assumed every number it was fed was true. V4 stops assuming - adding data quality scores, lineage, and performance monitoring so the warehouse can say how much it trusts its own data. Kicking off the data governance series.
V3 ships the largest usage-example set of the early versions - twenty queries against base facts and aggregates living in two tables. A tour of what a dual-star warehouse lets you ask, from base-vs-aggregate joins to watching a rollup evolve over time.
V3's rewritten CreateAggregatedFacts expires every aggregate and rebuilds them all from scratch on each run. It is blunt, it is correct, and the trade between rebuild-it-all simplicity and incremental cleverness is one KC Star kept relitigating all the way to V8.
An average with no denominator is a number you can't defend. KC Star V3 adds a FactCount column to every aggregate - how many facts went into this rollup - turning each stored aggregate into something you can audit rather than just trust.
KC Star V2 leaked aggregate rows because base facts and rollups shared one table. V3 gives aggregates a dedicated SalesAggregationFact table with its own SCD2 - and the fix is as much about what SalesFact loses as what the new table gains.
KC Star V2's measure formulas read like window functions - SUM(...) OVER (PARTITION BY SaleType) - but the implementation is plain GROUP BY. Here is how CreateAggregatedFacts actually builds the aggregates, and why the formula strings deliberately lie.
KC Star V2 stores base facts and aggregates in one table and tells them apart by a NULL. When it expires old aggregates, that one distinction turns out to be a distinction short - per-SaleType aggregates never retire, duplicates pile up, and V3 gets its reason to exist.
To compute per-SaleType aggregates, KC Star V2 lifted one dimension out of the row store and made it a real column. Here is the renumbering it forced, what it did to the version hash, and the honest tension of un-inverting one attribute in a schema built on inversion.
KC Star V1 declared eight measures and computed four. V2 computes all eight - base measures and aggregates together in a single SalesFact table. Kicking off the series on the version that made the warehouse analytical, and started the trouble that V3 had to fix.
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.
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.
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.
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.
