Insurer negotiated rates

HHS risk adjustment transfers by issuer (summary report appendices C and D), BY2021-BY2025

medicare_silver.fact_aca_risk_adjustment_transfer_history·partial·silver

Rows

17,322

Columns

History

N/A

Card

nocolumns only

Typed tools

What it is

1The issuer tables of CMS's Summary Report on Individual and Small Group Market Risk Adjustment Transfers, published each June 30 for the prior benefit year: Appendix C (non-merged markets) and Appendix D (merged markets, MA and ME), long form, one row per issuer and amount column. program_component is state_transfer (per state market risk pool: individual non-catastrophic, individual catastrophic, small group, merged) or high_cost_risk_pool; amount_usd is signed; amount_status keeps the publisher dash as no_enrollment_in_pool.

Grain

Key	Grain
N/A	1One amount cell per (benefit_year, sha256_content_hash, source_appendix, sheet_name, source_row_number, source_column_number), i.e. one issuer x state x amount column of one appendix file. 2Measured 2026-09-25, 17,322 rows = 17,322 keys (Athena 24f71ef5).

Columns (43)

Column	Type	Meaning
`benefit_year`	int
`source_appendix`	string
`sheet_name`	string
`source_row_number`	bigint
`source_column_number`	int
`column_header_raw`	string
`hios_issuer_id`	string
`issuer_name`	string
`state_code`	string
`program_component`	string
`market_risk_pool`	string
`amount_usd`	double
`amount_raw`	string
`amount_status`	string
`ordered_header_json`	string
`header_sha256`	string
`column_count`	int
`raw_values_json`	string
`raw_row_sha256`	string
`artifact_id`	string
`source_file_bytes`	bigint
`final_source_url`	string
`expected_artifact_rows`	bigint
`publisher_modified_at`	string
`publisher_etag`	string
`report_title`	string
`report_url`	string
`report_revision_label`	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

Query it

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

Item	Value
Typed tools that read it	none; raw SQL
Identity key	`benefit_year,sha256_content_hash,source_appendix,sheet_name,source_row_number,source_column_number`
Join	`how_to_join(source_fqn="medicare_silver.fact_aca_risk_adjustment_transfer_history")`
Raw door	Athena (read-only via query_substrate): medicare_silver.fact_aca_risk_adjustment_transfer_history
Company names	`issuer_name` resolve to a permanent Vlada ID: `resolve_entity(text=<name>)`; the company pages hold owners and holdings
Note	Registered 2026-09-25 (Data Tracker states program, lane B) under register w4 cms_hhs_risk_adjustment_transfers (the Lane G draft from #2746, uncommented after the medicare-fleet load). Door RAW, Data Tracker KPI MKT-15. BY2025 reconciles to the CMS report: payments $11.17B, high-cost risk pool $851.3M individual incl. merged and $420.3M small group (Athena 1ea2adbb). History-only: listed in vlada/query/versioned_tables.json history_only so the door never injects is_current.

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