IF Commons GitHub

sql quality (SQL-DQ)

0052Active

0052-sql-quality · owner if · domain data

Requirements

FR-001checkpassing

A generic test MUST be a value with the level it is written at (a column or a model) and the type of its arguments; a test of one level MUST NOT compile on the other, and a test whose arguments are missing MUST NOT compile.

Enforced (check): .github: cd commons/data && bun run verify — suite/quality.test.ts: a generic test has the level it is written at and the type of its arguments (a test of a column does not go on a model), takes the name dbt gives it, and a selection picks by tag, relation, test and kind and leaves out what it excludes

Proved by: data/suite/quality.test.ts

Written in: data/sql/quality/mod.ts, data/sql/quality/test.ts

Tests: passed: suite/quality.test.ts a generic test has the level it is written at and the type of its arguments (a test of a c

Open: issue data 00011: Capabilities of dbt that the library does not have yet

FR-002checkpassing

A test MUST take the name dbt gives it (the test, the relation, the column, the arguments), a long name MUST be made short, and a selection MUST pick by tag, relation, test and kind and leave out what it excludes.

Enforced (check): .github: cd commons/data && bun run verify — suite/quality.test.ts: a generic test has the level it is written at and the type of its arguments (a test of a column does not go on a model), takes the name dbt gives it, and a selection picks by tag, relation, test and kind and leaves out what it excludes

Proved by: data/suite/quality.test.ts

Written in: data/sql/quality/test.ts

Tests: passed: suite/quality.test.ts a generic test has the level it is written at and the type of its arguments (a test of a c

FR-003checkpassing

A use MUST be configured by severity, warnIf, errorIf, where, limit, failCalc, stored failures, schema, alias, tags, meta, enabled and name.

Enforced (check): .github: cd commons/data && bun run verify — suite/quality.test.ts: the status of a number of failures is judged as dbt judges it: severity, warnIf and errorIf

Proved by: data/suite/quality.test.ts

Written in: data/sql/quality/test.ts

Tests: passed: suite/quality.test.ts the status of a number of failures is judged as dbt judges it: severity, warnIf and errorI

FR-004checkpassing

The status of a count MUST be judged as dbt does: with severity error a count that meets errorIf fails and below it one that meets warnIf warns; with severity warn either threshold warns.

Enforced (check): .github: cd commons/data && bun run verify — suite/quality.test.ts: the status of a number of failures is judged as dbt judges it: severity, warnIf and errorIf

Proved by: data/suite/quality.test.ts

Written in: data/sql/quality/test.ts

Tests: passed: suite/quality.test.ts the status of a number of failures is judged as dbt judges it: severity, warnIf and errorI

FR-005checkpassing

The built-in tests (not_null, unique, accepted_values, relationships) and the package tests (expression_is_true, unique_combination_of_columns, not_empty_string, accepted_range, at_least_one, not_constant, equal_rowcount, equality) MUST give the rows that are wrong, and singular tests MUST be SQL of one's own.

Enforced (check): .github: cd commons/data && bun run verify — suite/quality.test.ts: the built-in and the package tests, on each engine, count the rows that are wrong and pass where there are none

Proved by: data/suite/quality.test.ts

Written in: data/sql/quality/generic.ts, data/sql/quality/mod.ts

Tests: passed: suite/quality.test.ts the built-in and the package tests, on each engine, count the rows that are wrong and pass

FR-006checkpassing

Tests MUST run through a driver on SQLite, DuckDB and PostgreSQL and count the rows that are wrong, and a test that cannot run MUST be an error.

Enforced (check): .github: cd commons/data && bun run verify — suite/quality.test.ts: the built-in and the package tests, on each engine, count the rows that are wrong and pass where there are none

Proved by: data/suite/quality.test.ts

Written in: data/sql/quality/mod.ts, data/sql/quality/run.ts

Tests: passed: suite/quality.test.ts the built-in and the package tests, on each engine, count the rows that are wrong and pass

Open: issue data 00010: Data loads, extracts and ETL/ELT: connectors and native bulk paths are left

FR-007checkpassing

where, limit and failCalc MUST apply, failures MUST be stored as a table or a view that a later run replaces, and a disabled test MUST be skipped.

Enforced (check): .github: cd commons/data && bun run verify — suite/quality.test.ts: where, limit, failCalc and stored failures: the tests are looked at through the filter, the failing rows are counted up to the limit or by the expression, and stored as a table or a view that a second run replaces

Proved by: data/suite/quality.test.ts

Written in: data/sql/quality/run.ts

Tests: passed: suite/quality.test.ts where, limit, failCalc and stored failures: the tests are looked at through the filter, th

FR-008checkpassing

Results MUST be counted (a warning is not a failure) and told as TAP.

Enforced (check): .github: cd commons/data && bun run verify — suite/quality.test.ts: a test that cannot run is an error, not a pass; the results are counted, good where nothing failed and nothing erred (a warning is not a failure), and told as TAP

Proved by: data/suite/quality.test.ts

Written in: data/sql/quality/mod.ts, data/sql/quality/run.ts

Tests: passed: suite/quality.test.ts a test that cannot run is an error, not a pass; the results are counted, good where nothin

FR-009checkpassing

The tests a schema's state asks for (not null, keys, codes) MUST be made from it and pass on a database that holds the schema.

Enforced (check): .github: cd commons/data && bun run verify — suite/quality.test.ts: the tests a schema's state asks for (not null, keys, codes) pass on a database that holds the schema and fail on one that was loaded around it

Proved by: data/suite/quality.test.ts

Written in: data/sql/quality/derive.ts

Tests: passed: suite/quality.test.ts the tests a schema's state asks for (not null, keys, codes) pass on a database that holds

FR-010checkpassing

A test MUST be able to expect an exact number of failing rows (exactly(n)): it MUST fail where the count is any other, fewer or more, and MUST never be a warning; exactly(0) is the ordinary test.

Enforced (check): .github: cd commons/data && bun run verify — suite/quality.test.ts: a test can expect an exact number of failing rows (it fails on one fewer and on one more and is never a warning), and a set of proposed tests is previewed on the data that is already there, with a sample of the rows each would reject, changing nothing

Proved by: data/suite/quality.test.ts

Tests: passed: suite/quality.test.ts a test can expect an exact number of failing rows (it fails on one fewer and on one more a

FR-011checkpassing

preview MUST run a set of proposed tests on the data that is already in a database and report, for each, its status, how many rows would be wrong and up to a given number of them, MUST report a test that cannot run as an error, and MUST change nothing.

Enforced (check): .github: cd commons/data && bun run verify — suite/quality.test.ts: a test can expect an exact number of failing rows (it fails on one fewer and on one more and is never a warning), and a set of proposed tests is previewed on the data that is already there, with a sample of the rows each would reject, changing nothing

Proved by: data/suite/quality.test.ts

Tests: passed: suite/quality.test.ts a test can expect an exact number of failing rows (it fails on one fewer and on one more a

Out of scope

  • dbt's unit tests of models and its freshness checks of sources (a later part); the load of data (ETL and ELT) is the integration part that follows. A test is not a model build: tests of transforms stay as they are (0023).

Edge cases

  • A dialect without the feature, an empty list, or a name that needs quoting: each is an error value or is quoted, never a silent omission, per FR-001.

Assumptions

  • The engines of the tests are SQLite, DuckDB and PGlite (PostgreSQL), in process.

Open questions

  • None.

Key entities

  • Data test - SQL that gives the rows that are wrong, with a level, typed arguments and a configuration; a run gives an outcome of pass, warn, fail, error or skipped.

Success criteria

SC-001success criterion

Each requirement above is proved by a test that runs on real engines, and bun run verify is green.

Review & acceptance checklist

  • [x] Every requirement is testable (MUST / MUST NOT), not aspirational
  • [x] No company fact is asserted here
  • [x] Every open item is marked, not silently decided

Cited by

Data, sql/quality/derive, sql/quality/generic, sql/quality/run, sql/quality/test, sql-loads

Titles, headings, exports, tables, columns and requirements. Esc closes.