Research Study Exports

System design

1. 01

   Study record

   The run is driven by a record, not a code change. It names the cohort, the extra forms, and whether imaging is included. An operator starts the job from that record.

2. 02

   Per-job read replica

   The worker creates a Cloud SQL read replica, waits until replication lag is zero, and reads only that replica. The primary keeps serving the product. A matching worker deletes the replica when the job ends, including when the job is failed by hand.

3. 03

   Extract worker

   SQL against the replica writes the source tables out as files. This is the bulk read. It does not build the research schema.

4. 04

   Transform worker

   A second Python pass builds person, condition, drug, measurement and procedure tables. Mentions from the same visit collapse into one record. Extra forms are merged onto an existing table on a key, or appended as their own table, from the study record. Imaging sheets are written only when the record asks for them.

5. 05

   Checks and delivery

   Schema validation and row-quality checks run before anything leaves the account. The delivery worker copies the package to the recipient bucket twice: once as a zip, once as the directory tree, and writes the bucket and key back onto the export row.

6. 06

   Warehouse path

   The long analysis then moved off Postgres into dbt models on BigQuery. The job reads those models instead of grinding the operational database. That is the cut from about half a day to minutes. The replica remains for the extract the warehouse does not hold.

Key decisions

• One Kubernetes job type for every study. Differences live on the study record.
• A form is a merge or an append. It does not get its own transform class.
• Read a replica, never the primary, and delete the replica with the job.
• Split extract from the research schema. Collapse a visit in the transform, not in the source query.
• Move the heavy SQL to dbt on BigQuery once the delivery shape is stable.

Outcome

New studies joined the same job. A run that had taken about half a day finished in minutes, and the primary database stopped carrying the export.