Insurer negotiated rates

Exchange Plan Profile — composed ACA marketplace plan brain

medicare_silver.fact_exchange_plan_profile·live·silver

Rows

60,591

Columns

History

N/A

Card

nocolumns only

Typed tools

Superseded by medicare_silver.fact_exchange_plan_profile_v2 (card cms_exchange_plan_profile_v2, built 2026-10-13 by tools/data_tracker/build_exchange_plan_profile.py, #3579): the same 35 columns and 60,591 rows, with the Rate input taken from the CMS ZIP history (vw_rate_puf_current). This table stays as the rollback..

What it is

1The composed "plan brain" for the ACA marketplace: one row per plan-year carrying its full vector — attributes (metal/type/on-off-exchange/issuer/TIN) from Plan Attributes, premium benchmarks (age 21/40/60 x rating area) from Rate, network name from Network, county footprint from Service Area, covered-benefit counts + headline cost-shares (PCP/specialist/ER/urgent/generic/inpatient) from Benefits & Cost Sharing, CMS star ratings from Marketplace Quality, and the issuer's individual-market financials (premium earned + MLR ratio) from the CMS MLR PUF.
2Joins measured/verified (Plan Attributes->Rate 100%; per-plan end-to-end proven); materialized so plan lookups serve fast (no parse-in-view cliff).

Grain

Key	Grain
N/A	1One row per (business_year, plan_id). 260,591 plan-years PY2014-2026. 3Materialized composition of six verified exchange PUF sources at plan grain (zero fanout).

Columns (35)

Column	Type	Meaning
`plan_id`	string
`issuer_id`	string
`state_code`	string
`plan_marketing_name`	string
`metal_level`	string
`plan_type`	string
`market_coverage`	string
`on_off_exchange`	string
`issuer_tin`	string
`network_id`	string
`network_name`	string
`service_area_id`	string
`covers_entire_state`	boolean
`n_counties`	bigint
`premium_age21`	double
`premium_age40`	double
`premium_age60`	double
`premium_age40_max`	double
`n_rating_areas`	bigint
`n_benefits_covered`	bigint
`n_ehb_benefits`	bigint
`pcp_copay`	string
`specialist_copay`	string
`er_copay`	string
`urgent_care_copay`	string
`generic_drug_copay`	string
`inpatient_coins`	string
`quality_overall`	int
`quality_medical`	int
`quality_member_exp`	int
`issuer_company_name`	string
`issuer_naic_group_code`	string
`issuer_ind_premium_earned`	double
`issuer_ind_mlr_ratio`	double
`business_year`	int

Query it

describe_data_source(source_fqn="medicare_silver.fact_exchange_plan_profile")     # the card, live
query_substrate(sql="SELECT * FROM medicare_silver.fact_exchange_plan_profile WHERE <filter> LIMIT 100")

Item	Value
Typed tools that read it	none; raw SQL
Identity key	`business_year,plan_id`
Join	`how_to_join(source_fqn="medicare_silver.fact_exchange_plan_profile")`
Raw door	Athena (read-only via query_substrate): medicare_silver.fact_exchange_plan_profile
Company names	`issuer_company_name` resolve to a permanent Vlada ID: `resolve_entity(text=<name>)`; the company pages hold owners and holdings

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