Data Quality Checks

Range Checks

Validate Imported Numbers

Imported text can be checked before casting, then compared against business limits.

Program

Play the script to choose a maximum amount and see which imported orders fail validation.

range_check_report.sql 
CREATE TABLE imported_orders (id INTEGER, amount_text TEXT, quantity INTEGER);
INSERT INTO imported_orders VALUES (1, '25', 2), (2, '40', 1), (3, 'bad', 4), (4, '80', 0), (5, '120', 3);
WITH params(max_amount) AS (VALUES ()), checked AS (SELECT id, amount_text, quantity, CASE WHEN amount_text <> '' AND amount_text NOT GLOB '*[^0-9]*' THEN CAST(amount_text AS INTEGER) END AS amount FROM imported_orders), issues AS (SELECT id, CASE WHEN amount IS NULL THEN 'bad_amount' WHEN quantity <= 0 THEN 'bad_quantity' WHEN amount > (SELECT max_amount FROM params) THEN 'amount_too_high' ELSE 'ok' END AS issue FROM checked) SELECT id, issue FROM issues WHERE issue <> 'ok' ORDER BY id;

CREATE TABLE imported_orders (id INTEGER, amount_text TEXT, quantity INTEGER);
INSERT INTO imported_orders VALUES (1, '25', 2), (2, '40', 1), (3, 'bad', 4), (4, '80', 0), (5, '120', 3);
WITH params(max_amount) AS (VALUES ()), checked AS (SELECT id, amount_text, quantity, CASE WHEN amount_text <> '' AND amount_text NOT GLOB '*[^0-9]*' THEN CAST(amount_text AS INTEGER) END AS amount FROM imported_orders), issues AS (SELECT id, CASE WHEN amount IS NULL THEN 'bad_amount' WHEN quantity <= 0 THEN 'bad_quantity' WHEN amount > (SELECT max_amount FROM params) THEN 'amount_too_high' ELSE 'ok' END AS issue FROM checked) SELECT id, issue FROM issues WHERE issue <> 'ok' ORDER BY id;
safe cast The `GLOB` check keeps non-numeric text from being cast as a valid amount.
rule order The `CASE` order chooses the first failure label for each row.
limit selector `max_amount` changes the business threshold while keeping the validation query shape.