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