Big Data AcademyQuery · move · process · govern

Chapter 06 · Conformed Dimensions, Enterprise Bus Architecture, and Cross-Process Analytics

Conformed Dimensions and Facts: Shared Meaning Across Business Processes

Define conformed dimensions and facts for AtlasMart, map source identities into shared date/product/customer domains, and prove that same-named objects are not necessarily semantically compatible.

Intermediate → Advanced110–130 minutesConformance contract labPython stdlib sqlite3 · local/syntheticLast reviewed: September 2026

Learning outcomes

AtlasMart now has several legitimate dimensional models. The danger is that each process team can create a table named dim_product or a column named units while silently assigning different meanings. Cross-process analytics becomes trustworthy only when common dimensions and facts share explicit domains, definitions, identity mappings, and owners.

01

Define conformed dimensions and distinguish physical table sharing from semantic conformance.

02

Define conformed facts and explain why equal column names are insufficient evidence of equal measurement semantics.

03

Map operational and marketing source identifiers to the governed AtlasMart product/customer keys without changing fact grain.

04

Detect false conformance with attribute-domain tests rather than relying on names.

05

Prove independent process controls before attempting any drill-across report.

Chapter 06 continuity 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.

Execution and scope note

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.

Important boundary

A shared surrogate key is convenient in this local lab because all facts reference the same dimension tables. In distributed marts, conformance can also be achieved through controlled copies or compatible row-header domains; do not assume physical co-location is required.

1. Conformance is a semantic contract, not a naming convention

A conformed dimension supplies compatible descriptive attributes across two or more business processes so the same row header means the same thing. “Product = P100 / Keyboard / Accessories” cannot mean one classification in Sales and another in Marketing if those attributes are used to align reports. A table name such as dim_product proves nothing by itself.

In the AtlasMart lab, Sales, Fulfillment, Inventory, Returns, and Marketing all reference the same governed dim_product. Sales, Fulfillment, Returns, and Marketing also use dim_customer; Inventory does not, because customer is not part of inventory-snapshot grain. All five processes use dim_date, but each fact names the date role explicitly: order, ship, snapshot, return, or exposure date.

Conformance question Evidence required Bad shortcut
Do product row headers mean the same thing? same governed product identity plus compatible attribute domains same table/column name
Can two facts be compared? separate grain declarations + compatible dimensional row headers they both contain product_key
Are two facts conformed? identical technical/business definition if they share a fact name both are numeric
Can a source ID be reused directly? source-to-durable identity mapping and collision policy the codes look similar

2. Preserve each process grain

Conformance does not mean forcing all activity into one giant fact table. Sales is one paid order line. Fulfillment is one shipped order line. Inventory is one product snapshot per date. Returns is one returned order-line event. Marketing is one customer/product/campaign exposure event. Those grains answer different questions and have different dimensions. Integration happens through shared semantic connection points, not through a universal row.

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 ✓ ✓ — ✓ —

3. Build the deterministic cross-process fixture

The following schema preserves Chapter 05 dimensions and fact controls, then adds three synthetic process fixtures. New process data is deliberately small so every control can be recomputed manually.

