Manual Edits and Hardcoded Logic
This page catalogs every manual override, hardcoded constant, and special-case rule in the SSP pricing DAG. Use it as a reference when debugging unexpected prices or when a new SSP requires custom handling.
All file paths below are relative to
airflow_dags/dags/data_science/ssp/ssp_pricing/.
1. Revenue Code Overrides
What it does: Forces specific HCPCS codes to map to a different revenue code than what claims data or the RC-HCPCS crosswalk would assign.
Where: sql/line_codes/institutional.sql — rc_overrides CTE.
| HCPCS code | Overridden RC | RC family |
|---|---|---|
| 44390 | 0750 | Gastrointestinal Services |
| 21820 | 0450 | Emergency Room |
Why: Claims data sometimes maps these codes to incorrect or inconsistent revenue code families. The overrides pin them to the correct RC family for charge allocation.
How to modify: Add or remove rows in the rc_overrides CTE
VALUES list: (line_code, revenue_code) pairs.
2. NS_ SSP Revenue Code Override
What it does: For SSPs whose ssp_grouper contains ns_
(non-surgical), any anchor code that would be mapped to Operating
Room (0360-0369) is rewritten to Emergency Room (0450-0459).
Where:
sql/line_codes/institutional.sql—CASE WHEN g.ssp_grouper LIKE '%ns_%'in the outerSELECT.sql/claims/rc_family_proportions.sql—merged_rev_familiesCTE applies the same mapping before the final aggregation so the proportions table is consistent.
Why: Non-surgical SSPs should not allocate institutional charges to Operating Room RC families; they are typically performed in an ER or procedure suite, so ER is the appropriate RC family for the anchor code.
How to modify: Edit the CASE WHEN expression in the outer
SELECT of sql/line_codes/institutional.sql and the
merged_rev_families CTE in sql/claims/rc_family_proportions.sql.
Both check ssp_grouper LIKE '%ns_%' and map 0360-0369 → 0450-0459.
3. Carve-Out Rules
What it does: Determines which revenue code families are priced as additive carve-outs (separate from the base allocation) and which participate in the proportional base allocation.
Where:
- Drug/Implant labeling —
sql/line_codes/institutional.sql(implant_codes,asp_drugs,carved_out_candidatesCTEs and the labelCASE). - Carve-out eligibility + pricing —
sql/fee_schedules/rc_family_allocation.sql(carveout_familiesCTE and final allocationCASEs).
Pharmacy (0630-0639)
- Only qualifies as a carve-out if
rc_family_avg_line_chargeexceeds $2,000. - Below the threshold, Pharmacy participates in the base allocation like any other RC family.
- Carve-out lines are labeled
Carved Out: Drug.
Med/Surg Supplies (0270-0279)
- Completely excluded from the base allocation.
- Only appears if the SSP has line codes with the
Carved Out: Implantlabel (implant_codesCTE — C-codes, Q-codes, L-codes fromref_aapc_cpthierarchy).
Carve-out pricing
All carve-out families are priced at:
allocated_price = rc_family_avg_line_charge × 0.5
Base (non-carve-out) families are priced at:
allocated_price = fee_schedule_price × charge_allocation
charge_allocation is the family's proportional share of total
rc_family_avg_line_charge across all base families (sums to 1.0
within each SSP).
Drug/Implant classification
| Label | Source |
|---|---|
Carved Out: Drug | asp_reference_pricing (ASP drugs) and avg_line_charge > 1000 on drug revenue code 0636 |
Carved Out: Implant | ref_aapc_cpthierarchy (C-codes for devices, Q-codes for skin substitutes, L-codes for prosthetics) and line on an implant revenue code (0275 pacemakers or 0278 other implants) |
How to modify:
- Change the $2,000 pharmacy threshold in the
carveout_familiesCTE (ilc.rc_family_avg_line_charge > 2000). - Change the $1,000 drug candidate threshold in the
carved_out_candidatesCTE (avg_line_charge > 1000). - Change the 50% carve-out pricing factor in the allocation
CASEs. - Add or remove implant device categories in the
implant_codesCTE.
4. NCCI Manual Overrides
What it does: Adds manually defined mutually exclusive code pairs to the NCCI PTP edit graph. Unioned into the edge set used by the Union-Find algorithm that clusters line codes into NCCI groups.
Where: tasks/line_codes.py — _MANUAL_ME_PAIRS list.
| Code A | Code B | Description |
|---|---|---|
| 00811 | 00812 | Lower GI anesthesia vs screening colonoscopy anesthesia |
Why: Some code pairs are clinically mutually exclusive (a provider would never bill both on the same encounter) but aren't flagged in CMS NCCI PTP. Without this override both codes would sit in separate NCCI groups and be summed, double-counting the anesthesia fee.
How to modify: Add tuples to the _MANUAL_ME_PAIRS list. Each
tuple is (code_a, code_b). Both directions are added to the lookup
set automatically.
5. Combo SSP Definitions
What it does: Creates combination SSPs that represent two procedures performed together, using multiple-procedure pricing logic (100% of the higher-priced SSP + 50% of the lower-priced SSP).
Where:
- Fee-schedule insert —
sql/fee_schedules/combo_ssps_insert.sqlandsql/fee_schedules/combo_ssps_insert_subcategory.sql. - Pre-insert cleanup —
sql/fee_schedules/combo_ssps_delete.sql,combo_ssps_delete_subcategory.sql. - Airflow wiring —
tasks/fee_schedules.py::build_combo_ssps.
Current combo SSPs
| Combo SSP | Source A | Source B | POS |
|---|---|---|---|
GA.2.colonoscopy_and_egd | GA.0.colonoscopy | GA.0.egd | OP |
The combo SSP has a single subcategory (sub_category = '0') and no
subcategory tiers.
Multiple-procedure logic
For each provider_id the SQL compares inst_price + prof_price
between SSP A and SSP B. The higher-total SSP gets factor 1.0
(primary); the lower-total gets factor 0.5 (secondary). Every price
column is computed as a_factor * A + b_factor * B.
Hardcoded institutional line codes
Where: sql/claims/discover_institutional.sql — combo-SSP
VALUES block at the tail.
| Base code | Line codes |
|---|---|
43250 + 45384 | 45384, 0750, 43250, 0310, 88305, 43239, J2704, 0636, J7120 |
43251 + 45385 | 0750, 43251, 45385, 0312, 88305, 0636, J2704, 43239, 45380, J7120 |
Hardcoded professional line codes
Where: sql/claims/discover_professional.sql — combo-SSP VALUES
block at the tail.
| Base code | Line codes |
|---|---|
43250 + 45384 | 43250, 45384, 43239, 00813, 88305 |
43251 + 45385 | 45385, 43251, 43239, 00813, 88305, 45380 |
Why: Combo SSPs cannot be derived from claims the same way
standard SSPs are (the two procedures share encounters). Line codes
are manually curated as the union of the two source SSPs with combo
base codes (43250 + 45384, 43251 + 45385) used as identifiers.
How to modify:
- To add a new combo SSP: add a second insert pair under
sql/fee_schedules/mirroringcombo_ssps_insert{,_subcategory}.sqlwith newSSP_A/SSP_B/COMBO_SSPliterals, register the combo in theVALUESblocks of bothdiscover_institutional.sqlanddiscover_professional.sql, register the combo SSP inssp_groupings, and add the matching delete stub + Airflow task totasks/fee_schedules.build_combo_ssps. - To change line codes for an existing combo: edit the
VALUESblocks in the twodiscover_*.sqlfiles.
6. Excluded Line Codes
What it does: Globally excludes specific codes from appearing as claims-derived line codes.
Where: sql/claims/excluded_line_codes.sql — a single
CREATE OR REPLACE TABLE excluded_line_codes_{version} … VALUES (…).
| Code | Description |
|---|---|
| 36415 | Venipuncture, routine |
| 36416 | Venipuncture, capillary |
| 36410 | Venipuncture, age 3+ |
| U0002 | COVID-19 test, non-CDC |
Why: These codes appear on nearly every encounter but are trivial, low-cost services that would inflate line code counts without adding pricing signal.
How to modify: Add or remove rows in the VALUES list. The table
is consumed by both discover_institutional.sql and
discover_professional.sql.
7. "Ends-in-F" CPT Category II filter
What it does: Drops any line code ending in F (CPT Category II
tracking codes) from claims discovery.
Where: sql/claims/discover_institutional.sql and
sql/claims/discover_professional.sql — inclusion CASE
(line_code NOT LIKE '%F').
Why: CPT Category II codes are non-billable performance-tracking
codes (typically four digits + F). They pollute the line-code
discovery with zero-price lines.
How to modify: Remove the NOT LIKE '%F' predicate to disable,
or narrow the pattern if a specific code is being incorrectly
excluded.
8. Anchor Must Have a Self-Billed Professional Fee
What it does: For CPT/HCPCS anchors, drops every
supplemented_sub_package_contents row (both institutional and
professional) unless the anchor itself appears as an included
professional line code (base_code = line_code). MS-DRG anchors are
exempt because the DRG never appears on a professional claim.
Where: sql/claims/combined_line_codes.sql — the
anchors_with_professional CTE and the AND m.base_code IN (SELECT base_code FROM anchors_with_professional) clause on both the
institutional and professional UNION branches.
Why: Ancillary pro codes (anesthesia, radiology, placeholder
unlisted codes like 53899) showing up on the professional side isn't
enough — the procedure itself has to be professionally billed or the
SSP has no usable professional fee and should drop out entirely.
How to modify: Edit the anchors_with_professional CTE. Relax the
g.type IN ('CPT', 'HCPCS') AND m.line_code = m.base_code predicate
to allow broader qualification, or drop the CTE to disable the filter
altogether.
9. I&S SSPs: Only the Anchor Counts
What it does: For I&S-category SSPs the combined line-code table
keeps only line_code = base_code rows; every other ancillary line is
suppressed.
Where: sql/claims/combined_line_codes.sql — the
AND NOT (a.category = 'I&S' AND m.line_code != m.base_code) clause
on both the institutional and professional UNION branches.
Why: I&S ("Individual & Separate") SSPs price individual services. Ancillary line codes don't apply regardless of how highly they associate in claims.
How to modify: Edit the predicate. To allow specific I&S SSPs to admit ancillaries, weaken the category test to an exclusion list.
10. I&S Ancillary Augmentation on the Professional Side
What it does: For every (base_code, line_code) in
manual_institutional_line_codes where the line code is an I&S SSP
anchor, synthesize an is_included = TRUE professional row so the
I&S procedure shows up on the pro side regardless of what claims
discovery produced.
Where: sql/claims/discover_professional.sql — final UNION ALL
branch. BOOL_OR(is_included) in the outer GROUP BY makes the
synthesized TRUE row win over any conflicting claims-derived FALSE.
Why: I&S SSPs (e.g. labs, radiology) routinely appear as ancillaries of other SSPs but may not meet the professional association-rate gate for themselves. Without the augmentation they would disappear from the professional side.
How to modify: Narrow the subquery that selects
category = 'I&S' anchor codes, or add more augmentation sources
as additional UNION ALL branches.
11. Revenue-Code Association Rate Gate
What it does: When resolving an institutional line code's
revenue-code family, the claims-derived revenue_code is only trusted
if it covers more than 30% of encounters carrying that line code.
Below that threshold the claims-derived RC is dropped and the
rc_overrides / revenue_codes dictionary / HCPCS-xwalk fallback
chain picks the RC.
Where: sql/line_codes/institutional.sql — ssp_groupings CTE,
CASE WHEN spc.revenue_code_association_rate > 0.30 THEN spc.revenue_code ELSE NULL END.
Why: Low association rates mean the line code's revenue code is unstable in claims; using it would propagate noise into the RC-family allocation. The fallback chain produces a more stable mapping.
How to modify: Adjust the 0.30 threshold.
12. Anesthesia Classification Authority
What it does: A code is only classified as Anesthesia service
type when it appears in anesthesia_reference_pricing (and isn't in
the exclusion list). spines_services_clinical_categories.category = 'Anesthesia' and services_spines_cleaned.ccs_category = 'Anesthesia'
fall-throughs are explicitly suppressed via NULLIF.
Where:
sql/claims/combined_line_codes.sql—service_typesCTE (NULLIF(cats.category, 'Anesthesia')in the ELSE arm).sql/line_codes/service_types.sql—WHEN ssc.ccs_category = 'Anesthesia'branch is removed and the final ELSE usesNULLIF(cats.category, 'Anesthesia').
Why: The spines categorization over-tagged non-anesthesia
procedural codes as Anesthesia. Restricting to
anesthesia_reference_pricing (CMS's anesthesia HCPCS universe) is
the authoritative list.
How to modify: If a code needs to be treated as Anesthesia but
isn't in the reference table, either add it to that reference feed
(preferred) or explicitly promote it via a WHEN before the
reference-pricing branch.
13. Configurable Pricing Factors (DAG params)
What it does: Defines the global constants used across the pipeline. In the DAG they are exposed as Airflow params; SQL templates read them via Jinja.
Where: __init__.py — DEFAULT_PARAMS dict.
| Param | Default | Usage |
|---|---|---|
base_rate | 500 | Denominator for relative-weight output (weight = price / base_rate) |
assistant_surgeon_factor | 0.16 | Assistant surgeon price = 16% of primary surgeon fee |
assistant_nonsurgeon_factor | 0.136 | Assistant non-surgeon price = 13.6% of primary surgeon fee |
crna_supervised_factor | 0.50 | Supervised CRNA price = 50% of anesthesiologist fee |
encounter_threshold | 0.3 | Minimum encounter association rate for claims-based line code discovery |
anesthesia_exclusion_codes | '99152','99153' | HCPCS codes excluded from the Anesthesia service-type branch |
labpath_radiology_codes | 33-code string | HCPCS codes force-classified Lab/Path or Radiology (see §14) |
Why: These factors are sourced from CMS payment policy (assistant
surgeon, CRNA medical-direction split), plus pipeline-engineering
choices for encounter_threshold (30% keeps only common co-billed
codes) and base_rate (arbitrary normalizing constant).
How to modify: Override at DAG run time via the Airflow UI params,
or change the default in DEFAULT_PARAMS. Changes propagate to every
SQL template on the next run.
14. LABPATH_RADIOLOGY_CODES
What it does: A hardcoded list of 33 HCPCS codes that should be classified as Lab/Path (via the service-type waterfall) but aren't captured by the CLFS or the spines Radiology category.
Where: __init__.py — labpath_radiology_codes entry in
DEFAULT_PARAMS.
Codes (33 total):
70481, 70487, 70491, 71047, 72083, 72132, 72148, 72158, 72193,
72197, 73502, 73522, 73701, 73722, 74177, 75572, 76801, 76818,
76827, 77002, 77062, 77066, 77076, 78071, 78433, 78815, 92133,
93307, 93351, 93923, 77372, 77386
Used by:
sql/claims/combined_line_codes.sql—service_typesCTE (forces Lab/Path classification when building the combined table).sql/line_codes/service_types.sql— waterfall step 3 (HCPCSIN ({{ labpath_radiology_codes }}) → 'Lab/Path').
Why: Some imaging and diagnostic codes (ultrasounds, nuclear medicine, certain interventional radiology codes) fall outside the standard CLFS and spines Radiology lookup. Without this list they would be classified as generic Professional and priced against the wrong fee schedule.
How to modify: Edit the labpath_radiology_codes default in
DEFAULT_PARAMS. The format is a comma-separated string of
single-quoted codes (used directly in SQL IN clauses).
15. Anchor Code Units Cap
What it does: When a line code is also an anchor code within the
same SSP, its units is capped at 1 regardless of the avg_units
value from avg_line_code_units / sub_package_contents. Anesthesia
codes get a time-based scaling (avg_units / 15, clamped to
≥ 1); all other non-anchor codes clamp to max(avg_units, 1).
Where: sql/line_codes/professional.sql — ssp_groupings_raw
CTE, units CASE expression.
Why: The anchor represents a single procedure occurrence.
Allowing avg_units > 1 would multiply the anchor price inside the
aggregation and inflate the schedule. Time-based anesthesia codes
genuinely scale with procedure duration, so they keep a unit scale.
How to modify: Edit the units CASE in the ssp_groupings_raw
CTE. Remove the first WHEN branch to disable the cap, or adjust the
15-minute divisor in the anesthesia branch.
16. Encounter-Level RC-Family Averaging
What it does: Controls how avg_line_charge is computed per
revenue code family. For encounter-level services, the average is
computed per encounter that carries the RC family (divide by
encounter_count). For all other families the total is divided by
total_encounters across the SSP.
Where: sql/claims/rc_family_proportions.sql — final SELECT,
avg_line_charge CASE expression.
| RC family description | Averaging method |
|---|---|
| Gastrointestinal Services | Per-encounter (total_charge / encounter_count) |
| Operating Room Services | Per-encounter |
| Emergency Room | Per-encounter |
| Clinic | Per-encounter |
| All others | Per-total-encounter (total_charge / total_encounters) |
Why: Some services are billed once per encounter (OR fee, ER visit
fee). Dividing their total charges by total_encounters would dilute
the average because not every encounter has that RC family. Other
services (labs, pharmacy) may appear on a subset of encounters but
are billed in varying quantities, so the per-total-encounter average
is more stable.
How to modify: Add or remove family_description values from the
WHEN clause. Descriptions must match the family_description column
in the revenue_codes reference table exactly.