Run on PostgreSQL

The RDBMS target is the production-shaped OMOP CDM: FK constraints enforced at write time, downstream OHDSI tools (Atlas, HADES, the Data Quality Dashboard) can connect to it as-is. Setup is a bit more work than CSV (you need a postgres instance + a schema), but the CDM-side output is identical row-for-row to the CSV run on engine 1.4.1+ — the difference is the destination, not the data.

Prepare PostgreSQL

You need PostgreSQL ≥ 14. On the postgres host (or any client that can reach it):

psql -h <pg_host> -U postgres -d postgres

-- A dedicated user is best practice; for the demo the postgres super
-- user works fine. Whichever you use, the role needs CREATE on the
-- target schema.
CREATE SCHEMA synthmas AUTHORIZATION postgres;

-- (Optional) verify
SELECT current_schema(), current_user;

The orchestrator doesn't create the schema — you do. It does create every table inside the schema + indexes + constraints during pre-automation.

Create the environment

Pipelines → Synthmas → Environments → New:

Field	Value
Name	pg-demo
Source DB backend	CSV (same as before — Synthea source)
Source set	synthea-1k
CDM DB backend	PostgreSQL
Host / Port / Database	your postgres connection
User / Password	the role above
Schema	synthmas
Vocab cache size	10000
Max DB connections	50 (lower if your postgres is small)

Save. The orchestrator stores the connection (the password is encrypted at rest with your login password; you'll be prompted to re-enter it once per session before runs).

Run it — first time

▶ Run pipeline. Pre-automation now does real work:

1. Build CDM (creates every OMOP table in your synthmas schema). Fast — ~1 second.
2. Populate vocabulary (loads the Athena bundle into the concept, concept_relationship, etc. tables). ~10 minutes on a typical box for the full Athena bundle, dropping indexes first for bulk-load speed.
3. Add constraints + indexes back. Another ~10 minutes. The pre-auto modal shows "Indexes: X of 17" as it goes.

Total pre-automation: ~20 minutes the first time. Subsequent runs skip the load entirely — see Settings → environment → CDM state; once vocab is loaded for an env, the orchestrator records it and re-runs short-circuit pre_auto's vocab phase.

After pre-auto, etl_map steps + post-auto run the same as on CSV, just faster per step thanks to indexed vocab lookups. Total wall-clock for a first run: ~30–35 minutes; subsequent runs (after vocab is cached): ~5 minutes.

"Reset" — wiping postgres externally

If you DROP SCHEMA synthmas CASCADE; CREATE SCHEMA synthmas; (e.g. to re-test from scratch), the orchestrator's cache still thinks the schema + vocab are loaded. Click Edit env → CDM state → Reset CDM state to clear the cache; the next run will recreate the schema + reload vocab.

Confirm CSV / PostgreSQL parity

On engine 1.4.1+, the two runs you just did should produce row-for-row identical CDM tables. Spot-check it from the orchestrator container:

docker exec etl-orchestrator-orchestrator-1 python <<'PY'
from collections import defaultdict
from app import create_app
from app.models import PipelineRun, StepRun, StepRunTable
app = create_app()
with app.app_context():
    # Replace with your two PipelineRun ids — one CSV, one PG.
    rids = (193, 194)
    per_run = {}
    for rid in rids:
        sr_ids = [sr.id for sr in StepRun.query.filter_by(pipeline_run_id=rid).all()]
        per = defaultdict(int)
        for srt in StepRunTable.query.filter(StepRunTable.step_run_id.in_(sr_ids)).all():
            per[srt.table_name] += srt.rows_added or 0
        per_run[rid] = per
    tables = sorted({t for d in per_run.values() for t in d})
    print(f'{"table":<24}', '  '.join(f'{r:>10}' for r in rids), '  delta')
    for t in tables:
        a, b = per_run[rids[0]][t], per_run[rids[1]][t]
        print(f'{t:<24}  {a:>10}  {b:>10}  {b-a:>+6}')
PY

Every delta column should be +0. A typical synthmas demo lands at 2,015,051 total CDM rows across 16 tables, identical CSV vs PG. If deltas show up, check that both runs used engine 1.4.1+ — older engines have known CSV-side correctness gaps (see CSV / PostgreSQL parity).

→ Next: Reading the run-mode UI.