# Commercial rate ↔ provider (NPI) edge

`vlada_curated.rate_provider_edge` · Insurer negotiated rates · status live

The normalized rate↔provider edge of the commercial substrate: rate_id → NPI (bucketed), payer, state, version, code, negotiated_rate/type. The join that attaches TiC negotiated rates to specific providers (NPI lookups).

**Grain.** One row per (rate_id, npi) = one (negotiated-rate-record x provider) edge. Verified: the builder dedups on exactly GROUP BY rate_id, npi (and a seen-set keyed on (rate_id,npi)); and on two independent partition slices the row count equals the distinct (rate_id||npi) count (fallon/VT/npi_bucket=7: 55,926 rows = 55,926 distinct pairs, while distinct rate_id alone = 55,867 < rows; fallon/WY/npi_bucket=20: 59 rows = 59 distinct pairs). Whole table = 2,430,077,970 rows, all payer_id='fallon', version_id='2026-04'. A single rate_id fans out to many NPIs (so rate_id alone is NOT the key); within any rate_id, code/code_system/negotiated_rate/negotiated_type are constant (max 1 distinct each) — only npi (and its derived npi_bucket/state/state_source) vary.

**How to use it.** Get the negotiated dollar prices a specific provider carries for a code from this payer, staying inside scan limits by pruning on the npi_bucket partition. Example: SELECT npi, code, negotiated_rate, state FROM vlada_curated.rate_provider_edge WHERE payer_id='fallon' AND npi_bucket = (conv(substr(sha256(to_utf8('1558096297')),1,8),16,10) % 64) AND npi='1558096297' AND code='99213' AND negotiated_type='negotiated'; — returns Fallon's per-NPI negotiated rate(s) for CPT 99213 (e.g. ~$95). For aggregate per-provider serving queries prefer the resolution engine (idx_rate_lookup + fact_rate).

