Insurer negotiated rates

Commercial Insurer MLR — financial facts (CMS MLR PUF)

insurer_silver.fact_insurer_mlr·live·silver

Rows

27,254

Columns

History

N/A

Card

nocolumns only

Typed tools

What it is

1The commercial insurer P&L: filed premium earned, incurred claims, and member-months per issuer submission x market (individual/small_group/large_group/...), from the CMS Medical Loss Ratio PUF.
2Join dim_insurer_mlr_identity on mr_submission_template_id for the NAIC crosswalk + brand. loss_ratio = incurred_claims/premium_earned.
3The insurer-economics side of the Payers/Investors wedge.

Grain

Key	Grain
N/A	1One row per (business_year, mr_submission_template_id, market). 2Wide pivot of 19 MLR lines (premium earned, incurred claims, member months, QI, taxes, fees ...) per issuer submission x market. 327,254 rows across RY2017-2024 (Athena 51461b5b, 2026-10-13).

Columns (39)

Column	Type	Meaning
`business_year`	int
`mr_submission_template_id`	string
`market`	string
`premium_earned`	double
`incurred_claims`	double
`member_months`	double
`source_sha`	string
`observed_period`	date
`asserted_at`	date
`effective_period_start`	date
`effective_period_end`	date
`cms_vintage_sequence`	int
`parse_revision`	int
`parser_artifact_hash`	string
`source_url`	string
`sha256_content_hash`	string
`source_id`	string
`is_current`	boolean
`bronze_object_key`	string
`s3_version_id`	string
`vintage_tag`	string
`drug_source_version_id`	string
`rx_drugs`	double
`pharma_rebates`	double
`admin_general`	double
`broker_commissions`	double
`direct_sales_salaries`	double
`claims_adjustment`	double
`cost_containment`	double
`quality_improvement`	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
`pcori_fee`	double
`taxes_other_5_5a`	double
`source_row_locators`	string

Query it

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

Item	Value
Typed tools that read it	none; raw SQL
Identity key	`business_year,mr_submission_template_id,market`
Join	`how_to_join(source_fqn="insurer_silver.fact_insurer_mlr")`
Raw door	Athena (read-only via query_substrate): insurer_silver.fact_insurer_mlr
Note	Registered 2026-10-13 under register w4 cms_insurer_mlr_puf with a World (W4) by the Data Tracker registration lane (PR claude/tracker-registration-2026-10-13); until then the table sat in the provenance index as internal (NO_COVERAGE at the customer door). RY2024 cell reconciliation PASS, 1,736 cells, 0 mismatches (reconcile_mlr_cells.py).

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