Insurance Hierarchy (1:N:N)

reporting_entities (BCBS Michigan)
     1:N
plans (HMO, PPO, Medicare Advantage)
     1:N
in_network_services (418,652 services)

Service Pricing (1:N:N)

in_network_services (e.g., "MRI Brain - CPT 70543")
     1:N
negotiated_rates (rate containers)
     1:N                     1:N
negotiated_prices      negotiated_rate_provider_groups (34M links!)
    ($ amounts)               N:1
                        provider_groups (34M organizations)

Provider Enrichment (N:1 Lookup)

provider_groups.npi_list [NPI1, NPI2, ...]
     JOIN
nppes_providers.npi (9.2M records)
     COPY TAXONOMY FIELDS
provider_groups:
    • primary_taxonomy_code
    • primary_taxonomy_name
    • primary_specialization
    • primary_classification
    • taxonomy_enriched = true 

• CASCADE DELETE: Deleting a service auto-deletes all its rates, prices, and provider links
• RESTRICT: Cannot delete reporting entities/plans if services exist
• REFERENTIAL INTEGRITY: All relationships enforced at database level

Detailed Foreign Key Behavior

DELETE FROM in_network_services WHERE id = 123
   CASCADE
DELETE FROM negotiated_rates WHERE service_id = 123
   CASCADE                            CASCADE
DELETE FROM negotiated_prices    DELETE FROM negotiated_rate_provider_groups
WHERE negotiated_rate_id IN ...  WHERE negotiated_rate_id IN ...

BCBS Michigan (Reporting Entity)
└── Plan A - PPO Network
    └── Service: MRI Brain (CPT 70543)
        ├── Negotiated Rate #1
        │   ├── Price: $500 (in-network)
        │   └── Provider Group #1
        │       ├── Name: Henry Ford Hospital
        │       ├── Location: Detroit, MI
        │       ├── Taxonomy: 282N00000X (Hospital)
        │       └── Plausibility Score: 85/100 
        └── Negotiated Rate #2
            ├── Price: $750 (in-network)
            └── Provider Group #2
                ├── Name: Michigan Medicine
                ├── Location: Ann Arbor, MI
                ├── Taxonomy: 282N00000X (Hospital)
                └── Plausibility Score: 92/100 

in_network_services (418K records)

provider_groups (34M records!) CRITICAL

negotiated_prices (418K records)

DO: Always Filter by Indexed Columns

SELECT * FROM provider_groups
WHERE state = 'MI'              --  Uses idx_provider_groups_state
  AND city = 'DETROIT'          --  Uses idx_provider_groups_city
  AND taxonomy_enriched = true  --  Uses idx_provider_groups_enriched
LIMIT 1000;                     --  Prevents memory exhaustion

DO: Use CTEs for Complex Queries

WITH filtered_services AS (
  SELECT id FROM in_network_services 
  WHERE billing_code IN ('99212', '70543')
    AND plausibility_score >= 75
)
SELECT pg.tin_value, AVG(np.negotiated_rate_value)
FROM provider_groups pg
  JOIN ... ON ...
  JOIN filtered_services fs ON ...
WHERE pg.state = 'MI'
GROUP BY pg.tin_value
LIMIT 100;

DON'T: Full Table Scans on 34M Records

--  BAD: Will crash with out of memory!
SELECT * FROM provider_groups;

--  BAD: No WHERE filter on 34M rows
SELECT AVG(negotiated_rate_value) 
FROM negotiated_rate_provider_groups;

DON'T: Complex Aggregations Without Filters

--  BAD: GROUP BY on 34M records without WHERE
SELECT provider_group_id, COUNT(*), AVG(rate)
FROM negotiated_rate_provider_groups
GROUP BY provider_group_id;  -- Database runs out of memory!

DO: Use EXISTS Instead of IN

