Three Severity Levels, Not Pass/Fail — and Why Severity Decides Where the Rule Lives

Most data quality systems don't die of bad checks. They die of a missing field in the rule model: the severity. A missing country code in three out of 80,000 rows blocks the nightly load, someone switches the check off "temporarily", and from that moment everything runs unchecked. Steering data quality with severity levels instead of binary pass/fail escapes that trap. And severity can do more: it decides whether a rule belongs in the target schema as a constraint or in the pipeline.

Key takeaways:

• Binary pass/fail ends in one of two dead ends: either a triviality blocks the whole load, or everything passes and nobody reads the findings.
• Three levels are enough: an error blocks, a warning is visible without blocking, an information merely documents.
• Severity is a routing decision: only an error rule is a candidate for a CHECK or UNIQUE constraint in the target schema — warnings and information stay in the pipeline.
• Promoting has a price: when a warning becomes an error, the existing data suddenly has to be clean. NOT VALID and VALIDATE CONSTRAINT make the transition controllable.
• The limits can be named precisely: cross-row, cross-table and time-based rules are beyond what a constraint can do. Those belong in the pipeline, permanently.

Prerequisite: Postgres as the example engine and the framework from the sub-hub Data Quality Checks with SQL with the shared error table, the rule configuration dq.check_rule and the quality gate. The principle itself is engine-neutral and applies just as well to SQL Server or any other system with constraints.

Why binary fails

A binary check system knows exactly one question per record: pass or fail. That sounds clean and leads, in practice, into one of two dead ends.

Dead end one: everything that fails blocks. Then a missing email address stops the same load as a duplicate customer number. The process stands still, the business day waits, and the person on call makes a decision at three in the morning that actually belongs in a rule review: they switch the check off. Not out of carelessness, but because the system offers them no other gradation.

Dead end two: nothing blocks, everything is merely logged. Then a findings table fills up that somebody still reads in the beginning. A few weeks later thousands of entries sit there, important ones next to trivial ones, and because the system itself doesn't say which of them demand action, at some point nobody looks anymore. The check keeps running and is dead anyway.

Both reflexes are understandable. The problem is not that people handle the system badly. The problem is that a binary model withholds the one piece of information that should steer the handling: how bad is this finding?

The three levels

Three severity levels answer exactly that question, and you rarely need more than three. The pattern is not new: syslog levels and linters have known it for decades. What makes it interesting is what each level concretely triggers in the ETL process:

• Error (E) blocks. A record with an error finding does not pass the quality gate and does not reach the target layer. Error is the right level for everything the load would fail on at the target anyway: missing mandatory values, duplicate keys, unknown references.
• Warning (W) is visible but does not block. The record flows on, the finding stays logged. That covers the largest share of real-world data problems: things that are odd, unclean or worth a look, but must not hold up the process.
• Information (I) merely documents. No pressure to act, no blocking, pure awareness, for instance a missing phone number whose frequency you want to observe.

In the framework's rule model, the severity (some rule models call the field "criticality") is a column of the configuration table, with a strict default and a CHECK on itself:

,severity   char(1) NOT NULL DEFAULT 'E'
,CONSTRAINT ck_check_rule_severity  CHECK (severity IN ('E', 'W', 'I'))

The default E is a deliberate decision: whoever writes a rule means it seriously at first. Downgrading to a warning is an active, documented step. The opposite default would silently turn new rules into mere observers. Five rules show the three levels in the configuration table's format:

INSERT INTO dq.check_rule
    (check_type,   schema_name, table_name, id1_column,    check_column,   where_clause,           severity, message)
VALUES
    ('constraint', 'staging',   'customer', 'customer_id', 'country_code', 'country_code IS NULL', 'E', 'country code is mandatory in the target schema')
   ,('constraint', 'staging',   'customer', 'customer_id', 'age',          'age IS NULL',          'E', 'age is mandatory in the target schema')
   ,('constraint', 'staging',   'customer', 'customer_id', 'age',          'age < 18',             'W', 'customer under 18 - needs business review')
   ,('constraint', 'staging',   'customer', 'customer_id', 'email',        'email IS NULL',        'W', 'email missing - needs business review')
   ,('constraint', 'staging',   'customer', 'customer_id', 'phone',        'phone IS NULL',        'I', 'phone number not provided - for information only');