chapter06_fixture.sql 
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS fact_marketing_exposure;DROP TABLE IF EXISTS fact_returns;DROP TABLE IF EXISTS fact_fulfillment;DROP TABLE IF EXISTS fact_inventory_snapshot;DROP TABLE IF EXISTS fact_sales;DROP TABLE IF EXISTS source_key_map;DROP TABLE IF EXISTS marketing_product_local;DROP TABLE IF EXISTS dim_return_reason;DROP TABLE IF EXISTS dim_campaign;DROP TABLE IF EXISTS dim_store;DROP TABLE IF EXISTS dim_product;DROP TABLE IF EXISTS dim_customer;DROP TABLE IF EXISTS dim_geography;DROP TABLE IF EXISTS dim_date;CREATE TABLE dim_date (  date_key INTEGER PRIMARY KEY,  full_date TEXT NOT NULL UNIQUE,  calendar_year INTEGER NOT NULL,  calendar_month INTEGER NOT NULL,  day_of_month INTEGER NOT NULL,  day_name TEXT NOT NULL);CREATE TABLE dim_geography (  geography_key INTEGER PRIMARY KEY,  geography_id TEXT NOT NULL UNIQUE,  country_name TEXT NOT NULL,  region_name TEXT NOT NULL,  city_name TEXT,  geography_status TEXT NOT NULL);CREATE TABLE dim_customer (  customer_key INTEGER PRIMARY KEY,  durable_customer_id TEXT NOT NULL UNIQUE,  source_system TEXT NOT NULL,  source_customer_id TEXT NOT NULL,  customer_name TEXT NOT NULL,  segment TEXT NOT NULL,  geography_key INTEGER NOT NULL REFERENCES dim_geography(geography_key),  UNIQUE(source_system, source_customer_id));CREATE TABLE dim_product (  product_key INTEGER PRIMARY KEY,  product_id TEXT NOT NULL UNIQUE,  product_name TEXT NOT NULL,  brand_name TEXT NOT NULL,  department_name TEXT NOT NULL,  category_name TEXT NOT NULL,  subcategory_name TEXT);CREATE TABLE dim_store (  store_key INTEGER PRIMARY KEY,  store_id TEXT NOT NULL UNIQUE,  store_name TEXT NOT NULL,  store_type TEXT NOT NULL);CREATE TABLE dim_campaign (  campaign_key INTEGER PRIMARY KEY,  campaign_id TEXT NOT NULL UNIQUE,  campaign_name TEXT NOT NULL);CREATE TABLE dim_return_reason (  return_reason_key INTEGER PRIMARY KEY,  return_reason_code TEXT NOT NULL UNIQUE,  return_reason_name TEXT NOT NULL);INSERT INTO dim_date VALUES(20260918,'2026-09-18',2026,9,18,'Friday'),(20260919,'2026-09-19',2026,9,19,'Saturday'),(20260920,'2026-09-20',2026,9,20,'Sunday');INSERT INTO dim_geography VALUES(0,'__UNKNOWN__','Unknown','Unknown',NULL,'unknown'),(-1,'__NOT_APPLICABLE__','Not applicable','Not applicable',NULL,'not_applicable'),(501,'G-NORTH','Freedonia','North','Northport','known'),(502,'G-WEST','Freedonia','West','Westhaven','known'),(503,'G-EAST','Freedonia','East',NULL,'known_ragged'),(504,'G-SOUTH','Freedonia','South','Southbank','known');INSERT INTO dim_customer VALUES(0,'D-UNKNOWN','system','__UNKNOWN__','Unknown customer','Unknown',0),(-1,'D-NA','system','__NOT_APPLICABLE__','Not applicable customer','Not applicable',-1),(101,'D-CUST-001','erp','C001','Ada Retail','SMB',501),(102,'D-CUST-002','erp','C002','Ben Home','Consumer',502),(103,'D-CUST-003','crm','C003','Cyra Labs','Enterprise',503),(104,'D-CUST-004','erp','C004','Dara Studio','Consumer',504);INSERT INTO dim_product VALUES(0,'__UNKNOWN__','Unknown product','Unknown','Unknown','Unknown',NULL),(-1,'__NOT_APPLICABLE__','Not applicable product','Not applicable','Not applicable','Not applicable',NULL),(201,'P100','Keyboard','KeyWorks','Hardware','Accessories','Input Devices'),(202,'P200','Mouse','KeyWorks','Hardware','Accessories','Input Devices'),(203,'P300','Monitor','ViewCo','Hardware','Displays','Monitors'),(204,'P400','Dock','ConnectCo','Hardware','Accessories','Docking');INSERT INTO dim_store VALUES(0,'__UNKNOWN__','Unknown selling location','Unknown'),(301,'S-WEB','Web Store','Digital'),(302,'S-MOBILE','Mobile Store','Digital'),(303,'S-SALES','Assisted Sales','Assisted');INSERT INTO dim_campaign VALUES(401,'CMP-ACCESSORY','Accessory Awareness'),(402,'CMP-DISPLAY','Display Upgrade');INSERT INTO dim_return_reason VALUES(501,'CHANGED_MIND','Changed mind');CREATE TABLE fact_sales (  sales_key INTEGER PRIMARY KEY,  date_key INTEGER NOT NULL REFERENCES dim_date(date_key),  customer_key INTEGER NOT NULL REFERENCES dim_customer(customer_key),  product_key INTEGER NOT NULL REFERENCES dim_product(product_key),  store_key INTEGER NOT NULL REFERENCES dim_store(store_key),  order_id TEXT NOT NULL,  line_no INTEGER NOT NULL,  quantity INTEGER NOT NULL CHECK(quantity > 0),  unit_price NUMERIC NOT NULL CHECK(unit_price >= 0),  extended_amount NUMERIC NOT NULL CHECK(extended_amount >= 0),  extended_cost NUMERIC NOT NULL CHECK(extended_cost >= 0),  source_batch_id TEXT NOT NULL,  UNIQUE(order_id,line_no));INSERT INTO fact_sales VALUES(1,20260918,101,201,301,'O1001',1,2,50,100,60,'BATCH-CH03-001'),(2,20260918,101,202,301,'O1001',2,1,25,25,10,'BATCH-CH03-001'),(3,20260918,102,203,302,'O1002',1,1,200,200,140,'BATCH-CH03-001'),(4,20260919,101,204,301,'O1003',1,1,100,100,60,'BATCH-CH03-001'),(5,20260919,101,202,301,'O1003',2,2,25,50,20,'BATCH-CH03-001'),(6,20260920,104,201,302,'O1005',1,1,50,50,30,'BATCH-CH03-001'),(7,20260920,104,204,302,'O1005',2,1,100,100,60,'BATCH-CH03-001');CREATE TABLE fact_inventory_snapshot (  snapshot_date_key INTEGER NOT NULL REFERENCES dim_date(date_key),  product_key INTEGER NOT NULL REFERENCES dim_product(product_key),  on_hand_units INTEGER NOT NULL CHECK(on_hand_units >= 0),  source_batch_id TEXT NOT NULL,  PRIMARY KEY(snapshot_date_key,product_key));INSERT INTO fact_inventory_snapshot VALUES(20260919,201,42,'INV-20260919'),(20260919,202,63,'INV-20260919'),(20260919,203,13,'INV-20260919'),(20260919,204,28,'INV-20260919'),(20260920,201,40,'INV-20260920'),(20260920,202,60,'INV-20260920'),(20260920,203,12,'INV-20260920'),(20260920,204,25,'INV-20260920');CREATE TABLE fact_fulfillment (  fulfillment_key INTEGER PRIMARY KEY,  ship_date_key INTEGER NOT NULL REFERENCES dim_date(date_key),  customer_key INTEGER NOT NULL REFERENCES dim_customer(customer_key),  product_key INTEGER NOT NULL REFERENCES dim_product(product_key),  order_id TEXT NOT NULL,  line_no INTEGER NOT NULL,  shipment_id TEXT NOT NULL,  shipped_units INTEGER NOT NULL CHECK(shipped_units > 0),  source_batch_id TEXT NOT NULL,  UNIQUE(shipment_id,order_id,line_no));INSERT INTO fact_fulfillment VALUES(1,20260919,101,201,'O1001',1,'SH1001',2,'FUL-001'),(2,20260919,101,202,'O1001',2,'SH1001',1,'FUL-001'),(3,20260920,102,203,'O1002',1,'SH1002',1,'FUL-001'),(4,20260920,101,204,'O1003',1,'SH1003',1,'FUL-001'),(5,20260920,101,202,'O1003',2,'SH1003',2,'FUL-001'),(6,20260920,104,201,'O1005',1,'SH1005',1,'FUL-001'),(7,20260920,104,204,'O1005',2,'SH1005',1,'FUL-001');CREATE TABLE fact_returns (  return_key INTEGER PRIMARY KEY,  return_date_key INTEGER NOT NULL REFERENCES dim_date(date_key),  customer_key INTEGER NOT NULL REFERENCES dim_customer(customer_key),  product_key INTEGER NOT NULL REFERENCES dim_product(product_key),  return_reason_key INTEGER NOT NULL REFERENCES dim_return_reason(return_reason_key),  order_id TEXT NOT NULL,  line_no INTEGER NOT NULL,  return_id TEXT NOT NULL UNIQUE,  returned_units INTEGER NOT NULL CHECK(returned_units > 0),  return_amount NUMERIC NOT NULL CHECK(return_amount >= 0),  source_batch_id TEXT NOT NULL);INSERT INTO fact_returns VALUES(1,20260920,101,202,501,'O1003',2,'R1001',1,25,'RET-001');CREATE TABLE fact_marketing_exposure (  exposure_key INTEGER PRIMARY KEY,  exposure_date_key INTEGER NOT NULL REFERENCES dim_date(date_key),  customer_key INTEGER NOT NULL REFERENCES dim_customer(customer_key),  product_key INTEGER NOT NULL REFERENCES dim_product(product_key),  campaign_key INTEGER NOT NULL REFERENCES dim_campaign(campaign_key),  source_event_id TEXT NOT NULL UNIQUE);INSERT INTO fact_marketing_exposure VALUES(1,20260918,101,201,401,'EXP-001'),(2,20260918,101,202,401,'EXP-002'),(3,20260918,102,203,402,'EXP-003'),(4,20260919,101,204,401,'EXP-004'),(5,20260920,104,201,401,'EXP-005'),(6,20260920,104,204,401,'EXP-006');CREATE TABLE source_key_map (  source_system TEXT NOT NULL,  entity_type TEXT NOT NULL,  source_key TEXT NOT NULL,  durable_id TEXT NOT NULL,  warehouse_key INTEGER NOT NULL,  PRIMARY KEY(source_system,entity_type,source_key));INSERT INTO source_key_map VALUES('erp','customer','C001','D-CUST-001',101),('marketing','customer','MKT-C001','D-CUST-001',101),('erp','customer','C002','D-CUST-002',102),('marketing','customer','MKT-C002','D-CUST-002',102),('erp','customer','C004','D-CUST-004',104),('marketing','customer','MKT-C004','D-CUST-004',104),('erp','product','P100','P100',201),('marketing','product','M-P100','P100',201),('erp','product','P200','P200',202),('marketing','product','M-P200','P200',202),('erp','product','P300','P300',203),('marketing','product','M-P300','P300',203),('erp','product','P400','P400',204),('marketing','product','M-P400','P400',204);CREATE TABLE marketing_product_local (  product_code TEXT PRIMARY KEY,  product_name TEXT NOT NULL,  category_name TEXT NOT NULL);INSERT INTO marketing_product_local VALUES('P100','Keyboard','Peripherals'),('P200','Mouse','Accessories'),('P300','Monitor','Displays'),('P400','Dock','Accessories');

