# Part D plan dimension (CMS SPUF), plan type from CMS's organization type, names as CMS wrote them

`drug_silver.vw_dim_part_d_plan_v4` · Drugs and formularies · status live · 166,440 rows

The SPUF Part D plan dim (every column of drug_silver.dim_part_d_plan) with plan_type from CMS's own organization type for the contract, read from CMS Monthly Enrollment by Contract/Plan (medicare_silver.enrollment_monthly_plan): 'MA-PD' (Local CCP, Regional CCP, PFFS, MSA), 'COST' (1876 Cost, HCPP - 1833 Cost), 'DEMO' (Demo: Medicare-Medicaid Plans), 'PACE' (National PACE), 'PDP'. cms_organization_type carries CMS's wording verbatim; plan_type_source says 'cms_contract_file' or, where CMS's contract file has no row for the contract, 'contract_prefix_fallback' (the record layout's H/R = MA, S = PDP rule); contract_id_prefix is the letter alone; plan_type_legacy keeps the stored value, which called every local MA-PD plan 'OTHER'. Plan names carry CMS's accented letters (v4).

**Grain.** One row per (contract_id, plan_id, segment_id, report_quarter) among is_current rows; 0 duplicate keys, 166,440 rows = drug_silver.dim_part_d_plan (Athena 622fdb61-469e-4096-90b8-2719a68c363b).

| 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 | varchar(5) |  |
| cms_organization_type | string |  |
| plan_type_source | varchar(24) |  |
| contract_id_prefix | string |  |
| plan_type_legacy | 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 |  |
| plan_name_legacy | string |  |
| plan_name_source | varchar(20) |  |

Query it: `query_substrate` with `SELECT ... FROM drug_silver.vw_dim_part_d_plan_v4 WHERE ... LIMIT 100`; read `describe_data_source("drug_silver.vw_dim_part_d_plan_v4")` first.