Drugs and formularies

Part D plan initial coverage limit (ICL), CMS SPUF sidecar, 2019-Q1 to 2024-Q3

drug_silver.fact_part_d_plan_initial_coverage_limit·live·silver

Rows

127,086

Columns

History

N/A

Card

nocolumns only

Typed tools

What it is

1Each Part D plan segment's initial coverage limit (ICL, dollars): the total drug spend at which the initial coverage phase ends and the coverage gap (donut hole) begins, from the ICL field of the quarterly SPUF plan information file, which the original SPUF parser never read.
2A sidecar of drug_silver.dim_part_d_plan: join on (contract_id, plan_id, segment_id, report_quarter) to the parent's is_current = true rows; every sidecar row joins exactly one parent row and every parent row in 2019-Q1..2024-Q3 has one.
3127,086 rows in 23 quarters; icl runs $2,750 to $11,000 (Athena 693d78a1).

Grain

Key	Grain
N/A	1One row per (report_quarter, contract_id, plan_id, segment_id), the key of drug_silver.dim_part_d_plan.

Columns (20)

Column	Type	Meaning
`contract_id`	string
`plan_id`	string
`segment_id`	string
`icl`	double
`report_quarter`	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="drug_silver.fact_part_d_plan_initial_coverage_limit")     # the card, live
query_substrate(sql="SELECT * FROM drug_silver.fact_part_d_plan_initial_coverage_limit WHERE <filter> LIMIT 100")

Item	Value
Typed tools that read it	none; raw SQL
Identity key	`report_quarter,contract_id,plan_id,segment_id`
Join	`how_to_join(source_fqn="drug_silver.fact_part_d_plan_initial_coverage_limit")`
Raw door	Athena (read-only via query_substrate): drug_silver.fact_part_d_plan_initial_coverage_limit JOIN drug_silver.dim_part_d_plan (or vw_dim_part_d_plan_v4) USING (contract_id, plan_id, segment_id, report_quarter), parent is_current = true, pin report_quarter
Note	Register source cms_part_d_spuf (w3, serve raw). RAW. Sidecar of drug_silver.dim_part_d_plan; the parent is unchanged. Loaded on the drug fleet 2026-10-06/07; registered 2026-10-07.

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