Skip to navigation

Data Model

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.

LevelTablePrimary keyJoins to the level above on
Encounter: one visit, billed as one claimexport_encounterencounter_id
Service line: one billed procedure on the encounterexport_service_lineservice_line_idencounter_id
Transaction: a charge, payment, adjustment, or balance transferexport_transactiontransaction_idservice_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.
  • 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:

ShapeWhat one row isHow to query it
Current stateOne entity at its latest stateQuery directly. There’s one row per ID.
Version historyOne version of a recordFilter on the latest-record flag (is_latest_record, or is_latest_record_for_patient) for current state. Don’t aggregate across versions.
LedgerOne financial event (export_transaction) or expected-revenue entry (export_service_line_expected_revenue)Sum across rows and exclude voids. For expected revenue, de-duplicate on expected_revenue_id before summing.
DetailExtra attributes for one transaction or remit codeJoin 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.
  • 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.