health_intelligence_ddl.sql

-- Health Intelligence Database DDL for Snowflake
-- Designed for Cortex Analyst natural language querying

-- Create database and schema
CREATE DATABASE IF NOT EXISTS HEALTH_INTELLIGENCE;
USE DATABASE HEALTH_INTELLIGENCE;
CREATE SCHEMA IF NOT EXISTS HEALTH_RECORDS;
USE SCHEMA HEALTH_RECORDS;

-- Create stage for semantic model files
CREATE STAGE IF NOT EXISTS RAW_DATA
    FILE_FORMAT = (TYPE = 'JSON');

-- Grant Cortex Analyst access
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE ACCOUNTADMIN;

-- Table 1: PATIENTS - Patient demographics
CREATE TABLE IF NOT EXISTS PATIENTS (
    PATIENT_ID VARCHAR(36) PRIMARY KEY DEFAULT UUID_STRING(),
    PATIENT_IDENTITY VARCHAR(255) NOT NULL,
    DATE_OF_BIRTH DATE NOT NULL,
    PATIENT_AGE INTEGER,
    CREATED_AT TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    UPDATED_AT TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

-- Table 2: IMPORTS - Track data import operations
CREATE TABLE IF NOT EXISTS IMPORTS (
    IMPORT_ID VARCHAR(36) PRIMARY KEY DEFAULT UUID_STRING(),
    PATIENT_ID VARCHAR(36) NOT NULL,
    IMPORT_TIMESTAMP TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    SOURCE_FILES VARCHAR(4000),
    RECORDS_BY_CATEGORY VARCHAR(4000),
    IMPORT_STATISTICS VARCHAR(16000),
    TOTAL_RECORDS INTEGER,
    IMPORT_STATUS VARCHAR(50) DEFAULT 'COMPLETED',
    FOREIGN KEY (PATIENT_ID) REFERENCES PATIENTS(PATIENT_ID)
);

-- Table 3: HEALTH_RECORDS - Unified table for all health data
CREATE TABLE IF NOT EXISTS HEALTH_RECORDS (
    RECORD_ID VARCHAR(36) PRIMARY KEY DEFAULT UUID_STRING(),
    PATIENT_ID VARCHAR(36) NOT NULL,
    IMPORT_ID VARCHAR(36) NOT NULL,
    RECORD_CATEGORY VARCHAR(50) NOT NULL,
    RECORD_DATE DATE NOT NULL,
    PROVIDER VARCHAR(255),
    
    -- Common fields for all record types
    ITEM_DESCRIPTION VARCHAR(500),
    VALUE_TEXT VARCHAR(1000),
    VALUE_NUMERIC DECIMAL(18,6),
    MEASUREMENT_DIMENSION VARCHAR(50),
    REFERENCE_RANGE VARCHAR(200),
    FLAG VARCHAR(100),
    TEST_CATEGORY VARCHAR(100),
    
    -- Medication-specific fields
    DOSAGE VARCHAR(100),
    FORM VARCHAR(100),
    FOR_CONDITION VARCHAR(255),
    FREQUENCY VARCHAR(100),
    MEDICATION_STATUS VARCHAR(50),
    
    -- Vital signs specific fields
    VITAL_CATEGORY VARCHAR(100),
    
    -- Clinical data specific fields
    CONDITION_STATUS VARCHAR(50),
    VACCINE_CATEGORY VARCHAR(100),
    PROCEDURE_CATEGORY VARCHAR(100),
    ALLERGY_CATEGORY VARCHAR(100),
    
    -- Metadata
    CREATED_AT TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    UPDATED_AT TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    
    -- Foreign keys
    FOREIGN KEY (PATIENT_ID) REFERENCES PATIENTS(PATIENT_ID),
    FOREIGN KEY (IMPORT_ID) REFERENCES IMPORTS(IMPORT_ID)
);

-- Add clustering for performance on frequently filtered columns
ALTER TABLE HEALTH_RECORDS CLUSTER BY (PATIENT_ID, RECORD_CATEGORY, RECORD_DATE);

-- Table comments for documentation
COMMENT ON TABLE PATIENTS IS 'Patient demographics and basic information';
COMMENT ON TABLE IMPORTS IS 'Track data import batches with metadata and statistics';
COMMENT ON TABLE HEALTH_RECORDS IS 'Unified storage for all health data types (labs, medications, vitals, clinical data)';

-- Column comments for key fields
COMMENT ON COLUMN PATIENTS.PATIENT_IDENTITY IS 'Patient full name or identifier';
COMMENT ON COLUMN HEALTH_RECORDS.RECORD_CATEGORY IS 'Type of health record: LAB, MEDICATION, VITAL, CONDITION, PROCEDURE, ALLERGY, IMMUNIZATION';
COMMENT ON COLUMN HEALTH_RECORDS.ITEM_DESCRIPTION IS 'Name or description of the health item (test name, medication name, vital sign type, etc.)';
COMMENT ON COLUMN HEALTH_RECORDS.VALUE_TEXT IS 'Text value for qualitative results (POSITIVE, NEGATIVE, etc.)';
COMMENT ON COLUMN HEALTH_RECORDS.VALUE_NUMERIC IS 'Numeric value for quantitative results';
COMMENT ON COLUMN HEALTH_RECORDS.MEASUREMENT_DIMENSION IS 'Unit of measurement (mg/dL, mmHg, %, etc.)';
COMMENT ON COLUMN HEALTH_RECORDS.FOR_CONDITION IS 'Medical condition this medication treats';
COMMENT ON COLUMN HEALTH_RECORDS.FREQUENCY IS 'Medication dosing frequency (once daily, twice daily, etc.)';