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

At some point, someone switches the check off. What usually happened before is always the same story: a missing country code stopped the entire load at three in the morning, the business side was left without numbers, and the cause was a triviality. Binary pass/fail only lets you choose whether a rule blocks everything or nothing. Steering data quality severity levels instead — three of them — separates “must not proceed” from “worth a look”, and the same classification makes a second decision on the side: whether a rule additionally belongs in the target schema as a hard constraint or stays in the pipeline alone.

Key takeaways:

• Binary pass/fail fails at both ends. Either a triviality blocks the load, or every finding scrolls by unread. Both roads end with the check switched off.
• Three severity levels cover real life: information documents, warning makes visible, error blocks.
• Severity is a routing decision. In this model, only an error rule can get a counterpart in the target schema as a CHECK or UNIQUE constraint — it is never created automatically. Warnings and information always stay in the pipeline.
• Four places to enforce a rule — source, pipeline, target schema, reporting — each with what it can do and what it costs.
• Promoting a rule has a price: an observation becomes a guarantee, and the existing data has to be clean for that. NOT VALID and VALIDATE CONSTRAINT show it in fast-forward.
• The honest limit: a CHECK may compare several columns of the same row, but it cannot look across rows, tables or time. For those rules the pipeline is the right place — a trigger enforces them only as a justified exception.

Prerequisite: Postgres as the demo engine and the framework from the sub-hub Data Quality Checks with SQL with its shared error table, rule config and quality gate. The decision logic itself applies to any checking system that collects findings.

Contents

• Why binary fails
• Three data quality severity levels
• Severity is a routing decision
• Four places, one decision
• Why the pre-check still runs up front
• What happens when you promote a rule
• The honest limits
• Decision matrix
• FAQ
• Related Articles

Why binary fails

A data quality system with a single switch has two operating modes, and both lead to the same off button.

With the switch set to “strict”, every violated rule blocks the load. That sounds like discipline and works right up to the first triviality. A customer without an email address is no reason to withhold the revenue numbers from the business side in the morning. After the third night shift over a cosmetic finding, pressure builds, and experience says it does not end in a better rule. It ends in a comment marker in front of the check.

With the switch set to “loose”, everything passes and the findings land in a report. That report gets read in week one, skimmed in week two and deleted unread from week three on. A check without consequences is documentation, not control.

The reflex to switch such a system off is understandable and no sign of a sloppy team. The problem sits in the model. A single pass/fail bit cannot carry the difference between “this record is unusable” and “a phone number is missing here”. Yet that difference is exactly what the process control needs.

Three data quality severity levels

The solution is unspectacular and well proven: three levels instead of one bit. Severity models are as old as syslog and as widespread as linter levels, and that is precisely why they work: everyone understands them without training.

• Information (I) documents. The finding is logged and appears in no blocking logic. A rarely maintained optional field is the typical case — you want to know about the gaps without ever acting on them.
• Warning (W) makes visible. The finding should catch someone’s eye, but it stops no load. Experience puts most real-world data problems on this level.
• Error (E) blocks. The record would fail at the target or cause real damage. It stays behind in staging until someone resolves the cause.

Severity is attached to the rule, not to the individual finding. Every row of the rule config carries its own classification, and every finding inherits it. In the framework’s config table that is one column, guarded by a CHECK that keeps the config itself clean:

  1: CREATE TABLE dq.check_rule
  2: (
  3:     id            bigint  NOT NULL GENERATED ALWAYS AS IDENTITY
  4:    ,check_type    text    NOT NULL
  5:    ,schema_name   text    NOT NULL
  6:    ,table_name    text    NOT NULL
  7:    ,id1_column    text    NOT NULL
  8:    ,check_column  text    NOT NULL
  9:    ,where_clause  text
 10:    ,severity      char(1) NOT NULL DEFAULT 'E'
 11:    ,message       text    NOT NULL
 12:    ,active        boolean NOT NULL DEFAULT true
 13:    ,CONSTRAINT pk_check_rule           PRIMARY KEY (id)
 14:    ,CONSTRAINT ck_check_rule_severity  CHECK (severity IN ('E', 'W', 'I'))
 15: );

  1: CREATE TABLE dq.check_rule
  2: (
  3:     id            bigint  NOT NULL GENERATED ALWAYS AS IDENTITY
  4:    ,check_type    text    NOT NULL
  5:    ,schema_name   text    NOT NULL
  6:    ,table_name    text    NOT NULL
  7:    ,id1_column    text    NOT NULL
  8:    ,check_column  text    NOT NULL
  9:    ,where_clause  text
 10:    ,severity      char(1) NOT NULL DEFAULT 'E'
 11:    ,message       text    NOT NULL
 12:    ,active        boolean NOT NULL DEFAULT true
 13:    ,CONSTRAINT pk_check_rule           PRIMARY KEY (id)
 14:    ,CONSTRAINT ck_check_rule_severity  CHECK (severity IN ('E', 'W', 'I'))
 15: );

