export_patient_referrals
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
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
Unique ID associated with the patient in the Candid system
Medical Record Number - the foreign key to map patient to user’s own system
Referral number
Timestamps
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
User’s unique organization ID in the Candid system
User’s Organization / Company Name
Organization’s External ID - the ID that the organization uses to map to their own upstream system
Referral
Start date of the referral’s authorized period
End date of the referral’s authorized period, if known
Whether the referral has a known end date (distinguishes open-ended referrals from data-quality unknowns)
Number of days until (positive) or since (negative) the referral’s end date
Status of the referral: Pending, Active, Expired, or Unknown
Unit type the referral authorizes: VISIT or UNIT
Quantity of visits or units the referral authorizes
Whether the referral applies to all CPT codes rather than a specific set
Array of CPT codes the referral authorizes. Delivered as ARRAY<STRING> in BigQuery-direct datasets.
Free-text notes on the referral
Referring Provider
Canonical ID of the referring provider, if present in the source data
First name of the referring provider
Last name of the referring provider
NPI of the referring provider
Type of the referring provider
Service Facility
ID of the referral’s service facility
Name of the referral’s service facility
NPI of the referral’s service facility
Deletes
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.
When the referral was removed or edited in the Candid system
Sync columns
Time when this row’s delivered data last changed. Use this for incremental ingestion.
Hash of this row’s delivered data. Changes whenever any value in the row changes.