How to turn business requirements into SQL data quality rules, with evidence

Step 3. Test every rule on its own examples, then run it

test_rule builds a small table from the example rows and checks that the rule flags exactly the bad ones. A rule that cannot fire, or fires on good rows, fails the self-check, and no evidence file is written. run_rule then counts, for each year, the rows tested and the rows failed, and lists the first three failing row numbers. It wraps the condition in COALESCE(..., TRUE), which is what turns an unknown into a failure.

def test_rule(con, req):
    """Run the rule on its own example rows. Returns a problem text, or '' if it flags exactly the bad ones."""
    rows = [("good", o) for o in req["good"]] + [("bad", o) for o in req["bad"]]
    fixture_table(con, req, rows)
    expected = [i for i, (kind, _o) in enumerate(rows, start=1) if kind == "bad"]
    cond = sql_of(req).replace("{t}", "fx")
    got = [r[0] for r in con.sql(f"SELECT row_no FROM fx WHERE COALESCE(({cond}), TRUE) ORDER BY row_no").fetchall()]
    if got != expected:
        return f"{req['id']} flagged example rows {got}, expected exactly {expected}"
    return ""


def run_rule(con, req):
    cond = sql_of(req).replace("{t}", "data")
    tested = dict(con.sql("SELECT grp, COUNT(*) FROM data GROUP BY grp").fetchall())
    failed = dict(con.sql(f"SELECT grp, COUNT(*) FROM data WHERE COALESCE(({cond}), TRUE) GROUP BY grp").fetchall())
    result = {}
    for g in sorted(tested, key=str):
        first = [r[0] for r in con.execute(f"SELECT row_no FROM data WHERE grp IS NOT DISTINCT FROM ? "
                                           f"AND COALESCE(({cond}), TRUE) "
                                           f"ORDER BY row_no LIMIT 3", [g]).fetchall()]
        n, bad = tested[g], failed.get(g, 0)
        pct = 100.0 * bad / n
        result[str(g)] = {"tested": n, "failed": bad, "pct": pct, "tolerance": req["tolerance"],
                          "verdict": "Pass" if pct <= req["tolerance"] else "Fail", "first": first}
    return result

The evidence row carries a 12-character fingerprint of the SQL text, so a later run can show that the SQL did not change between two evidence files. The fingerprint does not cover the code lists, the tolerance or the examples, so keep those with the evidence. The script also checks itself: the rows it loaded equal the rows in the source, every rule passes its example rows, every transaction type in the data is in the list, and every code in the list matches at least one row.

Step 4. Run 1: the clean data

We ran the script on 2026-11-05 with DuckDB 1.5.6. The self-check passed and every rule passed in both cycles. Two rules flagged rows that stayed inside the owner’s tolerance: rule R3 (dates outside the cycle or unusable) flagged 2,143 rows in 2018 and 5,699 in 2020, and rule R6 (state code) flagged 1,722 and 1,419. The evidence keeps those counts, with row numbers to start from, instead of a bare “Pass”.

Command: run 1

python3 -I requirements_to_rules.py --data-dir <a new empty folder>

Output from our run 1

duckdb 1.5.6, python 3.12.15
source  https://www.fec.gov/files/bulk-downloads/data_dictionaries/pas2_header_file.csv  bytes=212  last-modified=Wed, 10 Jan 2018 18:30:12 GMT  sha256=9ce58b0576635ddb
source  https://www.fec.gov/files/bulk-downloads/2018/pas218.zip  bytes=12556308  last-modified=Sun, 25 Dec 2022 21:49:49 GMT  sha256=5fee204a918b4546
source  https://www.fec.gov/files/bulk-downloads/2020/pas220.zip  bytes=28978598  last-modified=Sun, 29 Dec 2024 22:08:51 GMT  sha256=7a8355e2cdae6c4b
total downloaded: 41535118 bytes

column profile: percent of rows with a blank value, by group
column                  2018      2020
CMTE_ID                 0.00      0.00
AMNDT_IND               0.00      0.00
RPT_TP                  0.00      0.00
TRANSACTION_PGI         0.48      0.21
IMAGE_NUM               0.00      0.00
TRANSACTION_TP          0.00      0.00
ENTITY_TP               1.62      0.58
NAME                    0.40      0.15
CITY                    0.47      0.15
STATE                   0.49      0.16
ZIP_CODE                0.51      0.17
EMPLOYER              100.00    100.00
OCCUPATION            100.00    100.00
TRANSACTION_DT          0.58      0.63
TRANSACTION_AMT         0.00      0.00
OTHER_ID                0.00      0.02
CAND_ID                 0.42      0.21
TRAN_ID                 0.00      0.05
FILE_NUM                0.00      0.00
MEMO_CD                98.11     98.51
MEMO_TEXT              86.39     94.76
SUB_ID                  0.00      0.00

