Skip to content

Master/Reference Data Management: Tough Problems and How to Solve Them

Source: Oracle Enterprise Architecture Framework (OEAF)

Getting your master and reference data right, things like countries, currencies, and product codes, seems simple on the surface. That is, until you integrate your third ERP, ingest partner data feeds, or try to reconcile five different versions of the “United Kingdom” (GB? UK? GBR? Great Britain? 🇬🇧). The small cracks in reference data quickly become major fissures in your analytics and operations.

Below are the major challenges I see most often and some practical fixes you can implement. After that, you’ll find a ready-to-run Master/Reference Data Standardization project outline, complete with dbt models, scripts, and suggested datasets to get you started.

The Biggest Challenges (and What to Do About Them)

1. Inconsistent Code Systems Across Apps

  • Symptom: Your sales system uses the ISO 3166-1 alpha-2 code NL, your logistics platform uses the alpha-3 code NLD, and a legacy billing system stores the free-text name Netherlands. Reporting becomes a nightmare of complex CASE statements.
  • Fix:
    • Choose a canonical standard for each domain (For example, ISO 3166-1 alpha-2 for countries, ISO 4217 for currencies).
    • Create mapping tables (map_country_source) to translate all source codes into your chosen canonical code.
    • Enforce standards in your analytics layer with dbt tests and push for validation rules in source systems.

2. Code Set Drift & Versioning

  • Symptom: Standards evolve, countries are formed (South Sudan, SS), and currencies are re-denominated, but your internal seed files are stuck in 2015.
  • Fix:
    • Use Slowly Changing Dimensions (SCD-2). Add valid_from and valid_to columns to your reference tables to track the history of changes.
    • Automate refreshes. Schedule monthly jobs to pull the latest lists from authoritative sources like GeoNames or the UN.
    • Maintain a changelog to audit when reference data changes and how it impacts downstream metrics.

3. Name Chaos (Diacritics, Synonyms, Languages)

  • Symptom: Your data contains a mix of Côte d’Ivoire, Ivory Coast, Curaçao, and Curacao. Simple string matching fails, leading to fragmented reporting.
  • Fix:
    • Store multiple name variants. Include a canonical English short name, localized display names (from a source like Unicode CLDR), and a normalized search_key (ASCII-only, lowercased).
    • Use fuzzy matching and alias tables. Maintain a table of known alternate names to map synonyms to the canonical entity.

4. Semantic Mismatches

  • Symptom: Users and systems conflate different concepts. Is “country” the same as a “territory” or an “economic zone” (like the Eurozone)? This ambiguity leads to incorrect aggregations.
  • Fix:
    • Model concepts as separate entities. Create distinct tables like dim_country, dim_region (using UN M49 standards), and dim_location (using UN/LOCODE).
    • Document clear business rules. For example, explicitly state that the canonical code for the United Kingdom is GB (per ISO 3166-1) and that UK is a reserved code.

5. Duplicates & Survivorship Rules

  • Symptom: The same entity, a customer, a product, appears multiple times in your “golden” record data, often with slightly different attributes or codes.
  • Fix:
    • Define consolidation rules. For example, “the longest-lived code wins” or “prefer the ISO alpha-2 code over a free-text name.”
    • Combine fuzzy and exact matching. Use algorithms like Levenshtein distance on names while requiring exact matches on other identifiers, but flag borderline cases for human review.

6. Unclear Ownership & Governance

  • Symptom: When a new currency code is needed, no one knows who is responsible for adding it. The list of “official” product categories lives in a spreadsheet on someone’s desktop.
  • Fix:
    • Assign roles. Appoint a Data Owner (sets policy) and a Data Steward (handles day-to-day curation).
    • Establish a change workflow. Create a clear process: propose a change -> review -> publish -> audit.
    • Publish SLAs. Set expectations, such as “a new country code will be reflected in all systems within 3 business days of an official ISO update.”

7. Licensing & Access Constraints

  • Symptom: Your organization relies on a proprietary classification system (for example,, GS1 for products, certain financial identifiers) that requires a license, limiting its use in broader analytics.
  • Fix:
    • Prefer open datasets for your analytics layer whenever possible (for example UN, GeoNames, Datahub.io).
    • Isolate licensed data. Keep licensed datasets in a separate, governed zone in your data warehouse and only expose what is permitted by the license terms.

Project: Master/Reference Data Standardization (with dbt)

Here’s a practical, step-by-step project to build a standardized reference data layer using dbt.

GitHub link: https://github.com/hlosukwakha/dbt-ref-standardization-starter/tree/main

Problem Statement

Your organization runs multiple ERPs and CRMs and ingests partner data feeds, all of which use different code systems and free-text labels. This inconsistency causes errors in analytics, billing, and compliance reporting due to mismatches like UK vs. GB, incorrect currency decimal places, and stale or missing codes.

