FHIR Relational Schema v1 to v2 Migration Guide

Zus has released v2 of the FHIR Relational Schema in Snowflake. The new version includes schema adjustments that address several usability challenges we discovered with the v1 schema. We encourage all customers to upgrade to the v2 schema at their earliest convenience. Zus will continue to maintain the v1 schema until August 25, 2026 or when all customers have completed upgrades.

How to use this guide

This guide is organized to support two reading patterns:

• Targeted lookup. If you know which v1 table or column you're trying to migrate, jump to the Complete column mapping reference at the end of this guide for a flat, searchable table.
• Narrative migration. If you want to understand the conceptual changes and see example query rewrites, work through the Breaking changes sections in order. Each section ends with v1/v2 side-by-side SQL.

Before starting a non-trivial migration, also read the Edge cases and gotchas section, which covers patterns that don't map one-to-one between v1 and v2.

Migration timeline and parallel availability

Both the v1 and v2 schemas remain queryable in parallel until August 25, 2026. We strongly recommend running v1 and v2 versions of critical queries side by side during your transition and comparing row counts before cutting over. See Validating your migration for suggested checks.

General changes

Due to a number of breaking schema changes between v1 and v2, this guide provides a list of all breaking changes and recommended query changes to retrieve the same data as before.

Snowflake listing

The v2 FHIR Relational schema is available as a Snowflake listing to all Zus customers using Snowflake in any US region. The v1 schema is only available to customers on AWS and in the us-east-1, us-east-2, and us-west-2 regions.

For customers without Snowflake, the new schema is automatically available in the Zus reader accounts under the FHIR database.

ID column in all tables

All tables now have an id column that uniquely identifies the contents of each row. In the v1 schema, only a subset of tables had an id column, and these tables frequently had duplicate rows. In the v2 schema, the rate of duplicate rows is near zero, and Zus actively monitors tables to eliminate duplicate rows as early as possible.

Timestamp types

The v1 schema contained a mixture of timestamp columns with a timezone and timestamp columns without timezone. For consistency, all timestamp columns in v2 are timestamp with timezone.

Breaking changes

Tables in v1 that mixed data from different FHIR resource types - such as CONTACT_POINT and IDENTIFIER - have been removed. All columns that reference these tables (like IDENTIFIER_ID and CONTACT_POINT_ID) have also been removed.

No data has been removed. All data from tables and columns that were removed are available from other columns in the data.

This guide provides suggested changes to queries that depended on removed tables and columns. Note that these suggested query changes can already be made in the v1 schema, for customers who want to change queries before making the transition to v2.

The core pattern

Almost every breaking change in v2 follows one rule: shared concept tables that previously mixed data across resource types have been fanned out into per-resource tables. If you can recognize this pattern, you can predict almost every rename in this guide.

For each removed shared table SHARED_TABLE, look for per-resource replacements named <RESOURCE>_<CONCEPT> (and, where applicable, <RESOURCE>_<CONCEPT>_CODING). Foreign key columns are renamed to match the destination table.

Removed tables

The following tables are not in the v2 schema. All columns that reference these tables are also removed and listed in the section for removed columns.

• BODY_SITE_CONCEPT
• BODY_SITE_CONCEPT_CODING
• CONTACT_POINT
• DEVICE_NOTE (empty table)
• DOSAGE
• DOSAGE_METHOD
• DOSAGE_METHOD_CODING
• HUMAN_NAME
• HUMAN_NAME_GIVEN
• IDENTIFIER
• MEDICATION_CONCEPT
• MEDICATION_CONCEPT_CODING
• ROUTE_CONCEPT
• ROUTE_CONCEPT_CODING

Migrating from BODY_SITE_CONCEPT

The data in BODY_SITE_CONCEPT and BODY_SITE_CONCEPT_CODING have moved to the following tables:

• DEVICE_USE_STATEMENT_BODY_SITE and DEVICE_USE_STATEMENT_BODY_SITE_CODING
• IMMUNIZATION_BODY_SITE and IMMUNIZATION_BODY_SITE_CODING
• OBSERVATION_BODY_SITE and OBSERVATION_BODY_SITE_CODING
• PROCEDURE_BODY_SITE and PROCEDURE_BODY_SITE_CODING

The column names of foreign key columns that previously referenced BODY_SITE_CONCEPT have changed to reflect the name of the table they reference.

For example, the following query returns patients that have had procedures on their right knee. Tap on the v1 and v2 tabs to see the changes. Notice that the table BODY_SITE_CODING is changed to PROCEDURE_BODY_SITE and the column BODY_SITE_CODING_ID is changed to PROCEDURE_BODY_SITE_ID.

SELECT DISTINCT
    p.SUBJECT_PATIENT_ID,
    p.UPID,
    pn.GIVEN_1,
    pn.FAMILY,
    p.CODE_DISPLAY AS PROCEDURE_DESCRIPTION,
    bsc.DISPLAY AS BODY_SITE,
    p.PERFORMED_START,
    p.PERFORMED_END
FROM FHIR.RELATIONAL_V2_SECURE.PROCEDURE p
LEFT JOIN FHIR.RELATIONAL_V2_SECURE.PROCEDURE_BODY_SITE bsc
    ON p.PROCEDURE_BODY_SITE_ID = bsc.ID
