Pipeline

The Consumer Pricing Service is a pricing layer on top of Clear Rates. It will soon include PET data as well.

Clear Rates Integration

Clear Rates one-liner

Clear Rates stores a rate for each payer, network, provider, fee type, and billing code combination.

The pipeline consists of 5 steps:

1. Get SSP Inputs
2. Pull in Clear Rates
3. Map Professional Fees to Facilities
4. pricing_service_rates: Line-item level outputs
5. pricing_service_ssp: SSP-level case rates for consumer app

First, we filter Clear Rates to the codes that are relevant for pricing SSPs. Next, since Clear Rates stores professional fees at the Physician Group level, we need to map professional fees to facilities. Finally, we output two tables: one at the line-item level with all rates, and one at the SSP level with SSP prices.

---

1) ssp_inputs.sql

Creates: {{ ps_schema }}.pricing_service_inputs_ssp_lines_{{ ps_month }}.

Reads: SSP tables (sub-packages, contents)

What it does

• Pulls only the SSP line items relevant to the Pricing Service, across outpatient HCPCS packages and inpatient MS-DRGs.
• Applies average_units heuristic (1 for base codes or if units ≤ 1; otherwise use the value from SSP).
• Emergency department codes are explicitly excluded.

Example output rows:

ssp_id	ssp_name	sub_package_id	line_code	line_code_type	average_units	fee_type
DE000	Percutaneous Breast Biopsy	186DE	A4648	HCPCS	1	facility
DE000	Percutaneous Breast Biopsy	222DE	77065	HCPCS	1	professional
DE000	Percutaneous Breast Biopsy	222DE	A9585	HCPCS	80	facility
DE000	Percutaneous Breast Biopsy	186DE	77065	HCPCS	1	facility
DE000	Percutaneous Breast Biopsy	221DE	77065	HCPCS	1	facility
DE000	Percutaneous Breast Biopsy	221DE	77065	HCPCS	1	professional
DE000	Percutaneous Breast Biopsy	222DE	19085	HCPCS	1	professional
DE000	Percutaneous Breast Biopsy	221DE	A4648	HCPCS	1	facility
DE000	Percutaneous Breast Biopsy	186DE	19081	HCPCS	1	facility
DE000	Percutaneous Breast Biopsy	222DE	A4648	HCPCS	1	facility

---

2) clear_rates.sql

Creates: {{ ps_schema }}.clear_rates_enhanced_{{ ps_month }}
Reads:

• pricing_service_inputs_ssp_lines_{{ ps_month }} (from step 1)
• Clear Rates table + NYC DOH custom build

What it does

• Loads Clear Rates, filtered to the SSP line items. This gives us rates for each line item, per payer/network/provider.

Key details

• Outlier handling: keeps canonical_rate only when rate lies within CLD's lower_bound and outer_bound. If bounds do not exist, rate must be within 0.9×state_avg_medicare and 10×state_avg_medicare. CLD Outlier Methodology

Logic Differences vs Clear Rates

See Clear Rates Logic Divergence

Example output rows (same table as above, with additional columns):

sub_package_id	line_code	fee_type	payer	network	provider	rate
186DE	A4648	facility	UHC	CHOICE	Sinai	41
222DE	77065	professional	UHC	CHOICE	Sinai	55
222DE	A9585	facility	UHC	CHOICE	Sinai	41
186DE	77065	facility	UHC	CHOICE	Sinai	42
221DE	77065	facility	UHC	CHOICE	Sinai	1
221DE	77065	professional	UHC	CHOICE	Sinai	4
222DE	19085	professional	UHC	CHOICE	Sinai	43
221DE	A4648	facility	UHC	CHOICE	Sinai	20
186DE	19081	facility	UHC	CHOICE	Sinai	1000
222DE	A4648	facility	UHC	CHOICE	Sinai	4

---

3) professional.sql

Creates: {{ ps_schema }}.professional_cld_enhanced_{{ ps_month }}

What it does

• Maps Physician Group (PG) professional lines** to **Imaging Centers, ASCs, and Hospitals using provider crosswalks.

Key details

• Clear Rates stores professional fees at the Physician Group (PG) level.
• How the mapping works:
  • For each facility type (Imaging Center, ASC, Hospital): use Definitive table to identify affiliated Physicians
  • Then use Clear Rates PG spines to identify each Physician's affiliated Physician Groups
• In case of multiple PGs per facility, we take the highest Physician Group rate. (We're not using mode because of (1) "up to" language on consumer and (2) we're not selecting rates among HCPs, but PGs). But Mode may make sense too.
• Where a direct provider-level match is missing, it backs off to geo averages (CBSA if possible, otherwise state).

---

4) final_rates.sql

Creates: {{ ps_schema }}.pricing_service_rates_{{ ps_month }}

What it does

• Combines facility and mapped professional rows into one rates table
• Computes allocations of facility case rates to non-base line items for cost share purposes

Key details

• Allocation logic:
  • For Pricing Service, we only need the price of the base code to represent the facility portion of the SSP, since it's a case rate. However, Cost Share requires line-item level prices to be available for ALL facility line items. In case of missing data, we impute by computing an "allocation" factor:
    • allocation = avg_price of line_item / SUM(avg_price of sub_package)
    • imputed rate = case_rate × allocation
    • see example here

---

5) final_ssp.sql

Creates: {{ ps_schema }}.pricing_service_ssp_{{ ps_month }}

What it does

• Aggregates rates to SSP-level “case rate” per network/provider and picks the sub-package with highest total price per SSP-payer-network-provider.

Key details

• Aggregates rates to SSP-level “case rate” per network/provider by:
  • Inpatient:
    • selecting the base facility line item for facility portion
  • Outpatient
    • Hospitals + ASCs, take the sum of:
      • selecting the base facility line item for facility portion
      • summing professional fees
    • Imaging Centers:
      • summing professional fees only
• For each SSP-payer-network-provider picks the sub-package with highest total price
• Cash:
  • Cash prices have (payer_id='0', network_id=0)
  • Cash Price must be less than gross charge

PETs

[To be added.]