Skip to navigation

export_patient_authorizations

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

authorization_status and days_from_expiry are computed as of each daily refresh. updated_at advances when authorization_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 authorization_end_date instead of storing it.

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

Primary Key: authorization_id

Identifiers

authorization_id
STRINGRequired

Unique ID of the authorization: a hash of the patient and the authorization’s content. Stable while the authorization 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

authorization_number
STRING

Authorization number

Timestamps

updated_at
TIMESTAMP

Time when the authorization or its patient was last updated, or when the authorization’s removal was detected. Also advances when the authorization 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

Authorization

authorization_payer_id
STRING

Payer ID the authorization was issued under

authorization_payer_name
STRING

Name of the payer the authorization was issued under

authorization_start_date
DATE

Start date of the authorization’s authorized period

authorization_end_date
DATE

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

does_authorization_have_an_end_date
BOOLEAN

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

days_from_expiry
INTEGER

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

authorization_status
STRING

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

authorization_units
STRING

Unit type the authorization authorizes: VISIT or UNIT

authorization_quantity
STRING

Quantity of visits or units the authorization authorizes

authorization_cpt_codes
STRING

CPT code the authorization authorizes

does_authorization_apply_for_all_cpt_codes
BOOLEAN

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

no_prior_authorization_required
BOOLEAN

Whether the source system flagged this as not requiring prior authorization

authorization_dx_codes
JSON

Array of diagnosis codes the authorization covers. Delivered as ARRAY<STRING> in BigQuery-direct datasets.

authorization_billing_provider_npi
STRING

NPI of the authorization’s billing provider

authorization_notes
STRING

Free-text notes on the authorization

Service Facility

service_facility_id
STRING

ID of the authorization’s service facility

service_facility_name
STRING

Name of the authorization’s service facility

service_facility_npi
STRING

NPI of the authorization’s service facility

Deletes

is_deleted
BOOLEAN

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

deleted_at
TIMESTAMP

When the authorization 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.