VladaOmega
Sign inStart free
Insurer negotiated rates

Commercial gold mart — rate by entity

vlada_curated.mart_rate_by_entity·live·mart·static·Commercial Negotiated Rates (Transparency in Coverage)
Rows
N/A
Columns
16
History
2026-04 (MRF version_id; the only version present) to 2026-04 (single snapshot; loaded_at is a single constant 2026-04-23 18:07:41 UTC for all 140.36M rows)
Card
yesgrain, traps, filters
Typed tools
0

What it is

  1. 1A serving projection of commercial payer negotiated rates (from MRF / machine-readable transparency files) re-keyed by the in-network ENTITY (the named network in the MRF, e.g. the payer's own network or a rented network), for plan/network-level queries like "list all rates under this network in this state." It is a reshuffle of fact_rate — one row per fact_rate row — and is a sibling of mart_rate_by_code (same data, different access key).
  2. 2Currently it contains only payer 'fallon', MRF version 2026-04.

Grain

KeyGrain
rate_id
  1. 1One row per individual negotiated-rate observation, uniquely identified by rate_id alone.
  2. 2Verified: exact COUNT(DISTINCT rate_id) = 140,360,334 = total row_count, so rate_id is a per-row surrogate key.
  3. 3(payer_id, entity_id, version_id, code, code_system, code_prefix, state, negotiated_rate, negotiated_type, billing_class are all attributes of that one rate_id row, not part of the minimal key — the profiler's 7-column key is a SUPERSET.)

How to read it

  1. 1To get the median in-network DOLLAR price for CPT 99213 under Fallon's own network in Massachusetts: SELECT approx_percentile(negotiated_rate, 0.5) FROM vlada_curated.mart_rate_by_entity WHERE payer_id='fallon' AND entity_id='fallon_health' AND state='MA' AND code='99213' AND code_system='CPT' AND negotiated_type='negotiated'.
  2. 2The negotiated_type='negotiated' filter is essential — it excludes percentage-of-charge and per-diem rows that would otherwise pollute the dollar distribution.

Traps

  1. 1rate_id is the TRUE grain (one row each, 140,360,334 distinct = row count). The profiler-reported 7-column key (payer_id, entity_id, version_id, rate_id, code, code_system, code_prefix) is a superset — every column except rate_id is an attribute, so do NOT treat the composite as the key.
  2. 2UNITS OF negotiated_rate are NOT uniform: 11.8% of rows are negotiated_type='percentage' where the value is a PERCENT of billed charges (median ~99), and a few thousand are 'per diem' (per-day dollars). Averaging negotiated_rate across negotiated_type silently corrupts dollar statistics — always filter negotiated_type='negotiated' for true dollar amounts.
  3. 3`state` is provider-NPPES-derived (where the in-network providers are registered), NOT the plan's, member's, or rate-file's geography. The MRF is national; state is an inferred per-rate attribute. Check state_source: ~25% of rows are 'nppes_plurality' (best-guess across multiple states) and ~29K are 'unknown'.
  4. 4modifiers is an EMPTY array for all 140.36M rows here, and quality_flags.is_ghost_candidate is always false / n_similar always 0 — these columns are present but unpopulated in this mart. Do not build logic that expects modifier splits or ghost-candidate flags from this table.
  5. 5Scope is narrow and snapshot-only: ONLY payer 'fallon', ONLY version 2026-04, and fallon_health is 99.1% of rows. loaded_at is one constant timestamp, version_id is single-valued, and there is NO is_current column — history is kept as Iceberg snapshots, not as rows, so this table is a single live snapshot (do not expect time series or dedup-by-is_current).
  6. 6This is a reshuffle of fact_rate (sibling: mart_rate_by_code keyed for code lookups). This entity mart DROPPED the provider/NPI dimension entirely — there is no NPI or provider_count here, only the entity/network and provider-derived state. Join on rate_id to fact_rate or query the resolution engine if you need the providers behind a rate.

Columns (16)

ColumnTypeMeaning
payer_idstringPayer/carrier slug that published the MRF. Single value in the whole table: 'fallon' (Fallon Health). One of the three partition columns.
entity_idstringNormalized network-entity key = lowercase(trim(raw entity)) with internal whitespace collapsed to '_'. This is the named in-network entity from the MRF (the payer's own network or a RENTED network). 4 distinct values: fallon_health (139.16M rows, 99.1%), carelon (1.20M), eyemed_vision_care,_llc (145), fallon (93). Used for entity/network-level pruning.
version_idstringMRF publication version (year-month). Single value: '2026-04'. Partition column. This is the data vintage, not a row-validity range.
entity_displaystringOriginal raw entity label before normalization (e.g. 'Carelon', 'EyeMed Vision Care, LLC'). 1:1 with entity_id here. Use for display.
statestringTwo-letter US state, DERIVED from the in-network providers' NPPES state per rate (NOT the rate's own geography and NOT the patient/plan state). 43 distinct states; heavily MA-skewed (~131.5M of 140.36M rows = Fallon's home state). See state_source for how it was derived.
rate_idstringThe grain. A surrogate hash key (32-hex) unique per row across the whole table (140,360,334 distinct = row count). Carried straight from fact_rate; joins this mart to its sibling (mart_rate_by_code) and to fact_rate.
codestringThe billed procedure/service code (e.g. CPT '99213', HCPCS 'G0247', revenue code, or MS-DRG number). The thing being priced.
code_systemstringCoding system of `code`. Distribution: CPT 119.52M (85.1%), HCPCS 20.77M (14.8%), RC (institutional revenue codes) 72,619, MS-DRG 771. RC and MS-DRG rows are always billing_class='institutional'.
code_prefixstringFirst two characters of `code` (e.g. '93', '99', 'G0', 'Q4'), a coarse category rollup. Fully derivable from `code`; a convenience/pruning column, carries no information beyond `code`.
negotiated_ratedoubleThe negotiated price as a double. UNITS DEPEND ON negotiated_type: for 'negotiated' it is a USD dollar amount (min 0.01, median ~271, max ~99,896); for 'percentage' it is a PERCENT of billed charges (17–256, median ~99), NOT dollars; for 'per diem' it is a USD per-day amount. No nulls and no values <= 0 observed.
negotiated_typestringPricing basis. 'negotiated' = fixed dollar (123.75M, 88.2%), 'percentage' = % of billed charges (16.61M, 11.8%), 'per diem' = daily dollar rate (3,968, mostly revenue-code institutional). Determines how to read negotiated_rate.
billing_classstringMRF billing class: 'institutional' (82.48M, facility billing) or 'professional' (57.88M, clinician billing). Only these two values.
modifiersarray<string>array<string> of code modifiers. EMPTY ([]) for every one of the 140.36M rows in this mart — present in schema but carries no information here (a trap: filtering/grouping on modifiers yields nothing). Note the siblings/fact_rate may populate it; the reference/component_split_modifiers memo treats modifiers as the rate-component splitter, but it is not materialized in THIS table.
quality_flagsstruct<is_outlier:boolean,is_ghost_candidate:boolean,n_similar:bigint>struct<is_outlier boolean, is_ghost_candidate boolean, n_similar bigint>. Only two states occur: clean (is_outlier=false, 140.17M, 99.86%) and is_outlier=true (194,765, 0.14%). is_ghost_candidate is ALWAYS false and n_similar is ALWAYS 0 in this table — those sub-fields are unpopulated here (do not rely on ghost detection from this mart).
loaded_attimestamptimestamp the mart build wrote the row, appended at build time. A single constant for the entire table (2026-04-23 18:07:41.189878 UTC) — it marks the build, not any per-row event time, hence the table reads as a single static snapshot.
state_sourcestringHow `state` was derived per rate from NPPES provider states: 'nppes_single_state' (105.20M, all the rate's NPIs in one state), 'nppes_plurality' (35.13M, >=2 states, picked the most common; ties alphabetical), 'unknown' (29,466, nothing resolved). 'payer_default' is a possible value in the pipeline but is absent here. Added to fact_rate/marts via additive ALTER; reflects provider geography confidence, not rate confidence.

Query it

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

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