LEFT JOIN FHIR.RELATIONAL_V2_SECURE.PATIENT_NAME pn
    ON p.SUBJECT_PATIENT_ID = pn.PATIENT_ID
WHERE
    LOWER(bsc.DISPLAY) IN ('right knee', 'knee right', 'knee-right', 'r knee')
    OR LOWER(bsc.DISPLAY) LIKE '%right knee%'
    OR LOWER(bsc.DISPLAY) LIKE '%knee right%'
    OR LOWER(bsc.DISPLAY) LIKE '%knee-right%'
ORDER BY pn.FAMILY, pn.GIVEN_1;

SELECT DISTINCT
    p.SUBJECT_PATIENT_ID,
    p.UPID,
    pn.GIVEN_1,
    pn.FAMILY,
    p.CODE_DISPLAY AS PROCEDURE_DESCRIPTION,
    bsc.DISPLAY AS BODY_SITE,
    p.PERFORMED_START,
    p.PERFORMED_END
FROM <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.PROCEDURE p
LEFT JOIN <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.BODY_SITE_CONCEPT bsc
    ON p.BODY_SITE_CONCEPT_ID = bsc.ID
LEFT JOIN <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.PATIENT_NAME pn
    ON p.SUBJECT_PATIENT_ID = pn.PATIENT_ID
WHERE
    LOWER(bsc.DISPLAY) IN ('right knee', 'knee right', 'knee-right', 'r knee')
    OR LOWER(bsc.DISPLAY) LIKE '%right knee%'
    OR LOWER(bsc.DISPLAY) LIKE '%knee right%'
    OR LOWER(bsc.DISPLAY) LIKE '%knee-right%'
ORDER BY pn.FAMILY, pn.GIVEN_1;

Migrating from CONTACT_POINT

The columns SYSTEM, VALUE, USE, RANK, PERIOD_START, and PERIOD_END that were previously available on the CONTACT_POINT table have all been added to the following tables:

• CARE_TEAM_TELECOM
• LOCATION_TELECOM
• ORGANIZATION_TELECOM
• PATIENT_CONTACT_TELECOM
• PATIENT_TELECOM
• PRACTITIONER_TELECOM
• RELATED_PERSON_TELECOM

This example query below retrieves all email addresses of related persons for patients living in New York. Tap on the v1 and v2 tabs to see the changes. Notice that in v2, there is no join to the CONTACT_POINT table, as the desired VALUE column is present on RELATED_PERSON_TELECOM.

SELECT DISTINCT
    rpt.VALUE AS EMAIL_ADDRESS,
    rp.PATIENT_ID,
    rp.NAME_GIVEN_1,
    rp.NAME_FAMILY
FROM FHIR.RELATIONAL_V2_SECURE.PATIENT_ADDRESS pa
JOIN FHIR.RELATIONAL_V2_SECURE.RELATED_PERSON rp
    ON pa.PATIENT_ID = rp.PATIENT_ID
JOIN FHIR.RELATIONAL_V2_SECURE.RELATED_PERSON_TELECOM rpt
    ON rp.ID = rpt.RELATED_PERSON_ID
WHERE LOWER(pa.STATE) IN ('ny', 'new york')
    AND LOWER(rpt.SYSTEM) = 'email'
ORDER BY rp.NAME_FAMILY, rp.NAME_GIVEN_1;

SELECT DISTINCT
    cp.VALUE AS EMAIL_ADDRESS,
    rp.PATIENT_ID,
    rp.NAME_GIVEN_1,
    rp.NAME_FAMILY
FROM <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.PATIENT_ADDRESS pa
JOIN <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.RELATED_PERSON rp
    ON pa.PATIENT_ID = rp.PATIENT_ID
JOIN <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.RELATED_PERSON_TELECOM rpt
    ON rp.ID = rpt.RELATED_PERSON_ID
JOIN <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.CONTACT_POINT cp
    ON rpt.CONTACT_POINT_ID = cp.ID
WHERE LOWER(pa.STATE) IN ('ny', 'new york')
    AND LOWER(cp.SYSTEM) = 'email'
ORDER BY rp.NAME_FAMILY, rp.NAME_GIVEN_1;

Migrating from DOSAGE

The information previously in the DOSAGE table is now in the following tables:

• LENS_RXNORM_MEDICATION_STATEMENT_DOSAGE
• MEDICATION_REQUEST_DOSAGE
• MEDICATION_STATEMENT_DOSAGE

Note the asymmetry across the three dosage sub-concepts:

MedicationAdministration resources only have route information, and MedicationDispense resources only contain dosage display text - which is why their per-resource dosage tables are absent.

The following query returns active medications with dosage display text. Tap on the v1 and v2 tabs to see the changes. Notice that in v2, the joined table is MEDICATION_STATEMENT_DOSAGE instead of DOSAGE, and the column to join on is MEDICATION_STATEMENT_DOSAGE_ID instead of DOSAGE_ID.

SELECT
    ms.SUBJECT_PATIENT_ID,
    ms.CODE_DISPLAY AS MEDICATION,
    ms.CODE_RXNORM,
    ms.STATUS,
    ms.EFFECTIVE_START,
    ms.EFFECTIVE_END,
    d.DISPLAY AS DOSAGE_INSTRUCTION,
    d.DOSE_QUANTITY_VALUE,
    d.DOSE_QUANTITY_UNIT,
    d.TIMING_PERIOD,
    d.TIMING_REPEAT_PERIOD_UNIT,
    d.ADDITIONAL_INSTRUCTION_DISPLAY
