Data quality

Checking what a database contains: the automatically generated rules, the ones you write, and the score that comes out.

In short

A rule set gathers checks to run against a database: Linkr generates some automatically from the tables and the schema, and you add your own in SQL. A scan runs them all and produces a score, kept in a history. Each check answers with a proportion of offending rows, compared against a threshold.

Client Backend

What it is for

A warehouse degrades without warning. A feed changes, a column empties, an identifier stops matching. Nothing crashes: the dashboards keep showing numbers, they are simply wrong.

A rule set turns that watchfulness into something you re-run. Rather than discovering the problem inside an analysis six months later, you catch it by launching a scan.

This is a workspace feature

Rule sets live in the warehouse, not in a project. A database’s quality concerns everyone who uses it: checking it once per database makes more sense than re-checking it in every project.

Three sources of checks

Opening a rule set on a database reveals checks you did not write. They come from two generators, plus your own.

Built-in — on every table

Two checks per table, whatever the database: is the table empty, and what is each column’s missing-value rate. The second is purely informational — it cannot fail, it reports a measurement.

From the schema — if the database has one

Far more interesting, because they know what the tables mean: do the declared tables exist, are there stays with no patient, an end date before a start date, an age outside 0–130, an event before the date of birth.

Custom — yours

A SQL query you write. This is where the rules specific to your site go — the ones nobody can guess.

Schema checks only appear if the database has a schema

This is the most visible difference. With no schema attached, a scan only verifies empty tables and missing-value rates — useful, but blind to what the data means. The schema is attached to the database, not to the rule set: see Databases.

Writing a check

There is no menu of check types. A custom check is a SQL query, written in the Checks tab’s editor, under a single constraint: it must return two columns named violated_rows and total_rows.

SELECT
  COUNT(*) FILTER (WHERE weight_kg <= 0 OR weight_kg > 400)::BIGINT AS violated_rows,
  COUNT(*)::BIGINT AS total_rows
FROM measurement
WHERE weight_kg IS NOT NULL

Linkr does not read your SQL: it reads those two numbers, turns them into a percentage, and compares it to the threshold. All the domain logic is yours.

Three settings accompany the query:

Category — completeness, validity, uniqueness, consistency or plausibility. Used for grouping and charts.
Severity — error, warning or notice.
Threshold — the percentage of offending rows that is tolerated. 0 means zero tolerance; 100 makes the check purely informational.

The Test button runs the current check without launching a full scan — the normal editing loop.

Generated checks can be disabled, not edited

You can change a built-in check’s SQL in the editor to understand what it does, but the change is not kept: it disappears on reload. What is kept is disabling — a disabled check is excluded from both the scan and the score. For a lasting variant, copy the query into a custom check.

Running a scan and reading the result

Run Scan regenerates every check, then runs them one by one. The result is a table: status, check, category, table, percentage violated, severity. Clicking a row opens a detail panel with the violation bar, the counters, the execution time and the exact query.

The score is the proportion of checks that passed among those that apply. Checks with nothing to test — a missing table, zero rows — are excluded.

A check in error lowers the score

A check whose SQL fails — a renamed column, a typo — counts in the total but never among the successes. The score therefore drops as though the data were at fault. Faced with a score falling for no reason, look first for rows with the Error status.

Every scan is recorded in a history you can browse, replay on screen, and clear. A single check’s Test result is not: only full scans are kept.

You cannot see the offending rows

A check reports only two numbers: how many rows are offending, out of how many. There is no view of the offending records.

To examine them, copy the query from the detail panel and run it in a SQL script collection or in the IDE, replacing the counting with a SELECT *. This is the limitation most likely to catch you out.

What to know before launching

A scan can be long. Checks run in sequence, and the missing-value rate produces one check per column: a wide database generates hundreds. Nothing is cached, every scan starts over.
A database is essential. It is optional when creating the rule set, but without one there is nothing to run.
Checks always run in the browser, including in server mode. Only the rules and the history are stored on the server.
The history keeps the full report of every scan. On a wide database with frequent scans it grows: that is what the Clear button is for.

A rule set is an entity like any other: it exports, versions in a git repository, and publishes to the catalog. Its export contains the custom checks — never the results, never the data.

That is what lets a proven set of OMOP quality checks travel between sites and apply as-is to another database, provided it carries the same schema. See Entities and sharing.

Going further

Databases — attaching a schema to unlock the meaning-aware checks.
Schemas — what Linkr needs to know to verify a consistency.
SQL script collections — to examine the rows a check flags.
ETL pipelines — which have their own source/target comparison.