rules (SQL fingerprint, then the SQL that is TRUE for a bad row)
  R1  d05eab9843aa  cand_id IS NULL AND other_id IS NULL
  R2  807224c224f2  amount IS NULL
  R3  f9ef81d911f8  txn_date IS NULL OR txn_date < MAKE_DATE(grp - 1, 1, 1) OR txn_date > MAKE_DATE(grp, 12, 31)
  R4  dea6770623a4  sub_id IS NULL OR (grp, sub_id) IN (SELECT grp, sub_id FROM {t} GROUP BY grp, sub_id HAVING COUNT(*) > 1)
  R5  6bd9f684edde  txn_type IS NULL OR txn_type NOT IN (SELECT code FROM txn_types)
  R6  fd2c55694c09  state IS NULL OR state NOT IN (SELECT code FROM us_codes)

rule  group  tested  failed  failed%  tolerance%  verdict  first failing row numbers
R1    2018    349820       0    0.000       0.100  Pass     -
R1    2020    887829       0    0.000       0.100  Pass     -
R2    2018    349820       0    0.000       0.000  Pass     -
R2    2020    887829       0    0.000       0.000  Pass     -
R3    2018    349820    2143    0.613       1.000  Pass     24, 25, 26
R3    2020    887829    5699    0.642       1.000  Pass     2, 3, 5
R4    2018    349820       0    0.000       0.000  Pass     -
R4    2020    887829       0    0.000       0.000  Pass     -
R5    2018    349820       0    0.000       0.000  Pass     -
R5    2020    887829       0    0.000       0.000  Pass     -
R6    2018    349820    1722    0.492       2.000  Pass     3988, 5967, 6522
R6    2020    887829    1419    0.160       2.000  Pass     2649, 2652, 5138

SELF-CHECK PASSED: row counts equal the source, all 6 rules flag exactly their own bad examples, every value of a required lookup is listed, and every listed code is used
wrote rule_evidence.csv

Download the evidence CSV (rule_evidence.csv)

Step 5. Run 2: plant faults and see what catches them

A rule set that has never failed has not been shown to work. We ran the same script with five faults planted: the recipient blanked on 2,000 rows of 2018, 500 rows of 2020 given the transaction type 99, the last 1,000 rows of 2018 loaded twice, rule R3’s SQL replaced with FALSE, and a code, 24X, that no row carries added to the transaction types. The script stops if the data already holds the code 99 or has fewer than 2,000 rows with a recipient to blank.

• The rules caught the data faults: R1 failed on 2,000 rows of 2018, R5 on 500 rows of 2020, and R4 on 2,000 rows of 2018 (the 1,000 duplicated rows and their originals).
• The self-checks caught the script faults: the row-count check tripped on the duplicated rows, the example-row check tripped on R3 (it flagged none of the three bad examples), the lookup check tripped on 99, and the unused-code check tripped on 24X.
• No evidence file was written. Note that R3 shows Pass in this run, with zero failures: only its example rows showed the rule was broken. A verdict alone cannot tell you that.

Command: run 2

python3 -I requirements_to_rules.py --data-dir <another new empty folder> --out-dir planted --plant duplicate-rows,unknown-type,blank-recipient,broken-rule,unused-code

Output from our run 2: faults reported, no CSV written

PLANTED: the SQL of rule R3 replaced with FALSE, so it can never fire
PLANTED: the code '24X' added to the transaction types
duckdb 1.5.6, python 3.12.15
source  https://www.fec.gov/files/bulk-downloads/data_dictionaries/pas2_header_file.csv  bytes=212  last-modified=Wed, 10 Jan 2018 18:30:12 GMT  sha256=9ce58b0576635ddb
source  https://www.fec.gov/files/bulk-downloads/2018/pas218.zip  bytes=12556308  last-modified=Sun, 25 Dec 2022 21:49:49 GMT  sha256=5fee204a918b4546
source  https://www.fec.gov/files/bulk-downloads/2020/pas220.zip  bytes=28978598  last-modified=Sun, 29 Dec 2024 22:08:51 GMT  sha256=7a8355e2cdae6c4b
total downloaded: 41535118 bytes
PLANTED: the recipient blanked on 2,000 rows of the 2018 file
PLANTED: 500 rows of the 2020 file given the transaction type '99'
PLANTED: the last 1,000 rows of the 2018 file loaded twice

