# Commercial Insurer MLR — financial facts (CMS MLR PUF)

`insurer_silver.fact_insurer_mlr` · Insurer negotiated rates · status live · 27,254 rows

The 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. Join dim_insurer_mlr_identity on mr_submission_template_id for the NAIC crosswalk + brand. loss_ratio = incurred_claims/premium_earned. The insurer-economics side of the Payers/Investors wedge.

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

| 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: `query_substrate` with `SELECT ... FROM insurer_silver.fact_insurer_mlr WHERE ... LIMIT 100`; read `describe_data_source("insurer_silver.fact_insurer_mlr")` first.