Three related operations
ETL means extract, transform and load. Extraction obtains data from authorised sources; transformation applies preparation rules, and loading incorporates them into the destination. These operations may be organised differently according to the technology. In ELT, transformation occurs after loading, using the destination system’s capabilities.
Transformation is not arbitrary alteration
Transformations may normalise dates, relate codes, filter records or identify duplicates. Each rule needs justification and testing. Automatically changing an unknown category to a known one can hide a source problem. Data quality in OSH requires exceptions to be visible and the ability to review them to be maintained.
Preserving relationships and history
A load must respect links between people, workplaces, jobs and actions. It must also define what happens if data change or are deleted at source. Without these rules, an update may break history or duplicate a measure. Maintaining identifiers and source references helps investigate discrepancies and reconstruct the processing performed.
Controlling failures and retries
The process needs to know which batches completed, which failed and which stopped partway through. Repeating a load should not accidentally multiply records. Checks of counts, required fields and consistency are established alongside recovery mechanisms. A successful execution message does not itself demonstrate that loaded content represents all expected data.
Example using training records
In a hypothetical example, several workplaces send records with different date formats. The process identifies each source’s format, validates courses and people, and separates ambiguous cases for review. Output distinguishes accepted, rejected and pending records. Converting every value using a single assumption could change dates and produce incorrect conclusions about compliance.
Protecting the entire journey
Temporary areas, intermediate files and error logs may also contain personal information. Permissions, retention and access controls apply throughout the journey, not just at the final destination. Documentation should make the source, rules, version and loading time understandable. ETL provides technical preparation; preventive interpretation needs responsible people who understand the process.