column profile: percent of rows with a blank value, by group
column                  2018      2020
CMTE_ID                 0.00      0.00
AMNDT_IND               0.00      0.00
RPT_TP                  0.00      0.00
TRANSACTION_PGI         0.48      0.21
IMAGE_NUM               0.00      0.00
TRANSACTION_TP          0.00      0.00
ENTITY_TP               1.61      0.58
NAME                    0.40      0.15
CITY                    0.47      0.15
STATE                   0.49      0.16
ZIP_CODE                0.51      0.17
EMPLOYER              100.00    100.00
OCCUPATION            100.00    100.00
TRANSACTION_DT          0.59      0.63
TRANSACTION_AMT         0.00      0.00
OTHER_ID                0.57      0.02
CAND_ID                 0.99      0.21
TRAN_ID                 0.00      0.05
FILE_NUM                0.00      0.00
MEMO_CD                98.11     98.51
MEMO_TEXT              86.38     94.76
SUB_ID                  0.00      0.00

rules (SQL fingerprint, then the SQL that is TRUE for a bad row)
  R1  d05eab9843aa  cand_id IS NULL AND other_id IS NULL
  R2  807224c224f2  amount IS NULL
  R3  4e523a5ae5b4  FALSE
  R4  dea6770623a4  sub_id IS NULL OR (grp, sub_id) IN (SELECT grp, sub_id FROM {t} GROUP BY grp, sub_id HAVING COUNT(*) > 1)
  R5  6bd9f684edde  txn_type IS NULL OR txn_type NOT IN (SELECT code FROM txn_types)
  R6  fd2c55694c09  state IS NULL OR state NOT IN (SELECT code FROM us_codes)

rule  group  tested  failed  failed%  tolerance%  verdict  first failing row numbers
R1    2018    350820    2000    0.570       0.100  Fail     1, 2, 3
R1    2020    887829       0    0.000       0.100  Pass     -
R2    2018    350820       0    0.000       0.000  Pass     -
R2    2020    887829       0    0.000       0.000  Pass     -
R3    2018    350820       0    0.000       1.000  Pass     -
R3    2020    887829       0    0.000       1.000  Pass     -
R4    2018    350820    2000    0.570       0.000  Fail     348821, 348821, 348822
R4    2020    887829       0    0.000       0.000  Pass     -
R5    2018    350820       0    0.000       0.000  Pass     -
R5    2020    887829     500    0.056       0.000  Fail     1, 2, 3
R6    2018    350820    1727    0.492       2.000  Pass     3988, 5967, 6522
R6    2020    887829    1419    0.160       2.000  Pass     2649, 2652, 5138

SELF-CHECK FAILED: no evidence CSV written
  - group 2018: loaded 350820 rows but the source file has 349820 (+1000)
  - example rows: R3 flagged example rows [], expected exactly [4, 5, 6]
  - txn_types: '99' x 500 are not in the txn_types list
  - txn_types: the codes ['24X'] match no row in the data

Run it on your own data

To run it on your own table, export the table to a CSV file with a header row. In the script, set DATA_CSV to that file's path and GROUP_COLUMN to the column that holds each row's year or period. Replace everything inside REQUIREMENTS with your own requirements: for each one, put the sentence in text, a SQL condition that is true for a bad row and uses your own column names in bad_when, the percent of bad rows you accept in tolerance, and example rows in good and bad. Replace the values in BASELINE with one row of your own data that breaks no requirement, written as SQL values for your own columns, and keep grp and row_no in it. Replace everything inside LOOKUPS with your own code lists, or with {} if you have none. Then run the script without --data-dir.

The script prints the type it read each column as. DuckDB reads the whole file before it picks a type, so a column with even one value that is not a number or a date is read as text. A rule that compares such a column with a number cannot run on the data. The other rules still run, and the run ends with SELF-CHECK FAILED, a line that names the rule’s id, and no evidence CSV. To count those values as bad rows instead, write the column, here one named amount, as TRY_CAST(amount AS DECIMAL(14,2)) each time it appears in bad_when: a value that is not a number becomes NULL, the condition then comes out NULL, and a condition that comes out NULL counts the row as bad. If a date column is read as text, read it with the file’s own date format, for example TRY_STRPTIME(ship_date, '%m/%d/%Y')::DATE for dates written like 01/15/2022, and write that column’s values in BASELINE and in the example rows as text in the same format, such as '01/15/2022'. TRY_CAST(… AS DATE) reads only dates written like 2022-01-15.

We ran exactly these changes on a made-up orders file with other column names, a status written in the wrong case, a blank status, a blank customer, a negative amount, a duplicated id and a ship date before its year. Every planted error was reported. A lookup list that held a blank did not hide the unlisted status.