Insurer negotiated rates

Commercial Insurer MLR: current read surface (financials joined to issuer identity)

insurer_silver.vw_insurer_mlr_current·live·semantic_view

Rows

18,818

Columns

History

N/A

Card

nocolumns only

Typed tools

What it is

1The semantic read surface over fact_insurer_mlr: the highest cms_vintage_sequence per (business_year, submission, market), joined to dim_insurer_mlr_identity, with derived loss_ratio, retention, PMPMs, the re-bought (admin, broker, sales, claims adjustment, cost containment, QI) and harvested (taxes, fees) splits and the underwriting-margin residual.
2Defined in ops/sql/vw_insurer_mlr_current.sql.

Grain

Key	Grain
N/A	1One row per (business_year, market, issuer submission) of the current vintage, with carrier group, name, EIN, HIOS id and business state; 2Grand Total rollup rows excluded. 318,818 rows RY2017-2024 (Athena c0257f5f, 2026-10-13).

Columns (42)

Column	Type	Meaning
`business_year`	int
`market`	string
`carrier_group`	string
`carrier_name`	string
`carrier_ein`	string
`hios_issuer_id`	string
`state`	string
`issuer_identity_key`	string
`native_issuer_code`	string
`identity_row_locator`	string
`premium_earned`	double
`incurred_claims`	double
`member_months`	double
`retention`	double
`retention_pct`	double
`loss_ratio`	double
`premium_pmpm`	double
`retention_pmpm`	double
`admin_general`	double
`broker_commissions`	double
`direct_sales_salaries`	double
`claims_adjustment`	double
`cost_containment`	double
`quality_improvement`	double
`rebought_total`	double
`state_premium_tax`	double
`state_income_excise_tax`	double
`federal_income_tax`	double
`other_federal_taxes`	double
`employment_taxes`	double
`regulatory_licenses_fees`	double
`taxes_other_5_5a`	double
`pcori_fee`	double
`taxes_total`	double
`underwriting_margin`	double
`rx_drugs`	double
`pharma_rebates`	double
`cms_vintage_sequence`	int
`asserted_at`	date
`source_url`	string
`sha256_content_hash`	string
`source_row_locators`	string

Query it

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

Item	Value
Typed tools that read it	none; raw SQL
Identity key	`business_year,market,carrier_ein,hios_issuer_id,state`
Join	`how_to_join(source_fqn="insurer_silver.vw_insurer_mlr_current")`
Raw door	Athena (read-only via query_substrate): insurer_silver.vw_insurer_mlr_current
Company names	`carrier_name` resolve to a permanent Vlada ID: `resolve_entity(text=<name>)`; the company pages hold owners and holdings
Note	Registered 2026-10-13 under register w4 cms_insurer_mlr_puf by the Data Tracker registration lane (PR claude/tracker-registration-2026-10-13). Door RAW, World W4.

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