4. Source keys must map into the shared identity domain

The Marketing platform calls product P100 M-P100 and customer C001 MKT-C001. Those strings are not dimension keys. The source_key_map resolves them to durable business identities and warehouse keys. This protects conformance when operational systems use different namespaces or recycle identifiers.

Inspect source-to-conformed mappings 
SELECT source_system,entity_type,source_key,durable_id,warehouse_keyFROM source_key_mapORDER BY entity_type,durable_id,source_system;

5. Deliberately wrong approach: “same name means conformed”

The fixture contains a local Marketing product table whose P100 category is Peripherals while the governed product dimension classifies P100 as Accessories. Both rows are called P100/Keyboard; the shared attribute domain is still inconsistent.

False-conformance detector 
SELECT m.product_code,m.product_name,       m.category_name AS marketing_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;

Expected result: one row for P100. The repair is to map the source product identity into the governed product dimension and either adopt the governed category or explicitly keep a Marketing-only attribute that is never presented as the shared category. Silently overwriting the enterprise classification would make the conformance defect harder to detect.

6. Conformed facts need equal definitions, not equal labels

If two marts both expose paid_gmv, AtlasMart should require the same event inclusion rule, currency/unit, grain semantics, and formula before the facts are treated as conformed. By contrast, quantity in Sales means sold units, shipped_units in Fulfillment means shipped units, and returned_units in Returns means returned units. Renaming all three to units would erase process meaning instead of integrating it.