The information rule deliberately targets a field that is optional from a business point of view: a missing phone number is not a defect, but a value whose rate you want to know. On age, by contrast, two rules with different levels sit side by side: if the age is missing entirely, that is an error, because the target treats the column as mandatory. If it is merely conspicuously low, that is a warning. The choice of level is a business statement, not a technical one.

What the levels mean in practice is shown by a run against seven demo rows (one clean, one per severity, and three combinations). The framework's staging table is extended by the optional column phone for this:

INSERT INTO staging.customer (customer_id, country_code, email, age, phone) VALUES
    (1, 'DE', '[e-mail]', 44,   '[phone]')   -- clean
   ,(2, NULL, '[e-mail]', 33,   '[phone]')   -- E: country code missing
   ,(3, 'AT', '[e-mail]', 17,   '[phone]')    -- W: under 18
   ,(4, 'CH', '[e-mail]', 52,   NULL)               -- I: phone number missing
   ,(5, NULL, '[e-mail]', NULL, '[phone]')   -- E+E: country code and age missing
   ,(6, NULL, '[e-mail]', 16,   '[phone]')   -- E+W: country code missing, under 18
   ,(7, NULL, NULL,            NULL, NULL);              -- E+E+W+I: everything at once

SELECT dq.fn_run_checks('staging', 'customer') AS total_findings;  -- 11

The runner writes the severity counters back into the source table, one per record and level:

customer_id	country_code	email	age	phone	sys_error	sys_warning	sys_info
1	DE	[e-mail]	44	[phone]	0	0	0
2		[e-mail]	33	[phone]	1	0	0
3	AT	[e-mail]	17	[phone]	0	1	0
4	CH	[e-mail]	52		0	0	1
5		[e-mail]		[phone]	2	0	0
6		[e-mail]	16	[phone]	1	1	0
7					2	1	1

The quality gate queries only one of the three columns:

SELECT
    customer_id
   ,country_code
   ,email
   ,age
   ,phone
FROM
   staging.customer
WHERE
   sys_error = 0;

Three of the seven rows pass the gate. The warning row and the information row flow along, the four rows with at least one error stay behind — logged, with a plain-text message, findable for follow-up. The combination rows show in passing that sys_error is a counter, not a flag, and that the three counters run independently of each other: row 5 carries two errors, row 6 one error and one warning, row 7 fills all three counters at once. The gate still asks only for sys_error = 0. No on-call engineer has to decide at night whether a missing phone number matters more than the daily close. That decision was made long ago, in the rule review, when someone wrote W into the row.

Severity is a routing decision

Up to here, severity looks like a label on the finding. It can do more: it answers the question of where a rule may be enforced.

The reasoning behind it is simple. A constraint in the target schema is the hardest form a rule can take: on its own, it knows no exception, no log line and no "flows on anyway". Only the calling application can soften that, the constraint itself knows no gradation. Only a rule whose violation must truly never reach the target deserves that hardness. That is exactly the definition of the error level. A warning, on the other hand, is supposed to be allowed through, otherwise it would be an error. A constraint that rejects warning violations would contradict the rule's own definition. The routing follows from that:

• E is a candidate for a constraint: NOT NULL, CHECK, UNIQUE, foreign keys. The rule is enforced twice: up front as a check in the source, hard as a guarantee at the target.
• W and I stay in the pipeline: they exist only as check rules, produce findings and never block. A target constraint for them is not merely unnecessary, it is wrong.

Severity is therefore not a report field but the switch that decides about constraint derivation. How the error rules of the configuration can conversely be derived from the target schema is shown by this series' sibling article. The coupling works in both directions, and derived rules always carry E there, because their source is a hard constraint.

Concretely, the routing looks like this. The target layer materializes the two error rules as NOT NULL on country_code and age, while the warning rules and the information rule deliberately have no counterpart there:

CREATE TABLE core.customer
(
    customer_id   int   NOT NULL
   ,country_code  text  NOT NULL
   ,email         text
   ,age           int   NOT NULL
   ,phone         text
   ,CONSTRAINT pk_customer  PRIMARY KEY (customer_id)
);

INSERT INTO core.customer (customer_id, country_code, email, age, phone)
SELECT
    customer_id
   ,country_code
   ,email
   ,age
   ,phone
FROM
   staging.customer
WHERE
   sys_error = 0;
