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.
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.
- 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_transactionandexport_service_line_expected_revenuehave 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:
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_transactionto its detail table ontransaction_id, filtered bytransaction_type. See Financial Transactions. - Claim submissions: join
export_claim_submission_historytoexport_encounteronencounter_id. Filteris_latest_submissionfor the most recent submission. - Batches:
export_transaction.batch_id→export_batch_details.batch_id. Not every transaction has abatch_id. - Patients:
export_patient.mrn→export_encounter.patient_external_id, within the sameorganization_id. The otherexport_patient_*tables join toexport_patientonpatient_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. Onexport_transaction,counter_party_idis thepayer_uuidwhencounter_party_type = 'PAYER'. - Providers: match
npiandorganization_id. A provider can be both billing and rendering, so also filteris_billingoris_renderingfor a proper 1:1 join.