Insurer negotiated rates

CMS CCIIO Rate PUF — ACA Marketplace QHP plan premium rates

medicare_silver.fact_rate_puf·live·silver·Individual Exchange

Rows

32,902,283

Columns

History

N/A

Card

nocolumns only

Typed tools

Superseded by medicare_semantic.vw_rate_puf_current over medicare_silver.fact_rate_puf_history (cards cms_rate_puf_current and cms_rate_puf_history, #3568/#3576): every plan year read from the official CMS ZIPs, one revision per business_year. This table stays queryable as the rollback..

What it is

1The CMS CCIIO Rate PUF (data.healthcare.gov) — the actual filed monthly premium for every ACA Marketplace QHP by issuer × plan × rating area × age × tobacco status: individual rate, individual-tobacco rate, and family-tier rates (couple, +1/+2/+3 dependents).
2A commercial PRICE signal (plan premium geography by issuer/area/age) independent of TiC negotiated rates + hospital chargemaster — the premium side of the commercial market. ~2-3M rows/year, PY2014-2026, partition by business_year.

Grain

Key	Grain
N/A	1One row per (business_year, issuer × plan × rating-area × age × tobacco) — individual + family premiums.

Columns (35)

Column	Type	Meaning
`business_year`	int
`row_seq`	bigint
`state_code`	string
`issuer_id`	string
`source_name`	string
`rate_effective_date`	string
`rate_expiration_date`	string
`plan_id`	string
`rating_area_id`	string
`tobacco`	string
`age`	string
`individual_rate`	double
`individual_tobacco_rate`	double
`couple`	double
`primary_plus_one_dependent`	double
`primary_plus_two_dependents`	double
`primary_plus_three_or_more_dependents`	double
`couple_plus_one_dependent`	double
`couple_plus_two_dependents`	double
`couple_plus_three_or_more_dependents`	double
`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

Query it

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

Item	Value
Typed tools that read it	none; raw SQL
Identity key	`issuer_id`
Join	`how_to_join(source_fqn="medicare_silver.fact_rate_puf")`
Raw door	Athena (read-only via query_substrate): medicare_silver.fact_rate_puf
Note	Iceberg medicare_silver.fact_rate_puf, partition by business_year; bulk-load. row_seq keys each row.

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