Three people walk into the same board meeting with three different numbers for the same deal, and all three are right.
Sales says $648,000 — the full three-year contract Tabby just signed with PetCo Org, a vet chain rolling out cat trackers across 12 locations. Finance says $18,000 — the revenue actually recognized so far, because most of those locations haven't gone live yet and you can't book revenue for service you haven't delivered. Customer Success says 8 of 12 — not a dollar figure at all, but the fraction of locations actually onboarded and using the product, which is what predicts whether this account renews. Nobody in that room is lying, confused, or bad at their job. They're answering three different questions that happen to sound like the same question.
Every article in this series so far has had a textbook answer once you found the right technique — grain, SCD2, accumulating snapshots, bridge tables. This one doesn't. It's the case the first four were building toward: what do you actually do when the "correct" grain depends entirely on who's asking, and picking one is itself a decision you have to be able to defend.
The 60-second recap
- Grain = the exact definition of one fact row. Different real questions at different grains get different fact tables — a rule this series has invoked at every stop: the coffee shop's order lines vs. daily snapshots, Tabby's lifecycle vs. monthly MRR, fulfillment's orders vs. shipments, claims vs. diagnosis bridges.
- SCD Type 2 preserves history so a fact always joins to the dimension row that was true when the event happened.
- Accumulating snapshots update one row per entity across milestones; periodic snapshots take regular photos; semi-additive measures can be summed across some dimensions but not across time.
- Bridge tables resolve genuine many-to-many relationships without inventing numbers through a naive join.
What none of the last four articles said out loud: every fact table in this series has shared the same dim_date, and most have shared the same dim_account. That's not incidental — it's the thing that makes what happens in this article possible at all, and it finally gets a name below.
Meet the conflict
Tabby (Part 2's cat-collar SaaS company) just closed PetCo Org — a 12-location vet chain, three-year contract, $648,000 total contract value, $18,000/month once every location is fully live. The contract was signed two months ago. Locations are onboarding in waves: 4 went live in month one, 4 more in month two, the remaining 4 are scheduled for month three.
Three teams need a number for this deal, and none of them are asking for the same thing:
| Stakeholder | Question they're actually asking | Their answer |
|---|---|---|
| Sales | "How much did we sell, for commission and pipeline purposes?" | $648,000 — full contract value, credited at signature |
| Finance | "How much revenue have we actually earned so far?" | $18,000 — ratable recognition, only for location-months of service actually delivered |
| Customer Success | "Is this account actually succeeding, right now?" | 8 of 12 locations active — not a dollar figure at all |
Look at the units before anything else: two dollar figures thirty-six times apart, and a fraction that isn't a dollar figure. Forcing these into one number isn't hard because the math is hard — it's a category error, the same way "what's the average of a distance and a color" is a category error. There is no arithmetic that turns $648,000, $18,000, and 67% into one honest figure, because they were never measuring the same thing.
Why each one is correct, on its own terms
Sales is correct because commission and pipeline reporting exist to measure and reward the act of closing the deal — the moment the contract was signed is the event, and its full value is the relevant fact, regardless of how long delivery takes. Waiting three years to credit a three-year deal would break sales compensation entirely.
Finance is correct because revenue recognition rules exist specifically to prevent booking revenue for service not yet delivered — recognizing $648,000 today would overstate the company's earnings by the entire undelivered two years and ten months of the contract, which is not a rounding error, it's the difference between real and fictional financial statements.
Customer Success is correct because renewal risk tracks with product usage and onboarding health, not with contract value or accounting timing — a fully-recognized, fully-paid contract where nobody's using the product is a churn risk regardless of what Finance's ledger says.
None of these is a rougher approximation of one true number. They're three different, equally precise answers to three different questions.
What breaks if you force one grain to answer all three
It's worth actually trying each option, because the failure modes are the argument, not just an assertion:
Force everything to contract grain (Sales' shape). One row per contract, $648,000. Finance now has no way to recognize revenue ratably — the number either overstates earned revenue on day one or requires bolting a second, contradictory recognition schedule onto a table whose whole point was "one clean number per deal." Customer Success has nothing at all — a contract-grain table has no concept of "location," so activation tracking doesn't exist in this model.
Force everything to subscription-month grain (Finance's shape, and it already exists — fact_subscription_month from Part 2). Sales' $648,000 evaporates — a monthly snapshot only ever shows revenue for months that have already been recognized, so on day one of a three-year deal, this view of the world shows nothing, which is exactly backwards from what commission tracking needs. Customer Success again has no location-level detail, because the grain is subscription-month, not subscription-location-month.
Force everything to location-activation grain (Customer Success's shape). Now there's no way to attach a dollar figure at all without allocating the $648,000 across 12 locations and however many months — which reintroduces the exact allocation problem Part 4 spent an entire article on, for a question (activation health) that never needed a dollar figure to begin with.
Every single-grain option doesn't just make one team's job harder — it makes their question structurally unanswerable inside that model, not just inconvenient.
The resolution: three fact tables, one shared dimension
The fix isn't a fourth, cleverer grain. It's building all three, and linking them through a dimension every one of them can reference — without any of them referencing each other.
CREATE TABLE dim_contract (
contract_sk BIGSERIAL PRIMARY KEY,
contract_id TEXT NOT NULL,
account_sk BIGINT NOT NULL REFERENCES dim_account(account_sk),
signed_date DATE NOT NULL,
term_months INT NOT NULL,
total_contract_value NUMERIC(12,2) NOT NULL,
location_count INT NOT NULL,
sales_rep_sk INT REFERENCES dim_employee(employee_sk)
);
Sales gets fact_booking — one row per signing event, referencing dim_contract:
CREATE TABLE fact_booking (
booking_sk BIGSERIAL PRIMARY KEY,
contract_sk BIGINT NOT NULL REFERENCES dim_contract(contract_sk),
account_sk BIGINT NOT NULL REFERENCES dim_account(account_sk),
booked_date_sk INT NOT NULL REFERENCES dim_date(date_sk),
booking_type TEXT NOT NULL, -- NEW / RENEWAL / AMENDMENT / UPSELL
booked_value NUMERIC(12,2) NOT NULL
);
Finance keeps using fact_subscription_month from Part 2 — no new table needed, just a new foreign key added so it can be tied back to the deal that produced it:
ALTER TABLE fact_subscription_month
ADD COLUMN contract_sk BIGINT REFERENCES dim_contract(contract_sk);
Customer Success gets fact_location_activation — an accumulating snapshot in the same style as Part 3's fulfillment milestones, one row per location within the contract:
CREATE TABLE fact_location_activation (
activation_sk BIGSERIAL PRIMARY KEY,
contract_sk BIGINT NOT NULL REFERENCES dim_contract(contract_sk),
location_sk BIGINT NOT NULL REFERENCES dim_location(location_sk),
scheduled_activation_date_sk INT REFERENCES dim_date(date_sk),
actual_activation_date_sk INT REFERENCES dim_date(date_sk),
current_status TEXT NOT NULL -- SCHEDULED / ACTIVE / DELAYED
);
Notice what's not here: no fact table has a foreign key into another fact table. fact_location_activation doesn't reference fact_booking; fact_subscription_month doesn't reference fact_location_activation. They're linked only through dim_contract — a conformed dimension, the formal name for exactly this pattern. It's the same idea dim_date has been quietly doing since the first article's role-playing order-date/pickup-date trick: the same dimension, referenced by multiple fact tables at different grains, is what lets you query across them without ever needing them to share a grain, or even be joinable to each other directly.
The three "what's the number" queries
-- Sales: bookings
SELECT SUM(f.booked_value) AS bookings
FROM fact_booking f
JOIN dim_contract c ON c.contract_sk = f.contract_sk
WHERE c.contract_id = 'PETCO_2026_01';
-- $648,000
-- Finance: recognized revenue to date
SELECT SUM(f.mrr) AS recognized_revenue_to_date
FROM fact_subscription_month f
JOIN dim_contract c ON c.contract_sk = f.contract_sk
WHERE c.contract_id = 'PETCO_2026_01'
AND f.month_sk BETWEEN 20260101 AND 20260228;
-- $6,000 (month 1, 4 locations) + $12,000 (month 2, 8 locations) = $18,000
Worth a pause on that second query: summing mrr across two consecutive months for the same subscription is only meaningful because it's being read as "revenue recognized," not as "MRR." The exact same column, summed the exact same way, would be nonsense if the question were "what's the account's MRR across January and February" — you wouldn't add two run-rates together and call it a run-rate. Part 3 named this problem for a count that couldn't be summed across time at all; here it's sharper still — the same number is fully additive under one interpretation and meaningless under another, depending on which question you're actually asking of it. That's worth remembering any time a measure gets summed across a date range: check what you're claiming the sum means, not just whether the arithmetic runs.
-- Customer Success: activation
SELECT
COUNT(*) FILTER (WHERE current_status = 'ACTIVE') AS locations_active,
COUNT(*) AS locations_total
FROM fact_location_activation f
JOIN dim_contract c ON c.contract_sk = f.contract_sk
WHERE c.contract_id = 'PETCO_2026_01';
-- 8 of 12
Three queries, same dim_contract, three genuinely different answers — none of them wrong, none of them reconcilable into each other by any transformation, because they were never the same measurement.
The governance move: name the metrics, don't blend them
The technical fix is three fact tables. The organizational fix — the part that actually prevents this from becoming a recurring argument — is refusing to let anyone build a dashboard that presents these as one number. The way to do that concretely:
-- An executive summary view: three named, defined metrics, never blended
SELECT 'Bookings (Sales)' AS metric,
'$648,000' AS value,
'Full 3-year contract value, credited at signature' AS definition
UNION ALL
SELECT 'Recognized Revenue to Date (Finance)',
'$18,000',
'Ratable revenue for location-months of service actually delivered'
UNION ALL
SELECT 'Location Activation (Customer Success)',
'8 of 12 (67%)',
'Locations live and actively using the product as of today';
This isn't a workaround for not having a single number — it is the answer. "Bookings," "Recognized Revenue," and "Activation Rate" become three named, documented metrics in whatever metrics glossary or semantic layer the company uses, each with an owner and a definition, structurally incapable of being confused for one another because they're never presented as the same field. The failure mode this prevents isn't a technical one — it's someone building a dashboard that labels a column just "Revenue" and quietly picks whichever of the three numbers happens to be sitting in whatever table they joined to first.
A note on the whole series: conformed dimensions were always the point
Every fact table across all five articles in this series — fact_order_line, fact_subscription_month, fact_order_lifecycle, fact_claim_line, and the three built above — has shared the same dim_date. Most have shared dim_account or its equivalent. That repetition wasn't incidental; it's the single idea that makes a dimensional model more than a collection of unrelated tables.
A conformed dimension is a dimension built once, with one consistent set of keys and attributes, and reused across every fact table that needs it. It's what lets a coffee shop's daily sales and a SaaS company's MRR waterfall both be sliced by "quarter" using the exact same dim_date, and it's what let three fact tables at three irreconcilable grains sit next to each other in this article without contradiction. The grain of a fact table answers "what is one row." The conformed dimensions answer the question this whole series has actually been building toward: how do a dozen fact tables, at a dozen different grains, built at different times by different teams, still add up to one coherent model instead of a pile of disconnected spreadsheets with SQL in front of them.
Common mistakes
Treating "which number is right" as a technical question. It's a scope question — right for what audience, right for what decision. The fix is naming the metric precisely enough that "right" stops being ambiguous, not searching harder for a formula that reconciles $648,000 and $18,000.
Building the dashboard leadership asked for instead of the one that's honest. "Just give me one number for the deal" is a request that will get answered whether or not you push back — better to hand over three clearly labeled numbers than one blended, quietly wrong one that leadership will eventually catch and stop trusting.
Letting a fact table reference another fact table instead of a shared dimension. It's tempting to just point
fact_location_activation.booking_skatfact_bookingdirectly — it even works, mechanically. It also means the two tables' futures are now coupled for no reason: if bookings ever need a second grain (amendments as their own rows, say), every downstream reference into the old grain has to be found and fixed. Route through the dimension.Summing a measure across time without checking what the sum means. The
mrr-as-revenue example above: right when read as recognized revenue, meaningless when read as a run-rate. The column doesn't tell you which one you're doing — you have to know the question.Resolving the conflict once, informally, in a meeting, and not writing it down. Whatever gets agreed about which team owns which metric needs to live in a metrics glossary, not in the memory of whoever was in that board meeting — the alternative is having the same argument again in two quarters with different people in the room.
Exercises
This is the last set in the series, and there's no answer key for the first one — that's the point.
1. A professional services company bills clients by the hour but pays consultants a fixed salary. The PM wants project profitability tracked by project phase (discovery, build, delivery). Finance wants it by invoice, since that's what's actually billed and collectible. Resourcing wants it by consultant-week, since that's what determines who's overbooked next month. Design the fact tables. Which dimension conforms all three?
Hint — not a full solution, on purpose
Start by writing each stakeholder's actual question in one sentence, the way this article did for Sales/Finance/CS, before touching a schema. If you can't state the question precisely, you can't design the grain for it. A conformed dim_project or dim_engagement is a likely candidate — but defend it against at least one alternative before you commit, the way this article walked through what breaks under each single-grain option.
2. Revisit Part 4's healthcare claims model. Sales credits an insurance broker for signing a new employer group; Finance recognizes premium revenue ratably over the policy period; a Care Management team tracks which specific members within the group have actually completed onboarding health screenings. Sketch the three fact tables and the dimension that conforms them, following the pattern in this article.
Hint
This is structurally the same shape as the PetCo Org case — a group-level commitment (the employer contract), a ratable financial recognition, and a member-level activity tracker — with dim_employer_group or an equivalent doing the same job dim_contract did here.
Closing: the whole series in one paragraph
Five articles, one underlying argument: a dimensional model isn't a diagram of your data, it's a set of decisions about what one row means, made explicitly enough that two people looking at the same fact table agree on what it's telling them. Grain is the first and most important of those decisions. SCD2 handles the decision changing over time. Accumulating snapshots handle a decision that takes multiple steps to resolve. Bridge tables handle a decision that legitimately has more than one right answer within a single fact row. And this article handles the case where the decision doesn't have one right answer at all — where the correct move isn't picking, it's building enough separately-grained, honestly-labeled fact tables, conformed through shared dimensions, that nobody has to lie to get an answer. That's the actual skill underneath all the SQL: not knowing the four fact table types, but knowing which one — or which three — a real, messy, human question actually needs.
Resources
-
Ralph Kimball & Margy Ross, *The Data Warehouse Toolkit* — chapter 4 covers conformed dimensions and the bus matrix directly; it's the concept this entire series has been resting on since Part 1's
dim_date. - Kimball Group — Conformed Dimensions — the canonical short reference.
That's the series. If you read all five, you now have the fundamentals, the SaaS extensions, late-arriving facts, many-to-many relationships, and the judgment call none of the others could hand you a formula for. The companion repo has the full schema and exercises for all five parts, start to finish. Go build something someone else's team can actually trust.
Top comments (1)
Some comments may only be visible to logged-in visitors. Sign in to view all comments.