Skip to main content

Pipeline

This page offers a more technical overview of the DAG flow.

Task Groups:​

  1. SSP Template Generation: Generate SSP variant claim templates
  2. Data Preparation: Prepare hospital data and charge data
  3. Medicare Pricing Processing: Call Medicare API to get pricing
  4. Database Operations: Create rates tables
  5. QA Tests: Run data quality checks

1. SSP Template Generation​

warning

AS of 11/17/2025, we excluded PETs from Eli Lilly episodes. However, we expect that they may be added back in the future. "Subvariant" logic is only relevant for PETs.

Steps in this Task Group:

  1. get_ssp_variants_data() is used to construct variants. The SSP schemas do not have a variant-level table, and instead, store variations in a field optional_line_code_replacements. So we explode this field to create variants for each subpackage. Note that this is only relevant for PETs.
  2. generate claim templates for each provider_type and subpackage variant.
  3. store claim templates in s3: s3://turquoise-health-payer-export-main/eli_lilly/claims_templates/
  4. validate s3 templates to ensure that they are well-formed JSON and have the expected structure

The purpose of this section is to build claim templates from SSP variants. Here are examples of claim templates that we want to generate. Then these are used to submit calls to the Medicare API pricer, editing the provider-specific components:

{
  "ssp_id": "RA005",
  "ssp_name": "MRI without contrast",
  "sub_package_id": "161RA",
  "sub_package_variant_id": "161RA",
  "claim_id": null,
  "bill_type": "131",
  "billing_class": "facility",
  "ptan": null,
  "service_start": "2025-03-14",
  "covered_charges": null,
  "patient_age": 65,
  "patient_status": 0,
  "billing_npi": null,
  "line_items": [
    {
      "claim_line_id": "1",
      "hcpcs": "70551",
      "service_date": "2025-03-14",
      "covered_charges": null,
      "non_covered_charges": null,
      "revenue_code": "0351",
      "modifiers": [
        "",
        "",
        "",
        ""
      ],
      "units": 1
    }
  ],
  "patient_gender": 0
}

2. Data Preparation​

Steps in this Task Group:

  1. Load Provider Data
    • The list of HCOs and Imaging Centers are stored in a CSV file in dags/consumer_engineering/eli_lilly_pricing/data/Medicare NPI Enrollment - All Facilities with Sutter - 11022025.csv
    • Infusion Centers are stored in tq_dev.internal_dev_csong_spines.eli_lilly_spines_infusion_centers
  2. Save Provider Data to Trino
    • HCOs, Imaging Centers, and Infusion Centers are stored in tq_dev.internal_dev_csong_consumer_pricing_service.eli_lilly_hospitals_{version}
  3. Get Charges from Clear Rates for each Hospital and Line Code
  4. Create Network Status Indicators
    • create_mrf_coverage_table: generates network status indicators based on MRF data
    • create_komodo_coverage_table: generates network status indicators based on Komodo claims data
    • create_network_status_wide: combines MRF and Komodo indicators into a wide format
    • create_network_status_long: converts MRF and Komodo indicators into a long format

3. Medicare Pricing Processing​

  1. For each provider, complete each claim template with the provider's details and gross charges from Clear Rates.
  2. Call the Medicare API to retrieve the pricing rates.
  3. Store the results in S3: s3://turquoise-health-payer-export-main/eli_lilly/claims/
    • Each YYYY-MM-DD folder contains requests and priced
      • requests: stores the completed claim templates submitted to the Medicare API
      • priced: stores the responses from the Medicare API pricer

4. Database Operations​

Steps in this Task Group:

  1. Create Variants table and Load Priced Variants to Database
    • Initializes variants tables and for each priced claim from the Medicare API, extracts variant-level pricing and adds it to the table.
  2. Create MPFS table
    • For hospital outpatient claims, we use the Medicare API for OPPS pricing, but use MPFS reference data for the professional fees. In this step, we create a table that contains MPFS pricing for hospitals based on their location.
  3. Create Sub-Packages table
    • for each sub-package, select the sub-package-variant ID with the highest price
  4. Create Medicare base table
    • joins the variants table with the sub-packages table to create a base rates table
    • duplicates SSP UN000 with episode ID AZ-002, creating a "infusion-only" episode
  5. Create Rates Table
    • applies medicare advantage multipliers and network status indicators to create Medicare Advantage rates
  6. Create Clickhouse-ready Table
    • schema changes to comply with Clickhouse requirements

5. QA Tests​

See the QA Tests documentation for details on the data quality checks performed in this Task Group.

Additional Resources:​

On this page: