Proprietary RWD Extension Data Model

The Real-World Data Extension data model (RWD_ED_EXT_*Schemas) optimizes access to tables that use JSON data types by flattening content and incorporating data cleansing.

Key Features and Structure

Columns that use the JSON data type are not optimized for analytics. The RWD_ED_EXT data model extends the standard data model to improve access to data and performance:

• The EXT model extends the RAW RWD format with additional columns and data cleansing.
• EXT tables are named with the prefix DW_RWD_EXT_<RWD Object Name>. Synonyms are also created, so you could refer to the RWD Object Name.
• JSON column data expansion:
  • Original JSON columns are still in the EXT table.
  • JSON elements are expanded as separate string columns as follows: EXT_<JSONColumnName>_<JSONElementName>. For example, for the CONDITION table CONDITIONCODE JSON, the following columns are included:
    • EXT_CONDITIONCODE_CODINGSYSTEMID
    • EXT_CONDITIONCODE_ID
    • EXT_CONDITIONCODE_PRIMARYDISPLAY
    • For selected use cases, the coding system name is stored as EXT_CONDITIONCODE_CODINGSYSTEMNAME. For codes marked as **,** the coding system name is available for use.
  • The following rows do not expand in the extension model:
    • Arrays (multiple rows): Expanding these columns adds more than a million rows and changes the grain of the table.
    • Empty: These rows do not contain values. They are not split into multiple text columns.
• Date Cleansing:
  • Date columns in JSON format use the string data type. The data is adjusted to the date format of YYYY-MM-DD HH24:MI:SS. For example:
    • 2014 → 2014-01-01:00:00 (first day of the year)
    • 2022-03 → 2022-03-01:00:00 (first day of the month)
    • 2022-W48 → 2022-11-28:00:00 (first day of week 48)
    • 2019-11-27 → 2019-11-27:00:00
    • 2023-03-07T12:05:00Z → 2023-03-07 12:05:00
    • 2023-11-09T18:00:00.000Z → 2023-11-09 18:00:00
• Enriched columns are frequently used attributes that are added as derived columns to the demographics and preferred_demographics tables:
  • AGE
    • AGE_GROUP_5Y
    • AGE_GROUP_5Y_SORT_KEY
    • AGE_GROUP_10Y
• RACE_ETHNICITY is added to the preferred_demographics table.

Example of the Extended Data Model

For full details of the extended data model, see RWD_Expert_Determination_Extended_Data_Dictionary.xlsx.

Data Domain	Table Name	Key Columns	Additional Attributes	Date Fields
ALLERGY	DW_RWD_EXT_ALLERGY	ALLERGENCODE ALLERGYTYPE CATEGORY CRITICALITY STATUS	REACTIONS	ASSERTEDDATE ONSETDATE RESOLVEDDATE
CLINICAL_EVENT	DW_RWD_EXT_CLINICAL_EVENT	CLINICALEVENTCODE INTERPRETATION SPECIMENTYPE STATUS TYPEDVALUE	MEASUREMENTMETHOD REFERENCERANGE SERVICEPERIOD	ISSUEDDATE SERVICEDATE
CONDITION	DW_RWD_EXT_CONDITION	CLASSIFICATION CONDITIONCODE	CONFIRMATIONSTATUS MANAGEMENTDISCIPLINES PRESENTONADMISSION STATUS TYPE	ASSERTEDDATE EFFECTIVEDATE STATUSDATE
DEMOGRAPHICS	DW_RWD_EXT_DEMOGRAPHICS	MARITALSTATUS	ETHNICITIES RACES	DATEOFDEATH AGE AGE_GROUP_5Y AGE_GROUP_5Y_SORT_KEY AGE_GROUP_10Y
ENCOUNTER	DW_RWD_EXT_ENCOUNTER	CLASSIFICATION FINANCIALCLASS HOSPITALSERVICE STATUS TYPE	ADMISSIONTYPE ENCOUNTERTYPES FACILITYIDS HOSPITALSERVICES LOCATIONS REASONFORVISIT RELATEDPROVIDERS	ACTUALARRIVALDATE DISCHARGEDATE ESTIMATEDARRIVALDATE ESTIMATEDDEPARTUREDATE HOSPITALIZATIONSTARTDATE SERVICEDATE
IMMUNIZATION	DW_RWD_EXT_IMMUNIZATION	DRUGCODE IMMUNIZATIONCODE MANUFACTURER STATUS	DOSEUNIT REASONNOTGIVEN REFUSALREASON ROUTE	IMMUNIZATIONDATE
LAB	DW_RWD_EXT_LAB	INTERPRETATION LABCODE SPECIMENTYPE STATUS TYPEDVALUE	MEASUREMENTMETHOD REFERENCERANGE SERVICEPERIOD	ISSUEDDATE SERVICEDATE
MEASUREMENT	DW_RWD_EXT_MEASUREMENT	INTERPRETATION MEASUREMENTCODE SPECIMENTYPE STATUS TYPEDVALUE	MEASUREMENTMETHOD REFERENCERANGE SERVICEPERIOD	ISSUEDDATE SERVICEDATE
MEDICATION	DW_RWD_EXT_MEDICATION	DRUGCODE STATUS	CATEGORY DOSEUNIT FREQUENCY ROUTE	STARTDATE STOPDATE
MEDICATION_ADMINISTRATION	DW_RWD_EXT_MEDICATION_ADMINISTRATION	EFFECTIVETIME STATUS	DRUGREFERENCE MEDICATIONS ROUTE SITE	Not applicable
ORDER_LIST	DW_RWD_EXT_ORDER_LIST	EMPTYREASON	ORDERENTRIES	RECORDEDDATE
PREFERRED_DEMOGRAPHICS	DW_RWD_EXT_PREFERRED_DEMOGRAPHICS	Not applicable	Not applicable	AGE AGE_GROUP_5Y AGE_GROUP_5Y_SORT_KEY AGE_GROUP_10Y
PROBLEM_LIST	DW_RWD_EXT_PROBLEM_LIST	CONFIRMATIONSTATUS PROBLEMLISTCODE STATUS	Not applicable	ASSERTEDDATE EFFECTIVEDATE
PROCEDURE	DW_RWD_EXT_PROCEDURE	PROCEDURECODE	MODIFIERCODES STATUS	SERVICEENDDATE SERVICESTARTDATE
PROVIDER_DEMOGRAPHICS	DW_RWD_EXT_PROVIDER_DEMOGRAPHICS	Not applicable	Not applicable	Not applicable
TENANT_ATTRIBUTES	DW_RWD_EXT_TENANT_ATTRIBUTES	Not applicable	Not applicable	Not applicable