Data Dictionary
Schemas for the eight latest_* tables produced by Stage 6 of the
ssp_pricing DAG. These are the canonical downstream read targets;
intermediate / export_* tables are documented in
Methodology.
All tables live in tq_dev.internal_dev_csong_ssp (the DAG's schema
param default). Every row is tagged with a version column matching
the DAG's ssp_version param. The Stage 6 upsert does
DELETE … WHERE version = ssp_version then re-inserts, so each
pipeline run replaces its own version's rows without touching other
versions.
Except for latest_ssp_descriptions and latest_metadata, the
latest_* tables carry only the generic-library row
(provider_id = '0' in the underlying export_* table — the
national-median Google Sheet view). Per-provider rows stay in the
export_* tables.
Column Naming Convention
All price columns follow a consistent prefix + suffix scheme:
Prefix — identifies what is included in the price:
| Prefix | Included components | Formula |
|---|---|---|
inst_ | Institutional / facility | inst_price |
prof_ | Institutional + Professional Fee | inst_price + prof_professional_price |
prof_with_optional_ | Institutional + Professional Fee + Optional Fee | inst_price + prof_all_fees_price |
Suffix — identifies the metric:
| Suffix | Description |
|---|---|
_price | Volume-weighted average commercial canonical rate |
_medicare_price | Medicare reference rate from the highest-volume code |
_weight | price / base_rate (single base_rate = $500 applied to both IP and OP — DAG param) |
_medicare_weight | medicare_price / base_rate |
_list_charge | Billed-charge-style estimate (commercial × gross-charge markup ratio) |
_avg_charge | Average line charge from Komodo claims |
latest_ssp_descriptions
Grain: one row per (version, ssp_grouper).
SSP-level metadata — descriptions, classification, and encounter /
revenue share within the SSP universe. Source: export_ssp_descriptions_v3.
| Column | Type | Description |
|---|---|---|
version | varchar | SSP pipeline version (ssp_version DAG param) |
ssp_grouper | varchar | Legacy SSP identifier (e.g. CA.0.PCI) |
ssp_grouper_id | varchar | New-crosswalk SSP ID |
parent_description | varchar | Parent-level description (from crosswalk) |
parent_ssp_short_description | varchar | Short label for the parent SSP |
parent_xl_descriptions | varchar | Extra-long crosswalk description |
consumer_description | varchar | Consumer-facing description |
disease_area | varchar | Disease-area label (from ssp_groupings) |
status | varchar | SSP lifecycle status (from ssp_groupings) |
pos | varchar | Place of service: IP or OP |
category | varchar | SSP category (e.g. I&S, ns_) |
shoppability | varchar | Shoppability tier label |
medicare_apc | varchar | Highest-volume APC / DRG used for Medicare pricing |
encounter_pct | double | Share of total anchored encounters across all SSPs (from 1%-sampled ssp_encounter_volume) |
revenue_pct | double | Share of total estimated revenue across all SSPs |
latest_ssp_prices
Grain: one row per (version, ssp_grouper) — generic-library
(provider_id = '0') only.
Top-line SSP price summary surfaced in the Google Sheet view. Source:
export_ssp_prices_v3 filtered to provider_id = '0'.
| Column | Type | Description |
|---|---|---|
version | varchar | SSP pipeline version |
ssp_grouper | varchar | Legacy SSP identifier |
ssp_grouper_id | varchar | New-crosswalk SSP ID |
inst_weight | double | Institutional price / base_rate |
inst_avg_charge | decimal(38,2) | Komodo average institutional line charge for the SSP |
prof_weight | double | (Institutional + Professional Fee) price / base_rate |
total_weight | double | (Institutional + Professional + Optional) price / base_rate |
inst_list_charge | decimal(38,2) | Institutional list charge (billed-charge estimate) |
primary_list_charge | double | Primary surgeon list charge |
anes_list_charge | double | Anesthesia list charge |
labpath_list_charge | double | Lab/Path list charge |
radiology_list_charge | double | Radiology list charge |
provider_name | varchar | Provider name (null for the generic row) |
provider_city | varchar | Provider city (null for the generic row) |
provider_state | varchar | Provider state (null for the generic row) |
health_system_name | varchar | Health system name (null for the generic row) |
latest_facility_rc_families
Grain: one row per (version, ssp_grouper, revenue_code_family) —
generic-library only.
How the institutional price is split across revenue-code families
(drug/implant carve-outs are flagged). Source:
export_facility_rc_families_v3 filtered to provider_id = '0'.
| Column | Type | Description |
|---|---|---|
version | varchar | SSP pipeline version |
ssp_grouper | varchar | Legacy SSP identifier |
ssp_grouper_id | varchar | New-crosswalk SSP ID |
revenue_code_family | varchar | RC family (e.g. 0360-0369, 0450-0459) |
family_description | varchar | Human-readable family name (e.g. Operating Room Services) |
is_carveout | boolean | TRUE if this family is priced as an additive carve-out (implants or qualifying pharmacy) |
association_rate | decimal(38,17) | Komodo-derived encounter association for this family within the SSP |
avg_charge | decimal(18,2) | Average line charge used for the carve-out price or the proportional allocation weight |
line_codes | varchar | Comma-joined list of line codes that fed this family |
latest_professional_line_items
Grain: one row per
(version, ssp_grouper, service_type, line_code) — generic-library only.
Per-line-code professional detail. Source:
export_professional_line_items_v3 filtered to provider_id = '0'.
| Column | Type | Description |
|---|---|---|
version | varchar | SSP pipeline version |
ssp_grouper | varchar | Legacy SSP identifier |
ssp_grouper_id | varchar | New-crosswalk SSP ID |
service_type | varchar | Professional, Anesthesia, Lab/Path, or Radiology |
line_code | varchar | HCPCS / CPT line code |
association_rate | double | Encounter share within the anchor (NULL for sub_package_contents rows) |
units | double | Unit multiplier applied to the rate (anesthesia time-based, anchor-cap, etc.) |
weight | double | Commercial rate × units / base_rate |
list_charge | double | Commercial rate × markup × units |
ccs_category | varchar | CCS (Clinical Classification Software) category |
ncci_group | integer | NCCI cluster index — codes sharing an id are mutually exclusive |
line_code_description | varchar | Human-readable description from services_spines_cleaned |
latest_professional_conveners
Grain: one row per (version, ssp_grouper, component) —
generic-library only.
Professional fee rolled up by convener — the delivering specialty
class. Source: export_professional_conveners_v3 filtered to
provider_id = '0'.
| Column | Type | Description |
|---|---|---|
version | varchar | SSP pipeline version |
ssp_grouper | varchar | Legacy SSP identifier |
ssp_grouper_id | varchar | New-crosswalk SSP ID |
component | varchar | Convener bucket: primary, anes, crna, labpath, radiology, assistant_surgeon, assistant_nonsurgeon |
encounter_association | decimal(38,17) | Encounter share where this component fires (from ancillary_encounter_proportions) |
weight | double | Component price / base_rate |
list_charge | double | Component billed-charge estimate |
latest_sub_category
Grain: one row per (version, ssp_grouper, sub_category) —
generic-library only.
Subcategory breakdown (IP severity tiers, OP variant codes). Source:
export_sub_category_v3 filtered to provider_id = '0'.
| Column | Type | Description |
|---|---|---|
version | varchar | SSP pipeline version |
ssp_grouper | varchar | Legacy SSP identifier |
sub_category | varchar | Severity/variant within the SSP (0 = base) |
sub_category_id | varchar | New-crosswalk subcategory ID |
sub_category_name | varchar | Subcategory human-readable name |
pos | varchar | IP or OP |
inst_weight | double | Institutional price / base_rate at the subcategory level |
primary_weight | double | Primary surgeon price / base_rate at the subcategory level |
anchor_codes | varchar | Comma-joined list of anchor codes in this subcategory |
latest_all_line_items
Grain: one row per
(version, ssp_grouper, fee_type, code) — generic-library only.
Unified line-item view combining institutional, professional, and
revenue-code entries. Source: export_all_line_items_v3 filtered to
provider_id = '0'.
| Column | Type | Description |
|---|---|---|
version | varchar | SSP pipeline version |
ssp_grouper | varchar | Legacy SSP identifier |
ssp_grouper_id | varchar | New-crosswalk SSP ID |
fee_type | varchar | Facility Fee, Professional Fee, or Optional Fee |
code | varchar | Line code (HCPCS / CPT / MS-DRG / revenue code) |
code_type | varchar | HCPCS, CPT, MS-DRG, or RC |
is_anchor | boolean | TRUE when the line code is the SSP's anchor |
association_rate | double | Encounter share within the anchor (NULL for sub_package_contents-native rows) |
line_code_description | varchar | Human-readable description |
latest_metadata
Grain: one row per (version, key).
Key/value pipeline summary — static params plus row counts from each
export_* table. Used by the UI and QA tooling to confirm the latest
run completed.
| Column | Type | Description |
|---|---|---|
version | varchar | SSP pipeline version |
key | varchar | Metadata key (see rows emitted, below) |
value | varchar | Metadata value |
Rows emitted per run:
key | Meaning |
|---|---|
ssp_version | DAG ssp_version param |
pipeline_date | DAG pipeline_date param |
export_date | current_date at run time |
provider_id | '0' — the generic-library row ID used by the latest_* filter |
ssp_descriptions_rows | Row count of export_ssp_descriptions_v3 |
ssp_prices_rows | Row count of export_ssp_prices_v3 filtered to provider_id = '0' |
facility_rc_families_rows | Row count of export_facility_rc_families_v3 filtered to provider_id = '0' |
professional_line_items_rows | Row count of export_professional_line_items_v3 filtered to provider_id = '0' |
professional_conveners_rows | Row count of export_professional_conveners_v3 filtered to provider_id = '0' |
sub_category_rows | Row count of export_sub_category_v3 filtered to provider_id = '0' |
all_line_items_rows | Row count of export_all_line_items_v3 filtered to provider_id = '0' |