The default on line 10 is deliberately E. Whoever writes a rule means it seriously at first. Downgrading to a warning is a conscious decision, and it is an easier one than tightening later. What tightening drags along is covered in the promotion section below. Here are three example rules, one per level:

  1: INSERT INTO dq.check_rule
  2:     (check_type,   schema_name, table_name, id1_column,    check_column, where_clause,           severity, message)
  3: VALUES
  4:     ('constraint', 'staging',   'customer', 'customer_id', 'age',        'age < 0 OR age > 120', 'E', 'Age out of range 0..120')
  5:    ,('constraint', 'staging',   'customer', 'customer_id', 'email',      'email IS NULL',        'W', 'Email missing')
  6:    ,('constraint', 'staging',   'customer', 'customer_id', 'phone',      'phone IS NULL',        'I', 'Phone number not recorded');

  1: INSERT INTO dq.check_rule
  2:     (check_type,   schema_name, table_name, id1_column,    check_column, where_clause,           severity, message)
  3: VALUES
  4:     ('constraint', 'staging',   'customer', 'customer_id', 'age',        'age < 0 OR age > 120', 'E', 'Age out of range 0..120')
  5:    ,('constraint', 'staging',   'customer', 'customer_id', 'email',      'email IS NULL',        'W', 'Email missing')
  6:    ,('constraint', 'staging',   'customer', 'customer_id', 'phone',      'phone IS NULL',        'I', 'Phone number not recorded');

The quality gate afterwards evaluates nothing but the error counters. This is the demo set before the runner has done its work — four customer rows, ranging from clean to doubly conspicuous:

Three of these four rows pass the gate:

  1: SELECT
  2:     customer_id
  3:    ,sys_error
  4:    ,sys_warning
  5:    ,sys_info
  6: FROM
  7:    staging.customer
  8: WHERE
  9:    sys_error = 0
 10: ORDER BY
 11:    customer_id;

  1: SELECT
  2:     customer_id
  3:    ,sys_error
  4:    ,sys_warning
  5:    ,sys_info
  6: FROM
  7:    staging.customer
  8: WHERE
  9:    sys_error = 0
 10: ORDER BY
 11:    customer_id;

Customer 2 stays behind: the impossible age is the only error in the set. Customers 3 and 4 flow on even though their email is missing. Their warnings are logged but do not block. And the sys_info = 1 on customer 4 is the missing phone number — recorded without anyone ever having to act on it. Nobody had to get up at night for any of this.

Severity is a routing decision

Up to this point, severity would merely be a better label. What makes it interesting is a second role that at first glance has nothing to do with blocking: it decides whether a rule is enforced in the pipeline alone or additionally in the target schema.

The pattern comes from a metadata-driven ETL project, and the order of events is the actual point there: it starts not with the rule but with the restrictive data model. The metadata defines which columns are mandatory, which keys must be unique and which value ranges apply. The target schema with its hard constraints is generated from that model, and the error rules of the rule config are derived from the same metadata: every hard guarantee of the target schema automatically gets its up-front check with criticality E. A warning is never created this way. It is added by hand, because it is meant to observe something the data model deliberately does not enforce. Criticality in that model is not a reporting field. It marks whether a schema guarantee stands behind a rule.