--  GOOD: EXISTS stops at first match (faster)
SELECT s.billing_code, s.name
FROM in_network_services s
WHERE EXISTS (
  SELECT 1 FROM negotiated_rate_provider_groups nrpg
    JOIN provider_groups pg ON nrpg.provider_group_id = pg.id
  WHERE nrpg.negotiated_rate_id = (
    SELECT id FROM negotiated_rates WHERE service_id = s.id
  )
  AND pg.state = 'MI'
  AND pg.city = 'Detroit'
);

DON'T: Use IN with Large Subqueries

--  BAD: IN evaluates full subquery (slower)
SELECT s.billing_code, s.name
FROM in_network_services s
WHERE s.id IN (
  SELECT service_id FROM negotiated_rates nr
    JOIN negotiated_rate_provider_groups nrpg ON nr.id = nrpg.negotiated_rate_id
    JOIN provider_groups pg ON nrpg.provider_group_id = pg.id
  WHERE pg.state = 'MI'  -- Still processes all 34M rows!
);

DO: Use Proper Index Hints

--  GOOD: Sargable query (uses index)
SELECT * FROM provider_groups
WHERE primary_taxonomy_code = '207R00000X'  -- Exact match
  AND state = 'MI';

--  GOOD: Prefix LIKE (uses index)
SELECT * FROM provider_groups
WHERE primary_taxonomy_code LIKE '282%'  -- Hospital codes
  AND city = 'Detroit';

DON'T: Use Non-Sargable Queries

--  BAD: Leading wildcard can't use index
SELECT * FROM provider_groups
WHERE primary_taxonomy_code LIKE '%00000X';  -- Full scan!

--  BAD: Function on indexed column
SELECT * FROM provider_groups
WHERE LOWER(state) = 'mi';  -- Index not used!

--  GOOD: Store data in consistent case
SELECT * FROM provider_groups
WHERE state = 'MI';  -- Uses index

Advanced: Materialized Views

-- Create materialized view (refreshed periodically)
CREATE MATERIALIZED VIEW provider_state_summary AS
SELECT 
  state,
  COUNT(*) as provider_count,
  COUNT(*) FILTER (WHERE taxonomy_enriched = true) as enriched_count,
  COUNT(DISTINCT primary_taxonomy_code) as unique_specialties
FROM provider_groups
GROUP BY state;

CREATE INDEX ON provider_state_summary(state);

-- Query is instant (no 34M row scan!)
SELECT * FROM provider_state_summary WHERE state = 'MI';

Golden Rules for 34M Record Queries

