Conformed dimensions and surrogate keys

Last updated: 6 Oct 20265 min read

tutorial

A conformed dimension gives related facts one governed definition of an entity and its attributes, while surrogate keys identify warehouse rows independently of source identifiers.

Resolve identity before sharing it

Two source systems can both call a customer 47. Never join on that bare value. Carry source namespace and source customer ID through landing, map them to a governed customer identity, then apply an explicit merge or separation rule. Ambiguous matches belong in a review queue. Exact linkage shows why a convenient string match can create false merges.

Keep the warehouse key stable

A generated customer dimension key can remain stable even when the source changes its display name or ID format. For a Type 2 history, each valid version receives a separate surrogate key while the durable business identity groups versions. Facts use the version key valid at their event time. The as-of join supplies the selection rule.

Conform only what is truly shared

The finance and support marts might agree on customer identity and home region but disagree on account status at different cutoffs. Put shared definitions in one conformed dimension; keep mart-specific classifications separate or versioned. A shared column name without a common business rule is a false contract.

Track unknowns deliberately

A fact can arrive before its customer dimension. Map it to a governed unknown member and retain the natural source key for later repair. Do not reuse one unknown row to imply all such facts came from the same customer. The repair process must update the fact reference without changing the fact measure.

Audit version ranges

For each durable identity, effective intervals must not overlap; if the product demands full temporal coverage, detect gaps too. Preserve the previous version when a new one begins. Validate that every fact maps to exactly one version at its event timestamp, then compare historical reports before and after migration.

Implementation

customer_versions = [
    {"source": "billing", "source_id": "47", "dim_key": 701, "start": 40, "end": 50},
    {"source": "billing", "source_id": "47", "dim_key": 702, "start": 50, "end": None},
    {"source": "support", "source_id": "47", "dim_key": 903, "start": 40, "end": None},
]

def dimension_key(versions, source, source_id, event_day):
    matches = [row for row in versions
               if row["source"] == source and row["source_id"] == source_id
               and row["start"] <= event_day
               and (row["end"] is None or event_day < row["end"])]
    if len(matches) != 1:
        raise ValueError("dimension lookup must resolve exactly one version")
    return matches[0]["dim_key"]

assert dimension_key(customer_versions, "billing", "47", 51) == 702
assert dimension_key(customer_versions, "support", "47", 51) == 903

Performance and operating cost

This small scan is O(V) per fact for V dimension versions; a warehouse should index or partition by namespaced business key and effective range. Durable identity mapping and version history add storage, but prevent false joins and historical relabeling.

Common Mistakes

Do not merge identical source IDs from unrelated systems.
Do not reuse one version surrogate key after an attribute change that requires history.
Do not assume shared column names imply shared business meaning.