Chapter 05 · Dimension Design: Descriptive Context, Hierarchies, Attributes, and Analytical Usability

Natural/Business Keys vs Surrogate Keys, Unknown Members, and Durable Identity

Separate source business keys, durable entity identity, warehouse surrogate keys, and default unknown/not-applicable members so AtlasMart facts remain stable when source identifiers change or context arrives late.

Intermediate → Advanced110–130 minutesIdentity and unknown-member labPython stdlib sqlite3 · local/syntheticLast reviewed: September 2026

Learning outcomes

Source identifiers are convenient until a source renumbers customers, two systems reuse the same code, or history requires more than one dimensional row for the same real-world entity. This lesson gives AtlasMart three explicit identity layers so facts do not inherit operational key instability.

Distinguish natural/business keys, durable entity identifiers, and warehouse surrogate keys.

Explain why fact foreign keys reference surrogate dimension rows rather than mutable source identifiers.

Resolve source-key changes without rewriting existing fact rows when entity identity is unchanged.

Use unknown and not-applicable members as governed default rows instead of NULL fact foreign keys.

Prepare identity rules for later SCD2 history without implementing Chapter 07 prematurely.

Chapter 05 continuity contract

The Chapter 03 sales fact still contains exactly 7 paid order lines, 4 paid orders, 9 units, and 625 paid GMV; Chapter 04 added 380 cost-at-sale and therefore 245 gross profit / 39.2% recomputed gross margin. Chapter 05 keeps every fact row and fact foreign key stable. It enriches descriptive context around the existing customer/product keys and introduces a governed geography dimension; no prior control total is silently restated.

Execution scope

The mandatory lab uses Python's standard-library sqlite3 module with synthetic AtlasMart rows and PRAGMA foreign_keys = ON. It is a semantic/modeling lab, not a warehouse-performance benchmark. Record your actual Python/SQLite versions; query plans, optimizer behavior, physical storage, collation, and constraint enforcement differ across engines.

1. Three identities solve three different problems

Identity	AtlasMart example	Owned by	Purpose
Natural/business key	`erp:C001`	source system	Operational lookup; can be reformatted, reused, or changed
Durable identity	`D-CUST-001`	warehouse/integration governance	Represents the same real-world entity across source-key changes or multiple sources
Surrogate row key	`101`	warehouse dimension loader	Identifies one dimension row/version referenced by fact rows

A durable identity and a surrogate row key happen to be one-to-one in the simple Chapter 05 fixture. They are still different concepts. When Chapter 07 introduces Type 2 history, one durable customer can have multiple surrogate rows over time while its durable identity remains constant.

2. Natural keys need a namespace

The code C001 is not globally meaningful unless its source and semantics are known. A CRM and ERP can both emit C001 for different entities. AtlasMart therefore treats (source_system, source_customer_id) as the operational key and stores a separate durable identifier that integration governance can preserve across systems.

Identity-aware customer DDL

CREATE TABLE customer_identity_map (  source_system TEXT NOT NULL,  source_customer_id TEXT NOT NULL,  durable_customer_id TEXT NOT NULL,  is_current INTEGER NOT NULL CHECK (is_current IN (0,1)),  PRIMARY KEY (source_system, source_customer_id));INSERT INTO customer_identity_map VALUES('erp','C001','D-CUST-001',1),('erp','C002','D-CUST-002',1),('crm','C003','D-CUST-003',1),('erp','C004','D-CUST-004',1);

3. Controlled source-key change: facts do not move

Suppose the ERP renumbers Ada Retail from C001 to C9001 without changing the represented customer. If business governance confirms that this is the same entity, AtlasMart changes the source-key mapping while preserving durable identity D-CUST-001. Because the existing sales facts point to surrogate key 101 rather than C001, historical fact rows do not need to be rewritten merely because the source identifier changed.

Simulate a source-key renumbering

UPDATE customer_identity_mapSET is_current=0WHERE source_system='erp' AND source_customer_id='C001';INSERT INTO customer_identity_mapVALUES ('erp','C9001','D-CUST-001',1);UPDATE dim_customerSET source_customer_id='C9001'WHERE customer_key=101;-- Facts still reference customer_key=101.SELECT COUNT(*) AS ada_lines, SUM(extended_amount) AS ada_gmvFROM fact_salesWHERE customer_key=101;

The result remains four paid lines and 275 GMV for surrogate customer 101. If the source change also represented a true descriptive historical change, Chapter 07 would decide whether to overwrite or create a new Type 2 surrogate row; a key rename alone does not justify inventing history.

4. Unknown versus not applicable versus known

Default members let the fact maintain a total, non-null foreign-key relationship while exposing data-quality state. Key 0 means the relationship exists in principle but is unresolved. Key -1 means the dimension does not apply to the event. A positive surrogate identifies a known member. These codes are warehouse conventions for this course fixture; production teams must document their own reserved-key policy.

Resolve an incoming customer key safely

