> 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

> How the Candid Data Share tables fit together, the four table shapes, and an interactive map of how the tables join

Every Data Share table has a primary key, an `updated_at` column, and two sync columns, `_cds_row_version` and `_cds_row_fingerprint`. Each bundle's overview lists its tables with what one row represents. Check that before you aggregate.

## Explore how the tables join

Hover a table to light up what it joins to. Click a table to see its keys, then click a second table to get the shortest join path, ready-to-run SQL, and a warning wherever the join multiplies rows or needs a `LEFT JOIN`.

The interactive explorer needs JavaScript. Each table's page lists its keys, and the join notes below cover the main paths.

## How the tables fit together

Data Share follows one billing hierarchy, three levels deep. Each level joins to the level above it. Hover a level or a transaction type to see where it sits in an example encounter.

| Level                                                               | Table                 | Primary key       | Joins to the level above on                                       |
| ------------------------------------------------------------------- | --------------------- | ----------------- | ----------------------------------------------------------------- |
| **Encounter**: one visit, billed as one claim                       | `export_encounter`    | `encounter_id`    |                                                                   |
| **Service line**: one billed procedure on the encounter             | `export_service_line` | `service_line_id` | `encounter_id`                                                    |
| **Transaction**: a charge, payment, adjustment, or balance transfer | `export_transaction`  | `transaction_id`  | `service_line_id`, or `encounter_id` for claim-level transactions |

* **Claim fields are on the encounter.** There's no separate claim table.
* **Payments and adjustments point to the charge they offset** through `charge_id`.
* **Only `export_transaction` and `export_service_line_expected_revenue` have amounts to sum.** Each transaction type has a detail table with extra attributes. See [Financial Transactions](/data-share/financial-transactions/overview#detail-tables).
* **Everything else hangs off these three levels:** financial summaries, patient history, and reference tables such as payers and providers. The explorer above shows how each one joins.

## Table shapes

Every table has one of four shapes, shown in each bundle's table list:

| Shape               | What one row is                                                                                               | How to query it                                                                                                                                                     |
| ------------------- | ------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Current state**   | One entity at its latest state                                                                                | Query directly. There's one row per ID.                                                                                                                             |
| **Version history** | One version of a record                                                                                       | Filter on the latest-record flag (`is_latest_record`, or `is_latest_record_for_patient`) for current state. Don't aggregate across versions.                        |
| **Ledger**          | One financial event (`export_transaction`) or expected-revenue entry (`export_service_line_expected_revenue`) | Sum across rows and [exclude voids](/data-share/financial-transactions/overview#voids). For expected revenue, de-duplicate on `expected_revenue_id` before summing. |
| **Detail**          | Extra attributes for one transaction or remit code                                                            | Join to `export_transaction` on `transaction_id`. Informational adjustments join on `encounter_id`, `service_line_id` and `charge_id` instead. No amounts to sum.   |

No table is append-only: existing rows change, for example when a transaction is voided. Each nightly delivery updates them in place on the primary key. If you sync these tables into another system, pull rows whose `_cds_row_version` is newer than your last sync and upsert them on the primary key. `_cds_row_version` moves whenever any delivered value in a row changes; `updated_at` doesn't always.

Data Share destinations also receive `_candid_datashare_org_id` on every table. It identifies the Candid organization that owns the destination and is the same on every row, so you can ignore it unless you combine several Candid destinations.

Columns that hold lists or nested records arrive as `JSON` in Data Share destinations, which is the type each table page shows. BigQuery-direct datasets keep them as `ARRAY` columns, and each such field notes its BigQuery type.

## Join notes

The explorer shows every key. These joins need extra care:

* **Transaction details:** join `export_transaction` to its detail table on `transaction_id`, filtered by `transaction_type`. See [Financial Transactions](/data-share/financial-transactions/overview#detail-tables).
* **Claim submissions:** join `export_claim_submission_history` to `export_encounter` on `encounter_id`. Filter `is_latest_submission` for the most recent submission.
* **Batches:** `export_transaction.batch_id` → `export_batch_details.batch_id`. Not every transaction has a `batch_id`.
* **Patients:** `export_patient.mrn` → `export_encounter.patient_external_id`, within the same `organization_id`. The other `export_patient_*` tables join to `export_patient` on `patient_id`.
* **Payers:** an encounter's `subscriber_primary_payer_plan_group_id` (or the secondary or tertiary column) → `export_payer_plan_group.payer_plan_group_id` → `export_payer.payer_uuid`. On `export_transaction`, `counter_party_id` is the `payer_uuid` when `counter_party_type = 'PAYER'`.
* **Providers:** match `npi` and `organization_id`. A provider can be both billing and rendering, so also filter `is_billing` or `is_rendering` for a proper 1:1 join.