Fabric Reference Table & Data Mapping Service
Most data-mapping projects don’t fail because a field is missing. They fail because of meaning: inconsistent codes, domain values, legacy identifiers, “almost the same” spellings, and context-dependent rules. That’s where reference (lookup) tables win—turning messy source values into stable, auditable target values.
This post gives you a quick, practical overview of the most common lookup-table patterns—and a place to dive deeper in a working reference implementation.
Why lookup tables are more than “an Excel sheet in a database”
Well-designed reference tables help you:
- Centralize business semantics (instead of duplicating mapping logic across pipelines/notebooks)
- Stabilize heterogeneous sources (ERP/CRM/files/APIs, old/new, manual/automated)
- Answer audit & data quality questions (“who mapped what, when, and why?”)
The lookup-table patterns that show up in real projects
1) Code-to-code (deterministic mapping)
Classic source_code → target_code, often 1:1.
Examples: status codes, product types, country/region codes.
Typical columns:
- source_system, source_code
- target_domain, target_code
- valid_from, valid_to
- is_active, priority
2) Normalization / canonicalization
When sources get “creative”: spelling variants, abbreviations, typos.
Example: “DE”, “Germany”, “GER”, “Deutschland” → canonical DE.
Heads-up: these tables tend to grow fast, so governance matters (or they become a junk drawer).
3) Hierarchies and rollups
Analytics needs structure:
Example: SKU → product group → category → division.
Tip: hierarchies change—plan for versioning / effective dating from day one.
4) Crosswalks between identities (entity resolution light)
When “customer” in System A isn’t the same identifier as in System B, but they belong together.
Example: crm_customer_id ↔ erp_customer_id (often with confidence/source).
5) Conditional / contextual lookups
Mapping depends on context: country, line of business, channel, time.
Example: tax codes based on country + product_type + effective_date.
Rule of thumb: model conditions explicitly—don’t hide critical business logic in a random SQL snippet inside a job.
What “good” reference tables usually include
If you take one thing away: lookup tables are data products. Treat them like it.
Practical fields/patterns that pay off:
- Provenance: source, created_by, created_at, change_reason
- Quality / governance: confidence, rule_id, review_status
- Ops: is_active, priority, fallback_handling
- Time: valid_from, valid_to, versioning/effective dating
Want the implementation details?
I’ve put a reference implementation into this repository:
https://github.com/philippfrenzel/msfabric-mapping-etk
It’s an implementation of a data mapping service built around the Microsoft Fabric Extensibility Toolkit—with a focus on how to structure and operate mappings/lookups in a way that stays maintainable when requirements (inevitably) change.
Update, forgott to add the video: Fabric Reference Table & Data Mapping Service
1 Reply
Thanks for sharing this, and for putting a working implementation behind it. Treating lookup tables as data products is the point I’d underline for anyone starting a Fabric build. (Disclosure: I work at Quantexa, which builds Quantexa Unify, a native Microsoft Fabric workload, so pattern 4 is close to home.)
Pattern 4 is where we see teams spend the most effort over time. A crosswalk between crm_customer_id and erp_customer_id starts small and manageable. Once you add a third or fourth system, a couple of acquisitions and records with no shared key at all, someone ends up maintaining matching logic by hand, and the table slowly becomes the junk drawer you warn about in pattern 2.
Unify takes that crosswalk off the to-do list. It runs inside your Fabric tenant on your own capacity, reads source tables from OneLake and resolves the records that refer to the same real-world business or person, even when they share no identifier. The output lands back in OneLake as Delta tables, with one table of canonical entities and one that links every source system and source key to its entity ID. That second table is the pattern 4 crosswalk, generated rather than hand-maintained, and each run refreshes it as new records arrive.
It also lightens pattern 2 for identity data. Unify standardizes names, addresses, phone numbers and emails as part of matching, so you don’t need a normalization table for every spelling of a company name. Country codes, status values and similar domain values still belong in your code-to-code tables.
Patterns 1, 3 and 5 stay exactly where you have them. One small change makes them more useful. Keying customer-level rules and hierarchy assignments on the canonical entity ID means each one applies once per real customer, rather than once per system record.
Your governance fields also suit the one place a person should still have the final say. A steward-owned override table, carrying the provenance and review_status columns you describe, can capture match and non-match decisions and feed them back into resolution. That keeps the who, what, when and why answerable for identity decisions too.
Good to see the Extensibility Toolkit getting this kind of practical use.
