Advanced Fact Patterns
Coverage, lifecycles and connected stars
Beyond the simple star live factless facts (coverage), accumulating facts (lifecycles), and fact constellations (multiple linked stars). Knowing these patterns lets you answer process and coverage questions that basic stars can't.
What you will learn
- Use factless facts for events with no numeric measure
- Model processes with accumulating facts and lifecycle dates
- Design fact constellations and their key-sharing rules
What is this?
Three advanced patterns extend the simple star: factless facts count events like attendance or eligibility (no measures, just relationships); accumulating facts track a process's lifecycle dates (order → pay → ship → deliver) with one row per process; fact constellations link multiple fact tables that share conformed dims, enabling cross-process analysis like campaign-to-sale.
Why do you need it?
- Coverage questions ('what % of days was coverage staffed?') need factless facts, not invented measures.
- Lifecycle and aging questions are impossible with only transaction facts.
- Constellations turn separate stars into one analyzable business story.
- These patterns are the difference between 'enough for a star demo' and productionpower.
Mental model
An attendance register and a project timeline
Factless + accumulating facts
The register is a factless fact: daily who-showed-up, summed as counts of presence. The timeline is an accumulating fact: one row per project that records kickoff, review and launch dates — always one row, always current state.
How it works
- Factless facts: rows of keys with no measure; COUNT(DISTINCT) answers 'were they present?'.
- Accumulating facts: one row per process/workflow id with multiple date FKs; lifecycles and aging by date deltas.
- Constellations: two stars sharing conformed dims; joins flow through the shared dims, not fact-to-fact.
- Coverage drill: join the factless attendance fact to the calendar to compute coverage % per period.
Three advanced patterns in one warehouse.
In production
- A staffed-coverage factless fact (store, day, shift, presence=1) computes 'we met 96% of shifts last quarter'.
- An order-to-cash accumulating fact ages outstanding orders by days between order and ship.
- A campaign/sales constellation attributes revenue to campaigns through shared dim_campaign and dim_date.
Best practices
- Prefer a factless fact to padding measures with fake 1s — reading counts is honest.
- Accumulating facts get updated by the latest lifecycle event (upsert), not appended.
- Constellations join via conformed dims; direct fact-to-fact joins are the fan-trap trap.
- Assign an owner per constellation to resolve definition disputes early.
Common mistakes
- Putting SUM-able fake measures('1') in factless facts and losing the point.
- Appending lifecycle rows instead of updating one row per process, corrupting each pipeline's timing.
- Joining two facts directly within a constellation and multiplying grain.
- No measure, so the report SUMs a key column by accident.
Common issues
Coverage % > 100%Factless fact joined a many-sided dimension that multiplied rows
Join the factless fact to one-many dims only; count distinct at the right grain.
Pipeline aging no longer advancesAppend instead of upsert left old lifecycle rows
Upsert the accumulating row with latest dates; one row per process.
Debugging tips
- Coverage looks wrong? Verify the calendar join includes all days, not just attended ones.
- Aging wrong? Check which date FK defines 'as of' and that it's from the right role.
- Constellation totals balloon? Re-check conformed-key joins and grain.
Interview checkpoint
Quick quiz
Which pattern answers 'what % of days was this region fully staffed?'
Accumulating facts should insert a new row for every lifecycle event.