Conformed Dimensions Across Domains

Why this matters

As a BI Analyst, you often compare metrics across teams and systems: marketing vs. sales, finance vs. logistics, web analytics vs. support. Conformed dimensions make those comparisons trustworthy by using the same business definitions and keys across domains.

• Report tasks: Compare revenue by the same Product across Sales and Returns.
• Ad hoc analysis: Join Support tickets to Customer lifetime value without custom mapping each time.
• Data quality: Detect domain drift early when shared attributes change meaning.

Concept explained simply

A conformed dimension is a shared lookup table that multiple fact tables use consistently. It has the same business meaning, attribute names, data types, and values across domains so your joins and filters behave the same everywhere.

Mental model

Think of a conformed dimension as a passport. Every fact event stamps the same passport (same surrogate key and standardized attributes). Because the passport is identical everywhere, you can travel freely across domains with reliable joins.

Key criteria for a conformed dimension

• Same business definition: "Customer" means the same person/entity across Sales, Support, and Marketing.
• Compatible grain: Each row represents the same level (e.g., Customer at person level, not mixed with account level).
• Consistent attributes: Names, data types, and semantics align (e.g., Country uses ISO codes everywhere).
• Consistent SCD policy: Same slowly changing dimension strategy (Type 1 vs. Type 2) across domains.
• Shared surrogate keys or reliable crosswalk: Facts can resolve to the same dimension row.

Worked examples

Design patterns and anti-patterns

Step-by-step: How to create a conformed dimension

1. Inventory domains: List facts that need shared filtering (Sales, Returns, Support, Marketing).
2. Set the grain: Choose the atomic level (e.g., Customer person-level).
3. Harmonize attributes: Define one name, data type, and business meaning per attribute. Build a mapping/crosswalk for local codes.
4. Choose SCD policy: Type 1 vs. Type 2. Document it and get buy-in from all domains.
5. Assign surrogate keys: Generate a single surrogate key space for the dimension, used by all facts or by a stable crosswalk.
6. ETL alignment: Standardize, deduplicate, and late-arriving handling so all domains resolve to the same sk.
7. Validate conformance: Compare row counts, null rates, code coverage, and sample joins across domains.
8. Govern changes: Version attribute definitions and communicate impacts before deployment.

-- Same surrogate key across two facts
select count(*) as breaks
from sales_fact s
join support_fact t on s.customer_sk = t.customer_sk
where s.customer_sk is null or t.customer_sk is null;

-- Attribute consistency check
select d1.country_code, d2.country_code, count(*)
from customer_dim d1
join customer_dim d2 on d1.customer_sk = d2.customer_sk
where d1.country_code <> d2.country_code;

Exercises

These mirror the exercises below. Do them here, then compare with the solution toggles.

Exercise 1 — Conform Geography across domains

You have three systems:

• Marketing uses country names ("United States").
• Sales uses 2-letter ISO ("US").
• Tax uses 3-letter ISO ("USA").

Design a conformed Geography dimension and crosswalk plan.

• Deliverables: target attributes, SCD policy, and mapping rules.

Exercise 2 — What to conform: User vs. Account

Product Analytics tracks events by user_id; Billing tracks invoices by account_id. One account can have many users. Decide what should be the conformed dimension and how facts join to it. Handle many-to-many carefully.

• Deliverables: dimension design, bridge or helper tables, and SCD policy.

• Checklist before you compare with solutions:
  • Did you define a single grain for the conformed dimension?
  • Is SCD policy the same across domains?
  • Did you plan a crosswalk for legacy codes?
  • Can both facts resolve to the same surrogate key?

Common mistakes and how to self-check

• Mixing grains: User vs. Account rows in the same dimension.
  Self-check: Verify one unique business key per row; no duplicates at the chosen grain.
• Hidden semantic drift: Same attribute name, different meaning.
  Self-check: Maintain a data dictionary; compare definitions line by line across domains.
• Different SCD policies: One Type 1, another Type 2.
  Self-check: Confirm SCD type in ETL and documentation; unit test for history rows.
• No crosswalk: Local codes remain unharmonized.
  Self-check: Ensure every source code maps to a global code; track unmapped exceptions.

Practical projects

• Build a conformed Customer dimension used by two sample fact tables (Sales and Tickets). Include a small Type 2 change (segment changes) and validate joins.
• Create a master Date dimension with fiscal and ISO calendars. Implement role-playing in a BI tool by aliasing columns.
• Design a Product conformance crosswalk from two catalogs with different category trees, then produce a report: sales vs. returns by brand.

Who this is for

• BI Analysts and Analytics Engineers aligning metrics across departments.
• Data Modelers establishing a consistent semantic layer.

Prerequisites

• Basic star schema concepts (facts and dimensions).
• Understanding of SCD Types 1 and 2.
• Comfort with SQL joins.

Learning path

• Before: Star schemas, SCD basics, Date dimension.
• Now: Conformed Dimensions Across Domains.
• Next: Role-playing and shrunken dimensions in practice; data governance for shared semantics.

Mini challenge

Your marketing team adds a new "Region" definition. Propose a change management plan that keeps the Region attribute conformed without breaking existing reports. Include how you will version the attribute and communicate the rollout.

Quick Test

The quick test is available to everyone. If you are logged in, your progress will be saved automatically.