# Flag invalid values

Shape / inspect

A variant of [Check data quality](https://meridian.online/operations/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.

Page: <https://meridian.online/operations/check-data-quality/flag-invalid-values>

## Status

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.

## What other tools call it

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

- Duckle: [`Check Empty Geometry`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/frontend/src/workflow-ui/palette-data.ts) (read 2026-09-25)
- Duckle: [`Validate Geometry`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/frontend/src/workflow-ui/palette-data.ts) (read 2026-09-25)
- Apache Hop: [`Credit card validator`](https://hop.apache.org/manual/latest/pipeline/transforms/creditcardvalidator.html) (read 2026-09-27)
- Apache Hop: [`Mail validator`](https://hop.apache.org/manual/latest/workflow/actions/mailvalidator.html) (read 2026-09-27)
- OpenRefine: [`Duplicates facet`](https://openrefine.org/docs/manual/facets#duplicates-facet) (read 2026-09-27)
- OpenRefine: [`Facet by error`](https://openrefine.org/docs/manual/facets#facet-by-error) (read 2026-09-27)
- Pentaho Data Integration: [`Credit card validator`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27)
- Pentaho Data Integration: [`Mail Validator`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27)
- subtoken: [`subtoken_is_truncated`](https://github.com/meridian-online/subtoken/blob/ceb61bd796b15b4d54ca3bb2db8a74792d67026e/README.md) (read 2026-09-27)
- Tableau Prep: [`Assign data role`](https://help.tableau.com/current/prep/en-us/prep_validate_data.htm) (read 2026-09-27)
- Tableau Prep: [`Publish as Data Role`](https://help.tableau.com/current/prep/en-us/prep_validate_data.htm) (read 2026-09-27)
- Trifacta: [`Extract mismatched`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27)