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.

min_age
validate_staged_rows.sql
Replay: real traced execution (multi-file project)
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 (18)) 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 (21)) 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 (40)) 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;

  1. tables ← 1 row

    1CREATE TABLE staged_people (name TEXT, age_text TEXT);2INSERT INTO staged_people VALUES ('Ada', '31'), ('Lin', '17'), ('Nia', 'bad'), ('Mira', '42');
    values this step1 rowtables

  2. staged_people ← 4 rows

    1CREATE TABLE staged_people (name TEXT, age_text TEXT);2INSERT INTO staged_people VALUES ('Ada', '31'), ('Lin', '17'), ('Nia', 'bad'), ('Mira', '42');3WITH params(min_age) AS (VALUES (18)) 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;
    values this step4 rowsstaged_people

  3. result ← 4 rows

    2INSERT INTO staged_people VALUES ('Ada', '31'), ('Lin', '17'), ('Nia', 'bad'), ('Mira', '42');3WITH params(min_age) AS (VALUES (18)) 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;
    values this step4 rowsresult

  1. tables ← 1 row

    1CREATE TABLE staged_people (name TEXT, age_text TEXT);2INSERT INTO staged_people VALUES ('Ada', '31'), ('Lin', '17'), ('Nia', 'bad'), ('Mira', '42');
    values this step1 rowtables

  2. staged_people ← 4 rows

    1CREATE TABLE staged_people (name TEXT, age_text TEXT);2INSERT INTO staged_people VALUES ('Ada', '31'), ('Lin', '17'), ('Nia', 'bad'), ('Mira', '42');3WITH params(min_age) AS (VALUES (21)) 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;
    values this step4 rowsstaged_people

  3. result ← 4 rows

    2INSERT INTO staged_people VALUES ('Ada', '31'), ('Lin', '17'), ('Nia', 'bad'), ('Mira', '42');3WITH params(min_age) AS (VALUES (21)) 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;
    values this step4 rowsresult

  1. tables ← 1 row

    1CREATE TABLE staged_people (name TEXT, age_text TEXT);2INSERT INTO staged_people VALUES ('Ada', '31'), ('Lin', '17'), ('Nia', 'bad'), ('Mira', '42');
    values this step1 rowtables

  2. staged_people ← 4 rows

    1CREATE TABLE staged_people (name TEXT, age_text TEXT);2INSERT INTO staged_people VALUES ('Ada', '31'), ('Lin', '17'), ('Nia', 'bad'), ('Mira', '42');3WITH params(min_age) AS (VALUES (40)) 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;
    values this step4 rowsstaged_people

  3. result ← 4 rows

    2INSERT INTO staged_people VALUES ('Ada', '31'), ('Lin', '17'), ('Nia', 'bad'), ('Mira', '42');3WITH params(min_age) AS (VALUES (40)) 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;
    values this step4 rowsresult
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.