Data Quality Checks

Required Fields

Find Missing Values

Quality checks often start by normalizing blanks and reporting rows that miss required fields.

Program

Play the script to choose a customer status and see which rows fail the required-field rules.

required_field_report.sql 
CREATE TABLE staged_customers (id INTEGER, name TEXT, email TEXT, status TEXT);
INSERT INTO staged_customers VALUES (1, 'Ada', 'ada@example.com', 'active'), (2, 'Lin', NULL, 'active'), (3, '', 'mira@example.com', 'pending'), (4, 'Nia', '  ', 'pending');
WITH params(wanted_status) AS (VALUES ()), checked AS (SELECT id, status, trim(coalesce(name, '')) AS clean_name, trim(coalesce(email, '')) AS clean_email FROM staged_customers WHERE status = (SELECT wanted_status FROM params)), problems AS (SELECT id, CASE WHEN clean_name = '' THEN 'missing_name' WHEN clean_email = '' THEN 'missing_email' ELSE 'ok' END AS quality_status FROM checked) SELECT id, quality_status FROM problems WHERE quality_status <> 'ok' ORDER BY id;

CREATE TABLE staged_customers (id INTEGER, name TEXT, email TEXT, status TEXT);
INSERT INTO staged_customers VALUES (1, 'Ada', 'ada@example.com', 'active'), (2, 'Lin', NULL, 'active'), (3, '', 'mira@example.com', 'pending'), (4, 'Nia', '  ', 'pending');
WITH params(wanted_status) AS (VALUES ()), checked AS (SELECT id, status, trim(coalesce(name, '')) AS clean_name, trim(coalesce(email, '')) AS clean_email FROM staged_customers WHERE status = (SELECT wanted_status FROM params)), problems AS (SELECT id, CASE WHEN clean_name = '' THEN 'missing_name' WHEN clean_email = '' THEN 'missing_email' ELSE 'ok' END AS quality_status FROM checked) SELECT id, quality_status FROM problems WHERE quality_status <> 'ok' ORDER BY id;
normalize blanks `trim(coalesce(..., ''))` treats NULL, empty strings, and whitespace consistently.
rule labels The `CASE` expression turns each failed rule into a readable status.
focused report The selector narrows the quality report to one import status.