Synthmas — the person table

The person table is the most central table in the OMOP CDM — practically every clinical event references it via person_id. We'll map Synthmas's patients.csv into it, and along the way introduce:

• vocabulary lookups via Map with a default plus an AssertNot on the default,
• date deconstruction with YearFromDate / MonthFromDate / DayFromDate,
• ID generation with AutoGenId (recap from location),
• and a cross-table lookup with GetCDMTableId.

Field overview

The figure shows the field-to-field arrows for the person table. A few fields are not in the figure for space reasons (the various *_source_concept_id columns and provider_id/care_site_id); none are mapped in this walkthrough — Synthmas's gender, race, and ethnicity values are all strings, not concept ids, so the *_source_concept_id slots stay null.

Vocabulary lookups: Map + default + AssertNot

Three fields need vocabulary lookups:

• gender — M / F → gender_concept_id.
• race — white / black / asian / native → race_concept_id.
• ethnicity — nonhispanic / hispanic → ethnicity_concept_id.

The pattern is the same for all three. Use a Map with a default value, then chain an AssertNot that fails the row when the default has been used. This way:

• Values you expected get mapped to the right concept id.
• Values you didn't expect end up with the default, fail the assert, and get logged — so you know to add them to the Map.

Gender:

Map([
  ["M", 8507],
  ["F", 8532]
], 0)
AssertNot(0)

Race:

Map([
  ["white", 8527],
  ["black", 8516],
  ["asian", 8515],
  ["native", 8657]
], 0)
AssertNot(0)

Ethnicity:

Map([
  ["nonhispanic", 38003564],
  ["hispanic", 38003563]
], 0)
AssertNot(0)

For each of the three sources, there is also a parallel arrow to the matching *_source_value field with no rule — that preserves the original string so it survives into the CDM.

Why default + assert, not just Map(...)

Without a default, Map does not raise an error on an unmapped input — it passes the original value through unchanged and sets successfulMapping = false.

Inside a || chain that's the cue for the next conditional rule to fire. Outside a || chain it's a footgun: an unmapped STRING like "unknown" flows on into a CDM INT field, which either errors at the load boundary or — depending on the driver's strictness — inserts garbage. Big KA-BOOM.

Default + AssertNot(default) short-circuits both failure modes:

• The default sentinel (here, 0) is a value of the correct type, so type checks pass.
• AssertNot rejects exactly that sentinel and logs the row, so unmapped values land in the table log instead of the CDM table.

Use this pattern any time Map is the last rule in a chain.

Date deconstruction

Synthmas has birthdate as a date, but the CDM person table wants it split into year_of_birth (mandatory), month_of_birth, day_of_birth, and birth_datetime (the original date).

birth_datetime is a straight field-to-field arrow with no rule — both sides are dates.

The other three need YearFromDate, MonthFromDate, and DayFromDate:

YearFromDate()

Each rule sits on its own field-to-field arrow from birthdate to the matching CDM field. They take no parameters.

person_id and location_id

person_id follows the same pattern as location_id:

• Map the UUID id from patients.csv to person_source_value (string-to-string, no rule).
• Add an AutoGenId on the CDM person_id field.

location_id is more interesting. The CDM person.location_id is a foreign key into the CDM location table — and the location table was populated with new AutoGenId-issued integers, not the source UUIDs. The source UUID was preserved in location_source_value.

So to set person.location_id, we look up in location by location_source_value and return the matching location_id. That's exactly what GetCDMTableId does — and it defaults to using <table>_source_value as the lookup key and <table>_id as the value, which is the convention we followed:

The arrow is patients.csv:id → person.location_id, with the rule:

GetCDMTableId(@location)

Because the rule references the CDM location table, the engine records an implicit dependency: person cannot be transformed until location has finished loading.

Recap

The person table now has:

• Map(...) + AssertNot(0) for each of gender_concept_id, race_concept_id, and ethnicity_concept_id, with the unmapped rows logged and skipped,
• *_source_value field-to-field arrows that preserve the original string,
• birth_datetime as a direct field-to-field arrow,
• YearFromDate / MonthFromDate / DayFromDate on the three integer date components,
• AutoGenId on person_id, with the UUID preserved in person_source_value,
• GetCDMTableId(@location) on location_id, which also expresses the load-order dependency.

Next: Useful patterns — vocabulary lookups against the OMOP standard vocabulary, Usagi maps, and conditional chaining.