Referentially-intact EHR seeding · on DuckDB

Ferry

Seed masked EHR slices into your lower environments.

Ferry uses DuckDB as a cross-database compute layer to FK-walk a complete patient record out of PROD, mask the direct identifiers in the Synthea schema on the way through, and load a slice into UAT with masked identifiers and zero broken foreign keys — in seconds.

View on GitHub See a live report →

Verified numbers from a real PostgreSQL → PostgreSQL run against a throwaway PostgreSQL 16 container

0
Dangling foreign keys
verified after every seed
9,312→2,459
Rows, PROD → UAT slice
the record, not the database
7
Direct-identifier categories masked
names · NHI · address · dates · licence · passport · geocodes
2
Interchange formats
FHIR R4 + HL7 v2
1
Command to try it
ferry demo

How it works

DuckDB sits between your two databases and does the hard part.

A naïve WHERE id IN (…) subset leaves dangling foreign keys that break the app the moment it boots. Ferry attaches both environments through DuckDB, walks the foreign-key graph to a fix-point, masks in one vectorised pass, and loads parents-first into an FK-enforced target.

Ferry data-flow architecture PROD (PostgreSQL or DuckDB) is attached read-only into an in-memory DuckDB engine, which performs FK-walk subsetting to a fix-point, deterministic PHI masking, and a topological FK-safe load into UAT or DEV, with zero orphaned foreign keys. The engine also emits FHIR R4 bundles and HL7 v2 ADT and ORU messages. PROD Postgres / DuckDB ATTACH (read-only) DUCKDB ENGINE in-memory · cross-database 1 FK-walk subset · fix-point 2 deterministic PHI masking 3 topological, FK-safe load FK-safe load 0 orphans UAT / DEV FK-enforced export FHIR R4 bundles HL7 v2 ADT · ORU PROD Postgres / DuckDB ATTACH (read-only) DUCKDB ENGINE in-memory compute 1 FK-walk subset 2 deterministic masking 3 topological load FK-safe load · 0 orphans UAT / DEV FK-enforced export FHIR R4 bundles HL7 v2 ADT · ORU
01

Introspect

Read primary and foreign keys straight from duckdb_constraints() or PostgreSQL's information_schema. No hand-maintained schema map.

02

Subset to a fix-point

Downward from the anchored patients for the full longitudinal record; upward to a fix-point for every parent it references — until the slice is referentially closed.

03

Mask deterministically

One vectorised DuckDB pass. Re-keying a patient id applies the same pure function to every FK, so joins survive with no side mapping table.

04

Load & prove

Load parents-first into an FK-enforced target, then count dangling references — expected 0. Export FHIR R4 and HL7 v2.

Features

Everything you need to hand a realistic slice to UAT — and nothing you can't ship.

FK-walk subset with provenance

Fix-point expansion pulls each anchored patient's complete record plus every parent it needs — providers, organizations, payers. The report shows exactly why each table is in the slice.

Join-preserving PHI masking

Deterministic, salted transforms mask the direct identifiers in the Synthea patient record (NHI, names, address, dates). Dates shift per-patient so ages and intervals survive; re-keys cascade so joins never break. Not a full de-identification tool on its own — expert review is still needed before real PHI.

Dirty-source integrity handling

Real prod has orphaned FKs. A pre-flight scan flags them, then the on_orphan policy decides: error aborts, prune cascade-removes, report loads and flags.

FHIR R4 + HL7 v2 export

Emit the masked slice as FHIR R4 bundles and HL7 v2 ADT^A01 / ORU^R01 messages — the same re-keyed patient id in every format, so downstream systems still line up.

DuckDB or PostgreSQL

Point at local .duckdb files or ATTACH real PostgreSQL — DuckDB is the compute layer either way. Ferry reads the source's real keys and loads against the live database.

pip install & ferry demo

One command generates synthetic Synthea data, seeds a slice, exports both formats, and opens a clinical-grade HTML report. No warehouse, no cluster, no setup.

Live example

The clinical-grade seed report Ferry generates — every run, one command.

Not a log line. A self-contained, EHR-styled report that proves what happened: the slice, the masking, and the integrity check, in the visual language of the systems your data came from.

  • Headline KPIs — rows seeded, PHI columns masked, identifiers re-keyed, orphaned FKs
  • Referential-integrity verdict with every FK relationship checked
  • Per-table subset provenance — click a row to see why it's included
  • Before → after PHI samples and the identifier-masking checklist
  • FHIR & HL7 export tiles — 145 bundles, 631 ADT + 631 ORU this run
Open the full report →
Open the full report →

Get started

Two commands to a masked slice and a report.

Ferry installs as an editable Python package and ships a self-contained demo — no external database required. Point it at your own environments with a small ferry.yml.

shell
# uses the pyenv virtualenv `ferry`
$ pip install -e .

# generate → seed → export → open the report
$ ferry demo
ferry.yml
salt: "change-me-per-project"   # seeds masking
source: { duckdb: out/demo/prod.duckdb }
target: { duckdb: out/demo/uat.duckdb }
root: patients
anchor:
  where: >                        # SQL on the root
    EXISTS (SELECT 1 FROM src.encounters e
      WHERE e.PATIENT = patients."Id")
rekey_root: true              # cascade to every FK
export: { formats: [fhir, hl7], out: out/demo }