> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://docs.joincandidhealth.com/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://docs.joincandidhealth.com/_mcp/server.

# Data Model & Grain

> Entity hierarchy, table grain, and join paths across the Candid Data Share dataset

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](/data-share/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.

| Table                                      | One row per…                                                                                                |
| ------------------------------------------ | ----------------------------------------------------------------------------------------------------------- |
| `export_encounter`                         | `encounter_id` (claim fields flattened on)                                                                  |
| `export_service_line`                      | `service_line_id`                                                                                           |
| `export_financial_summary`                 | `encounter_id` (computed rollup per claim/encounter)                                                        |
| `export_service_line_financial_summary`    | `service_line_id` (computed rollup)                                                                         |
| `export_service_line_projected_financials` | `service_line_id` (projected financials)                                                                    |
| `export_batch_details`                     | `batch_id` (accounting period)                                                                              |
| `export_payer`                             | `payer_uuid` (reference / dimension)                                                                        |
| `export_patient_merge`                     | `patient_merge_id` (patient MRN mapping)                                                                    |
| `export_claim_submission_history`          | `claim_submission_id` (one claim can have many submissions; use `is_latest_submission` for the most recent) |
| `export_patient`                           | `patient_id` (join to encounters on `mrn = export_encounter.patient_external_id`)                           |
| `export_patient_appointments`              | `appointment_id` (deactivated appointments included; filter `deactivated = FALSE`)                          |
| `export_patient_referrals`                 | `referral_id`                                                                                               |
| `export_patient_authorizations`            | `authorization_id`                                                                                          |

### 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.

| Table                                        | One row per…                                                                                                                                        |
| -------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------- |
| `export_patient_insurance_history`           | `patient_insurance_history_pk` (one coverage version; `is_latest_record = TRUE` for current coverage)                                               |
| `export_patient_non_insurance_payer_history` | `patient_non_insurance_payer_history_pk` (one patient version × non-insurance payer association; `is_latest_record_for_patient = TRUE` for current) |
| `export_patient_note_history`                | `patient_note_history_pk` (one note version; `is_latest_record = TRUE` for current)                                                                 |

### 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).

| Table                                     | One row per…                                                    |
| ----------------------------------------- | --------------------------------------------------------------- |
| `export_transaction`                      | `transaction_id` (a single financial event — the ledger)        |
| `export_charge_details`                   | `transaction_id` (attributes of a `CHARGE` transaction)         |
| `export_payment_details`                  | `transaction_id` (attributes of a `PAYMENT` transaction)        |
| `export_adjustment_details`               | `transaction_id` (attributes of an `ADJUSTMENT` transaction)    |
| `export_informational_adjustment_details` | `adjustment_detail_id` (a non-balance-impacting ERA adjustment) |

`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.

> **Rolling up amounts**
>
> Sum `export_transaction.amount_cents` (excluding voids) at whatever grain you need: `charge_id`, `service_line_id`, or `encounter_id`. Only the `encounter_id` rollup captures claim-level transactions (`target_type = 'CLAIM'`), which have no `charge_id` or `service_line_id`. Add `counter_party_type` (or `counter_party_id`) to the grouping to see how much of that balance sits with each counterparty — a payer, the patient, or a non-insurance payer.

### 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:

```sql
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.

```mermaid
erDiagram
    export_encounter ||--o{ export_service_line : "encounter_id"
    export_encounter ||--|| export_financial_summary : "encounter_id"
    export_encounter ||--o{ export_claim_submission_history : "encounter_id"
    export_service_line ||--|| export_service_line_financial_summary : "service_line_id"
    export_service_line ||--|| export_service_line_projected_financials : "service_line_id"
    export_encounter ||--o{ export_transaction : "encounter_id"
    export_service_line ||--o{ export_transaction : "service_line_id"
    export_transaction |o--o| export_charge_details : "transaction_id (Charge)"
    export_transaction |o--o| export_payment_details : "transaction_id (Payment)"
    export_transaction |o--o| export_adjustment_details : "transaction_id (Adjustment)"
    export_batch_details |o--o{ export_transaction : "batch_id"
    export_informational_adjustment_details }o--|| export_service_line : "service_line_id"
    export_patient |o--o{ export_encounter : "mrn = patient_external_id"
```

* **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](/data-share/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](https://docs.joincandidhealth.com/api-reference/patient-collections/patient-invoicing-integration-guide) for that. For incremental ingestion guidance, see [Candid Data Share: Incremental Data Pulls](https://support.joincandidhealth.com/hc/en-us/articles/47645614050196-Candid-Data-Share-Incremental-Data-Pulls).