dwh_data
Description¶
This table contains clinical events and observations associated with a patient, including conditions, procedures, laboratory results, drug exposures, and other healthcare-related data. Each record represents a single data point with its associated concept, value, temporal attributes, and care context.
Columns¶
| Field | User Guide | ETL Conventions | Datatype | Required | Primary Key | Foreign Key | FK Table |
|---|---|---|---|---|---|---|---|
| data_num | Unique identifier | bigint(64) | Yes | Yes | No | ||
| patient_num | bigint(64) | Yes | No | Yes | dwh_patient | ||
| thesaurus_data_num | The concept identifier associated with the data record (e.g., diagnosis, procedure, lab test). | bigint(64) | Yes | No | Yes | dwh_thesaurus_data | |
| thesaurus_code | The source vocabulary code corresponding to the associated concept (e.g., ICD-10, LOINC, ATC). | varchar(40) | Yes | No | No | ||
| document_date | The date the data was recorded in the source system. | timestamptz | Yes | No | No | ||
| start_date | The start date of the clinical event, observation or drugs. | timestamptz | Yes | No | No | ||
| end_date | The end date of the clinical event, observation or drugs, if applicable. | timestamptz | No | No | No | ||
| val_numeric | A numeric value associated with the data record (e.g., lab result, measurement). | double(53) | No | No | No | ||
| operator | The operator applied to the value (e.g. <, >, =). | varchar(5) | No | No | No | ||
| val_text | A textual or categorical value associated with the data record. | varchar(10000) | No | No | No | ||
| lower_bound | The lower boundary of a value range, if applicable. | double(53) | No | No | No | ||
| upper_bound | The upper boundary of a value range, if applicable. | double(53) | No | No | No | ||
| instance_data_id | Code of the healthcare center, see hospital_instance for more informations. | varchar(40) | No | No | No | ||
| document_origin_code | Indicate source software for this record. | varchar(60) | No | No | No | ||
| id_data_source | Unique identifier in source software. | varchar(300) | No | No | No | ||
| stay_num | The visit during which the data record was generated. | bigint(64) | No | No | Yes | dwh_patient_stay | |
| department_num | The service associated with the data record. | bigint(64) | No | No | Yes | dwh_thesaurus_department | |
| document_num | The identifier of the document grouping multiple related data records (e.g. all results from a lab report). | bigint(64) | No | No | Yes | dwh_document | |
| data_pid | Optional pseudo-identifier for a data. Mainly included for structural consistency; not required for standard analytical use. | Generated as a hash of id_data_source combined with data_salt. | varchar(300) | No | No | No | |
| data_salt | Optional random salt used in the hash algorithm to generate data_pid. | varchar(300) | No | No | No | ||
| upload_id | Identifier of the pipeline integration run, used to differentiate each batch of integrated data. | Defined at the start of the pipeline as datetime.now().strftime("%Y%m%d%H%M%S"). For example a batch integrated on 15/09/2025 at 00:00:00 has upload_id = 20250915000000. |
bigint(64) | No | No | No | |
| update_date | Date and time of the record’s last update. | timestamptz | No | No | No |
French PMSI Extension Fields¶
| Field | User Guide | ETL Conventions | Datatype | Required | Primary Key | Foreign Key | FK Table |
|---|---|---|---|---|---|---|---|
| activity_form | The form of activity related to the PMSI coding. | 30 : CMP 31 : Outpatient care provided by other facilities than CMP or CATTP 32 : CATTP |
varchar(4) | No | No | No | |
| intersectoriel_number | Identifier for inter-sectorial linkage of PMSI episodes. | varchar(6) | No | No | No | ||
| residency_zip_code | Patient's residency postal code as recorded in PMSI. | integer(32) | No | No | No |
Drug Exposure Extension Fields¶
| Field | User Guide | ETL Conventions | Datatype | Required | Primary Key | Foreign Key | FK Table |
|---|---|---|---|---|---|---|---|
| frequency | Frequency of drug administration (e.g. once daily, twice daily) | integer(32) | No | No | No | ||
| period | The duration of the prescription or administration period. | integer(32) | No | No | No | ||
| period_unit | The unit of time for the period (e.g. days, weeks, months). | varchar(25) | No | No | No | ||
| route | The route of drug administration (e.g. oral, intravenous). | varchar(50) | No | No | No | ||
| data_status | The status of the prescription or data record (e.g. prescribed, administered, cancelled) | varchar(20) | No | No | No | ||
| data_unit | The unit of measure for the drug dose (e.g. mg, mL). | varchar(25) | No | No | No | ||
| daily_details | Additional structured details about daily drug administration (e.g. morning/evening, meal-related intake). | varchar(400) | No | No | No | ||
| drug_condition | Indicates whether the prescription is conditional. | boolean | No | No | No | ||
| drug_condition_reason | The clinical reason or trigger for a conditional prescription (e.g., if needed, if severe pain). | varchar(4000) | No | No | No |