Note · checked against the source SQL
Why a Synthea procedures.csv row can land in Observation or Drug
An engineer building a domain-driven ETL from Synthea asked on the OHDSI forum why some procedures.csv rows end up in Observation and Drug while OHDSI's ETL-Synthea puts procedures in the procedure table. Both behaviours are legitimate. They apply two different rules, and the SQL shows which.
What ETL-Synthea v5.4 does
Each target table reads each source file through the vocabulary map and keeps only rows whose standard concept belongs to that table's domain. In the v5.4 SQL (commit e59d1c7):
| Source file | Loaded into | Domain filter | SQL |
|---|---|---|---|
procedures.csv | procedure_occurrence | target_domain_id = 'Procedure'also requires SNOMED source code, standard concept, not invalid | insert_procedure_occurrence.sql |
procedures.csv | measurement | target_domain_id = 'Measurement' | insert_measurement.sql |
observations.csv | measurement | target_domain_id = 'Measurement' | insert_measurement.sql |
observations.csv | observation | target_domain_id = 'Observation' | insert_observation.sql |
allergies.csv | observation | target_domain_id = 'Observation' | insert_observation.sql |
conditions.csv | observation | target_domain_id = 'Observation'rows whose concept is in the Observation domain | insert_observation.sql |
medications.csv | drug_exposure | target_domain_id = 'Drug' | insert_drug_exposure.sql |
immunizations.csv | drug_exposure | target_domain_id = 'Drug' | insert_drug_exposure.sql |
Read the table by its absences: procedures.csv appears only in procedure_occurrence and measurement. A procedure whose standard concept sits in the Observation or Drug domain is not loaded into either of those tables by ETL-Synthea, because insert_observation reads allergies, conditions and observations, and insert_drug_exposure reads medications and immunizations.
-- insert_procedure_occurrence.sql, v5.4
from @synthea_schema.procedures pr
join @cdm_schema.source_to_standard_vocab_map srctostdvm
on srctostdvm.source_code = pr.code
and srctostdvm.target_domain_id = 'Procedure'
and srctostdvm.target_vocabulary_id = 'SNOMED'What a domain-driven ETL does
It maps each source code to its standard concept with "Maps to" and routes the row to the table that matches the concept's domain_id. Rows that ETL-Synthea filters out are kept, so Observation and Drug counts from a domain-driven run have no counterpart in ETL-Synthea output. An answer in the forum thread confirms that this domain change is expected and points to chapters 4 to 6 of the Book of OHDSI.
How to compare the two without a false alarm
- Count rows per (source file, target domain), not per target table. The same count by table mixes two different rules.
- Decide which rule your network study expects before you reconcile counts. Neither is a bug.
- Record the ETL-Synthea version and the vocabulary release. The domain of a concept can change between releases, which moves rows between tables.
The same method on a table you can re-run
The public benchmark applies it to person, visit_occurrence and condition_occurrence: the official SQL runs on the same files as the baseline, and every difference is a named row count. One command re-runs it.