-- INSERT 0 3

The 17-year-old row and the row without a phone number arrive as well. That is exactly the intent: the warning has served its purpose (the finding sits in the log), and the target accepts the record. What would happen if someone materialized the warning rule as a constraint after all is shown by the direct attempt:

ALTER TABLE core.customer
   ADD CONSTRAINT ck_customer_adult CHECK (age >= 18);
-- ERROR: check constraint ck_customer_adult of relation customer
--        is violated by some row

The ALTER TABLE fails on its own existing data, on precisely the row the warning was explicitly supposed to let pass. The error is not an accident but the database pointing out the contradiction: this rule was classified as W, and a CHECK is the enforcement form of E. The same holds for the information rule with the opposite sign: its hard counterpart would be a NOT NULL on phone — technically perfectly expressible, but wrong, because an information must never block. The target allows the missing phone number deliberately, the pipeline merely keeps count.

Four places, one decision

Zooming out of the framework, there are four places where a data quality rule can live. Each can do something the others cannot, and each has a price:

Place	What it can do	What it costs
Source (pre-filter before the load)	finds all bad records up front, classifies by severity, the load carries on with the good ones	its own infrastructure: error table, rule configuration, runner
Pipeline (check steps in the process)	the most expressive option — every rule type, every severity, trends and reports	the rule lives next to the data, and every path around the pipeline bypasses it
Target schema (constraints)	the only guarantee that every write path respects, including the manual hotfix	knows only two outcomes, can only express error semantics, blocks the whole run of a set-based load when in doubt
Reporting (dashboards, analyses)	makes warnings and information visible over time, shows trends	enforces nothing, depends on the findings of the other places

Today's industry default clearly sits on the second place: tools like Great Expectations, Soda or dbt tests formulate checks in the pipeline, and most of them know severity gradations as well. dbt, for instance, distinguishes error and warn per test. That is a workable model, not a mistake. You should just be able to name what you are buying into: a rule that lives exclusively in the pipeline is valid only there. The colleague with the direct INSERT, the second load script, the weekend migration — none of them run through the pipeline, and none of them hit its checks. A constraint in the target schema has no such gap, because it sits in the data itself.

The four places are therefore not competitors you pick one of. The decision is made per rule, and severity is its first criterion: error rules deserve the double floor of pre-filter plus constraint, warnings and information belong in pipeline and reporting.

Why the source still checks first

If only error rules become constraints, why check in the source at all? You could simply let the CHECK do what it is there for.

The answer is already in the framework article, and it remains fully valid under the routing view: a constraint knows only two outcomes. The row fits, or the whole load breaks. When loading thousands of rows, "breaks" is the worst of all options, because a single bad row stops the complete process, and the error message names at best that one row, not the other nine that were still waiting behind it.

One qualification belongs here: this all-or-nothing applies to the set-based load, that is, a single INSERT … SELECT moving all rows in one transaction. That is exactly how an ETL process developed in SQL works, and dbt belongs on this side as well, because its models compile to set-based SQL statements. Row-based ETL tools like SSIS or Talend process the rows individually instead: a failing row can be routed out through an error output there, the remaining rows carry on, and the constraint stops only that one row rather than the whole load. You pay for that convenience with the row-by-row processing itself, which is considerably slower than a set-based load, and with error handling that lives inside the tool instead of in a queryable table. The pre-filter is the set-based answer to the same need: it does what a row-based tool's error output does — just up front, in set logic and with severity levels.

The error output also has a second, less obvious weakness: the checks in the data flow run sequentially. The first hit routes the row out, and the remaining checks never see it — unless you explicitly wire the error path through all further check steps, which quickly clutters the data flow. In practice that means: you fix the first error found, run the load again, find the second one, fix it, find the third. The pre-filter knows no such iterating, because every rule runs set-based across all rows. That is why row 7 of the demo sits in the log completely after a single run, with two errors, one warning and one information.

The pre-filter in the source and the constraint at the target are therefore not alternatives but two halves of the same error rule. The check in the source finds all records that would fail at the target, classifies them and lets the load carry on with the clean ones. The constraint at the target guarantees the rule even for everything that goes past the pre-filter: the manual hotfix, the forgotten second script. If the pre-filter fails, the load breaks loudly instead of silently accepting bad data. If the constraint is dropped, the pre-filter still checks. Only together do the two produce the property neither half has alone: complete findings and a hard guarantee.

