Shape / inspect · All operations

Flag invalid values

A variant of Check data quality

Keep every row and add a column marking whether its value is valid, such as an error, a malformed geometry or a bad email address.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Flag invalid values and keep every row

SQL
CREATE TEMP TABLE contacts AS SELECT * FROM (VALUES
  (1, 'ada@example.com', '5',   '2026-03-01'),
  (2, 'not an email',    '12',  '2026-03-02'),
  (3, 'cy@example',      'x',   '2026-02-30'),
  (4, NULL,              NULL,  NULL),
  (5, 'di@example.org',  '7.5', '2026-03-05')
) t(id, email, quantity, shipped);
-- a flag is true or false and never NULL: a missing value is not valid
CREATE TEMP TABLE flagged AS
SELECT *,
       coalesce(regexp_full_match(email, '[^@\s]+@[^@\s]+\.[A-Za-z]{2,}'), false) AS email_ok,
       TRY_CAST(quantity AS DOUBLE) IS NOT NULL AS quantity_ok,
       TRY_CAST(shipped AS DATE) IS NOT NULL AS shipped_ok
FROM contacts;
SELECT * FROM flagged ORDER BY id;
SELECT CASE WHEN ((SELECT count(*) FROM flagged) = 5 AND (SELECT list(id ORDER BY id) FROM flagged WHERE email_ok) = [1, 5] AND (SELECT list(id ORDER BY id) FROM flagged WHERE quantity_ok) = [1, 2, 5] AND (SELECT list(id ORDER BY id) FROM flagged WHERE shipped_ok) = [1, 2, 5] AND (SELECT count(*) FROM flagged WHERE email_ok IS NULL OR quantity_ok IS NULL OR shipped_ok IS NULL) = 0 AND (SELECT list(column_name) FROM (DESCRIBE flagged)) = ['id', 'email', 'quantity', 'shipped', 'email_ok', 'quantity_ok', 'shipped_ok']) THEN 'as expected' ELSE error('all five rows should be kept with a flag for the email, the quantity and the ship date, true for rows 1 and 5 on the email and 1, 2 and 5 on the other two') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “all five rows should be kept with a flag for the email, the quantity and the ship date, true for rows 1 and 5 on the email and 1, 2 and 5 on the other two”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache Hop (2)
  • Credit card validatorread 2026-09-27

    Check a card number with the Luhn algorithm and identify the card vendor

  • Mail validatorread 2026-09-27

    Check that an email address is valid, optionally by SMTP lookup

Duckle (2)
OpenRefine (2)
  • Duplicates facetread 2026-09-27

    Mark each row true or false by whether its value in the column occurs more than once

  • Facet by errorread 2026-09-27

    Split rows true or false by whether the cell holds a stored error

Pentaho Data Integration (2)
subtoken (1)
  • subtoken_is_truncatedread 2026-09-27

    Report whether subtoken_embed would drop part of a text to fit its input limit

Tableau Prep (2)
  • Assign data roleread 2026-09-27

    Mark a field as Email, URL or a geographic role; values invalid for the role are flagged

  • Publish as Data Roleread 2026-09-27

    Save a field's values as a custom data role to validate other fields against

Trifacta (1)

Background

An advisory validation action typically allows data to be entered unchanged while reporting the validation issues it found.[1] Apache Hop's Credit card validator writes a Boolean result field saying whether each card number is valid, using the Luhn (MOD-10) algorithm.[2] In OpenRefine, a Duplicates facet marks each cell true or false by whether its value is not unique in the column.[3]

When to use it

An advisory action is most suitable for non-interactive systems, for changes that are not business critical and for cleansing steps on existing data.[1] Flag rather than drop when the rows are still needed downstream, for a count, a review or a fix, and let a later step decide what to do with them. (Meridian's view)

In DuckDB, regexp_matches returns true if the string merely contains the pattern, unless the pattern is anchored with ^ and $.[4] Use a full match, or anchor the pattern, when a flag should say that the whole value has the expected form. (Meridian's view) Apache Hop's Mail validator checks only an email address's structure, with a regular expression, unless its Simple Mail Transfer Protocol (SMTP) check is selected.[5] A flag from a pattern says that a value has the right form, not that it is real; an address can match and still not exist. (Meridian's view)

DuckDB uses three-valued logic with true, false and NULL, and NOT applied to NULL gives NULL.[6] Make every flag true or false and never NULL, deciding on purpose whether a missing value counts as valid. (Meridian's view)

The method

In DuckDB, TRY_CAST never throws an error and instead returns NULL if a cast is not possible.[7] Testing whether TRY_CAST of a value is NULL gives a type flag, but keep a separate flag for a value that was missing, so the two problems can be told apart. (Meridian's view)

Apache Hop's Credit card validator can also write the card vendor to one output field and an error message to another.[2] Add a reason beside each flag where the rule has more than one way to fail, so a reader can act on a flagged row without re-running the check. (Meridian's view)

In OpenRefine, a transformation can store an error in a cell instead of its original value, and a facet by error then finds those cells.[3]

Sources

  1. [1]Data validation - Wikipediasecondary source
  2. [2]Credit card validator | Apache Hopdocumentation
  3. [3]Exploring facets | OpenRefinedocumentation
  4. [4]Regular Expressions – DuckDBdocumentation
  5. [5]Mail Validator | Apache Hopdocumentation
  6. [6]Logical Operators – DuckDBdocumentation
  7. [7]Casting – DuckDBdocumentation