Data Model & Grain

This page describes the shape of Candid Data Share — how the tables relate, what one row of each table represents (its grain), and the join paths that connect them. It complements the per-table column references linked from the Overview. It describes structure only and contains no sample data.

Entity hierarchy

Candid Data Share is organized around a single billing hierarchy, with financial summaries and reference tables hanging off of it.

Encounter                        (export_encounter — encounter_id, claim_id flattened on)
  └─ Service Line                (export_service_line — service_line_id)
       └─ Charge                 (charge_id — attributes in export_charge_details)
            └─ Transaction       (export_transaction — transaction_id)
                 ├─ Payment      (transaction_type = 'PAYMENT'    → export_payment_details)
                 ├─ Adjustment   (transaction_type = 'ADJUSTMENT' → export_adjustment_details)
                 └─ Balance Transfer (transaction_type = 'BALANCE_TRANSFER')

• Encounter (export_encounter) is the top-level clinical/billing event for a patient visit. Claim-level fields (claim_id, claim_status, claim_patient_control_number, …), every provider role, patient/guarantor identity, and the primary/secondary/tertiary insurance blocks are all flattened onto this one row. There is no separate claim table — claim attributes live on the encounter.
• Service Line (export_service_line) is one procedure/line item within an encounter (encounter_id). It carries up to four positional diagnosis pointers (diagnosis_{zero,one,two,three}_code) and the modifiers array.
• Charge is the billed A/R unit, identified by charge_id. Charge attributes are exposed through export_charge_details; payments and adjustments tie back to the charge they offset via charge_id.
• Transaction (export_transaction) is the financial ledger entry. Its transaction_type is one of CHARGE, PAYMENT, ADJUSTMENT, or BALANCE_TRANSFER, and each balance-impacting type has a matching detail table joined on transaction_id.

Grain reference (one row = ?)

Every table falls into one of three shapes. Knowing which one before you aggregate is the single most important thing to get right.

Current-state tables (one row per entity)

These are refreshed to reflect the latest state — there is only ever one row per ID, so there is nothing to deduplicate. Query them directly.

Version history tables (many rows per patient or note)

These keep one row per version, so a patient or note has many rows over time. Filter on the latest-record flag for current state; don’t aggregate across versions.

Transaction tables (many rows per charge)

These hold one row per financial event, so a single charge, service line, or encounter has many rows. To get a current total you aggregate across rows and filter out voids (see below).

export_transaction is the only ledger table — it carries amount_cents. The *_details tables annotate its rows (payment type, CARC codes, charge attributes) and have no amounts to aggregate.

None of these tables is append-only. Existing rows can change: when a transaction is voided, its voided_by_transaction_id is filled in, and updated_at also moves when related claim or batch data changes. For incremental ingestion, pull rows by updated_at daily and run a full refresh weekly to stay fully in sync.

Voids and re-adjudication

A void never changes the original transaction’s amount. Instead, a new, linked row reverses it:

• voids_transaction_id — set on the reversing transaction, pointing at the transaction it reverses.
• voided_by_transaction_id — set on the original transaction once it has been reversed.

To get the current, live ledger, exclude both sides of every void:

SELECT *
FROM export_transaction
WHERE voids_transaction_id IS NULL
  AND voided_by_transaction_id IS NULL

Also note the sign convention on amount_cents: charges are positive, payments are usually negative, and reversals/refunds are positive.

Key join paths

All keys below are real columns on the published tables.

• Encounter → Service Line → Transaction: export_encounter.encounter_id → export_service_line.encounter_id; export_service_line.service_line_id → export_transaction.service_line_id.
• Transaction → detail tables: export_transaction.transaction_id → export_{charge,payment,adjustment}_details.transaction_id, filtered by transaction_type ('CHARGE', 'PAYMENT', 'ADJUSTMENT').
• Tie payments/adjustments to their charge: charge_id is present on export_transaction, export_charge_details, export_payment_details, and export_adjustment_details — aggregate by charge_id to net payments and adjustments against the original billed amount.
• Financial rollups: export_financial_summary joins on encounter_id; export_service_line_financial_summary and export_service_line_projected_financials join on service_line_id.
• Claim submissions: export_claim_submission_history joins to export_encounter on encounter_id (or claim_id). Order a claim’s submissions by submission_number.
• Batches: export_transaction.batch_id → export_batch_details.batch_id (closed_at IS NULL marks the open, current period).
• Patients: export_patient.mrn → export_encounter.patient_external_id within the same organization_id. Every other export_patient_* table joins to export_patient on patient_id.
• Payers: export_payer is keyed by payer_uuid; on export_transaction, counter_party_id equals the payer UUID when counter_party_type is a payer. payer_plan_group_id appears on several transaction tables as an identifier but has no dedicated lookup table in the data share.
• Informational adjustments: export_informational_adjustment_details links directly to encounter_id, service_line_id, and charge_id (non-balance-impacting; see the Financial Transactions overview).

Refresh cadence

Tables are refreshed nightly. Candid Data Share is not a live, real-time view of a patient’s balance and must not be used for patient invoicing - use the patient invoicing integration guide for that. For incremental ingestion guidance, see Candid Data Share: Incremental Data Pulls.