Skip to content

CSV / PostgreSQL parity

The CSV CDM target and the PostgreSQL CDM target produce the same data when you run the same pipeline against both with the same input. Not "approximately the same" — every row in every CDM table matches by value, in the same order, with the same rejected counts. The difference is the destination, not the data.

This page exists because that wasn't always true. CSV used to be excluded from the orchestrator's supported-backends list (the original argument was "no global state to manage" — the engine wrote per-table files lazily, with no DDL phase and no canonical-header guarantee). Engine 1.4.0 and 1.4.1 closed that gap; CSV is now first-class.

Why it works now

Four engine fixes, in order.

Engine commit What it does Why it matters
a43b83f (1.4.0) create omop cdm: once now writes each CSV CDM file with the canonical OMOP header on day-zero. Multi-writer tables (e.g. cost, written by both synthmas-claims and synthmas-cost-drugs) see the full column set regardless of write order. No more "field X is missing in table" on first-writer-locked schemas.
f928368 (1.4.0) clean omop data: true now rewrites each CSV file to its canonical header on day-N (SQL TRUNCATE semantics: schema preserved, body empty). Day-N runs preserve the canonical schema instead of falling back to lazy first-write. The orchestrator can pin one cleanup config for both CSV and RDBMS.
4519128 + 8131819 (1.4.1) CSVTable::loadReadTable skips virtual fields in header validation. Virtual fields declared in RiaH table comments (e.g. field: visit_occurrence_source_value used only as a StoreIndexMap key) don't trip the parse-time column-presence check against the canonical OMOP header.
213521d (1.4.1) CSVTable::loadWriteTable opens the existing file with out \| app instead of out \| ate, so the canonical header laid by create omop cdm / clean omop data survives the first writer's append. The original out \| ate open silently truncated the file (per the C++ standard, out alone truncates). Without this fix, the first writer landed a data row at offset 0 and the second writer's read-side check then tripped.

The orchestrator side was simplified at the same time: the CSV pre-automation special-casing went away (dispatcher.py no longer has a CSV branch in cleanup_scope, no csv_cdm_cleanup call, no orchestrator-side short-circuit in _run_step). Both backends now emit the same etl.conf shape — the only difference is the cdm database.driver string.

How to verify

The synthmas demo's parity comes out at 2,015,051 rows across 16 CDM tables, identical CSV vs PG. Six PG runs back-to-back also produce identical output to each other (the older "PG write path is non-deterministic" report is now resolved).

The verification snippet is in Run on PostgreSQL → Confirm CSV / PostgreSQL parity — pass it the two pipeline_run ids (one CSV, one PG) and it prints a per-table delta column that should be +0 on every row.

Pin engine 1.4.1+

The orchestrator's settings page lets you set a per-pipeline engine image. For CSV envs, pin rook-it.com/etl-engine:1.4.1 or newer. Earlier engines have known correctness gaps on multi-writer CDM tables — the pipeline will abort with "field … is missing in table" on synthmas-cost-drugs (and similar steps in your own pipelines).

For RDBMS-only envs, the version pin matters less for correctness, but keeping CSV and RDBMS envs on the same engine image makes parity testing trivial — same image, same pipeline, both backends, identical output.

What stays asymmetric

Three things still differ between the backends. None of them affect CDM table row counts; they're all about what happens around the data.

  • Vocabulary loading on CSV is orchestrator-side (the orchestrator extracts the Athena vocab zip into csv_cdm_dir before the engine spawns). The engine's populate vocabulary path INSERTs into vocab tables via SOCI and is RDBMS-only. End-state is the same — pre-populated CONCEPT.csv et al. — just a different code path.
  • Era + preceding-visit-IDs post-automation is RDBMS-only. The orchestrator forces those flags off on CSV regardless of the pipeline's toggle (condition_era, drug_era, dose_era, and visit_occurrence.preceding_visit_occurrence_id stay empty / NULL).
  • Cascade re-run cleanup on CSV relies on the canonical header pre-automation already left in place — there's no CSV equivalent of the RDBMS rdbms_cdm_cleanup per-table DELETE-pivot. For a true re-clean of cascade tables on CSV, run the full pipeline instead of the cascade.

These are documented in the etl.conf template reference and in the CSV-side-of-the-house design notes; they're stable, intentional asymmetries — not parity gaps.