So the question is not whether schema or pipeline. The question is which rule deserves both — and that is answered by the severity.

What happens when you promote a rule

Rules are not static. The business decides that customers under 18 must no longer be created: the warning from above is to become an error. In the configuration that is one statement:

UPDATE dq.check_rule
SET
   severity = 'E'
WHERE
       schema_name  = 'staging'
   AND table_name   = 'customer'
   AND where_clause = 'age < 18';

SELECT dq.fn_run_checks('staging', 'customer') AS total_findings;  -- 11

From the next run on, the 17-year-old row blocks at the gate (sys_error = 1). That is the easy part. The demanding part follows from the routing: an error rule is a constraint candidate, so the target schema should guarantee the new hardness as well. And right here waits the price of promotion — the existing data suddenly has to be clean. The 17-year-old row arrived at the target long ago, perfectly legitimately, under the old rule. The direct ADD CONSTRAINT fails on it, as seen above.

Postgres offers a controlled path for this transition. NOT VALID accepts the constraint immediately without checking the existing data. For new rows it applies from the first second anyway:

ALTER TABLE core.customer
   ADD CONSTRAINT ck_customer_adult CHECK (age >= 18) NOT VALID;

INSERT INTO core.customer (customer_id, country_code, email, age)
VALUES (9, 'DE', '[e-mail]', 16);
-- ERROR: new row violates check constraint ck_customer_adult

DELETE FROM core.customer WHERE age < 18;

ALTER TABLE core.customer VALIDATE CONSTRAINT ck_customer_adult;

The order is the point. First NOT VALID seals the future, then the existing data is cleaned up calmly (in the demo via DELETE, in practice more likely: routed out for business review), and only the final VALIDATE CONSTRAINT re-checks the existing rows and completes the guarantee. Between the two steps the constraint sits in a documented intermediate state: it applies to new rows while the existing data is still unchecked. Postgres shows this state in the catalog as convalidated = false. The VALIDATE does not lock the table against writes, it runs with a weak lock alongside normal operation. One subtlety in passing: a CHECK does not fire on NULL, so a missing age would pass ck_customer_adult without complaint. Here that case is already caught by the NOT NULL that the error rule age IS NULL materializes at the target anyway. The same pattern carries a retrofitted NOT NULL on an existing table. How to roll that out without downtime is shown in a dedicated article.

For SQL Server the principle holds with different mechanics: WITH NOCHECK likewise adds a constraint without checking the existing data. The difference sits in the aftermath. The constraint stays permanently marked as not trusted until a WITH CHECK CHECK CONSTRAINT re-validates the existing rows, and an untrusted constraint is ignored by the optimizer in plan decisions. Whoever does only the first step has the guarantee but gives away performance.

Demoting, the reverse path, is just as much a routing decision, by the way: E becomes W, so the corresponding constraint at the target has to go, otherwise it keeps blocking a rule that is only supposed to observe. Promoting pulls constraints in, demoting clears them away — configuration and schema move together.

Taken together, that is the real payoff of the three levels: a rule has a controlled lifecycle. It starts as an observation, proves itself, gets promoted and moves into the schema as a guarantee via NOT VALID and VALIDATE CONSTRAINT — and, when needed, walks the same path back. Severity is the control knob of that lifecycle, not just a label on the finding.

The honest limits

The routing "E becomes a constraint" has one restriction that hides in the word candidate: not every error rule can become a constraint. A CHECK sees exactly one row. Within that row it may compare several columns, a CHECK (end_date > start_date) is perfectly legitimate. But three rule families lie beyond its reach:

• Cross-row rules. "A key may occur at most three times" is one row in the configuration as a check rule (max_occurrence = 3), but not expressible with the declarative constraints (UNIQUE, CHECK, foreign keys), because UNIQUE knows only cardinality 1. Sum, share and distribution rules belong here as well.
• Cross-table rules. A CHECK may not contain a subquery, Postgres rejects that with a clear error message. The only cross-table guarantee the schema knows is the foreign key. Everything beyond it stays pipeline work, say "the discount code must match the customer group".
• Time-based rules. Postgres silently assumes that a CHECK expression always yields the same result, but does not enforce that immutability: a CHECK (order_date <= current_date) is accepted, and that is precisely what makes it a trap. What is valid today is no longer valid tomorrow, and at the latest when restoring a dump or running a VALIDATE, existing rows fail that were correct when they were created. Rules with a time reference belong in the pipeline, where every run checks against the current reference date.

