Decomposing FHIR JSON into a warehouse, and typed HL7v2-to-FHIR mapping — May 14, 2026
Forge Core — FHIR JSON to normalised warehouse tables
Brady's Forge Core takes raw FHIR JSON streamed into a cloud warehouse and decomposes it into normalised dbt models, one table per sub-structure, on the premise that working with JSON in its raw state in a warehouse is a nightmare.
The load-bearing trick is the idx column, a positional path from the root encoding both depth and array position — without it you cannot join arrays inside arrays back to their parents correctly, and with it every Forge dataset joins the same way.
The engine-specific part is only SQL syntax, so an adapter pattern covers BigQuery, Snowflake, Databricks and Redshift over shared decomposition logic. Brady had Claude Code work through the algorithm and generate a formal proof that for any valid JSON it is deterministic, lossless and self-terminating — so it can't loop forever or silently drop a key.
Avalon Public — the dbt-to-OMOP layer
Avalon Public is the open source dbt layer on top, mapping Forge's tables into OMOP CDM models, with a forge_join macro generating the parent-child joins programmatically and a contract file declaring exactly which sub-tables and columns each OMOP model depends on — a machine-readable interface between the two layers.
Vocabulary is the part that isn't done: concept IDs are still zeros in places. Brady was frank that mapping SNOMED, LOINC and RxNorm is tougher than you'd imagine at first, with the real gap being real-time updates from volatile vocabulary sources.
Merlin, the commercial front end, turns a question into BigQuery SQL you can copy out and run yourself — the whole chain stays auditable.
The performance objection — and where SQL on FHIR fits
Flattening everything into thousands of tables means real queries need dozens of joins. When my team ran that experiment it became extremely slow past a certain point.
Brady's counter: breaking data out into individual columns is precisely what lets the query engine work optimally. His framing is that these are two flavours of one problem — batch analytics over billions of records belonging in a warehouse, while real-time clinical transaction queries and alerting are what SQL on FHIR is good at.
His proposed bridge — have Forge emit SQL on FHIR-compatible queries so the same logic runs in both — didn't get taken up.
SQL on FHIR already runs at scale with Spark and BigQuery implementations. Flattening conventions are all it really is. Give it a proper look.
Nikolai pressed separately on how vocabulary drives routing — what decides that one observation becomes an OMOP measurement and another stays an observation, or what happens to a condition arriving as ICD-10 — and the answer stayed at the level of concept joins and macros before Arjun called it too big a topic to open.
HL7v2-to-FHIR mapping when everything is code
Ilia's pipeline starts from a rejection of the usual pitch — send us your messages and we'll map everything — which he thinks plainly doesn't work: you still map by hand, and sometimes you need an if statement in a place a DSL won't let you put one, so everything is code.
What makes it work for agents is that it's typed end to end, with generated TypeScript types for both v2 segments and FHIR R4, so a wrong status is a compile error and the agent gets a tight feedback loop. Message quirks are handled by config-driven preprocessors like swap-if-reversed that keep the mess out of the converters.
Unmapped codes go to a triage queue with suggested LOINC matches, and a confirmed mapping is stored as a FHIR ConceptMap per sender.
You accumulate an improving database of concept maps instead of adjustments living in a fifteen-year-old codebase that three people hack on.
The best moment — the agent declining a shortcut
Given a message with no PV1-19 to identify the visit, the agent rejected PID-18 as a substitute because it would collide when the same patient's encounters were loaded, and recommended deferring the message for a human instead.
Shamil's design pushback
Build converters around message structure rather than message type — one structure serves many types and you cover more for less. And use HL7v2 profiles, which say precisely what must and shall appear, to pre-validate messages that arrive malformed.
Nikolai's response was to invite an issue or a pull request against the open source parser. Arjun's caveat on the whole approach: the mechanical part may now be done, but lab codes are exactly where you care about the last two percent, so a human still confirms.