The logic behind it holds without any generator. An error rule says: “a violation must not reach the target under any circumstances.” That happens to be the job description of a constraint. A warning says: “a violation should catch attention but stop nothing.” A constraint cannot do that job, because it knows exactly two outcomes: the row fits, or the statement aborts. A rule that is meant to observe has no business in the target schema.

Just as important is what severity does not trigger: a rule with severity E does not automatically create a constraint. It can get one, and whether it is created is a decision of the data model. What separates cleanly here are the roles: the rule ensures data quality through the ETL process. It finds violations, logs them and stops them at the quality gate before loading. The constraint safeguards data quality in the database itself. It holds on every normal write path of the database, including the ones outside the ETL process. The mapping itself is a decision of this model, not an engine rule, because Postgres knows no severity levels. That only error rules get a schema counterpart is the framework’s logic: a rule whose violation must be rejected hard is, by definition here, an error.

For the age rule, that decision has been made in the demo model. Its counterpart stands in the target schema as a CHECK:

  1: CREATE TABLE core.customer
  2: (
  3:     customer_id  int   NOT NULL
  4:    ,email        text
  5:    ,phone        text
  6:    ,age          int
  7:    ,CONSTRAINT pk_customer      PRIMARY KEY (customer_id)
  8:    ,CONSTRAINT ck_customer_age  CHECK (age >= 0 AND age <= 120)
  9: );

  1: CREATE TABLE core.customer
  2: (
  3:     customer_id  int   NOT NULL
  4:    ,email        text
  5:    ,phone        text
  6:    ,age          int
  7:    ,CONSTRAINT pk_customer      PRIMARY KEY (customer_id)
  8:    ,CONSTRAINT ck_customer_age  CHECK (age >= 0 AND age <= 120)
  9: );

The email rule is deliberately absent here. After the load, the rows without an email sit in the target, their warning is logged, and the process ran through. Whoever tries to push the same violation past the constraint instead meets the second outcome:

  1: INSERT INTO core.customer (customer_id, email, phone, age)
  2: VALUES (2, 'b@example.com', NULL, 200);
  3: -- ERROR:  new row for relation "customer" violates check constraint "ck_customer_age"
  

  1: INSERT INTO core.customer (customer_id, email, phone, age)
  2: VALUES (2, 'b@example.com', NULL, 200);
  3: -- ERROR:  new row for relation "customer" violates check constraint "ck_customer_age"
  

The same business violation, two completely different consequences. In the pipeline, age 200 was a finding in the error table. At the target it is an aborted statement. Whether a rule knows only the first path or both is written in its severity — that is why severity is a routing decision, not a label. The whole routing at a glance:

Four places, one decision

Zooming out, there are four places where a data quality rule can live. Each one can do something the others cannot:

In modern data stacks, the default today mostly places rules in the pipeline. dbt tests, Great Expectations and Soda have good reasons for that: the rules live as code in the repository, run in CI, and the same check works against Postgres just as it does against a cloud warehouse. That is a legitimate trade-off, not a mistake.

Its price is still worth knowing. A rule in a pipeline tool applies exactly when the pipeline runs. The colleague with an ad-hoc INSERT, the migration script from last quarter and the second process writing to the same table never see the rule. A constraint in the target schema has no such gap, because it is part of the data itself. What you pay for that is its lack of compromise. The trade-off follows two questions: severity determines how a violation is reacted to. Whether a rule is additionally guaranteed in the schema is decided by the data model and by what a constraint can express. What is meant to observe belongs in the pipeline or the pre-check.

Why the pre-check still runs up front

When an error rule stands at the target as a constraint, the up-front check in the source looks redundant at first glance. It is not, because the two layers answer different questions.

The constraint guarantees. It is the last line of defense that nobody can bypass. But it only knows its two outcomes, and when loading thousands of rows, “statement aborts” is the worst possible answer: the process stands still, and you do not even know which rows caused it. The pre-check answers the other question. It identifies all records that would fail at the target, logs them with key, value and message, and lets the clean ones continue. How that pre-check is built — error table, rule config, generic runner — is described in the sub-hub Data Quality Checks with SQL.