FROM FHIR.RELATIONAL_V2_SECURE.MEDICATION_STATEMENT ms
JOIN FHIR.RELATIONAL_V2_SECURE.MEDICATION_STATEMENT_DOSAGE d
    ON ms.MEDICATION_STATEMENT_DOSAGE_ID = d.ID
WHERE ms.STATUS = 'active'
ORDER BY ms.SUBJECT_PATIENT_ID, ms.CODE_DISPLAY;

SELECT
    ms.SUBJECT_PATIENT_ID,
    ms.CODE_DISPLAY AS MEDICATION,
    ms.CODE_RXNORM,
    ms.STATUS,
    ms.EFFECTIVE_START,
    ms.EFFECTIVE_END,
    d.DISPLAY AS DOSAGE_INSTRUCTION,
    d.DOSE_QUANTITY_VALUE,
    d.DOSE_QUANTITY_UNIT,
    d.TIMING_PERIOD,
    d.TIMING_REPEAT_PERIOD_UNIT,
    d.ADDITIONAL_INSTRUCTION_DISPLAY
FROM <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.MEDICATION_STATEMENT ms
JOIN <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.DOSAGE d
    ON ms.DOSAGE_ID = d.ID
WHERE ms.STATUS = 'active'
ORDER BY ms.SUBJECT_PATIENT_ID, ms.CODE_DISPLAY;

Dosage method details

The data in DOSAGE_METHOD and DOSAGE_METHOD_CODING have moved to the following tables:

• LENS_RXNORM_MEDICATION_STATEMENT_DOSAGE_METHOD and LENS_RXNORM_MEDICATION_STATEMENT_DOSAGE_METHOD_CODING
• MEDICATION_REQUEST_DOSAGE_METHOD and MEDICATION_REQUEST_DOSAGE_METHOD_CODING
• MEDICATION_STATEMENT_DOSAGE_METHOD and MEDICATION_STATEMENT_DOSAGE_METHOD_CODING

The following query retrieves medication requests with dosage and administration method details. Tap on the v1 and v2 tabs to see the changes. Note the joined tables of MEDICATION_REQUEST_DOSAGE and MEDICATION_REQUEST_DOSAGE_METHOD_CODING, and the joined columns of MEDICATION_REQUEST_DOSAGE_ID and MEDICATION_REQUEST_DOSAGE_METHOD_ID.

SELECT
    mr.SUBJECT_PATIENT_ID,
    mr.CODE_DISPLAY AS MEDICATION,
    mr.CODE_RXNORM,
    mr.STATUS,
    mr.INTENT,
    mr.AUTHORED_ON,
    mr.QUANTITY,
    mr.NUMBER_OF_REPEATS_ALLOWED,
    mr.SUBSTITUTION_ALLOWED,
    d.DISPLAY AS DOSAGE_INSTRUCTION,
    d.DOSE_QUANTITY_VALUE,
    d.DOSE_QUANTITY_UNIT,
    d.TIMING_PERIOD,
    d.TIMING_REPEAT_PERIOD_UNIT,
    dmc.DISPLAY AS ADMINISTRATION_METHOD,
    dmc.CODE AS METHOD_CODE,
    dmc.SYSTEM AS METHOD_CODE_SYSTEM
FROM FHIR.RELATIONAL_V2_SECURE.MEDICATION_REQUEST mr
JOIN FHIR.RELATIONAL_V2_SECURE.MEDICATION_REQUEST_DOSAGE d
    ON mr.MEDICATION_REQUEST_DOSAGE_ID = d.ID
JOIN FHIR.RELATIONAL_V2_SECURE.MEDICATION_REQUEST_DOSAGE_METHOD_CODING dmc
    ON d.MEDICATION_REQUEST_DOSAGE_METHOD_ID = dmc.MEDICATION_REQUEST_DOSAGE_METHOD_ID
ORDER BY mr.SUBJECT_PATIENT_ID, mr.AUTHORED_ON DESC;

SELECT
    mr.SUBJECT_PATIENT_ID,
    mr.CODE_DISPLAY AS MEDICATION,
    mr.CODE_RXNORM,
    mr.STATUS,
    mr.INTENT,
    mr.AUTHORED_ON,
    mr.QUANTITY,
    mr.NUMBER_OF_REPEATS_ALLOWED,
    mr.SUBSTITUTION_ALLOWED,
    d.DISPLAY AS DOSAGE_INSTRUCTION,
    d.DOSE_QUANTITY_VALUE,
    d.DOSE_QUANTITY_UNIT,
    d.TIMING_PERIOD,
    d.TIMING_REPEAT_PERIOD_UNIT,
    dmc.DISPLAY AS ADMINISTRATION_METHOD,
    dmc.CODE AS METHOD_CODE,
    dmc.SYSTEM AS METHOD_CODE_SYSTEM
FROM <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.MEDICATION_REQUEST mr
JOIN <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.DOSAGE d
    ON mr.DOSAGE_ID = d.ID
JOIN <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.DOSAGE_METHOD_CODING dmc
    ON d.DOSAGE_METHOD_ID = dmc.DOSAGE_METHOD_ID
ORDER BY mr.SUBJECT_PATIENT_ID, mr.AUTHORED_ON DESC;

Dosage route details

The DOSAGE table previously joined to the ROUTE_CONCEPT and ROUTE_CONCEPT_CODING tables, which are also removed in the v2 schema. Instead, dosage route information is included in the following tables:

• LENS_RXNORM_MEDICATION_STATEMENT_DOSAGE_ROUTE and LENS_RXNORM_MEDICATION_STATEMENT_DOSAGE_ROUTE_CODING
• MEDICATION_ADMINISTRATION_DOSAGE_ROUTE and MEDICATION_ADMINISTRATION_DOSAGE_ROUTE_CODING
• MEDICATION_REQUEST_DOSAGE_ROUTE and MEDICATION_REQUEST_DOSAGE_ROUTE_CODING
• MEDICATION_STATEMENT_DOSAGE_ROUTE and MEDICATION_STATEMENT_DOSAGE_ROUTE_CODING

The following query retrieves coding values of the dosage administration route on medication administrations to patients. Tap on the v1 and v2 tabs to see the changes.

Note about the v1 example: The v1 query below joins MEDICATION_ADMINISTRATION to DOSAGE via a route-concept column. This reflects the original v1 schema's denormalization and is intentionally preserved so that customers can match the pattern in their existing pipelines. In v2, this awkward join chain is replaced with a clean parent-child relationship.

SELECT
    ma.ID AS MEDICATION_ADMIN_ID,
    ma.SUBJECT_PATIENT_ID,
    ma.STATUS,
    ma.MEDICATION_CONCEPT_ID,
    ma.EFFECTIVE_START,
    ma.EFFECTIVE_END,
    ma.DOSAGE_DOSE_VALUE,
    ma.DOSAGE_DOSE_UNIT,
    d.DISPLAY AS DOSAGE_INSTRUCTION,
    d.DOSE_QUANTITY_VALUE,
    d.DOSE_QUANTITY_UNIT,
    d.TIMING_PERIOD,
    d.TIMING_REPEAT_PERIOD_UNIT,
    d.ADDITIONAL_INSTRUCTION_DISPLAY,
    rcc.SYSTEM AS ROUTE_CODE_SYSTEM,
    rcc.CODE AS ROUTE_CODE,
    rcc.DISPLAY AS ROUTE_DISPLAY
FROM FHIR.RELATIONAL_V2_SECURE.MEDICATION_ADMINISTRATION ma
JOIN FHIR.RELATIONAL_V2_SECURE.MEDICATION_ADMINISTRATION_DOSAGE_ROUTE d
    ON ma.MEDICATION_ADMINISTRATION_DOSAGE_ROUTE_ID = d.ID
JOIN FHIR.RELATIONAL_V2_SECURE.MEDICATION_ADMINISTRATION_DOSAGE_ROUTE_CODING rcc
    ON d.ID = rcc.MEDICATION_ADMINISTRATION_DOSAGE_ROUTE_ID

SELECT
    ma.ID AS MEDICATION_ADMIN_ID,
    ma.SUBJECT_PATIENT_ID,
    ma.STATUS,
    ma.MEDICATION_CONCEPT_ID,
    ma.EFFECTIVE_START,
    ma.EFFECTIVE_END,
    ma.DOSAGE_DOSE_VALUE,
    ma.DOSAGE_DOSE_UNIT,
    d.DISPLAY AS DOSAGE_INSTRUCTION,
    d.DOSE_QUANTITY_VALUE,
    d.DOSE_QUANTITY_UNIT,
    d.TIMING_PERIOD,
    d.TIMING_REPEAT_PERIOD_UNIT,
    d.ADDITIONAL_INSTRUCTION_DISPLAY,
    rcc.SYSTEM AS ROUTE_CODE_SYSTEM,
    rcc.CODE AS ROUTE_CODE,
    rcc.DISPLAY AS ROUTE_DISPLAY
FROM <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.MEDICATION_ADMINISTRATION ma
JOIN <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.DOSAGE d
    ON ma.DOSAGE_ROUTE_CONCEPT_ID = d.ROUTE_CONCEPT_ID
JOIN <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.ROUTE_CONCEPT_CODING rcc
    ON d.ROUTE_CONCEPT_ID = rcc.ROUTE_CONCEPT_ID

Finally, there are no dosage join tables for MEDICATION_DISPENSE, as dosage information on MedicationDispense resources only contains text descriptions. The data for the text description is already available in the DOSAGE_INSTRUCTION column.

Migrating from HUMAN_NAME

The HUMAN_NAME and HUMAN_NAME_GIVEN tables are removed in v2. All name fields previously available through these tables are now present directly on PATIENT_NAME. The HUMAN_NAME_ID foreign key column has been removed from PATIENT_NAME.

A new RELATED_PERSON_NAME table is available in v2, providing equivalent structured name data for related persons (see New tables).

The following query retrieves the full names of patients with active name records. Tap on the v1 and v2 tabs to see the changes. Notice that the v2 query does not require a join to HUMAN_NAME.

SELECT
    p.ID AS PATIENT_ID,
    p.UPID,
    pn.GIVEN_1,
    pn.GIVEN_2,
    pn.FAMILY,
    pn.PREFIX_1,
    pn.DISPLAY
FROM FHIR.RELATIONAL_V2_SECURE.PATIENT p
JOIN FHIR.RELATIONAL_V2_SECURE.PATIENT_NAME pn
    ON p.ID = pn.PATIENT_ID
WHERE pn.PERIOD_END IS NULL
ORDER BY pn.FAMILY, pn.GIVEN_1;

SELECT
    p.ID AS PATIENT_ID,
    p.UPID,
    pn.GIVEN_1,
    pn.GIVEN_2,
    hn.FAMILY,
    hn.PREFIX_1,
    hn.DISPLAY
FROM <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.PATIENT p
JOIN <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.PATIENT_NAME pn
    ON p.ID = pn.PATIENT_ID
JOIN <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.HUMAN_NAME hn
    ON pn.HUMAN_NAME_ID = hn.ID
WHERE hn.PERIOD_END IS NULL
ORDER BY hn.FAMILY, pn.GIVEN_1;

Migrating from IDENTIFIER

The SYSTEM, VALUE, USE, TYPE_CODE, TYPE_DISPLAY, and ASSIGNER_DISPLAY columns previously on the IDENTIFIER table are available on the respective <RESOURCE>_IDENTIFIER tables. The following resources have a per-resource identifier table in v2:

• ALLERGY_INTOLERANCE_IDENTIFIER
• APPOINTMENT_IDENTIFIER
• CARE_PLAN_IDENTIFIER
• CARE_TEAM_IDENTIFIER
• CONDITION_IDENTIFIER
• CONSENT_IDENTIFIER
• COVERAGE_IDENTIFIER
• DEVICE_IDENTIFIER
• DEVICE_USE_STATEMENT_IDENTIFIER
• DIAGNOSTIC_REPORT_IDENTIFIER
• ENCOUNTER_IDENTIFIER
• MEDICATION_DISPENSE_IDENTIFIER
• MEDICATION_REQUEST_IDENTIFIER
• MEDICATION_STATEMENT_IDENTIFIER
• OBSERVATION_IDENTIFIER
• PATIENT_IDENTIFIER
• PROCEDURE_IDENTIFIER
• RELATED_PERSON_IDENTIFIER

The following query retrieves the names and UPIDs of all patients who have an identifier system matching https://demohealth.com/record-number. Tap on the v1 and v2 tabs to see the changes. Note that the v2 query has one fewer join.

SELECT
    p.NAME_GIVEN_1,
    p.NAME_FAMILY,
    p.UPID,
    pi.SYSTEM,
    pi.VALUE
FROM FHIR.RELATIONAL_V2_SECURE.PATIENT p
JOIN FHIR.RELATIONAL_V2_SECURE.PATIENT_IDENTIFIER pi
    ON p.ID = pi.PATIENT_ID
WHERE pi.SYSTEM = 'https://demohealth.com/record-number'

SELECT
    p.NAME_GIVEN_1,
    p.NAME_FAMILY,
    p.UPID,
    i.SYSTEM,
    i.VALUE
FROM <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.PATIENT p
JOIN <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.PATIENT_IDENTIFIER pi
    ON p.ID = pi.PATIENT_ID
JOIN <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.IDENTIFIER i
    ON i.ID = pi.IDENTIFIER_ID
WHERE i.SYSTEM = 'https://demohealth.com/record-number'

Migrating from MEDICATION_CONCEPT

The MEDICATION_CONCEPT and MEDICATION_CONCEPT_CODING tables are removed in v2. The foreign key column on each medication resource table that previously referenced MEDICATION_CONCEPT has been renamed to reference the per-resource medication code table.

The following column renames apply across medication tables:

A new IS_ACTIVE_INGREDIENT column is available on each <RESOURCE>_MEDICATION_CODE_CODING table in v2. This column was not present in MEDICATION_CONCEPT_CODING.

The following query retrieves all coding values for active medication statements. Tap on the v1 and v2 tabs to see the changes. Notice that in v2, the join column is MEDICATION_STATEMENT_MEDICATION_CODE_ID and the coding table is MEDICATION_STATEMENT_MEDICATION_CODE_CODING instead of the shared MEDICATION_CONCEPT_CODING.

SELECT
    ms.SUBJECT_PATIENT_ID,
    ms.CODE_DISPLAY AS MEDICATION,
    ms.CODE_RXNORM,
    mcc.SYSTEM,
    mcc.CODE,
    mcc.DISPLAY AS CODING_DISPLAY,
    mcc.IS_ACTIVE_INGREDIENT
FROM FHIR.RELATIONAL_V2_SECURE.MEDICATION_STATEMENT ms
JOIN FHIR.RELATIONAL_V2_SECURE.MEDICATION_STATEMENT_MEDICATION_CODE_CODING mcc
    ON ms.MEDICATION_STATEMENT_MEDICATION_CODE_ID = mcc.MEDICATION_STATEMENT_MEDICATION_CODE_ID
WHERE ms.STATUS = 'active'
ORDER BY ms.SUBJECT_PATIENT_ID;

SELECT
    ms.SUBJECT_PATIENT_ID,
    ms.CODE_DISPLAY AS MEDICATION,
    ms.CODE_RXNORM,
    mcc.SYSTEM,
    mcc.CODE,
    mcc.DISPLAY AS CODING_DISPLAY
