The rule “country_code is mandatory” lives in your database twice: once as NOT NULL in the target table’s schema, and once as a hand-typed row in the check configuration. On the next ALTER TABLE, only one of the two places changes, and the check silently goes wrong. With derived data quality rules you no longer type that repetition: the metadata already knows which columns are mandatory, which keys must be unique and which value ranges the types allow. And by pinning the derived state at deployment time, you detect schema drift instead of suffering it.
The essentials up front:
- NOT NULL, key uniqueness and type bounds can be projected mechanically from
information_schema— including the message text and the business key. - Projected at deployment time, not on every run: an ETL process is a contract between the source and the target system. The derived rules are generated and persisted at deployment, schema changes are not adopted automatically.
- The live projection stays on as a drift detector: a diff against the deployed state shows when the contract has to be renegotiated.
- Read-only is not a convenience: derived rules are neither edited nor deactivated, the
is_activeflag belongs to the manual rules. Whoever wants a change changes the schema and redeploys. - Handwritten rules remain for the business logic the schema does not contain. Exactly those deserve the review time.
Prerequisite: Postgres as the example engine and the framework from the sub-hub Data Quality Checks with SQL with its shared error table and the rule configuration dq.check_rule. The metadata principle needs only information_schema and thus carries over to any engine that offers it. The code shown uses Postgres syntax and has to be adapted to the dialect of any other engine.
Contents
- The rule that already exists
- Which data quality rules can be derived
- Project at deployment time, not on every run
- The projection as drift detector
- Why read-only is not a convenience
- The boundary you don’t build
- Duplicates among your own rules
- What remains handwritten
- The honest limit
- FAQ
- Related Articles
The rule that already exists
The framework from the sub-hub works off a configuration table: one row is one rule. That is deliberate, because a new check is a data row instead of a code change, and the business side can read along. Such a row is still rolled out through a deployment — why it has to be that way is shown further down in this article. The price of the configuration only shows over time. A large share of the rows you type into such a configuration merely repeats what the target table enforces anyway:
1: CREATE SCHEMA IF NOT EXISTS core;
2:
3: CREATE TABLE core.customer
4: (
5: customer_id int NOT NULL
6: ,source_system varchar(10) NOT NULL
7: ,source_id varchar(20) NOT NULL
8: ,country_code varchar(2) NOT NULL
9: ,email varchar(100)
10: ,age numeric(3,0)
11: ,CONSTRAINT pk_customer PRIMARY KEY (customer_id)
12: ,CONSTRAINT uq_customer_source UNIQUE (source_system, source_id)
13: );
This one table already dictates seven check rules for the staging layer: four mandatory fields (lines 5 through 8), two unique keys (lines 11 and 12), plus the length and value-range bounds of the types. Whoever enters them into dq.check_rule by hand creates seven copies of a truth that already lives in the schema.
Copies drift. When a column phone varchar(30) NOT NULL is added later, someone has to remember to extend the configuration as well. If they forget, the check stays silent and the load fails at the target constraint — exactly the scenario the pre-filter was supposed to prevent. If, the other way around, a NOT NULL is dropped, the old rule keeps checking against a requirement that no longer exists and produces findings without a basis. Both failures are quiet. Nobody gets a message that configuration and schema have drifted apart.
Which data quality rules can be derived
There is a simple way out of the drift problem: derive these rules instead of typing them. Everything the target schema enforces as a constraint or as a declared type bound translates mechanically into a rule row. The mapping onto the framework’s rule types is one to one:
| Metadata source | Derived check | Rule type in the framework | Dimension |
|---|---|---|---|
is_nullable = 'NO' | mandatory-field check | constraint (WHERE clause) | Completeness |
PRIMARY KEY / UNIQUE | uniqueness, cardinality 1 (all key columns NOT NULL) | unique | Uniqueness |
FOREIGN KEY | reference check against the master table (see the outlook below) | lookup | Consistency / Integrity |
character_maximum_length, numeric_precision/_scale | length and value-range bound | constraint (WHERE clause) | Validity |
Which quality dimensions these checks pay into is laid out by the concept article Data Quality: Dimensions and Error Classes. The pattern itself comes from a metadata-driven ETL project: there, the projection ran over a dedicated metadata model with business-side extras such as business and alternate keys and a per-column null handling. For this article it has been transferred to information_schema so that it can be followed without a model of your own. The principle stays the same: the rules live in the metadata, and the projection merely reads them out.
NOT NULL from is_nullable
The simplest projection. Every mandatory column of the target table becomes a mandatory-field check on the staging layer — as a finished row in the configuration table’s format, including where_clause and message text:
1: SELECT
2: 'constraint' AS check_type
3: ,'staging' AS schema_name
4: ,T01.table_name
5: ,T01.column_name AS check_column
6: ,format('%I IS NULL', T01.column_name) AS where_clause
7: ,'E' AS severity
8: ,format('%s is mandatory in the target schema', T01.column_name) AS message
9: FROM
10: information_schema.columns T01
11: WHERE
12: T01.table_schema = 'core'
13: AND T01.table_name = 'customer'
14: AND T01.is_nullable = 'NO'
15: ORDER BY
16: T01.ordinal_position;
For the demo table this yields four rows:
| check_column | where_clause | severity | message |
|---|---|---|---|
customer_id | customer_id IS NULL | E | customer_id is mandatory in the target schema |
source_system | source_system IS NULL | E | source_system is mandatory in the target schema |
source_id | source_id IS NULL | E | source_id is mandatory in the target schema |
country_code | country_code IS NULL | E | country_code is mandatory in the target schema |
Two details are worth a look. The format() with %I on line 6 quotes the column name as an identifier — a column name with special characters yields a correct where_clause, not a broken one. And the projection targets staging (line 3) although it reads from core: checking happens at the source, enforcing happens at the target. That is the division of labour of the whole framework. What would fail as a constraint at the target, the pre-filter finds beforehand.
Uniqueness from PRIMARY KEY and UNIQUE
Keys live in two catalog views: information_schema.table_constraints knows the constraints, information_schema.key_column_usage their columns. With multi-column keys, the columns must be sorted by position, that is by ordinal_position within the key. That field does not count the column’s position in the table but its position in the key, so it reproduces exactly the order from the DDL. Without an ORDER BY, array_agg emits the columns in whatever order the join happens to deliver them, and that order is neither guaranteed nor stable over time in Postgres. A different execution plan is enough to turn source_system,source_id into source_id,source_system on the next run.
For the check result this would be harmless, because a GROUP BY over both columns finds the same duplicates in any order. The damage happens one level up, because check_column carries the key columns as a comma-separated list, and that list identifies the rule. At runtime the runner splits it back up, builds the multi-column GROUP BY from it, and writes the value belonging to each listed column into error_value in the error log. If the order flips, the same rule looks like a different one on the next run, the string comparison against the target constraint fails, and the column-value pairs in the log come out in a different order. The message suffers too, because anyone reading “Key (source_id, source_system) not unique” has to re-sort the columns against the DDL in their head. The projection therefore sorts exactly once, when collecting into the array:
1: WITH
2: CTE_key_column AS
3: (
4: SELECT
5: T01.table_name
6: ,T01.constraint_name
7: ,array_agg(T02.column_name ORDER BY T02.ordinal_position) AS key_column
8: FROM
9: information_schema.table_constraints T01
10: INNER JOIN information_schema.key_column_usage T02
11: ON
12: T02.constraint_schema = T01.constraint_schema
13: AND T02.constraint_name = T01.constraint_name
14: INNER JOIN information_schema.columns T03
15: ON
16: T03.table_schema = T02.table_schema
17: AND T03.table_name = T02.table_name
18: AND T03.column_name = T02.column_name
19: WHERE
20: T01.table_schema = 'core'
21: AND T01.table_name = 'customer'
22: AND T01.constraint_type IN ('PRIMARY KEY', 'UNIQUE')
23: GROUP BY
24: T01.table_name
25: ,T01.constraint_name
26: HAVING
27: bool_and(T03.is_nullable = 'NO')
28: )
29: SELECT
30: 'unique' AS check_type
31: ,'staging' AS schema_name
32: ,table_name
33: ,array_to_string(key_column, ',') AS check_column
34: ,1 AS max_occurrence
35: ,'E' AS severity
36: ,format('Key (%s) not unique'
37: ,array_to_string(key_column, ', ')) AS message
38: FROM
39: CTE_key_column;
The result: one rule per key, not one per column.
| check_column | max_occurrence | message |
|---|---|---|
customer_id | 1 | Key (customer_id) not unique |
source_system,source_id | 1 | Key (source_system, source_id) not unique |
The composite key from line 12 of the table DDL lands position-correct as the comma list source_system,source_id in check_column, which is exactly the form the runner expects and splits back up.
The HAVING on line 27 additionally draws a semantic line. Only keys whose columns are all NOT NULL get projected. The additional join on information_schema.columns takes care of that. The reason lies in the NULL semantics of UNIQUE: under the default NULLS DISTINCT, Postgres allows any number of missing values in a UNIQUE column, while the check’s GROUP BY collapses several NULL into one group and would report them as duplicates. A rule projected without this filter onto a nullable key would be stricter than the constraint it claims to mirror — it would flag rows the target accepts without complaint. For a PRIMARY KEY the condition always holds. A UNIQUE over nullable columns deliberately stays out and belongs in a user-defined rule instead, with whatever NULL treatment the business actually means. How the uniqueness check deals with composite keys and the NULL semantics of UNIQUE in detail is covered by the spoke Finding Duplicates with SQL. The cardinality of derived key rules is always 1: a UNIQUE constraint knows no “at most three times”.
The target table’s foreign keys can be projected into lookup rules as well. This is deliberately an outlook rather than a recipe, because that projection needs more metadata than the two above. information_schema.referential_constraints names only the two constraints involved, that is the foreign key and the referenced primary or unique key. The columns themselves live in key_column_usage again, on both sides: the referencing columns come from a lookup by the foreign key’s name in their ordinal_position, the referenced columns from a second lookup into the same view by the name of the target constraint. The two sides are joined through position_in_unique_constraint, which states for each foreign key column where its counterpart sits within the referenced key. Only that mapping yields the column pairs the check builds its LEFT JOIN … IS NULL from. For this, the lookup routine accepts the child and master columns each as a comma-separated list, and both lists have to be position-aligned: the first child field belongs to the first master field, pair by pair. The projection therefore has to produce both lists in exactly the same key order, or the generated check joins the wrong pairs. On NULL semantics, catalog and routine do agree: under MATCH SIMPLE, the Postgres default, a composite foreign key accepts every row in which even one of the participating columns is NULL, and the lookup check skips exactly those rows too, because a missing value is a completeness finding and not an integrity one. Anyone looking that match type up in the catalog finds it there as match_option = 'NONE', not as SIMPLE. A foreign key declared as MATCH FULL would show up as FULL and would need a stricter check, because it rejects partially filled keys — one more reason this projection stays an outlook. How the check for orphaned records itself works is covered by the corresponding spoke.
Type bounds from numeric_precision and character_maximum_length
The data types themselves are check rules too. A varchar(2) says “at most two characters”, a numeric(3,0) says “absolute value below 1000”. In the staging layer, which still holds such values as unchecked text or generous types, this becomes a value-range rule:
1: SELECT
2: 'constraint' AS check_type
3: ,'staging' AS schema_name
4: ,T01.table_name
5: ,T01.column_name AS check_column
6: ,CASE
7: WHEN T01.character_maximum_length IS NOT NULL
8: THEN format('length(%I) > %s'
9: ,T01.column_name
10: ,T01.character_maximum_length)
11: ELSE format('abs(%I) >= %s'
12: ,T01.column_name
13: ,trim_scale(10::numeric ^ (T01.numeric_precision - T01.numeric_scale))::text)
14: END AS where_clause
15: ,'E' AS severity
16: ,CASE
17: WHEN T01.character_maximum_length IS NOT NULL
18: THEN format('%s longer than %s characters'
19: ,T01.column_name
20: ,T01.character_maximum_length)
21: ELSE format('%s outside numeric(%s,%s)'
22: ,T01.column_name
23: ,T01.numeric_precision
24: ,T01.numeric_scale)
25: END AS message
26: FROM
27: information_schema.columns T01
28: WHERE
29: T01.table_schema = 'core'
30: AND T01.table_name = 'customer'
31: AND ( T01.character_maximum_length IS NOT NULL
32: OR T01.numeric_precision_radix = 10)
33: ORDER BY
34: T01.ordinal_position;
| check_column | where_clause | message |
|---|---|---|
source_system | length(source_system) > 10 | source_system longer than 10 characters |
source_id | length(source_id) > 20 | source_id longer than 20 characters |
country_code | length(country_code) > 2 | country_code longer than 2 characters |
email | length(email) > 100 | email longer than 100 characters |
age | abs(age) >= 1000 | age outside numeric(3,0) |
The filter on line 32 is this projection’s fine print. For int and bigint columns, numeric_precision counts bits, not decimal digits — information_schema.columns reveals this via numeric_precision_radix = 2. Without the radix filter, the projection would claim a bound of 10 to the power of 32 for customer_id, which neither matches the type semantics nor makes for a meaningful check. Only for explicitly declared numeric(p,s) types (radix 10) does the precision carry a business statement.
Project at deployment time, not on every run
What do you do with the three queries? Two obvious answers each come with a catch. The first: take the results into the configuration by hand via INSERT INTO dq.check_rule. That only automates the typing effort. The inserted rows would be editable copies, stale again after the next ALTER TABLE. The second: turn the projection into the runtime rule set directly, as a view, and every rule is fresh at every moment. Exactly that freshness is the second answer’s catch.
An ETL process is a contract between the source and the target system, and that contract is valid at development time. When either side changes, the contract is renegotiated, not adjusted automatically. A live view would do exactly that, in both directions. If the target gets stricter, say through a new NOT NULL column, the run that was green yesterday fails today without any deployment on the ETL side. If the target gets laxer because a constraint is dropped, the view silently drops the corresponding check, and the pre-filter softens without anyone having decided it. Add the audit angle: which rules were in force for a given run is a question a view can, by principle, not answer.
For perspective: within a single application that validates its own schema, live derivation is the right model. There is no second contracting party there, the schema is the only truth. The contract logic begins as soon as two systems are involved.
The conclusion that holds therefore separates two points in time. The projection itself stays a view — the tool that can compute the derived rules fresh from the metadata at any moment:
1: CREATE OR REPLACE VIEW dq.derived_rule AS
2: WITH
3: CTE_pk_column AS
4: (
5: -- primary key of the target table = business key of the error table
6: SELECT
7: T01.table_name
8: ,array_agg(T02.column_name ORDER BY T02.ordinal_position) AS pk_column
9: FROM
10: information_schema.table_constraints T01
11: INNER JOIN information_schema.key_column_usage T02
12: ON
13: T02.constraint_schema = T01.constraint_schema
14: AND T02.constraint_name = T01.constraint_name
15: WHERE
16: T01.table_schema = 'core'
17: AND T01.constraint_type = 'PRIMARY KEY'
18: GROUP BY
19: T01.table_name
20: HAVING
21: count(*) <= 3
22: )
23: ,CTE_key_column AS
24: (
25: -- every key as an ordered column array; only keys whose columns are
26: -- all NOT NULL (otherwise the rule would check more strictly than
27: -- the constraint guarantees - Postgres default NULLS DISTINCT)
28: SELECT
29: T01.table_name
30: ,T01.constraint_name
31: ,array_agg(T02.column_name ORDER BY T02.ordinal_position) AS key_column
32: FROM
33: information_schema.table_constraints T01
34: INNER JOIN information_schema.key_column_usage T02
35: ON
36: T02.constraint_schema = T01.constraint_schema
37: AND T02.constraint_name = T01.constraint_name
38: INNER JOIN information_schema.columns T03
39: ON
40: T03.table_schema = T02.table_schema
41: AND T03.table_name = T02.table_name
42: AND T03.column_name = T02.column_name
43: WHERE
44: T01.table_schema = 'core'
45: AND T01.constraint_type IN ('PRIMARY KEY', 'UNIQUE')
46: GROUP BY
47: T01.table_name
48: ,T01.constraint_name
49: HAVING
50: bool_and(T03.is_nullable = 'NO')
51: )
52: -- NOT NULL from is_nullable
53: SELECT
54: 'constraint' AS check_type
55: ,'staging' AS schema_name
56: ,T01.table_name
57: ,T02.pk_column[1] AS id1_column
58: ,T02.pk_column[2] AS id2_column
59: ,T02.pk_column[3] AS id3_column
60: ,T01.column_name AS check_column
61: ,format('%I IS NULL', T01.column_name) AS where_clause
62: ,1 AS max_occurrence
63: ,'E' AS severity
64: ,format('%s is mandatory in the target schema', T01.column_name) AS message
65: FROM
66: information_schema.columns T01
67: INNER JOIN CTE_pk_column T02
68: ON
69: T02.table_name = T01.table_name
70: WHERE
71: T01.table_schema = 'core'
72: AND T01.is_nullable = 'NO'
73: UNION ALL
74: -- uniqueness from PRIMARY KEY / UNIQUE
75: SELECT
76: 'unique'
77: ,'staging'
78: ,T01.table_name
79: ,T02.pk_column[1]
80: ,T02.pk_column[2]
81: ,T02.pk_column[3]
82: ,array_to_string(T01.key_column, ',')
83: ,NULL
84: ,1
85: ,'E'
86: ,format('Key (%s) not unique'
87: ,array_to_string(T01.key_column, ', '))
88: FROM
89: CTE_key_column T01
90: INNER JOIN CTE_pk_column T02
91: ON
92: T02.table_name = T01.table_name;
The CTE on lines 3 through 22 pulls one more thing out of the metadata that the configuration table would otherwise demand by hand: the business key, through which the error table later maps a finding back to the source record. That, too, lives in the schema — it is the target table’s primary key. As an ordered array it spreads across id1_column through id3_column, and the HAVING count(*) <= 3 on line 21 draws the line of the error table’s contract: it carries no more than three mapping columns.
The projection becomes the rule set only through the deployment step: it reads the view exactly once and writes the result into a table of its own. That table is the signed contract. It records which derived rules were valid at deployment time, and the runner reads it exclusively:
1: CREATE TABLE dq.deployed_rule
2: (
3: check_type text NOT NULL
4: ,schema_name text NOT NULL
5: ,table_name text NOT NULL
6: ,id1_column text
7: ,id2_column text
8: ,id3_column text
9: ,check_column text NOT NULL
10: ,where_clause text
11: ,max_occurrence int NOT NULL DEFAULT 1
12: ,severity char(1) NOT NULL
13: ,message text NOT NULL
14: ,deployed_on timestamptz NOT NULL DEFAULT now()
15: );
16:
17: -- deployment step: sign the contract. Runs at deployment time against
18: -- the target system, not on every run of the runner.
19: DELETE FROM dq.deployed_rule;
20:
21: INSERT INTO dq.deployed_rule
22: (
23: check_type
24: ,schema_name
25: ,table_name
26: ,id1_column
27: ,id2_column
28: ,id3_column
29: ,check_column
30: ,where_clause
31: ,max_occurrence
32: ,severity
33: ,message
34: )
35: SELECT
36: check_type
37: ,schema_name
38: ,table_name
39: ,id1_column
40: ,id2_column
41: ,id3_column
42: ,check_column
43: ,where_clause
44: ,max_occurrence
45: ,severity
46: ,message
47: FROM
48: dq.derived_rule;
The table deliberately carries no active flag. The configuration table’s is_active belongs exclusively to the manual rules, because a derived rule is neither edited nor deactivated. The deployed_on field documents, in passing, when the contract was signed.
The contract idea does not stop at the derived rules. The manual rules in the configuration table change only as part of a deployment as well — inserted, edited or toggled via is_active in a versioned way, not live. The reason is the same as with the schema: a rule list that can be rearranged by hand between two deployments no longer tells you, at a glance, what was in force during the last run. The execution log does record that, but the list is supposed to predict what runs, not merely document what ran. is_active is thus a versioned switch, not a live dial.
The check scope of a table is now — within the limits the projection itself draws — the union of both worlds: the deployed derived rules plus the active handwritten ones from the configuration table. Add a single user-defined rule as an example — an age limit that no constraint contains:
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 < 18', 'W', 'Customer under 18 - review with business');
5:
6: SELECT
7: 'derived' AS origin
8: ,check_type
9: ,check_column
10: ,where_clause
11: ,severity
12: ,message
13: FROM
14: dq.deployed_rule
15: UNION ALL
16: SELECT
17: 'user-defined'
18: ,check_type
19: ,check_column
20: ,where_clause
21: ,severity
22: ,message
23: FROM
24: dq.check_rule
25: WHERE
26: active
27: ORDER BY
28: origin
29: ,check_type
30: ,check_column;
| origin | check_type | check_column | where_clause | severity |
|---|---|---|---|---|
| derived | constraint | country_code | country_code IS NULL | E |
| derived | constraint | customer_id | customer_id IS NULL | E |
| derived | constraint | source_id | source_id IS NULL | E |
| derived | constraint | source_system | source_system IS NULL | E |
| derived | unique | customer_id | E | |
| derived | unique | source_system | E | |
| user-defined | constraint | age | age < 18 | W |
Six out of this table’s seven rules come from the schema. Exactly one was typed — the one that actually contains business knowledge. How large that share turns out does depend on the table, though: a target table without mandatory fields and without key constraints gives the projection simply nothing.
The projection as drift detector
What happens when the target schema changes after the deployment? To the running process, at first: nothing. The runner reads the deployed state, and that is exactly the point of the model. The change does not stay invisible, though, because the view keeps computing the live state — and the difference between the two can be queried mechanically:
1: ALTER TABLE core.customer ALTER COLUMN email SET NOT NULL;
2: ALTER TABLE core.customer DROP CONSTRAINT uq_customer_source;
3:
4: SELECT
5: 'added' AS drift
6: ,check_type
7: ,check_column
8: ,message
9: FROM
10: (
11: SELECT check_type, schema_name, table_name, id1_column, id2_column,
12: id3_column, check_column, where_clause, max_occurrence, severity, message
13: FROM
14: dq.derived_rule
15: EXCEPT
16: SELECT check_type, schema_name, table_name, id1_column, id2_column,
17: id3_column, check_column, where_clause, max_occurrence, severity, message
18: FROM
19: dq.deployed_rule
20: ) T01
21: UNION ALL
22: SELECT
23: 'removed' AS drift
24: ,check_type
25: ,check_column
26: ,message
27: FROM
28: (
29: SELECT check_type, schema_name, table_name, id1_column, id2_column,
30: id3_column, check_column, where_clause, max_occurrence, severity, message
31: FROM
32: dq.deployed_rule
33: EXCEPT
34: SELECT check_type, schema_name, table_name, id1_column, id2_column,
35: id3_column, check_column, where_clause, max_occurrence, severity, message
36: FROM
37: dq.derived_rule
38: ) T02
39: ORDER BY
40: drift
41: ,check_column;
| drift | check_type | check_column | message |
|---|---|---|---|
| added | constraint | email | email is mandatory in the target schema |
| removed | unique | source_system,source_id | Key (source_system, source_id) not unique |
The two directions of the diff tell different stories. An added row means: the target has become stricter. Without a redeploy, the pre-filter does not know the new guarantee, and the load would fail at the target constraint instead of at the pre-filter — the detector shows this before it happens. A removed row means: the target has become laxer. The pre-filter checks more strictly than necessary, which endangers no run but is worth a negotiation. In both cases the answer is the same: review the contract and sign it again deliberately, that is, run the deployment step once more. The diff works as a check in the deployment gate or as a warning before each run. It is a signal, never an automatic adoption.
Why read-only is not a convenience
For the deployed derived rules, a hard convention applies: no UPDATE, no deactivating, no deleting. That is why the table carries no is_active either — this flag belongs exclusively to the manual rules in the configuration table. What looks like a limitation is the core of the pattern, not its side effect.
If the derived rule were editable, it would be a copy with an expiry date. Any change to it would decouple it from its source, and from that moment on there would again be two truths: the one in the schema and the one in the rule. Worse still: a well-meant “let’s switch this one check off for a bit” would bypass a guarantee that the target schema keeps enforcing regardless. The load would then fail at a constraint for which there apparently was no active rule. The check would no longer be the target’s honest pre-filter but its own drifting opinion of it.
For the same reason, the criticality of derived rules follows the source semantics rather than an editor’s judgement. A NOT NULL and a unique key are hard constraints at the target. Their derived rules therefore carry E as in error, because a violation would make the load fail there. That is not an opinion to be debated per rule but a property of the schema.
Whoever really wants to get rid of a derived rule has exactly one path: change the schema and redeploy. That sounds inconvenient, and it is intended. The discussion “does this column really have to be mandatory?” belongs at the table, not at a configuration row that merely mirrors the table.
The boundary you don’t build
As soon as two rule sources exist, one question suggests itself: what happens when both check the same thing? If someone manually creates a uniqueness rule on customer_id that already exists as a derived one — do you need conflict detection, a precedence scheme, a merge?
The better answer is: you never let the situation arise. Instead of detecting and resolving conflicts between derived and user-defined rules, the user-defined territory is cut so that it never enters the derived one. Concretely: the keys the schema already secures are not offered again in the rule editor — no “check primary key” quick pick, no pre-filled uniqueness form for the constraint columns. Those checks already exist, automatically and non-negotiably. The free uniqueness type is there for the cases the schema does not know: a composite business key plus a validity date, or an “at most three times per region”, in other words different column combinations and different cardinalities.
The conflict you don’t build does not have to be resolved. There is no merge logic, no precedence table and no special case in the runner — not because the problem was solved elegantly, but because by construction it does not exist. That is a design decision, not an algorithm, and it is cheaper and more robust than any conflict detection you would have to write instead.
Duplicates among your own rules
Entirely without checks, though, the user-defined territory does not get by. Within the handwritten rules, the same check can accidentally be created twice, and then two identical findings for the same violation would sit in the error table. What gets blocked is therefore the exact content duplicate: same rule type, same columns, same condition.
What deliberately does not get blocked is worth noting. Several rules of the same type per column are explicitly allowed: two different value-range checks on the same column are two separate, legitimate checks with their own messages. A “one rule per column and type” constraint would forbid exactly the cases a freely configurable rule table is built for.
The second subtlety: the criticality is not part of a rule’s identity. Two rules with an identical condition but different severity count as the same duplicate. Otherwise the same check could exist once as an error and once as a warning, and the same violation would be reported twice — once blocking, once not. Whoever wants to change a rule’s severity edits the existing rule instead of placing a second one next to it.
What remains handwritten
After all the deriving, a remainder is left, and it is the most valuable part of the configuration. The schema only holds what the database can enforce. Everything else is business knowledge:
- Cross-field conditions — a discount requires an active status, an end date lies after the start date.
- Plausibility and temporal logic — an order date does not lie in the future, an age under 18 is possible but worth reviewing (the example rule from above).
- Value lists owned by the business — allowed status values or product codes deliberately not cemented as constraints, because the business side maintains them.
The derived rules need no review time, because they cannot be wrong — they claim nothing the target schema does not enforce anyway. Every minute a review spends nodding off “customer_id is mandatory” is missing from the question of whether the age limit is right. The projection shifts the attention to where mistakes are actually possible: into the handwritten rules.
The honest limit
The projection is exactly as good as the schema it reads from. That is its strength and its limit at once, and the limit deserves the same clear look as the pattern itself.
Where mandatory fields exist only in the application, because the column allows NULL and only the input form enforces a value, information_schema sees nothing, and the projection yields nothing. The same goes for business keys that were never created as constraints, and for length bounds on text columns without a declared length. The projection must not silently paper over these gaps: it delivers exactly the guarantees the schema states, and not a single one more. If you want more derived, you have to make the schema more honest — which keys and constraints a target table should carry in the first place is covered by the article on Postgres table conventions.
This pattern deserves to be called by its name: it is a deficit of application development, and not a rare one. Many applications treat the database as mere storage. Mandatory fields, value ranges and keys are checked by application code, while the schema allows almost anything. As long as only the application itself writes, this goes unnoticed. But anyone who wants to load data into such an application past the input form, say during a migration or through an interface, stands there without any of the guarantees this article projects. The consequence is uncomfortable but clear: exactly the rules that an honest schema would make derivable have to be created and maintained manually as user-defined rules for safe loading — with the full drift risk this article started with.
On top of that comes a Postgres-specific subtlety: information_schema is portable but not complete. A CREATE UNIQUE INDEX without an accompanying constraint does not appear in table_constraints, and neither do partial unique indexes or the NULLS NOT DISTINCT behaviour of newer Postgres versions. If you use such constructs, project from the system catalogs pg_constraint and pg_index instead — with more detail, but without portability. A partial unique index translated into a full uniqueness rule would simply be wrong, so its index predicate belongs in the rule, or the index deliberately stays out of the projection.
Deriving does not replace thinking about data quality. It moves the thinking to where it belongs: into the schema.
FAQ
Everything the target table declares as a constraint or type: mandatory-field checks from is_nullable, uniqueness checks from PRIMARY KEY and UNIQUE (provided the key columns are NOT NULL), reference checks from foreign keys, plus length and value-range bounds from character_maximum_length and numeric_precision. Not derivable is business logic without a schema trace — cross-field conditions, temporal plausibility, value lists owned by the business.
Because an editable derived rule would be a copy with an expiry date. After the first change there would be two truths: the one in the schema and the one in the rule. A switched-off check would additionally hide a guarantee the target still enforces. That is why the deployed state deliberately carries no is_active — the flag belongs to the manual rules. Whoever wants to change a derived rule changes the schema and redeploys.
information_schema enough, or do I need my own metadata model? For NOT NULL, keys and type bounds, information_schema is enough, and it is portable — the same views exist in SQL Server. The Postgres system catalogs (pg_constraint, pg_index) additionally see unique indexes without constraints and partial indexes. A metadata model of your own pays off only once business metadata should join in that the database does not know — say, business keys without a database constraint or a per-column null-handling semantic.
Then one of the two sides is wrong, and that belongs resolved at the schema rather than overridden in the configuration. If the business says “email is mandatory” while the column allows NULL, the schema is too lax: add the constraint, and the derived rule follows automatically. Until then, a user-defined rule with severity warning can make the gap visible without faking a hard guarantee.
Through the origin of the data itself: derived rules live only in the deployment table, which only the deployment step fills, user-defined ones only in the configuration table. A combined view with an origin column (via UNION ALL) shows the complete check scope. Only one rule of discipline matters: never copy projected rules into the configuration table by hand — there they would be editable copies, and the drift starts all over again.
Related Articles
Framework and routines:
- Data Quality Checks with SQL — the sub-hub: the configurable framework with error table, rule configuration and runner, whose configuration this article fills automatically.
- Validating Data with SQL — the routine behind the projected WHERE rules: value ranges, mandatory fields and the NULL trap.
- Finding Duplicates with SQL — the uniqueness routine: cardinality, composite keys and the NULL semantics of UNIQUE.
- Finding Orphaned Records with SQL — the lookup routine that derived foreign-key rules build on.
- Three severity levels instead of pass/fail — the severity spoke of the series: why E/W/I can do more than passed/failed. (coming soon)
Theory:
- Data Quality: Dimensions and Error Classes — the concept frame: which dimensions the derived rules pay into.
Target schema:
- Postgres Table Conventions — which keys and constraints a target table should carry so the projection has something to read.