IF Commons GitHub

sql/quality/test

Written to: 4 requirements: 4 proved, 0 partly, 0 failing, 0 with no test recorded. 0052-sql-quality FR-001, 0052-sql-quality FR-002, 0052-sql-quality FR-003, 0052-sql-quality FR-004

SQL data quality, SQL-DQ (commons data 0052-sql-quality): data tests as typed values, an algebra of the data tests of dbt. A test is SQL that gives the rows that are wrong; it passes where there are none. A generic test is a value that is made once, with the type of its arguments and the level it is written at (a column or a model), and used any number of times; a singular test is SQL of one's own. The configuration of a use (severity, warnIf and errorIf thresholds, where, limit, failCalc, stored failures, tags, meta, name, enabled) is a typed record, and what a run finds is a sum of outcomes.

Pure: a test makes SQL for a dialect and runs nothing; run.ts runs it through a driver. It is a unit of its own because it is used by the project command, by models and by a person's own checks alike, and because nothing in it is about the shape of a schema.

Source: commons/data/sql/quality/test.ts

8 of 19 exports are imported by a test of the run. A test that imports an export may not call it or check what it returns.

columnTestconstno test imports it
const columnTest: <A = void>(name: string, failing: (c: Context<"column">, args: A) => string) => GenericTest<A, "column">

Makes a generic test of the level of a column.

exactlyconst
const exactly: (n: number, config?: TestConfig) => TestConfig

The configuration of a test that expects a number of failing rows, exactly: it fails where the count is any other, and is never a warning. exactly(0) is the ordinary test; exactly(3) is the book's seeded test (three rows are wrong and the test says so), which fails both where one of them is missed and where a new one appears.

ParameterWhat it is
nthe number of failing rows (or the value of failCalc) that is expected
configthe rest of the configuration

Imported by 7 tests of the run (7 passed, 0 failed): the library's results

DqTesttype
type DqTest = | { readonly kind: "generic"; readonly name: string; readonly test: string; readonly relation: Relation;… (401 characters)
type DqTest =
  | { readonly kind: "generic"; readonly name: string; readonly test: string; readonly relation: Relation; readonly column?: string; readonly args: unknown; readonly config: TestConfig; readonly failing: (d: Dialect) => string }
  | { readonly kind: "singular"; readonly name: string; readonly relation?: Relation; readonly config: TestConfig; readonly failing: (d: Dialect) => string };

A test that is placed: what it tests, and the SQL of the rows that are wrong.

Imported by 7 tests of the run (7 passed, 0 failed): the library's results

GenericTestinterfaceno test imports it
interface GenericTest<A, L extends Level>

A generic test: a name, a level, the type of its arguments and the SQL of the rows that are wrong.

MemberTypeWhat it is
kind"generic"
namestring
levelL
failing(c: Context<L>, args: A) => string
judgefunction
function judge(failures: number, config: TestConfig): "fail" | "pass" | "warn"

The status that a number of failures gives, as dbt determines it: with severity error, a number that meets errorIf is a failure and, below it, one that meets warnIf is a warning; with severity warn, a number that meets either threshold is a warning. Both thresholds are != 0 where they are not said.

Imported by 7 tests of the run (7 passed, 0 failed): the library's results

Leveltypeno test imports it
type Level = "column" | "model";

Where a generic test is written: on a column, or on a whole model.

meetsconstno test imports it
const meets: (n: number, t: Threshold) => boolean

Whether a number meets a threshold.

modelTestconstno test imports it
const modelTest: <A = void>(name: string, failing: (c: Context<"model">, args: A) => string) => GenericTest<A, "model">

Makes a generic test of the level of a model.

onfunction
function on(relation: string | Relation): OnRelation

Starts the tests of a relation; a generic test of a column cannot be put on a model, nor a test of a model on a column: it does not compile.

Imported by 15 tests of the run (15 passed, 0 failed): the library's results

OnRelationinterfaceno test imports it
interface OnRelation

The tests of one relation, written the way a project of dbt writes them: under a column, or under the model.

MemberTypeWhat it is
column(name: string, ...uses: readonly Use<"column">[]) => readonly DqTest[]Tests of a column.
model(...uses: readonly Use<"model">[]) => readonly DqTest[]Tests of the model.
Relationinterfaceno test imports it
interface Relation

A table or a view by the name it has in the database.

MemberTypeWhat it is
namestring
schema?string
selectfunction
function select(tests: readonly DqTest[], s: Selection): readonly DqTest[]

The tests that a selection picks: a test is picked when it meets every part the selection has (a tag that it has, a relation, a name, a kind) and is not picked by exclude.

Imported by 7 tests of the run (7 passed, 0 failed): the library's results

Selectioninterfaceno test imports it
interface Selection

The tests of a selection: by tag, relation, level or kind of test, by name, and what is left out.

MemberTypeWhat it is
tags?readonly string[]
relations?readonly string[]
tests?readonly string[]
kind?"generic" | "singular"
exclude?Selection
singularfunction
function singular(name: string, sql: string, config?: TestConfig, relation?: Relation | undefined): DqTest

A singular test: SQL of one's own that gives the rows that are wrong.

Imported by 7 tests of the run (7 passed, 0 failed): the library's results

TestConfiginterfaceno test imports it
interface TestConfig

What a use of a test is told: dbt's configs of a data test.

MemberTypeWhat it is
severity?"error" | "warn"error where it is not said.
warnIf?ThresholdWhere a test is a warning: != 0 where it is not said.
errorIf?ThresholdWhere a test is a failure, for severity error: != 0 where it is not said.
where?stringA condition that the rows tested must meet, written in the dialect.
limit?numberThe most failing rows that are looked at, and stored.
failCalc?stringThe expression that is compared with the thresholds: count(*) where it is not said.
storeFailures?boolean
storeFailuresAs?"table" | "view"
schema?stringThe schema the failures are stored in, and the name they are stored under (the name of the test where it is not said).
alias?string
tags?readonly string[]
meta?{ readonly [key: string]: string | number | boolean; }
enabled?booleanfalse takes the test out of a run: it is reported as skipped.
name?stringThe name of the test, where the one that is made from its parts is not wanted.
testNamefunction
function testName(test: string, relation: Relation, column: string | undefined, args: unknown): string

The name a test has where none is given: the test, the relation, the column and the arguments, as dbt makes it (accepted_values_orders_status__a__b).

Imported by 7 tests of the run (7 passed, 0 failed): the library's results

Thresholdinterfaceno test imports it
interface Threshold

How many failing rows (or the number failCalc gives) make a test warn or fail: { op: ">", n: 10 } is dbt's > 10.

MemberTypeWhat it is
op"!=" | "<" | "<=" | "=" | ">" | ">="
nnumber
usefunction
function use<A, L extends Level>(test: GenericTest<A, L>, ...rest: [A] extends [void] ? [args?: undefined, config?: TestConfig | undefined] : [args: A, config?: TestConfig | undefined]): Use<L>

Uses a generic test: its arguments (none to give where it takes none) and, last, its configuration.

use(acceptedValues, { values: ["a", "b"] }, { severity: "warn", where: "created > '2026-01-01'" });
use(notNull);

Imported by 15 tests of the run (15 passed, 0 failed): the library's results

Useinterfaceno test imports it
interface Use<L extends Level>

A use of a generic test, with its arguments and its configuration, still without a place.

MemberTypeWhat it is
testGenericTest<never, L>
argsunknown
configTestConfig

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