Pipeline

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

Clear Rates

Clear Rates contains a rate for each payer, network, provider, fee type, and billing code combination. 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.

The pipeline consists of 5 main steps:

1. 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

---

1) ssp_inputs.sql

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

Reads: SSP tables (sub-packages, contents, history)

What it does

• Pulls only the SSP line items relevant to the Pricing Service, across outpatient HCPCS packages and inpatient MS-DRGs (OB bundles).
• 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 (high level)

• Loads Clear Rates, filtered to the SSP line items

Key details

• Outlier handling: keeps canonical_rate only when canonical_rate_score > 1 and rate lies within [lower_bound or 0.9×state_avg_medicare, upper_bound or 10×state_avg_medicare].
• Fee type normalization: derives fee_type from bill_type
  • Inpatient/Outpatient → facility
  • Professional → professional (facility = True)
• Join to SSP lines: matches on {fee_type, billing_code, billing_code_type} to keep only SSP-relevant lines and carry in average_units, fee_type, is_package_base_code.

Logic Differences vs Clear Rates

• Outlier handling adds an additional constraint that the canonical_rate always falls between the Clear Rates lower/upper bounds. This is different from Clear Rates, which may allow rates outside the bounds if the rate is validated (score = 5) or if the rate is derived from a Percent-of-Charge methodology with a hospital-reported gross charge.

---

3) professional.sql

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

What it does (high level)

• 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/state).

---

4) final_rates.sql

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

What it does (high level)

• Combines facility and mapped professional rows into one rates fact 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
• Hospital/ASC vs Imaging Centers
  • Hospitals/ASCs: facility + professional fees are summed for total price
  • Imaging Centers: professional fees only
• Outputs for Cost Share:
  • price: line-item price (professional uses negotiated×units×anesthesia; base facility uses case_rate; non-base facility is NULL pre-allocation).
  • allocated_price: COALESCE(case_rate, prof price) × allocation (or 1 for base/prof). Filters to values > 1 or raw negotiated/cash > 1.
• Compatibility shims:
  • temporary legacy payer/plan alias mappings for downstream Consumer app parity (plan_alias, hosp_payer_id).

---

5) final_ssp.sql

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

What it does (high level)

• 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.]