# 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](/files/8JeEqHxDqwjlmeMyrBed)

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/)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.dataplex-consulting.com/data-catalog/aca-marketplace-dataset.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.