# MA pbp mrx p

`medicare_silver.pbp_mrx_p` · Medicare Advantage, Part D, Medicaid · status partial

Part D plan x pharmacy-tier formulary detail from mrx_p. The source-native post-tier identifiers retain repeated legacy rows without a synthetic mrx_p_id.

**Grain.** One row per Part D plan segment per formulary drug tier per quarterly CMS snapshot. Verified minimal key = contract_id + plan_id + segment_id + report_year + quarter + mrx_tier_post_id (each column is required; dropping any makes it non-unique). 622,445 rows total; 290,631 within a single quarter (quarter=1). The "p" in the table name = the POST (post-deductible / initial coverage) phase of the Part D benefit.

**How to use it.** To compare a plan's published drug-tier cost structure for a benefit year, filter to one snapshot and inspect the tiers. Example: SELECT mrx_tier_post_id, mrx_tier_post_label_list, mrx_tier_post_cost_struct, mrx_tier_post_copay_amt, mrx_tier_post_coins_pct FROM medicare_silver.pbp_mrx_p WHERE contract_id='H0028' AND plan_id='014' AND segment_id='0' AND report_year=2026 AND quarter=1 ORDER BY CAST(mrx_tier_post_id AS int); — returns that plan's 2026 formulary tiers (Preferred Generic → Specialty) and their post-deductible cost-sharing structure. For the actual retail/mail copays by day-supply, join the same plan key to medicare_silver.pbp_mrx_tier.

