. 2020 Aug 27;36(2):165–178. doi: 10.1007/s10654-020-00677-6

Table 3.

ETL Stage 3: Defining the control group

This stage is very similar to the previous stage in many ways. The he definition of the code entity remains the same as in the previous stage
ETL stage 3 user inputs
#	Variable name	Data type	Example
1	Code entity	Code Entity of exposure	Name: Type2Diabetes Criteria: Inclusion criteria Exposure type: Incident only Definition: {C10F.11, C10F.00}
2	Combination logic of the exposure	Text: formatted as regular grammar suggested	Type2Diabetes
3	Parsing mode	Categorical with following levels • Strict • Loose	Loose

ETL stage 3 transformation logic
Repeat the following for each eligible patient record marked as ‘potential control’ from previous stage • For each code entity with inclusion criteria if the exposure type is ○ Incident only: find the earliest event of the code entity before patient end date and save details if found, if the event is before patient start date exclude patient and document reason for exclusion ○ Incident or prevalent: find the earliest event of the code entity before patient end date and save details if found ○ First record after cohort entry: find the earliest event of the code entity after the patient start date and before patient end date and save details if found • The parser in the system based on the combination logic supplied does the following each time it encounters a code entity of the patient ○ If all inclusion code entities are found in the patient go to next step, else discard the patient and document reason ○ If the parsing mode is loose, latest event date among the code entities is set as patient’s index date ○ Else if the parsing mode is strict, If and only if the code entities have occurred in the same order as defined in the combination logic, set patient’s index date as date of latest entities’ event date else discard the patient and document reason for rejection • For each code entity with exclusion criteria if the exposure type is ○ Exclude if ever recorded: find the event described by the code entity, if the entity is found exclude patient and record documentation for rejection

ETL stage 3 transformation logic

Transformation logic

Repeat the following for each eligible patient record marked as ‘potential control’ from previous stage

• For each code entity with inclusion criteria if the exposure type is

○ Incident only: find the earliest event of the code entity before patient end date and save details if found, if the event is before patient start date exclude patient and document reason for exclusion

○ Incident or prevalent: find the earliest event of the code entity before patient end date and save details if found

○ First record after cohort entry: find the earliest event of the code entity after the patient start date and before patient end date and save details if found

• The parser in the system based on the combination logic supplied does the following each time it encounters a code entity of the patient

○ If all inclusion code entities are found in the patient go to next step, else discard the patient and document reason

○ If the parsing mode is loose, latest event date among the code entities is set as patient’s index date

○ Else if the parsing mode is strict, If and only if the code entities have occurred in the same order as defined in the combination logic, set patient’s index date as date of latest entities’ event date else discard the patient and document reason for rejection

• For each code entity with exclusion criteria if the exposure type is

○ Exclude if ever recorded: find the event described by the code entity, if the entity is found exclude patient and record documentation for rejection