By Nicola Symmers, Developer.
Edinburgh Cancer Informatics has built a data lineage and exclusion audit framework into the process that produces our OMOP Common Data Model. For every record that could have entered the model, the framework records whether it was loaded or excluded, which source dataset, row and column it came from, and, where it was excluded, the reason for that decision. A user can therefore establish why a particular diagnosis or observation does not appear in the model, which is otherwise hard to determine once several source datasets have been transformed and combined.
What is the OMOP Common Data Model?
The OMOP (Observational Medical Outcomes Partnership) Common Data Model is a standard structure for observational health data, maintained by the Observational Health Data Sciences and Informatics (OHDSI) community (Hripcsak et al., 2015). It specifies a fixed set of tables, among them person, condition_occurrence and drug_exposure, and requires that clinical content is expressed using standard concepts drawn from shared vocabularies. Data held in different hospitals and different national systems, recorded originally in local codes and local formats, can then be queried with the same analytical code.
Edinburgh Cancer Informatics maps Scottish cancer data into this model so that studies can run across sites without patient-level data leaving the hospital. Our OMOP page describes the local mapping and the federated network approach in more detail.
Populating the model requires an ETL process, meaning extract from the source systems, transform into OMOP tables and vocabularies, then load into the model. The transform step applies quality checks, mapping decisions and business rules, and it is the step at which source data can be dropped.
Data Lineage in the ETL process
One of the challenges of working with large-scale health datasets is understanding what happens to data as it moves through an ETL (Extract, Transform and Load) process. Simple questions which come up in the course of research, or initial feasibility assessment, can be surprisingly difficult to answer once multiple data sources have been transformed and aggregated within the new context of an OMOP CDM; for example:
Why is this diagnosis not present in the OMOP CDM? or What was the source dataset, row and column origin for a specific clinical observation recorded in the OMOP CDM? or How much source data was excluded during transformation, and why?
To help answer these questions, we have been developing a data lineage and exclusion audit framework as part of our OMOP Common Data Model (CDM) ETL process.
Why Data Lineage Matters
The OMOP CDM provides a standardised structure for health data, enabling consistent representation and analysis across datasets and organisations. However, transforming source data into OMOP inevitably involves a series of quality checks, mapping decisions and business rules.
Not every piece of relevant source data will be loaded into the CDM. Data items may be excluded because:
- The associated person record is incomplete or implausible.
- Required dates are missing or inconsistent.
- There is no valid standard OMOP concept mapping.
- Duplicate records are identified and suppressed.
- Source data fail predefined data quality checks.
Without a lineage framework, it can be difficult to understand why a particular record is absent from the CDM.
Our goal is to make these decisions transparent and traceable.
Beyond Simple Source-to-Target Mapping
Data lineage is often thought of as a direct link between a source row and a corresponding CDM row. In practice, healthcare data are more complex.
For example, a single hospital episode may contain:
- A primary diagnosis
- Several secondary diagnoses
- One or more procedures
- Information about the clinic visit or admission type, priority, specialty and facility
A Cancer registry record typically contains:
- A primary cancer diagnosis derived from site and morphology codes
- Tumour laterality
- Staging and grading
- Receptor statuses
Each of these, and more, elements may generate its own OMOP record, and each may be treated differently by the ETL. Demographic data, diagnoses, treatments, disease dynamics, comorbidities, death records and other data points may then be linked across multiple source datasets.
Rather than auditing source rows, we therefore audit candidate CDM records. Each candidate record represents a potential record in an OMOP target table and is assigned an outcome:
- LOADED: successfully inserted into the CDM
- EXCLUDED: not loaded into the CDM
Where a candidate is excluded, the reason is recorded.
This allows us to answer not only where a CDM record came from, but also why a source data item did not appear in the CDM.
Recording Exclusion Reasons
A key feature of the framework is the classification of exclusion reasons.
High-level exclusion categories include:
- PERSON_EXCLUDED
- DATA_QUALITY_FAILURE
- NO_STANDARD_MAPPING
- DUPLICATE_RECORD
More detailed reasons are also retained, for example:
- PERSON_NO_SEX
- PERSON_NO_YOB* (*year of birth)
- PERSON_IMPLAUSIBLE_AGE
- EVENT_AFTER_DEATH
- EVENT_BEFORE_BIRTH
- MISSING_EVENT_DATE
- UNMATCHED_SOURCE_CONCEPT
- DUPLICATE_CONDITION
This provides a balance between simple reporting and detailed investigation. Analysts can quickly summarise exclusions by category, while ETTL developers and others responsible for data quality can drill down into the underlying causes.
Supporting FAIR Data Principles
The FAIR data principles aim to make data Findable, Accessible, Interoperable and Reusable. They emphasise the importance of rich metadata, provenance information and transparency regarding how data are created and transformed. Detailed provenance is specifically recognised as an important component of data reusability.
Although FAIR is often discussed in the context of datasets, it also applies to the workflows and processes that generate those datasets. Transparent lineage information helps users understand where data originated, how they were transformed and why certain records were excluded.
Our data lineage and exclusion audit framework contributes to FAIR principles by:
- Providing traceable provenance from CDM records back to source data.
- Recording the reasons behind ETL decisions.
- Supporting reproducibility and transparency.
- Improving users’ ability to assess the fitness of data for a particular research question.
- Enabling systematic investigation of data quality issues.
In short, the framework helps turn ETL processing from a “black box” into a transparent and auditable workflow.
Looking Ahead
While the framework was initially developed to support technical validation and debugging of the ETL, its value extends much further. By making inclusion and exclusion decisions visible, we can provide researchers, analysts and data partners with a clearer understanding of how source data are represented within the OMOP CDM.
As health data infrastructures continue to grow and evolve, transparent lineage and audit processes will become increasingly important for building trust, supporting reproducible research and advancing FAIR data practices.
Ultimately, good data lineage is not just about tracking records. It is about helping users understand the journey from source data to research-ready data, and ensuring that journey is transparent, explainable and reproducible.
Further reading
- Standardised data: the OMOP Common Data Model, Edinburgh Cancer Informatics.
- Hripcsak G, et al. Observational Health Data Sciences and Informatics (OHDSI): opportunities for observational researchers. Stud Health Technol Inform. 2015;216:574-8. PMID 26262116.
- Wilkinson MD, et al. The FAIR Guiding Principles for scientific data management and stewardship. Sci Data. 2016;3:160018. doi:10.1038/sdata.2016.18.
- The Book of OHDSI, OHDSI community.