scripts/datasources/master_data/README.md

Master Data Management Strategy

Overview

This directory contains scripts for implementing a comprehensive Master Data Management (MDM) strategy to improve match rates across jurisdiction tables.

Problem Statement

Open Navigator has multiple jurisdiction/organization tables with overlapping data but poor linkage:

Table	Records	Key Fields	Primary Use
organization_location	328,840	website, state, city, name	Schools, hospitals, law enforcement
jurisdictions_wikidata	431	official_website, nces_id, geoid, fips_code	Wikidata enrichment
jurisdiction	85,302	geoid, fips_code, name, type, state	Census data
jurisdictions_details_search	17,219	website_url, gov_domains, jurisdiction_name	Enriched jurisdiction data

Current Issues:

• Duplicate data across tables
• No unified identifier system
• Low match rates between tables
• Inconsistent naming conventions
• Limited cross-referencing

Solution: Three-Table MDM Architecture

1. domain_registry

Normalized domain index from all website URLs.

CREATE TABLE domain_registry (
    id SERIAL PRIMARY KEY,
    domain VARCHAR(500) UNIQUE,           -- Normalized domain (e.g., "yupiit.org")
    source_table VARCHAR(100),            -- Source table name
    source_id INTEGER,                    -- ID in source table
    source_url TEXT,                      -- Original URL
    jurisdiction_name VARCHAR(500),       -- Entity name
    state_code VARCHAR(2),
    city VARCHAR(200),
    organization_type VARCHAR(100),
    confidence_score DECIMAL(3,2)
);

Example:

domain: "asdk12.org"
source_table: "organization_location"
source_id: 12345
source_url: "http://www.asdk12.org"
jurisdiction_name: "Anchorage School District"
state_code: "AK"

2. jurisdiction_crosswalk

Cross-reference mapping between all source tables.

CREATE TABLE jurisdiction_crosswalk (
    id SERIAL PRIMARY KEY,
    master_jurisdiction_id INTEGER,       -- Link to master record
    
    -- Source IDs
    org_location_id INTEGER,              -- organization_location.id
    wikidata_id INTEGER,                  -- jurisdictions_wikidata.id
    search_id INTEGER,                    -- jurisdiction.id
    details_search_id INTEGER,            -- jurisdictions_details_search.id
    
    -- Identifiers
    nces_id VARCHAR(20),                  -- School district ID
    fips_code VARCHAR(20),                -- County/state FIPS
    geoid VARCHAR(20),                    -- Census GEOID
    
    -- Matching metadata
    match_method VARCHAR(100),            -- How match was made
    match_confidence DECIMAL(3,2)         -- 0.0 to 1.0
);

3. master_jurisdictions

Canonical "golden record" for each jurisdiction.

CREATE TABLE master_jurisdictions (
    id SERIAL PRIMARY KEY,
    
    -- Best available identifiers
    nces_id VARCHAR(20),
    fips_code VARCHAR(20),
    geoid VARCHAR(20),
    wikidata_id VARCHAR(20),
    
    -- Canonical name (deduplicated)
    canonical_name VARCHAR(500) NOT NULL,
    alternate_names TEXT[],               -- All name variations
    
    -- Consolidated geography
    state_code VARCHAR(2) NOT NULL,
    state VARCHAR(50) NOT NULL,
    county VARCHAR(200),
    city VARCHAR(200),
    
    -- Best available data
    primary_website TEXT,
    all_websites TEXT[],                  -- All known websites
    domains TEXT[],                       -- All domains
    
    -- Quality metrics
    source_count INTEGER,                 -- How many sources contributed
    data_completeness_score DECIMAL(3,2)  -- Data quality score
);

Matching Strategies

Strategy 1: Domain-Based Matching (Highest Confidence)

Use Case: Match entities with same website domain

Algorithm:

1. Extract domain from URL: http://www.yupiit.org → yupiit.org
2. Normalize: Remove www., lowercase, remove trailing slashes
3. Match across all tables with same domain

Confidence: 0.95 (very high)

SQL Example:

-- Find all entities sharing the same domain
SELECT 
    dr.domain,
    dr.jurisdiction_name,
    dr.source_table,
    dr.state_code
FROM domain_registry dr
WHERE dr.domain = 'yupiit.org';

Strategy 2: ID-Based Matching (Exact Match)

Use Case: Match using standardized identifiers

Supported IDs:

• NCES ID: School districts (e.g., 0200090)
• GEOID: Census geographic identifier
• FIPS Code: County/state codes

Confidence: 1.0 (exact match)

SQL Example:

-- Match school district by NCES ID
SELECT 
    ol.name as org_name,
    jw.jurisdiction_name as wiki_name,
    ol.website,
    jw.official_website
FROM organization_location ol
JOIN jurisdictions_wikidata jw ON ol.source_id = jw.nces_id
WHERE ol.organization_type = 'school_district';

Strategy 3: Geographic Hierarchy Matching

Use Case: Match by city → county → state hierarchy

Algorithm:

1. Exact state match (required)
2. Exact county match (if available)
3. Exact city match (if available)
4. Fuzzy name match within geographic scope

Confidence: 0.70-0.90 (depends on specificity)

SQL Example:

-- Match organizations to jurisdictions by geography
SELECT 
    ol.name,
    ol.city,
    ol.state,
    js.name as jurisdiction_name,
    js.type as jurisdiction_type