import sqlite3conn=sqlite3.connect(":memory:")conn.execute("PRAGMA foreign_keys=ON")conn.executescript("PRAGMA foreign_keys = ON;\nDROP TABLE IF EXISTS fact_sales;\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);\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);\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);\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);\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);\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);\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');\n\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');\n\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);\n\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');\n\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');\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');")lookup={('erp','C001'):101,('erp','C002'):102,('crm','C003'):103,('erp','C004'):104}def customer_key(source_system, source_customer_id, applicable=True):    if not applicable:        return -1    return lookup.get((source_system,source_customer_id),0)assert customer_key('erp','C001')==101assert customer_key('erp','C999')==0assert customer_key('erp','',applicable=False)==-1print('resolved known / unknown / not-applicable keys: PASS')

5. Deliberately wrong approaches

Mutable natural key as fact foreign key: when the ERP renumbers a customer, either historical facts must be rewritten or the join stops working. NULL foreign key: an inner join silently drops the measurement and a left join produces an unlabeled bucket with no documented business meaning. Smart composite surrogate: embedding source code, customer code, or dates in the warehouse key couples facts to source semantics and makes future history harder.

The repair is a warehouse-controlled surrogate key plus a separately governed source-key/durable-identity mapping. Facts store the surrogate row key; integration logic owns identity resolution.

6. Hands-on identity acceptance test

verify_identity.py

import sqlite3conn=sqlite3.connect(":memory:")conn.execute("PRAGMA foreign_keys=ON")conn.executescript("PRAGMA foreign_keys = ON;\nDROP TABLE IF EXISTS fact_sales;\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);\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);\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);\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);\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);\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);\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');\n\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');\n\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);\n\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');\n\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');\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');")conn.executescript("""CREATE TABLE customer_identity_map( source_system TEXT NOT NULL, source_customer_id TEXT NOT NULL, durable_customer_id TEXT NOT NULL, is_current INTEGER NOT NULL, PRIMARY KEY(source_system,source_customer_id));INSERT INTO customer_identity_map VALUES('erp','C001','D-CUST-001',1),('erp','C002','D-CUST-002',1),('crm','C003','D-CUST-003',1),('erp','C004','D-CUST-004',1);""")before=conn.execute("SELECT COUNT(*),SUM(extended_amount) FROM fact_sales WHERE customer_key=101").fetchone()conn.execute("UPDATE customer_identity_map SET is_current=0 WHERE source_system='erp' AND source_customer_id='C001'")conn.execute("INSERT INTO customer_identity_map VALUES('erp','C9001','D-CUST-001',1)")conn.execute("UPDATE dim_customer SET source_customer_id='C9001' WHERE customer_key=101")after=conn.execute("SELECT COUNT(*),SUM(extended_amount) FROM fact_sales WHERE customer_key=101").fetchone()assert before==after==(4,275), (before,after)assert conn.execute("SELECT COUNT(*) FROM dim_customer WHERE customer_key IN (0,-1)").fetchone()[0]==2assert conn.execute("SELECT COUNT(*) FROM fact_sales WHERE customer_key IS NULL").fetchone()[0]==0print('fact stability =',after)print('identity acceptance: PASS')

7. Production judgment

Identity policy belongs in the warehouse contract, not in ad-hoc dashboard joins. Keep operational keys for source matching, create a durable entity identifier when cross-system/renumbering semantics require it, and use surrogate row keys for fact joins and later history. Unknown-member rates should be observable because a rising unresolved-key rate is a data-quality signal, not merely a cosmetic bucket.

The next lesson uses the descriptive attributes behind these identities to build safe drill paths, including a legitimately ragged geography hierarchy.

Knowledge check

Check your understanding

Why is erp:C001 safer than storing only C001 as a natural key?
Can one durable customer eventually have multiple surrogate keys?
Why does a source-key rename not require rewriting existing facts in this design?
When should key -1 be used instead of key 0?

Review the answers

1. The source namespace prevents accidental collisions with another system that uses the same code.

2. Yes. Type 2 history can create multiple dimension rows for the same durable entity while each row has its own surrogate key.

3. Facts reference the stable warehouse surrogate row, not the mutable operational identifier.

4. Use not applicable when the relationship has no business meaning; use unknown when the relationship should exist but is unresolved.

Summary and next step

Natural keys identify source records, durable identifiers represent stable entities, and surrogate keys identify dimension rows. Keeping those roles separate protects facts from operational key churn and prepares AtlasMart for historical versioning. Next, model hierarchy levels and drill paths explicitly.

Authoritative references

Kimball Group — Dimensions for Descriptive Context — Role of dimensions as descriptive filtering/grouping context.
Kimball Group — Dimension Table Structure — Wide, descriptive dimension-table structure and analytical labels.
Kimball Group — Dimension Surrogate Keys — Warehouse-controlled dimension primary keys and source-key independence.
Kimball Group — Multiple Hierarchies in Dimensions — Coexisting natural hierarchies and drill paths.
Kimball Group — Snowflaked Dimensions — Tradeoffs of normalizing dimension hierarchies.
Kimball Group — Outrigger Dimensions — Secondary dimension references and sparing use.
SQLite — Foreign Key Support — Local lab referential-integrity behavior.

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.