What makes a dimension conformed across two star schemas in a warehouse?

## The problem conformed dimensions solve A dimensional warehouse is never one schema; it is many. Each business process - order taking, shipping, returns, inventory counting, marketing spend - gets its own fact table, and each fact table gets its own surrounding star of dimensions. Left to themselves those stars drift apart. The sales team's product dimension has a column `category` holding `Footwear`; the returns team's has `prod_group` holding `Shoes`; the marketing mart identifies a product by a SKU string while sales identifies it by an internal warehouse key. Every star answers its own questions perfectly, and no question that spans two of them can be answered at all. The conformed dimension is the agreement that prevents this drift, and it is the single mechanism that turns a collection of independently built marts into one warehouse. ## The definition A dimension is **conformed** across two or more fact tables when, for the attributes they share: - the **keys identify the same entities** - warehouse key 4471 is the same product row wherever it turns up, and the same durable business key underlies it; - the **attribute names are the same** - both sides call it `category`, not `category` on one side and `prod_group` on the other; - the **attribute values are drawn from the same domain and carry the same meaning** - `Footwear` means the same set of products in both stars, produced by the same classification rule. Notice what is *not* in the definition. Nothing requires a single physical table, nothing requires the same number of columns everywhere, and nothing is enforced by the database. Conformance is a statement about meaning that a load process is responsible for making true. ## The three legal physical shapes **One shared table.** Both fact tables reference the same `dim_product`. This is the simplest and most common form inside a single warehouse, and it is conformed by construction. **Replicated copies.** One team owns and builds the dimension; copies are published to other marts, other schemas, sometimes other platforms. This is still conformance, provided one process is the source and the copies are not edited downstream. The moment a downstream team adds a local column or relabels a value, conformance is gone. **A rolled-up subset.** A dimension can conform to a richer one by being a strict subset of its attributes and a rollup of its rows - for example a month-grain calendar that conforms to a day-grain date dimension. The subset must be exact: same attribute names, same values, no locally invented labels. ## What conformance buys you The direct payoff is the ability to combine measures that live in different fact tables. Because the sales star and the returns star both carry `dim_product.category` with the same values, you can aggregate sales by category, aggregate returns by category, and align the two summaries on the label. That alignment is the whole point: the shared dimension supplies a common vocabulary that separate fact tables can both speak. The indirect payoff is organisational. A conformed dimension has one owner, one load job and one definition. When the business reclassifies a product line, the change happens once and every report that groups by category moves together. In an unconformed estate the same reclassification is a coordinated change across every mart, and it never lands cleanly. ## What breaks without it The failure is quiet, which is what makes it dangerous. Suppose the sales mart classifies returns-eligible items under `Footwear` and the returns mart under `Shoes`. A report that puts the two side by side does not error; it shows a `Footwear` row with sales and zero returns, and a `Shoes` row with returns and no sales. Someone reads the numbers, concludes footwear has no quality problem, and acts on it. Unconformed dimensions do not produce error messages - they produce confident wrong answers. ## A concrete check ```sql -- Values present in one star's product dimension but not the other: SELECT category FROM sales_mart.dim_product EXCEPT SELECT category FROM returns_mart.dim_product; ``` If that returns rows, the two dimensions are not conformed, whatever the column names say. Running the same check on keys - do the same business keys map to the same warehouse keys - is the second half of the test. ## Common misunderstandings Conformance is not the same as denormalisation, and it is not a query-engine feature. It does not require every mart to hold every attribute; it requires that the attributes they *do* share agree. And it is not achieved by naming two tables the same thing: two `dim_customer` tables loaded from two different source systems with two different definitions of a customer are the classic example of a dimension that looks conformed on a diagram and is not conformed in the data.