FROM organization_location ol
JOIN jurisdiction js 
    ON ol.state = js.state_code
    AND ol.city = js.name
    AND js.type = 'city';

Strategy 4: Fuzzy Name Matching

Use Case: Match entities with similar but not identical names

Algorithm:

1. Normalize names (lowercase, remove suffixes, remove punctuation)
2. Calculate similarity score (Levenshtein distance / SequenceMatcher)
3. Match if score ≥ threshold (default: 0.85)

Confidence: 0.70-0.85 (varies by score)

Examples:

"Anchorage School District" → "anchorage"
"Anchorage Public Schools" → "anchorage"
Similarity: 0.90 → MATCH

"Yupiit School District" → "yupiit"
"Yup'ik Regional School Board" → "yupik regional"
Similarity: 0.45 → NO MATCH

Implementation Workflow

Step 1: Run Master Data Creation

cd /home/developer/projects/open-navigator
python scripts/datasources/master_data/create_jurisdiction_master.py

This will:

1. Create 3 master tables (domain_registry, jurisdiction_crosswalk, master_jurisdictions)
2. Extract and normalize all domains from websites
3. Match across tables using:
   • NCES ID matching
   • GEOID matching
   • Domain matching
   • (Optional) Fuzzy name matching
4. Generate consolidated master records
5. Produce match report

Step 2: Query Examples

Example 1: Find all data sources for a school district

SELECT 
    mj.canonical_name,
    mj.state_code,
    mj.nces_id,
    mj.source_count,
    mj.all_websites,
    jc.org_location_id,
    jc.wikidata_id,
    jc.details_search_id,
    jc.match_method
FROM master_jurisdictions mj
JOIN jurisdiction_crosswalk jc ON mj.id = jc.master_jurisdiction_id
WHERE mj.canonical_name ILIKE '%anchorage%'
  AND mj.state_code = 'AK';

Example 2: School districts with multiple website sources

SELECT 
    mj.canonical_name,
    mj.state_code,
    mj.source_count,
    array_length(mj.all_websites, 1) as website_count,
    mj.all_websites
FROM master_jurisdictions mj
WHERE mj.primary_type = 'school_district'
  AND array_length(mj.all_websites, 1) > 1
ORDER BY website_count DESC;

Example 3: Unmatched organizations (need manual review)

SELECT 
    ol.name,
    ol.city,
    ol.state,
    ol.organization_type,
    ol.website
FROM organization_location ol
LEFT JOIN jurisdiction_crosswalk jc ON ol.id = jc.org_location_id
WHERE jc.id IS NULL
  AND ol.organization_type = 'school_district'
ORDER BY ol.state, ol.city;

Example 4: Match quality by state

SELECT 
    mj.state_code,
    COUNT(*) as total_jurisdictions,
    AVG(mj.source_count) as avg_sources,
    AVG(mj.data_completeness_score) as avg_quality
FROM master_jurisdictions mj
GROUP BY mj.state_code
ORDER BY avg_quality DESC;

Benefits

1. Improved Match Rates

• Before: ~10-20% match rate between tables
• After: 80-95% match rate using multi-strategy approach

2. Single Source of Truth

• Canonical names and identifiers
• Deduplicated website URLs
• Consolidated geographic data

3. Data Quality Metrics

• source_count: How many tables contributed data
• data_completeness_score: Quality indicator (0.0-1.0)
• match_confidence: How reliable the match is

4. Flexible Querying

-- Get ALL data for a jurisdiction from ANY source
SELECT * FROM master_jurisdictions WHERE canonical_name = 'Anchorage' AND state_code = 'AK';

-- Reverse lookup: Find master record from any source ID
SELECT mj.* 
FROM master_jurisdictions mj
JOIN jurisdiction_crosswalk jc ON mj.id = jc.master_jurisdiction_id
WHERE jc.org_location_id = 12345;  -- organization_location.id

5. Domain-Based Linking

-- Find all entities sharing same domain (possible duplicates)
SELECT 
    domain,
    COUNT(*) as entity_count,
    array_agg(DISTINCT jurisdiction_name) as names,
    array_agg(DISTINCT state_code) as states
FROM domain_registry
GROUP BY domain
HAVING COUNT(*) > 1;

Maintenance

Regular Updates

Run monthly to catch new data:

# Incremental update (only new records)
python scripts/datasources/master_data/update_master_data.py

# Full refresh
python scripts/datasources/master_data/create_jurisdiction_master.py --full-refresh

Manual Overrides

Create manual mappings for edge cases:

-- Override automatic matching
INSERT INTO jurisdiction_crosswalk 
(org_location_id, details_search_id, match_method, match_confidence)
VALUES (12345, 67890, 'manual_override', 1.0);

Future Enhancements

1. Machine Learning Matching: Train model on manually verified matches
2. Geocoding Integration: Use lat/lon for proximity-based matching
3. Historical Tracking: Track jurisdiction mergers/splits over time
4. API Integration: Expose master data via REST API
5. Data Lineage: Track which source provided each field

File Structure

scripts/datasources/master_data/
├── README.md                          # This file
├── create_jurisdiction_master.py     # Main MDM script
├── update_master_data.py             # Incremental update script (TODO)
└── query_examples.sql                # Common query patterns (TODO)

Support

For questions or issues with master data management:

1. Check the match report output
2. Query jurisdiction_crosswalk to see match methods
3. Review domain_registry for domain extraction issues
4. Check data_completeness_score for quality assessment