Why This Matters

  • Accuracy 🎯: Financial, tax, and regulatory reports depend on correct reference data.
  • Speed ⚡️: Clean reference data eliminates countless hours spent on manual “join fixes” and reconciliation.
  • Interoperability 🤝: Canonical codes are the key to building repeatable, reliable integrations.
  • Governance 🏛️: A single, auditable source of truth for reference data reduces operational risk.

Scope (Phase 1)

  1. Countries/Territories: Standardize on ISO 3166-1, enriched with UN M49 regions and GeoNames metadata.
  2. Currencies: Standardize on ISO 4217 (alphabetic code, numeric code, minor units).
  3. Locations (Optional): Standardize on UN/LOCODE for ports and cities used in logistics.

Data Sources You Can Use

Target Model Design

  • Core Tables:
    • dim_country: The canonical list of countries, with country_code (ISO alpha-2) as the primary key.
    • dim_currency: The canonical list of currencies, with currency_code (ISO 4217 alpha-3) as the primary key.
    • map_country_source: A mapping table to link each source system’s country representation to the canonical country_code.
  • Conventions:
    • Use Slowly Changing Dimensions (SCD-2) for descriptive fields (country_name) with valid_from, valid_to, and is_current columns.
    • Implement dbt tests for uniqueness, not-null constraints, and referential integrity.

dbt Project Skeleton

ref_data_project/
├─ models/
│  ├─ staging/
│  │  ├─ stg_erp_a_countries.sql
│  │  ├─ stg_erp_b_countries.sql
│  │  └─ ...
│  ├─ marts/
│  │  ├─ ref/
│  │  │  ├─ dim_country.sql
│  │  │  ├─ dim_currency.sql
│  │  │  └─ map_country_source.sql
│  └─ ref.yml
├─ macros/
│  └─ normalize.sql
├─ seeds/
│  ├─ iso_3166_countries.csv
│  ├─ iso_4217_currencies.csv
│  ├─ un_m49_regions.csv
│  └─ country_aliases.csv
└─ dbt_project.yml

Example Seed Files

seeds/iso_3166_countries.csv

Code snippet

country_code,country_name,alpha3,numeric,valid_from,valid_to,is_current
NL,Netherlands,NLD,528,1900-01-01,,true
GB,United Kingdom,GBR,826,2635167,1900-01-01,,true
CI,Côte d’Ivoire,CIV,384,1986-01-01,,true
CUW,Curaçao,CUW,531,2010-10-10,,true

seeds/country_aliases.csv

Code snippet

alias,country_code
Great Britain,GB
UK,GB
United Kingdom of Great Britain and Northern Ireland,GB
Ivory Coast,CI

Reusable dbt Macros

macros/normalize.sql

SQL

{% macro normalize_text(text_column) -%}
    -- Removes accents, converts to lowercase, and removes non-alphanumeric characters
    -- Example: "Côte d'Ivoire" -> "cotedivoire"
    regexp_replace(lower(unaccent({{ text_column }})), '[^a-z0-9]+', '', 'g')
{%- endmacro %}

dbt Models

models/marts/ref/map_country_source.sql

SQL

-- Unifies country codes from multiple source systems into a single canonical mapping table.
with erp_a as (
    select distinct
        'ERP_A' as source_system,
        country_code_raw as source_value,
        {{ normalize_text('country_code_raw') }} as normalized_value
    from {{ ref('stg_erp_a_countries') }}
),
erp_b as (
    select distinct
        'ERP_B' as source_system,
        country_name as source_value,
        {{ normalize_text('country_name') }} as normalized_value
    from {{ ref('stg_erp_b_countries') }}
),
all_sources as (
    select * from erp_a
    union all
    select * from erp_b
),
-- Match against canonical codes, names, and aliases
final as (
    select
        s.source_system,
        s.source_value,
        coalesce(
            direct_match.country_code,
            name_match.country_code,
            alias_match.country_code
        ) as canonical_country_code
    from all_sources s
    -- 1. Direct match on alpha-2 or alpha-3 code
    left join {{ ref('iso_3166_countries') }} direct_match
        on s.normalized_value = lower(direct_match.country_code) or s.normalized_value = lower(direct_match.alpha3)
    -- 2. Match on normalized country name
    left join {{ ref('iso_3166_countries') }} name_match
        on s.normalized_value = {{ normalize_text('name_match.country_name') }}
    -- 3. Match on alias
    left join {{ ref('country_aliases') }} alias_lookup
        on s.normalized_value = {{ normalize_text('alias_lookup.alias') }}
    left join {{ ref('iso_3166_countries') }} alias_match
        on alias_lookup.country_code = alias_match.country_code
)
select
    source_system,
    source_value,
    canonical_country_code,
    current_timestamp as mapped_at
