Candid Data Share is an open data platform and makes it easy to retrieve datasets out of Candid Health in a first-class way. The intention of this Data Share is to provide access to datasets that can be easily synchronized into other data platforms for customer and partner needs outside of the Candid Health platform.

Cloud-native access to these shared BigQuery datasets is provided through service account credentials. In addition to using one of BigQuery’s client libraries, external tools such as Fivetran or Airbyte can be configured with those credentials to provide direct access to and automatically synchronize the datasets.

Available datasets and their schemas are listed below. More detailed column level documentation is available in this Candid support article, Candid Data Share: Tables and Definitions

Please email support@joincandidhealth.com for more information and access to the feature.

Table Information

Encounter, Service Line & General Metadata

General metadata is provided at both the service_line and encounter granularities for easy reporting & summarization.

export_patient_merge helps users track which patient records have been merged together. When a patient is merged, the alternative_patient_mrn represents the patient that was merged (the source), and the primary_patient_mrn represents the patient that received the merge (the target). All encounters and data associated with the alternative patient are consolidated under the primary patient.

export_payer contains metadata about all payers.

Schemas

Financial Transactions Data

Transaction Detail Tables

The core of the financial transactions data model is the export_transaction table. This table will have most of the information you need to analyze your financial transactions. If you need to access more detailed data about one type of transaction, you can join the export_transaction table to one of the associated detail tables using an inner join:

1
SELECT
2
  *
3
FROM export_transaction
4
JOIN export_adjustment_details
5
  ON export_transaction.transaction_type = 'ADJUSTMENT'
6
  AND export_transaction.transaction_id = export_adjustment_details.transaction_id

Full diagram of relationships and JOIN predicates:

Each transaction type has its own detail table that can be joined based on the transaction type:

Transaction to Encounter and Service Line

Transactions can be linked to encounters and service lines:

Service Line Transaction Summary

To view a current transaction summary at the service line level, you can join transactions to their associated service line charges. This query filters out voids and voided transactions to show only active financial data:

1
SELECT
2
  *
3
FROM export_transaction
4
JOIN export_charge_details
5
  ON export_transaction.charge_transaction_id = export_charge_details.transaction_id
6
JOIN export_service_line
7
  ON export_transaction.service_line_id = export_service_line.service_line_id
8
WHERE export_transaction.voids_transaction_id IS NULL
9
  AND export_transaction.voided_by_transaction_id IS NULL

In diagram form:

Informational Adjustments

Informational Adjustments are not associated with financial transactions and can be directly associated with Service Lines or Encounters:

Schemas

General Financial Data & Aggregations

Financial Summary

export_financial_summary summarizes the latest financial amounts for a given encounter at encounter granularity.

These data can be joined to the export_encounter table:

1
SELECT
2
  *
3
FROM export_encounter
4
JOIN export_financial_summary
5
  ON export_encounter.encounter_id=export_financial_summary.encounter_id

Service Line Projected Financials

export_service_line_projected_financials provides financial projections at service line granularity, used for projecting revenue calculations.

PLEASE NOTE: No guarantees are made as to the accuracy of these projections. The expected_allowed_amount_source field applies waterfall logic to project the allowed amount for a service line given its context, in the priority order below:

These data can be joined to the export_service_line table:

1
SELECT
2
  *
3
FROM export_service_line_projected_financials
4
JOIN export_service_line
5
  ON export_service_line_projected_financials.service_line_id=export_service_line.service_line_id

Schemas

Data Share Metadata

To help keep track of when data is updated, we publish a metadata view with pertinent information about when tables were built and when their latest records were processed

Schemas