Chapter 06 · Conformed Dimensions, Enterprise Bus Architecture, and Cross-Process Analytics
Enterprise Definitions, Stewardship, Master/Reference Data, and Preventing Metric Drift
Turn shared dimensions and metrics into governed enterprise definitions with named stewards, master/reference-data ownership, versioned metric contracts, and tests that expose semantic drift.
Learning outcomes
A bus matrix can say Product is shared, yet two teams can still disagree about category, customer identity, or what a metric called revenue means. AtlasMart therefore needs stewardship and versioned semantic contracts in addition to tables and keys.
Separate master/reference data responsibilities from fact ownership.
Assign named stewards for shared dimensions and metric definitions.
Define paid GMV, return amount, and post-return merchandise value without overloading one ambiguous label.
Detect same-name/different-formula metric drift before dashboards disagree.
Treat local attributes as local unless they pass the enterprise conformance contract.
Chapter 06 preserves every accepted AtlasMart control from Chapters 01–05. Paid sales remain 7 order lines, 4 paid orders, 9 sold units, 625 paid GMV, 380 cost-at-sale, and 245 gross profit (39.2% recomputed gross margin). The September 20 inventory snapshot remains 137 units. Chapter 06 adds clearly labeled synthetic fulfillment, return, and marketing process fixtures only to teach cross-process conformance; those new processes never rewrite the established sales or inventory evidence.
The mandatory lab uses Python's standard-library
sqlite3 module and synthetic local data. It
proves semantic and reconciliation properties, not
warehouse-scale performance. Record your actual Python/SQLite
versions before execution. Date roles, key mappings, metric
definitions, and conformance rules are explicit contracts; do
not infer production governance, latency, security, cost, or
SLO guarantees from this toy fixture.
1. Enterprise definition does not mean “one team owns every business meaning”
Central governance should define the connection points that require enterprise consistency, not monopolize every local attribute. Product identity, product name, and enterprise category may be shared. A Marketing propensity score can remain Marketing-owned if no other process relies on it as a conformed attribute. The contract should state which fields are enterprise row headers and which are private extensions.
| Contract object | Proposed steward | Shared responsibility | Local extension example |
|---|---|---|---|
| Product | Product Data Steward | durable product ID, name, enterprise category | campaign affinity score |
| Customer | Customer Data Steward | durable customer identity, governed segment | marketing audience label |
| Date | Data Platform Steward | calendar key/domain and timezone/calendar policy | process-specific date role name |
| Paid GMV | Sales Analytics Owner | paid-event inclusion + amount formula + currency/unit | dashboard formatting |
| Return amount | Returns Analytics Owner | accepted return-event amount semantics | return-operations reason grouping |
2. Master data, reference data, and conformed dimensions are related but not synonyms
Master data often represents durable business entities such as customers and products. Reference data supplies controlled code sets such as enterprise categories or return reasons. A conformed dimension is the analytical presentation contract that exposes compatible attributes and identities to fact tables. It may be populated from master/reference systems, but the analytical contract still needs its own history, unknown-member, freshness, and publication rules.
In AtlasMart, source_key_map proves that
operational and Marketing identifiers can converge on the same
durable customer or product. The mapping is evidence of identity
resolution; it does not by itself decide every descriptive
attribute.
3. Metric names need explicit event and grain semantics
| Metric | Definition in this lab | Atomic evidence | Owner |
|---|---|---|---|
| Paid GMV |
sum of fact_sales.extended_amount for paid
order lines
|
7 sales rows = 625 | Sales Analytics |
| Return amount | sum of accepted return-event amount | 1 return row = 25 | Returns Analytics |
| Post-return merchandise value | Paid GMV − Return amount under this chapter’s simplified fixture | 625 − 25 = 600 | Cross-process Metrics Council |
| Ending on-hand units | sum of product balances at 2026-09-20 snapshot only | 4 inventory rows = 137 | Inventory Analytics |
The post-return metric is deliberately named rather than calling both 625 and 600 “revenue.” Real organizations may have taxes, cancellations, shipping, discounts, recognition rules, currencies, and accounting periods that require a richer contract. Chapter 06 demonstrates the governance mechanism; it does not declare a universal accounting definition.
4. Deliberately wrong approach: one ambiguous metric name in every mart
Suppose Sales publishes revenue = 625 from paid
order lines while Finance publishes
revenue = 600 after the recorded return. Neither
formula is automatically wrong, but using the same unqualified
business name creates a false conformed fact. Users will
interpret the difference as a data defect instead of a
definition difference.
The repair is to give facts explicit names and contracts until
the organization agrees on a single definition:
paid_gmv, return_amount, and
post_return_merchandise_value. If a metric later
becomes enterprise-conformed, all producers must satisfy the
same inclusion, unit, time, and calculation rules.
import sqlite3conn=sqlite3.connect(":memory:")conn.executescript("PRAGMA foreign_keys = ON;\nDROP TABLE IF EXISTS fact_marketing_exposure;\nDROP TABLE IF EXISTS fact_returns;\nDROP TABLE IF EXISTS fact_fulfillment;\nDROP TABLE IF EXISTS fact_inventory_snapshot;\nDROP TABLE IF EXISTS fact_sales;\nDROP TABLE IF EXISTS source_key_map;\nDROP TABLE IF EXISTS marketing_product_local;\nDROP TABLE IF EXISTS dim_return_reason;\nDROP TABLE IF EXISTS dim_campaign;\nDROP TABLE IF EXISTS dim_store;\nDROP TABLE IF EXISTS dim_product;\nDROP TABLE IF EXISTS dim_customer;\nDROP TABLE IF EXISTS dim_geography;\nDROP TABLE IF EXISTS dim_date;\n\nCREATE TABLE dim_date (\n date_key INTEGER PRIMARY KEY,\n full_date TEXT NOT NULL UNIQUE,\n calendar_year INTEGER NOT NULL,\n calendar_month INTEGER NOT NULL,\n day_of_month INTEGER NOT NULL,\n day_name TEXT NOT NULL\n);\nCREATE TABLE dim_geography (\n geography_key INTEGER PRIMARY KEY,\n geography_id TEXT NOT NULL UNIQUE,\n country_name TEXT NOT NULL,\n region_name TEXT NOT NULL,\n city_name TEXT,\n geography_status TEXT NOT NULL\n);\nCREATE TABLE dim_customer (\n customer_key INTEGER PRIMARY KEY,\n durable_customer_id TEXT NOT NULL UNIQUE,\n source_system TEXT NOT NULL,\n source_customer_id TEXT NOT NULL,\n customer_name TEXT NOT NULL,\n segment TEXT NOT NULL,\n geography_key INTEGER NOT NULL REFERENCES dim_geography(geography_key),\n UNIQUE(source_system, source_customer_id)\n);\nCREATE TABLE dim_product (\n product_key INTEGER PRIMARY KEY,\n product_id TEXT NOT NULL UNIQUE,\n product_name TEXT NOT NULL,\n brand_name TEXT NOT NULL,\n department_name TEXT NOT NULL,\n category_name TEXT NOT NULL,\n subcategory_name TEXT\n);\nCREATE TABLE dim_store (\n store_key INTEGER PRIMARY KEY,\n store_id TEXT NOT NULL UNIQUE,\n store_name TEXT NOT NULL,\n store_type TEXT NOT NULL\n);\nCREATE TABLE dim_campaign (\n campaign_key INTEGER PRIMARY KEY,\n campaign_id TEXT NOT NULL UNIQUE,\n campaign_name TEXT NOT NULL\n);\nCREATE TABLE dim_return_reason (\n return_reason_key INTEGER PRIMARY KEY,\n return_reason_code TEXT NOT NULL UNIQUE,\n return_reason_name TEXT NOT NULL\n);\n\nINSERT INTO dim_date VALUES\n(20260918,'2026-09-18',2026,9,18,'Friday'),\n(20260919,'2026-09-19',2026,9,19,'Saturday'),\n(20260920,'2026-09-20',2026,9,20,'Sunday');\nINSERT INTO dim_geography VALUES\n(0,'__UNKNOWN__','Unknown','Unknown',NULL,'unknown'),\n(-1,'__NOT_APPLICABLE__','Not applicable','Not applicable',NULL,'not_applicable'),\n(501,'G-NORTH','Freedonia','North','Northport','known'),\n(502,'G-WEST','Freedonia','West','Westhaven','known'),\n(503,'G-EAST','Freedonia','East',NULL,'known_ragged'),\n(504,'G-SOUTH','Freedonia','South','Southbank','known');\nINSERT INTO dim_customer VALUES\n(0,'D-UNKNOWN','system','__UNKNOWN__','Unknown customer','Unknown',0),\n(-1,'D-NA','system','__NOT_APPLICABLE__','Not applicable customer','Not applicable',-1),\n(101,'D-CUST-001','erp','C001','Ada Retail','SMB',501),\n(102,'D-CUST-002','erp','C002','Ben Home','Consumer',502),\n(103,'D-CUST-003','crm','C003','Cyra Labs','Enterprise',503),\n(104,'D-CUST-004','erp','C004','Dara Studio','Consumer',504);\nINSERT INTO dim_product VALUES\n(0,'__UNKNOWN__','Unknown product','Unknown','Unknown','Unknown',NULL),\n(-1,'__NOT_APPLICABLE__','Not applicable product','Not applicable','Not applicable','Not applicable',NULL),\n(201,'P100','Keyboard','KeyWorks','Hardware','Accessories','Input Devices'),\n(202,'P200','Mouse','KeyWorks','Hardware','Accessories','Input Devices'),\n(203,'P300','Monitor','ViewCo','Hardware','Displays','Monitors'),\n(204,'P400','Dock','ConnectCo','Hardware','Accessories','Docking');\nINSERT INTO dim_store VALUES\n(0,'__UNKNOWN__','Unknown selling location','Unknown'),\n(301,'S-WEB','Web Store','Digital'),\n(302,'S-MOBILE','Mobile Store','Digital'),\n(303,'S-SALES','Assisted Sales','Assisted');\nINSERT INTO dim_campaign VALUES\n(401,'CMP-ACCESSORY','Accessory Awareness'),\n(402,'CMP-DISPLAY','Display Upgrade');\nINSERT INTO dim_return_reason VALUES\n(501,'CHANGED_MIND','Changed mind');\n\nCREATE TABLE fact_sales (\n sales_key INTEGER PRIMARY KEY,\n date_key INTEGER NOT NULL REFERENCES dim_date(date_key),\n customer_key INTEGER NOT NULL REFERENCES dim_customer(customer_key),\n product_key INTEGER NOT NULL REFERENCES dim_product(product_key),\n store_key INTEGER NOT NULL REFERENCES dim_store(store_key),\n order_id TEXT NOT NULL,\n line_no INTEGER NOT NULL,\n quantity INTEGER NOT NULL CHECK(quantity > 0),\n unit_price NUMERIC NOT NULL CHECK(unit_price >= 0),\n extended_amount NUMERIC NOT NULL CHECK(extended_amount >= 0),\n extended_cost NUMERIC NOT NULL CHECK(extended_cost >= 0),\n source_batch_id TEXT NOT NULL,\n UNIQUE(order_id,line_no)\n);\nINSERT INTO fact_sales VALUES\n(1,20260918,101,201,301,'O1001',1,2,50,100,60,'BATCH-CH03-001'),\n(2,20260918,101,202,301,'O1001',2,1,25,25,10,'BATCH-CH03-001'),\n(3,20260918,102,203,302,'O1002',1,1,200,200,140,'BATCH-CH03-001'),\n(4,20260919,101,204,301,'O1003',1,1,100,100,60,'BATCH-CH03-001'),\n(5,20260919,101,202,301,'O1003',2,2,25,50,20,'BATCH-CH03-001'),\n(6,20260920,104,201,302,'O1005',1,1,50,50,30,'BATCH-CH03-001'),\n(7,20260920,104,204,302,'O1005',2,1,100,100,60,'BATCH-CH03-001');\n\nCREATE TABLE fact_inventory_snapshot (\n snapshot_date_key INTEGER NOT NULL REFERENCES dim_date(date_key),\n product_key INTEGER NOT NULL REFERENCES dim_product(product_key),\n on_hand_units INTEGER NOT NULL CHECK(on_hand_units >= 0),\n source_batch_id TEXT NOT NULL,\n PRIMARY KEY(snapshot_date_key,product_key)\n);\nINSERT INTO fact_inventory_snapshot VALUES\n(20260919,201,42,'INV-20260919'),\n(20260919,202,63,'INV-20260919'),\n(20260919,203,13,'INV-20260919'),\n(20260919,204,28,'INV-20260919'),\n(20260920,201,40,'INV-20260920'),\n(20260920,202,60,'INV-20260920'),\n(20260920,203,12,'INV-20260920'),\n(20260920,204,25,'INV-20260920');\n\nCREATE TABLE fact_fulfillment (\n fulfillment_key INTEGER PRIMARY KEY,\n ship_date_key INTEGER NOT NULL REFERENCES dim_date(date_key),\n customer_key INTEGER NOT NULL REFERENCES dim_customer(customer_key),\n product_key INTEGER NOT NULL REFERENCES dim_product(product_key),\n order_id TEXT NOT NULL,\n line_no INTEGER NOT NULL,\n shipment_id TEXT NOT NULL,\n shipped_units INTEGER NOT NULL CHECK(shipped_units > 0),\n source_batch_id TEXT NOT NULL,\n UNIQUE(shipment_id,order_id,line_no)\n);\nINSERT INTO fact_fulfillment VALUES\n(1,20260919,101,201,'O1001',1,'SH1001',2,'FUL-001'),\n(2,20260919,101,202,'O1001',2,'SH1001',1,'FUL-001'),\n(3,20260920,102,203,'O1002',1,'SH1002',1,'FUL-001'),\n(4,20260920,101,204,'O1003',1,'SH1003',1,'FUL-001'),\n(5,20260920,101,202,'O1003',2,'SH1003',2,'FUL-001'),\n(6,20260920,104,201,'O1005',1,'SH1005',1,'FUL-001'),\n(7,20260920,104,204,'O1005',2,'SH1005',1,'FUL-001');\n\nCREATE TABLE fact_returns (\n return_key INTEGER PRIMARY KEY,\n return_date_key INTEGER NOT NULL REFERENCES dim_date(date_key),\n customer_key INTEGER NOT NULL REFERENCES dim_customer(customer_key),\n product_key INTEGER NOT NULL REFERENCES dim_product(product_key),\n return_reason_key INTEGER NOT NULL REFERENCES dim_return_reason(return_reason_key),\n order_id TEXT NOT NULL,\n line_no INTEGER NOT NULL,\n return_id TEXT NOT NULL UNIQUE,\n returned_units INTEGER NOT NULL CHECK(returned_units > 0),\n return_amount NUMERIC NOT NULL CHECK(return_amount >= 0),\n source_batch_id TEXT NOT NULL\n);\nINSERT INTO fact_returns VALUES\n(1,20260920,101,202,501,'O1003',2,'R1001',1,25,'RET-001');\n\nCREATE TABLE fact_marketing_exposure (\n exposure_key INTEGER PRIMARY KEY,\n exposure_date_key INTEGER NOT NULL REFERENCES dim_date(date_key),\n customer_key INTEGER NOT NULL REFERENCES dim_customer(customer_key),\n product_key INTEGER NOT NULL REFERENCES dim_product(product_key),\n campaign_key INTEGER NOT NULL REFERENCES dim_campaign(campaign_key),\n source_event_id TEXT NOT NULL UNIQUE\n);\nINSERT INTO fact_marketing_exposure VALUES\n(1,20260918,101,201,401,'EXP-001'),\n(2,20260918,101,202,401,'EXP-002'),\n(3,20260918,102,203,402,'EXP-003'),\n(4,20260919,101,204,401,'EXP-004'),\n(5,20260920,104,201,401,'EXP-005'),\n(6,20260920,104,204,401,'EXP-006');\n\nCREATE TABLE source_key_map (\n source_system TEXT NOT NULL,\n entity_type TEXT NOT NULL,\n source_key TEXT NOT NULL,\n durable_id TEXT NOT NULL,\n warehouse_key INTEGER NOT NULL,\n PRIMARY KEY(source_system,entity_type,source_key)\n);\nINSERT INTO source_key_map VALUES\n('erp','customer','C001','D-CUST-001',101),\n('marketing','customer','MKT-C001','D-CUST-001',101),\n('erp','customer','C002','D-CUST-002',102),\n('marketing','customer','MKT-C002','D-CUST-002',102),\n('erp','customer','C004','D-CUST-004',104),\n('marketing','customer','MKT-C004','D-CUST-004',104),\n('erp','product','P100','P100',201),\n('marketing','product','M-P100','P100',201),\n('erp','product','P200','P200',202),\n('marketing','product','M-P200','P200',202),\n('erp','product','P300','P300',203),\n('marketing','product','M-P300','P300',203),\n('erp','product','P400','P400',204),\n('marketing','product','M-P400','P400',204);\n\nCREATE TABLE marketing_product_local (\n product_code TEXT PRIMARY KEY,\n product_name TEXT NOT NULL,\n category_name TEXT NOT NULL\n);\nINSERT INTO marketing_product_local VALUES\n('P100','Keyboard','Peripherals'),\n('P200','Mouse','Accessories'),\n('P300','Monitor','Displays'),\n('P400','Dock','Accessories');\n")paid_gmv=conn.execute("SELECT SUM(extended_amount) FROM fact_sales").fetchone()[0]return_amount=conn.execute("SELECT SUM(return_amount) FROM fact_returns").fetchone()[0]post_return=paid_gmv-return_amountassert (paid_gmv,return_amount,post_return)==(625,25,600)# False conformance: local marketing category disagrees with governed category.mismatch=conn.execute("""SELECT COUNT(*)FROM marketing_product_local m JOIN dim_product p ON p.product_id=m.product_codeWHERE m.category_name<>p.category_name""").fetchone()[0]assert mismatch==1print('paid_gmv=',paid_gmv,'return_amount=',return_amount,'post_return=',post_return)print('false product conformance rows=',mismatch)print('semantic contract checks: PASS')5. Change management: conformance creates a blast radius
Once Product is conformed, changing P100’s enterprise category is no longer a local Marketing edit. The steward must determine whether the change is a correction, a current-state overwrite, or a historical change that needs effective dating. Chapter 07 handles SCD mechanics; Chapter 06 records the ownership and downstream impact so the loader does not invent policy.
A conformed dimension therefore reduces semantic duplication but increases the importance of controlled change. Every supposedly harmless shared-attribute edit should identify affected facts, reports, metric slices, and reload/restatement policy.
Knowledge check
Check your understanding
- Why is master data not automatically the same thing as a conformed dimension?
- Why should 625 and 600 not both be labeled simply “revenue” in this lab?
- Can a Marketing-only attribute exist on a shared Product dimension?
- What new operational cost comes with conformance?
Review the answers
1. Master data governs durable entities; the conformed dimension is the analytical contract exposed to facts and still needs analytical history, unknown-member, freshness, and publication rules.
2. They represent different event inclusion rules: paid GMV versus paid GMV net of the recorded return. Equal names would imply a conformed fact definition that does not exist.
3. Yes, if it is clearly scoped/private and not used as a supposedly conformed row header. Conformance can cover a shared subset of attributes.
4. Shared changes have a larger blast radius, so stewardship, versioning, impact analysis, and controlled rollout become more important.
Summary and next step
Shared dimensions and metrics need owners, definitions, and change controls—not just identical column names. Next, use those contracts across fact tables whose grains differ and prove a safe drill-across.
Authoritative references
- Kimball Group — Conformed Dimensions — Shared attribute domains that enable consistent row headers across fact tables.
- Kimball Group — Conformed Facts — Consistent technical definitions for measurements reused across fact tables.
- Kimball Group — Enterprise Data Warehouse Bus Architecture — Incremental process-centric delivery connected by standardized conformed dimensions.
- Kimball Group — Enterprise Data Warehouse Bus Matrix — Business-process rows, dimension columns, and incremental delivery planning.
- Kimball Group — Drilling Across — Separate fact-table queries aligned on identical conformed row headers.
- SQLite — Foreign Key Support — Referential-integrity behavior for the local deterministic lab.