In short
An ETL pipeline is built in its tabs: you write or generate the SQL scripts, pick the source and target databases, run the chain in the Pipeline tab, then the Quality check tab compares both databases to tell whether the conversion lost anything.
A pipeline is created from the warehouse’s ETL pipelines page. Its scripts refer to the databases by their role — source., target., vocab. — as the overview page explains; this page follows the rest, tab by tab.
The tabs
| Tab | What you do there |
|---|---|
| Pipeline | The run board: the scripts as cards, in the order they chain. This is where you pick the source and target databases, reorder, enable or disable a step, and launch. |
| Scripts | The editor proper, with the query results — and the Generate from the schemas button. |
| Browse schemas | Source and target tables side by side — essential while writing the correspondence. |
| Vocabulary | Generate the vocabulary scripts from a concept mapping project. |
| Quality check | Verify what the run produced: both databases’ figures, and a concept-by-concept account of what was mapped. |
Overview, Readme, License and Versioning sit alongside them, as for every other entity.
Only SQL files are steps
A pipeline’s folder can hold other files — a Markdown README, notes, helper code in Python or R. They travel with the pipeline, but the Pipeline tab only shows the .sql files, and a run chains those alone. A Markdown file opened in the Scripts tab is previewed rather than run.
Writing the scripts
An ETL script is ordinary SQL, which you can write entirely by hand. But most of a conversion to OMOP follows from what Linkr already knows, and two generators write it for you:
- Generate from the schemas, in the Scripts tab — the scripts that load patients, stays and events, worked out from the source’s schema mapping and the target’s.
- The Vocabulary tab — the scripts that load the translation of your local codes, worked out from a concept mapping project.
What they produce are ordinary scripts, which you review and edit; Linkr then spots the ones you touched before regenerating them. It is all in Generating the scripts.
Running, and checking
The Pipeline tab shows the chain as it will run: the source database at the top, the SQL scripts in their order, the target database at the bottom. There you pick the two databases, drag a card to move it, turn a step off with its switch, and Run pipeline launches the lot. Each card also has its own button, to run one script alone while working things out.
Click a node to view details
Progress shows in the toolbar, query by query, with the name of the current script and the elapsed time — on a vocabulary script where one statement can take minutes, that is what distinguishes work in progress from a hang. A failing run stops at the first script in error, and Linkr opens the detail panel directly on the offending script.
Scripts run in the order of the cards, not of their names. When the two drift apart — a 35_… script dragged after a 50_… —, the toolbar’s alphabetical sort button realigns them; it is greyed out when the order already follows the names.
Pausing and stopping are not the same
Pause holds the run without ending it: resuming re-runs the interrupted script from its start, then carries on, within the same run.
Stop ends it. In both cases the statement already sent to the database runs to completion — so part of a script may have been applied. With a half-filled target database, recreating it empty beats re-running on top.
Then comes the Quality check tab, which answers the only question that matters: did the conversion lose anything? It offers two views.
| Status | Source vocabulary | Source code | Description | Source patients | Source rows | Target ID | Expected rows | Target rows |
|---|---|---|---|---|---|---|---|---|
| Missing | REA_LOCAL | BIO_PCT | Procalcitonin | 812 | 2,140 | 44817130 | 2,140 | 0 |
| Fewer | REA_LOCAL | BIO_K | Serum potassium | 3,021 | 48,902 | 3023103 | 48,902 | 46,115 |
| More | REA_LOCAL | BIO_NA | Serum sodium | 3,019 | 48,877 | 3019550 | 48,877 | 49,012 |
| OK | REA_LOCAL | VS_FC | Heart rate | 3,102 | 912,440 | 3027018 | 912,440 | 912,440 |
| OK | REA_LOCAL | VS_SPO2 | SpO2 | 3,098 | 887,310 | 40762499 | 887,310 | 887,310 |
| OK | REA_LOCAL | VS_TEMP | Temperature | 3,087 | 201,544 | 3020891 | 201,544 | 201,544 |
Statistics puts both databases side by side: patients, hospitalizations, unit stays, gender breakdown, lengths of stay, and each table’s row count. An unexpected gap — three thousand patients on one side, two thousand eight hundred on the other — points at a join that silently dropped rows.
Concepts is finer, and works differently from what you might assume: both columns are read from the target database. A source database in its original shape has no comparable columns; once converted to OMOP, however, each table carries both the original concept and the standard one it was mapped to. Comparing the two says exactly how many rows arrived with a source code, and how many actually found a match.
Each concept gets a verdict — Missing, Fewer, More or OK — usable as a filter, and the whole thing exports to CSV. “Missing” is the one to look at first: rows arrived, none were mapped.
The 'Expected rows' column is not redundant
When several source codes point at the same target concept, one code’s row count does not match the expected total. The column therefore shows the sum of every code feeding that concept — otherwise an “OK” verdict next to two different numbers would look like a bug.
Patient counts need a schema on both sides
With no schema mapping on a database, Linkr does not know what a patient is: it can only count rows per table. See Schemas.
Going further
- Generating the scripts — the OMOP load and vocabulary scripts, written by Linkr.
- ETL pipelines — the source, target and vocabulary roles, and sharing a pipeline.
- Databases — creating the empty target database from a schema.
- Schemas — required for the quality check’s patient counts.
- Data quality — checking the target database once filled.