• Always use WHERE clauses with indexed columns (state, city, taxonomy_code)
• Always use LIMIT to cap result sets
• Filter early with CTEs before complex JOINs
• Use EXISTS instead of IN for subqueries (stops at first match)
• Avoid LIKE '%text' (leading wildcards can't use indexes)
• Don't use functions on indexed columns in WHERE clauses
• Consider materialized views for repeated complex aggregations
• Test with EXPLAIN ANALYZE to verify index usage

Plausibility Score (0-100)

Validates whether provider specialty matches service type

1 Find Cheapest Provider for Service

SELECT 
  COALESCE(npp.provider_organization_name, npp.provider_name) as provider,
  pg.city, pg.state,
  pg.primary_specialization,
  np.negotiated_rate_value as rate,
  ins.plausibility_score
FROM in_network_services ins
  JOIN negotiated_rates nr ON ins.id = nr.service_id
  JOIN negotiated_prices np ON nr.id = np.negotiated_rate_id
  JOIN negotiated_rate_provider_groups nrpg ON nr.id = nrpg.negotiated_rate_id
  JOIN provider_groups pg ON nrpg.provider_group_id = pg.id
  LEFT JOIN nppes_providers npp ON SPLIT_PART(pg.npi_list, ',', 1) = npp.npi
WHERE ins.billing_code = '70543'  -- MRI Brain
  AND pg.state = 'MI'
  AND ins.plausibility_score >= 75  -- Only quality matches
ORDER BY np.negotiated_rate_value ASC
LIMIT 10;

2 Provider Cost Efficiency Score

WITH market_avg AS (
  SELECT AVG(np.negotiated_rate_value) as avg_rate
  FROM in_network_services ins
    JOIN negotiated_rates nr ON ins.id = nr.service_id
    JOIN negotiated_prices np ON nr.id = np.negotiated_rate_id
  WHERE ins.billing_code = '99212'
)
SELECT 
  pg.tin_value,
  COALESCE(npp.provider_organization_name, 'Unknown') as provider_name,
  pg.city,
  AVG(np.negotiated_rate_value) as provider_avg,
  ma.avg_rate as market_avg,
  ROUND((AVG(np.negotiated_rate_value) - ma.avg_rate) / ma.avg_rate * 100, 1) as pct_vs_market
FROM provider_groups pg
  LEFT JOIN nppes_providers npp ON SPLIT_PART(pg.npi_list, ',', 1) = npp.npi
  JOIN negotiated_rate_provider_groups nrpg ON pg.id = nrpg.provider_group_id
  JOIN negotiated_rates nr ON nrpg.negotiated_rate_id = nr.id
  JOIN negotiated_prices np ON nr.id = np.negotiated_rate_id
  JOIN in_network_services ins ON nr.service_id = ins.id
  CROSS JOIN market_avg ma
WHERE ins.billing_code = '99212'
  AND pg.state = 'MI'
GROUP BY pg.tin_value, npp.provider_organization_name, pg.city, ma.avg_rate
ORDER BY pct_vs_market ASC
LIMIT 20;

3 Geographic Price Variation

SELECT 
  pg.city,
  pg.state,
  COUNT(DISTINCT pg.id) as provider_count,
  MIN(np.negotiated_rate_value) as min_rate,
  AVG(np.negotiated_rate_value) as avg_rate,
  MAX(np.negotiated_rate_value) as max_rate,
  MAX(np.negotiated_rate_value) - MIN(np.negotiated_rate_value) as price_spread
FROM in_network_services ins
  JOIN negotiated_rates nr ON ins.id = nr.service_id
  JOIN negotiated_prices np ON nr.id = np.negotiated_rate_id
  JOIN negotiated_rate_provider_groups nrpg ON nr.id = nrpg.negotiated_rate_id
  JOIN provider_groups pg ON nrpg.provider_group_id = pg.id
WHERE ins.billing_code = '70543'
  AND pg.state = 'MI'
  AND pg.city IS NOT NULL
GROUP BY pg.city, pg.state
HAVING COUNT(DISTINCT pg.id) >= 3
ORDER BY price_spread DESC;

Why Junction Table?

negotiated_rates.provider_list = "[group1, group2, ...]"

negotiated_rate_provider_groups:
  rate_id | provider_group_id
  --------|------------------
  1       | 12345
  1       | 67890

Why Separate negotiated_prices?

Why Denormalize Taxonomy?

provider_groups.taxonomy_id  taxonomy_reference_table.id

provider_groups:
  • primary_taxonomy_code
  • primary_taxonomy_name
  • primary_specialization
  • primary_classification

Design Philosophy

All design decisions optimize for read performance and query simplicity in an analytics workload. With 34M+ provider records, every decision prioritizes fast filtering and aggregation over storage efficiency.

Performance Implications

• provider_groups: REQUIRES indexed WHERE clauses or database runs out of memory
• Junction table: Every provider price query touches this - indexes are critical
• NPPES providers: NPI lookups must use primary key index
• Total records: Full table scans are impossible - always filter first

enrichment_log Table

CREATE TABLE enrichment_log (
    id SERIAL PRIMARY KEY,
    batch_id VARCHAR(100) UNIQUE NOT NULL,
    records_processed INTEGER,
    records_enriched INTEGER,
    records_failed INTEGER,
    status VARCHAR(50),
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP,
    error_message TEXT
);

Provider Enrichment Flags

SELECT 
    COUNT(*) AS total_providers,
    COUNT(*) FILTER (WHERE taxonomy_enriched = true) AS enriched,
    COUNT(*) FILTER (WHERE taxonomy_enriched = false) AS pending,
    ROUND(100.0 * COUNT(*) FILTER (WHERE taxonomy_enriched = true) / COUNT(*), 2) AS pct_enriched
FROM provider_groups;

Enrichment Process Flow

1. LOAD NPPES DATA
   ├─ Download 10.29GB NPPES CSV
   ├─ Parse 9,236,343 provider records
   └─ Load into nppes_providers table

2. MATCH NPIs
   ├─ Extract NPI list from provider_groups.npi_list (JSON array)
   ├─ JOIN nppes_providers ON npi
   └─ Match primary NPI (first in array)

3. COPY TAXONOMY FIELDS
   ├─ primary_taxonomy_code (e.g., "207R00000X")
   ├─ primary_taxonomy_name (e.g., "Internal Medicine")
   ├─ primary_specialization (e.g., "Internal Medicine")
   ├─ primary_classification (e.g., "Allopathic & Osteopathic Physicians")
   └─ Set taxonomy_enriched = true

4. LOG RESULTS
   └─ Insert batch record into enrichment_log

Current Enrichment Status

Hospital-Specific Optimizations

-- Partial index for hospitals only
CREATE INDEX idx_provider_groups_hospital_taxonomy 
ON provider_groups(primary_taxonomy_code) 
WHERE primary_taxonomy_code LIKE '282%';

-- Fast hospital-only queries
SELECT * FROM provider_groups 
WHERE primary_taxonomy_code LIKE '282%' 
  AND city = 'Detroit'
  AND state = 'MI';

Geographic Data Structure

-- Find all providers in Detroit
SELECT * FROM provider_groups WHERE city = 'Detroit';

-- Find all Michigan hospitals
SELECT * FROM provider_groups 
WHERE state = 'MI' 
  AND primary_taxonomy_code LIKE '282%';

-- Find providers in specific zip code
SELECT * FROM provider_groups WHERE zip_code = '48201';

Field Design Rationale

• Partial Hospital Index: Optimizes for high-value queries without bloating main indexes
• Denormalized Geography: Faster queries than normalizing to separate location table
• Multiple Zip Formats: Supports both 5-digit and ZIP+4 formats
• City/State Indexes: Enable fast regional provider searches

Evolution Metrics

                                    HEALTHCARE TRANSPARENCY DATABASE
                                                    │
                        ┌───────────────────────┼───────────────────────┐
                        │                       │                       │
                ┌─── INSURANCE ────┐  ┌──── SERVICES ────┐  ┌──── PROVIDERS ────┐
                │                  │  │                  │  │                   │
                │ reporting_       │  │ in_network_      │  │ provider_groups   │
                │   entities (1)   │  │   services       │  │   (34M records!)  │
                │        │         │  │   (418K)         │  │        │          │
                │                 │  │        │         │  │        │          │
                │   plans (~50)    │  │        │         │  │  ┌─────┴─────┐   │
                └──────────────────┘  │                 │  │  │           │   │
                                      │ negotiated_      │  │                │
                                      │   rates          │  │ Enriched   npi   │
                                      │   (418K)         │  │ Taxonomy  _list  │
                                      │        │         │  │  Fields   (JSON) │
                                      │        ├─────┐   │  │  │           │   │
                                      │                │  │                │
                                      │ negotiated  rate │  │ primary_  nppes_ │
                                      │   _prices  _pvdr │  │ taxonomy  pvdrs  │
                                      │   (418K)  _grps  │  │ _code    (9.2M)  │
                                      │           (34M!) │  │                  │
                                      └──────────────────┘  └──────────────────┘
                                                    │
                        ┌───────────────────────┼───────────────────────┐
                        │                       │                       │
                ┌─── ENRICHMENT ───┐  ┌──── QUALITY ─────┐  ┌───── SEARCH ─────┐
                │                  │  │                  │  │                  │
                │ enrichment_log   │  │ plausibility_    │  │ Elasticsearch    │
                │ • batch_id       │  │   score (0-100)  │  │ • 3,869 codes    │
                │ • records_       │  │ • flags (JSON)   │  │ • Fuzzy search   │
                │   processed      │  │ • validated_at   │  │ • Full-text      │
                │ • records_       │  │                  │  │                  │
                │   enriched       │  │ Distribution:    │  │ Hybrid Query:    │
                │ • status         │  │ • 37.54% Good    │  │ 1. ES discovery  │
                │                  │  │ • 22.64% Fair    │  │ 2. PG analytics  │
                │ NPPES Pipeline:  │  │ • 39.58% Quest.  │  │ 3. Combine       │
                │ └─ Match NPI    │  │                  │  │                  │
                │ └─ Copy Tax.    │  │ Taxonomy Logic:  │  │ GIN Index:       │
                │ └─ Set Flag     │  │ CPT  Taxonomy   │  │ description_gin  │
                └──────────────────┘  └──────────────────┘  └──────────────────┘
                                                    │
                        ┌───────────────────────┼───────────────────────┐
                        │                       │                       │
                ┌──── INDEXES ─────┐  ┌─── GEOGRAPHY ────┐  ┌──── ANALYTICS ───┐
                │                  │  │                  │  │                  │
                │ 50+ total        │  │ city, state, zip │  │ Price Comp.:     │
                │                  │  │ indexes          │  │ • Geographic     │
                │ Critical:        │  │                  │  │ • Provider       │
                │ • tin_value      │  │ Regional Search: │  │ • Specialty      │
                │ • taxonomy_code  │  │ └─ Fast city    │  │                  │
                │ • billing_code   │  │     queries      │  │ Quality Filter:  │
                │ • plausibility   │  │ └─ State agg.   │  │ • Score ranges   │
                │                  │  │ └─ Zip lookup   │  │ • Flag analysis  │
                │ Partial:         │  │                  │  │                  │
                │ • hospital_tax.  │  │ Hospital-spec:   │  │ Cost Analysis:   │
                │   (282% only)    │  │ └─ 282% codes   │  │ • Min/max/avg    │
                │                  │  │     + city       │  │ • Percentiles    │
                └──────────────────┘  └──────────────────┘  └──────────────────┘

KEY DESIGN PRINCIPLES:
├─ Scale First: Every decision optimized for 34M+ provider queries
├─ Denormalize: Taxonomy fields copied for performance (no JOIN needed)
├─ Index Everything: 50+ indexes enable sub-second queries on massive tables
├─ Junction Table: Many-to-many relationships properly normalized
├─ Quality Scores: Built-in validation for service-provider appropriateness
└─ Hybrid Search: Elasticsearch for discovery, PostgreSQL for analytics
                            

Architecture Highlights

Basic Commands

State Filter

Filter data by provider state using NPPES enrichment. Only providers in the specified state(s) will be loaded.

Billing Code Filter

Filter data by billing codes to load only specific procedures or services. Supports exact matching on billing_code and/or billing_code_type.

Combined Filters

Filters can be combined for precise data loading. Example: load only specific CPT codes for Michigan providers.

Performance Options

Storage Optimization

Reduce disk usage when processing large files. The --stream-gz flag reads compressed files directly without decompressing to disk (saves 10x storage). The --delete-after-process flag removes files after loading.

Batch Download & Process Mode (Recommended)

The most efficient way to load large datasets. Downloads N files at a time, processes them with ETL, deletes them, then downloads the next batch. Minimizes disk usage by never having all files on disk at once.

Post-ETL Steps

After loading data, run these commands to complete the pipeline:

CLI Arguments Quick Reference

Batch Download Mode Arguments

These arguments are for the download_and_process.py script with --batch-mode: