June 19, 2024: Decommissioning select tables in legacy data mart schema
After careful consideration, the following tables in the legacy data mart schema will be decommissioned due to their limited usage and value to analytics use cases:
- DeviceUseStatement
- DocumentReference_Author
- LocationTag
- PatientTag
- Child tables for contained resources: DiagnosticReportContained, EncounterContained, ImmunizationContained, MedicationDispenseContained, and MedicationRequestContained
On July 8, 2024, the above tables will be removed.
They are no longer actively maintained with the latest data, updates, bug fixes, or support for issues. Please ensure that you have migrated your integration before this date to avoid disruptions to your service.
Why is Zus decommissioning these tables?
We are decommissioning these tables to make way for significant improvements and enhancements with the relational schema and to reduce the number of supported tables in the data mart schema that have limited analytics value. Specifically, contained resources do not have an independent existence apart from the resource that contains them. They cannot be identified independently, nor can they have their own independent transaction scope. Contained resources are used for a small minority of FHIR resources in Zus:
- Less than .1% of DiagnosticReport resources, and used to represent Observation and Practitioner
- Less than .1% of Encounter resources, and used to represent Condition, Location, and Practitioner
- Less than .1% of Immunization resources, and sed to represent Observation and Organization
- Less than 1% of MedicationRequest resources, and used to represent Condition, Medication, Practitioner, and Organization
- Less than 4% of MedicationDispense resources, and used to represent Medication, Location, and Organization
How can I migrate to the relational schema if I'm depending on these tables?
If you would like to access the same data, you can transition to the relational schema
DeviceUseStatement
| Legacy Schema | Relational Schema |
|---|---|
| DEVICEUSESTATEMENT table to be removed | DEVICE_USE_STATEMENT table is available |
DocumentReference_Author
| Legacy Schema | Relational Schema |
|---|---|
| DOCUMENTREFERENCE_AUTHOR to be removed | AUTHOR_PRACTITIONER_ID, AUTHOR_ORGANIZATON_ID, and AUTHOR_DEVICE_ID columns are available in the DOCUMENT_REFERENCE table |
LocationTag and PatientTag
The full FHIR resource is available in the RESOURCE_JSON column.
| Legacy Schema | Relational Schema SQL |
|---|---|
| LOCATIONTAG table to be removed | select tags.VALUE:code::varchar as tag_code, tags.VALUE:system::varchar as tag_system, tags.VALUE:display::varchar as tag_display from location, lateral flatten (input => resource_json:meta:tag) as tags |
| PATIENTTAG table to be removed | select tags.VALUE:code::varchar as tag_code, tags.VALUE:system::varchar as tag_system, tags.VALUE:display::varchar as tag_display from patient, lateral flatten (input => resource_json:meta:tag) as tags ; |
DiagnosticReportContained, EncounterContained, ImmunizationContained, MedicationDispenseContained, and MedicationRequestContained
The full FHIR resource is available in the RESOURCE_JSON column.
| Legacy Schema | Relational Schema SQL |
|---|---|
| DIAGNOSTICREPORTCONTAINED to be removed | select id as fhir_id, containeds.VALUE:name as name, containeds.VALUE:subject as subject, containeds.VALUE:valueQuantity as valueQuantity, containeds.VALUE:resourceType::varchar as resourceType, containeds.VALUE:referenceRange as referenceRange, containeds.VALUE:valueString as valueString, containeds.VALUE:interpretation as interpretation, containeds.VALUE:text as text, containeds.VALUE:code as code, containeds.VALUE:valueRange as valueRange, containeds.VALUE:valueDateTime as valueDateTime, containeds.VALUE.id::varchar as id, containeds.VALUE:note as note, containeds.VALUE:status::varchar as status, containeds.VALUE:telecom as telecom, containeds.VALUE:address as address, containeds.VALUE:category as category, containeds.VALUE:dataAbsentReason as dataAbsentReason, containeds.VALUE:valueCodeableConcept as valueCodeableConcept, containeds.VALUE:effectiveDateTime as effectiveDateTime, containeds.VALUE:performer as perfoormer, builder_id, upid from diagnostic_report, lateral flatten (input=>resource_json:contained) as containeds |
| ENCOUNTERCONTAINED to be removed | select id as fhir_id, containeds.VALUE:name as name, containeds.VALUE:clinicalStatus as clinicalStatus, containeds.VALUE:subject as subject, containeds.VALUE:qualification as qualification, containeds.VALUE:resourceType::varchar as resourceType, containeds.VALUE:recordedDate::varchar as recordedDate, containeds.VALUE:text as text, containeds.VALUE:code as code, containeds.VALUE.id::varchar id, containeds.VALUE:telecom as telecom, containeds.VALUE:address as address, containeds.VALUE:category as category, containeds.VALUE:type as type, builder_id, upid from encounter, lateral flatten (input=>resource_json:contained) as containeds |
| IMMUNIZATIONCONTAINED to be removed | select id as fhir_id, containeds.VALUE:name::varchar as name, containeds.VALUE:subject as subject, containeds.VALUE:identifier as identifier, containeds.VALUE:resourceType::varchar as resourcetype, containeds.VALUE:code as code, containeds.VALUE.id::varchar as id, containeds.VALUE:status::varchar as status, containeds.VALUE:dataAbsentReason as dataAbsentReason, containeds.VALUE:type as type, builder_id, upid from immunization, lateral flatten (input=>resource_json:contained) as containeds |
| MEDICATIONDISPENSECONTAINED to be removed | select id as fhir_id, containeds.VALUE:name::varchar as name, containeds.VALUE:identifier as identifier, containeds.VALUE:resourceType::varchar as resourceType, containeds.VALUE:ingredient as ingredient, containeds.VALUE:code as code, containeds.VALUE.id::varchar as id, containeds.VALUE:telecom as telecom, containeds.VALUE:address as address, containeds.VALUE:extension as extension, builder_id, upid from medication_dispense, lateral flatten (input=>resource_json:contained) as containeds |
| MEDICATIONREQUESTCONTAINED to be removed | select id as fhir_id, containeds.VALUE:name::varchar as name, containeds.VALUE:subject as subject, containeds.VALUE:identifier as identifier, containeds.VALUE:resourceType::varchar as resourceType, containeds.VALUE:ingredient as ingredient, containeds.VALUE:code as code, containeds.VALUE.id::varchar as id, containeds.VALUE:meta as meta, containeds.VALUE:note as note, containeds.VALUE:telecom as telecom, containeds.VALUE:address as address, builder_id, upid from medication_request, lateral flatten (input=>resource_json:contained) as containeds |
Contact Us
If you have any questions or encounter any issues during the migration process, please do not hesitate to reach out to our support team at [email protected]. We are here to assist you and ensure a seamless transition.
We appreciate your continued support and cooperation as we work to provide you with the best possible service. Thank you for being a valued part of our data mart user community.