**Traps.**
- DEPRECATED ARCHITECTURE: this is the LEGACY full rate-by-NPI materialization. The locked canonical architecture (2026-04-29 graph-resolution-engine ADR; v8.1 two-payer proof 2026-05-01) explicitly forbids 'payer-wide rate x NPI edge tables' — they were judged infeasible (~394B estimated rows at payer scale) and replaced by scope-filtered edges + the canonical provider-group primitives graph. build_rate_provider_edge.py is documented as 'legacy, deprecated, not invoked by any active pipeline.' This 2.43B-row table is a FROZEN Fallon-only proof snapshot, not a live/growing source (acceptance gate = CANDIDATE, not CERTIFIED). Do not assume it covers other payers or will refresh.
- CATALOG MISLABELS THE GRAIN: catalog_glue_discovered.json auto-profiles this as grain='one row per npi (auto-profiled)' with identity_key='npi'. That is WRONG — npi has only ~90K distinct values vs 2.43B rows (off by ~27,000x). The real grain is (rate_id, npi). rate_id alone is also NOT unique (it fans out to many NPIs). Trust the verified (rate_id, npi) grain, not the catalog.
- rate_id is NOT a working foreign key to fact_rate. Joining edge.rate_id -> fact_rate.rate_id returns almost nothing (0/10 sampled, 59 of 55,867 in a slice) because the edge's rate_ids come from the deprecated Spark edge_staging id generation, divergent from the current fact_rate build. Do not enrich this table by joining to fact_rate on rate_id; the hot rate attributes (code, rate, type) are already denormalized onto every edge row anyway.
- negotiated_rate UNITS are not uniform: you MUST filter/branch on negotiated_type. type='percentage' rows store a percent (17-255, can exceed 100), not dollars; mixing them into a dollar AVG/median silently contaminates the result. For dollar price analytics use negotiated_type='negotiated' (and beware real $0.01 penny rates and component-split low values).
- state is NULLABLE even though it is a partition key, and ~0.07% of rows have state_source='unknown' with NULL/ambiguous state. WHERE state='XX' silently drops these edges. Also `state` is the PROVIDER's NPPES state, NOT the payer's market — Fallon edges legitimately span all 54 states.
- SCALE TRAP: at 2.43B rows, full-table count(distinct ...) and unfiltered SELECT * both blow the vlada-mcp-readonly 10GB scan cap / 90s timeout (observed: the profiler's SELECT * and a full count(distinct npi) both failed). ALWAYS prune by the partition keys payer_id + state + npi_bucket; the profiler's empty 'sample' and null grain-test cells are timeout artifacts, not real data.

| column | type | meaning |
|---|---|---|
| rate_id | string | 32-hex-char hash identifying ONE negotiated-rate record (a payer's price for one code/type). Half of the grain key. Fans out to many NPIs. CAVEAT: does NOT join to the current fact_rate Iceberg table — probed edge rate_ids returned 0/10 and 59/55,867 matches because this edge's rate_ids were generated by the (now-deprecated) Spark edge_staging path, a different id generation than today's fact_rate. Treat as an internal dedup key, not a live FK. |
| npi | string | 10-digit National Provider Identifier of the provider attached to this rate (the other half of the grain key). ~90,115 distinct NPIs across the table (approx_distinct; exact count(distinct) over 2.4B rows times out at the 10GB workgroup cap). |
| npi_bucket | int | Integer 0-63 = conv(sha256(npi)[0:8 hex],16,10) % 64. A deterministic hash bucket of npi used as a physical PARTITION key (cross-engine-stable: DuckDB and Spark produce identical buckets). Not business data; purely for parallelism/partition pruning. Always query with a bucket filter to stay under scan limits. |
| payer_id | string | Payer slug. Partition key. CONSTANT 'fallon' for the entire table today (single-payer proof). |
| state | string | Two-letter state code, PARTITION key. This is the PROVIDER's state, derived from NPPES (not the payer's geography). 54 distinct values observed (50 states + DC + a few territories from the 56-code VALID_US_STATES set). Fallon (a Massachusetts payer) spans all 54 because its MRF rents national ancillary networks (e.g. Eyemed); MA dominates (e.g. 31.4M edges in just npi_bucket=7 MA) with a long national tail. NULLABLE despite being a partition key — NULL-state rows exist (130 in one bucket alone). |
| version_id | string | MRF data version, format 'YYYY-MM'. CONSTANT '2026-04' for the whole table. This is the real time dimension (not loaded_at). |
| code | string | The procedure/service code the rate is for (e.g. CPT '99213', HCPCS 'J0873', a revenue code, or an MS-DRG). Functionally determined by rate_id (constant within a rate_id). |
| code_system | string | Code system for `code`. Observed values: CPT (dominant), HCPCS, RC (revenue codes), MS-DRG. Functionally determined by rate_id. |
| negotiated_rate | double | The negotiated price. UNITS DEPEND ON negotiated_type: for type='negotiated' it is a dollar amount (CPT median ~$305, min $0.01 penny rates are real); for type='percentage' it is a PERCENT (range 17-255, can exceed 100), NOT dollars; for 'per diem' a daily dollar amount. Functionally determined by rate_id. |
| negotiated_type | string | Pricing basis. Observed: 'negotiated' (~84%, dollar), 'percentage' (~16%, percent-of-billed), 'per diem' (rare). Determines how to interpret negotiated_rate. Functionally determined by rate_id. |
| source_file | string | Filename of the originating MRF gzip (e.g. '20260323_Eyemed_Fallon_in-network-rates.json.gz'). Provenance. Set to min(source_file) when an edge appears in multiple files, so it is the alphabetically-first source, not necessarily the only one. |
| loaded_at | timestamp | Build timestamp, IDENTICAL (2026-04-23 17:49:55.901632 UTC) for every row — it is the table build time, not a record observation date and not a usable time dimension. No is_current / snapshot semantics; the table is a single overwrite. |
| state_source | string | How `state` was derived (provenance/quality of the state attribution). Enum of 6 possible values (nppes_single_state, nppes_plurality, source_file_default, network_level_ambiguous, payer_default, unknown), but only TWO appear in this Fallon data: nppes_single_state (2,428,342,135 rows = 99.93%) and unknown (1,735,835 = 0.07%). 'unknown' marks providers whose NPI did not resolve to a clean NPPES state (correlates with NULL/ambiguous state). Added by the Spark builder via schema evolution — the legacy DuckDB builder left state NULL with no state_source. |

Query it: `query_substrate` with `SELECT ... FROM vlada_curated.rate_provider_edge WHERE ... LIMIT 100`; read `describe_data_source("vlada_curated.rate_provider_edge")` first.