What are conformed facts, and what breaks when two marts define revenue differently?

## The half of conformance people forget Conformed dimensions get all the attention, and they solve half the problem: they make sure `Footwear` means the same set of products in every star, so the rows of a cross-process report line up. Conformed facts solve the other half. Once the rows line up, the question becomes whether the *numbers* in them are comparable, and that depends on whether the measures were defined the same way. A fact is **conformed** across fact tables when the measure means exactly the same thing everywhere it appears: - **the same calculation** - is revenue gross of discounts or net of them; - **the same inclusion rules** - does it include shipping, tax, cancelled orders, internal transfers, test accounts; - **the same units and currency** - grams or kilograms, and if currency, converted at which rate on which date; - **the same sign convention** - are returns negative revenue or a positive amount in a separate measure; - **a compatible grain interpretation** - a measure that is meaningful per line item may not be simply summable at order level. ## The naming rule The practical discipline is a naming rule: **identical definition, identical name; different definition, different name.** This sounds trivial and is the single highest-leverage habit in the whole area, because a shared name is an implicit promise that the two numbers can be added, subtracted and compared. If the order-entry mart's `revenue` is gross of discounts and the finance mart's `revenue` is net of them, the promise is false, and the model itself is lying to every consumer. Renaming to `gross_revenue` and `net_revenue` is not a cop-out; it is the correct outcome when the business genuinely needs both. The unacceptable outcome is two identically named columns with different semantics. ## How the failure actually shows up The failure is quiet, which is what makes it costly. Consider a drill-across report combining an order-entry fact and an invoicing fact by month: ```sql -- Both columns are called revenue; only one includes shipping SELECT COALESCE(o.year_month, i.year_month) AS year_month, o.revenue AS order_revenue, i.revenue AS invoiced_revenue, o.revenue - i.revenue AS variance FROM orders_by_month o FULL OUTER JOIN invoiced_by_month i ON o.year_month = i.year_month; ``` Every join is correct, every dimension is conformed, no query errors. The `variance` column is nonetheless meaningless: it is a definitional difference, not a business signal. Someone will build a dashboard on it, someone else will open an investigation into a leakage that does not exist, and the finance team will spend a quarter reconciling two numbers that were never supposed to match. Unconformed facts do not break reports; they break trust. ## Currency and unit conformance A measure expressed in different currencies is not conformed even when the calculation is identical, and this is a frequent real-world case for multinational businesses. If one regional fact table stores local currency and another stores a converted amount, they cannot be summed. The usual resolution is to carry both - a local-currency measure and a standardised measure converted at an explicitly stated rate - with names that say which is which. The rate policy (transaction-date rate, month-end rate, budget rate) is part of the definition, so two standardised measures using different rate policies are still not conformed. ## Semi-conformance and honest partial agreement Sometimes two processes genuinely cannot produce the same measure. An order fact can count units ordered; a shipment fact counts units shipped. These are different measures and should be named differently - and that is the *correct* answer, not a failure. Conformance does not mean forcing every process to report identical measures; it means never disguising two different measures as one. ## How you make it stick Conformed facts are enforced by process rather than by the database, since no constraint can check a definition. The practical mechanisms are a written definition attached to every measure that says what is included and excluded, a single owning team per measure, a naming convention that surfaces variants explicitly, and periodic reconciliation queries that compare the same measure computed from two fact tables and alert when the difference exceeds an agreed threshold. When a definition must change, it changes for all consumers together, exactly as with a conformed dimension - a measure that means one thing in one report and another thing in the next is the failure this whole discipline exists to prevent.