States K1H SBM enrollment history: marketplace_sbm_issuer_enrollment_history_v1

What it is

1. 1Marketplace product K1H (SBM enrollment history) of the States lane: state-based exchange enrollment by issuer before CMS's issuer file reaches them (2014-2017), from the exchanges' own reports.
2. 2THE EXCHANGE'S OWN COUNT, NOT THE CMS ISSUER PUF: never splice it onto the CMS series (vw_marketplace_enrollment_by_parent_v1) without keeping source_label beside every year.
3. 3The measure differs by state: NY is qhp_individual_enrollees from the open enrollment report, CA is active_members_paid_effectuated, so never compare or add the two as one measure.
4. 4Filter geo_level: NY prints counties and the state row is derivation sum_of_printed_county_cells, so summing both double counts.
5. 5CA is quarterly snapshots (one source_label per as_of_date): pick one, never sum a year's quarters. parent_id_as_now is null where the issuer has no parent match (issuer_match_basis says why);
6. 6WA does not publish it (absent is not zero).
7. 72 state(s): CA NY.

Grain

Columns (26)

Query it

describe_data_source(source_fqn="medicare_silver.marketplace_sbm_issuer_enrollment_history_v1")     # the card, live
query_substrate(sql="SELECT * FROM medicare_silver.marketplace_sbm_issuer_enrollment_history_v1 WHERE <filter> LIMIT 100")