**Traps.**
- COST IS MOSTLY NULL HERE. Only ~23.6% of the standard 5-tier rows carry any copay or coinsurance (148K/622K have a copay, 8K have coinsurance, 472K rows have neither). This 'post' table is the tier SUMMARY; the real per-pharmacy-channel cost-sharing (preferred vs standard retail, mail order, 1/2/3-month supply, LTC, OON) is in the SIBLING table medicare_silver.pbp_mrx_tier (columns mrx_tier_inp_copay_1m, mrx_tier_innpp_*, mrx_tier_oon_*, etc.). For actual drug copays, join to pbp_mrx_tier, not this table alone.
- NO is_current flag and quarter is the snapshot dimension. CMS republishes the entire PBP file each quarter, so one plan-year appears up to 4 times (Q1–Q4) as near-duplicate rows. The grain REQUIRES quarter. To get one row per plan-year, filter to a single quarter (quarter=1, or MAX(quarter)). Counting rows without this double/triple/quadruple-counts plans in 2018–2024.
- Decode copay vs coinsurance via mrx_tier_post_cost_struct: NULL=cost not stored here (look in pbp_mrx_tier), '2'=copay only, '1'=coinsurance only, '3'=both. Don't assume a NULL copay means $0 — it usually means 'detail lives elsewhere'.
- Dead / near-dead columns: mrx_tier_post_type_id is the constant '2' (the profiler wrongly put it in the grain — it adds nothing); mrx_tier_post_ref_prcng_yn and mrx_tier_post_lbl are 100% NULL; spec_yn/inject_yn/cst_shr_type/includes_mmp are >98% NULL. bid_id is just contract_id_plan_id_segment_id concatenated. plan_type/effective_period_start/observed_period in the profiler's 10-col key are redundant — the true minimal grain is 6 columns.
- plan_id and segment_id are zero-padded STRINGS ('001','0'), not integers — cast carefully when joining. report_year/quarter/version are stored as doubles (2023.0, 1.0). orgtype='10' rows are standalone PDPs and legitimately have NULL pbp_a_ben_cov.
- asserted_at / parse_revision / cms_vintage_sequence / parser_artifact_hash / sha256_content_hash are Vlada ingestion provenance, NOT plan attributes. asserted_at (2026-05-16/20) is the load date — never use it as a plan effective date; use report_year / effective_period_start for that.

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS Medicare contract ID (e.g. H0022, H0028); 1,275 distinct. Part of the plan identity. H-contracts=MA/MA-PD, S-contracts=PDP. |
| plan_id | string | 3-digit plan number within the contract, zero-padded string (e.g. '001','014'); 546 distinct. NOT unique on its own. |
| segment_id | string | Plan segment ('0' for non-segmented; up to 24 distinct). Distinguishes geographic/benefit segments of the same plan. Required for the grain. |
| mrx_tier_post_label_list | string | Human label of THIS tier as named by the plan (e.g. 'Preferred Brand','Specialty Tier','Preferred Generic','Generic','Non-Preferred Drug','Vaccines','Select Care Drugs'). The descriptive name for the tier whose number is mrx_tier_post_id. |
| version | double | PBP bid submission version for this plan (1.0–26.0). Constant across all tier rows of a given plan-year-quarter (verified: no plan-year-quarter has >1 version). Higher = later resubmission of that plan's bid. |
| report_year | double | Plan/benefit year the cost-sharing applies to (2010–2026). Equals the year of effective_period_start. The main time dimension. |
| quarter | double | CMS quarterly release (1–4) the row was sourced from. CMS republishes the full PBP file each quarter, so one plan-year can appear up to 4 times. THIS is the snapshot/dedup dimension — there is NO is_current flag. Pick quarter=1 (or MAX) for one row per plan-year. |
| bid_id | string | Concatenation contract_id_plan_id_segment_id (e.g. 'H0022_001_0'). Fully redundant with the first three columns; convenience key only. |
| mrx_tier_post_benefit_type | string | Part-D benefit-design code for the tier: '4' (defined standard / actuarially-equivalent, 88% of rows), '3', '2'. Indicates the standard-vs-enhanced benefit context, not the dollar amount. |
| mrx_tier_post_coins_pct | double | Coinsurance percentage the member pays for this tier in the post-deductible phase (0–50%, avg ~5.4 when set). NULL on 98.6% of rows. Set only when cost_struct IN (1,3). Mutually-exclusive-ish with copay_amt. |
| mrx_tier_post_copay_amt | double | Flat dollar copay the member pays for this tier (post-deductible) ($0–$100, avg ~$1.40 over all rows). NULL on 76% of rows. Populated only when cost_struct IN (2,3). |
| mrx_tier_post_copay_amt_mmpmax | double | Upper-bound copay for Medicare-Medicaid Plans (MMP/D-SNP integrated) members. Non-null for only 3,623 rows / 72 contracts (range 0–3.4). NULL = not an MMP-specific row. |
| mrx_tier_post_copay_amt_mmpmin | double | Lower-bound MMP copay (companion to mmpmax). Same 3,623-row MMP-only population. NULL = not MMP-specific. |
| mrx_tier_post_cost_struct | string | Cost-sharing structure flag that decodes which cost column is populated: NULL=neither populated here (76% — detail is in pbp_mrx_tier), '2'=copay only, '1'=coinsurance only, '3'=both copay AND coinsurance. The key to interpreting copay vs coins. |
| mrx_tier_post_cst_shr_type | string | Cost-share type sub-code; almost entirely NULL (616,230 rows), '2'=5,737, '1'=478. Low-value / rarely populated. |
| mrx_tier_post_drug_type | string | Bitmask-style code of drug categories the tier covers ('11'=most common, '01','10', plus 6-digit masks like '010010'). Encodes generic/brand/specialty mix; pairs with form_model_desc. |
| mrx_tier_post_form_model_desc | string | Full ordered tier-model string for the WHOLE plan (e.g. 'Preferred Generic, Generic, Preferred Brand, Non-Preferred Drug, Specialty Tier'). Same value repeated on every tier row of that plan; describes the overall formulary tier layout. |
| mrx_tier_post_id | string | Tier NUMBER 1–7 (1=lowest/preferred-generic … 5=specialty; 6–7 rare extra tiers). Part of the grain — identifies which tier the row's cost-sharing is for. mrx_tier_post_label_list is its name. |
| mrx_tier_post_includes | string | Code for what the tier 'includes' relative to other tiers: '1' (89%), '3','2', NULL. Cross-references tiers (e.g. specialty tier includes injectables). Mostly '1'. |
| mrx_tier_post_includes_mmp | string | MMP-specific 'includes' code; NULL on 99.4% of rows (only the 3,623 MMP rows populate '1'/'2'/'3'). |
| mrx_tier_post_inject_yn | string | Whether the tier is for injectable drugs ('1'/'2'); 99.8% NULL. Sparse flag. |
| mrx_tier_post_lbl | string | Free-text tier label override; 100% NULL in this extract — effectively a dead column. |
| mrx_tier_post_ref_prcng_yn | string | Reference-pricing yes/no flag; 100% NULL across all 622,445 rows — DEAD column, never populated. |
| mrx_tier_post_spec_yn | string | Whether the tier is a specialty tier ('1'/'2'); 99.8% NULL. Specialty tiers are usually identified via the label instead. |
| mrx_tier_post_type_id | string | CONSTANT '2' on all 622,445 rows — a dead key. The profiler put it in the grain but it carries no information. |
| orgtype | string | CMS organization type: '01'=local CCP/MA-PD (79%), '10'=standalone PDP (113K rows, these have NULL pbp_a_ben_cov), plus '04','05','06','11'. Identifies MA vs standalone-PDP context. |
| part_d_enhncd_cvrg_demo | string | Enhanced-coverage demonstration flag ('Y'/'N'); NULL on 76% of rows (older years). Indicates participation in the Part D enhanced-coverage demo. |
| part_d_model_demo | string | Part D model/demonstration participation ('Y'/'N'); 97% NULL, 'Y' only 122 rows. Niche demo marker (e.g. VBID/Senior Savings). |
| pbp_a_ben_cov | string | Benefit-coverage indicator from PBP section A: '1'=508K, NULL=113K (all the orgtype=10 standalone-PDP rows), '2'=547. Distinguishes MA-PD vs PDP benefit-coverage context. |
| pbp_a_plan_type | string | MA/PDP plan-type code: '01'=HMO (283K), '04'=Local PPO (150K), '29'=PDP standalone (113K), '02'=HMO-POS, '31'=Regional PPO, '48','18','09'… Identifies the product type. Part of the profiler's superset key but redundant given contract/plan/segment+year+quarter. |
| observed_period | date | Date the data represents = Jan 1 of report_year (always equals effective_period_start in this table). Vlada bitemporal column. |
| asserted_at | date | Date Vlada loaded/asserted the row (two batches: 2026-05-16 and 2026-05-20). Pure ingestion provenance — NOT plan data; do not use as plan effective date. |
| effective_period_start | date | Start of the plan year = Jan 1 of report_year (2010-01-01 … 2026-01-01). The profiler's temporal anchor; interchangeable with report_year. |
| effective_period_end | date | End of the plan year = Dec 31 of report_year. Always the full calendar year. |
| cms_vintage_sequence | int | CMS file-vintage sequence (1–4). Roughly tracks quarter for re-released years (2→Q2, 3→Q3, 4→Q4) but for Q1 it is mostly 1 with exceptions (2022→2, 2024→4). Provenance/dedup helper; do NOT treat as identical to quarter. |
| parse_revision | int | Vlada parser revision that produced the row ('1' for the 2026-05-16 batch, '2' for 2026-05-20). Ingestion provenance only. |
| parser_artifact_hash | string | SHA-256 of the parser artifact/code version. Provenance for reproducibility. |
| source_url | string | CMS source ZIP the row came from, e.g. https://www.cms.gov/files/zip/pbp-benefits-2023-quarter-1.zip. Encodes year+quarter of the release. |
| sha256_content_hash | string | SHA-256 of the underlying source content file. Provenance/integrity hash. |
| source_row_ordinal | double |  |

Tools that read it: get_pbp_benefit

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