The direction of view matters: these rules do not land in the pipeline because sadly nothing better exists. The pipeline is the right place for them: it checks set-based, knows the run context and can still rate a violation as an error and block it at the gate. A cross-row error rule is an error without a constraint counterpart, and that is not a contradiction to the routing but its honest limit.

Decision matrix

The per-rule decision, condensed:

Rule type	Typical severity	Place of enforcement
Mandatory field the target enforces	E	pre-filter in the source + NOT NULL at the target
Hard value range (type bound, country-code format)	E	pre-filter + CHECK at the target
Key uniqueness	E	pre-filter + PRIMARY KEY/UNIQUE at the target
Reference to a master table	E	pre-filter + FOREIGN KEY at the target
Business anomaly (worth a look, not blocking)	W	pipeline, finding in the log
Pure observation (frequencies, missing optional values)	I	pipeline + reporting
Cross-row (cardinality > 1, sums, shares)	E or W	pipeline only — UNIQUE cannot do cardinality > 1
Cross-table beyond the foreign key	E or W	pipeline only — CHECK cannot hold a subquery
Time-based (date against a reference day)	mostly W	pipeline only — a constraint ages badly

Two readings sit in this table. Top to bottom: the harder the guarantee, the higher the rule stands, and only the E rows reach the schema. And across: the place is never "either source or target". Every constraint row carries both, because the pre-filter delivers the findings and the constraint the guarantee.

FAQ

Why is binary pass/fail not enough?
Because it withholds the urgency of a finding. Either every triviality then blocks the load, or nothing blocks and the findings list goes stale. The three severity levels error, warning and information separate "must not proceed" from "worth a look" and "for information only", and they make the reaction configurable per rule instead of negotiable per night shift.

Which check rules belong in a CHECK constraint?
Only error rules whose violation must never reach the target, and of those only the ones a constraint can express: checks on a single row without a time reference and without looking into other tables. Warnings never belong in a constraint, because a constraint that rejects non-blocking findings contradicts the rule's own definition.

What happens when I promote a warning to an error?
Two things. From the next run on, affected records block at the quality gate. And the target schema should follow, because an error rule deserves a constraint counterpart. For that, the existing data has to be clean: in Postgres, ADD CONSTRAINT … NOT VALID takes the guarantee on immediately for new rows, then the existing data is cleaned up and re-checked via VALIDATE CONSTRAINT.

Does a warning block the load?
No, and that is its purpose. A record with warning findings passes the quality gate, and the finding stays in the log with a plain-text message. Blocking is reserved for the severity error alone. A warning that would need to hold up the process is misclassified and should be promoted.

Does data quality belong in dbt and Great Expectations or in the database?
Both have their place. Pipeline tools like dbt tests, Great Expectations or Soda are expressive, well maintained and know severity gradations themselves. Their checks, however, only apply to paths through the pipeline. A direct INSERT past it hits none of them. Hard error rules therefore deserve an additional constraint in the target schema that every write path respects.

Related Articles

Framework and routines:

• Data Quality Checks with SQL — the series' sub-hub: error table, rule configuration, runner and the quality gate whose severity mechanics this article deepens.
• Validate Data with SQL — the WHERE routine: value ranges, mandatory fields and the NULL trap of three-valued logic.
• Find Duplicates with SQL — the uniqueness routine: cardinality, composite keys and the NULL semantics of UNIQUE.
• Find Orphaned Records with SQL — the lookup routine: checking referential integrity without foreign keys.
• Derive Data Quality Rules from the Schema — the routing's opposite direction: target constraints become error rules of the configuration, projected mechanically.

Theory:

• Data Quality: Dimensions and Error Classes — the conceptual frame: which dimensions the checks cover and which they don't.

Target schema and deployment:

• Postgres Table Conventions — which keys and constraints a target table should carry so hard counterparts exist at all.
• Adding a NOT NULL Column to an Existing Table — the same promotion pattern for mandatory fields: expand/contract without downtime.
• Design Pattern // The Architecture of an ETL Process — the staging architecture in which the pre-filter and the gate have their place.