SQL — Spines

SQL Files

File	Description
reference/spines/payer.sql	Builds tmp_ref_payer_spine from TQ payer spines
reference/spines/provider.sql	Builds tmp_ref_provider_spine; sources from TQ unified provider table, geo lookups, and AMSURG ASC data
reference/spines/network_base.sql	CREATE TABLE for tmp_ref_network_spine; inserts PPO (Commercial) networks
reference/spines/network_hmo.sql	INSERT: HMO (Commercial) networks
reference/spines/network_narrow.sql	INSERT: Narrow (Commercial) networks
reference/spines/network_exchange.sql	INSERT: Exchange (ACA marketplace) networks
reference/spines/network_custom.sql	INSERT: custom one-off networks not covered by standard types
reference/spines/network_medicare_advantage.sql	INSERT: Medicare Advantage networks
reference/spines/network_dedup.sql	Deduplication pass; removes duplicate rows introduced by overlapping keyword matches
reference/spines/code.sql	Builds tmp_ref_code_spine; assembles codesets per provider type and bill type using utilization, claims, and codeset reference tables

Input Tables (params.py)

Variable	Table	Used in
REF_CMS_ZIP_CARRIER_LOCALITY	tq_production.reference_legacy.ref_cms_zip_carrier_locality	provider.sql
AMSURG_ASC_PROVIDER_TYPES	tq_intermediate.cld_utils.amsurg_asc_provider_types	provider.sql
UTILIZATION_NATIONAL	tq_intermediate.claims_benchmarks.claims_benchmarks_utilization_national	code.sql
ALLOWABLES_NATIONAL_PAYERCHANNEL	hive.claims_benchmarks.claims_benchmarks_allowable_national_payerchannel	code.sql
ASP_PRICING_TABLE	tq_production.reference_internal.asp_reference_pricing	code.sql
TMP_OPPS_MEDICARE_REFERENCE_TABLE	tq_production.reference_internal.opps_reference_pricing	code.sql
CMV_INFO	tq_production.cmv.cmv_info	code.sql
CMV_FF	tq_production.cmv.cmv_ff	code.sql
REF_ASP_HCPCS_NDC_CROSSWALK	tq_intermediate.cld_utils.ref_asp_hcpcs_ndc_crosswalk	code.sql
CLAIMS_MEDICAL_HEADERS	tq_intermediate.external_komodo.medical_headers	code.sql
CLAIMS_MEDICAL_SERVICE_LINES	tq_intermediate.external_komodo.medical_service_lines	code.sql
PROVIDER_DEMOGRAPHICS_TABLE	tq_production.reference_legacy.provider_demographics	code.sql
CLINICAL_LAB_REFERENCE_PRICING	tq_production.reference_internal.clinical_laboratory_reference_pricing	code.sql
OUTPATIENT_CODESET	tq_intermediate.cld_utils.outpatient_codeset_2026_01_13	code.sql
ASC_CODESET	tq_intermediate.cld_utils.asc_codeset_2026_01_16	code.sql
CLD_PHYSICIAN_GROUP_CODESET	tq_intermediate.cld_utils.pg_codeset_2025_10_11	code.sql
LAB_HOSPITAL_AND_PGS_CODESET	tq_intermediate.cld_utils.lab_template_codes_2026_01_07	code.sql
OPG_SCHEDULES_UNITED	tq_intermediate.cld_utils.contract_surg_grouper_united_v0	code.sql
OPG_SCHEDULES_AETNA	tq_intermediate.cld_utils.contract_surg_grouper_aetna_v0	code.sql
OPG_SCHEDULES_CIGNA	tq_intermediate.cld_utils.contract_surg_grouper_cigna_v0	code.sql
OPG_SCHEDULES_BCBS_CA	tq_intermediate.cld_utils.contract_surg_grouper_bcbs_ca_v0	code.sql
REF_CMS_MSDRG_WEIGHTS	tq_production.reference_internal.ipps_msdrg	code.sql
ANESTHESIA_BASE_UNITS	tq_intermediate.cld_utils.ref_anesthesia_base_units	code.sql
DRUG_THERAPEUTIC_AREAS	tq_intermediate.cld_utils.drug_therapeutic_areas	code.sql