Preview Schema

Suggest Edits

🚧

Beta

This schema is in a stage of development that is subject to change and may not yet be feature complete or perform consistently. We do not warrant against bugs or defects in data availability.

Today, data mart users have to extract JSON from key data elements to dashboard their key questions. We are introducing a more performant relational schema familiar to seasoned data analysts.

📘

For more information about the data structure of the schema, please see our Entity Resource Diagrams and column definitions

Maintenance Windows

There are two maintenance windows per week (Tuesdays and Thursdays 4 - 5 p.m. eastern) during which there may be data unavailability.

Upcoming changes

Expected release date: April 11, 2024

:warning:Breaking Change - Simplifying DOSAGE

We are simplifying the DOSAGE table for better usability. These changes include:

  • The TIMING_REPEAT_BOUNDS_PERIOD_START column has been shortened to TIMING_REPEAT_BOUNDS_START.
  • The TIMING_REPEAT_BOUNDS_PERIOD_END column has been shortened to TIMING_REPEAT_BOUNDS_END.
  • The ROUTE_CODEABLE_CONCEPT_ID column has been shortened to ROUTE_CONCEPT_ID and references ROUTE_CONCEPT, a dedicated table for medication/immunization routes, instead of CODEABLE_CONCEPT.
  • The SITE_CODEABLE_CONCEPT_ID has been dropped as data for this column was rarely populated
  • The METHOD_CODEABLE_CONCEPT_ID has been shortened to DOSAGE_METHOD_ID, and references a dedicated table DOSAGE_METHOD
  • We are adding a new column, ADDITIONAL_INSTRUCTION_DISPLAY, which contains the text description of additional dosage instructions. This text was previously in the DOSAGE_ADDITIONAL_INSTRUCTION table, which is removed.
  • We are adding a new column, DOSAGE_QUANTITY_UNIT, which contains the unit by which doses are administered. This value was previously in the DOSE_AND_RATE table, which is removed.
  • We are adding a new column:DOSAGE_QUANTITY_VALUE, which contains the quantity by which doses are administered. This value was previously in the DOSE_AND_RATE table, which is removed.
  • The DOSAGE_ADDITIONAL_INSTRUCTIONS table has been removed, as most of the columns are missing except the text. The text field has been top-leveled onto the DOSAGE table for convenience.
  • The DOSAGE_DOSE_AND_RATE table has been removed, as most column values are missing except for quantity value and unit. These two columns have been top-leveed onto the DOSAGE table for convenience.

Changelog

DateChange TypeChanges
April 9, 2024:hammer: Fixing a bugThe VALUE_QUANTITY column in the OBSERVATION table was fixed to show the full value, including decimals.
March 26, 2024:green-heart: Adding a featureThe following columns are added to the MEDICATION_DISPENSE table:

- The CODE_NDC column contains the code from the FDA's National Drug Code Directory on the medication dispense.
- The CODE_RXNORM column contains the code from the National Library of Medicine's Unified Medical Language System on the medication dispense.
March 21, 2024:green-heart: Adding a featureThe following columns are added to the PATIENT table:

- RACE: This column represents the race of the patient based on the U.S. Core race categories .
- ETHNICITY: This column represents the ethnicity of the patient based on the U.S. Core ethnicity categories .
March 21, 2024:warning: Breaking changeThe following columns are added to the HUMAN_NAME table:
- PREFIX_1: This column represents the first item in a patient's array of prefixes, such as Dr., Mr., Mrs., and Ms.
- SUFFIX_1: This column represents the first item in a patient's array of suffixes, such as Jr.

The HUMAN_NAME_PREFIX and HUMAN_NAME_SUFFIX tables are removed. If a user would like to access the full array of prefixes and suffixes for a name associated with a patient, the RESOURCE_JSON column in the PATIENT table should be used.
March 21, 2024:warning: Breaking changeThe TYPE_CODEABLE_CONCEPT_ID column is removed on the IDENTIFIER table, and the TYPE_DISPLAY and TYPE_CODE columns are added. Though typically NULL, when the identifier type has a populated code, it has tended to come from this code system .
March 19, 2024:green-heart: Adding a featureUsers can now query for the source resources (MedicationAdministration, MedicationDispense, MedicationRequest, and MedicationStatement) that contributed to a Zus-summarized medication through the MEDICATION_STATEMENT_SUMMARY_RXNORM_AGGREGATED_FROM join table. In addition, the following columns have been added to the MEDICATION_STATEMENT_SUMMARY_RXNORM table:- MEDICATION_DAYS_SUPPLY: This column contains the number of days supplied for the medication.

- MEDICATION_QUANTITY: This column contains the amount of medication supplied.
- MEDICATION_LAST_PRESCRIBER_PRACTITIONER_ID: This is a reference to the Practitioner who last prescribed the medication.
- MEDICATION_LAST_PRESCRIBER_MEDICATION_REQUEST_ID: This is a reference to the MedicationRequest resource of the last prescriber.
March 14, 2024:green-heart: Adding a featureUsers can now more easily query Zus-summarized Medication data. For more information on Zus summarization efforts, please see our Zus Lens documentation .

The new MEDICATION_STATEMENT_SUMMARY_RXNORM table includes summarized medication statements based on raw data in the MEDICATION_ADMINISTRATION, MEDICATION_DISPENSE, MEDICATION_REQUEST, and MEDICATION_STATEMENT table. Summarized medication statements are based on the RxNorm code of the source data.
March 14, 2024:green-heart: Adding a featureUsers can now more easily query Zus-summarized Condition data. For more information on Zus summarization efforts, please see our Zus Lens documentation .

The new CONDITION_SUMMARY_SNOMED table includes summarized conditions based on the raw data available in the CONDITION table. Summarized conditions are based on the SNOMED code of the source data.

CONDITION_SUMMARY_SNOMED_AGGREGATED_FROM is a mapping table between source conditions and summarized conditions.
March 14, 2024:warning: Breaking changeThe PERIOD_START and PERIOD_END columns are removed from the IDENTIFIER table.
March 14, 2024:warning: Breaking changeThe HUMAN_NAME_ID column in the HUMAN_NAME table is renamed to ID to be consistent with the primary key naming convention.
March 14, 2024:warning: Breaking changeThe MEDICATION_STATEMENT_DERIVED_FROM child table is removed. The entries in this table only exist for summarized resources.
March 14, 2024:warning: Breaking changeThe medication_refills, medication_last_fill_date, and medication_prescribed_date are removed from MEDICATION_STATEMENT. These fields are o
March 12, 2024:green-heart: Adding a featureThe PRACTITIONER table has a new column identifier_npi. This column contains the national provider identifier (NPI) of the practitioner
March 12, 2024:green-heart: Adding a featureThe CONDITION table has three new columns: code_ccs, code_icd10cm, and code_cms_hcc.

The code_ccs column contains the meaningful clinical category for the condition based on the Clinical Classifications Software (CCS).

Tthe code_cms_HCC column contains the risk adjustment category for the condition based on CMS' definition.

For more information on these groupings based on ICD-10 codes, please see Zus' data enrichment documentation .

The code_icd10cm column contains one of the ICD-10-CM codes on the condition, when available.
March 12, 2024:green-heart: Adding a featureThe IDENTIFIER table has a new column, assigner_display. The column contains the display name of the Organization that issued the Identifier.

Updated 3 days ago