Chapter 06 therefore keeps process-specific names unless the measurement contract is truly identical. Conformed dimensions provide the row headers for comparison; conformed facts provide safe shared measures where definitions genuinely match.

Knowledge check

Check your understanding

  1. Why is a shared table name insufficient to prove conformance?
  2. Why does inventory omit customer_key?
  3. What is wrong with calling sold, shipped, and returned quantities all units?
  4. What concrete defect exists in the local Marketing product table?
Review the answers

1. Because conformance concerns compatible attribute domains, identities, and meanings used across fact tables; names alone do not prove any of those properties.

2. Customer is not part of the one-product-per-snapshot-date inventory grain. Adding a not-applicable customer merely to make every fact look alike would add a meaningless dimension.

3. They are measurements of different business events. A common label would imply conformed fact semantics that do not exist.

4. P100 is classified as Peripherals locally but Accessories in the governed product dimension, so category is not conformed.

Summary and next step

Conformance creates reusable semantic connection points while preserving business-process grain. Next, turn those connection points into an enterprise bus matrix that guides incremental warehouse delivery.

Authoritative references

Keep knowledge open

Help the academy stay free and grow.

If these tutorials save you time, a small donation supports new lessons, technical review, diagrams, examples, and long-term maintenance.

ETHEthereum / ERC-20 only
0x716c4Ab160C4B66F31a28AE2448BfF68fc3a2ef0

Send only Ethereum or ERC-20 compatible assets to this address.