Drugs and formularies

Part D plan dimension (CMS SPUF)

drug_silver.dim_part_d_plan·live·silver·Part D Plans & Formularies

Rows

160,922

Columns

History

N/A

Card

nocolumns only

Typed tools

What it is

1The Part D plan-identity dim from CMS SPUF: the plan -> formulary bridge and carrier/plan attribution for the whole SPUF family.
2Columns (from DIM_PART_D_PLAN_SCHEMA in pipelines/drug/silver/spuf_writers.py): contract_name, plan_name, formulary_id, premium, deductible, ma_region_code, pdp_region_code, snp, plan_suppressed_yn, plan_type, plus the is_current vintage flag.
3Resolve contract_id/plan_id -> formulary_id here before joining fact_drug_formulary.
4Geography (state, county_code) is NOT here — it moved to fact_part_d_plan_service_area on 2026-05-17 after Gate A.5 caught 4,587 PK collisions (one dim row was being written once per served county).

Grain

Key	Grain
N/A	1One row per (contract_id, plan_id, segment_id, report_quarter).

Columns (29)

Column	Type	Meaning
`contract_id`	string
`plan_id`	string
`segment_id`	string
`report_quarter`	string
`contract_name`	string
`plan_name`	string
`formulary_id`	string
`premium`	double
`deductible`	double
`ma_region_code`	string
`pdp_region_code`	string
`snp`	string
`plan_suppressed_yn`	boolean
`plan_type`	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.dim_part_d_plan")     # the card, live
query_substrate(sql="SELECT * FROM drug_silver.dim_part_d_plan WHERE <filter> LIMIT 100")

Item	Value
Typed tools that read it	formulary_churn
Identity key	`contract_id,plan_id,segment_id`
Join	`how_to_join(source_fqn="drug_silver.dim_part_d_plan")`
Raw door	Tools: plan_landscape, cost_share_lens, pricing_lens (+ all formulary tools resolve plan->formulary here); Athena: drug_silver.dim_part_d_plan (filter is_current = true)
Note	SPUF plan-identity dim shared by cost_share_lens, pricing_lens, and (for plan->formulary) the formulary tools; filter is_current = true. plan_landscape and formulary_coverage_lookup read drug_silver.vw_dim_part_d_plan_v3 since 2026-09-30 (PDPM-1); this table is unchanged and stays the physical storage.

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