Ingest three related HL7 FHIR R5 clinical resource types (Condition, Procedure, AllergyIntolerance) from a single directory, demonstrating file-filter-based resource separation, deep nested arrays, and cross-resource coding patterns.
-- 1. Zone + schema
CREATE ZONE IF NOT EXISTS {{zone_name}} TYPE EXTERNAL
COMMENT 'External tables, demo datasets and file-backed data';
CREATE SCHEMA IF NOT EXISTS {{zone_name}}.fhir_demos
COMMENT 'FHIR-backed external tables: HL7 FHIR R5 resources as JSON';
-- 2. Conditions (diagnoses)
CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.fhir_demos.conditions
USING JSON
LOCATION '{{data_path}}'
OPTIONS (
file_filter = 'condition-example*.json',
json_flatten_config = '{
"root_path": "$",
"include_paths": [
"$.resourceType", "$.id", "$.clinicalStatus", "$.verificationStatus",
"$.category", "$.severity", "$.code", "$.bodySite", "$.subject",
"$.onsetDateTime", "$.onsetAge", "$.abatementDateTime", "$.abatementAge",
"$.recordedDate", "$.evidence", "$.stage", "$.note"
],
"json_paths": ["$.clinicalStatus", "$.verificationStatus", "$.severity",
"$.code", "$.subject", "$.onsetAge", "$.abatementAge"],
"column_mappings": {
"$.id": "condition_id",
"$.clinicalStatus": "clinical_status",
"$.verificationStatus": "verification_status",
"$.bodySite": "body_site",
"$.onsetDateTime": "onset_date",
"$.abatementDateTime": "abatement_date",
"$.recordedDate": "recorded_date"
},
"max_depth": 4, "separator": "_",
"default_array_handling": "to_json", "infer_types": true
}',
file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);
-- 3. Procedures (surgical interventions)
CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.fhir_demos.procedures
USING JSON
LOCATION '{{data_path}}'
OPTIONS (
file_filter = 'procedure-example*.json',
json_flatten_config = '{
"include_paths": [
"$.resourceType", "$.id", "$.status", "$.code", "$.subject",
"$.occurrenceDateTime", "$.performer", "$.reason", "$.bodySite",
"$.outcome", "$.followUp", "$.note", "$.complication", "$.focalDevice"
],
"json_paths": ["$.performer", "$.reason", "$.followUp", "$.code",
"$.subject", "$.outcome"],
"column_mappings": {
"$.id": "procedure_id",
"$.occurrenceDateTime": "occurrence_date",
"$.bodySite": "body_site",
"$.focalDevice": "focal_device"
},
"max_depth": 4, "separator": "_", "infer_types": true
}',
file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);
-- 4. Allergies
CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.fhir_demos.allergies
USING JSON
LOCATION '{{data_path}}'
OPTIONS (
file_filter = 'allergyintolerance*.json',
json_flatten_config = '{
"include_paths": [
"$.resourceType", "$.id", "$.clinicalStatus", "$.verificationStatus",
"$.type", "$.category", "$.criticality", "$.code", "$.patient",
"$.onsetDateTime", "$.recordedDate", "$.lastOccurrence", "$.reaction"
],
"json_paths": ["$.reaction", "$.clinicalStatus", "$.verificationStatus",
"$.type", "$.code", "$.patient"],
"column_mappings": {
"$.id": "allergy_id",
"$.clinicalStatus": "clinical_status",
"$.onsetDateTime": "onset_date",
"$.lastOccurrence": "last_occurrence"
},
"max_depth": 4, "separator": "_", "infer_types": true
}',
file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);
-- 5. Cross-resource summary
SELECT 'Conditions' AS resource_type, COUNT(*) AS n FROM {{zone_name}}.fhir_demos.conditions
UNION ALL
SELECT 'Procedures', COUNT(*) FROM {{zone_name}}.fhir_demos.procedures
UNION ALL
SELECT 'Allergies', COUNT(*) FROM {{zone_name}}.fhir_demos.allergies
ORDER BY resource_type;
-- 6. Cleanup
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.fhir_demos.conditions WITH FILES;
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.fhir_demos.procedures WITH FILES;
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.fhir_demos.allergies WITH FILES;
DROP SCHEMA IF EXISTS {{zone_name}}.fhir_demos;## When to Use Use this demo when building a unified patient chart view that spans multiple clinical resource types. In real EHR exports, Conditions (diagnoses), Procedures (surgical interventions), and AllergyIntolerance records are frequently delivered as a mixed directory of JSON files. This demo shows how to separate them by filename prefix, preserve the deep clinical detail (`reaction[]`, `performer[]`, `stage`, `evidence`), and run cross-resource queries while keeping each resource type in its own strongly-shaped table. ## What You Will Learn 1. How `file_filter` patterns (`condition-example*.json`, `procedure-example*.json`, `allergyintolerance*.json`) carve three typed tables from one directory. 2. How `json_paths` preserves FHIR's deeply nested arrays: `reaction[].manifestation[].concept.coding[]`, `performer[].actor`, `stage`, `evidence`, as JSON blobs that remain queryable but do not explode into dozens of columns. 3. How SNOMED CT and ICD-10 `CodeableConcept` hierarchies appear in FHIR Conditions and Procedures, and how to surface the primary code display via `column_mappings`. 4. How FHIR `clinicalStatus` vs `verificationStatus` encode orthogonal life-cycle and certainty dimensions (active/resolved vs confirmed/provisional/refuted). 5. How `Condition.onsetDateTime` + `abatementDateTime` model disease duration, including how NKA / NKDA / NKLA "no known allergy" assertions differ structurally from real allergies. 6. How FHIR references (`subject`, `patient`) link clinical records back to Patient resources for longitudinal analysis. ## Prerequisites - Completion of `fhir-patient-demographics` and `fhir-clinical-observations` (recommended). - Familiarity with FHIR CodeableConcept structure and with SNOMED CT / ICD-10 coding systems. - Workspace permission to `CREATE ZONE`, `CREATE SCHEMA`, and `CREATE EXTERNAL TABLE`.