Import and Export Concepts

Validate Rows

Report Before Loading

A staging query can label rows as loadable or rejected before any final insert happens.

Program

Play the script to choose the minimum allowed age and inspect the validation report.

validate_staged_rows.sql 
CREATE TABLE staged_people (name TEXT, age_text TEXT);
INSERT INTO staged_people VALUES ('Ada', '31'), ('Lin', '17'), ('Nia', 'bad'), ('Mira', '42');
WITH params(min_age) AS (VALUES ()) SELECT name, age_text, CASE WHEN age_text <> '' AND age_text NOT GLOB '*[^0-9]*' AND CAST(age_text AS INTEGER) >= (SELECT min_age FROM params) THEN 'load' ELSE 'reject' END AS decision FROM staged_people ORDER BY name;

CREATE TABLE staged_people (name TEXT, age_text TEXT);
INSERT INTO staged_people VALUES ('Ada', '31'), ('Lin', '17'), ('Nia', 'bad'), ('Mira', '42');
WITH params(min_age) AS (VALUES ()) SELECT name, age_text, CASE WHEN age_text <> '' AND age_text NOT GLOB '*[^0-9]*' AND CAST(age_text AS INTEGER) >= (SELECT min_age FROM params) THEN 'load' ELSE 'reject' END AS decision FROM staged_people ORDER BY name;

CREATE TABLE staged_people (name TEXT, age_text TEXT);
INSERT INTO staged_people VALUES ('Ada', '31'), ('Lin', '17'), ('Nia', 'bad'), ('Mira', '42');
WITH params(min_age) AS (VALUES ()) SELECT name, age_text, CASE WHEN age_text <> '' AND age_text NOT GLOB '*[^0-9]*' AND CAST(age_text AS INTEGER) >= (SELECT min_age FROM params) THEN 'load' ELSE 'reject' END AS decision FROM staged_people ORDER BY name;
validation The report labels each staged row before it is loaded.
numeric check `GLOB` rejects text that contains non-digits before casting.
threshold The selector changes the minimum age rule used by the report.