-- Elation Analyst Installer for standalone Snowflake accounts SET target_database = '<%target_database%>'; SET target_schema = '<%target_schema%>'; SET warehouse_name = '<%warehouse_name%>'; -- Don't change anything below this line ---------------------------------------- SET listing_global_name = 'GZSTZWIAJIJ'; SET account_management_db = 'ELATIONHEALTH_ACCOUNT_MANAGEMENT'; SET semantic_models_schema = (SELECT $account_management_db || '.SEMANTIC_MODELS'); USE ROLE ACCOUNTADMIN; CREATE DATABASE IF NOT EXISTS IDENTIFIER($account_management_db); CREATE SCHEMA IF NOT EXISTS IDENTIFIER($semantic_models_schema); -- Semantic model: appointment_semantic_model.yml SELECT SYSTEM$WRITE_SEMANTIC_MODEL_YAML($semantic_models_schema, 'name: APPOINTMENT_SEMANTIC_MODEL tables: - name: APPOINTMENT base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: APPOINTMENT primary_key: columns: - SERVICE_LOCATION_ID - PATIENT_ID - PHYSICIAN_USER_ID - ID dimensions: - name: APPT_TYPE synonyms: - appointment_type - visit_type - consultation_type - procedure_type - service_category - appointment_category - visit_category description: The type of appointment, such as new patient, follow-up, consultation, or procedure. expr: APPT_TYPE data_type: VARCHAR(16777216) - name: DURATION synonyms: - length - time_span - time_length - appointment_length - session_duration - time_duration - meeting_length - event_duration description: The length of time scheduled for an appointment. expr: DURATION data_type: NUMBER(38,0) - name: IN_PERSON_OR_VIRTUAL synonyms: - in_person - virtual - meeting_type - appointment_format - session_mode - delivery_method - interaction_type - meeting_style description: Indicates whether the appointment was conducted in person or virtually. expr: IN_PERSON_OR_VIRTUAL data_type: VARCHAR(16777216) - name: PRACTICE_ID expr: PRACTICE_ID description: Unique identifier for the medical practice synonyms: - clinic_id - office_id - medical_group_id - provider_id - facility_id - organization_id data_type: NUMBER(38, 0) - name: PHYSICIAN_USER_ID expr: PHYSICIAN_USER_ID description: The unique identifier of the physician user who is assigned to or scheduled for the appointment. synonyms: - physician_id - doctor_id - healthcare_provider_id - medical_professional_id - practitioner_id - attending_physician_id - treating_doctor_id data_type: NUMBER(38, 0) facts: - name: DELETION_TIME synonyms: - deletion_timestamp - deleted_at - removal_time - purge_date - expiration_time - archival_time - deletion_date - removed_at description: The date and time when the appointment was deleted from the system. expr: DELETION_TIME data_type: TIMESTAMP_NTZ(9) - name: ID synonyms: - identifier - unique_id - record_id - key - unique_identifier - primary_key description: Unique identifier for the appointment. expr: ID data_type: NUMBER(38,0) - name: PATIENT_ID synonyms: - patient_number - patient_identifier - patient_code - person_id - individual_id - medical_record_number - patient_key description: Unique identifier for the patient who has scheduled an appointment. expr: PATIENT_ID data_type: NUMBER(38,0) - name: SERVICE_LOCATION_ID synonyms: - location_id - service_location - facility_id - site_id - clinic_id - department_id - service_site_id - location_identifier description: The unique identifier of the location where the appointment will take place, such as a clinic, hospital, or office. expr: SERVICE_LOCATION_ID data_type: NUMBER(38,0) filters: - name: not_deleted synonyms: - active - existing - present - undeleted - not_removed - valid - current description: Indicates whether the appointment has been deleted or not. expr: DELETION_TIME IS NULL - name: valid_patient_appointment synonyms: - scheduled_patient_appointment description: Indicates whether the appointment is valid for a patient. expr: PATIENT_ID IS NOT NULL time_dimensions: - name: APPT_TIME synonyms: - appointment_time - scheduled_time - appointment_schedule - visit_time - appointment_datetime - scheduled_appointment_time description: The scheduled time of the appointment. expr: APPT_TIME data_type: TIMESTAMP_NTZ(9) - name: CREATION_TIME synonyms: - created_at - creation_date - timestamp_created - record_creation_time - time_created - date_created - inception_time - initialization_time - setup_time description: The date and time when the appointment was created. expr: CREATION_TIME data_type: TIMESTAMP_NTZ(9) description: This table stores information about scheduled appointments, including the appointment details, patient and physician information, practice and service location details, and billing information. It tracks the appointment status, including creation, modification, and deletion, and maintains a record of the appointment''s history. synonyms: - appointments - scheduled_visits - patient_visits - doctor_appointments - medical_appointments - scheduled_appointments - patient_scheduled_visits - doctor_scheduled_visits - name: APPOINTMENT_STATUS base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: APPOINTMENT_STATUS primary_key: columns: - APPOINTMENT_ID dimensions: - name: STATUS expr: STATUS description: The current status of an appointment, such as scheduled, in progress, completed, cancelled, or no-show. synonyms: - state - condition - situation - position - standing - circumstance data_type: VARCHAR(16777216) sample_values: - scheduled - checkedOut - notSeen - cancelled - checkedIn - inRoom - confirmed - vitalsTaken - billed - withDr facts: - name: APPOINTMENT_ID synonyms: - appointment_number - appointment_reference - appointment_key - schedule_id - booking_id - appointment_identifier description: Unique identifier for each appointment. expr: APPOINTMENT_ID data_type: NUMBER(38,0) description: This table stores the status history of appointments, including the current status, any notes or comments, and the user who made the update, as well as the timestamp of when the update was made. It also tracks when an appointment status record is deleted and by whom. synonyms: - appointment_status - appointment_info - appointment_details - schedule_status - booking_status - meeting_status - appointment_record - name: PATIENT base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT primary_key: columns: - ID dimensions: - name: DETAILED_PRIMARY_ETHNICITY synonyms: - specific_primary_ethnicity - detailed_ethnicity_origin - primary_ethnicity_description - ethnic_background_detail - precise_ethnicity - primary_ethnicity_subtype description: The patient''s primary ethnicity, categroized as either "Hispanic or Latino" or "Not Hispanic or Latino". expr: DETAILED_PRIMARY_ETHNICITY data_type: VARCHAR(50) sample_values: - Not Hispanic or Latino - Hispanic or Latino - No ethnicity specified - Declined To Specify - name: EMAIL synonyms: - email_address - contact_email - patient_email - electronic_mail - email_id description: The email address of the patient. expr: EMAIL data_type: VARCHAR(16777216) - name: GENDER_IDENTITY synonyms: - gender - sex - gender_type - gender_category - personal_gender - self_identified_gender description: The patient''s self-identified gender, which may not necessarily align with their biological sex or the sex they were assigned at birth. expr: GENDER_IDENTITY data_type: VARCHAR(39) sample_values: - Woman - Unknown - Man - name: MARITAL_STATUS synonyms: - marital_state - relationship_status - civil_status - family_status - partnership_status description: The current marital status of the patient, such as single, married, divorced, widowed, etc. expr: MARITAL_STATUS data_type: VARCHAR(16777216) - name: PATIENT_STATUS synonyms: - patient_condition - patient_state - patient_category - patient_classification - patient_type - patient_description - patient_summary - patient_overview description: Indicates the current status of a patient''s relationship with the healthcare provider, where "active" means the patient is currently receiving care and "inactive" means the patient is no longer receiving care. expr: PATIENT_STATUS data_type: VARCHAR(16777216) sample_values: - active - inactive - deceased - prospect - name: RACE synonyms: - ethnicity - ancestry - heritage - cultural_bkg - demographic_race - racial_identity - racial_group - ethnic_origin description: The patient''s racial or ethnic identity, as reported or observed. expr: RACE data_type: VARCHAR(41) sample_values: - Asian - No race specified - White - Declined to specify - Black or African American - American Indian or Alaska Native - Native Hawaiian or Other Pacific Islander - name: SEX synonyms: - gender - biological_sex - sex_category - sex_type - sex_classification description: The sex of the patient, indicating whether the patient is male, female, or unknown. expr: SEX data_type: VARCHAR(16777216) sample_values: - Male - Female - Unknown - Intersex/Other - name: SEXUAL_ORIENTATION synonyms: - sexual_identity - sexual_preference - sexual_affiliation - sexual_inclination - romantic_orientation description: The patient''s self-identified sexual orientation, which can be used to understand their personal characteristics and preferences, and to provide culturally sensitive care. expr: SEXUAL_ORIENTATION data_type: VARCHAR(16777216) sample_values: - Straight - Bisexual - Unknown - Gay - Queer - Prefer not to say - Asexual - Option not listed - Lesbian - name: FIRST_NAME expr: FIRST_NAME description: The patient''s first name. synonyms: - given_name - first_name_field - personal_name - christian_name - forename data_type: VARCHAR(16777216) - name: LAST_NAME expr: LAST_NAME description: The patient''s last name. synonyms: - surname - family_name - last_name_field - full_last_name - person_last_name data_type: VARCHAR(16777216) facts: - name: DECEASED_DATE synonyms: - date_of_death - date_of_passing - deceased_on - date_deceased - death_date - passed_away_date description: Date of death for the patient, if applicable. expr: DECEASED_DATE data_type: DATE sample_values: - ''2024-05-22'' - ''2023-11-15'' - name: ID synonyms: - unique_identifier - identifier - patient_identifier - patient_id - unique_patient_id - record_id description: Unique identifier assigned to each patient for tracking and record-keeping purposes. expr: ID data_type: NUMBER(38,0) filters: - name: not_deceased synonyms: - alive - living - not passed away - non deceased description: Indicates whether the patient is deceased or not. expr: DECEASED_DATE IS NULL time_dimensions: - name: DELETION_TIME synonyms: - deletion_timestamp - deleted_at - removed_time - deletion_date - deleted_on - removal_timestamp description: The date and time when a patient''s record was deleted from the system. expr: DELETION_TIME data_type: TIMESTAMP_NTZ(9) - name: DOB synonyms: - date_of_birth - birth_date - birthdate - birthday - age - date_of_entry description: Date of Birth of the patient. expr: DOB data_type: DATE sample_values: - ''1977-12-18'' - ''1995-06-07'' - ''1980-02-03'' description: This table stores information about patients, including their demographic data, contact information, medical history, and relationships with healthcare providers. It also tracks changes to patient status, emergency contact information, and other relevant details. synonyms: - patient - individual - person - client - customer - subject - case - record - profile - demographic - name: PATIENT_IMMUNIZATION base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT_IMMUNIZATION dimensions: - name: NAME synonyms: - vaccine_name - immunization_name - shot_name - vaccine_type - immunization_type description: The type of vaccine administered to the patient. expr: NAME data_type: VARCHAR(16777216) facts: - name: PATIENT_ID synonyms: - patient_number - patient_identifier - patient_key - person_id - individual_id - subject_id - patient_reference description: Unique identifier for the patient who received the immunization. expr: PATIENT_ID data_type: NUMBER(38,0) filters: - name: not_deleted synonyms: - active - existing - present - retained - undeleted - not_removed - still_there - intact - preserved description: Indicates whether the patient''s immunization record has been deleted or is still active. expr: DELETION_TIME IS NOT NULL time_dimensions: - name: ADMINISTERED_DATE synonyms: - vaccination_date - immunization_date - date_administered - date_given - date_vaccinated - administration_date description: Date and time when the immunization was administered to the patient. expr: ADMINISTERED_DATE data_type: TIMESTAMP_NTZ(9) sample_values: - 2022-10-19T21:00:00.000+0000 - 2021-11-08T21:00:00.000+0000 - 2021-04-09T21:00:00.000+0000 - name: DELETION_TIME synonyms: - deletion_timestamp - deleted_at - removal_time - deleted_date - purge_time - expiration_time - archival_time - removal_timestamp description: The date and time when the patient''s immunization record was deleted from the system. expr: DELETION_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2021-03-16T09:23:57.000+0000 - 2024-07-10T07:07:24.000+0000 - name: EXPIRATION_DATE synonyms: - expiration - end_date - validity_date - termination_date - deadline - lapse_date - due_date - end_of_validity description: The date by which the patient''s immunization is no longer considered effective. expr: EXPIRATION_DATE data_type: DATE sample_values: - ''2021-10-31'' - ''2020-01-01'' description: This table stores information about immunizations administered to patients, including the type of vaccine, administering physician, date, dosage, and other relevant details, as well as metadata about the record itself, such as creation and modification timestamps and user IDs. synonyms: - Vaccination Record - Immunization History - Patient Vaccination - Shot Record - Immunization Data - Vaccination Details - Patient Shot Record - Immunization Information - Vaccination History Record - name: PATIENT_PHONE base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT_PHONE primary_key: columns: - PATIENT_ID dimensions: - name: PHONE synonyms: - phone_number - contact_number - mobile_number - landline_number - home_phone - work_phone - cell_phone - telephone_number - contact_info description: The phone number associated with a patient. expr: PHONE data_type: VARCHAR(16777216) - name: PHONE_TYPE synonyms: - phone_category - call_type - communication_method - phone_classification - contact_type - dialing_type description: The type of phone number provided by the patient, such as cell phone, main phone, or home phone. expr: PHONE_TYPE data_type: VARCHAR(16777216) sample_values: - Cell Phone - Main - Home facts: - name: IS_DELETED synonyms: - is_inactive - is_archived - is_removed - deleted_flag - is_disabled - is_hidden description: Indicates whether the patient''s phone number has been deleted or is still active. expr: IS_DELETED data_type: BOOLEAN sample_values: - ''FALSE'' - ''TRUE'' - name: PATIENT_ID synonyms: - patient_number - patient_identifier - patient_key - person_id - individual_id - medical_record_number - patient_reference description: Unique identifier for a patient in the healthcare system. expr: PATIENT_ID data_type: NUMBER(38,0) filters: - name: not_deleted synonyms: - active - existing - present - available - not_removed - valid - current - in_use description: Indicates whether the patient''s phone number is still active and in use (not deleted) or has been removed from the system. expr: IS_DELETED = false description: This table stores phone number information for patients, including the patient''s ID, phone number, phone type, and last modified date, with the ability to track deleted records and synchronize with an external warehouse. synonyms: - patient_phone_numbers - patient_contact_info - patient_telephone - patient_communication_details - patient_phone_list - name: SERVICE_LOCATION base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: SERVICE_LOCATION primary_key: columns: - ID dimensions: - name: NAME synonyms: - title - designation - label - identifier - appellation description: The physical or virtual location where a service is provided or supported. expr: NAME data_type: VARCHAR(200) sample_values: - dfds - Another locations - Elation Support Internal facts: - name: ID synonyms: - identifier - unique_id - record_id - key - unique_identifier - primary_key description: Unique identifier for a specific service location. expr: ID data_type: NUMBER(38,0) filters: - name: not_deleted synonyms: - active - existing - not_removed - present - retained - undeleted - valid description: Indicates whether the service location is active and available for use (not deleted) or has been removed from the system (deleted). expr: DELETION_TIME IS NULL time_dimensions: - name: DELETION_TIME synonyms: - DELETED_AT - DELETED_ON - DELETION_DATE - DELETED_TIMESTAMP - REMOVAL_TIME - ARCHIVAL_DATE - PURGE_DATE - REMOVAL_TIMESTAMP description: The date and time when the service location was deleted. expr: DELETION_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2024-01-23T06:42:05.000+0000 - 2023-11-13T11:06:34.000+0000 description: This table stores information about physical locations where medical services are provided, including addresses, contact details, and associations with specific medical practices, with tracking of creation, deletion, and synchronization metadata. synonyms: - service_locations - service_location_table - location_services - service_sites - practice_locations - service_addresses - name: PRACTICE base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PRACTICE dimensions: - name: NAME expr: NAME data_type: VARCHAR(200) description: The name of the individual or entity being referred to in the practice. synonyms: - title - label - designation - identifier - appellation - moniker - denomination facts: - name: ID expr: ID data_type: NUMBER(38,0) description: Unique identifier for a practice. synonyms: - identifier - unique_id - practice_id - record_id - key_id - unique_identifier - practice_identifier primary_key: columns: - ID description: This table stores information about medical practices, including their unique identifier, name, address, contact details, and warehouse association, with the most recent synchronization timestamp from a healthcare database. synonyms: - practice - medical practice - clinic - healthcare provider - medical facility - doctor''s office - healthcare facility - medical center - name: USER base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: USER dimensions: - name: IS_ACTIVE expr: IS_ACTIVE data_type: BOOLEAN description: Indicates whether the user account is currently active and enabled, or inactive and disabled. sample_values: - ''TRUE'' - ''FALSE'' synonyms: - active_status - enabled - status - is_enabled - activation_status - account_status - user_status - name: FIRST_NAME expr: FIRST_NAME data_type: VARCHAR(256) description: The first name of the user. sample_values: - Rebecca - Anthony - Joseph synonyms: - given_name - first_name - forename - personal_name - christian_name - name: LAST_NAME expr: LAST_NAME data_type: VARCHAR(256) description: The LAST_NAME column stores the last name of a user, providing a way to identify and distinguish between individuals. sample_values: - Troyer - Kennedy - Wortham synonyms: - surname - family_name - last_name_field - full_last_name - surname_name - name: USER_TYPE expr: USER_TYPE data_type: VARCHAR(9) description: The type of user accessing the system, which can be a staff member, a physician, or the system itself (e.g., automated processes). sample_values: - staff - physician - system synonyms: - user_role - user_category - account_type - user_classification - user_designation - role_type - user_status - name: TIN expr: TIN data_type: VARCHAR(10) description: Taxpayer Identification Number (TIN) assigned to the user, a unique identifier used for tax purposes. sample_values: - 32-0231140 - ''320231140'' synonyms: - taxpayer_identification_number - employer_identification_number - federal_tax_id - tax_id - ein facts: - name: ID expr: ID data_type: NUMBER(38,0) description: Unique identifier for a user in the system. sample_values: - ''2639024'' - ''2395626'' - ''1262091'' synonyms: - identifier - unique_id - user_id - record_id - key_id - unique_identifier - name: PRACTICE_ID expr: PRACTICE_ID data_type: NUMBER(38,0) description: Unique identifier for a medical practice or healthcare organization where a user is affiliated or associated. sample_values: - ''546238333255684'' - ''618839851270148'' - ''768220391342084'' synonyms: - practice_number - medical_group_id - clinic_id - office_id - organization_id - group_id - provider_id - facility_id - name: CANONICAL_PHYSICIAN_ID expr: CANONICAL_PHYSICIAN_ID data_type: NUMBER(38,0) description: Unique identifier for a physician in a standardized format, allowing for consistent referencing and matching across different systems and databases. sample_values: - ''840385383629090'' - ''880949009514786'' synonyms: - standardized_physician_id - normalized_physician_id - unique_physician_identifier - physician_id_standard - standardized_doctor_id - normalized_doctor_id primary_key: columns: - ID description: This table stores information about users within a healthcare organization, including their identification, contact details, role, and affiliations with practices and physicians. synonyms: - user_table - users - user_info - user_data - user_profile - user_account - user_details - name: PATIENT_TAG synonyms: - label - tag description: This table contains free-form labels or tags for a given patient. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT_TAG primary_key: columns: - PATIENT_ID dimensions: - name: PATIENT_ID synonyms: - patient_number - patient_identifier - patient_key - person_id - individual_id - subject_id description: Unique identifier for a patient in the healthcare system. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''605988843618305'' - ''572578348335105'' - ''565565298311169'' - name: TAG_VALUE synonyms: - tag_description - label - patient_label - patient_tag_description - patient_category - classification - patient_classification description: A unique identifier assigned to a patient. expr: TAG_VALUE data_type: VARCHAR(16777216) sample_values: - CRM Patient ID 10744 - CRM Patient ID 29532 - CRM Patient ID 17554 filters: - name: current_patient_tags_only synonyms: - not deleted - active description: Limits set of Patient Tags to only those that have not been deleted. expr: DELETION_TIME IS NOT NULL relationships: - name: APPOINTMENT_APPOINTMENT_STATUS left_table: APPOINTMENT right_table: APPOINTMENT_STATUS relationship_columns: - left_column: ID right_column: APPOINTMENT_ID relationship_type: many_to_one join_type: left_outer - name: APPOINTMENT_PATIENT_PHONE left_table: APPOINTMENT right_table: PATIENT_PHONE relationship_columns: - left_column: PATIENT_ID right_column: PATIENT_ID relationship_type: many_to_one join_type: left_outer - name: APPOINTMENT_SERVICE_LOCATION left_table: APPOINTMENT right_table: SERVICE_LOCATION relationship_columns: - left_column: SERVICE_LOCATION_ID right_column: ID relationship_type: one_to_one join_type: left_outer - name: PATIENT_APPOINTMENT left_table: APPOINTMENT right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: many_to_one join_type: inner - name: APPOINTMENT_PRACTICE join_type: inner relationship_type: many_to_one left_table: APPOINTMENT relationship_columns: - left_column: PRACTICE_ID right_column: ID right_table: PRACTICE - name: APPOINTMENT_PHYSICIAN_USER join_type: left_outer relationship_type: one_to_one left_table: APPOINTMENT relationship_columns: - left_column: PHYSICIAN_USER_ID right_column: ID right_table: USER - name: PATIENT_PATIENT_TAG left_table: PATIENT_TAG right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: many_to_one join_type: left_outer verified_queries: - name: All no-show appointments question: All no-show appointments sql: |- select a.id from appointment as a left join appointment_status as ast on a.id = ast.appointment_id where ast.status ilike ''notSeen'' use_as_onboarding_question: false custom_instructions: |- - use ilike over like and `=` - year means calendar year not 365 days - always dedup - filter out inactive and deceased patients unless otherwise specified - a no show patient is a patient with an appointments that its status is `notSeen`'); -- Semantic model: billing_code_semantic_model.yml SELECT SYSTEM$WRITE_SEMANTIC_MODEL_YAML($semantic_models_schema, 'name: BILLING_CODE_SEMANTIC_MODEL tables: - name: BILL base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: BILL primary_key: columns: - ID dimensions: - name: BILLING_STATUS synonyms: - payment_status - invoice_status - billing_state - charge_status - payment_state - invoice_state description: The current status of a bill, indicating whether it has been successfully billed, is awaiting resolution due to an error, or has not yet been billed. expr: BILLING_STATUS data_type: VARCHAR(16777216) sample_values: - Billed - ResolvedError - Unbilled - UnresolvedError - name: PRACTICE_ID synonyms: - practice_number - clinic_id - medical_group_id - provider_id - office_id - facility_id - organization_id description: Unique identifier for a medical practice or healthcare organization that is associated with the bill. expr: PRACTICE_ID data_type: NUMBER(38,0) sample_values: - ''5403894480900'' - ''235818542825476'' - ''854508817350660'' - name: REF_NUMBER synonyms: - reference_number - reference_id - ref_no - reference_code - invoice_number - order_number - transaction_id - claim_number description: Unique reference number assigned to each bill for tracking and identification purposes. expr: REF_NUMBER data_type: VARCHAR(16777216) sample_values: - ''114297'' - name: VISIT_NOTE_ID synonyms: - visit_id - note_id - encounter_id - patient_visit_id - visit_reference_id - note_reference_id description: Unique identifier for a visit note in a patient''s medical record. expr: VISIT_NOTE_ID data_type: NUMBER(38,0) sample_values: - ''204593294737432'' - ''690995235455000'' - ''515787619368984'' facts: - name: ID synonyms: - identifier - unique_id - record_id - primary_key - key_id - unique_identifier description: Unique identifier for a bill. expr: ID data_type: NUMBER(38,0) sample_values: - ''1029170276860060'' - ''1029149439623324'' - ''1029519075246236'' - name: PLACE_OF_SERVICE synonyms: - service_location - point_of_care - care_setting - treatment_location - service_delivery_location - facility_type description: The location where the medical service was provided, such as office, hospital, or clinic. expr: PLACE_OF_SERVICE data_type: NUMBER(38,0) sample_values: - ''10'' - ''11'' - name: SERVICE_LOCATION_ID synonyms: - location_id - service_location - facility_id - site_id - clinic_id - department_id - service_site_id - location_identifier description: Unique identifier for the location where the service was provided. expr: SERVICE_LOCATION_ID data_type: NUMBER(38,0) sample_values: - ''201104882925815'' - ''235504420782327'' - ''4494184677623'' filters: - name: billed synonyms: - invoiced - charged - paid - settled - accounted_for - paid_out - disbursed - expended description: The billed column indicates whether a bill has been sent to the customer or not, typically represented by a boolean value (true/false or yes/no) to track the billing status of an invoice or order. expr: BILLING_STATUS = ''Billed'' time_dimensions: - name: BILLING_DATE synonyms: - invoice_date - payment_date - charge_date - billing_timestamp - transaction_date - settlement_date - payment_due_date description: Date and time when the bill was generated. expr: BILLING_DATE data_type: TIMESTAMP_NTZ(9) sample_values: - 2021-08-10T08:05:43.000+0000 - 2024-11-09T05:14:18.000+0000 - name: CREATION_TIME synonyms: - created_at - creation_date - timestamp_created - record_creation_time - date_created - time_of_creation - creation_timestamp description: The date and time when the bill was created, in Coordinated Universal Time (UTC) format. expr: CREATION_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2025-07-02T04:14:19.000+0000 - 2025-07-02T08:39:09.000+0000 - 2025-07-02T07:00:24.000+0000 - name: BILL_ITEM base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: BILL_ITEM primary_key: columns: - ID - CPT dimensions: - name: BILL_ID synonyms: - invoice_id - bill_number - invoice_number - account_id - billing_id - payment_id description: Unique identifier for a bill in the billing system. expr: BILL_ID data_type: NUMBER(38,0) sample_values: - ''411545479348380'' - ''881437104865436'' - ''848649625469084'' - name: CPT synonyms: - current_procedural_terminology - procedure_code - medical_procedure - cpt_code - procedure_descriptor description: Current Procedural Terminology (CPT) code, a medical code used to report medical, surgical, and diagnostic procedures and services. expr: CPT data_type: VARCHAR(16777216) sample_values: - G8476 - 0041A - ''97803'' facts: - name: ID synonyms: - identifier - unique_id - record_id - key - unique_identifier - primary_key description: Unique identifier for a bill item. expr: ID data_type: NUMBER(38,0) sample_values: - ''690250871537821'' - ''991992525422749'' - ''663037877944477'' - name: SEQNO synonyms: - sequence_number - record_number - row_number - entry_number - item_number description: Unique identifier for each item on a bill. expr: SEQNO data_type: NUMBER(38,0) sample_values: - ''2'' - ''25'' - ''1'' filters: - name: not_deleted synonyms: - active - existing - present - available - not_removed - valid - current - retained description: Indicates whether the bill item is deleted or not. expr: DELETION_TIME is null time_dimensions: - name: CREATION_TIME synonyms: - created_at - creation_date - timestamp_created - record_creation_time - time_created - date_created - inception_time - initialization_time - setup_time description: The date and time when the bill item was created. expr: CREATION_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2024-05-30T13:08:54.000+0000 - 2023-12-12T11:36:48.000+0000 - 2025-04-01T11:14:27.000+0000 - name: DELETION_TIME synonyms: - deleted_at - deletion_date - removed_time - removal_timestamp - deleted_timestamp - purge_time description: The date and time when the bill item was deleted. expr: DELETION_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2021-08-13T07:55:50.000+0000 - 2020-08-17T08:40:41.000+0000 - name: LAST_MODIFIED synonyms: - last_updated - modified_date - modified_timestamp - update_date - update_timestamp description: The date and time when the bill item was last modified. expr: LAST_MODIFIED data_type: TIMESTAMP_NTZ(9) sample_values: - 2024-06-15T12:05:24.000+0000 - 2023-12-12T11:36:48.000+0000 - 2018-06-29T10:15:56.000+0000 - name: PATIENT base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT primary_key: columns: - ID dimensions: - name: DETAILED_PRIMARY_ETHNICITY synonyms: - specific_primary_ethnicity - detailed_ethnicity_origin - primary_ethnicity_description - ethnic_background_detail - precise_ethnicity - primary_ethnicity_subtype description: The patient''s primary ethnicity, categorized as either Hispanic or Latino or Not Hispanic or Latino. expr: DETAILED_PRIMARY_ETHNICITY data_type: VARCHAR(50) sample_values: - Not Hispanic or Latino - Hispanic or Latino - name: EMAIL synonyms: - email_address - contact_email - patient_email - electronic_mail - email_id description: The email address of the patient. expr: EMAIL data_type: VARCHAR(16777216) - name: FIRST_NAME synonyms: - given_name - first_given_name - personal_name - christian_name - forename description: The first name of the patient. expr: FIRST_NAME data_type: VARCHAR(16777216) - name: GENDER_IDENTITY synonyms: - gender - sex - gender_type - gender_category - personal_gender - gender_identification - gender_self_identification - gender_affiliation description: The patient''s self-identified gender, which may not necessarily align with their biological sex, and is used to provide a more accurate and respectful representation of their identity. expr: GENDER_IDENTITY data_type: VARCHAR(39) sample_values: - Woman - Man - Unknown - name: LAST_NAME synonyms: - surname - family_name - last_name_field - full_last_name - person_last_name - patient_last_name - individual_last_name description: The patient''s last name. expr: LAST_NAME data_type: VARCHAR(16777216) sample_values: - Melendezdeje - Eddins - Terracinaficd - name: MARITAL_STATUS synonyms: - marital_state - relationship_status - civil_status - family_status - partnership_status description: The current marital status of the patient, such as single, married, divorced, widowed, etc. expr: MARITAL_STATUS data_type: VARCHAR(16777216) - name: PATIENT_STATUS synonyms: - patient_condition - patient_state - patient_category - patient_classification - patient_type - patient_description - patient_summary - patient_overview description: Indicates the current status of a patient, either actively receiving care or inactive, meaning they are no longer receiving care or have been discharged. expr: PATIENT_STATUS data_type: VARCHAR(16777216) sample_values: - active - inactive - name: PRIMARY_ETHNICITY synonyms: - main_ethnicity - primary_ethnic_group - dominant_ethnicity - primary_cultural_identity - main_cultural_affiliation description: The patient''s primary ethnicity, which indicates whether the patient identifies as Hispanic or Latino, Not Hispanic or Latino, or if their ethnicity is not specified. expr: PRIMARY_ETHNICITY data_type: VARCHAR(22) sample_values: - Not Hispanic or Latino - Hispanic or Latino - No ethnicity specified - name: RACE synonyms: - ethnicity - ancestry - heritage - cultural_background - nationality - ethnic_origin description: The patient''s racial or ethnic identity, as reported or identified. expr: RACE data_type: VARCHAR(41) sample_values: - Asian - Declined to specify - White - name: SECONDARY_RACE synonyms: - secondary_ethnicity - secondary_race_identity - additional_race - alternate_race - secondary_cultural_background description: The patient''s secondary racial identity, which may be in addition to their primary racial identity, categorized as American Indian or Alaska Native, or indicating that the patient declined to specify or did not provide a racial identity. expr: SECONDARY_RACE data_type: VARCHAR(41) sample_values: - American Indian or Alaska Native - Declined to specify - No race specified - name: SEX synonyms: - gender - biological_sex - sex_category - sex_type - sex_classification description: The sex of the patient, indicating whether the patient is male, female, or unknown. expr: SEX data_type: VARCHAR(16777216) sample_values: - Male - Unknown - Female - name: SEXUAL_ORIENTATION synonyms: - sexual_identity - sexual_preference - sexual_affiliation - romantic_orientation - affectional_orientation - personal_orientation description: The patient''s self-identified sexual orientation, which may be used to provide culturally sensitive care and support. expr: SEXUAL_ORIENTATION data_type: VARCHAR(16777216) sample_values: - Straight - Unknown - Asexual facts: - name: ID synonyms: - unique_identifier - identifier - patient_id - record_id - unique_patient_id - patient_record_id description: Unique identifier for each patient in the healthcare system. expr: ID data_type: NUMBER(38,0) sample_values: - ''970881870790657'' - ''410677172633601'' - ''922932737343489'' - name: PRACTICE_ID synonyms: - practice_number - clinic_id - medical_group_id - provider_id - office_id - facility_id description: Unique identifier for the medical practice or healthcare facility where the patient receives care. expr: PRACTICE_ID data_type: NUMBER(38,0) sample_values: - ''235818542825476'' - ''737874562514948'' - ''9642727505924'' time_dimensions: - name: CREATION_TIME synonyms: - created_at - creation_date - record_creation_time - timestamp_created - date_created - time_of_creation - creation_timestamp - record_creation_date description: The date and time when the patient''s record was created. expr: CREATION_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2024-06-25T19:03:06.000+0000 - 2017-12-21T23:48:49.000+0000 - 2023-06-19T09:21:03.000+0000 - name: DECEASED_DATE synonyms: - date_of_death - date_of_passing - deceased_on - date_deceased - date_of_demise description: Date of death for the patient, if applicable. expr: DECEASED_DATE data_type: DATE sample_values: - ''2022-12-17'' - ''2025-05-12'' - name: DELETION_TIME synonyms: - deletion_timestamp - deleted_at - removal_time - deleted_date - purge_time - expiration_time - archival_time description: The date and time when a patient''s record was deleted from the system. expr: DELETION_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2023-06-08T01:18:45.000+0000 - 2023-08-08T17:48:03.000+0000 - name: DOB synonyms: - date_of_birth - birth_date - birthdate - birthday - date_of_origin description: Date of Birth of the patient. expr: DOB data_type: DATE sample_values: - ''1960-04-18'' - ''1968-07-27'' - ''1993-06-16'' - name: PATIENT_PHONE base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT_PHONE primary_key: columns: - ID dimensions: - name: IS_DELETED synonyms: - is_archived - is_inactive - deleted_flag - is_removed - is_voided - is_invalid - is_disabled description: Indicates whether the patient''s phone number has been deleted or is still active. expr: IS_DELETED data_type: BOOLEAN sample_values: - ''TRUE'' - ''FALSE'' - name: PATIENT_ID synonyms: - patient_number - patient_identifier - patient_key - person_id - individual_id - patient_reference description: Unique identifier for a patient in the healthcare system. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''306772214480897'' - ''505089052770305'' - ''138585015713793'' - name: PHONE synonyms: - telephone - contact_number - phone_number - mobile_number - landline - cell_phone - home_phone - work_phone - contact_info description: The phone number associated with a patient. expr: PHONE data_type: VARCHAR(16777216) sample_values: - ''6366286516'' - ''6192467357'' - ''2102591200'' - name: PHONE_TYPE synonyms: - phone_category - phone_classification - phone_kind - phone_label - phone_classification_type - phone_description description: The type of phone number associated with the patient, such as their main contact number, work number, or other phone number. expr: PHONE_TYPE data_type: VARCHAR(16777216) sample_values: - Main - Work - Other facts: - name: ID synonyms: - identifier - unique_id - record_id - row_id - primary_key - key_id - unique_identifier description: Unique identifier for a patient''s phone number record. expr: ID data_type: NUMBER(38,0) sample_values: - ''610440726446160'' - ''677516848922704'' - ''85142478520400'' time_dimensions: - name: LAST_MODIFIED synonyms: - last_updated - modified_date - modified_timestamp - update_date - update_timestamp - last_changed - modified_at - last_update description: The date and time when the patient''s phone information was last updated or modified. expr: LAST_MODIFIED data_type: TIMESTAMP_NTZ(9) sample_values: - 2023-04-01T03:39:50.000+0000 - 2024-08-22T15:00:02.000+0000 - 2024-12-10T05:51:57.000+0000 - name: PRACTICE base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PRACTICE primary_key: columns: - ID dimensions: - name: NAME synonyms: - title - label - designation - identifier - appellation - moniker - denomination description: The name of the practice or organization that provides medical services. expr: NAME data_type: VARCHAR(200) sample_values: - Elation Demo Practice - Sina Health - Medici and Health by Design facts: - name: ID synonyms: - identifier - unique_id - practice_id - record_id - key_id - unique_identifier - practice_identifier description: Unique identifier for a practice, likely a medical or healthcare practice, used to distinguish it from others in the database. expr: ID data_type: NUMBER(38,0) sample_values: - ''156611667296260'' - ''443411168821252'' - ''546238333255684'' - name: CPT_CODES base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: CPT_CODES primary_key: columns: - CODE dimensions: - name: CODE synonyms: - cpt - procedure_code - medical_code - billing_code - treatment_code - service_code description: CPT (Current Procedural Terminology) codes for medical services, representing specific procedures or services provided by healthcare professionals, such as office visits or consultations. expr: CODE data_type: VARCHAR(16777216) sample_values: - ''99395'' - ''99203'' - ''99204'' - name: DESCRIPTION synonyms: - detail - explanation - note - comment - remark - definition - elaboration - summary description: This column contains a brief description of the medical service or procedure associated with a specific CPT (Current Procedural Terminology) code, providing context for the type of healthcare service provided. expr: DESCRIPTION data_type: VARCHAR(16777216) sample_values: - pap smear - Medicare well person preventative visit - new patient, moderate complexity office visit - name: USER base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: USER primary_key: columns: - ID dimensions: - name: CREDENTIALS synonyms: - qualifications - certifications - professional_credentials - medical_credentials - professional_qualifications - licenses - professional_licenses description: The type of nursing credential held by the user, such as Registered Nurse (RN) or Advanced Practice Registered Nurse (APRN) with a specific certification (CNP). expr: CREDENTIALS data_type: VARCHAR(256) sample_values: - RN - APRN CNP - name: FIRST_NAME synonyms: - given_name - first_name - forename - personal_name - christian_name description: The first name of the user. expr: FIRST_NAME data_type: VARCHAR(256) sample_values: - Erica - Rebecca - Anthony - name: LAST_NAME synonyms: - surname - family_name - last_name_field - full_last_name - person_last_name - user_last_name - individual_last_name - name_last description: The last name of the user. expr: LAST_NAME data_type: VARCHAR(256) sample_values: - Wortham - Ng - Myastkovetskaya - name: NPI synonyms: - national_provider_identifier - provider_id_number - unique_provider_id - healthcare_provider_id - national_provider_id_number description: National Provider Identifier, a unique 10-digit identification number assigned to healthcare providers in the United States. expr: NPI data_type: VARCHAR(256) sample_values: - ''1992539852'' - . - name: PRACTICE_ID synonyms: - practice_number - practice_code - medical_group_id - clinic_id - office_id - organization_id - group_id description: Unique identifier for a medical practice or healthcare organization where a patient receives care. expr: PRACTICE_ID data_type: NUMBER(38,0) sample_values: - ''831423838093316'' - ''237407481626628'' - ''334589232021508'' - name: SPECIALTY synonyms: - field_of_expertise - area_of_specialization - medical_specialty - professional_specialty - area_of_practice - discipline - profession description: The type of medical specialty or area of expertise of the user. expr: SPECIALTY data_type: VARCHAR(16777216) sample_values: - Family - Psychotherapy - name: USER_TYPE synonyms: - user_role - user_category - account_type - user_classification - user_status - role_type - user_designation description: The type of user accessing the system, which can be a physician, a member of the hospital staff, or the system itself (e.g. automated processes). expr: USER_TYPE data_type: VARCHAR(9) sample_values: - physician - staff - system facts: - name: ID synonyms: - identifier - unique_id - user_id - record_id - key_id - unique_identifier - primary_key description: Unique identifier for a user in the system. expr: ID data_type: NUMBER(38,0) sample_values: - ''2572921'' - ''2548800'' - ''2331744'' - name: VISIT_NOTE base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: VISIT_NOTE primary_key: columns: - ID dimensions: - name: PATIENT_ID synonyms: - patient_number - person_id - individual_id - subject_id - medical_record_number - patient_identifier - unique_patient_identifier - patient_key description: Unique identifier for the patient associated with the visit note. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''85140626145281'' - ''585017285672961'' - ''566713650053121'' - name: PHYSICIAN_USER_ID synonyms: - physician_id - doctor_user_id - medical_professional_id - healthcare_provider_id - attending_physician_id - treating_physician_id description: The unique identifier of the physician who created or updated the visit note. expr: PHYSICIAN_USER_ID data_type: NUMBER(38,0) - name: PRACTICE_ID synonyms: - practice_number - clinic_id - provider_id - facility_id - organization_id - site_id - office_id description: Unique identifier for the medical practice where the visit note was created. expr: PRACTICE_ID data_type: NUMBER(38,0) sample_values: - ''334589232021508'' - ''235818542825476'' - ''5319578091524'' facts: - name: ID synonyms: - identifier - unique_id - record_id - row_id - primary_key description: Unique identifier for a visit note, used to distinguish and reference specific notes within the system. expr: ID data_type: NUMBER(38,0) sample_values: - ''680363412029464'' - ''599823083372568'' - ''669545268969496'' time_dimensions: - name: CREATION_TIME synonyms: - created_at - creation_date - timestamp_created - record_creation_time - time_created - date_created - created_timestamp - initialization_time description: The date and time when the visit note was created. expr: CREATION_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2017-09-13T05:00:00.000+0000 - 2023-06-16T07:09:54.000+0000 - 2020-04-14T07:55:14.000+0000 - name: DELETION_TIME synonyms: - deletion_timestamp - deleted_at - removal_time - deleted_date - purge_time - expiration_time - archival_time description: The date and time when the visit note was deleted. expr: DELETION_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2023-09-12T09:09:01.000+0000 - 2023-03-11T00:18:29.000+0000 - name: PATIENT_TAG synonyms: - label - tag description: This table contains free-form labels or tags for a given patient. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT_TAG primary_key: columns: - PATIENT_ID dimensions: - name: PATIENT_ID synonyms: - patient_number - patient_identifier - patient_key - person_id - individual_id - subject_id description: Unique identifier for a patient in the healthcare system. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''605988843618305'' - ''572578348335105'' - ''565565298311169'' - name: TAG_VALUE synonyms: - tag_description - label - patient_label - patient_tag_description - patient_category - classification - patient_classification description: A unique identifier assigned to a patient. expr: TAG_VALUE data_type: VARCHAR(16777216) sample_values: - CRM Patient ID 10744 - CRM Patient ID 29532 - CRM Patient ID 17554 filters: - name: current_patient_tags_only synonyms: - not deleted - active description: Limits set of Patient Tags to only those that have not been deleted. expr: DELETION_TIME IS NOT NULL relationships: - name: BILL_PRACTICE left_table: BILL right_table: PRACTICE relationship_columns: - left_column: PRACTICE_ID right_column: ID relationship_type: one_to_one join_type: left_outer - name: NOTE_BILL left_table: BILL right_table: VISIT_NOTE relationship_columns: - left_column: VISIT_NOTE_ID right_column: ID relationship_type: many_to_one join_type: inner - name: BILLITEM_CPT left_table: BILL_ITEM right_table: CPT_CODES relationship_columns: - left_column: CPT right_column: CODE relationship_type: one_to_one join_type: inner - name: BILL_BILLITEM left_table: BILL_ITEM right_table: BILL relationship_columns: - left_column: BILL_ID right_column: ID relationship_type: many_to_one join_type: inner - name: PATIENT_PATIENTPHONE left_table: PATIENT_PHONE right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: many_to_one join_type: left_outer - name: NOTE_USER left_table: VISIT_NOTE right_table: USER relationship_columns: - left_column: PHYSICIAN_USER_ID right_column: ID relationship_type: one_to_one join_type: left_outer - name: PATIENT_NOTE left_table: VISIT_NOTE right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: many_to_one join_type: inner - name: PATIENT_PATIENT_TAG left_table: PATIENT_TAG right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: many_to_one join_type: left_outer custom_instructions: |- - use ilike over like and = - always dedup - filter out inactive and deceased patients unless otherwise specified - year means calendar year not 365 days'); -- Semantic model: diagnosis_semantic_model.yml SELECT SYSTEM$WRITE_SEMANTIC_MODEL_YAML($semantic_models_schema, 'name: DIAGNOSIS_SEMANTIC_MODEL description: This semantic model connects Patient information with historical diagnoses, so that you can better understand your patient panel. tables: - name: ICD10 description: This table stores a listing of ICD-10 codes. They are used to record codified diagnoses of patient problems. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: ICD10 primary_key: columns: - ID dimensions: - name: CODE synonyms: - icd10_code - diagnosis_code - medical_code - classification_code - identifier description: Diagnosis Codes expr: CODE data_type: VARCHAR(50) sample_values: - S56.123A - F50.811 - I83.003 - name: DESCRIPTION synonyms: - definition - explanation - note - details - summary - caption - remark - comment - annotation description: This column contains detailed descriptions of various medical conditions and diagnoses, as classified by the International Classification of Diseases, 10th Revision (ICD-10). expr: DESCRIPTION data_type: VARCHAR(300) sample_values: - Polyhydramnios, unspecified trimester, fetus 1 - Binge eating disorder, moderate - Varicose veins of unspecified lower extremity with ulcer of ankle facts: - name: ID synonyms: - identifier - unique_id - record_id - row_id - primary_key - unique_identifier - key_id description: Unique identifier for a medical record or patient encounter. expr: ID data_type: NUMBER(38,0) sample_values: - ''860642786345312'' - ''20805058756960'' - ''20583634239840'' - name: PATIENT synonyms: - panel description: This table contains a listing of all Patients within a practice, including relevant demographic details. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT primary_key: columns: - ID dimensions: - name: CITY synonyms: - town - municipality - metropolis - urban_area - city_name - location - municipality_name - urban_center - populated_area description: The city where the patient resides. expr: CITY data_type: VARCHAR(16777216) sample_values: - Tampa - West memphis - CLAYTON - name: EMAIL synonyms: - email_address - contact_email - patient_email - electronic_mail - email_id description: The email address of the patient. expr: EMAIL data_type: VARCHAR(16777216) sample_values: - tomperry932@gmail.com - knutsonk08@gmail.com - name: FIRST_NAME synonyms: - given_name - first_name - forename - personal_name - christian_name description: The first name of the patient. expr: FIRST_NAME data_type: VARCHAR(16777216) sample_values: - Miranda - Anastasia - Tambudzai - name: ID synonyms: - unique_identifier - identifier - patient_identifier - patient_id - unique_patient_id - record_id description: Unique identifier assigned to each patient for tracking and record-keeping purposes. expr: ID data_type: NUMBER(38,0) sample_values: - ''911606825091073'' - ''380304571367425'' - ''256898711289857'' - name: LAST_NAME synonyms: - surname - family_name - last_name_field - full_last_name - person_last_name description: The patient''s last name. expr: LAST_NAME data_type: VARCHAR(16777216) sample_values: - Pacheco - Sanders - Jurkowski - name: MARITAL_STATUS synonyms: - marital_state - relationship_status - civil_status - family_status - partnership_status description: The current marital status of the patient, such as single, married, divorced, widowed, etc. expr: MARITAL_STATUS data_type: VARCHAR(16777216) - name: MIDDLE_NAME synonyms: - middle_initial - second_name - middle_initials - second_given_name - middle_given_name description: The middle name of the patient. expr: MIDDLE_NAME data_type: VARCHAR(16777216) sample_values: - ''N'' - MARGARET - Dawn - name: PATIENT_STATUS synonyms: - patient_condition - patient_state - patient_category - patient_classification - patient_type - patient_description - patient_summary - patient_overview - patient_profile_status description: Indicates the current status of a patient, either actively receiving care or inactive, meaning they are no longer receiving care or have been discharged. expr: PATIENT_STATUS data_type: VARCHAR(16777216) sample_values: - active - inactive - name: PRACTICE_ID synonyms: - practice_number - clinic_id - office_id - medical_group_id - provider_id - facility_id description: Unique identifier for the medical practice or healthcare facility where the patient receives care. expr: PRACTICE_ID data_type: NUMBER(38,0) sample_values: - ''156611667296260'' - ''79171750068228'' - name: PRIMARY_ETHNICITY synonyms: - main_ethnicity - primary_ethnic_group - dominant_ethnicity - ethnic_origin - primary_cultural_identity description: The patient''s primary ethnicity, which indicates whether the patient identifies as Hispanic or Latino, Not Hispanic or Latino, or if their ethnicity is not specified. expr: PRIMARY_ETHNICITY data_type: VARCHAR(22) sample_values: - Not Hispanic or Latino - No ethnicity specified - Hispanic or Latino - name: PRIMARY_PHYSICIAN_USER_ID synonyms: - primary_doctor_id - primary_care_physician_id - primary_medical_doctor_id - primary_healthcare_provider_id - primary_attending_physician_id description: The unique identifier of the primary physician assigned to the patient. expr: PRIMARY_PHYSICIAN_USER_ID data_type: NUMBER(38,0) sample_values: - ''199402'' - ''1216975'' - ''385730'' - name: RACE synonyms: - ethnicity - ancestry - heritage - cultural_background - national_origin description: The patient''s racial or ethnic identity, as reported or observed. expr: RACE data_type: VARCHAR(41) sample_values: - Asian - No race specified - White - name: SECONDARY_ETHNICITY synonyms: - secondary_ethnicity_group - secondary_ethnicity_category - additional_ethnicity - secondary_cultural_identity - alternate_ethnicity - secondary_ethnic_affiliation description: The patient''s secondary ethnicity, which may be reported in addition to their primary ethnicity, indicating whether they identify as Hispanic or Latino, Not Hispanic or Latino, or if no ethnicity was specified. expr: SECONDARY_ETHNICITY data_type: VARCHAR(22) sample_values: - Not Hispanic or Latino - No ethnicity specified - Hispanic or Latino - name: SECONDARY_RACE synonyms: - secondary_ethnicity - secondary_race_identity - additional_race - alternate_race - secondary_cultural_background description: The patient''s secondary racial identity, which may be in addition to their primary racial identity, categorized as Asian, No race specified, or White. expr: SECONDARY_RACE data_type: VARCHAR(41) sample_values: - Asian - No race specified - White - name: SEX synonyms: - gender - biological_sex - sex_category - sex_type - sex_classification description: The sex of the patient, indicating whether the patient is male, female, or unknown. expr: SEX data_type: VARCHAR(16777216) sample_values: - Male - Female - Unknown - name: SEXUAL_ORIENTATION synonyms: - sexual_identity - sexual_preference - sexual_affiliation - romantic_orientation - affectional_orientation description: The patient''s self-identified sexual orientation, which can be used to understand demographic characteristics and tailor healthcare services to meet individual needs. expr: SEXUAL_ORIENTATION data_type: VARCHAR(16777216) sample_values: - Straight - Bisexual - Asexual - name: STATE synonyms: - province - region - territory - county - parish - prefecture - district - area - location - jurisdiction description: The two-letter abbreviation for the state in the United States where the patient resides. expr: STATE data_type: VARCHAR(16777216) sample_values: - SC - MA time_dimensions: - name: DECEASED_DATE synonyms: - date_of_death - date_of_passing - deceased_on - date_deceased - death_date - passed_away_date description: Date of death for the patient, if applicable. expr: DECEASED_DATE data_type: DATE sample_values: - ''2024-05-22'' - ''2023-03-09'' - name: DOB synonyms: - date_of_birth - birth_date - birthdate - birthday - dob_date description: Date of Birth of the patient. expr: DOB data_type: DATE sample_values: - ''1964-01-10'' - ''1971-03-09'' - ''1960-07-11'' - name: PATIENT_ALLERGY description: This table contains a list of allergies for a given patient. This can refer to allergies to medications, ingredients, or other types. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT_ALLERGY primary_key: columns: - PATIENT_ID dimensions: - name: ALLERGEN_TYPE synonyms: - allergen_category - allergen_class - allergen_group - allergen_substance - allergen_description description: The type of allergen that the patient is allergic to, which can be either a specific drug name or a broader allergen class. expr: ALLERGEN_TYPE data_type: VARCHAR(16777216) sample_values: - drugName - allergenClass - name: NAME synonyms: - allergen_name - patient_name - allergy_name - description - label description: The type of allergy a patient has, such as food, medication, or environmental allergies. expr: NAME data_type: VARCHAR(16777216) sample_values: - dairy - fh pcn - Seasonal allergies - name: PATIENT_ID synonyms: - patient_key - patient_reference - patient_identifier - patient_number - patient_code - person_id - individual_id description: Unique identifier for the patient in the healthcare system. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''387779853615105'' - ''706101437399041'' - ''621743747694593'' - name: REACTION synonyms: - response - effect - outcome - result - consequence - symptom - answer - reply description: The REACTION column captures the specific adverse response or symptom experienced by a patient as a result of an allergic reaction. expr: REACTION data_type: VARCHAR(16777216) sample_values: - difficulty breathing - itchiness, throat swells - Unknown - name: STATUS synonyms: - state - condition - situation - position - standing - category - classification - designation description: Indicates whether the patient''s allergy is currently active and being considered in their care, or inactive and no longer a concern. expr: STATUS data_type: VARCHAR(10) sample_values: - active - inactive filters: - name: active_allergies_only synonyms: - not deleted - active description: Limits set of Patient Allergies to only those that have not been deleted. expr: DELETION_TIME IS NOT NULL - name: PATIENT_HCC synonyms: - Risk Score description: This table contains risk scores for a given patient. They are available in 2 different types - community and institutional. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT_HCC primary_key: columns: - PATIENT_ID dimensions: - name: IS_CURRENT synonyms: - is_active - current_status - active_ind - is_valid - current_record - active_flag - is_up_to_date description: Indicates whether the patient''s HCC (Hierarchical Condition Category) is currently active or not. expr: IS_CURRENT data_type: BOOLEAN sample_values: - ''FALSE'' - ''TRUE'' - name: PATIENT_ID synonyms: - patient_key - patient_identifier - patient_number - person_id - individual_id - subject_id description: Unique identifier assigned to each patient within the healthcare system. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''912028773777409'' - ''704362074669057'' - ''245350415138817'' facts: - name: COMMUNITY synonyms: - community_score - community_value - community_rating - community_measure - community_index - population_score description: The percentage of patients from the community who have been diagnosed with a specific condition or disease, likely used to track and analyze health trends and outcomes in different community populations. expr: COMMUNITY data_type: NUMBER(38,12) sample_values: - ''0.311000000000'' - ''0.549000000000'' - ''0.301000000000'' - name: INSTITUTIONAL synonyms: - facility_based - hospital_based - inpatient_care - residential_care - institutional_setting - facility_care - in_facility - organization_based description: Institutional cost factor, representing the average cost of care for a patient in an institutional setting, such as a hospital or skilled nursing facility, relative to the average cost of care for a patient in the general population. expr: INSTITUTIONAL data_type: NUMBER(38,12) sample_values: - ''0.000000000000'' - ''1.288000000000'' - ''1.358000000000'' filters: - name: current_hcc_score_only synonyms: - current - not historical description: Limits set of Risk Scores to only the most current calculations. expr: IS_CURRENT = TRUE - name: PATIENT_HISTORY description: This table contains information about a Patient''s history. This can including past surgical, medical, family, and other types of history. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT_HISTORY primary_key: columns: - PATIENT_ID dimensions: - name: PATIENT_ID synonyms: - patient_number - patient_identifier - patient_key - person_id - individual_id - subject_id - medical_record_number - patient_reference description: Unique identifier for a patient in the healthcare system, used to track and manage their medical history and records. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''287738453229569'' - ''581160761884673'' - ''159996987834369'' - name: TYPE synonyms: - category - classification - kind - sort - designation - label - nature - genre description: ''The TYPE column categorizes the type of information recorded in the patient''''s history, which can be one of the following: Family (family medical history), Past (past medical history), or Habits (lifestyle habits).'' expr: TYPE data_type: VARCHAR(20) sample_values: - Family - Past - Habits - name: VALUE synonyms: - amount - quantity - measure - magnitude - figure - number - score - level - worth description: This column captures the patient''s medical and employment history, including their current work status, job title, employer, and any relevant medical conditions or diagnoses. expr: VALUE data_type: VARCHAR(16777216) sample_values: - ''Work: Employed Full Time, Senior IT Engineer, Insource Services Inc.'' - reguular - endometriosis filters: - name: active_patient_history_only synonyms: - not deleted - active description: Limits set of Patient Histories to only those that have not been deleted. expr: DELETION_TIME IS NOT NULL - name: PATIENT_INSURANCE description: This table contains information for which insurance a Patient is using. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT_INSURANCE primary_key: columns: - PATIENT_ID dimensions: - name: CARRIER synonyms: - insurer - insurance_provider - health_insurer - insurance_carrier - insurance_company - payer - health_plan_provider description: The name of the insurance carrier providing coverage to the patient. expr: CARRIER data_type: VARCHAR(16777216) sample_values: - Firefly Health Plan - Blue Cross Blue Shield of Massachusetts - Allways (Neighborhood Health Plan) - name: PATIENT_ID synonyms: - patient_number - patient_identifier - patient_key - medical_record_number - patient_reference_id - unique_patient_identifier description: Unique identifier for the patient, used to link insurance information to the corresponding patient record. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''707627575607297'' - ''756640010207233'' - ''813645374357505'' - name: PATIENT_PHONE synonyms: - contact description: This table contains phone numbers for a patient. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT_PHONE primary_key: columns: - PATIENT_ID dimensions: - name: PATIENT_ID synonyms: - patient_number - patient_key - patient_identifier - person_id - individual_id - medical_record_number - patient_reference description: Unique identifier for a patient in the healthcare system. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''248828411248641'' - ''355972079943681'' - ''444400661954561'' - name: PHONE synonyms: - telephone - phone_number - contact_number - mobile_number - landline_number - cell_phone - home_phone - work_phone description: The phone number associated with a patient. expr: PHONE data_type: VARCHAR(16777216) sample_values: - ''9784271558'' - ''7742382120'' - ''8185154750'' - name: PHONE_TYPE synonyms: - phone_category - phone_classification - phone_kind - phone_label - phone_description - phone_designation description: The type of phone number provided by the patient, such as cell phone, work phone, or other type of phone. expr: PHONE_TYPE data_type: VARCHAR(16777216) sample_values: - Cell Phone - Other - Work filters: - name: active_patient_phone_only synonyms: - not deleted - active description: Limits set of Patient Phone Numbers to only those that have not been deleted. expr: IS_DELETED = FALSE - name: PATIENT_PROBLEM synonyms: - problem list - problems - diagnoses description: This table contains a list of problems or diagnoses which a patient has. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT_PROBLEM primary_key: columns: - ID dimensions: - name: DESCRIPTION synonyms: - detailed_info - detailed_description - explanation - narrative - account - depiction - portrayal - summary - details description: This column stores the description of a patient''s problem or reason for a medical encounter, as documented in the patient''s record. expr: DESCRIPTION data_type: VARCHAR(16777216) sample_values: - Stress due to illness of family member - Foamy urine - Encounter for screening examination for mental health and behavioral disorders - name: ID synonyms: - identifier - unique_id - record_id - row_id - primary_key description: Unique identifier for a patient''s medical problem or condition. expr: ID data_type: NUMBER(38,0) sample_values: - ''589425396809771'' - ''669783891640363'' - ''730718317707307'' - name: PATIENT_ID synonyms: - patient_number - patient_identifier - patient_key - person_id - individual_id - subject_id - medical_record_number description: Unique identifier for a patient in the healthcare system. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''617417128017921'' - ''692173150420993'' - ''734566353338369'' - name: STATUS synonyms: - state - condition - situation - position - standing - circumstance description: The current state of a patient''s problem or condition, indicating whether it has been resolved, is being managed, or is still active and requires attention. expr: STATUS data_type: VARCHAR(16777216) sample_values: - Resolved - Controlled - Active filters: - name: non_deleted_patient_problems_only synonyms: - not deleted description: Limits set of Patient Problems to only those that have not been deleted. expr: DELETION_TIME IS NOT NULL - name: PATIENT_PROBLEM_CODE description: This table links patient problems to their specific coded concepts in ICD9, ICD10, or SNOMED. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT_PROBLEM_CODE primary_key: columns: - PATIENT_PROBLEM_ID - SNOMED_ID dimensions: - name: ICD10_ID synonyms: - icd10_code - icd10_identifier - icd10_reference - diagnosis_code - medical_diagnosis_id - icd10_key description: Unique identifier for a specific medical diagnosis or condition as classified by the International Classification of Diseases, 10th Revision (ICD-10) system. expr: ICD10_ID data_type: NUMBER(38,0) sample_values: - ''20046545289568'' - ''20048850256224'' - ''20083458900320'' - name: PATIENT_PROBLEM_ID synonyms: - patient_problem_key - patient_issue_id - medical_problem_id - patient_condition_id - health_issue_identifier description: Unique identifier for a specific patient''s medical problem or condition. expr: PATIENT_PROBLEM_ID data_type: NUMBER(38,0) sample_values: - ''863872907935787'' - ''905823903481899'' - ''766987533615147'' - name: SNOMED_ID synonyms: - SNOMED_code - SNOMED_identifier - Systematized_Nomenclature_of_Medicine_ID - SNOMED_reference - SNOMED_key description: A unique identifier for a specific medical condition or problem, as defined by the Systematized Nomenclature of Medicine (SNOMED) clinical terminology, assigned to a patient. expr: SNOMED_ID data_type: NUMBER(38,0) sample_values: - ''5295469363523'' - ''5303006855491'' - ''5305251332419'' - name: PATIENT_TAG synonyms: - label - tag description: This table contains free-form labels or tags for a given patient. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT_TAG primary_key: columns: - PATIENT_ID dimensions: - name: PATIENT_ID synonyms: - patient_number - patient_identifier - patient_key - person_id - individual_id - subject_id description: Unique identifier for a patient in the healthcare system. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''605988843618305'' - ''572578348335105'' - ''565565298311169'' - name: TAG_VALUE synonyms: - tag_description - label - patient_label - patient_tag_description - patient_category - classification - patient_classification description: A unique identifier assigned to a patient. expr: TAG_VALUE data_type: VARCHAR(16777216) sample_values: - CRM Patient ID 10744 - CRM Patient ID 29532 - CRM Patient ID 17554 filters: - name: current_patient_tags_only synonyms: - not deleted - active description: Limits set of Patient Tags to only those that have not been deleted. expr: DELETION_TIME IS NOT NULL - name: PRACTICE synonyms: - CDU - location - practice description: This table contains information about a medical practice. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PRACTICE primary_key: columns: - ID dimensions: - name: ID synonyms: - identifier - unique_id - practice_id - record_id - key_id - unique_identifier - practice_identifier description: Unique identifier for a practice, likely a medical or healthcare practice, used to distinguish it from others in the database. expr: ID data_type: NUMBER(38,0) sample_values: - ''156611667296260'' - ''79171750068228'' - name: NAME synonyms: - title - label - designation - identifier - appellation - denomination description: The name of the practice or organization. expr: NAME data_type: VARCHAR(200) sample_values: - Firefly Health - Elation Demo Practice - name: SNOMED description: This table stores a listing of SNOMED codes. They are used to record codified diagnoses of patient problems. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: SNOMED primary_key: columns: - UNIQUE_SNOMED_ID dimensions: - name: CODE synonyms: - id - identifier - snomed_id - code_value - snomed_code - unique_code description: SNOMED Concept Identifier expr: CODE data_type: VARCHAR(50) sample_values: - ''40861000087102'' - ''245182007'' - ''450545004'' - name: DESCRIPTION synonyms: - definition - explanation - note - details - summary - caption - remark - comment - annotation description: This column contains descriptive terms used to identify and classify various concepts, objects, or entities within the SNOMED system, including anatomical structures, organisms, and other medical concepts. expr: DESCRIPTION data_type: VARCHAR(250) sample_values: - Bananaquit - Entire articular part of tubercle of fourth rib - Coccidian parasite - name: UNIQUE_SNOMED_ID expr: UQ_SNOMED data_type: NUMBER(38,0) - name: USER synonyms: - Physician - PCP - Doctor description: This table contains information for Physicians, most commonly used when understanding who a Patient''s primary care physician is. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: USER primary_key: columns: - ID dimensions: - name: FIRST_NAME synonyms: - given_name - first_name - forename - personal_name - christian_name description: The first name of the user. expr: FIRST_NAME data_type: VARCHAR(256) sample_values: - Philip - Fayo - Jenny - name: ID synonyms: - identifier - unique_id - user_id - record_id - key_id - unique_identifier description: Unique identifier for a user in the system. expr: ID data_type: NUMBER(38,0) sample_values: - ''2228367'' - ''199402'' - ''670940'' - name: LAST_NAME synonyms: - surname - family_name - last_name_field - full_last_name - person_last_name - user_last_name - individual_last_name - patient_last_name description: The LAST_NAME column stores the last name of a user, which can be used to identify and distinguish between individual users. expr: LAST_NAME data_type: VARCHAR(256) sample_values: - Onboarding test - Feagins - Nelson - name: PRACTICE_ID synonyms: - practice_number - practice_code - medical_group_id - clinic_id - office_id - organization_id - group_id description: Unique identifier for a medical practice or healthcare organization where a user is affiliated or associated with. expr: PRACTICE_ID data_type: NUMBER(38,0) sample_values: - ''156611667296260'' - ''79171750068228'' relationships: - name: PATIENT_PRACTICE left_table: PATIENT right_table: PRACTICE relationship_columns: - left_column: PRACTICE_ID right_column: ID relationship_type: one_to_one join_type: left_outer - name: PRIMARY_PHYSICIAN_PATIENT left_table: PATIENT right_table: USER relationship_columns: - left_column: PRIMARY_PHYSICIAN_USER_ID right_column: ID relationship_type: one_to_one join_type: left_outer - name: PATIENT_PATIENT_ALLERGY left_table: PATIENT_ALLERGY right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: many_to_one join_type: left_outer - name: PATIENT_PATIENT_HCC left_table: PATIENT_HCC right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: many_to_one join_type: left_outer - name: PATIENT_PATIENT_HISTORIES left_table: PATIENT_HISTORY right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: many_to_one join_type: left_outer - name: PATIENT_PATIENT_INSURANCES left_table: PATIENT_INSURANCE right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: many_to_one join_type: left_outer - name: PATIENT_PATIENT_PHONE_NUMBERS left_table: PATIENT_PHONE right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: many_to_one join_type: left_outer - name: PATIENT_PATIENT_PROBLEMS left_table: PATIENT_PROBLEM right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: one_to_one join_type: left_outer - name: PATIENT_PROBLEM_CODE_ICD10 left_table: PATIENT_PROBLEM_CODE right_table: ICD10 relationship_columns: - left_column: ICD10_ID right_column: ID relationship_type: one_to_one join_type: left_outer - name: PATIENT_PROBLEM_CODE_SNOMED left_table: PATIENT_PROBLEM_CODE right_table: SNOMED relationship_columns: - left_column: SNOMED_ID right_column: UNIQUE_SNOMED_ID relationship_type: one_to_one join_type: left_outer - name: PATIENT_PROBLEM_PROBLEM_CODE left_table: PATIENT_PROBLEM_CODE right_table: PATIENT_PROBLEM relationship_columns: - left_column: PATIENT_PROBLEM_ID right_column: ID relationship_type: many_to_one join_type: left_outer - name: PATIENT_PATIENT_TAG left_table: PATIENT_TAG right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: many_to_one join_type: left_outer custom_instructions: |- - exclude patient_id unless specified - year means calendar year not 365 days - always dedup - filter out inactive and deceased patients unless otherwise specified - use ilike over like and = - Basic patient info includes gender, dob, phone, and email. This should be included if providing a patient list.'); -- Semantic model: medication_semantic_model.yml SELECT SYSTEM$WRITE_SEMANTIC_MODEL_YAML($semantic_models_schema, 'name: MEDICATION_SEMANTIC_MODEL tables: - name: MEDICATION base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: MEDICATION primary_key: columns: - ID dimensions: - name: BRAND_NAME synonyms: - product_name - trade_name - proprietary_name - label_name - trademark_name - brand_label description: The brand name of the medication prescribed to the patient. expr: BRAND_NAME data_type: VARCHAR(16777216) sample_values: - Progesterone 50mg - cloZAPine - c-Amitrip4/Bac4/Diclo4/Gab6% Cream - name: CONTROLLED synonyms: - regulated - restricted - monitored - supervised - governed - licensed description: Indicates whether the medication is a controlled substance, requiring special handling and monitoring due to its potential for abuse or dependence. expr: CONTROLLED data_type: BOOLEAN sample_values: - ''TRUE'' - ''FALSE'' - name: FORM synonyms: - shape - dosage_form - preparation - presentation - dosage - format - type - kind description: The form of the medication, which describes the physical presentation of the medication, such as a liquid solution in an auto-injector, a kit containing multiple components, or a powder that requires reconstitution. expr: FORM data_type: VARCHAR(16777216) sample_values: - Solution Auto-injector - Kit - Powder - name: GENERIC_NAME synonyms: - non_brand_name - chemical_name - active_ingredient - non_proprietary_name - common_name description: The name of the medication in its generic form, which is the chemical or scientific name of the active ingredient(s) in the medication, without reference to a specific brand or manufacturer. expr: GENERIC_NAME data_type: VARCHAR(16777216) sample_values: - Cobalamin Combination Lozenge - Carboplatin IV Soln 450 mg/45ML - Aspirin-Calcium Carbonate Tab 81/777 mg - name: IS_MAINTENANCE_DRUG synonyms: - is_preventative_drug - is_prophylactic_drug - is_long_term_drug - maintenance_medication - ongoing_treatment - curation_drug - is_chronic_drug description: Indicates whether the medication is used for maintenance purposes, meaning it is taken regularly to manage an ongoing condition, rather than to treat a specific acutely occurring condition. expr: IS_MAINTENANCE_DRUG data_type: BOOLEAN sample_values: - ''TRUE'' - ''FALSE'' - name: STRENGTH synonyms: - potency - concentration - dosage - power - intensity - magnitude description: The strength of the medication, representing the amount of active ingredient per unit of the medication, measured in milligrams (mg). expr: STRENGTH data_type: VARCHAR(16777216) sample_values: - 200 mg - name: TYPE synonyms: - category - classification - kind - sort - form_type - medication_type - drug_type - classification_code description: The type of medication, indicating whether it is available over-the-counter (OTC) or if the type is undeterminable. expr: TYPE data_type: VARCHAR(16777216) sample_values: - otc - undeterminable facts: - name: ID synonyms: - identifier - unique_id - record_id - primary_key - key_id - unique_identifier description: Unique identifier for a medication. expr: ID data_type: NUMBER(38,0) sample_values: - ''983775197397039'' - ''737390608056367'' - ''612806453493807'' - name: MED_ORDER synonyms: - med_order - medication_order - prescription_order - medical_order - order - prescription - med_order_entry - medication_request - prescription_request - medical_request description: This table stores information about medical orders, including details about the medication, patient, prescribing user, and fulfillment instructions. It captures the order''s status, including creation, modification, deletion, and signing timestamps, as well as the users involved in these actions. The table also references related entities such as medications, patients, practices, and order threads, enabling the tracking of medical orders. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: MED_ORDER primary_key: columns: - ID dimensions: - name: MEDICATION_ID synonyms: - medication_key - med_id - medication_code - rx_id - prescription_id - med_code - medication_identifier description: Unique identifier for a medication order, used to track and manage medication prescriptions within the healthcare system. expr: MEDICATION_ID data_type: NUMBER(38,0) sample_values: - ''725592533696559'' - ''55538917834799'' - ''7426038497327'' - name: MED_ORDER_THREAD_ID synonyms: - med_order_thread_key - med_order_group_id - med_order_chain_id - med_order_sequence_id - med_order_link_id description: Unique identifier for a thread of related medical orders. expr: MED_ORDER_THREAD_ID data_type: NUMBER(38,0) sample_values: - ''446615096918039'' - ''506213755912215'' - ''253219102916631'' - name: PATIENT_ID synonyms: - patient_number - patient_code - person_id - individual_id - subject_id - patient_key - medical_record_number - patient_identifier description: Unique identifier for the patient who the medical order is associated with. expr: PATIENT_ID data_type: NUMBER(38,0) - name: PRACTICE_ID synonyms: - practice_number - clinic_id - medical_group_id - healthcare_provider_id - facility_id description: Unique identifier for the medical practice that issued the order. expr: PRACTICE_ID data_type: NUMBER(38,0) - name: PRESCRIBING_USER_ID synonyms: - prescriber_id - prescribing_doctor_id - ordering_provider_id - prescribing_physician_id - rx_user_id description: The unique identifier of the healthcare professional who prescribed the medication order. expr: PRESCRIBING_USER_ID data_type: NUMBER(38,0) facts: - name: ID synonyms: - identifier - unique_id - record_id - primary_key - key_id - unique_identifier description: Unique identifier for a medical order. expr: ID data_type: NUMBER(38,0) sample_values: - ''828335147843610'' - ''392215678484506'' - ''573393397678106'' filters: - name: not_deleted synonyms: - active - existing - present - not_removed - valid - current - retained - not_archived - live description: Indicates whether a medication order has been deleted or is still active. expr: DELETION_TIME is null time_dimensions: - name: DELETION_TIME synonyms: - deletion_timestamp - deleted_at - removal_time - deleted_date - purge_time description: The date and time when the medical order was deleted from the system. expr: DELETION_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2025-02-28T12:22:41.000+0000 - 2023-05-15T05:25:10.000+0000 - name: START_DATE synonyms: - start_date - begin_date - initiation_date - effective_date - activation_date - commencement_date description: The date on which the medical order was initiated or started. expr: START_DATE data_type: DATE sample_values: - ''2017-07-03'' - ''2024-10-23'' - ''2022-07-14'' - name: MED_ORDER_FILL synonyms: - MED_ORDER_FILLS - MEDICATION_ORDERS - PRESCRIPTION_FILLS - RX_ORDERS - MEDICATION_HISTORY - PRESCRIBED_MEDICATIONS - MED_ORDER_DETAILS - PHARMACY_ORDERS - MEDICATION_FILL_HISTORY description: This table stores information about filled medication orders, including details about the medication, patient, prescriber, pharmacy, and order fulfillment status. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: MED_ORDER_FILL primary_key: columns: - ID dimensions: - name: IS_DELETED synonyms: - is_removed - deleted_flag - removed_status - is_archived - deleted_indicator - is_inactive description: Indicates whether a medication order has been deleted from the system. expr: IS_DELETED data_type: BOOLEAN sample_values: - ''TRUE'' - ''FALSE'' - name: MEDICATION_ID synonyms: - medication_code - med_id - rx_id - prescription_id - medication_key - drug_id description: A unique identifier for the medication being ordered. expr: MEDICATION_ID data_type: NUMBER(38,0) sample_values: - ''6581780527'' - ''5585305647'' - ''8756068399'' - name: MED_ORDER_ID synonyms: - medication_order_id - prescription_id - order_number - med_order_number - medication_request_id description: Unique identifier for a medical order fill, used to track and manage the fulfillment of a specific medical order. expr: MED_ORDER_ID data_type: NUMBER(38,0) sample_values: - ''781098814144538'' - ''629239759962138'' - ''919907336978458'' - name: PATIENT_ID synonyms: - patient_number - patient_identifier - patient_code - medical_record_number - person_id - individual_id - subject_id description: Unique identifier for the patient associated with the medication order. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''125186946170881'' - ''436936526790657'' - ''530566466174977'' facts: - name: ID synonyms: - identifier - unique_id - record_id - primary_key - key_id - unique_identifier description: Unique identifier for a medication order fill. expr: ID data_type: NUMBER(38,0) sample_values: - ''625410292121811'' - ''438359072374995'' - ''435453934698707'' time_dimensions: - name: LAST_FILL_DATE synonyms: - last_medication_fill - most_recent_fill_date - latest_fill_timestamp - last_medication_refill - most_recent_medication_fill_date - last_prescription_fill description: The date and time when the medication order was last filled. expr: LAST_FILL_DATE data_type: TIMESTAMP_NTZ(9) sample_values: - 2022-06-15T00:00:00.000+0000 - 2024-08-14T00:00:00.000+0000 - 2024-08-11T00:00:00.000+0000 - name: WRITTEN_DATE synonyms: - date_written - prescription_date - order_date - written_timestamp - prescription_timestamp - date_prescribed description: Date the medication order was written. expr: WRITTEN_DATE data_type: TIMESTAMP_NTZ(9) sample_values: - 2022-06-02T00:00:00.000+0000 - 2025-02-17T00:00:00.000+0000 - 2023-08-14T00:00:00.000+0000 - name: MED_ORDER_ICD10_CODES base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: MED_ORDER_ICD10_CODES primary_key: columns: - ID dimensions: - name: ICD10_CODE synonyms: - diagnosis_code - icd10_diagnosis - medical_code - health_code - disease_code - icd10_identifier description: A unique identifier for a specific medical diagnosis code in the ICD-10 (International Classification of Diseases, 10th Revision) system, used to classify and code diseases, symptoms, and procedures for billing and insurance purposes. expr: ICD10_CODE data_type: NUMBER(38,0) sample_values: - ''20720481075552'' - ''20087511449952'' - ''519008987709792'' - name: MED_ORDER_ID synonyms: - medical_order_identifier - order_id_number - med_order_number - medical_record_id - order_reference_id - prescription_id description: Unique identifier for a medical order. expr: MED_ORDER_ID data_type: NUMBER(38,0) sample_values: - ''1031323828355098'' - ''1031423161204762'' - ''1031579844083738'' - name: SEQNO synonyms: - sequence_number - record_number - row_number - order_number - index_number description: Sequence number of the ICD-10 code in the order. expr: SEQNO data_type: NUMBER(38,0) sample_values: - ''1'' - ''3'' - ''2'' facts: - name: ID synonyms: - identifier - unique_id - record_id - row_id - primary_key - key_id - unique_identifier description: Unique identifier for a medical order. expr: ID data_type: NUMBER(38,0) sample_values: - ''25598658'' - ''25597164'' - ''25600603'' - name: PATIENT base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT primary_key: columns: - ID dimensions: - name: FIRST_NAME synonyms: - given_name - first_given_name - personal_name - christian_name - forename description: The first name of the patient. expr: FIRST_NAME data_type: VARCHAR(16777216) sample_values: - Marcuszgcbc - CHAN - Visitnotetwozcbeb - name: LAST_NAME synonyms: - surname - family_name - last_name_field - full_last_name - person_last_name description: The patient''s last name. expr: LAST_NAME data_type: VARCHAR(16777216) sample_values: - NOGLE - Hazarokaab - Sartoriuschag - name: PRACTICE_ID synonyms: - practice_number - clinic_id - medical_group_id - provider_id - office_id - facility_id description: Unique identifier for the medical practice or healthcare facility where the patient receives care. expr: PRACTICE_ID data_type: NUMBER(38,0) sample_values: - ''854508817350660'' - ''235818542825476'' - ''765192619229188'' - name: PRIMARY_ETHNICITY synonyms: - main_ethnicity - primary_ethnic_group - dominant_ethnicity - principal_ethnicity - ethnic_origin - main_cultural_background description: The patient''s primary ethnicity, which represents their self-identified ethnic group, categorized as either "Not Hispanic or Latino", "Declined to specify", or "No ethnicity specified". expr: PRIMARY_ETHNICITY data_type: VARCHAR(22) sample_values: - Not Hispanic or Latino - Declined to specify - No ethnicity specified - name: RACE synonyms: - ethnicity - ancestry - heritage - cultural_background - national_origin description: The patient''s racial or ethnic identity, as reported or self-identified, which may be used for demographic analysis, health disparities research, and culturally sensitive care. expr: RACE data_type: VARCHAR(41) sample_values: - No race specified - Declined to specify - White - name: SEX synonyms: - gender - biological_sex - sex_category - sex_type - sex_classification description: The sex of the patient, indicating whether the patient is female, male, or unknown. expr: SEX data_type: VARCHAR(16777216) sample_values: - Female - Male - Unknown facts: - name: ID synonyms: - unique_identifier - identifier - patient_identifier - record_id - patient_id - unique_patient_id description: Unique identifier assigned to each patient for tracking and record-keeping purposes. expr: ID data_type: NUMBER(38,0) sample_values: - ''733404633300993'' - ''565616063545345'' - ''930299767226369'' time_dimensions: - name: DECEASED_DATE synonyms: - date_of_death - date_of_passing - deceased_on - date_deceased - death_date - passed_away_date description: Date of death for the patient, if applicable. expr: DECEASED_DATE data_type: DATE sample_values: - ''2023-07-19'' - ''2024-07-20'' - name: DELETION_TIME synonyms: - deletion_timestamp - deleted_at - removal_time - deleted_date - purge_time - expiration_time - archival_time description: The date and time when a patient''s record was deleted from the system. expr: DELETION_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2023-02-15T07:31:24.000+0000 - 2021-07-28T14:19:13.000+0000 - name: DOB synonyms: - date_of_birth - birth_date - birthdate - birthday - date_of_entry description: Date of Birth of the patient. expr: DOB data_type: DATE sample_values: - ''1965-12-06'' - ''1948-07-07'' - ''1937-03-09'' - name: PATIENT_PHONE base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT_PHONE primary_key: columns: - ID dimensions: - name: IS_DELETED synonyms: - is_archived - is_inactive - deleted_flag - is_removed - is_disabled - is_voided description: Indicates whether the patient''s phone number has been deleted or is no longer valid. expr: IS_DELETED data_type: BOOLEAN sample_values: - ''TRUE'' - ''FALSE'' - name: PATIENT_ID synonyms: - patient_number - patient_identifier - patient_key - person_id - individual_id - medical_record_number - patient_reference description: Unique identifier for a patient in the healthcare system. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''306772214480897'' - ''505089052770305'' - ''138585015713793'' - name: PHONE synonyms: - phone_number - contact_number - mobile_number - landline_number - telephone_number description: The phone number associated with a patient. expr: PHONE data_type: VARCHAR(16777216) sample_values: - ''6366286516'' - ''6192467357'' - ''2102591200'' - name: PHONE_TYPE synonyms: - phone_category - phone_classification - phone_kind - phone_label - phone_classification_type - phone_description description: The type of phone number associated with the patient, such as their main contact number, work number, or other phone number. expr: PHONE_TYPE data_type: VARCHAR(16777216) sample_values: - Main - Work - Other facts: - name: ID synonyms: - identifier - unique_id - record_id - primary_key - key_id - unique_identifier description: Unique identifier for a patient''s phone number record. expr: ID data_type: NUMBER(38,0) sample_values: - ''610440726446160'' - ''677516848922704'' - ''85142478520400'' time_dimensions: - name: LAST_MODIFIED synonyms: - last_updated - modified_date - modified_timestamp - date_last_changed - last_change_date - update_date - last_update_timestamp description: The date and time when the patient''s phone information was last updated or modified. expr: LAST_MODIFIED data_type: TIMESTAMP_NTZ(9) sample_values: - 2023-04-01T03:39:50.000+0000 - 2024-08-22T15:00:02.000+0000 - 2024-12-10T05:51:57.000+0000 - name: PRACTICE base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PRACTICE primary_key: columns: - ID dimensions: - name: NAME synonyms: - title - label - designation - identifier - appellation - moniker - denomination description: The name of the medical practice or healthcare organization. expr: NAME data_type: VARCHAR(200) sample_values: - Elation Demo Practice - Sina Health - Medici and Health by Design facts: - name: ID synonyms: - identifier - unique_id - practice_id - record_id - key_id - unique_identifier - practice_identifier description: Unique identifier for a practice, likely a medical or healthcare practice, used to distinguish it from others in the database. expr: ID data_type: NUMBER(38,0) sample_values: - ''156611667296260'' - ''443411168821252'' - ''546238333255684'' - name: USER base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: USER primary_key: columns: - ID dimensions: - name: CANONICAL_PHYSICIAN_ID synonyms: - standardized_physician_id - normalized_physician_id - unique_physician_identifier - physician_id_standard - standardized_doctor_id - normalized_doctor_id description: Unique identifier for a physician, used to link to other data sources and ensure consistency across different systems. expr: CANONICAL_PHYSICIAN_ID data_type: NUMBER(38,0) sample_values: - ''880949009514786'' - ''586904983568674'' - name: FIRST_NAME synonyms: - given_name - first_name - forename - personal_name - christian_name description: The first name of the user. expr: FIRST_NAME data_type: VARCHAR(256) sample_values: - Brandon - Sida - Alison - name: IS_ACTIVE synonyms: - active_status - enabled - status - is_enabled - activation_status - account_status - user_status description: Indicates whether the user account is currently active and enabled, or inactive and disabled. expr: IS_ACTIVE data_type: BOOLEAN sample_values: - ''TRUE'' - ''FALSE'' - name: LAST_NAME synonyms: - surname - family_name - last_name_field - full_last_name - person_last_name - user_last_name - individual_last_name - name_last description: The last name of the user. expr: LAST_NAME data_type: VARCHAR(256) sample_values: - Agaton - Rakowski - name: NPI synonyms: - national_provider_identifier - unique_provider_id - provider_id_number - national_provider_id_number - unique_national_provider_identifier description: National Provider Identifier (NPI) assigned to a healthcare provider, uniquely identifying them in the healthcare system. expr: NPI data_type: VARCHAR(256) sample_values: - ''1679076855'' - ''1356025514'' - ''1609943661'' - name: PHYSICIAN_ID synonyms: - physician_key - doctor_id - medical_professional_id - healthcare_provider_id - practitioner_id - physician_identifier description: Unique identifier for a licensed medical doctor or physician who is authorized to provide medical care and services. expr: PHYSICIAN_ID data_type: NUMBER(38,0) sample_values: - ''870404766302210'' - ''367021536903170'' - name: PRACTICE_ID synonyms: - practice_number - practice_code - medical_group_id - clinic_id - office_id - group_id - organization_id - facility_id description: Unique identifier for a medical practice or healthcare organization where a user is affiliated or associated. expr: PRACTICE_ID data_type: NUMBER(38,0) sample_values: - ''601886991843332'' - ''154310082564'' - ''114099081379844'' - name: TIN synonyms: - taxpayer_identification_number - employer_identification_number - federal_tax_id - tax_id - ein description: Taxpayer Identification Number (TIN) assigned to the user, typically a unique identifier for tax purposes, such as a Social Security Number (SSN) or Employer Identification Number (EIN). expr: TIN data_type: VARCHAR(10) sample_values: - ''123456789'' - 32-0231140 - name: USER_TYPE synonyms: - user_role - user_category - account_type - user_classification - user_status - role_type - user_designation description: The type of user accessing the system, which can be a physician, a member of the hospital staff, or the system itself (e.g. automated processes). expr: USER_TYPE data_type: VARCHAR(9) sample_values: - physician - staff - system facts: - name: ID synonyms: - identifier - unique_id - user_id - record_id - key_id - unique_identifier description: Unique identifier for a user in the system. expr: ID data_type: NUMBER(38,0) sample_values: - ''2395626'' - ''1262091'' - ''1635507'' - name: PATIENT_TAG synonyms: - label - tag description: This table contains free-form labels or tags for a given patient. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT_TAG primary_key: columns: - PATIENT_ID dimensions: - name: PATIENT_ID synonyms: - patient_number - patient_identifier - patient_key - person_id - individual_id - subject_id description: Unique identifier for a patient in the healthcare system. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''605988843618305'' - ''572578348335105'' - ''565565298311169'' - name: TAG_VALUE synonyms: - tag_description - label - patient_label - patient_tag_description - patient_category - classification - patient_classification description: A unique identifier assigned to a patient. expr: TAG_VALUE data_type: VARCHAR(16777216) sample_values: - CRM Patient ID 10744 - CRM Patient ID 29532 - CRM Patient ID 17554 filters: - name: current_patient_tags_only synonyms: - not deleted - active description: Limits set of Patient Tags to only those that have not been deleted. expr: DELETION_TIME IS NOT NULL relationships: - name: MEDORDER_MEDICATION left_table: MED_ORDER right_table: MEDICATION relationship_columns: - left_column: MEDICATION_ID right_column: ID relationship_type: many_to_one join_type: inner - name: MEDORDER_PATIENT left_table: MED_ORDER right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: one_to_one join_type: inner - name: MEDORDER_PRACTICE left_table: MED_ORDER right_table: PRACTICE relationship_columns: - left_column: PRACTICE_ID right_column: ID relationship_type: one_to_one join_type: left_outer - name: MEDORDER_PRESCRIBINGPHYS left_table: MED_ORDER right_table: USER relationship_columns: - left_column: PRESCRIBING_USER_ID right_column: ID relationship_type: one_to_one join_type: left_outer - name: MEDFULFILLMENT_MEDICATION left_table: MED_ORDER_FILL right_table: MEDICATION relationship_columns: - left_column: MEDICATION_ID right_column: ID relationship_type: one_to_one join_type: left_outer - name: MEDFULFILLMENT_MEDORDER left_table: MED_ORDER_FILL right_table: MED_ORDER relationship_columns: - left_column: MED_ORDER_ID right_column: ID relationship_type: many_to_one join_type: inner - name: MEDICD10_MEDORDER left_table: MED_ORDER_ICD10_CODES right_table: MED_ORDER relationship_columns: - left_column: MED_ORDER_ID right_column: ID relationship_type: many_to_one join_type: left_outer - name: PATIENT_PATIENTPHONE left_table: PATIENT_PHONE right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: one_to_one join_type: left_outer - name: PATIENT_PATIENT_TAG left_table: PATIENT_TAG right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: many_to_one join_type: left_outer custom_instructions: |- - year means calendar year not 365 days - always dedup - filter out inactive and deceased patients unless otherwise specified - use ilike over like and = - check for generic and brand name versions of drugs'); -- Semantic model: orders_semantic_model.yml SELECT SYSTEM$WRITE_SEMANTIC_MODEL_YAML($semantic_models_schema, 'name: ORDERS_SEMANTIC_MODEL tables: - name: LAB_ORDER base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: LAB_ORDER primary_key: columns: - ID - RESOLVING_DOCUMENT_ID - ORDERING_PROVIDER dimensions: - name: BILL_TYPE synonyms: - invoice_type - payment_category - billing_code - charge_type - payment_method description: The type of entity responsible for paying the bill, either the patient directly or a third-party payer such as an insurance company. expr: BILL_TYPE data_type: VARCHAR(16777216) sample_values: - patient - thirdparty - name: FREQUENCY synonyms: - repetition - recurrence - periodicity - regularity - interval - cycle - pattern - occurrence rate - event frequency - repetition rate description: The frequency at which a lab order is scheduled to be performed, such as a one-time order or a recurring order that is performed on a weekly basis. expr: FREQUENCY data_type: VARCHAR(16777216) sample_values: - STANDING WEEKLY LAB ORDER - name: HAS_REMINDER synonyms: - has_alert - reminder_set - reminder_enabled - alert_status - notification_flag description: Indicates whether a reminder has been set for the lab order. expr: HAS_REMINDER data_type: BOOLEAN sample_values: - ''TRUE'' - ''FALSE'' - name: IS_EORDER synonyms: - is_electronic_order - electronic_order_indicator - eorder_flag - is_online_order - electronic_order_status description: Indicates whether the lab order was submitted electronically. expr: IS_EORDER data_type: BOOLEAN sample_values: - ''TRUE'' - ''FALSE'' - name: LAB_CENTER synonyms: - lab_facility - testing_center - lab_location - laboratory_site - medical_lab - testing_facility - lab_site - clinical_lab description: The laboratory or testing center where the lab order was sent for processing. expr: LAB_CENTER data_type: VARCHAR(16777216) sample_values: - Quest Diagnostics - ..Other - name: LAB_ORDER_TESTS_ID synonyms: - lab_test_id - test_order_id - lab_order_test_number - test_id - lab_order_test_identifier description: Unique identifier for a specific laboratory test order. expr: LAB_ORDER_TESTS_ID data_type: NUMBER(38,0) sample_values: - ''1035680346603621'' - ''1035685028757605'' - ''304641316814949'' - name: LAB_SITE synonyms: - lab_location - test_site - lab_facility - testing_site - lab_center_location - lab_clinic description: The location or system from which the laboratory order was placed. expr: LAB_SITE data_type: VARCHAR(16777216) sample_values: - Quest - Fair Oaks - CHL eOrdering - name: LAB_VENDOR synonyms: - lab_provider - lab_supplier - lab_company - testing_vendor - lab_service_provider - diagnostic_vendor description: The laboratory vendor that processed the lab order. expr: LAB_VENDOR data_type: VARCHAR(16777216) sample_values: - Other - Quest - LabCorp - name: ORDERING_PROVIDER synonyms: - requesting_provider - ordering_doctor - ordering_physician - requesting_doctor - ordering_clinician - requesting_clinician - provider_id - ordering_party description: The healthcare provider who ordered the lab test. expr: ORDERING_PROVIDER data_type: NUMBER(38,0) sample_values: - ''102042'' - ''2165333'' - ''2494185'' - name: ORDER_STATE synonyms: - order_status - status - order_condition - current_state - processing_stage - fulfillment_status description: The current status of a laboratory order, indicating whether it is outstanding (awaiting action), cancelled (no longer being processed), or fulfilled (completed). expr: ORDER_STATE data_type: VARCHAR(16777216) sample_values: - outstanding - cancelled - fulfilled - name: ORDER_STATUS synonyms: - order_state - status - order_condition - lab_order_status - test_status - request_status description: The current status of the laboratory order, with "IP" indicating that the order is currently in progress. expr: ORDER_STATUS data_type: VARCHAR(16777216) sample_values: - IP - name: PATIENT_ID synonyms: - patient_number - patient_key - person_id - individual_id - subject_id - medical_record_number - patient_identifier description: Unique identifier for the patient associated with the laboratory order. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''292887133224961'' - ''889207837360129'' - ''1028447083298817'' - name: PT_COND synonyms: - patient_condition - patient_status - patient_state - medical_condition - patient_diagnosis - health_status description: The condition under which the patient''s lab sample was collected, indicating whether the patient was fasting or not, and if so, for how long. expr: PT_COND data_type: VARCHAR(18) sample_values: - Fasting - 8 hours - Non-Fasting - Fasting - 12 hours - name: REQ_NUMBER synonyms: - request_number - lab_request_number - order_number - lab_order_id - test_request_number description: Unique identifier for a laboratory order, used to track and manage individual lab requests. expr: REQ_NUMBER data_type: VARCHAR(16777216) sample_values: - ''1035714874441761'' - ''1035759104426017'' - name: RESOLVING_DOCUMENT_ID synonyms: - resolving_doc_id - document_resolution_id - resolving_reference_id - associated_document_id - linked_document_id - related_document_id description: The unique identifier of the document that resolved the lab order, indicating the document that contains the results or outcome of the lab test. expr: RESOLVING_DOCUMENT_ID data_type: NUMBER(38,0) - name: STAT synonyms: - status - state - condition - situation - position description: Indicates whether the lab order is a stat order, which requires immediate attention and processing, or a routine order. expr: STAT data_type: VARCHAR(3) sample_values: - ''Yes'' - ''No'' facts: - name: ID synonyms: - identifier - unique_id - record_id - primary_key - key_id - unique_identifier description: Unique identifier for a laboratory order. expr: ID data_type: NUMBER(38,0) sample_values: - ''1035664658202657'' - ''1036138586701857'' - ''898773365162017'' time_dimensions: - name: CREATION_TIME synonyms: - created_at - creation_date - timestamp_created - record_creation_time - time_created - date_created - creation_timestamp - time_of_creation description: The date and time when the lab order was created. expr: CREATION_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2022-12-07T02:49:06.000+0000 - 2020-12-21T07:43:02.000+0000 - 2025-03-20T15:52:22.000+0000 - name: DATE_FOR_TEST synonyms: - test_date - test_schedule - test_timestamp - test_scheduled_date - test_date_time - date_of_test - test_completion_date - test_execution_date description: Date on which the laboratory test was ordered or scheduled to be performed. expr: DATE_FOR_TEST data_type: DATE sample_values: - ''2019-12-12'' - ''2022-12-14'' - name: DELETION_TIME synonyms: - deletion_timestamp - deleted_at - removal_time - deleted_date - purge_time - expiration_time - archival_time description: The date and time when the lab order was deleted. expr: DELETION_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2025-07-14T19:07:24.000+0000 - 2025-07-09T11:06:12.000+0000 - name: SIGNED_TIME synonyms: - signature_time - timestamp_signed - date_signed - signing_date - approval_time - verification_time - authentication_time description: The date and time when the lab order was signed by the authorized personnel. expr: SIGNED_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2025-07-14T14:18:43.000+0000 - 2025-07-14T15:35:53.000+0000 - 2025-07-14T06:26:51.000+0000 - name: LAB_ORDER_ANSWERS base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: LAB_ORDER_ANSWERS primary_key: columns: - LAB_ORDER_ID dimensions: - name: ANSWER synonyms: - response - reply - solution - result - outcome - conclusion - resolution - feedback - reaction - conclusion description: Type of lab order answer, indicating the source or type of specimen collected for laboratory testing, such as routine, cervical, or stool samples. expr: ANSWER data_type: VARCHAR(16777216) sample_values: - routine - ccevical - stool - name: QUESTION synonyms: - query - prompt - inquiry - interrogative - problem - issue - topic - subject - matter - concern description: The type of question or test being ordered or answered, such as a specific lab test or demographic information. expr: QUESTION data_type: VARCHAR(16777216) sample_values: - BLOOD LEAD TYPE - WEIGHT (OUNCES) - ''Add On Test: Lutenizing Hormone #4004?'' - name: QUESTION_CODE synonyms: - question_id_code - query_code - code_for_question - question_identifier - query_identifier - code_for_query description: The type of laboratory test or assessment ordered for a patient. expr: QUESTION_CODE data_type: VARCHAR(16777216) sample_values: - CYTLMP - LAMOTRIGINE - SRC6 facts: - name: LAB_ORDER_ID synonyms: - lab_order_number - lab_request_id - lab_test_order_id - lab_order_reference - lab_order_code description: Unique identifier for a laboratory order, used to track and manage laboratory tests and results. expr: LAB_ORDER_ID data_type: NUMBER(38,0) sample_values: - ''639272958820385'' - ''800380243804193'' - ''437448188493857'' - name: LAB_ORDER_TESTS_ID synonyms: - lab_test_id - test_order_id - lab_order_test_number - test_id_in_lab_order - lab_order_test_reference description: Unique identifier for the laboratory test order. expr: LAB_ORDER_TESTS_ID data_type: NUMBER(38,0) sample_values: - ''895360750714942'' - ''826276185440318'' - ''506792119107646'' - name: SEQUENCE synonyms: - order - position - index - ranking - serial_number - ordinal_position description: A unique identifier for the laboratory order answer. expr: SEQUENCE data_type: NUMBER(38,0) sample_values: - ''251'' - ''9'' - ''90'' - name: LAB_RESULT base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: LAB_RESULT primary_key: columns: - LAB_REPORT_ID dimensions: - name: ABNORMAL_FLAG synonyms: - abnormal_result - irregular_result - unusual_result - anomalous_result - irregularity_indicator - abnormality_flag - unusual_status - irregular_status description: Indicates whether the lab result is outside the normal range, with ''L'' representing a value lower than normal and ''H'' representing a value higher than normal. expr: ABNORMAL_FLAG data_type: VARCHAR(3) sample_values: - L - H - name: ACCESSION_NUMBER synonyms: - accession_id - accession_code - lab_request_number - test_accession_number - specimen_id - sample_id description: Unique identifier assigned to a laboratory result, used to track and reference the result throughout the laboratory testing process. expr: ACCESSION_NUMBER data_type: VARCHAR(100) sample_values: - EN403881W - ''18732700910'' - ''51674802'' - name: ACCESSION_STATUS synonyms: - specimen_status - sample_status - accession_state - test_status_category - specimen_receipt_status - sample_collection_status description: The current status of the accession, indicating whether it is Final (F), Cancelled (C), or Pending (P). expr: ACCESSION_STATUS data_type: VARCHAR(20) sample_values: - F - C - P - name: IS_ABNORMAL synonyms: - is_anomalous - is_unusual - is_irregular - is_atypical - is_out_of_range - is_exceptional - is_unexpected - has_abnormal_result - is_outlier description: Indicates whether the laboratory result is abnormal or not. expr: IS_ABNORMAL data_type: BOOLEAN sample_values: - ''TRUE'' - ''FALSE'' - name: IS_DELETED synonyms: - is_removed - deleted_flag - is_archived - is_inactive - removed_status - deletion_status - is_purged description: Indicates whether the lab result has been deleted or is still active. expr: IS_DELETED data_type: BOOLEAN sample_values: - ''TRUE'' - ''FALSE'' - name: LAB_REPORT_ID synonyms: - lab_result_id - report_id - lab_id - test_report_id - lab_test_id - report_number description: Unique identifier for a laboratory report, used to track and reference a specific lab result. expr: LAB_REPORT_ID data_type: NUMBER(38,0) sample_values: - ''794721573404712'' - ''53288237662248'' - ''981070403731496'' - name: LOINC synonyms: - Logical Observation Identifiers Names and Codes - laboratory observation code - lab test code - medical test identifier - clinical observation code description: The LOINC (Logical Observation Identifiers Names and Codes) column represents a standardized code for identifying medical laboratory tests and observations, allowing for consistent and unambiguous communication of laboratory results across different healthcare systems and organizations. expr: LOINC data_type: VARCHAR(20) sample_values: - 20495-8 - 17861-6 - name: REFERENCE_MAX synonyms: - upper_reference_value - max_reference_value - reference_upper_limit - highest_reference_value - reference_ceiling_value description: The maximum reference value for a lab result, with a threshold of 47.0 and a catch-all category for values less than or equal to 199. expr: REFERENCE_MAX data_type: VARCHAR(100) sample_values: - ''47.0'' - <=199 - name: REFERENCE_MIN synonyms: - min_reference_value - lower_reference_limit - reference_lower_bound - minimum_reference_value - lower_threshold_value description: The minimum reference value for a laboratory test result, indicating the lower bound of the normal range for a specific test, with values representing the minimum threshold for a normal result, an abnormal result, and a critical result, respectively. expr: REFERENCE_MIN data_type: VARCHAR(100) sample_values: - ''-2.0 - +2.0'' - ''3.3'' - ''130'' - name: TEST_CATEGORY synonyms: - test_type - test_class - category_of_test - type_of_test - test_group - test_classification description: The type of laboratory test performed, such as blood work or other diagnostic tests, categorized by the specific test name. expr: TEST_CATEGORY data_type: VARCHAR(16777216) sample_values: - CARDIO IQ(R) LIPOPROTEIN (a) - ALT (SGPT) - Vitamin D, 25-Hydroxy - name: TEST_NAME synonyms: - test_label - lab_test_name - test_type - test_description - assay_name - analysis_name - examination_name description: The type of laboratory test or examination performed. expr: TEST_NAME data_type: VARCHAR(16777216) sample_values: - NOTE - ''LMP:'' - COMMENT(S) - name: TEST_STATUS synonyms: - test_result - test_outcome - lab_result_status - test_completion_status - test_result_status - test_state description: The status of the laboratory test result, where ''C'' indicates the test was completed and ''I'' indicates the test is incomplete. expr: TEST_STATUS data_type: VARCHAR(10) sample_values: - C - I - name: UNITS synonyms: - unit_of_measurement - measurement_unit - unit_type - unit_name - measurement_type - unit_description description: The unit of measurement for the laboratory result value. expr: UNITS data_type: VARCHAR(20) sample_values: - (calc) - femtograms/microlite - u[IU]/mL - name: VALUE synonyms: - result - measurement - reading - quantity - amount - score - level - count - figure - data_point description: The LAB_RESULT column "VALUE" represents the measured or calculated value of a laboratory test result, which can be a numerical value indicating the level or concentration of a particular substance or analyte in a sample. expr: VALUE data_type: VARCHAR(1024) sample_values: - ''0.81'' - ''5.09'' - ''0.89'' - name: VALUE_TYPE synonyms: - data_type - data_format - value_format - data_category - measurement_type - value_classification description: The type of value recorded in the lab result, where TX refers to a textual or descriptive value, NM refers to a numeric value, and ST refers to a structured or coded value. expr: VALUE_TYPE data_type: VARCHAR(50) sample_values: - TX - NM - ST facts: - name: ID synonyms: - identifier - unique_id - record_id - key - primary_key - unique_identifier - serial_number - code - reference_number description: Unique identifier for a laboratory result, used to distinguish and reference individual test outcomes within the system. expr: ID data_type: NUMBER(38,0) sample_values: - ''784586759929960'' - ''209496518295656'' - ''818792165998696'' time_dimensions: - name: COLLECTED_DATE synonyms: - collection_date - sample_date - test_date - specimen_collection_date - date_collected - collection_timestamp description: Date and time when the lab result was collected. expr: COLLECTED_DATE data_type: TIMESTAMP_NTZ(9) sample_values: - 2019-11-29T06:58:00.000+0000 - 2023-08-01T11:11:00.000+0000 - 2023-10-02T09:29:00.000+0000 - name: RESULTED_DATE synonyms: - result_date - test_result_date - lab_result_date - date_resulted - result_completion_date - test_completion_date - lab_completion_date - date_test_resulted description: Date and time when the laboratory result was finalized and made available. expr: RESULTED_DATE data_type: TIMESTAMP_NTZ(9) sample_values: - 2025-04-10T02:10:59.000+0000 - 2024-05-16T13:48:00.000+0000 - 2025-04-18T15:24:21.000+0000 - name: MISC_ORDERS base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: MISC_ORDERS primary_key: columns: - PRESCRIBER_USER_ID - PRACTICE_ID dimensions: - name: CLINICAL_REASON synonyms: - medical_reason - clinical_diagnosis - diagnosis_code - medical_diagnosis - health_reason - clinical_explanation - medical_explanation - diagnosis_description description: The reason for ordering a miscellaneous test or procedure, as provided by the clinician. expr: CLINICAL_REASON data_type: VARCHAR(16777216) sample_values: - multiple compression fractures in thoracic and lumbar spine seen on CT chest - eval baseline osteoporosis status - Scoliosis, suspected leg length discrepancy - history of iritis, ?IBS v IBD, and monoarthritis; eval sacroiliitis to support spondyloarthritis. - name: FOLLOW_UP_METHOD synonyms: - follow_up_approach - follow_up_procedure - follow_up_protocol - follow_up_strategy - follow_up_tactic - post_visit_plan - post_visit_procedure - post_visit_protocol description: The method by which the order follow-up will be conducted, such as sending a fax or email to the customer. expr: FOLLOW_UP_METHOD data_type: VARCHAR(16777216) sample_values: - ''Fax resutls '' - email - name: IS_DELETED synonyms: - is_archived - is_inactive - is_removed - deleted_flag - is_obsolete - is_retired description: Indicates whether the order has been deleted or is still active. expr: IS_DELETED data_type: BOOLEAN sample_values: - ''TRUE'' - ''FALSE'' - name: PATIENT_ID synonyms: - patient_number - patient_identifier - patient_key - medical_record_number - patient_code - individual_id - person_id - subject_id description: Unique identifier for the patient associated with the order. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''973030890274817'' - ''1028433759961089'' - ''870607105425409'' - name: PRACTICE_ID synonyms: - practice_number - clinic_id - medical_group_id - provider_id - office_id - facility_id - health_center_id description: Unique identifier for a medical practice. expr: PRACTICE_ID data_type: NUMBER(38,0) - name: PRESCRIBER_USER_ID synonyms: - prescriber_id - prescribing_doctor_id - ordering_provider_id - prescribing_user - healthcare_provider_id - ordering_user_id description: The identifier of the user who prescribed the order. expr: PRESCRIBER_USER_ID data_type: NUMBER(38,0) sample_values: - ''870758'' - ''29625'' - ''2164669'' - name: RESOLUTION_STATE synonyms: - status - outcome - disposition - conclusion - settlement - determination - result - resolution_status - final_state - end_state description: The current state of the resolution of the order, indicating whether it is still outstanding or has been cancelled. expr: RESOLUTION_STATE data_type: VARCHAR(16777216) sample_values: - outstanding - cancelled - name: STAT synonyms: - status - state - condition - situation - circumstance description: The status of the order, indicating whether it was received via a wet signature (physical signature) or a fax. expr: STAT data_type: VARCHAR(16777216) sample_values: - wet_reading_fax - name: TEST_NAME synonyms: - test_type - exam_name - assessment_name - diagnostic_test - medical_test - lab_test - procedure_name - examination_name description: This column captures the specific type of medical test or examination ordered for a patient, such as imaging studies or laboratory tests, and stores the name or description of the test. expr: TEST_NAME data_type: VARCHAR(16777216) sample_values: - bilateral hand and fingers - Ultrasound Liver and Gall Bladder - ''SENDOUT Xray: Spine, Lumbar - 3 View'' - name: TYPE synonyms: - category - classification - kind - sort - designation - label - class description: The type of medical order, indicating whether it is related to pulmonary, sleep, or cardiac care. expr: TYPE data_type: VARCHAR(15) sample_values: - pulmonary_order - sleep_order - cardiac_order facts: - name: ID synonyms: - identifier - unique_id - record_id - row_id - unique_identifier description: Unique identifier for a miscellaneous order. expr: ID data_type: NUMBER(38,0) sample_values: - ''700172998803484'' - ''501635463970844'' - ''1036291283681308'' - name: TEST_SCORE synonyms: - test_result - exam_score - assessment_result - test_grade - evaluation_score - score_value description: The total score achieved by a test-taker on a particular assessment or evaluation. expr: TEST_SCORE data_type: NUMBER(38,0) sample_values: - ''416'' - ''43838'' - ''13'' time_dimensions: - name: CREATION_TIME synonyms: - created_at - creation_date - timestamp_created - record_creation_time - insertion_time - entry_time - init_time - start_time - birth_time description: The date and time when the order was created. expr: CREATION_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2025-05-19T13:28:16.000+0000 - 2022-10-27T13:52:25.000+0000 - 2024-06-18T14:07:56.000+0000 - name: DATE_FOR_TEST synonyms: - test_date - test_schedule - test_scheduled_date - test_date_planned - test_planned_date - test_calendar_date description: Date for which the order is being tested or validated, typically used for quality control or auditing purposes. expr: DATE_FOR_TEST data_type: DATE sample_values: - ''2022-10-01'' - ''2023-12-14'' - name: SIGNED_TIME synonyms: - signature_time - signing_time - timestamp_signed - date_signed - signed_date - approval_time - confirmation_time - verification_time description: The date and time when the order was signed. expr: SIGNED_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2024-09-09T13:58:21.000+0000 - 2025-07-14T14:21:53.000+0000 - 2024-02-16T09:53:07.000+0000 - name: PATIENT base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT primary_key: columns: - ID dimensions: - name: EMAIL synonyms: - email_address - contact_email - patient_email - electronic_mail - email_id description: The email address of the patient. expr: EMAIL data_type: VARCHAR(16777216) - name: FIRST_NAME synonyms: - given_name - first_name_field - personal_name - christian_name - forename description: The first name of the patient. expr: FIRST_NAME data_type: VARCHAR(16777216) sample_values: - Edenzfiic - Aspen - Activozggbi - name: LAST_NAME synonyms: - surname - family_name - last_name_field - full_last_name - person_last_name description: The patient''s last name. expr: LAST_NAME data_type: VARCHAR(16777216) sample_values: - Rexrode - Terracinaibff - Mitchell - name: PRIMARY_ETHNICITY synonyms: - main_ethnicity - primary_ethnic_group - dominant_ethnicity - principal_ethnicity - ethnic_origin - ethnic_identity description: The patient''s primary ethnicity, which indicates whether the patient identifies as Hispanic or Latino, Not Hispanic or Latino, or if their ethnicity is not specified. expr: PRIMARY_ETHNICITY data_type: VARCHAR(22) sample_values: - Not Hispanic or Latino - No ethnicity specified - Hispanic or Latino - name: RACE synonyms: - ethnicity - ancestry - heritage - cultural_background - racial_identity - national_origin description: The patient''s racial identity, as reported or identified during registration or intake, categorized as White, or unknown/unspecified due to patient decline or omission. expr: RACE data_type: VARCHAR(41) sample_values: - Declined to specify - No race specified - White facts: - name: ID synonyms: - unique_identifier - identifier - patient_id - record_id - patient_number - unique_patient_id - patient_key description: Unique identifier assigned to each patient for tracking and record-keeping purposes. expr: ID data_type: NUMBER(38,0) sample_values: - ''513754082639873'' - ''492115907903489'' - ''548546957213697'' filters: - name: not_deleted synonyms: - active - existing - present - not_removed - valid - current - retained - not_archived description: Indicates whether the patient''s record is active (not deleted) or inactive (deleted). expr: DELETION_TIME is null - name: alive synonyms: - living - not deceased - surviving - existing - breathing - animate - vital - extant description: Indicates whether the patient is currently alive (true) or deceased (false). expr: DECEASED_DATE is null time_dimensions: - name: DECEASED_DATE synonyms: - date_of_death - date_of_passing - deceased_on - date_deceased - death_date - passed_away_date description: Date of death for the patient, if applicable. expr: DECEASED_DATE data_type: DATE sample_values: - ''2025-01-09'' - ''2023-09-24'' - name: DELETION_TIME synonyms: - deletion_timestamp - deleted_at - removed_time - deleted_date - removal_timestamp - purge_time description: The date and time when a patient''s record was deleted from the system. expr: DELETION_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2023-02-15T07:31:24.000+0000 - 2021-07-28T14:19:13.000+0000 - name: DOB synonyms: - date_of_birth - birth_date - birthdate - birthday - date_of_entry description: Date of Birth of the patient. expr: DOB data_type: DATE sample_values: - ''1962-01-22'' - ''1951-08-13'' - ''1966-02-04'' - name: PATIENT_PHONE base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT_PHONE primary_key: columns: - ID - PATIENT_ID dimensions: - name: IS_DELETED synonyms: - is_archived - is_inactive - deleted_flag - is_removed - is_disabled - is_voided description: Indicates whether the patient''s phone number has been deleted or is no longer valid. expr: IS_DELETED data_type: BOOLEAN sample_values: - ''TRUE'' - ''FALSE'' - name: PATIENT_ID synonyms: - patient_number - patient_identifier - patient_key - person_id - individual_id - patient_reference description: Unique identifier for a patient in the healthcare system. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''306772214480897'' - ''505089052770305'' - ''138585015713793'' - name: PHONE synonyms: - phone_number - contact_number - mobile_number - landline_number - telephone_number description: The phone number associated with a patient. expr: PHONE data_type: VARCHAR(16777216) sample_values: - ''6366286516'' - ''6192467357'' - ''2102591200'' - name: PHONE_TYPE synonyms: - phone_category - phone_classification - phone_kind - phone_label - phone_classification_type - phone_description description: The type of phone number associated with the patient, such as their main contact number, work number, or other phone number. expr: PHONE_TYPE data_type: VARCHAR(16777216) sample_values: - Main - Work - Other facts: - name: ID synonyms: - identifier - unique_id - record_id - primary_key - key_id - unique_identifier description: Unique identifier for a patient''s phone number record. expr: ID data_type: NUMBER(38,0) sample_values: - ''610440726446160'' - ''677516848922704'' - ''85142478520400'' time_dimensions: - name: LAST_MODIFIED synonyms: - last_updated - modified_date - modified_timestamp - date_last_changed - last_changed_date - update_date - date_updated description: The date and time when the patient''s phone information was last updated or modified. expr: LAST_MODIFIED data_type: TIMESTAMP_NTZ(9) sample_values: - 2023-04-01T03:39:50.000+0000 - 2024-08-22T15:00:02.000+0000 - 2024-12-10T05:51:57.000+0000 - name: PRACTICE base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PRACTICE primary_key: columns: - ID dimensions: - name: NAME synonyms: - title - label - designation - identifier - appellation - moniker - denomination description: The name of the practice or organization that provides medical services. expr: NAME data_type: VARCHAR(200) sample_values: - Elation Demo Practice - Sina Health - Medici and Health by Design facts: - name: ID synonyms: - identifier - unique_id - practice_id - record_id - key_id - unique_identifier - practice_identifier description: Unique identifier for a practice, likely a medical or healthcare practice, used to distinguish it from others in the database. expr: ID data_type: NUMBER(38,0) sample_values: - ''156611667296260'' - ''443411168821252'' - ''546238333255684'' - name: REFERRAL_ORDER base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: REFERRAL_ORDER primary_key: columns: - ID - PRESCRIBING_USER_ID - PRACTICE_ID dimensions: - name: AUTHORIZATION_FOR synonyms: - authorization_purpose - authorized_for - approval_for - permission_for - certified_for description: Indicates whether the referral order is for treatment or procedure only. expr: AUTHORIZATION_FOR data_type: VARCHAR(30) sample_values: - ForTreat - ProcOnly - name: AUTH_NUMBER synonyms: - authorization_number - auth_code - approval_number - permit_number - license_number - certification_number description: Unique identifier for a referral order, typically assigned by the healthcare provider or insurance company, used to track and manage patient referrals. expr: AUTH_NUMBER data_type: VARCHAR(256) sample_values: - 1194221317Lead CareB - 1255606190CareBridge - name: BODY synonyms: - medical_reason - clinical_diagnosis - diagnosis_reason - medical_diagnosis - health_reason - clinical_basis - medical_basis - diagnosis_code - clinical_rationale description: This column contains clinical reasons or instructions for referral orders, including specific tests, procedures, or actions to be taken, as well as additional information or context to facilitate patient care. expr: BODY data_type: VARCHAR(16777216) sample_values: - |- New Procedure order: O2 oximetry test at rest, during activity and at night Contact Marzell for appt Fax results to 682-318-0014 - |- Please set patient up with an auto-titrate cpap. To help you with seeing this patient, I''ve created a customized, interactive, HIPAA compliant patient chart that you can easily access online by following the instructions below. Please use Google Chrome or Firefox instead of Internet Explorer if at all possible to view the interactive chart. - name: DELIVERY_METHOD synonyms: - shipping_method - delivery_type - dispatch_method - transmission_method - distribution_channel description: The method by which the referral order was delivered to the recipient. expr: DELIVERY_METHOD data_type: VARCHAR(20) sample_values: - direct - fax - passport - name: EMAIL_TO synonyms: - recipient_email - email_recipient - send_to_email - email_address - to_email description: The email address to which the referral order was sent. expr: EMAIL_TO data_type: VARCHAR(200) sample_values: - ''eReferrals@medi.ci '' - kellieb@easystreet.net - name: FAX_STATUS synonyms: - fax_result - transmission_status - fax_transmission_status - fax_delivery_status - fax_completion_status description: The status of the fax transmission for the referral order, indicating whether it was successfully sent or failed. expr: FAX_STATUS data_type: VARCHAR(16777216) sample_values: - success - failed - name: IS_DELETED synonyms: - is_removed - deleted_flag - removed_status - is_archived - deleted_indicator - removal_status description: Indicates whether a referral order has been deleted or not. expr: IS_DELETED data_type: BOOLEAN sample_values: - ''FALSE'' - ''TRUE'' - name: PATIENT_ID synonyms: - patient_number - patient_identifier - patient_key - person_id - individual_id - medical_record_number - patient_reference description: Unique identifier for the patient associated with the referral order. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''838223511617537'' - ''163490562899969'' - ''737884461531137'' - name: PRACTICE_ID synonyms: - practice_number - medical_group_id - clinic_id - provider_id - facility_id - organization_id - office_id description: Unique identifier for the medical practice that referred the patient. expr: PRACTICE_ID data_type: NUMBER(38,0) - name: PRESCRIBING_USER_ID synonyms: - prescriber_id - prescribing_doctor_id - ordering_provider_id - rx_user_id - prescribing_clinician_id description: The unique identifier of the healthcare professional who prescribed the referral order. expr: PRESCRIBING_USER_ID data_type: NUMBER(38,0) - name: PROCESSING_STATUS synonyms: - status - processing_state - workflow_status - job_status - execution_status - progress_status - operation_status description: The current status of the referral order''s processing workflow, indicating whether the attachments have failed to be generated, are currently being created, or have been successfully completed. expr: PROCESSING_STATUS data_type: VARCHAR(20) sample_values: - attachments_failed - making_attachments - complete - name: RECIPIENT_CREDENTIALS synonyms: - recipient_qualifications - recipient_certifications - recipient_degrees - recipient_titles - recipient_designations description: The medical credentials of the healthcare provider who received the referral order. expr: RECIPIENT_CREDENTIALS data_type: VARCHAR(256) sample_values: - MD - name: RECIPIENT_FIRSTNAME synonyms: - first_name - recipient_given_name - first_name_of_recipient - recipient_firstname_field - sender_first_name description: The first name of the recipient of the referral order. expr: RECIPIENT_FIRSTNAME data_type: VARCHAR(16777216) sample_values: - Patrick - Richard - Right at Home - name: RECIPIENT_LASTNAME synonyms: - surname - family_name - last_name - recipient_surname - recipient_family_name description: The last name of the recipient of the referral order. expr: RECIPIENT_LASTNAME data_type: VARCHAR(16777216) sample_values: - Nephrology - Liedtke - harris - name: RECIPIENT_NPI synonyms: - recipient_provider_id - recipient_identifier - healthcare_provider_id - recipient_national_provider_identifier - recipient_unique_id description: The unique National Provider Identifier (NPI) assigned to the healthcare provider or organization that received the referral order. expr: RECIPIENT_NPI data_type: VARCHAR(16777216) sample_values: - ''1871998534'' - ''1225369515'' - name: RESOLUTION_STATE synonyms: - resolution_status - issue_resolution - problem_resolution - resolution_outcome - case_resolution - dispute_resolution - conflict_resolution - resolution_result description: The current state of the referral order, indicating whether the order is still outstanding and awaiting fulfillment or has been fulfilled. expr: RESOLUTION_STATE data_type: VARCHAR(16777216) sample_values: - outstanding - fulfilled - name: SEND_TO_NAME synonyms: - recipient_name - send_to_person - recipient_full_name - addressee_name - intended_recipient description: The name of the provider or facility to which the patient is being referred. expr: SEND_TO_NAME data_type: VARCHAR(1024) sample_values: - Pain Management - Patient Choice - Neurology- Patient Choice - Imaging Center of Choice facts: - name: ID synonyms: - identifier - unique_id - record_id - row_id - primary_key - unique_identifier - key_id - identifier_code description: Unique identifier for the referral order. expr: ID data_type: NUMBER(38,0) sample_values: - ''609749208530976'' - ''559176089534496'' - ''975105517551648'' time_dimensions: - name: CHART_FEED_DATE synonyms: - chart_update_date - chart_refresh_date - chart_sync_date - chart_data_date - chart_load_date - chart_import_date description: Date and time when the referral order was charted and fed into the system. expr: CHART_FEED_DATE data_type: TIMESTAMP_NTZ(9) sample_values: - 2024-12-16T13:21:47.000+0000 - 2021-10-01T14:47:25.000+0000 - 2023-10-23T13:01:33.000+0000 - name: CREATION_TIME synonyms: - created_at - creation_date - timestamp_created - record_creation_time - time_created - date_created - creation_timestamp - time_of_creation description: The date and time when the referral order was created. expr: CREATION_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2024-04-25T07:06:34.000+0000 - 2024-11-18T09:09:41.000+0000 - 2025-03-26T10:55:06.000+0000 - name: DELETION_TIME synonyms: - deletion_timestamp - deleted_at - removal_time - deleted_date - purge_time - deletion_date - removal_date - deleted_timestamp description: The date and time when the referral order was deleted. expr: DELETION_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2022-09-20T09:48:47.000+0000 - 2018-11-09T10:16:09.000+0000 - name: DELIVERY_DATE synonyms: - dispatch_date - delivery_timestamp - send_date - transmission_date - order_dispatch_date - order_delivery_timestamp description: The date and time when the referral order is scheduled to be delivered. expr: DELIVERY_DATE data_type: TIMESTAMP_NTZ(9) sample_values: - 2021-08-23T00:00:00.000+0000 - 2024-12-10T00:00:00.000+0000 - name: SIGNED_TIME synonyms: - signature_time - signing_date - timestamp_signed - date_signed - signed_timestamp - signature_date - time_signed description: The date and time when the referral order was signed. expr: SIGNED_TIME data_type: TIMESTAMP_NTZ(9) sample_values: - 2024-01-08T08:11:20.000+0000 - 2023-02-15T10:22:24.000+0000 - 2019-04-10T09:59:11.000+0000 - name: USER base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: USER primary_key: columns: - ID dimensions: - name: CREDENTIALS synonyms: - qualifications - certifications - licenses - professional_credentials - medical_credentials - professional_qualifications - credentials_list description: The type of advanced practice nursing credential held by the user, such as Advanced Practice Registered Nurse (APRN) or Certified Nurse Practitioner (CNP). expr: CREDENTIALS data_type: VARCHAR(256) sample_values: - APRN CNP - NP - name: FIRST_NAME synonyms: - given_name - first_name_field - personal_name - forename - christian_name description: The first name of the user. expr: FIRST_NAME data_type: VARCHAR(256) sample_values: - Brandon - Sida - Alison - name: IS_ACTIVE synonyms: - active_status - enabled - status - is_enabled - is_valid - is_current - is_live - activation_status description: Indicates whether the user account is currently active and enabled, or inactive and disabled. expr: IS_ACTIVE data_type: BOOLEAN sample_values: - ''TRUE'' - ''FALSE'' - name: LAST_NAME synonyms: - surname - family_name - last_name_field - full_last_name - surname_name description: The last name of the user. expr: LAST_NAME data_type: VARCHAR(256) sample_values: - Agaton - Rakowski - name: NPI synonyms: - national_provider_identifier - provider_id_number - unique_provider_id - healthcare_provider_id - medical_provider_id description: National Provider Identifier (NPI) assigned to a healthcare provider, uniquely identifying them in the healthcare system. expr: NPI data_type: VARCHAR(256) sample_values: - ''1679076855'' - ''1356025514'' - ''1609943661'' - name: PHYSICIAN_ID synonyms: - physician_key - doctor_id - medical_professional_id - healthcare_provider_id - practitioner_id - physician_identifier description: Unique identifier for a licensed medical doctor or physician who is authorized to provide medical care and services. expr: PHYSICIAN_ID data_type: NUMBER(38,0) sample_values: - ''870404766302210'' - ''367021536903170'' - name: PRACTICE_ID synonyms: - practice_number - practice_code - medical_group_id - clinic_id - office_id - organization_id - group_id description: Unique identifier for a medical practice or healthcare organization where a user is affiliated or associated. expr: PRACTICE_ID data_type: NUMBER(38,0) sample_values: - ''601886991843332'' - ''154310082564'' - ''114099081379844'' - name: SPECIALTY synonyms: - field_of_expertise - area_of_specialization - medical_specialty - professional_specialty - area_of_practice - discipline - profession description: The type of medical specialty or area of expertise of the user, such as Nurse Practitioner or Family Medicine. expr: SPECIALTY data_type: VARCHAR(16777216) sample_values: - Nurse Practitioner, Family - Nurse Practitioner - name: TIN synonyms: - taxpayer_identification_number - employer_identification_number - federal_tax_id - tax_id - ein description: Taxpayer Identification Number (TIN) assigned to the user, typically a unique identifier for tax purposes, such as a Social Security Number (SSN) or Employer Identification Number (EIN). expr: TIN data_type: VARCHAR(10) sample_values: - ''123456789'' - 32-0231140 - name: USER_TYPE synonyms: - user_role - user_category - account_type - user_classification - user_status - role_type - user_designation description: The type of user accessing the system, which can be a physician, a member of the hospital staff, or the system itself (e.g. automated processes). expr: USER_TYPE data_type: VARCHAR(9) sample_values: - physician - staff - system facts: - name: ID synonyms: - identifier - unique_id - user_id - record_id - key_id - unique_identifier description: Unique identifier for a user in the system. expr: ID data_type: NUMBER(38,0) sample_values: - ''2395626'' - ''1262091'' - ''1635507'' - name: PATIENT_TAG synonyms: - label - tag description: This table contains free-form labels or tags for a given patient. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT_TAG primary_key: columns: - PATIENT_ID dimensions: - name: PATIENT_ID synonyms: - patient_number - patient_identifier - patient_key - person_id - individual_id - subject_id description: Unique identifier for a patient in the healthcare system. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''605988843618305'' - ''572578348335105'' - ''565565298311169'' - name: TAG_VALUE synonyms: - tag_description - label - patient_label - patient_tag_description - patient_category - classification - patient_classification description: A unique identifier assigned to a patient. expr: TAG_VALUE data_type: VARCHAR(16777216) sample_values: - CRM Patient ID 10744 - CRM Patient ID 29532 - CRM Patient ID 17554 filters: - name: current_patient_tags_only synonyms: - not deleted - active description: Limits set of Patient Tags to only those that have not been deleted. expr: DELETION_TIME IS NOT NULL relationships: - name: LABORDERANSWERS_LABORDER left_table: LAB_ORDER right_table: LAB_ORDER_ANSWERS relationship_columns: - left_column: ID right_column: LAB_ORDER_ID relationship_type: many_to_one join_type: left_outer - name: LABORDER_LABRESULT left_table: LAB_ORDER right_table: LAB_RESULT relationship_columns: - left_column: RESOLVING_DOCUMENT_ID right_column: LAB_REPORT_ID relationship_type: many_to_one join_type: left_outer - name: LABORDER_USER left_table: LAB_ORDER right_table: USER relationship_columns: - left_column: ORDERING_PROVIDER right_column: ID relationship_type: one_to_one join_type: left_outer - name: PATIENT_LABORDER left_table: LAB_ORDER right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: one_to_one join_type: inner - name: MISCORDERS_USER left_table: MISC_ORDERS right_table: USER relationship_columns: - left_column: PRESCRIBER_USER_ID right_column: ID relationship_type: one_to_one join_type: left_outer - name: MISCORDER_PRACTICE left_table: MISC_ORDERS right_table: PRACTICE relationship_columns: - left_column: PRACTICE_ID right_column: ID relationship_type: one_to_one join_type: left_outer - name: PATIENT_MISCORDERS left_table: MISC_ORDERS right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: many_to_one join_type: inner - name: PATIENT_PATIENTPHONE left_table: PATIENT_PHONE right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: one_to_one join_type: left_outer - name: PATIENT_REFERRALORDER left_table: REFERRAL_ORDER right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: many_to_one join_type: inner - name: REFERRALORDER_PRACTICE left_table: REFERRAL_ORDER right_table: PRACTICE relationship_columns: - left_column: PRACTICE_ID right_column: ID relationship_type: one_to_one join_type: left_outer - name: REFERRALORDER_USER left_table: REFERRAL_ORDER right_table: USER relationship_columns: - left_column: PRESCRIBING_USER_ID right_column: ID relationship_type: one_to_one join_type: left_outer - name: PATIENT_PATIENT_TAG left_table: PATIENT_TAG right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: many_to_one join_type: left_outer custom_instructions: |- - use ilike instead of like and = unless specified to be case sensitive. - deduplicate results - cardiac queries should look in the lab results and also in the misc_orders table with a cardiac_order type - pulomonary queries should look in the lab results and also in the misc_orders table with a pulomonary_order type - imaging queries should look in the lab results and also in the misc_orders table with a imaging_order type - sleep queries in the misc_orders table with a sleep_order type - results for the misc_orders table is in the test_score column - if misc_order.date_for_test is null include it in date constrained queries but set the value to null - default to providing multiple rows and do not aggregate unless specified - if a lab_result value cannot be cast to a decimal treat it as a null value - prefer try_cast over cast'); -- Semantic model: vitals_semantic_model.yml SELECT SYSTEM$WRITE_SEMANTIC_MODEL_YAML($semantic_models_schema, 'name: VITALS_SEMANTIC_MODEL description: Enables plain language queries over patient vitals such as blood pressure, weight, etc. tables: - name: MEDICATION base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: MEDICATION primary_key: columns: - ID dimensions: - name: BRAND_NAME synonyms: - product_name - trade_name - proprietary_name - brand_title - trademark_name - product_title description: The brand name of the medication prescribed to the patient. expr: BRAND_NAME data_type: VARCHAR(16777216) sample_values: - Alfuzosin ER - CARVEDILOL 6.25 MG TABS - Dexcom G6 Receiver - name: BRAND_TYPE synonyms: - brand_category - brand_classification - brand_designation - brand_identifier - brand_label - brand_status description: Indicates whether the medication is a branded, trademarked product or a generic equivalent. expr: BRAND_TYPE data_type: VARCHAR(16777216) sample_values: - Trademarked - Generic - name: CONTROLLED synonyms: - regulated - restricted - monitored - supervised - governed description: Indicates whether the medication is a controlled substance, requiring special handling and monitoring. expr: CONTROLLED data_type: BOOLEAN sample_values: - ''FALSE'' - ''TRUE'' - name: DEA_DESCRIPTION synonyms: - controlled_substance_description - dea_schedule - dea_classification - controlled_substance_classification - dea_controlled_substance_description description: A description of the DEA''s classification of the medication''s potential for abuse. expr: DEA_DESCRIPTION data_type: VARCHAR(16777216) sample_values: - Limited abuse potential, small amounts (763689) - Limited abuse potential (763688) - name: DISPLAY_NAME synonyms: - full_name - label - name - title - description - designation - identifier - caption description: The DISPLAY_NAME column contains the full name of a medication, including the brand name, active ingredient, strength, and dosage form, as it would be displayed to a user or patient. expr: DISPLAY_NAME data_type: VARCHAR(16777216) sample_values: - Zithromax Tab 250 mg - Triamcinolone Acetonide Lotion 0.1 % - Lantus SoloStar Subcutaneous Solution Pen-injector 100 UNIT/ML - name: FORM synonyms: - shape - dosage_form - preparation - type - format - presentation - configuration description: The form in which the medication is administered, such as a capsule (Cap) or tablet (Tab). expr: FORM data_type: VARCHAR(16777216) sample_values: - Cap - Tab - name: GENERIC_NAME synonyms: - non_brand_name - chemical_name - active_ingredient - non_proprietary_name - common_name description: The GENERIC_NAME column contains the non-proprietary name of a medication, which is the official name given to a pharmaceutical substance, as assigned by the United States Adopted Name (USAN) Council or other naming authorities. expr: GENERIC_NAME data_type: VARCHAR(16777216) sample_values: - Semaglutide Soln Pen-inj 0.25 or 0.5 mg/DOSE (2 mg/3ML) - Insulin Pen Needle 31 G X 8 MM (1/3" or 5/16") - Iodine (Kelp) Tab 0.15 mg - name: IS_DRUG synonyms: - is_medication - is_pharmaceutical - is_prescription - is_rx - is_medical_treatment description: Indicates whether the medication is a drug or not. expr: IS_DRUG data_type: BOOLEAN sample_values: - ''FALSE'' - ''TRUE'' - name: IS_MAINTENANCE_DRUG synonyms: - is_preventative_drug - is_prophylactic_drug - maintenance_medication - ongoing_treatment - chronic_drug - long_term_medication description: Indicates whether the medication is used for maintenance purposes, meaning it is taken regularly to manage an ongoing condition, rather than to treat a specific acute illness or symptom. expr: IS_MAINTENANCE_DRUG data_type: BOOLEAN sample_values: - ''TRUE'' - ''FALSE'' - name: ROUTE synonyms: - administration_method - delivery_method - dosage_route - intake_method - medication_path description: The method by which a medication is administered to a patient, such as swallowing a pill (Oral) or breathing in a medication through the lungs (Inhalation). expr: ROUTE data_type: VARCHAR(16777216) sample_values: - Oral - Inhalation - name: STRENGTH synonyms: - potency - concentration - dosage - power - intensity - magnitude description: The strength or dosage of the medication, representing the amount of active ingredient per unit of the medication. expr: STRENGTH data_type: VARCHAR(16777216) sample_values: - 150 MCG - 1 mg - 45/105 mg - name: TYPE synonyms: - category - classification - kind - sort - form_type - medication_type - drug_type - classification_code description: The type of medication, either over-the-counter (OTC) or prescription, indicating whether a medication can be purchased without a doctor''s prescription or requires a prescription from a licensed healthcare professional. expr: TYPE data_type: VARCHAR(16777216) sample_values: - otc - prescription facts: - name: ID synonyms: - identifier - unique_id - record_id - primary_key - key_id - unique_identifier description: Unique identifier for a medication. expr: ID data_type: NUMBER(38,0) sample_values: - ''824942975844399'' - ''727451905097775'' - ''9857531951'' - name: MED_ORDER base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: MED_ORDER primary_key: columns: - MEDICATION_ID dimensions: - name: DAYS_SUPPLY synonyms: - days supply from start date of prescription - total days prescribed expr: DAYS_SUPPLY data_type: NUMBER(38,0) - name: DIRECTIONS synonyms: - administering_instructions - dosage_instructions - medication_administration - usage_guidelines - treatment_directions - prescription_instructions description: Instructions provided by a healthcare professional on how to administer a prescribed medication, including dosage, frequency, and duration of treatment. expr: DIRECTIONS data_type: VARCHAR(16777216) sample_values: - TAKE 1 CAPSULE BY MOUTH DAILY FOR 3 MONTHS - Inject 0.5 ml subcutaneously weekly for 4 weeks - TAKE 1 TABLET DAILY FOR 2 WEEKS, AND THEN INCREASE TO 1 TABLET TWICE DAILY - name: DISPLAYED_MEDICATION_NAME synonyms: - medication_name - displayed_med_name - med_name - medication_label - med_display_name - med_full_name - medication_description description: The name of the medication as it appears on the medication order, including the medication name, strength, dosage form, and route of administration. expr: DISPLAYED_MEDICATION_NAME data_type: VARCHAR(16777216) sample_values: - Albuterol Sulfate 0.63 mg/3ML Nebulization Solution Inhalation - Suboxone 12/3 mg Film Sublingual - Wegovy 1.7 mg/0.75ML Solution Auto-injector Subcutaneous - name: INDICATION synonyms: - reason_for_medication - medical_reason - diagnosis - medical_condition - treatment_reason - prescribed_for - medical_indication - health_condition description: The reason or purpose for which a medication is prescribed or ordered. expr: INDICATION data_type: VARCHAR(16777216) sample_values: - performance Anxiety - name: MEDICATION_ID synonyms: - medication_key - med_id - medication_code - rx_id - prescription_id - med_code - medication_identifier description: Unique identifier for a medication order, used to track and manage medication prescriptions within the healthcare system. expr: MEDICATION_ID data_type: NUMBER(38,0) sample_values: - ''5380898863'' - ''166258882117679'' - ''654616166447'' - name: MEDICATION_TYPE synonyms: - medication_class - medication_category - drug_type - pharmaceutical_type - medication_classification description: The type of medication ordered, indicating whether it is available over-the-counter (OTC), requires a prescription, or cannot be determined. expr: MEDICATION_TYPE data_type: VARCHAR(16777216) sample_values: - otc - prescription - undeterminable - name: NOTES synonyms: - comments - remarks - annotations - descriptions - explanations - additional_info description: Free-form text field used to capture additional information or comments related to the medical order, such as the department or provider that initiated the order or relevant patient conditions. expr: NOTES data_type: VARCHAR(16777216) sample_values: - added by cardiology - ''Seasonal allergies '' - name: PATIENT_ID synonyms: - patient_number - patient_identifier - patient_code - medical_record_number - patient_key description: Unique identifier for the patient associated with the medical order. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''643337438363649'' - ''274814153719809'' - ''599916795723777'' - name: PHARMACY_INSTRUCTIONS synonyms: - pharmacy_directions - medication_instructions - dispensing_instructions - pharmacy_guidelines - prescription_instructions description: Free text field for pharmacy staff to document any special instructions or counseling provided to the patient regarding their medication order. expr: PHARMACY_INSTRUCTIONS data_type: VARCHAR(16777216) sample_values: - please provide complete counseling - name: PRESCRIPTION_START_DATE synonyms: - prescription signed - prescription created expr: START_DATE data_type: DATE - name: PROVIDER_ID synonyms: - provider id - provider - prescribing provider id - physician - prescribing physician - physician id - prescribing physician id expr: PRESCRIBING_USER_ID data_type: NUMBER(38,0) sample_values: - ''2184738'' - ''2517103'' - name: ROUTE expr: ROUTE data_type: VARCHAR(16777216) - name: STRENGTH synonyms: - potency - concentration - dosage - intensity - magnitude - power description: The strength of the medication ordered, representing the amount of active ingredient per unit of the medication, measured in milligrams (mg) or micrograms (MCG). expr: STRENGTH data_type: VARCHAR(16777216) sample_values: - 100 mg - 180 mg - 10 MCG - name: TYPE synonyms: - category - classification - kind - sort - designation - description - nature - classification_code description: The type of medication order, indicating whether it is a new prescription, a refill of an existing prescription, or a change to the dosage of an existing prescription. expr: TYPE data_type: VARCHAR(16777216) sample_values: - New - Refill - DoseChange facts: - name: SUBSTITUTIONS synonyms: - substitutions_allowed - substitution_permitted - generic_permitted - interchangeable_permitted - replacement_allowed description: Indicates whether the medication order allows for substitutions, with 0 indicating no substitutions allowed and 1 indicating substitutions are allowed. expr: SUBSTITUTIONS data_type: NUMBER(38,0) sample_values: - ''0'' - ''1'' - name: PATIENT base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT primary_key: columns: - ID dimensions: - name: DECEASED_DATE synonyms: - date of death - death date - died expr: DECEASED_DATE data_type: DATE sample_values: - ''2024-05-23'' - ''2023-06-25'' - name: DOB synonyms: - date_of_birth - birth_date - birthdate - birthday - date_of_entry description: Date of Birth expr: DOB data_type: DATE sample_values: - ''1962-01-22'' - ''1951-08-13'' - ''1966-02-04'' - name: ETHNICITY synonyms: - race expr: PRIMARY_ETHNICITY data_type: VARCHAR(22) sample_values: - Not Hispanic or Latino - No ethnicity specified - Hispanic or Latino - name: FIRST_NAME synonyms: - given_name - first_name_field - personal_name - christian_name - forename description: The first name of the patient. expr: FIRST_NAME data_type: VARCHAR(16777216) sample_values: - Edenzfiic - Aspen - Activozggbi - name: LAST_NAME synonyms: - surname - family_name - last_name_field - full_last_name - person_last_name description: The patient''s last name. expr: LAST_NAME data_type: VARCHAR(16777216) sample_values: - Rexrode - Terracinaibff - Mitchell - name: MIDDLE_NAME synonyms: - middle_initial - second_name - middle_initials - second_given_name - middle_given_name description: The middle name of the patient. expr: MIDDLE_NAME data_type: VARCHAR(16777216) sample_values: - G - Braut - M - name: SEX synonyms: - gender - sex expr: SEX data_type: VARCHAR(16777216) sample_values: - Unknown - Female - Male facts: - name: ID synonyms: - unique_identifier - identifier - patient_identifier - record_id - patient_id - unique_patient_id - patient_key description: Unique identifier assigned to each patient for tracking and record-keeping purposes. expr: ID data_type: NUMBER(38,0) sample_values: - ''889202939789313'' - ''939776958726145'' - ''819628204490753'' - name: PATIENT_PROBLEM synonyms: - diagnosis - diagnoses - complaints - issues base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT_PROBLEM primary_key: columns: - ID dimensions: - name: DESCRIPTION synonyms: - detailed_info - detailed_description - narrative - account - depiction - explanation - portrayal - summary - outline - details description: This column contains a list of medical conditions or problems associated with a patient, providing a description of their health issues. expr: DESCRIPTION data_type: VARCHAR(16777216) sample_values: - |- Chronic constipation IBS (irritable bowel syndrome) - Chronic kidney disease due to diabetes mellitus - Chronic sinusitis, unspecified - name: PATIENT_ID synonyms: - patient_key - patient_reference - patient_identifier - person_id - individual_id - subject_id - patient_number description: Unique identifier for a patient in the healthcare system. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''352011078991873'' - ''236146257625089'' - ''275594565451777'' - name: STATUS synonyms: - state - condition - situation - position - standing - circumstance description: The current status of a patient''s problem, indicating whether it has been resolved or is still active. expr: STATUS data_type: VARCHAR(16777216) sample_values: - resolved - Active - active - name: SYNOPSIS synonyms: - summary - abstract - overview - brief_description - concise_summary - description_summary description: A brief summary or overview of a patient''s medical problem or condition. expr: SYNOPSIS data_type: VARCHAR(16777216) sample_values: - |- PRN nitro Stable on long isosorbide - ''since 2015, reports she has a full workup, but nothing found. Trying to get those specific records. '' facts: - name: ID synonyms: - identifier - unique_id - record_id - row_id - primary_key description: Unique identifier for a patient''s medical problem or condition. expr: ID data_type: NUMBER(38,0) sample_values: - ''805862817726507'' - ''902413746831403'' - ''314225773838379'' time_dimensions: - name: RESOLVED_DATE synonyms: - resolved_on - resolution_date - date_resolved - closed_date - completion_date - end_date - finished_date description: The date on which the patient''s problem or condition was resolved or no longer exists. expr: RESOLVED_DATE data_type: DATE sample_values: - ''2023-07-12'' - ''2024-05-31'' - name: START_DATE synonyms: - initiation_date - beginning_date - commencement_date - start_time - inception_date - effective_date - initial_date description: The date when the patient''s problem or condition started. expr: START_DATE data_type: DATE sample_values: - ''2015-12-10'' - ''2020-09-03'' - ''2016-07-21'' - name: PRACTICE base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PRACTICE primary_key: columns: - ID dimensions: - name: NAME synonyms: - title - label - designation - identifier - appellation - moniker - denomination description: The name of the medical practice or healthcare organization. expr: NAME data_type: VARCHAR(200) sample_values: - Elation Demo Practice - Sina Health - Medici and Health by Design facts: - name: ID synonyms: - identifier - unique_id - practice_id - record_id - key_id - unique_identifier - practice_identifier description: Unique identifier for a practice, likely a medical or healthcare practice, used to distinguish it from others in the database. expr: ID data_type: NUMBER(38,0) sample_values: - ''156611667296260'' - ''443411168821252'' - ''546238333255684'' - name: USER base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: USER primary_key: columns: - ID dimensions: - name: FIRST_NAME synonyms: - given_name - first_name - forename - personal_name - christian_name description: The first name of the user. expr: FIRST_NAME data_type: VARCHAR(256) sample_values: - Vivikah - Sylvia - Erica - name: LAST_NAME synonyms: - surname - family_name - last_name_field - full_last_name - surname_name description: The LAST_NAME column stores the surname of a user, representing their family name or the name they are commonly known by. expr: LAST_NAME data_type: VARCHAR(256) sample_values: - Wortham - De los Reyes - Kennedy - name: PRACTICE_ID synonyms: - practice_number - medical_group_id - clinic_id - office_id - group_practice_identifier description: Unique identifier for a medical practice or healthcare organization where a patient receives care. expr: PRACTICE_ID data_type: NUMBER(38,0) sample_values: - ''546238333255684'' - ''334589232021508'' - ''831423838093316'' facts: - name: ID synonyms: - identifier - unique_id - user_id - record_id - key_id - unique_identifier description: Unique identifier for a user in the system. expr: ID data_type: NUMBER(38,0) sample_values: - ''2638993'' - ''2638991'' - ''2637498'' - name: VITAL base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: VITAL dimensions: - name: PATIENT_ID synonyms: - patient_number - patient_code - person_id - individual_id - subject_id - medical_record_number - patient_identifier description: Unique identifier for a patient in the healthcare system. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''720109311623169'' - ''829919257362433'' - ''230624149307393'' - name: PRACTICE_ID synonyms: - practice id - practice expr: PRACTICE_ID data_type: NUMBER(38,0) sample_values: - ''334589232021508'' - ''1622323363844'' - ''252938006560772'' - name: PROVIDER_ID synonyms: - physician id - physician - provider id - provider expr: CREATED_BY_USER_ID data_type: NUMBER(38,0) sample_values: - ''667263'' - ''2604959'' - ''794131'' - name: VITAL_PK synonyms: - vital_id - id expr: ID data_type: NUMBER(38,0) facts: - name: BMI synonyms: - body_mass_index - mass_index - body_mass - bmi_value - weight_to_height_ratio description: Body Mass Index (BMI) is a numerical value calculated from a person''s weight and height, used to assess their weight status and potential health risks. expr: BMI data_type: FLOAT sample_values: - ''33.45'' - ''24.82'' - name: HEART_RATE synonyms: - heart rate - heart beats - pulse rate description: Measured heart rate expr: HR data_type: VARCHAR(16777216) sample_values: - ''72'' - ''73'' - ''71'' - name: HEART_RATE_NOTE synonyms: - heart rate note - heart rate notes - pulse rate notes description: Notes on the heart rate recorded, if any expr: HEIGHT_NOTE data_type: VARCHAR(16777216) sample_values: - Wife reported - '' '' - Recorded at home - name: HEIGHT synonyms: - length - stature - altitude - vertical measurement - body height - tallness description: The height of the individual in inches. expr: HEIGHT data_type: VARCHAR(16777216) sample_values: - ''75'' - ''65'' - name: HEIGHT_NOTE synonyms: - height_comment - height_description - height_remark - height_annotation - height_explanation description: A note or comment related to the patient''s height measurement, with a value of "Unknown" indicating that the height was not recorded or is missing. expr: HEIGHT_NOTE data_type: VARCHAR(16777216) sample_values: - ''8'' - Unknown - name: HEIGHT_UNITS synonyms: - height_unit - unit_of_height - height_measurement_unit - height_unit_of_measurement - unit_for_height description: The unit of measurement for the patient''s height, which is inches. expr: HEIGHT_UNITS data_type: VARCHAR(16777216) sample_values: - in - name: OXYGEN synonyms: - oxygen - o2 - saturation description: Oxygen saturation percentage expr: O2_PERCENT data_type: VARCHAR(16777216) sample_values: - ''0'' - ''94'' - 58 % - name: OXYGEN_NOTE synonyms: - oxygen note - o2 note - saturation note description: Notes about O2 reading expr: O2_NOTE data_type: VARCHAR(16777216) sample_values: - Ra - 3LPM - name: RESPIRATION_RATE synonyms: - Breaths per minute - respiration rate - rr description: Respiration rate, assumed to be measured per minute expr: RR data_type: VARCHAR(16777216) sample_values: - ''16'' - 16 /min - name: RESPIRATION_RATE_NOTE synonyms: - breaths per minute note - respiration note - rr note description: Notes on measured respiration rate, if any expr: RR_NOTE data_type: VARCHAR(16777216) sample_values: - OK - name: TEMPERATURE synonyms: - temperature - temp - body temp description: Measured temperature, units is stored in a different fact, TEMPERATURE_UNIT expr: TEMPERATURE data_type: VARCHAR(16777216) sample_values: - ''102'' - ''36.5'' - afebrile - name: TEMPERATURE_NOTE synonyms: - body temp note - temp notes - temperature note description: Notes taken when temperature was measured, if any expr: TEMPERATURE_NOTE data_type: VARCHAR(16777216) sample_values: - ear - name: TEMPERATURE_UNIT synonyms: - temp unit - body temp unit - temperature units description: What unit of measurement was used when measuring the temperature expr: TEMPERATURE_UNITS data_type: VARCHAR(16777216) sample_values: - F - C - name: WEIGHT synonyms: - mass - body_weight - weight_value - patient_weight - recorded_weight description: The weight of the patient in pounds. expr: WEIGHT data_type: VARCHAR(16777216) sample_values: - ''285.8'' - ''195'' - ''200.621'' - name: WEIGHT_NOTE synonyms: - weight_comment - weight_description - weight_remark - weight_annotation - weight_explanation description: A note or comment related to the patient''s weight, which may include the weight in kilograms or an indication that the weight is unknown. expr: WEIGHT_NOTE data_type: VARCHAR(16777216) sample_values: - 80 kilo - Unknown - name: WEIGHT_UNITS synonyms: - weight_unit - unit_of_weight - weight_measurement_unit - mass_unit - unit_of_mass description: The unit of measurement for the patient''s weight. expr: WEIGHT_UNITS data_type: VARCHAR(16777216) sample_values: - lbs time_dimensions: - name: CHART_FEED_DATE synonyms: - vital date - measurement date - recording date - appointment time - when seen. expr: chart_feed_date data_type: TIMESTAMP_NTZ(9) sample_values: - 2024-02-28T09:36:14.000+0000 - 2022-09-08T00:00:00.000+0000 - 2024-08-02T09:32:58.000+0000 - name: VITAL_BP base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: VITAL_BP primary_key: columns: - VITAL_ID dimensions: - name: BP_D synonyms: - diastolic_blood_pressure - diastolic_pressure - bp_diastolic - diastolic_bp_value - resting_diastolic_pressure description: Diastolic Blood Pressure reading, measured in millimeters of mercury (mmHg), representing the minimum pressure in the arteries between beats when the heart is resting and refilling with blood. expr: BP_D data_type: VARCHAR(16777216) sample_values: - ''88'' - ''73'' - ''98'' - name: BP_NOTE synonyms: - blood_pressure_comment - bp_comment - blood_pressure_note - bp_note_description - blood_pressure_observation - bp_observation - blood_pressure_remark - bp_remark description: ''Blood Pressure Note: A free-text field to capture additional information or notes about the blood pressure reading, such as the position of the patient (e.g. sitting, left arm) or any irregularities (e.g. "oversize adult").'' expr: BP_NOTE data_type: VARCHAR(16777216) sample_values: - sitting - left oversize adult; 105/60? right. - left arm seated - name: BP_S synonyms: - systolic_blood_pressure - systolic_bp - blood_pressure_systolic - systolic_reading - systolic_value description: Systolic Blood Pressure reading, measured in millimeters of mercury (mmHg), representing the maximum pressure exerted in the arteries when the heart beats. expr: BP_S data_type: VARCHAR(16777216) sample_values: - ''115'' - ''144'' - ''134'' - name: PRACTICE_ID synonyms: - practice id - practice expr: PRACTICE_ID data_type: NUMBER(38,0) sample_values: - ''546238333255684'' - ''61823929352196'' - ''618839851270148'' facts: - name: PATIENT_ID synonyms: - patient_number - patient_identifier - person_id - individual_id - subject_id - medical_record_number - patient_key description: Unique identifier for a patient in the healthcare system. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''70536457420801'' - ''162233162334209'' - ''517662842028033'' - name: VITAL_ID synonyms: - vital_sign_id - vital_record_id - vital_measurement_id - health_id - medical_id - patient_vital_id - vital_identifier description: Unique identifier for a vital sign blood pressure measurement. expr: VITAL_ID data_type: NUMBER(38,0) sample_values: - ''577761643659536'' - ''715198903746832'' - ''430767712436496'' - name: PATIENT_TAG synonyms: - label - tag description: This table contains free-form labels or tags for a given patient. base_table: database: ' || $target_database || ' schema: ' || $target_schema || ' table: PATIENT_TAG primary_key: columns: - PATIENT_ID dimensions: - name: PATIENT_ID synonyms: - patient_number - patient_identifier - patient_key - person_id - individual_id - subject_id description: Unique identifier for a patient in the healthcare system. expr: PATIENT_ID data_type: NUMBER(38,0) sample_values: - ''605988843618305'' - ''572578348335105'' - ''565565298311169'' - name: TAG_VALUE synonyms: - tag_description - label - patient_label - patient_tag_description - patient_category - classification - patient_classification description: A unique identifier assigned to a patient. expr: TAG_VALUE data_type: VARCHAR(16777216) sample_values: - CRM Patient ID 10744 - CRM Patient ID 29532 - CRM Patient ID 17554 filters: - name: current_patient_tags_only synonyms: - not deleted - active description: Limits set of Patient Tags to only those that have not been deleted. expr: DELETION_TIME IS NOT NULL relationships: - name: ''"MED ORDER -> PATIENT"'' left_table: MED_ORDER right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: many_to_one join_type: left_outer - name: ''"MED ORDER -> PROVIDER"'' left_table: MED_ORDER right_table: USER relationship_columns: - left_column: PROVIDER_ID right_column: ID relationship_type: many_to_one join_type: left_outer - name: ''"MED-ORDER-TO-MEDS"'' left_table: MED_ORDER right_table: MEDICATION relationship_columns: - left_column: MEDICATION_ID right_column: ID relationship_type: many_to_one join_type: inner - name: ''"PATIENT -> PROBLEMS"'' left_table: PATIENT_PROBLEM right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: many_to_one join_type: left_outer - name: ''"PROVIDER -> PRACTICE"'' left_table: USER right_table: PRACTICE relationship_columns: - left_column: PRACTICE_ID right_column: ID relationship_type: many_to_one join_type: left_outer - name: ''"VITAL -> BP"'' left_table: VITAL right_table: VITAL_BP relationship_columns: - left_column: VITAL_PK right_column: VITAL_ID relationship_type: many_to_one join_type: left_outer - name: ''"VITAL -> PATIENT"'' left_table: VITAL right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: many_to_one join_type: left_outer - name: ''"VITALS -> PRACTICE"'' left_table: VITAL right_table: PRACTICE relationship_columns: - left_column: PRACTICE_ID right_column: ID relationship_type: many_to_one join_type: left_outer - name: ''"VITALS -> PROVIDER"'' left_table: VITAL right_table: USER relationship_columns: - left_column: PROVIDER_ID right_column: ID relationship_type: many_to_one join_type: left_outer - name: ''"VITAL_BP -> PROVIDER"'' left_table: VITAL right_table: USER relationship_columns: - left_column: PROVIDER_ID right_column: ID relationship_type: many_to_one join_type: left_outer - name: ''"VITAL BP -> PRACTICE"'' left_table: VITAL_BP right_table: PRACTICE relationship_columns: - left_column: PRACTICE_ID right_column: ID relationship_type: many_to_one join_type: left_outer - name: PATIENT_PATIENT_TAG left_table: PATIENT_TAG right_table: PATIENT relationship_columns: - left_column: PATIENT_ID right_column: ID relationship_type: many_to_one join_type: left_outer verified_queries: - name: High blood pressure, diagnoses, medications question: I want to know who has a blood pressure reading of 130/80 mm Hg or higher and what diagnosis they have, include medication list use_as_onboarding_question: false sql: WITH bp_patients AS (SELECT DISTINCT v.patient_id FROM vital AS v LEFT OUTER JOIN vital_bp AS vb ON v.vital_pk = vb.vital_id WHERE REGEXP_LIKE(vb.bp_s, ''^[0-9]+$'') AND REGEXP_LIKE(vb.bp_d, ''^[0-9]+$'') AND CAST(vb.bp_s AS INT) >= 130 AND CAST(vb.bp_d AS INT) >= 80), diagnoses AS (SELECT p.patient_id, LISTAGG(pp.description, ''; '') WITHIN GROUP (ORDER BY pp.description) AS diagnoses FROM bp_patients AS p LEFT OUTER JOIN patient_problem AS pp ON p.patient_id = pp.patient_id WHERE pp.status = ''Active'' GROUP BY p.patient_id), medications AS (SELECT p.patient_id, LISTAGG(m.display_name, ''; '') WITHIN GROUP (ORDER BY m.display_name) AS medications FROM bp_patients AS p LEFT OUTER JOIN med_order AS mo ON p.patient_id = mo.patient_id INNER JOIN medication AS m ON mo.medication_id = m.id GROUP BY p.patient_id) SELECT d.patient_id, d.diagnoses, m.medications FROM diagnoses AS d LEFT OUTER JOIN medications AS m ON d.patient_id = m.patient_id ORDER BY d.patient_id DESC NULLS LAST verified_by: Liam Clarke-Hutchinson verified_at: 1752536763 - name: High blood pressure and medications. question: | I want to know who has a blood pressure reading of 130/80 mm Hg or higher and what medications they are on use_as_onboarding_question: false sql: WITH bp_patients AS (SELECT DISTINCT v.patient_id FROM vital AS v LEFT OUTER JOIN vital_bp AS vb ON v.vital_pk = vb.vital_id WHERE REGEXP_LIKE(vb.bp_s, ''^[0-9]+$'') AND REGEXP_LIKE(vb.bp_d, ''^[0-9]+$'') AND CAST(vb.bp_s AS INT) >= 130 AND CAST(vb.bp_d AS INT) >= 80), medications AS (SELECT p.patient_id, LISTAGG(m.display_name, ''; '') WITHIN GROUP (ORDER BY m.display_name) AS medications FROM bp_patients AS p LEFT OUTER JOIN med_order AS mo ON p.patient_id = mo.patient_id INNER JOIN medication AS m ON mo.medication_id = m.id GROUP BY p.patient_id) SELECT p.patient_id, m.medications FROM bp_patients AS p LEFT OUTER JOIN medications AS m ON p.patient_id = m.patient_id ORDER BY p.patient_id DESC NULLS LAST verified_by: Liam Clarke-Hutchinson verified_at: 1752536828 - name: Who has blood pressure over 130/80 mm Hg question: I want to know who has a blood pressure reading of 130/80 mm Hg or higher use_as_onboarding_question: false sql: SELECT DISTINCT v.patient_id FROM vital AS v LEFT OUTER JOIN vital_bp AS vb ON v.vital_pk = vb.vital_id WHERE REGEXP_LIKE(vb.bp_s, ''^[0-9]+$'') AND REGEXP_LIKE(vb.bp_d, ''^[0-9]+$'') AND CAST(vb.bp_s AS INT) >= 130 AND CAST(vb.bp_d AS INT) >= 80 ORDER BY v.patient_id DESC NULLS LAST verified_by: Liam Clarke-Hutchinson verified_at: 1752536891 - name: Unhealthy BMI, and last time it was measured question: I want to find patients with a BMI that is considered unhealthy, and the last time their weight was measured. use_as_onboarding_question: false sql: WITH latest_weight AS (SELECT v.patient_id, v.bmi, v.chart_feed_date, ROW_NUMBER() OVER (PARTITION BY v.patient_id ORDER BY v.chart_feed_date DESC NULLS LAST) AS rn FROM vital AS v WHERE NOT v.bmi IS NULL AND (v.bmi < 18.5 OR v.bmi >= 25)) SELECT lw.patient_id, lw.bmi, CASE WHEN lw.bmi < 18.5 THEN ''Underweight'' WHEN lw.bmi >= 25 AND lw.bmi < 30 THEN ''Overweight'' WHEN lw.bmi >= 30 THEN ''Obese'' END AS bmi_category, TO_CHAR(lw.chart_feed_date, ''MM/DD/YYYY'') AS last_measurement_date FROM latest_weight AS lw WHERE rn = 1 ORDER BY lw.chart_feed_date DESC NULLS LAST verified_by: Liam Clarke-Hutchinson verified_at: 1752538341 - name: Patients with high blood pressure question: I want to find patients with High Blood pressure use_as_onboarding_question: false sql: SELECT DISTINCT v.patient_id FROM vital AS v LEFT OUTER JOIN vital_bp AS vb ON v.vital_pk = vb.vital_id WHERE REGEXP_LIKE(vb.bp_s, ''^[0-9]+$'') AND REGEXP_LIKE(vb.bp_d, ''^[0-9]+$'') AND (CAST(vb.bp_s AS INT) >= 130 OR CAST(vb.bp_d AS INT) >= 80) ORDER BY v.patient_id DESC NULLS LAST verified_by: Liam Clarke-Hutchinson verified_at: 1752538397 - name: FEVER_DETAILS question: Show me the names and dates of birth of anyone who had a fever, when the high temperature was recorded, and what their temperature was, in Fahrenheit. use_as_onboarding_question: false sql: WITH fever_readings AS (SELECT v.patient_id, v.chart_feed_date, CASE WHEN v.temperature_unit = ''C'' THEN TRY_CAST(v.temperature AS FLOAT) * 9 / NULLIF(5, 0) + 32 ELSE TRY_CAST(v.temperature AS FLOAT) END AS temp_f FROM vital AS v WHERE ((v.temperature_unit = ''F'' AND TRY_CAST(v.temperature AS FLOAT) > 98.6) OR (v.temperature_unit = ''C'' AND TRY_CAST(v.temperature AS FLOAT) > 37)) AND NOT TRY_CAST(v.temperature AS FLOAT) IS NULL) SELECT p.first_name, p.last_name, TO_CHAR(p.dob, ''MM/DD/YYYY'') AS date_of_birth, TO_CHAR(fr.chart_feed_date, ''MM/DD/YYYY HH24:MI:SS'') AS fever_timestamp, ROUND(fr.temp_f, 1) AS temperature_f FROM fever_readings AS fr LEFT OUTER JOIN patient AS p ON fr.patient_id = p.id ORDER BY p.last_name, p.first_name, fr.chart_feed_date DESC NULLS LAST verified_by: Liam Clarke-Hutchinson verified_at: 1752619797 - name: I want to see patients with an oxygen level below 90 percent, include oxygen level, grouped by provider question: I want to see patients with an oxygen level below 90 percent, include oxygen level, grouped by provider sql: |- WITH oxygen_patients AS ( SELECT DISTINCT v.patient_id, v.provider_id, REGEXP_REPLACE(v.oxygen, ''[^0-9]'', '''') || ''%'' AS oxygen_level FROM vital AS v LEFT OUTER JOIN patient AS p ON v.patient_id = p.id WHERE REGEXP_LIKE(v.oxygen, ''^[0-9]+'') AND CAST(REGEXP_REPLACE(v.oxygen, ''[^0-9]'', '''') AS INT) < 90 AND p.deceased_date IS NULL ) SELECT CONCAT(u.first_name, '' '', u.last_name) AS provider_name, CONCAT(p.first_name, '' '', p.last_name) AS patient_name, op.oxygen_level FROM oxygen_patients AS op LEFT OUTER JOIN user AS u ON op.provider_id = u.id LEFT OUTER JOIN patient AS p ON op.patient_id = p.id ORDER BY provider_name DESC NULLS LAST, patient_name DESC NULLS LAST use_as_onboarding_question: false verified_by: Liam Clarke-Hutchinson verified_at: 1753670786 custom_instructions: |- When answering queries about blood pressure, only include records where both systolic (bp_s) and diastolic (bp_d) blood pressure was recorded. Some vital records won''t be numeric, please ignore those. To find medications for a patient whose vitals match the query, join from vital to patient, then from patient to med order. When asked to list a patient''s diagnoses and their medications at the same time, collate diagnoses and medications so that there is one row per patient. When displaying dates stored as integer timestamps, please format them to be human readable, choose a convenient format for Americans. When discussing BMI, please provide both the numerical form, and in a column beside it, the description of what the number represents, e.g.,. 35 or higher represents "obese". When querying temperature, ensure that the temperature unit is taken into consideration for numeric temperatures, as some recordings will be in Celsius, and some in Fahrenheit. Some temperature recordings won''t be numeric, ignore these. Some heart rate and respiration rate measurements will include text like "per minute" or "/min" - this is okay, we assume all heart rate, and respiration rates are per minute, so just take the integer reading from the field and work with that. Year means calendar year not 365 days. Always deduplicate daata. Filter out inactive and deceased patients unless otherwise specified. When writing SQL, prefer ilike over like and =. Name = First and Last not Full Name or First and Middle And Last. When showing patient or provider names, please show their name as one column. Unless asked specifically for a patient or provider''s id, show their name over their id. When showing oxygen levels, append a percent sign to the number. When asked for a list of patients grouped by anything, show each patient and their corresponding data.'); -- Install Elation Analyst CREATE APPLICATION IF NOT EXISTS IDENTIFIER('"ELATIONHEALTH_EHDW_ELATION_ANALYST"') FROM LISTING IDENTIFIER($listing_global_name); -- Grant privileges to Elation Analyst GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO APPLICATION ELATIONHEALTH_EHDW_ELATION_ANALYST; GRANT ALL ON DATABASE IDENTIFIER($account_management_db) TO APPLICATION ELATIONHEALTH_EHDW_ELATION_ANALYST; GRANT ALL ON SCHEMA IDENTIFIER($semantic_models_schema) TO APPLICATION ELATIONHEALTH_EHDW_ELATION_ANALYST; GRANT SELECT ON ALL SEMANTIC VIEWS IN SCHEMA IDENTIFIER($semantic_models_schema) TO APPLICATION ELATIONHEALTH_EHDW_ELATION_ANALYST; GRANT IMPORTED PRIVILEGES ON DATABASE IDENTIFIER($target_database) TO APPLICATION ELATIONHEALTH_EHDW_ELATION_ANALYST; GRANT APPLICATION ROLE ELATIONHEALTH_EHDW_ELATION_ANALYST.APP_ELATION_ANALYST_PUBLIC TO ROLE PUBLIC; GRANT OPERATE, USAGE ON WAREHOUSE IDENTIFIER($warehouse_name) TO ROLE PUBLIC;