Chapter 06 · Conformed Dimensions, Enterprise Bus Architecture, and Cross-Process Analytics
Build a Bus Matrix and Identify Where Supposedly Shared Dimensions Are Not Actually Conformed
Audit the AtlasMart bus matrix and conformance contracts end to end, inject false conformance and incompatible-grain failures, and verify cross-process results against independent control totals.
Learning outcomes
A bus matrix is useful only if AtlasMart can prove the shaded cells are really conformed. This final lesson converts the matrix into executable checks: shared identities resolve, common attributes agree, process controls reconcile, incompatible fact names remain distinct, and drill-across uses independent aggregation rather than atomic cross-joins.
Translate bus-matrix claims into executable conformance tests.
Detect local attribute drift even when keys and names appear compatible.
Reject forced all-in-one fact designs and atomic fact-to-fact joins.
Reconcile every process independently and then reconcile the aligned cross-process report.
Define a production change/rollback surface for shared dimensions and metric contracts.
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. Audit the matrix as a set of promises
| Business process / fact grain | Date | Customer | Product | Store | Campaign | Return reason |
|---|---|---|---|---|---|---|
| Sales — one paid order line | ✓ order date | ✓ | ✓ | ✓ | — | — |
| Fulfillment — one shipped order line | ✓ ship date | ✓ | ✓ | — | — | — |
| Inventory — one product per snapshot date | ✓ snapshot date | — | ✓ | — | — | — |
| Returns — one returned order line event | ✓ return date | ✓ | ✓ | — | — | ✓ |
| Marketing — one customer/product/campaign exposure event | ✓ exposure date | ✓ | ✓ | — | ✓ | — |
| Promise | Executable evidence | Failure response |
|---|---|---|
| Product is conformed | source keys resolve to governed product; shared attributes match allowed domain | quarantine mapping/attribute drift; do not merge row headers |
| Customer is conformed where applicable | durable IDs and warehouse keys resolve across sources | repair identity map; use explicit unknown if policy allows |
| Facts keep independent grain | unique keys and process controls pass | stop load/report; fix grain before integration |
| Shared fact names mean same definition | formula/inclusion/unit/time contracts identical | rename incompatible facts; do not claim conformance |
| Drill-across is safe | independent aggregates reconcile then align | replace atomic fact join with multipass aggregation |
2. Failure injection A: false Product conformance
The local Marketing table advertises P100 as “Peripherals.” An analyst who groups Marketing by that local category and Sales by the governed category will split P100 across incompatible row headers. The audit must fail before the two results are stitched together.
SELECT m.product_code,m.product_name, m.category_name AS local_category, p.category_name AS governed_categoryFROM marketing_product_local mJOIN dim_product p ON p.product_id=m.product_codeWHERE m.category_name<>p.category_name;3. Failure injection B: incompatible grains hidden by one universal fact
A universal row with columns such as sold_units,
shipped_units, on_hand_units,
returned_units, and exposure_count has
no single honest grain. One row cannot simultaneously be an
order line, shipment line, daily product snapshot, return event,
and campaign exposure. NULL-heavy columns do not solve the
semantic contradiction.
The repair is the bus architecture itself: separate process facts connected by conformed dimensions. Integration happens in queries/semantic models after each process has been aggregated appropriately.
4. Failure injection C: atomic fact-to-fact join
SELECT SUM(s.quantity) AS sold_units, SUM(f.shipped_units) AS shipped_unitsFROM fact_sales sJOIN fact_fulfillment f ON f.product_key=s.product_key;
The result 17/17 is an observable cardinality failure. The
correct controls are 9/9, proven independently. A
DISTINCT wrapper is not a repair because repeated
quantities can be legitimate and the business-event multiplicity
remains unresolved.
5. End-to-end Chapter 06 acceptance test
import sqlite3, mathconn=sqlite3.connect(":memory:")conn.execute("PRAGMA foreign_keys=ON")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")# A. Preserve prior accepted sales/inventory controls.sales=conn.execute("""SELECT COUNT(*),COUNT(DISTINCT order_id),SUM(quantity),SUM(extended_amount),SUM(extended_cost),SUM(extended_amount-extended_cost)FROM fact_sales""").fetchone()assert sales==(7,4,9,625,380,245),salesassert conn.execute("SELECT SUM(on_hand_units) FROM fact_inventory_snapshot WHERE snapshot_date_key=20260920").fetchone()[0]==137# B. New process controls remain independently reproducible.assert conn.execute("SELECT COUNT(*),SUM(shipped_units) FROM fact_fulfillment").fetchone()==(7,9)assert conn.execute("SELECT COUNT(*),SUM(returned_units),SUM(return_amount) FROM fact_returns").fetchone()==(1,1,25)assert conn.execute("SELECT COUNT(*) FROM fact_marketing_exposure").fetchone()[0]==6# C. Cross-source key maps converge on the same durable/warehouse identities.for src,etype,key,durable,wkey in conn.execute("SELECT source_system,entity_type,source_key,durable_id,warehouse_key FROM source_key_map"): if etype=='product': target=conn.execute("SELECT product_id FROM dim_product WHERE product_key=?",(wkey,)).fetchone() else: target=conn.execute("SELECT durable_customer_id FROM dim_customer WHERE customer_key=?",(wkey,)).fetchone() assert target and target[0]==durable,(src,etype,key,durable,wkey,target)# D. Deliberate false conformance is detected.mismatch=conn.execute("""SELECT COUNT(*) FROM marketing_product_local mJOIN dim_product p ON p.product_id=m.product_codeWHERE m.category_name<>p.category_name""").fetchone()[0]assert mismatch==1# E. Atomic fact-to-fact join demonstrates the cardinality bug.wrong=conn.execute("""SELECT SUM(s.quantity),SUM(f.shipped_units)FROM fact_sales s JOIN fact_fulfillment f ON f.product_key=s.product_key""").fetchone()assert wrong==(17,17),wrong# F. Multipass drill-across reconciles to all independent controls.rows=conn.execute('WITH sales AS (\n SELECT product_key,\n SUM(quantity) AS sold_units,\n SUM(extended_amount) AS paid_gmv\n FROM fact_sales GROUP BY product_key\n), shipped AS (\n SELECT product_key, SUM(shipped_units) AS shipped_units\n FROM fact_fulfillment GROUP BY product_key\n), inventory AS (\n SELECT product_key, SUM(on_hand_units) AS ending_on_hand\n FROM fact_inventory_snapshot\n WHERE snapshot_date_key = 20260920\n GROUP BY product_key\n), returns AS (\n SELECT product_key,\n SUM(returned_units) AS returned_units,\n SUM(return_amount) AS return_amount\n FROM fact_returns GROUP BY product_key\n), marketing AS (\n SELECT product_key, COUNT(*) AS exposures\n FROM fact_marketing_exposure GROUP BY product_key\n)\nSELECT p.product_id, p.product_name,\n COALESCE(s.sold_units,0) AS sold_units,\n COALESCE(sh.shipped_units,0) AS shipped_units,\n COALESCE(i.ending_on_hand,0) AS ending_on_hand,\n COALESCE(r.returned_units,0) AS returned_units,\n COALESCE(m.exposures,0) AS exposures,\n COALESCE(s.paid_gmv,0) AS paid_gmv,\n COALESCE(r.return_amount,0) AS return_amount\nFROM dim_product p\nLEFT JOIN sales s ON s.product_key=p.product_key\nLEFT JOIN shipped sh ON sh.product_key=p.product_key\nLEFT JOIN inventory i ON i.product_key=p.product_key\nLEFT JOIN returns r ON r.product_key=p.product_key\nLEFT JOIN marketing m ON m.product_key=p.product_key\nWHERE p.product_key > 0\nORDER BY p.product_key;').fetchall()assert sum(r[2] for r in rows)==9assert sum(r[3] for r in rows)==9assert sum(r[4] for r in rows)==137assert sum(r[5] for r in rows)==1assert sum(r[6] for r in rows)==6assert sum(r[7] for r in rows)==625assert sum(r[8] for r in rows)==25print('sales=',sales)print('false-conformance rows=',mismatch)print('wrong atomic join=',wrong)print('drill-across=',rows)print('Chapter 06 acceptance: PASS')
Expected final line: Chapter 06 acceptance: PASS.
The intentional mismatched Marketing category and the 17/17
atomic join are negative-test evidence; they are expected to be
detected, not published as valid analytics.
6. Production rollout and rollback surface
Conformed dimensions are shared infrastructure. Before changing Product or Customer identity/attributes, record the proposed contract version, impacted facts/marts/reports, source mappings, effective date/history policy, and whether existing facts need restatement. Publish changes through a controlled compatibility window where consumers can validate the new domain.
A rollback is not merely restoring a dimension table backup. If downstream facts or materializations were rebuilt under a new mapping, the rollback plan must restore the compatible dimension version and any dependent outputs together. Chapter 07 adds the effective-dating mechanics needed for historical attribute changes.
7. What Chapter 06 intentionally does not claim
The local lab does not prove an enterprise MDM platform, global data catalog, production lineage graph, or cross-cloud semantic layer. It demonstrates the modeling invariants those systems must preserve: explicit process grain, shared attribute domains, durable key mapping, named metric semantics, independent controls, and multipass drill-across.
It also does not claim that one bus matrix is permanently static. New processes and dimensions can be added, but previously shaded cells are contracts with downstream consumers and must be changed deliberately.
Knowledge check
Check your understanding
- What is the strongest evidence that two same-named Product dimensions are not conformed?
- Why is the 17/17 join result valuable even though it is wrong?
- What does a conformed fact require?
- What must a shared-dimension rollback consider besides the dimension table itself?
- What chapter comes next and why?
Review the answers
1. A shared attribute used as a row header has incompatible domain values—for example P100 category Peripherals locally versus Accessories in the governed dimension.
2. It is controlled failure evidence showing that valid shared keys do not make atomic fact-to-fact joins safe; it motivates the multipass repair.
3. An identical technical/business measurement definition—including event inclusion, units, and calculation semantics—if separate fact tables expose it under the same name.
4. Any dependent facts, mappings, materializations, semantic models, or reports produced under the changed contract may need coordinated restoration/rebuild.
5. Chapter 07 adds slowly changing dimension techniques so governed shared attributes can preserve or overwrite history according to explicit reporting requirements.
Summary and next step
Chapter 06 turns conformance into an executable enterprise contract: business-process rows keep independent grain, shared dimensions provide compatible row headers, shared facts require equal definitions, and cross-process analysis uses independent aggregation before alignment. Next, add time-aware history to these dimensions with SCD techniques and effective dating.
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.