50 articles filed under this topic.
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.
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.
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.
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.
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 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.
The per-row triggers that ran KC Star for six versions stop scaling at high volume. V7 turns them off and moves fact generation into an explicit batch pipeline. Kicking off the ultra-scale series - batch processing, feature flags, automated monitoring.
Every version through V7 rebuilt all the measures when a dimension changed. KC Star V8 recalculates only the ones that actually depend on what moved - dependency analysis, selective recalculation, and claimed 60 to 80 percent less work. Kicking off the intelligent recalculation series.
After nine versions of accumulating ambition, KC Star V9 Simplified does the rarest thing - it takes features away. No permission groups, no group cache, just direct permission lists. The README recommends starting here. Kicking off the series on the version I would actually deploy.
KC Star V9 fuses V8's intelligent recalculation with V6's permission-aware calculations - and folds the employee and their permissions into the very hash that decides what to recompute. Kicking off the series where two engines meet in one version, and complexity meets its reckoning.
A pile of subsystems isn't an operable system until you can run it. KC Star's docs distil into a runbook - daily health checks, weekly index hygiene, monthly reindex and security review, PostgreSQL tuning, backups, and one consistency check that catches a whole class of corruption.
From an inverted foundation to the version I'd deploy, KC Star evolved through ten explicit releases in the open. The retrospective on all of it - what versioning a schema publicly taught me about drift, variants, bugs as motivations, and simplicity as the real destination.
Refactoring KC Star V6's monolith into V7's subfolders broke the schema - so badly it took two passes of fix scripts to repair. Missing columns, duplicate triggers, a trigger literally named _Corrected, and a live test INSERT embedded in a migration. What the scars teach.
KC Star's wishlist folder holds four future engines - a multi-engine measures platform, multi-tenancy, predictive monitoring, and automated maintenance - all marked research phase, one with 500 lines of not-ready reference SQL. An honest look at the roadmap, and which one I'd build first.
KC Star V8's row-level trigger is genuinely selective. Its batch path, despite the name, loops every calculation-affecting dimension and recomputes most measures anyway. An honest audit of where the selectivity holds, where it doesn't, and a measure that zeroes itself out.
KC Star V7 ships two overlapping ways to partition and age out cold data - a static backup-partitioning subsystem and a dynamic one - both monthly range partitions on CreatedDate, both gated on one flag. The honest read on why a feature ended up implemented twice.
Two fix scripts in KC Star repair a V6 database where dimensions were hand-seeded but the fact-generation half of the pipeline never ran. A war story about half-run pipelines, the DO-block backfills that rescue them, and what a MANUAL_SETUP batch id confesses.
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'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.
V9 cached yes/no permission answers and inherited a revocation-staleness hazard. V9 Simplified caches finished calculation results instead - keyed on the employee's permission list, so a permission change changes the key and the stale entry is simply never hit. A better choice about what to cache.
When KC Star V9 denies a measure, it returns NULL, not zero - and the difference matters enormously once those values flow into aggregates. Here is the semantics of denial, the PermissionContext that records it, and why a redacted zero would quietly corrupt every total downstream.
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.
Switching KC Star from always-rebuild to selective recalculation means dropping V7's triggers and installing V8's without losing data. Unlike V7's chaotic refactor, this migration ships a real verification function - and one landmine where dimension ids mean different things.
KC Star V7 scores its own health from queue depth, processing time and success rate, then maps that score to a batch cadence - from 30 seconds when healthy down to emergency mode when critical - and writes the decision back into its own settings. Clever, and honestly limited.
The KC Star V6 changelog documents an entire RBAC system - tables, functions, triggers, password columns - that appears nowhere in the actual SQL. Here is how I found the drift, why it happened, and why I treat the folder code as ground truth and the changelog as fiction.
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.
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.
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.
KC Star V9 Simplified puts the permission check inside the measure calculation itself - a HasPermission call in the CASE that computes each aggregate. Read-time filtering, column masking, and an access level the view derives on the fly. The whole model, inlined.
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.
KC Star V9 checks permissions on every calculation, and checking is expensive. So it caches the yes/no answers in a PermissionCache table with a one-hour TTL and an upsert on every miss. Here is how it works, the claimed 95 percent hit rate, and the real cost of caching authorization.
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 most satisfying demonstration of KC Star V8 is an UPDATE that does no work. Change PaymentMethod, a dimension nothing depends on, and the selective trigger recalculates zero measures. Here is the trigger that pulls it off - and a tautological bug sitting right beside it.
KC Star V7 ships a six-table feature-flag system so you can turn capabilities on and retune settings without a deploy. Here is the normalized flag model, the twenty-one seeded flags, and the honest gap between what the changelog documents and what the code ships.
KC Star V6's GetFilteredAnalyticalData returns different rows to different callers - each row self-selects by the caller's clearance. Two personas, one query, two answers. Here is how permission-filtered aggregates work, and where the filtering really lives.