There is a third variant, and many load paths are built exactly this way: no explicit check at all, but the conditions inlined into the load statement. The INSERT … SELECT gets a WHERE clause that excludes every known defect, from the age range to the missing email to the unknown country code, and whatever remains gets loaded. In a dbt model or a Talend job this only looks syntactically different. For a handful of conditions it is the shortest path, and that is exactly why many load paths start out this way.

What gets lost only shows in operation. The clause grows with every new rule into a predicate nobody fully reads anymore, and the checking logic is buried in load code instead of being readable as configuration. Above all, the discarded rows disappear silently: no finding, no message, no counter. That 500 records fewer arrived today, and why, is something nobody learns. And such an inline filter knows no severity levels, it only knows in or out — which is the binary pass/fail from the beginning, just hidden. The pre-check is the same logic turned around once: every condition becomes a rule row with severity and message, every hit becomes a logged finding, and the load statement keeps one single, stable condition: sys_error = 0.

The payoff then shows in everything that follows the gate. Once the bad records are flagged and set aside, every downstream step works on a verified set. Transformations, historization, KPI calculations: everything behind the gate is clean, simple SQL without a single defensive condition, and the same goes for the dbt model or whatever artifact sits at that spot. Nobody writing those steps has to keep in mind that an age might be 200 or a country code might point nowhere. That mental load is concentrated in one place, the pre-check, instead of being spread across every downstream step.

Together they are not a contradiction but a division of labor: the rule ensures data quality through the ETL process so the load does not break. The constraint safeguards data quality in the database so that even the path around the pipeline leaves no bad data behind. The two layers are not equals in this: the hard constraint is optional and a decision of the data model. Once it does stand in the schema, though, the pre-check is mandatory, because without it the load breaks on exactly that constraint. So the question is never whether schema or pipeline. The question is which rule additionally gets a schema guarantee, and the answer is written in its severity.

What happens when you promote a rule

Promoting a rule looks harmless. In the config it is one UPDATE:

  1: UPDATE
  2:    dq.check_rule
  3: SET
  4:    severity = 'E'
  5: WHERE
  6:        table_name   = 'customer'
  7:    AND check_column = 'email';
  

  1: UPDATE
  2:    dq.check_rule
  3: SET
  4:    severity = 'E'
  5: WHERE
  6:        table_name   = 'customer'
  7:    AND check_column = 'email';
  

From the next run on, the email rule blocks the gate. That is the small part of the change. The big part follows from the routing: if the rule is also to be guaranteed hard, a CHECK now belongs in the target schema. Promotion is then the one case where the rule exists before the schema guarantee and the data model has to catch up. And at the target sits the existing data, including all the rows that arrived perfectly legally without an email under the old warning.

Postgres offers a two-step path for exactly this situation. NOT VALID arms the constraint immediately for new rows and leaves the existing data unchecked for now:

  1: ALTER TABLE core.customer
  2:    ADD CONSTRAINT ck_customer_email CHECK (email IS NOT NULL) NOT VALID;

  1: ALTER TABLE core.customer
  2:    ADD CONSTRAINT ck_customer_email CHECK (email IS NOT NULL) NOT VALID;

New rows without an email are rejected from this moment on. Validating the existing data is the second, separate step, and it honestly fails as long as legacy rows exist:

  1: ALTER TABLE core.customer VALIDATE CONSTRAINT ck_customer_email;
  2: -- ERROR:  check constraint "ck_customer_email" of relation "customer" is violated by some row
  

  1: ALTER TABLE core.customer VALIDATE CONSTRAINT ck_customer_email;
  2: -- ERROR:  check constraint "ck_customer_email" of relation "customer" is violated by some row
  

Only after the existing data has been cleaned up — backfilled, archived or deliberately deleted, which is a business decision — does VALIDATE CONSTRAINT run through, and the rule holds hard. That is the real price of a promotion: an observation becomes a guarantee, and a guarantee must also hold for everything already there. How to pull off such retroactive tightening on a populated table without downtime is covered in detail by Adding a NOT NULL Column to a Populated Table.

