Synthmas — derived tables¶

Three CDM tables in this walkthrough aren't a 1-to-1 map of any source table. They're built from joins, cross-table lookups, or by drawing from two source tables at once — which is where the engine's table-level rules and multi-source row-construction earn their keep.

`death` — built from a `LeftJoin`¶

The OMOP death table needs a person_id, a death_date, and optionally a cause of death (a SNOMED concept). In Synthmas, the death date lives on patients.csv (column deathdate, often null) and the cause lives in conditions.csv (a condition row whose start matches the patient's deathdate).

We can't draw a single arrow for this — we need rows from both tables, joined by the matching date. That's a table-level LeftJoin, placed on the CDM death table's Comments field:

logic: LeftJoin(@patients.csv, @conditions.csv, [:id, :deathdate], [:patient, :start])

Read it as: "left-join patients.csv and conditions.csv where the patient's (id, deathdate) matches the condition's (patient, start)". LeftJoin keeps every patient row even when no matching condition exists — patients who haven't died still appear in the join result, just without a condition. The downstream death_date is NULL filter then drops them naturally (the field mapping skips rows that have no value).

Once the join is in place, the field-level rules on death look just like ordinary field-to-field mappings:

person_id:
  logic: GetCDMTableId(@person)
  logic: AssertNot(NULL)
death_date: <-- patients.csv:deathdate                     # field-to-field, no rule
cause_concept_id:
  logic: AsString()
  logic: VocabMap("SNOMED")                                # the condition's code

`observation_period` — cross-CDM-table lookup¶

The observation_period table records the time span during which the engine's view of the person was valid. There's no source table that captures it directly; it's derived from the source patients.csv file's earliest and latest event dates, with a fallback when the "latest" date is missing.

The interesting field is observation_period_end_date:

logic: Selector(:stop, "<>", NULL, :stop, :start)

If the source has a non-null :stop, use it; otherwise reuse :start (the period collapses to a single day, which is the right answer for a patient with no recorded follow-up).

The person_id field uses the by-now-familiar pattern:

person_id:
  logic: GetCDMTableId(@person)
  logic: AssertNot(NULL)

Because GetCDMTableId(@person) records an implicit dependency on the person table, the engine guarantees person is fully loaded before observation_period runs.

The table also declares a virtual field for traceability:

observation_period:
  field: observation_period_source_value

…and the observation_period_id rule writes into both the virtual field (via SaveToVirtualField) and the integer ID:

observation_period_id:
  logic: SaveToVirtualField(:observation_period_source_value)
  logic: AutoGenId()

Each row gets a unique CDM observation_period_id plus the source identifier preserved in the virtual field for downstream reference.

Automation alternative (Paid / AUTO)

This row-wise mapping is the Free-tier way to build observation_period. With a Paid licence carrying the AUTO feature you can instead let the engine derive it — one OMOP-correct period per person from the span of their clinical events — by setting automation { post { create observation periods: true } }. The same toggle also builds the condition_era / drug_era / dose_era tables, and all four now run on a CSV target as well as a database. A demo licence is available on request if you'd like to try it on this dataset.

`payer_plan_period` — two source tables, one CDM row¶

Synthea exposes the insurance picture across two files. payer_transitions.csv has one row per (patient, payer, year-range) — exactly the shape OMOP's payer_plan_period expects, so it drives row creation. payers.csv is the small lookup table that gives each payer UUID a human-readable name ("Aetna", "Medicare", "NO_INSURANCE", …).

The challenge is that the row driver and the concept-id lookup source aren't the same table. Just registering two table-to-CDM arrows isn't enough — the engine would run the two sources as independent row generators, producing one cohort of rows with patient/dates but no payer info, and another cohort with payer info but no patient/dates. To get a real composed row, the CDM table needs an explicit join:

# On the payer_plan_period CDM table Comments:
logic: LeftJoin(@payer_transitions.csv, @payers.csv, [:payer], [:id])

That tells the engine: use payer_transitions as the driving table; for each driving row, look up the matching payers row on payer_transitions.payer = payers.id; combine both into the row that flows downstream into the field rules. LeftJoin keeps the transitions row even if no payer match is found (in which case the payer-derived fields land as NULL); a plain Join would drop the row entirely.

With the join in place, the field rules split cleanly along the two source sides:

# From the payer_transitions side of the join:
patient     <-- payer_transitions:patient
              logic: GetCDMTableId(@person)
              logic: AssertNot(NULL)
start_year  <-- payer_transitions:start_year
end_year    <-- payer_transitions:end_year

# From the payers side of the join:
name        <-- payers:name
              logic: UsagiMap("payers.csv")        # → payer_concept_id
name        <-- payers:name
              logic: Truncate(50)                  # → payer_source_value

The UsagiMap looks up the payer name in etc/etl-engine.d/usagi/payers.csv and returns the matching SOPT concept id (280 for Medicare, 289 for Medicaid, 327 for the named commercial payers, 436 for self-pay, and so on). The starter Usagi file shipped with the demo covers all ten payer names Synthea generates; extend or replace it for your own payer set.

The payer_plan_period_id field uses the same pattern you saw on observation_period:

payer_plan_period_id:
  logic: AutoGenId()

The other concept-id fields — plan_concept_id, stop_reason_concept_id, plan_source_concept_id — have no equivalent column in payer_transitions.csv, so they're left unmapped and default to NULL/0. That's the honest answer for Synthea's data; if your real source has plan and stop-reason fields, add the rules in your own RiaH.

The pattern this section introduces: one CDM table fed by two source tables, where one drives the rows and the other contributes field values through an automatic foreign-key join. The same shape shows up any time a fact table needs a label from a dimension table — common in real EHR ETLs.

Range joins — matching on more than equality¶

The joins above all match each key pair by equality. An optional comparison array — one operator per key pair ("=", ">", ">=", "<", "<="), AND-ed together like an SQL ON clause — lets a join match on order, not just equality:

# a.person_id = b.person_id AND a.date >= b.start AND a.date <= b.end
Join(@a.csv, @b.csv, [:person_id, :date, :date], [:person_id, :start, :end],
     ["=", ">=", "<="])

The "=" pairs drive the fast index lookup; the comparison pairs filter the matched candidates — e.g. attaching the row whose [start, end] window contains an event date (an equality key on the person plus a date range).

A join with no "=" pair at all is a pure-range join: with no key to index on, the right table is scanned and the comparisons do all the matching (useful for bucketing — e.g. a value falling between a lookup table's low/ high bounds). Omit the operator array entirely and the join is plain equality, exactly as before. Comparison operands must be field references on both sides — a constant bound is a filter, not a join condition. LeftJoin takes the same operator array (every left row is still preserved; the comparisons decide which right rows match).

`cdm_source` — a single static row¶

The CDM cdm_source table holds metadata about the data source itself — typically one row, hard-coded. InsertRecord is the right tool:

logic: InsertRecord(
  cdm_source_name="Synthetic Massachusetts",
  cdm_source_abbreviation="Synthmas",
  cdm_holder="Rook IT ApS",
  source_description="Test translation of the Synthmas dataset",
  source_documentation_reference="https://mitre.box.com/shared/static/aw9po06ypfb9hrau4jamtvtz0e5ziucz.zip",
  source_release_date='2023-01-13',
  cdm_release_date='2023-06-30',
  cdm_version="5.4",
  cdm_version_concept_id=756265,
  vocabulary_version="v5.0 31-MAY-23")

Every non-nullable field must be supplied. The values are pure metadata — no source data flows in.

Splitting an ETL with `StoreIndexMap` (Paid tier)¶

For real ETLs that span more than one Rabbit-in-a-Hat file, the StoreIndexMap and StoreSequence table rules persist (source-key → CDM-id) maps and global sequences to disk. A second engine run, in a different Rabbit-in-a-Hat file, can then resolve the original source UUIDs back to the CDM integer IDs without re-loading either table.

location:
  logic: StoreIndexMap([:location_source_value], [:location_id])

visit_occurrence:
  logic: StoreIndexMap([:visit_occurrence_source_value], [:visit_occurrence_id])

These rules require a Paid licence — they're the foundation of multi-RiaH ETLs, which is firmly an enterprise pattern. The Synthmas walkthrough does not use them; everything in the bundled hat file is single-pass and works on the Free tier.

For more on the multi-RiaH pattern, see Folder Layout → Splitting an ETL.

Recap¶

death, observation_period, and payer_plan_period are all derived CDM tables — none of them comes from a single source table. The patterns they introduce:

LeftJoin at the table level to construct a CDM table from two source tables matched on shared keys.
Two table-to-CDM arrows pointing at the same CDM table, composed by an explicit LeftJoin at the CDM-table level so one source drives rows and the other contributes field values — not implicit; registering two arrows alone produces two parallel row streams.
GetCDMTableId(@person) + AssertNot(NULL) for cross-CDM-table foreign keys with a hard-fail on miss.
Selector for null-fallback (use :stop if non-null, otherwise :start).
UsagiMap on a field value that came in via a joined-in source table — e.g. payer name from payers.csv joined onto a payer_transitions.csv row.
InsertRecord for static metadata rows.
StoreIndexMap for cross-RiaH ETL stitching (Paid tier; not in the bundled Synthmas RiaH).

That's the whole walkthrough. The closing Useful patterns page collects the language-level techniques we built up — vocabulary lookups, Usagi maps, conditional chaining — in one reference for when you're writing a new mapping and want a reminder of the toolkit.

Synthmas — derived tables¶

death — built from a LeftJoin¶

observation_period — cross-CDM-table lookup¶

payer_plan_period — two source tables, one CDM row¶