Skip to navigation

export_patient_referrals

A log of the latest state/version of every referral derived from a patient. One row per referral on the patient record.

referral_status and days_from_expiry are computed as of each daily refresh. updated_at advances when referral_status changes (on the start date, and on the day after the end date). days_from_expiry changes every day, so rows that carry it get a new _cds_row_version and re-sync daily. To avoid that churn, compute it from referral_end_date instead of storing it.

The source has no referral ID, so referral_id is a hash of the patient and the referral’s content. An edited referral arrives as a tombstone (is_deleted = TRUE) for the old referral_id plus a new row with a new referral_id. Removed referrals remain as tombstones so incremental consumers can remove them; filter is_deleted = FALSE for current referrals.

Primary Key: referral_id

Identifiers

referral_id
STRINGRequired

Unique ID of the referral: a hash of the patient and the referral’s content. Stable while the referral is unchanged; an edit retires it and issues a new one

patient_id
STRING

Unique ID associated with the patient in the Candid system

patient_mrn
STRING

Medical Record Number - the foreign key to map patient to user’s own system

referral_number
STRING

Referral number

Timestamps

updated_at
TIMESTAMP

Time when the referral or its patient was last updated, or when the referral’s removal was detected. Also advances when the referral starts or expires.

Organization

organization_id
STRING

User’s unique organization ID in the Candid system

organization_name
STRING

User’s Organization / Company Name

external_organization_id
STRING

Organization’s External ID - the ID that the organization uses to map to their own upstream system

Referral

referral_start_date
DATE

Start date of the referral’s authorized period

referral_end_date
DATE

End date of the referral’s authorized period, if known

does_referral_have_an_end_date
BOOLEAN

Whether the referral has a known end date (distinguishes open-ended referrals from data-quality unknowns)

days_from_expiry
INTEGER

Number of days until (positive) or since (negative) the referral’s end date

referral_status
STRING

Status of the referral: Pending, Active, Expired, or Unknown

referral_authorized_units
STRING

Unit type the referral authorizes: VISIT or UNIT

referral_authorized_quantity
INTEGER

Quantity of visits or units the referral authorizes

does_referral_apply_for_all_cpt_codes
BOOLEAN

Whether the referral applies to all CPT codes rather than a specific set

referral_cpt_codes
JSON

Array of CPT codes the referral authorizes. Delivered as ARRAY<STRING> in BigQuery-direct datasets.

referral_notes
STRING

Free-text notes on the referral

Referring Provider

referring_provider_canonical_id
STRING

Canonical ID of the referring provider, if present in the source data

referring_provider_first_name
STRING

First name of the referring provider

referring_provider_last_name
STRING

Last name of the referring provider

referring_provider_npi
STRING

NPI of the referring provider

referring_provider_type
STRING

Type of the referring provider

Service Facility

service_facility_id
STRING

ID of the referral’s service facility

service_facility_name
STRING

Name of the referral’s service facility

service_facility_npi
STRING

NPI of the referral’s service facility

Deletes

is_deleted
BOOLEAN

Whether the referral was removed or edited in the Candid system. Removed referrals remain in this table as tombstones so incremental consumers can remove them.

deleted_at
TIMESTAMP

When the referral was removed or edited in the Candid system

Sync columns

_cds_row_version
TIMESTAMP

Time when this row’s delivered data last changed. Use this for incremental ingestion.

_cds_row_fingerprint
STRING

Hash of this row’s delivered data. Changes whenever any value in the row changes.