dwh_patient
Description¶
This table serves as the central identity management for all Patients in the database. It contains records that uniquely identify each patient, and some demographic information.
CNIL compliant warehouse¶
This data model was published before the CNIL referential for health data warehouse. Some fields are not used to comply with the obligation to separate directly identifying data from clinical data (SEC-LOG-4)
Merged patient¶
In some data source, a patient can be recorded multiple times.
You can use the is_merged and merged_into fields to control patient merges.
A merged patient should not have associated data in other tables.
Columns¶
| Field | User Guide | ETL Conventions | Datatype | Required | Primary Key | Foreign Key | FK Table |
|---|---|---|---|---|---|---|---|
| patient_num | Unique identifier | bigint(64) | Yes | Yes | No | ||
| lastname | null in CNIL compliant warehouse | varchar(120) | No | No | No | ||
| maiden_name | null in CNIL compliant warehouse | varchar(120) | No | No | No | ||
| firstname | null in CNIL compliant warehouse | varchar(120) | No | No | No | ||
| birth_date | If the precise date include day or month is not known or not allowed, January is used as the default month, and the first day of the month the default day | timestamptz | No | No | No | ||
| sex | Biological sex at birth | F (female), M (male), O (other), empty if unknown | varchar(2) | No | No | No | |
| nss | Social security number | null in CNIL compliant warehouse | varchar(20) | No | No | No | |
| phone_number | null in CNIL compliant warehouse | varchar(50) | No | No | No | ||
| null in CNIL compliant warehouse | varchar(500) | No | No | No | |||
| residence_address | null in CNIL compliant warehouse | varchar(1000) | No | No | No | ||
| residence_country | varchar(100) | No | No | No | |||
| residence_city | varchar(200) | No | No | No | |||
| zip_code | varchar(30) | No | No | No | |||
| birth_country | varchar(100) | No | No | No | |||
| birth_city | varchar(200) | No | No | No | |||
| birth_zip_code | varchar(30) | No | No | No | |||
| death_code | Vital status | null if alive, d if dead | varchar(2) | No | No | No | |
| death_date | Date of death | If the precise date include day or month is not known or not allowed, January is used as the default month, and the first day of the month the default day | timestamptz | No | No | No | |
| is_merged | Indicate this patient was merged in another patient | true if merged | boolean | No | No | No | |
| merged_into | Patient this one was merged into | bigint(64) | No | No | Yes | dwh_patient | |
| max_date | The last time this patient came to the healthcare facility | Computed from the other table | date | No | No | No | |
| id_patient_source | Unique identifier in source software | varchar(255) | No | No | No | ||
| instance_id | Code of the healthcare center, see hospital_instance for more informations | varchar(40) | No | No | No | ||
| patient_pid | Pseudo-identifier for the patient. Used in correspondence tables to retrieve the original patient identity if needed. | Generated as a SHA-256 hash of id_patient_source combined with patient_salt. | varchar(300) | No | No | No | |
| patient_salt | Random salt used in the hash algorithm to generate patient_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 |