# PBP moop (cleaned view)

`medicare_semantic.vw_pbp_moop` · Medicare Advantage, Part D, Medicaid · status live

Cleaned source-faithful PBP moop rows: era-normalized, CMS codes decoded, with offered_status/numeric_amount_observability/null_amount_reason/annualization_policy/combined_limit_policy/confidence_tier. The agent reasoning surface for moop (

**Grain.** One row per MA plan-segment per CMS release (report_year + quarter) per MOOP flavor, where the flavor is identified by source_amount_column (which equals one of in-network / out-of-network / combined / legacy-non-network MOOP). Minimal verified key = contract_id + plan_id + segment_id + report_year + quarter + network_type + source_amount_column → 736,834 distinct vs 737,270 total rows. The 436-row (0.06%) gap is fully-identical duplicate rows that exist only in the legacy 2001-2003 CMS PBP files (no column distinguishes them; even sha256_content_hash matches). network_type alone is NOT sufficient because 'out_of_network' is shared by two flavors (modern Out-of-network MOOP via pbp_d_oon_max_enr_oopc_amt and legacy Non-network MOOP via pbp_d_maxenr_oopc_amt), so source_amount_column is required to separate them.

**How to use it.** Get the current in-network MOOP dollar cap for every MA plan in a plan year using only the latest CMS release: SELECT contract_id, plan_id, segment_id, benefit_amount AS moop_dollar FROM vw_pbp_moop WHERE report_year=2026 AND network_type='in_network' AND quarter=(SELECT max(quarter) FROM vw_pbp_moop WHERE report_year=2026) AND benefit_amount IS NOT NULL. Answers 'what is each MA plan's in-network out-of-pocket maximum in 2026' (median ~$6,684, range $0–$90,000). For out-of-network or combined, switch network_type and the matching source_amount_column.

**Traps.**
- MULTI-SNAPSHOT ACCUMULATION, NO is_current flag: the view keeps EVERY CMS quarterly re-release. A single plan-year appears under multiple (report_year, quarter) snapshots — e.g. plan H0028/059 shows up in all 4 quarters of 2023, and some plans' MOOP amount CHANGES between quarters (H0342/801 has 2 distinct values in 2023). To get the 'current' MOOP for a plan-year you MUST pick the latest quarter (or version) per report_year; otherwise you over-count and mix stale values. Never SUM across rows (aggregation_policy='do_not_sum').
- network_type does NOT identify the row — 'out_of_network' is overloaded by TWO flavors: modern Out-of-network MOOP (pbp_d_oon_max_enr_oopc_amt) and legacy Non-network MOOP (pbp_d_maxenr_oopc_amt). The grain needs source_amount_column (or moop_sub_benefit_label) to separate them. The 4 flavors map 1:1: in_network=pbp_d_out_pocket_amt, both/combined=pbp_d_comb_max_enr_amt, out_of_network=pbp_d_oon_max_enr_oopc_amt, plus the legacy non-network one.
- benefit_amount is NULL in 67% of rows and the NULL is SEMANTIC, not just missing. Disambiguate with offered_status / null_amount_reason: 'offered_unknown' + 'unknown_requires_crosswalk' (385K rows) = plan SAYS it offers this MOOP but the dollar amount is not in the source bid (data gap, needs a crosswalk); 'not_offered' (110K rows) = plan genuinely does not offer this flavor. Only 'offered'/'not_null_numeric_amount' rows (241,725) carry a usable dollar cap. moop_amount_populated_in_bid is the clean boolean for 'has a number'. Do NOT use moop_present (mostly NULL, unreliable).
- moop_type_code is a TIME-VERSIONED code: '1' = Voluntary in 2012-2022 but = 'Lower' in 2023-2026, and there is NO code before 2012. Always read moop_type_regime ('binary_voluntary_mandatory' vs 'three_tier_lower_intermediate_mandatory' vs 'no_type_code_pre_2012') alongside it, or just use the pre-decoded moop_type_label.
- 436 truly-duplicate rows (0.06%) exist only in the legacy 2001-2003 CMS files — fully identical across every column INCLUDING sha256_content_hash, so nothing breaks the tie. The 7-column grain key yields 736,834 distinct vs 737,270 total because of these. From 2004 onward the 7-column key is exactly unique. SELECT DISTINCT or restrict to report_year>=2004 if you need a perfectly unique set.
- Many generic columns inherited from the shared semantic schema are 100% NULL/constant here and carry NO MOOP information: copay_amount, coinsurance_pct, cost_share_string, auth_required, referral_required, max_oop_applies, limit_quantity, benefit_amount_pmpm (all NULL); benefit_category='moop', benefit_amount_kind='maximum_out_of_pocket', benefit_amount_period_code='annual', confidence_tier='source_faithful', consumer_document_reconciliation_status='not_checked', combined_limit_policy='none_detected', source_pbp_section='D' (all constant). The service-category *_raw columns are raw BITMASK strings (e.g. '0111111...'), not human-readable. lmi_authorities is always empty.

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS MA contract ID (e.g. H0028). 1,972 distinct. Part of plan identity. Join key to other Medicare plan tables. |
| plan_id | string | Plan ID within the contract (3-char, e.g. '001'). 656 distinct. Not unique alone; only meaningful with contract_id. |
| segment_id | string | Plan segment within the contract+plan ('0' is default for 2006+, 662K rows; '1'-'9' for multi-segment/regional/SNP plans). EMPTY STRING '' for 2001-2005 legacy files (pre-segment era, 20.7K rows) — treat '' and '0' carefully when joining across eras. |
| version | double | CMS bid/file version counter (0,1,2,... up to ~11+) within a release. Functionally determined by the grain key (adding it does not change distinct count). Informational, not part of grain. |
| report_year | double | The plan year the PBP applies to (2001–2026). THE primary time axis (the profiler mislabeled 'quarter' as the time column). 26 distinct years. |
| quarter | double | CMS release quarter within report_year (1–4). CMS re-publishes the PBP file 1–4 times per plan year; each (report_year,quarter) = exactly one source_url/file. Combined with report_year gives 40 distinct snapshots. NOT a calendar quarter of service — it is a re-release counter. 2025/2026 currently have only Q1. |
| benefit_category | string | Constant 'moop' for 100% of rows — this view is MOOP-only (inherited tag from the shared semantic schema). |
| cost_share_string | string | UNUSED for MOOP — 100% NULL (generic shared-schema column). |
| copay_amount | double | UNUSED for MOOP — 100% NULL. MOOP is a cap, not a copay. |
| coinsurance_pct | double | UNUSED for MOOP — 100% NULL. |
| limit_text | string | Human-readable formatted MOOP string, e.g. '$2000.0 annual MOOP'. Populated (241,725) only when benefit_amount is non-null; mirrors the amount. |
| auth_required | boolean | UNUSED for MOOP — 100% NULL. |
| referral_required | boolean | UNUSED for MOOP — 100% NULL. |
| network_type | varchar(14) | Network scope of the MOOP: 'in_network' (201,597), 'both'=combined in+out (201,597), 'out_of_network' (334,076). CAUTION: 'out_of_network' is OVERLOADED across two distinct MOOP flavors (modern Out-of-network MOOP + legacy Non-network MOOP) — must combine with source_amount_column to disambiguate. |
| max_oop_applies | boolean | UNUSED for MOOP — 100% NULL (generic column; this whole table IS the max-OOP, so the flag is redundant). |
| limit_quantity | double | UNUSED for MOOP — 100% NULL. |
| limit_unit | string | UNUSED for MOOP — effectively NULL (generic shared column). |
| limit_period_code | string | Generic limit-period code; not the MOOP period (the MOOP period is annual, see benefit_amount_period_code). Largely null/unused here. |
| limit_period_label | string | Label for limit_period_code; unused for MOOP. |
| limit_quantity_per_year | double | UNUSED for MOOP — NULL. |
| benefit_amount | double | THE MOOP DOLLAR CAP — the headline cost number. Non-null in only 241,725/737,270 rows (32.8%); median ~$6,684, range $0–$90,000. NULL is meaningful (see offered_status / null_amount_reason): NULL can mean 'offered but amount not in source bid' OR 'not offered'. |
| benefit_amount_kind | varchar(21) | Constant 'maximum_out_of_pocket' (100%) — confirms semantic type of benefit_amount. |
| benefit_amount_period_code | varchar(6) | Constant 'annual' (100%) — MOOP is always an annual cap. |
| benefit_amount_period_label | varchar(6) | Constant 'Annual' (100%). |
| benefit_amount_annualized | double | Annualized MOOP; identical to benefit_amount (same 241,725 populated, MOOP is already annual). Use either. |
| benefit_amount_pmpm | double | Per-member-per-month version — 100% NULL by design (MOOP is an annual cap, not a PMPM concept). Do not expect values. |
| normalization_status | varchar(22) | 'normalized' (241,725, amount present & cleaned) / 'not_populated' (386,125, offered-but-no-amount) / 'not_applicable_no_moop' (109,420, not offered). Tracks amount availability. |
| normalization_basis | varchar(19) | Constant 'cms_moop_annual_cap' (100%) — describes the normalization rule applied. |
| aggregation_policy | varchar(10) | Constant 'do_not_sum' (100%) — instruction NOT to sum MOOP rows across flavors/snapshots (they are caps, not additive line items). |
| offered_status | varchar(15) | Whether the plan offers this MOOP flavor: 'offered' (241,729, amount populated) / 'offered_unknown' (385,777, Y/N flag said offered but no numeric amount in bid) / 'not_offered' (109,764). KEY column for interpreting NULL benefit_amount. |
| numeric_amount_observability | varchar(31) | Mirrors offered_status from the numeric angle: 'all_offered_numeric' (241,725, amount usable) / 'offered_without_numeric_amounts' (385,781, NULL-but-offered) / 'not_offered' (109,764). |
| null_amount_reason | varchar(30) | Why benefit_amount is NULL: 'not_null_numeric_amount' (241,725, actually populated) / 'unknown_requires_crosswalk' (385,781, offered but undisclosed in source) / 'not_offered' (109,764). Use to decide if a NULL is a data gap vs a genuine no-MOOP. |
| annualization_policy | varchar(17) | 'annualized_safe' (241,725, amount safely annual) / 'not_numeric' (495,545, no amount to annualize). |
| combined_limit_policy | varchar(29) | Constant 'none_detected' (100%) — no combined-limit logic detected/applied for MOOP. |
| consumer_document_reconciliation_status | varchar(11) | Constant 'not_checked' (100%) — these values have NOT been reconciled against consumer-facing docs (SB/EOC). A provenance/confidence caveat. |
| confidence_tier | varchar(23) | Constant 'source_faithful' (100%) — values are taken faithfully from the CMS bid source, no inference applied. |
| moop_amount_populated_in_bid | boolean | Boolean: TRUE iff the CMS bid carried a numeric MOOP amount. TRUE for exactly 241,725 rows = exactly the rows where benefit_amount is non-null. Reliable populated-flag (unlike moop_present). |
| moop_benefit_scope_codes | string | Short raw scope code from the source (e.g. '0101','1111') indicating the MOOP's benefit scope. Populated ~237,609 rows. Raw CMS encoding, not human-readable. |
| moop_in_network_medicare_service_categories_raw | string | BITMASK STRING (e.g. '0111111000100000...') — each position = a PBP Medicare service category; 1 = MOOP applies to that category. In-network Medicare services. Raw source encoding. ~233,138 populated. |
| moop_in_network_medicare_services_apply_to_all | boolean | Boolean: does the in-network Medicare MOOP apply to ALL service categories (vs the subset in the bitmask). |
| moop_in_network_non_medicare_service_categories_raw | string | Bitmask of in-network NON-Medicare (supplemental) service categories the MOOP applies to. Raw encoding; sparsely populated. |
| moop_in_network_non_medicare_services_apply_to_all | boolean | Boolean: in-network non-Medicare MOOP applies to all categories. |
| moop_medicare_service_categories_raw | string | Bitmask of Medicare service categories (legacy/unscoped variant) the MOOP applies to. ~39,356 populated (older-era field). |
| moop_medicare_services_apply_to_all | boolean | Boolean: legacy Medicare-services-apply-to-all flag. ~178,471 populated. |
| moop_non_medicare_service_categories_raw | string | Bitmask of non-Medicare service categories (legacy variant). ~41,648 populated. |
| moop_non_medicare_services_apply_to_all | boolean | Boolean: legacy non-Medicare-services-apply-to-all flag. |
| moop_out_of_network_medicare_service_categories_raw | string | Bitmask of out-of-network Medicare service categories the MOOP applies to. ~15,859 populated. |
| moop_out_of_network_medicare_services_apply_to_all | boolean | Boolean: out-of-network Medicare MOOP applies to all categories. |
| moop_out_of_network_non_medicare_service_categories_raw | string | Bitmask of out-of-network non-Medicare service categories. Sparsely populated. |
| moop_out_of_network_non_medicare_services_apply_to_all | boolean | Boolean: out-of-network non-Medicare MOOP applies to all categories. |
| moop_present | boolean | Boolean MOOP-present flag — UNRELIABLE: mostly NULL (385,777 NULL). TRUE only 241,729 (≈ the populated rows). Do NOT use as the offered indicator; use offered_status or moop_amount_populated_in_bid instead. |
| moop_sub_benefit | string | 100% NULL — the sub-benefit code is never populated; use moop_sub_benefit_label instead. |
| moop_sub_benefit_label | varchar(36) | Human-readable MOOP flavor: 'In-network MOOP' (201,597) / 'Out-of-network MOOP' (201,597) / 'Combined in- and out-of-network MOOP' (201,597) / 'Non-network MOOP' (132,479, legacy). 1:1 with (network_type + source_amount_column). |
| moop_type_code | string | CMS MOOP type code (1/2/3); NULL pre-2012. TIME-VERSIONED CODE SYSTEM (see moop_type_regime): in 2012-2022, 1=Voluntary, 2=Mandatory; in 2023-2026, 1=LOWER, 2=Mandatory, 3=Intermediate. CODE '1' MEANS DIFFERENT THINGS IN DIFFERENT YEARS. NULL when not offered/no-amount or pre-2012. |
| moop_type_label | string | Decoded label for moop_type_code: 'Voluntary','Mandatory','Lower','Intermediate' (NULL pre-2012 / when no amount). Already disambiguates the code-reuse, so prefer this over moop_type_code. |
| moop_type_regime | varchar(39) | Which code scheme is in effect: 'no_type_code_pre_2012' (2001-2011) / 'binary_voluntary_mandatory' (2012-2022) / 'three_tier_lower_intermediate_mandatory' (2023-2026). ESSENTIAL to interpret moop_type_code correctly across years. |
| source_amount_column | varchar(26) | The exact PBP Section D source field this row's amount came from — 4 values: pbp_d_out_pocket_amt (in-network), pbp_d_oon_max_enr_oopc_amt (out-of-network), pbp_d_comb_max_enr_amt (combined), pbp_d_maxenr_oopc_amt (legacy non-network). REQUIRED in the grain (disambiguates the overloaded 'out_of_network' network_type). |
| source_present_column | varchar(25) | The PBP source Y/N flag column for this MOOP flavor (e.g. pbp_d_oon_max_enr_oopc_yn). Provenance of the 'offered' signal. |
| source_type_column | string | The PBP source column carrying the MOOP type code (e.g. pbp_d_oon_max_enr_oopc_type). NULL for ~120,472 pre-2012 rows where no type column existed. |
| source_pbp_section | varchar(1) | Constant 'D' (100%) — all MOOP comes from PBP Section D. |
| source_silver_table | varchar(29) | Constant 'medicare_silver.pbp_section_d' (100%) — the upstream silver table this view is built from. (View name in this DB: vw_pbp_moop.) |
| lmi_authorities | string | Empty string '' for 100% of rows — placeholder, not populated for this view. |
| source_url | string | CMS download URL of the exact PBP zip for this (report_year, quarter), e.g. .../pbp-benefits-2024-quarter-1.zip. One URL per snapshot — provenance / re-download anchor. |
| sha256_content_hash | string | Content hash of the source record for provenance/integrity. NOTE: identical across the legacy 2002-2003 duplicate rows (so it does not break ties on those dupes). |

Tools that read it: get_pbp_benefit

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