VladaOmega
Sign inStart free
Insurer negotiated rates

Commercial rate ↔ provider (NPI) edge

vlada_curated.rate_provider_edge·live·curated·static·Commercial Negotiated Rates (Transparency in Coverage)
Rows
N/A
Columns
13
History
static — single snapshot, loaded_at = 2026-04-23 17:49:55 UTC for every row; the data version is version_id='2026-04' (Fallon April 2026 MRF). No historical versions, no is_current column. to 2026-04-23 (loaded_at); data vintage version_id=2026-04
Card
yesgrain, traps, filters
Typed tools
0

What it is

  1. 1A massive rate-to-provider EDGE (bridge) table for US commercial healthcare prices: each row pairs one negotiated-rate record (rate_id) with one provider (NPI) that the payer's machine-readable file (MRF) attaches to that rate.
  2. 2It exists to answer "which individual providers carry this negotiated price?" by exploding payer rate rows along their provider lists.
  3. 3NOTE: it is currently a single-payer (Fallon) frozen proof artifact built 2026-04-23, and it is the LEGACY full rate-by-NPI materialization that the locked architecture has since deprecated in favor of scope-filtered edges + the canonical provider-group graph.

Grain

KeyGrain
rate_idnpi
  1. 1One row per (rate_id, npi) = one (negotiated-rate-record x provider) edge.
  2. 2Verified: 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).
  3. 3Whole table = 2,430,077,970 rows, all payer_id='fallon', version_id='2026-04'.
  4. 4A 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 read it

  1. 1Get 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.
  2. 2Example: 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).
  3. 3For aggregate per-provider serving queries prefer the resolution engine (idx_rate_lookup + fact_rate).

Traps

  1. 1DEPRECATED 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.
  2. 2CATALOG 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.
  3. 3rate_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.
  4. 4negotiated_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).
  5. 5state 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.
  6. 6SCALE 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.

Columns (13)

ColumnTypeMeaning
rate_idstring32-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.
npistring10-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_bucketintInteger 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_idstringPayer slug. Partition key. CONSTANT 'fallon' for the entire table today (single-payer proof).
statestringTwo-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_idstringMRF data version, format 'YYYY-MM'. CONSTANT '2026-04' for the whole table. This is the real time dimension (not loaded_at).
codestringThe 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_systemstringCode system for `code`. Observed values: CPT (dominant), HCPCS, RC (revenue codes), MS-DRG. Functionally determined by rate_id.
negotiated_ratedoubleThe 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_typestringPricing 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_filestringFilename 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_attimestampBuild 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_sourcestringHow `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

describe_data_source(source_fqn="vlada_curated.rate_provider_edge")     # the card, live
query_substrate(sql="SELECT * FROM vlada_curated.rate_provider_edge WHERE rate_id = '<value>' AND npi = '<value>' LIMIT 100")
ItemValue
Typed tools that read itnone; raw SQL
Identity keyrate_id
Joinhow_to_join(source_fqn="vlada_curated.rate_provider_edge")
Raw doorAthena (read-only via query_substrate): vlada_curated.rate_provider_edge

This page as markdown: /docs-md/data/vlada_curated_rate_provider_edge.md · every page in one file: /llms-full.txt