# ACA Marketplace Analytics Dataset

### About the Dataset

The **ACA Marketplace Analytics Dataset** (Affordable Care Act / Obamacare) provides comprehensive, analytics-ready access to the **CMS Health Insurance Exchange Public Use Files** (Exchange PUFs) from the Centers for Medicare & Medicaid Services, spanning Plan Years 2014 through 2026. Seven government data files from **HealthCare.gov** and the **Federally Facilitated Exchange (FFE)** are combined into a single, query-ready Snowflake database: the **Rate PUF** (premium rates by age and geography), **Plan Attributes PUF** (deductibles, out-of-pocket maximums, metal levels), **Benefits and Cost Sharing PUF** (copays, coinsurance, EHB status), **Plan ID Crosswalk PUF** (year-over-year plan mappings), **Service Area PUF** (issuer geographic coverage), **Business Rules PUF** (rate application rules), and **Network PUF** (provider network references). Built for health insurance analysts, actuaries, health policy researchers, benefits consultants, and anyone working with **Qualified Health Plan (QHP)** data from the ACA individual marketplace.

🔗 [Find the ACA Marketplace Analytics Dataset on the Snowflake Marketplace.](https://app.snowflake.com/marketplace/listing/GZT1Z7QRSXWC/dataplex-consulting-data-products-aca-marketplace-analytics-dataset)

### Quick Access

**Tables**: RATES, PLAN\_ATTRIBUTES, BENEFITS\_COST\_SHARING, PLAN\_CROSSWALK, SERVICE\_AREAS, BUSINESS\_RULES, NETWORK\
**Sources**: 7 CMS Exchange Public Use Files from cms.gov\
**Update Frequency**: Annual initial release (\~October), with 2-3 mid-year corrections\
**Coverage**: Federally Facilitated Exchange (FFE) states (\~30 states for PY2026)

## Overview

The ACA Marketplace Analytics Dataset provides comprehensive access to CMS Exchange PUF data including:

* **RATES** (Rate PUF) - Premium rates by plan, age band, tobacco use, and geographic rating area (\~2.2M rows per plan year)
* **PLAN\_ATTRIBUTES** (Plan Attributes PUF) - Plan-level details: metal level, plan type, deductibles, MOOP, HSA eligibility, SBC scenario costs (\~22K plan variants per year)
* **BENEFITS\_COST\_SHARING** (Benefits & Cost Sharing PUF) - Benefit-level copays, coinsurance, and coverage details per plan variant (\~1.5M rows per year)
* **PLAN\_CROSSWALK** (Plan ID Crosswalk PUF) - Year-over-year plan mappings for multi-year trend analysis (\~159K rows per year)
* **SERVICE\_AREAS** (Service Area PUF) - Geographic coverage by issuer, county, and ZIP code (\~8.8K rows per year)
* **BUSINESS\_RULES** (Business Rules PUF) - Rate application rules: age determination, tobacco use, dependent rules (\~5.1K rows per year)
* **NETWORKS** (Network PUF) - Provider network names and URLs per issuer (\~550 rows per year)

All tables include 13 plan years (PY2014-PY2026) of historical data, enabling longitudinal trend analysis across the full history of the ACA marketplaces.

### Metadata Tables

Every Dataplex data product includes these standard metadata tables:

| Table             | Purpose                                                        |
| ----------------- | -------------------------------------------------------------- |
| `FEEDS`           | Dataset catalog — available tables, descriptions, update dates |
| `FEEDS_FILES`     | Batch load history with `is_latest` flag for data freshness    |
| `CHANGELOG`       | Change log — data loads, schema changes, corrections           |
| `DATA_DICTIONARY` | Column descriptions for all tables                             |

## Entity Relationship Diagram

![ACA Marketplace Entity Relationship Diagram](https://813439891-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDeimGtBflXKQn786VLvj%2Fuploads%2Fgit-blob-539ac73c726926351a12bec9ce953ad44e263a35%2Fentity-relationship.png?alt=media)

The core join key across RATES, PLAN\_ATTRIBUTES, and BENEFITS\_COST\_SHARING is `plan_id`. SERVICE\_AREAS links via `issuer_id` + `state_code`. PLAN\_CROSSWALK maps plans across years via `plan_id_prior` and `plan_id_current`. Every table includes `plan_year` for period-partitioned queries and links to `FEEDS`/`FEEDS_FILES` via `feed_id` and `feeds_files_id` for data lineage tracking.

## Data Tables

### RATES (Rate PUF)

Premium rate data by plan, subscriber age, tobacco use, and geographic rating area. Each row represents one plan in one rating area for one age/tobacco combination.

**Key Features:**

* \~2.2M rows per plan year (PY2026), \~29M rows across all 13 years
* Individual and family tier premiums (couple, subscriber+dependents, etc.)
* 30 FFE states, 359 issuers, 5,144 unique plans (PY2026)
* Annual data with mid-year issuer corrections

#### Column Reference

| Column                                                | Type      | Description                                                                   |
| ----------------------------------------------------- | --------- | ----------------------------------------------------------------------------- |
| plan\_year                                            | NUMBER    | Plan year (2014-2026) — use for period filtering                              |
| state\_code                                           | VARCHAR   | Two-letter state abbreviation (FFE states only)                               |
| issuer\_id                                            | NUMBER    | CMS-assigned issuer identifier                                                |
| plan\_id                                              | VARCHAR   | 14-character HIOS plan identifier                                             |
| rating\_area\_id                                      | VARCHAR   | Geographic rating area within state                                           |
| tobacco                                               | VARCHAR   | Tobacco use indicator                                                         |
| age                                                   | VARCHAR   | Subscriber age band ("0-14", "15", ..., "64", "65 and over", "Family Option") |
| individual\_rate                                      | DECIMAL   | Monthly premium for individual subscriber                                     |
| individual\_tobacco\_rate                             | DECIMAL   | Monthly premium with tobacco surcharge                                        |
| couple                                                | DECIMAL   | Monthly premium for couple tier                                               |
| primary\_subscriber\_and\_one\_dependent              | DECIMAL   | Premium for subscriber + 1 dependent                                          |
| primary\_subscriber\_and\_two\_dependents             | DECIMAL   | Premium for subscriber + 2 dependents                                         |
| primary\_subscriber\_and\_three\_or\_more\_dependents | DECIMAL   | Premium for subscriber + 3+ dependents                                        |
| couple\_and\_one\_dependent                           | DECIMAL   | Premium for couple + 1 dependent                                              |
| couple\_and\_two\_dependents                          | DECIMAL   | Premium for couple + 2 dependents                                             |
| couple\_and\_three\_or\_more\_dependents              | DECIMAL   | Premium for couple + 3+ dependents                                            |
| feed\_id                                              | VARCHAR   | FK to FEEDS — identifies which dataset this row belongs to                    |
| feeds\_files\_id                                      | VARCHAR   | FK to FEEDS\_FILES — identifies which batch loaded this data                  |
| created\_at                                           | TIMESTAMP | When the source data was loaded into the warehouse                            |
| updated\_at                                           | TIMESTAMP | When the table was last rebuilt                                               |

### PLAN\_ATTRIBUTES (Plan Attributes PUF)

Plan variant-level data including marketing name, metal level, plan type, HSA eligibility, deductibles, maximum out-of-pocket amounts, and SBC scenario costs.

**Key Features:**

* \~22,000 plan variants per year across all FFE states
* 151 columns including cost sharing at multiple tier/network levels
* SBC standard scenarios (having a baby, managing diabetes, simple fracture)
* Metal level, plan type (HMO/PPO/POS/EPO), and CSR variant classification

#### Column Reference

| Column                             | Type      | Description                                                  |
| ---------------------------------- | --------- | ------------------------------------------------------------ |
| plan\_year                         | NUMBER    | Plan year (2014-2026)                                        |
| state\_code                        | VARCHAR   | Two-letter state abbreviation                                |
| issuer\_id                         | NUMBER    | CMS-assigned issuer identifier                               |
| standard\_component\_id            | VARCHAR   | 14-character standard plan component ID                      |
| plan\_id                           | VARCHAR   | Full HIOS plan ID including variant suffix                   |
| plan\_marketing\_name              | VARCHAR   | Consumer-facing plan name                                    |
| plan\_type                         | VARCHAR   | Plan network type: HMO, PPO, POS, or EPO                     |
| metal\_level                       | VARCHAR   | Metal tier: Bronze, Silver, Gold, Platinum, or Catastrophic  |
| is\_new\_plan                      | VARCHAR   | Whether this plan is new for the current year                |
| is\_hsa\_eligible                  | VARCHAR   | Whether the plan qualifies for Health Savings Account        |
| mehb\_inn\_tier1\_individual\_moop | DECIMAL   | Maximum out-of-pocket for in-network individual (MEHB)       |
| mehb\_ded\_inn\_tier1\_individual  | DECIMAL   | Annual deductible for in-network individual (MEHB)           |
| sbc\_having\_a\_baby\_deductible   | DECIMAL   | SBC scenario: total deductible for having a baby             |
| sbc\_having\_diabetes\_deductible  | DECIMAL   | SBC scenario: total deductible for managing diabetes         |
| csr\_variation\_type               | VARCHAR   | Cost-sharing reduction variant type                          |
| issuer\_actuarial\_value           | DECIMAL   | Actuarial value of the plan                                  |
| feed\_id                           | VARCHAR   | FK to FEEDS — identifies which dataset this row belongs to   |
| feeds\_files\_id                   | VARCHAR   | FK to FEEDS\_FILES — identifies which batch loaded this data |
| created\_at                        | TIMESTAMP | When the source data was loaded into the warehouse           |
| updated\_at                        | TIMESTAMP | When the table was last rebuilt                              |

### BENEFITS\_COST\_SHARING (Benefits & Cost Sharing PUF)

Benefit-level cost sharing details: copays, coinsurance rates, quantity limits, and EHB status per plan variant and benefit category.

**Key Features:**

* \~1.46M rows per plan year (one row per plan variant per benefit)
* In-network Tier 1/2 and out-of-network copay and coinsurance
* Essential Health Benefit (EHB) classification
* Coverage and exclusion indicators

#### Column Reference

| Column                | Type      | Description                                                                     |
| --------------------- | --------- | ------------------------------------------------------------------------------- |
| plan\_year            | NUMBER    | Plan year (2014-2026)                                                           |
| state\_code           | VARCHAR   | Two-letter state abbreviation                                                   |
| issuer\_id            | NUMBER    | CMS-assigned issuer identifier                                                  |
| plan\_id              | VARCHAR   | HIOS plan ID with variant — join to PLAN\_ATTRIBUTES                            |
| benefit\_name         | VARCHAR   | EHB benefit category (e.g., "Primary Care Visit to Treat an Injury or Illness") |
| copay\_inn\_tier1     | VARCHAR   | In-network Tier 1 copay amount                                                  |
| copay\_inn\_tier2     | VARCHAR   | In-network Tier 2 copay amount                                                  |
| copay\_out\_of\_net   | VARCHAR   | Out-of-network copay amount                                                     |
| coins\_inn\_tier1     | VARCHAR   | In-network Tier 1 coinsurance rate                                              |
| coins\_inn\_tier2     | VARCHAR   | In-network Tier 2 coinsurance rate                                              |
| coins\_out\_of\_net   | VARCHAR   | Out-of-network coinsurance rate                                                 |
| is\_ehb               | VARCHAR   | Essential Health Benefit flag                                                   |
| is\_covered           | VARCHAR   | Whether the benefit is covered by the plan                                      |
| quant\_limit\_on\_svc | VARCHAR   | Whether a quantity limit applies                                                |
| limit\_qty            | VARCHAR   | Quantity limit value                                                            |
| limit\_unit           | VARCHAR   | Quantity limit unit (e.g., visits, days)                                        |
| exclusions            | VARCHAR   | Benefit exclusion notes                                                         |
| feed\_id              | VARCHAR   | FK to FEEDS — identifies which dataset this row belongs to                      |
| feeds\_files\_id      | VARCHAR   | FK to FEEDS\_FILES — identifies which batch loaded this data                    |
| created\_at           | TIMESTAMP | When the source data was loaded into the warehouse                              |
| updated\_at           | TIMESTAMP | When the table was last rebuilt                                                 |

### PLAN\_CROSSWALK (Plan ID Crosswalk PUF)

Year-to-year plan mappings at the county/ZIP level. Maps QHPs from the prior plan year to the current year, enabling multi-year trend analysis and plan continuity tracking.

**Key Features:**

* \~159K rows per year (PY2015-PY2026; PY2014 has no prior year)
* County and ZIP-level geographic granularity
* Crosswalk reason codes (renewal, discontinuation, new issuer)
* Metal level change tracking and age-off plan mappings

#### Column Reference

| Column                 | Type      | Description                                                  |
| ---------------------- | --------- | ------------------------------------------------------------ |
| state                  | VARCHAR   | Two-letter state abbreviation                                |
| plan\_id\_prior        | VARCHAR   | Plan ID from the prior year                                  |
| plan\_id\_current      | VARCHAR   | Mapped plan ID for the current year (NULL if discontinued)   |
| issuer\_id\_prior      | NUMBER    | Issuer ID from prior year                                    |
| issuer\_id\_current    | NUMBER    | Issuer ID in current year                                    |
| metal\_level\_prior    | VARCHAR   | Metal tier in the prior year                                 |
| metal\_level\_current  | VARCHAR   | Metal tier in the current year                               |
| crosswalk\_level       | VARCHAR   | Match confidence level (plan-to-plan, issuer-level)          |
| reason\_for\_crosswalk | VARCHAR   | Why this mapping exists (renewal, discontinuation, etc.)     |
| fips\_code             | VARCHAR   | 5-digit FIPS county code                                     |
| zip\_code              | VARCHAR   | ZIP code                                                     |
| dental\_plan           | VARCHAR   | Whether this is a dental plan                                |
| feed\_id               | VARCHAR   | FK to FEEDS — identifies which dataset this row belongs to   |
| feeds\_files\_id       | VARCHAR   | FK to FEEDS\_FILES — identifies which batch loaded this data |
| created\_at            | TIMESTAMP | When the source data was loaded into the warehouse           |
| updated\_at            | TIMESTAMP | When the table was last rebuilt                              |

### SERVICE\_AREAS (Service Area PUF)

Geographic service area coverage by issuer, mapping issuers to the counties and ZIP codes where they offer plans.

**Key Features:**

* \~8,800 rows per year
* Issuer-to-geography mapping at county and ZIP level
* Essential for geographic coverage gap analysis

#### Column Reference

| Column              | Type      | Description                                                  |
| ------------------- | --------- | ------------------------------------------------------------ |
| plan\_year          | NUMBER    | Plan year                                                    |
| state\_code         | VARCHAR   | Two-letter state abbreviation                                |
| issuer\_id          | NUMBER    | CMS-assigned issuer identifier                               |
| service\_area\_id   | VARCHAR   | Issuer's service area code                                   |
| service\_area\_name | VARCHAR   | Service area display name                                    |
| county\_name        | VARCHAR   | County name                                                  |
| zip\_code           | VARCHAR   | ZIP code                                                     |
| partial\_county     | VARCHAR   | Whether partial county coverage applies                      |
| feed\_id            | VARCHAR   | FK to FEEDS — identifies which dataset this row belongs to   |
| feeds\_files\_id    | VARCHAR   | FK to FEEDS\_FILES — identifies which batch loaded this data |
| created\_at         | TIMESTAMP | When the source data was loaded into the warehouse           |
| updated\_at         | TIMESTAMP | When the table was last rebuilt                              |

### BUSINESS\_RULES (Business Rules PUF)

Rate application rules: age determination, tobacco policy, dependent coverage limits, and market coverage scope per standard component.

**Key Features:**

* \~5,100 rows per year
* Rules define how premiums are calculated and applied
* Covers age determination, tobacco-free periods, dependent age limits

#### Column Reference

| Column                               | Type      | Description                                                  |
| ------------------------------------ | --------- | ------------------------------------------------------------ |
| plan\_year                           | NUMBER    | Plan year                                                    |
| state\_code                          | VARCHAR   | Two-letter state abbreviation                                |
| issuer\_id                           | NUMBER    | CMS-assigned issuer identifier                               |
| product\_id                          | VARCHAR   | HIOS product identifier                                      |
| standard\_component\_id              | VARCHAR   | Standard component identifier                                |
| medical\_dental\_both                | VARCHAR   | Whether the rule applies to medical, dental, or both         |
| medical\_dental\_indicator           | VARCHAR   | Medical or dental indicator                                  |
| maximum\_underage\_dependent         | VARCHAR   | Maximum age for dependent coverage                           |
| dependent\_maximum\_age\_rule        | VARCHAR   | Rule for maximum dependent age                               |
| age\_determination\_rule             | VARCHAR   | How age is determined for rating purposes                    |
| minimum\_tobacco\_free\_months\_rule | VARCHAR   | Minimum months tobacco-free to qualify as non-tobacco user   |
| cohabitation\_rule                   | VARCHAR   | Cohabitation rule for domestic partners                      |
| market\_coverage                     | VARCHAR   | Market coverage type (Individual, SHOP, or Both)             |
| dental\_only\_plan                   | VARCHAR   | Whether this is a dental-only plan                           |
| feed\_id                             | VARCHAR   | FK to FEEDS — identifies which dataset this row belongs to   |
| feeds\_files\_id                     | VARCHAR   | FK to FEEDS\_FILES — identifies which batch loaded this data |
| created\_at                          | TIMESTAMP | When the source data was loaded into the warehouse           |
| updated\_at                          | TIMESTAMP | When the table was last rebuilt                              |

### NETWORK (Network PUF)

Provider network names and directory URLs for each issuer, enabling consumers to check which network a plan uses and find providers.

**Key Features:**

* \~550 rows per year
* Maps issuer to network name and provider directory URL
* Essential for provider search and network adequacy analysis

#### Column Reference

| Column           | Type      | Description                                                  |
| ---------------- | --------- | ------------------------------------------------------------ |
| plan\_year       | NUMBER    | Plan year                                                    |
| state\_code      | VARCHAR   | Two-letter state abbreviation                                |
| issuer\_id       | NUMBER    | CMS-assigned issuer identifier                               |
| network\_id      | VARCHAR   | Network identifier                                           |
| network\_name    | VARCHAR   | Consumer-facing network name                                 |
| network\_url     | VARCHAR   | URL to the provider directory                                |
| feed\_id         | VARCHAR   | FK to FEEDS — identifies which dataset this row belongs to   |
| feeds\_files\_id | VARCHAR   | FK to FEEDS\_FILES — identifies which batch loaded this data |
| created\_at      | TIMESTAMP | When the source data was loaded into the warehouse           |
| updated\_at      | TIMESTAMP | When the table was last rebuilt                              |

## Data Quality

### Standardization

* **Column normalization**: CMS PUF column names are standardized to snake\_case across all plan years
* **Type casting**: Premium amounts cast to DECIMAL; IDs maintained as VARCHAR for leading-zero preservation
* **Schema alignment**: PY2014-PY2022 schema differences (TIN/HPID removal, DesignType addition) are normalized to the current schema
* **UTF-8 BOM handling**: Some plan years include BOM markers that are stripped during extraction
* **Quoting consistency**: CSV quoting changed between PY2020 (quoted) and PY2022+ (unquoted) — handled transparently

### Coverage Scope

Exchange PUFs cover states on the **Federally Facilitated Exchange (FFE)** and **State-Based Exchanges on the Federal Platform (SBE-FP)** — approximately 30 states for PY2026. The following 21 states run fully independent State-Based Exchanges (SBEs) and are **not included**: CA, CO, CT, DC, ID, KY, ME, MD, MA, MN, NV, NJ, NM, NY, OR, PA, RI, VT, VA, WA.

### Data Freshness

Check when data was last updated:

```sql
SELECT source_name, data_period, row_count, created_at, is_latest
FROM DWV.FEEDS_FILES
WHERE is_latest = TRUE;
```

## Getting Started

### Platform Schema Reference

This dataset is available on both Snowflake and Databricks. Queries use schema-only references — the database is already set by the share or catalog context:

| Platform       | Schema                | Example                     |
| -------------- | --------------------- | --------------------------- |
| **Snowflake**  | `DWV`                 | `DWV.RATES`                 |
| **Databricks** | `aca_marketplace_dwv` | `aca_marketplace_dwv.rates` |

### Discover Available Data

Start with the `FEEDS` table to see what's available, and `FEEDS_FILES` to understand data freshness and load history.

{% tabs %}
{% tab title="Snowflake" %}

```sql
-- Browse the data catalog
SELECT table_name, description, latest_data_period,
       latest_row_count, last_loaded_at, total_batches
FROM DWV.FEEDS
ORDER BY table_name;
```

{% endtab %}

{% tab title="Databricks" %}

```sql
-- Browse the data catalog
SELECT table_name, description, latest_data_period,
       latest_row_count, last_loaded_at, total_batches
FROM aca_marketplace_dwv.feeds
ORDER BY table_name;
```

{% endtab %}
{% endtabs %}

{% tabs %}
{% tab title="Snowflake" %}

```sql
-- Check data freshness per source
-- is_latest = TRUE marks the current batch for each source
SELECT source_name, data_period, row_count, row_count_delta,
       created_at AS loaded_at, is_latest
FROM DWV.FEEDS_FILES
ORDER BY source_name, created_at DESC;
```

{% endtab %}

{% tab title="Databricks" %}

```sql
-- Check data freshness per source
SELECT source_name, data_period, row_count, row_count_delta,
       created_at AS loaded_at, is_latest
FROM aca_marketplace_dwv.feeds_files
ORDER BY source_name, created_at DESC;
```

{% endtab %}
{% endtabs %}

### Working with Data Lineage

Every data row links to `FEEDS_FILES` via `feeds_files_id`, which tells you exactly which batch loaded that data. Use this to filter to the current data version or trace any row back to its source load.

{% tabs %}
{% tab title="Snowflake" %}

```sql
-- Get current rate data with batch lineage
SELECT
    r.state_code,
    r.plan_id,
    r.rating_area_id,
    r.age,
    r.individual_rate,
    r.plan_year,
    ff.data_period,
    ff.created_at AS data_loaded_at
FROM DWV.RATES r
JOIN DWV.FEEDS_FILES ff ON r.feeds_files_id = ff.id
WHERE ff.is_latest = TRUE
  AND r.plan_year = 2026
  AND r.age = '40'
  AND r.tobacco = 'No Preference'
ORDER BY r.individual_rate DESC
LIMIT 20;
```

{% endtab %}

{% tab title="Databricks" %}

```sql
-- Get current rate data with batch lineage
SELECT
    r.state_code,
    r.plan_id,
    r.rating_area_id,
    r.age,
    r.individual_rate,
    r.plan_year,
    ff.data_period,
    ff.created_at AS data_loaded_at
FROM aca_marketplace_dwv.rates r
JOIN aca_marketplace_dwv.feeds_files ff ON r.feeds_files_id = ff.id
WHERE ff.is_latest = TRUE
  AND r.plan_year = 2026
  AND r.age = '40'
  AND r.tobacco = 'No Preference'
ORDER BY r.individual_rate DESC
LIMIT 20;
```

{% endtab %}
{% endtabs %}

### Premium Rate Benchmarking

{% tabs %}
{% tab title="Snowflake" %}

```sql
-- Average premiums by metal level and state for a 40-year-old
-- Join RATES to base plan variant (%-00) via standard_component_id
SELECT
    r.state_code,
    pa.metal_level,
    COUNT(DISTINCT r.plan_id) AS plan_count,
    ROUND(AVG(r.individual_rate), 2) AS avg_premium,
    ROUND(MIN(r.individual_rate), 2) AS min_premium,
    ROUND(MAX(r.individual_rate), 2) AS max_premium
FROM DWV.RATES r
JOIN DWV.FEEDS_FILES ff_r ON r.feeds_files_id = ff_r.id AND ff_r.is_latest = TRUE
JOIN DWV.PLAN_ATTRIBUTES pa
  ON r.plan_id = pa.standard_component_id
  AND r.plan_year = pa.plan_year
  AND pa.plan_id LIKE '%-00'
JOIN DWV.FEEDS_FILES ff_pa ON pa.feeds_files_id = ff_pa.id AND ff_pa.is_latest = TRUE
WHERE r.plan_year = 2026
  AND r.age = '40'
  AND r.tobacco = 'No Preference'
  AND pa.metal_level IN ('Bronze', 'Silver', 'Gold', 'Platinum')
GROUP BY r.state_code, pa.metal_level
ORDER BY r.state_code, pa.metal_level;
```

{% endtab %}

{% tab title="Databricks" %}

```sql
-- Average premiums by metal level and state for a 40-year-old
SELECT
    r.state_code,
    pa.metal_level,
    COUNT(DISTINCT r.plan_id) AS plan_count,
    ROUND(AVG(r.individual_rate), 2) AS avg_premium,
    ROUND(MIN(r.individual_rate), 2) AS min_premium,
    ROUND(MAX(r.individual_rate), 2) AS max_premium
FROM aca_marketplace_dwv.rates r
JOIN aca_marketplace_dwv.feeds_files ff_r ON r.feeds_files_id = ff_r.id AND ff_r.is_latest = TRUE
JOIN aca_marketplace_dwv.plan_attributes pa
  ON r.plan_id = pa.standard_component_id
  AND r.plan_year = pa.plan_year
  AND pa.plan_id LIKE '%-00'
JOIN aca_marketplace_dwv.feeds_files ff_pa ON pa.feeds_files_id = ff_pa.id AND ff_pa.is_latest = TRUE
WHERE r.plan_year = 2026
  AND r.age = '40'
  AND r.tobacco = 'No Preference'
  AND pa.metal_level IN ('Bronze', 'Silver', 'Gold', 'Platinum')
GROUP BY r.state_code, pa.metal_level
ORDER BY r.state_code, pa.metal_level;
```

{% endtab %}
{% endtabs %}

### Premium Trend Analysis (Multi-Year)

{% tabs %}
{% tab title="Snowflake" %}

```sql
-- Track average Silver plan premiums over time for a 40-year-old
-- Uses all 13 years of historical data (PY2014-PY2026)
SELECT
    r.plan_year,
    COUNT(DISTINCT r.state_code) AS states,
    COUNT(DISTINCT r.plan_id) AS silver_plans,
    ROUND(AVG(r.individual_rate), 2) AS avg_silver_premium,
    ROUND(MEDIAN(r.individual_rate), 2) AS median_silver_premium
FROM DWV.RATES r
JOIN DWV.PLAN_ATTRIBUTES pa
  ON r.plan_id = pa.standard_component_id
  AND r.plan_year = pa.plan_year
  AND pa.plan_id LIKE '%-00'
WHERE r.age = '40'
  AND r.tobacco = 'No Preference'
  AND pa.metal_level = 'Silver'
GROUP BY r.plan_year
ORDER BY r.plan_year;
```

{% endtab %}

{% tab title="Databricks" %}

```sql
-- Track average Silver plan premiums over time for a 40-year-old
SELECT
    r.plan_year,
    COUNT(DISTINCT r.state_code) AS states,
    COUNT(DISTINCT r.plan_id) AS silver_plans,
    ROUND(AVG(r.individual_rate), 2) AS avg_silver_premium,
    ROUND(PERCENTILE(r.individual_rate, 0.5), 2) AS median_silver_premium
FROM aca_marketplace_dwv.rates r
JOIN aca_marketplace_dwv.plan_attributes pa
  ON r.plan_id = pa.standard_component_id
  AND r.plan_year = pa.plan_year
  AND pa.plan_id LIKE '%-00'
WHERE r.age = '40'
  AND r.tobacco = 'No Preference'
  AND pa.metal_level = 'Silver'
GROUP BY r.plan_year
ORDER BY r.plan_year;
```

{% endtab %}
{% endtabs %}

### Market Concentration Analysis

{% tabs %}
{% tab title="Snowflake" %}

```sql
-- Issuer competition by state: count issuers and plans per metal level
-- Filter to base variant (%-00) to count distinct plans, not CSR variants
SELECT
    pa.state_code,
    pa.metal_level,
    COUNT(DISTINCT pa.issuer_id) AS issuer_count,
    COUNT(DISTINCT pa.standard_component_id) AS plan_count,
    CASE
        WHEN COUNT(DISTINCT pa.issuer_id) = 1 THEN 'Monopoly'
        WHEN COUNT(DISTINCT pa.issuer_id) <= 3 THEN 'Limited'
        ELSE 'Competitive'
    END AS market_type
FROM DWV.PLAN_ATTRIBUTES pa
JOIN DWV.FEEDS_FILES ff ON pa.feeds_files_id = ff.id AND ff.is_latest = TRUE
WHERE pa.plan_year = 2026
  AND pa.metal_level IN ('Bronze', 'Silver', 'Gold', 'Platinum')
  AND pa.plan_id LIKE '%-00'
GROUP BY pa.state_code, pa.metal_level
ORDER BY issuer_count ASC, pa.state_code;
```

{% endtab %}

{% tab title="Databricks" %}

```sql
-- Issuer competition by state
SELECT
    pa.state_code,
    pa.metal_level,
    COUNT(DISTINCT pa.issuer_id) AS issuer_count,
    COUNT(DISTINCT pa.standard_component_id) AS plan_count,
    CASE
        WHEN COUNT(DISTINCT pa.issuer_id) = 1 THEN 'Monopoly'
        WHEN COUNT(DISTINCT pa.issuer_id) <= 3 THEN 'Limited'
        ELSE 'Competitive'
    END AS market_type
FROM aca_marketplace_dwv.plan_attributes pa
JOIN aca_marketplace_dwv.feeds_files ff ON pa.feeds_files_id = ff.id AND ff.is_latest = TRUE
WHERE pa.plan_year = 2026
  AND pa.metal_level IN ('Bronze', 'Silver', 'Gold', 'Platinum')
  AND pa.plan_id LIKE '%-00'
GROUP BY pa.state_code, pa.metal_level
ORDER BY issuer_count ASC, pa.state_code;
```

{% endtab %}
{% endtabs %}

### Plan Continuity Tracking

{% tabs %}
{% tab title="Snowflake" %}

```sql
-- Plan continuity analysis: renewals vs discontinuations by state
-- PLAN_CROSSWALK maps prior-year plan IDs to current-year IDs
SELECT
    cw.state,
    COUNT(*) AS total_mappings,
    COUNT(DISTINCT cw.plan_id_prior) AS prior_plans,
    COUNT(DISTINCT cw.plan_id_current) AS current_plans,
    COUNT(CASE WHEN cw.metal_level_prior <> cw.metal_level_current THEN 1 END) AS metal_level_changes
FROM DWV.PLAN_CROSSWALK cw
JOIN DWV.FEEDS_FILES ff ON cw.feeds_files_id = ff.id AND ff.is_latest = TRUE
GROUP BY cw.state
ORDER BY cw.state;
```

{% endtab %}

{% tab title="Databricks" %}

```sql
-- Plan continuity analysis: renewals vs discontinuations by state
SELECT
    cw.state,
    COUNT(*) AS total_mappings,
    COUNT(DISTINCT cw.plan_id_prior) AS prior_plans,
    COUNT(DISTINCT cw.plan_id_current) AS current_plans,
    COUNT(CASE WHEN cw.metal_level_prior <> cw.metal_level_current THEN 1 END) AS metal_level_changes
FROM aca_marketplace_dwv.plan_crosswalk cw
JOIN aca_marketplace_dwv.feeds_files ff ON cw.feeds_files_id = ff.id AND ff.is_latest = TRUE
GROUP BY cw.state
ORDER BY cw.state;
```

{% endtab %}
{% endtabs %}

### Benefit Cost Comparison

{% tabs %}
{% tab title="Snowflake" %}

```sql
-- Compare primary care visit copays across Silver plans in a state
SELECT
    pa.plan_marketing_name,
    pa.plan_type,
    pa.issuer_id,
    bcs.copay_inn_tier1 AS primary_care_copay,
    bcs.coins_inn_tier1 AS primary_care_coinsurance,
    bcs.is_ehb
FROM DWV.BENEFITS_COST_SHARING bcs
JOIN DWV.FEEDS_FILES ff_bcs ON bcs.feeds_files_id = ff_bcs.id AND ff_bcs.is_latest = TRUE
JOIN DWV.PLAN_ATTRIBUTES pa ON bcs.plan_id = pa.plan_id AND bcs.plan_year = pa.plan_year
JOIN DWV.FEEDS_FILES ff_pa ON pa.feeds_files_id = ff_pa.id AND ff_pa.is_latest = TRUE
WHERE bcs.plan_year = 2026
  AND bcs.state_code = 'TX'
  AND pa.metal_level = 'Silver'
  AND bcs.benefit_name = 'Primary Care Visit to Treat an Injury or Illness'
  AND bcs.is_covered = 'Covered'
ORDER BY bcs.copay_inn_tier1;
```

{% endtab %}

{% tab title="Databricks" %}

```sql
-- Compare primary care visit copays across Silver plans in Texas
SELECT
    pa.plan_marketing_name,
    pa.plan_type,
    pa.issuer_id,
    bcs.copay_inn_tier1 AS primary_care_copay,
    bcs.coins_inn_tier1 AS primary_care_coinsurance,
    bcs.is_ehb
FROM aca_marketplace_dwv.benefits_cost_sharing bcs
JOIN aca_marketplace_dwv.feeds_files ff_bcs ON bcs.feeds_files_id = ff_bcs.id AND ff_bcs.is_latest = TRUE
JOIN aca_marketplace_dwv.plan_attributes pa ON bcs.plan_id = pa.plan_id AND bcs.plan_year = pa.plan_year
JOIN aca_marketplace_dwv.feeds_files ff_pa ON pa.feeds_files_id = ff_pa.id AND ff_pa.is_latest = TRUE
WHERE bcs.plan_year = 2026
  AND bcs.state_code = 'TX'
  AND pa.metal_level = 'Silver'
  AND bcs.benefit_name = 'Primary Care Visit to Treat an Injury or Illness'
  AND bcs.is_covered = 'Covered'
ORDER BY bcs.copay_inn_tier1;
```

{% endtab %}
{% endtabs %}

### Tracking Data Changes Over Time

`FEEDS_FILES` records every batch load with `row_count_delta` showing what changed. Use this to monitor source data updates and understand how the dataset evolves.

{% tabs %}
{% tab title="Snowflake" %}

```sql
-- Track how rates data has evolved across batches
-- row_count_delta shows net row changes per load
SELECT
    source_name,
    data_period,
    row_count,
    row_count_delta,
    created_at AS loaded_at,
    is_latest
FROM DWV.FEEDS_FILES
WHERE source_name = 'rates'
ORDER BY created_at;
```

{% endtab %}

{% tab title="Databricks" %}

```sql
-- Track how Rate PUF data has evolved across batches
SELECT
    source_name,
    data_period,
    row_count,
    row_count_delta,
    created_at AS loaded_at,
    is_latest
FROM aca_marketplace_dwv.feeds_files
WHERE source_name = 'rates'
ORDER BY created_at;
```

{% endtab %}
{% endtabs %}

{% hint style="success" %}
**Ready to access ACA Marketplace data?**
{% endhint %}

| Platform       | Action                                              |
| -------------- | --------------------------------------------------- |
| **Snowflake**  | Coming soon to Snowflake Marketplace                |
| **Databricks** | [Free Trial](https://trial.dataplex-consulting.com) |

{% hint style="success" %}
Questions? [Contact our team](mailto:support@dataplex-consulting.com) for a walkthrough.
{% endhint %}

## Data Sources

* [CMS Health Insurance Exchange Public Use Files](https://www.cms.gov/marketplace/resources/data/public-use-files)
* [CMS State-Based Exchange PUFs](https://www.cms.gov/marketplace/resources/data/state-based-public-use-files)
* [Healthcare.gov QHP Landscape Files](https://data.healthcare.gov/qhp-landscape-files)
* [CMS Marketplace API Documentation](https://developer.cms.gov/marketplace-api/)