from final
-- Use QUALIFY to pick the best match if multiple are found, though the coalesce logic handles precedence.
qualify row_number() over (partition by source_system, source_value order by canonical_country_code is not null desc) = 1

models/marts/ref/dim_country.sql

SQL

-- Creates the final, enriched dimension table for countries.
with countries as (
    select * from {{ ref('iso_3166_countries') }}
),
regions as (
    select * from {{ ref('un_m49_regions') }}
)
select
    c.country_code,          -- Primary Key (ISO 3166-1 alpha-2)
    c.country_name,
    c.alpha3,
    c.numeric,
    r.region_name,
    r.sub_region_name,
    c.valid_from,
    c.valid_to,
    c.is_current
from countries c
left join regions r on c.numeric = r.country_numeric_code

Tests & Documentation

models/ref.yml

YAML

version: 2
models:
  - name: dim_country
    description: "Canonical list of countries (ISO-3166-1) with UN M49 regions. SCD-2."
    columns:
      - name: country_code
        description: "Primary Key: ISO 3166-1 alpha-2 code."
        tests:
          - unique
          - not_null
  - name: map_country_source
    description: "Mapping of source system country values to canonical country_code."
    columns:
      - name: canonical_country_code
        tests:
          - relationships:
              to: ref('dim_country')
              field: country_code
      - name: source_value
        tests:
          - dbt_utils.expression_is_true:
              expression: "canonical_country_code is not null"
              config:
                severity: warn # Warn for unmapped codes instead of failing the build

Automation Scripts

Bash: Bootstrap a dbt project

Bash

#!/usr/bin/env bash
set -euo pipefail
PROJECT="ref_data_project"
dbt init "$PROJECT"
cd "$PROJECT"
mkdir -p models/marts/ref models/staging macros seeds
# Add dbt_utils package for testing
echo 'packages:
  - package: dbt-labs/dbt_utils
    version: ">=1.1.1"
' > packages.yml
dbt deps
echo "✅ Project '$PROJECT' created."

Python: Pull seed data from the web

Python

import pandas as pd
# --- Countries from Datahub.io ---
print("Fetching country data...")
countries_url = "https://datahub.io/core/country-codes/r/country-codes.csv"
df_countries = pd.read_csv(countries_url)
iso_countries = df_countries[['ISO3166-1-Alpha-2', 'official_name_en', 'ISO3166-1-Alpha-3', 'ISO3166-1-Numeric']].copy()
iso_countries.columns = ['country_code', 'country_name', 'alpha3', 'numeric']
iso_countries.dropna(subset=['country_code'], inplace=True)
iso_countries['valid_from'] = '1900-01-01'
iso_countries['valid_to'] = pd.NA
iso_countries['is_current'] = True
iso_countries.to_csv('seeds/iso_3166_countries.csv', index=False)
# --- Currencies from Wikipedia ---
print("Fetching currency data...")
wiki_url = "https://en.wikipedia.org/wiki/ISO_4217"
df_curr = pd.read_html(wiki_url)[1] # The second table on the page is the active codes list
df_curr = df_curr[['Code', 'Num', 'Minor unit', 'Currency']].copy()
df_curr.columns = ['currency_code', 'numeric', 'minor_unit', 'currency_name']
df_curr.dropna(subset=['currency_code'], inplace=True)
df_curr['valid_from'] = '1900-01-01'
df_curr['valid_to'] = pd.NA
df_curr['is_current'] = True
df_curr.to_csv('seeds/iso_4217_currencies.csv', index=False)
print("✅ Seed files written to ./seeds folder.")

(Note: Always verify licensing and terms of use before automating the use of third-party data.)

Visualizations to Monitor & Explain Quality

Once your models are running, visualize the results to track progress and demonstrate value.

  • Bar Chart: Unmapped Codes by Source System: Quickly shows which source systems need the most cleanup.
  • Sankey Diagram: Source Names → Canonical Country: Powerfully illustrates the consolidation impact, showing how many variations are mapped to a single standard.
  • Time Series: Reference Rows Changed per Week: Helps you detect unexpected churn or verify that automated refreshes are working.
  • Choropleth Map: Customer Count by Canonical Country: Validates that your geographic data is being labeled correctly and completely.

Conclusion

Reference data is the thin, foundational layer that everything else stands on. Standardizing it by choosing canonical code sets, enforcing mappings with dbt, refreshing from authoritative sources, and governing changes pays dividends everywhere. You’ll enable faster analytics, fight fewer reconciliation fires, and build cleaner integrations. Start small with countries and currencies, automate the process, and make your data quality visible with a handful of clear charts.