Payer Spine

The payer spine defines every payer that Clear Rates prices. Approximately 90 payers are enrolled, each identified by a stable internal payer_id. The authoritative list lives in a Jinja2 set block at the top of payer.sql — adding a payer ID to this list is the only change required to enroll a new payer in the pipeline.

Build Steps

The payer spine is built in four layers:

Step 1 — Jinja2 set block: A hardcoded set of payer_id values at the top of payer.sql defines the enrolled payer universe. This set controls all downstream filtering.

Step 2 — payer_ids CTE: Expands the Jinja2 set into a single-column relation of enrolled payer IDs. Serves as the filter anchor for all subsequent CTEs.

Step 3 — payers CTE: Joins payer_ids against the upstream payer registry to pull payer_name, definitive_payer_id (the Definitive Healthcare legal entity array), and blue_payer (whether the payer is a Blues plan). Only enrolled payer IDs pass through.

Step 4 — Final SELECT: Joins the payers CTE against spines_payer (the existing spine table) to produce the final output with all columns. New payers not yet in spines_payer are appended.

Payer Spine — Structure

-- payer.sql (simplified)
{% set enrolled_payer_ids = [
  '42',   -- Aetna
  '101',  -- Multiplan
  '643',  -- UHC
  -- ... ~90 payers total
] %}

WITH payer_ids AS (
  SELECT payer_id
  FROM UNNEST(ARRAY[{{ enrolled_payer_ids | join("', '") | surround("'") }}]) AS t(payer_id)
),

payers AS (
  SELECT
      p.payer_id,
      p.payer_name,
      p.definitive_payer_id,   -- ARRAY[BIGINT]
      p.blue_payer              -- BOOLEAN
  FROM tq_production.payer_registry p
  INNER JOIN payer_ids pi ON p.payer_id = pi.payer_id
)

SELECT
  payer_id,
  payer_name,
  definitive_payer_id,
  blue_payer
FROM payers

Walkthrough Examples

Aetna — multi-entity Blues plan

payer_id: '42' (VARCHAR)

payer_name: 'Aetna'

definitive_payer_id: ARRAY[1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010, 1011] — 11 Definitive Healthcare legal entities that operate under the Aetna umbrella

blue_payer: True

Aetna is enrolled with 11 Definitive entity IDs. Any downstream join on definitive_payer_id must UNNEST the array to match individual entities.

Multiplan — rental network, no Definitive entity

payer_id: '101' (VARCHAR)

payer_name: 'Multiplan'

definitive_payer_id: NULL — Multiplan is a rental network, not a health plan; it has no Definitive Healthcare entity

blue_payer: False

Multiplan flows through the pipeline with NULL definitive_payer_id. Joins that filter on definitive_payer_id will exclude Multiplan ROIDs — use IS NULL checks if Multiplan coverage is needed.

Key Columns

Column	Type	Description
payer_id	VARCHAR	Clear Rates's stable internal payer identifier
payer_name	STRING	Human-readable payer name
definitive_payer_id	ARRAY[BIGINT]	Definitive Healthcare legal entity IDs associated with this payer; NULL for rental networks
blue_payer	BOOLEAN	True if the payer is a Blues plan; used to apply Blues-specific network matching logic

definitive_payer_id is an ARRAY

definitive_payer_id is an ARRAY because some payers operate under multiple Definitive Healthcare legal entities. When joining downstream, use CROSS JOIN UNNEST(definitive_payer_id) AS t(def_id) to expand.

payer_id is VARCHAR, not INTEGER

Cast explicitly when joining to tables that store payer_id as bigint:

CAST(hospital_rates.payer_id AS VARCHAR) = payer_spine.payer_id

Failing to cast causes a type mismatch error in Trino and no rows are returned.