For SQL Server readers: WITH NOCHECK over there looks similar but has different semantics. The constraint remains “not trusted” until it is revalidated with WITH CHECK CHECK CONSTRAINT, and the optimizer ignores it in its assumptions until then. The Postgres path and the SQL Server path solve the same problem, but they are not interchangeable.

The honest limits

The correspondence between error rule and constraint has a limit, and it lies not in the severity but in what a constraint can express.

A CHECK sees exactly one row. Cross-field rules within that row are still within reach, such as “the end date lies after the start date”. As soon as the rule looks across rows, that is the end of it: an “at most three contracts per customer” is no constraint with on-board means, and a UNIQUE constraint only covers the special case that a combination of values may occur at most once. Cross-table business rules like “discount only with an active master agreement” fail just the same, because a foreign key checks existence, not business logic.

Time-based rules do not belong in a CHECK either, even though Postgres accepts them syntactically. A CHECK (order_date <= current_date) is a trap: a row that is valid today would still be valid after a restore tomorrow, but a check against “now” is not stably reproducible, and dump order or later validations can fail on it. Constraints should stand on immutable expressions whose result stays the same for the same row.

For the cross-row and cross-table cases there is a way out, though, and it deserves an honest look: the trigger. A BEFORE trigger may query what a CHECK cannot see, namely other rows and other tables, and raise an exception on a violation. An “at most three contracts per customer” can indeed be enforced in the database this way, with the same reach as a constraint: on every write path, including the one around the pipeline.

The price is substantial, and it has three items. First, runtime: the trigger fires on every INSERT and UPDATE and runs its own queries while doing so, which noticeably slows down a bulk load. Second, concurrency: two simultaneous transactions do not see each other’s uncommitted rows, and both can pass the “at most three” check at the same time. The rule only becomes watertight with additional protection against concurrent transactions, such as explicit locking or the SERIALIZABLE isolation level, which is exactly the work a real UNIQUE takes off your hands. Third, visibility: a constraint stands declaratively in the schema and is readable for everyone, a trigger hides the same rule in procedural code, and there is no two-step NOT VALID/VALIDATE for its existing data. That leaves the trigger as the justified exception for the case where a cross-row or cross-table guarantee really has to be hard. As a default it does not qualify.

For all remaining rules the pipeline is not the fallback but the right place. It can check across rows, tables and time, and it can report the result in a differentiated way instead of aborting hard. An error rule of this kind therefore stays a pipeline rule — it blocks the gate without ever getting a counterpart in the schema. The correspondence therefore comes with a condition: only what a constraint can express also stands in the target schema as one.

Decision matrix

The summary as a table. For the most common kinds of rules it answers both questions at once: which severity, and where the rule is enforced.

Two readings sit in this table. First: severity alone does not settle the question of place. An E makes a rule a candidate for a constraint counterpart, but only if its condition can be written as a constraint at all. The cross-row or cross-table error rule therefore stays in the pipeline despite its E. Second: for the rules that do have a schema counterpart, E means “twice” — checked up front in the source and guaranteed at the target. That is no contradiction but the core of the division of labor.

FAQ

Related Articles

Framework and routines:

• Data Quality Checks with SQL — the sub-hub: the configurable framework with error table, rule config, runner and the quality gate whose severity logic this article deepens.
• Validating Data with SQL — value ranges, mandatory fields and the NULL trap: the routine behind the WHERE rules.
• Finding Duplicates with SQL — the uniqueness routine whose error rules have their counterpart in the target’s UNIQUE constraint.
• Finding Orphaned Records with SQL — the reference routine, the pre-check in front of the FOREIGN KEY.
• Deriving Data Quality Rules from the Schema — how the error rules are derived from the restrictive data model instead of being typed by hand. (coming soon)

Theory and architecture:

• Data Quality: Dimensions and Error Classes — the concept frame of the series: which dimensions exist and which ones SQL reaches.
• Design Pattern // The Architecture of an ETL Process — where bad data is isolated in layered staging levels.

Target schema:

• Postgres Table Conventions — which keys and constraints a target table should carry.
• Adding a NOT NULL Column to a Populated Table — the expand/contract pattern for retroactive tightening on populated tables.