Skip to navigation

Reference Data

The master data the other tables point to: payers, payer plans, providers, service facilities and non-insurance payers.

Tables

TableOne row perPrimary keyShape
export_payerPayerpayer_uuidCurrent state
export_payer_plan_groupPayer planpayer_plan_group_idCurrent state
export_providerProviderorganization_provider_idCurrent state
export_service_facilityService facilityorganization_service_facility_idCurrent state
export_non_insurance_payerNon-insurance payeridCurrent state

Notes

  • An encounter’s payer: join export_encounter.subscriber_primary_payer_plan_group_id (or the secondary or tertiary column) to export_payer_plan_group.payer_plan_group_id, then payer_uuid to export_payer.
  • Providers: join on npi and organization_id, and filter is_billing or is_rendering.
  • Service facilities: join export_encounter.service_facility_id to export_service_facility.organization_service_facility_id.
  • Non-insurance payers: join non_insurance_payer_id on export_encounter or export_patient_non_insurance_payer_history to export_non_insurance_payer.id.
  • export_provider has no deleted-row marker. Reload this small table in full rather than incrementally.

Common queries

Encounters with their billing provider

SELECT
e.encounter_id,
e.billing_provider_npi,
p.first_name,
p.last_name,
p.taxonomy_code
FROM export_encounter e
LEFT JOIN export_provider p
ON p.npi = e.billing_provider_npi
AND p.organization_id = e.organization_id
AND p.is_billing