FROM <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.MEDICATION_STATEMENT ms
JOIN <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.MEDICATION_CONCEPT_CODING mcc
    ON ms.MEDICATION_CONCEPT_ID = mcc.MEDICATION_CONCEPT_ID
WHERE ms.STATUS = 'active'
ORDER BY ms.SUBJECT_PATIENT_ID;

Migrating from ROUTE_CONCEPT

The ROUTE_CONCEPT and ROUTE_CONCEPT_CODING tables are removed in v2. Dosage route information for MEDICATION_ADMINISTRATION, MEDICATION_REQUEST, MEDICATION_STATEMENT, and LENS_RXNORM_MEDICATION_STATEMENT is covered in detail in the Migrating from DOSAGE section above.

For IMMUNIZATION resources specifically, the ROUTE_CONCEPT_ID column has been renamed to IMMUNIZATION_ROUTE_ID, and route data is now stored in the per-resource IMMUNIZATION_ROUTE and IMMUNIZATION_ROUTE_CODING tables.

SELECT
    i.ID AS IMMUNIZATION_ID,
    i.SUBJECT_PATIENT_ID,
    i.VACCINE_NAME,
    i.OCCURRENCE,
    r.DISPLAY AS ROUTE_DISPLAY,
    rc.SYSTEM AS ROUTE_SYSTEM,
    rc.CODE AS ROUTE_CODE
FROM FHIR.RELATIONAL_V2_SECURE.IMMUNIZATION i
LEFT JOIN FHIR.RELATIONAL_V2_SECURE.IMMUNIZATION_ROUTE r
    ON i.IMMUNIZATION_ROUTE_ID = r.ID
LEFT JOIN FHIR.RELATIONAL_V2_SECURE.IMMUNIZATION_ROUTE_CODING rc
    ON r.ID = rc.IMMUNIZATION_ROUTE_ID
ORDER BY i.OCCURRENCE DESC;

SELECT
    i.ID AS IMMUNIZATION_ID,
    i.SUBJECT_PATIENT_ID,
    i.VACCINE_NAME,
    i.OCCURRENCE,
    r.DISPLAY AS ROUTE_DISPLAY,
    rc.SYSTEM AS ROUTE_SYSTEM,
    rc.CODE AS ROUTE_CODE
FROM <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.IMMUNIZATION i
LEFT JOIN <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.IMMUNIZATION_ROUTE r
    ON i.ROUTE_CONCEPT_ID = r.ID
LEFT JOIN <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.ROUTE_CONCEPT_CODING rc
    ON r.ID = rc.ROUTE_CONCEPT_ID
ORDER BY i.OCCURRENCE DESC;

Edge cases and gotchas

These patterns don't follow the simple "rename and re-join" template. Read this section before migrating any non-trivial pipeline.

One v1 query may become several v2 queries

Queries that pivoted through a shared concept table to span multiple resource types must be restructured. The shared table no longer exists, so the join axis is gone.

Example. A v1 query asking "give me all body sites recorded for patient X" was a single join through BODY_SITE_CONCEPT. In v2, this becomes a UNION ALL of four queries - one against each of PROCEDURE_BODY_SITE, OBSERVATION_BODY_SITE, IMMUNIZATION_BODY_SITE, and DEVICE_USE_STATEMENT_BODY_SITE.

The same pattern applies to CONTACT_POINT (seven destination _TELECOM tables), IDENTIFIER (eighteen destination _IDENTIFIER tables), DOSAGE (three to four destinations depending on sub-concept), MEDICATION_CONCEPT (five destinations), and ROUTE_CONCEPT (five destinations including IMMUNIZATION_ROUTE).

If your v1 pipeline included queries like "find all identifiers issued by system X across any resource" or "find all phone numbers across any resource for patient X," plan for UNION ALL rewrites in v2.

Asymmetric replacement sets

Not every removed concept table fans out to the same set of resources. Use the core pattern table and the section-specific lists as the source of truth, not analogy:

• DOSAGE → 3 destinations; DOSAGE_METHOD → 3 destinations; DOSAGE_ROUTE → 4 destinations (adds MedicationAdministration).
• BODY_SITE_CONCEPT → 4 destinations across PROCEDURE, OBSERVATION, IMMUNIZATION, DEVICE_USE_STATEMENT.
• ROUTE_CONCEPT → 4 medication destinations plus IMMUNIZATION_ROUTE.

Removed columns with no direct replacement column

A few columns were removed and their data is reachable only via the coding table:

• CONDITION.CODE_HCC, CONDITION.CODE_CMSHCC_V24, CONDITION.CODE_CMSHCC_V28 → use CONDITION_CODE_CODING and filter by system/version.
• LENS_SNOMED_CONDITION.CODE_ICD10CM → use LENS_SNOMED_CONDITION_CODE_CODING and filter for http://hl7.org/fhir/sid/icd-10-cm.

If your pipeline used these convenience columns, the v2 rewrite requires an explicit filter on the coding system and (for HCC) version.

Renamed-but-not-relocated columns that change semantics

Two Lens condition tables changed which table they link to. The rename is small; the semantic change is not:

• LENS_ENCOUNTER_DIAGNOSIS.LENS_SNOMED_CONDITION_ID → CONDITION_ID. This table now links to the base CONDITION table, not LENS_SNOMED_CONDITION.
• LENS_TRANSITION_OF_CARE_DIAGNOSIS.CONDITION_ID → LENS_SNOMED_CONDITION_ID. This table now links to Lens SNOMED conditions, not base CONDITION.

Audit any joins on these two tables carefully - the column-name swap goes in opposite directions.

New columns on existing tables

v2 introduces new columns on some carry-over tables. The most notable:

• <RESOURCE>_MEDICATION_CODE_CODING tables expose IS_ACTIVE_INGREDIENT.

If you're regenerating column lists programmatically (e.g., for an ETL schema), don't assume v2 column sets are subsets of v1.

Validating your migration

We recommend running these checks before cutting over from v1 to v2.

Row-count parity

For each query you're migrating, run both the v1 and v2 versions during the parallel-availability window and compare row counts:

-- v1 count
SELECT COUNT(*) FROM (<your v1 query>);

-- v2 count
SELECT COUNT(*) FROM (<your v2 query>);

For queries that should return one row per resource (e.g., one row per patient identifier), exact equality is expected. Small differences may indicate that v2 has eliminated duplicate rows that v1 was returning - investigate by comparing distinct keys before concluding there's a bug.

Spot-check identifier values

-- v1: list distinct identifier systems for one resource type
SELECT DISTINCT i.SYSTEM
FROM <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.PATIENT_IDENTIFIER pi
JOIN <NAME>_EXPORT_ZUS.<NAME>_RELATIONAL.IDENTIFIER i ON i.ID = pi.IDENTIFIER_ID;

-- v2: same question
SELECT DISTINCT SYSTEM
FROM FHIR.RELATIONAL_V2_SECURE.PATIENT_IDENTIFIER;

The set of distinct systems should match.

Confirm no rows lost in the fan-out

For queries that previously hit a shared table, sum row counts across the per-resource destinations and compare to the v1 shared-table count for the same patient cohort. Drift here usually means a join in your rewrite isn't covering all source rows.

Column changes by table

This section is a per-table index. For an exhaustive, sortable mapping, see the Complete column mapping reference at the end of this guide.

Table: ALLERGY_INTOLERANCE_IDENTIFIER

• Removed IDENTIFIER_ID. See Migrating from IDENTIFIER.

Table: APPOINTMENT_IDENTIFIER

• Removed IDENTIFIER_ID. See Migrating from IDENTIFIER.

Table: CARE_PLAN_IDENTIFIER

• Removed IDENTIFIER_ID. See Migrating from IDENTIFIER.

Table: CARE_TEAM_IDENTIFIER

• Removed IDENTIFIER_ID. See Migrating from IDENTIFIER.

Table: CARE_TEAM_TELECOM

• Removed CONTACT_POINT_ID. See Migrating from CONTACT_POINT.

Table: CONDITION

• Removed CODE_HCC
• Removed CODE_CMSHCC_V24
• Removed CODE_CMSHCC_V28

HCC codes require accompanying information about the issuing organization and version to be useful, which made the single column CODE_HCC ambiguous and unsuitable for analytical work. The CONDITION_CODE_CODING table provides HCC codes along with provider and version.

Table: CONDITION_IDENTIFIER

• Removed IDENTIFIER_ID. See Migrating from IDENTIFIER.

Table: CONSENT_IDENTIFIER

• Removed IDENTIFIER_ID. See Migrating from IDENTIFIER.

Table: COVERAGE_IDENTIFIER

• Removed IDENTIFIER_ID. See Migrating from IDENTIFIER.

Table: DEVICE_IDENTIFIER

• Removed IDENTIFIER_ID. See Migrating from IDENTIFIER.

Table: DEVICE_USE_STATEMENT

• Renamed BODY_SITE_CONCEPT_ID to DEVICE_USE_STATEMENT_BODY_SITE_ID. See Migrating from BODY_SITE_CONCEPT.

Table: DEVICE_USE_STATEMENT_IDENTIFIER

• Removed IDENTIFIER_ID. See Migrating from IDENTIFIER.

Table: DIAGNOSTIC_REPORT_IDENTIFIER

• Removed IDENTIFIER_ID. See Migrating from IDENTIFIER.

Table: DOCUMENT_REFERENCE

• Removed MASTER_IDENTIFIER_ID.

Table: ENCOUNTER_IDENTIFIER

• Removed IDENTIFIER_ID. See Migrating from IDENTIFIER.

Table: IMMUNIZATION

• Renamed BODY_SITE_CONCEPT_ID to IMMUNIZATION_BODY_SITE_ID. See Migrating from BODY_SITE_CONCEPT.
• Renamed ROUTE_CONCEPT_ID to IMMUNIZATION_ROUTE_ID. See Migrating from ROUTE_CONCEPT.

Table: LENS_ENCOUNTER_DIAGNOSIS

• Renamed LENS_SNOMED_CONDITION_ID to CONDITION_ID.

LENS_ENCOUNTER_DIAGNOSIS now links directly to the base CONDITION table. Update any joins that referenced LENS_SNOMED_CONDITION via this table to reference CONDITION instead. See Renamed-but-not-relocated columns that change semantics.

Table: LENS_RXNORM_MEDICATION_STATEMENT

• Renamed MEDICATION_CONCEPT_ID to LENS_RXNORM_MEDICATION_STATEMENT_MEDICATION_CODE_ID. See Migrating from MEDICATION_CONCEPT.
• Renamed DOSAGE_ID to LENS_RXNORM_MEDICATION_STATEMENT_DOSAGE_ID. See Migrating from DOSAGE.

Table: LENS_SNOMED_CONDITION

• Removed CODE_ICD10CM.

ICD-10-CM codes for Lens SNOMED Conditions can still be retrieved from LENS_SNOMED_CONDITION_CODE_CODING by filtering for the ICD-10-CM system (http://hl7.org/fhir/sid/icd-10-cm).

Table: LENS_TRANSITION_OF_CARE_DIAGNOSIS

• Renamed CONDITION_ID to LENS_SNOMED_CONDITION_ID.

LENS_TRANSITION_OF_CARE_DIAGNOSIS now links to LENS_SNOMED_CONDITION instead of the base CONDITION table. Update joins to reference LENS_SNOMED_CONDITION using the renamed column. See Renamed-but-not-relocated columns that change semantics.

Table: MEDICATION_ADMINISTRATION

• Renamed MEDICATION_CONCEPT_ID to MEDICATION_ADMINISTRATION_MEDICATION_CODE_ID. See Migrating from MEDICATION_CONCEPT.
• Renamed DOSAGE_ROUTE_CONCEPT_ID to MEDICATION_ADMINISTRATION_DOSAGE_ROUTE_ID. See Migrating from ROUTE_CONCEPT.

Table: MEDICATION_DISPENSE

• Renamed MEDICATION_CONCEPT_ID to MEDICATION_DISPENSE_MEDICATION_CODE_ID. See Migrating from MEDICATION_CONCEPT.
• Removed DOSAGE_ID. Dosage text instructions remain available in the DOSAGE_INSTRUCTION varchar column on MEDICATION_DISPENSE.

Table: MEDICATION_DISPENSE_IDENTIFIER

• Removed IDENTIFIER_ID. See Migrating from IDENTIFIER.

Table: MEDICATION_REQUEST

• Renamed MEDICATION_CONCEPT_ID to MEDICATION_REQUEST_MEDICATION_CODE_ID. See Migrating from MEDICATION_CONCEPT.
• Renamed DOSAGE_ID to MEDICATION_REQUEST_DOSAGE_ID. See Migrating from DOSAGE.

Table: MEDICATION_REQUEST_IDENTIFIER

• Removed IDENTIFIER_ID. See Migrating from IDENTIFIER.

Table: MEDICATION_STATEMENT

• Renamed MEDICATION_CONCEPT_ID to MEDICATION_STATEMENT_MEDICATION_CODE_ID. See Migrating from MEDICATION_CONCEPT.
• Renamed DOSAGE_ID to MEDICATION_STATEMENT_DOSAGE_ID. See Migrating from DOSAGE.

Table: MEDICATION_STATEMENT_IDENTIFIER

• Removed IDENTIFIER_ID. See Migrating from IDENTIFIER.

Table: OBSERVATION

• Renamed BODY_SITE_CONCEPT_ID to OBSERVATION_BODY_SITE_ID. See Migrating from BODY_SITE_CONCEPT.

Table: OBSERVATION_IDENTIFIER

• Removed IDENTIFIER_ID. See Migrating from IDENTIFIER.

Table: PATIENT_NAME

• Removed HUMAN_NAME_ID. See Migrating from HUMAN_NAME.

Table: PROCEDURE

• Renamed BODY_SITE_CONCEPT_ID to PROCEDURE_BODY_SITE_ID. See Migrating from BODY_SITE_CONCEPT.

Table: PROCEDURE_IDENTIFIER

• Removed IDENTIFIER_ID. See Migrating from IDENTIFIER.

Table: RELATED_PERSON_IDENTIFIER

• Removed IDENTIFIER_ID. See Migrating from IDENTIFIER.

Table: RELATED_PERSON_TELECOM

• Removed CONTACT_POINT_ID. See Migrating from CONTACT_POINT.

New tables

LENS_RXNORM_MEDICATION_STATEMENT_AGGREGATED_FROM

This table exposes the source medication resources that were combined to produce each LENS_RXNORM_MEDICATION_STATEMENT. Each row links a Lens RxNorm Medication Statement to one of its source resources. Use this table to trace a summarized medication entry back to the raw clinical events it was derived from.

LENS_SNOMED_CONDITION_AGGREGATED_FROM

This table exposes the source Condition resources that were combined to produce each LENS_SNOMED_CONDITION. Use this table to trace a deduplicated Lens SNOMED Condition back to the raw Condition resources from which it was derived.

RELATED_PERSON_NAME

A structured name table for RELATED_PERSON resources, following the same pattern as PATIENT_NAME. This table provides a normalized representation of related person names including use, display text, individual name parts, and validity periods.

Complete column mapping reference

This is the authoritative, flat mapping of every removed or renamed v1 column to its v2 location. It is intended to be searchable and copy-able. Rows where the v2 column name equals the v1 column name reflect a same-name column whose semantic meaning or target table changed (read the linked section).

Inverse lookup: removed v1 tables → v2 destinations