Shape / inspect · All operations

Report a quality scorecard

A variant of Check data quality

Run a set of rules and return one row per rule with its outcome, instead of stopping the run.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Report one row per rule with its outcome instead of stopping the run

It reads the orders and customers tables the proofs share. Create them first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

CREATE TABLE customers AS SELECT * FROM (VALUES
  ('Alice Ng',    'retail',    DATE '2025-06-01', 52.52, 13.40),
  ('Bob Okafor',  'wholesale', DATE '2025-09-15', 48.85,  2.35),
  ('Carla Diaz',  'retail',    DATE '2025-11-30', 40.42, -3.70),
  ('Frank Moore', 'retail',    DATE '2026-01-01', 51.51, -0.13)
) t(customer, segment, joined, lat, lon);

Then run the statement:

SQL
CREATE TEMP TABLE scorecard AS
SELECT rule, failing, checked, CASE WHEN failing <= allowed THEN 'pass' ELSE 'fail' END AS outcome
FROM (
  SELECT 'amount is present' AS rule, count(*) FILTER (WHERE amount IS NULL) AS failing, count(*) AS checked, 0 AS allowed FROM orders
  UNION ALL SELECT 'order_id is unique', count(*) - count(DISTINCT order_id), count(*), 0 FROM orders
  UNION ALL SELECT 'amount is positive', count(*) FILTER (WHERE amount <= 0), count(*), 0 FROM orders
  UNION ALL SELECT 'region is present (one gap allowed)', count(*) FILTER (WHERE region IS NULL), count(*), 1 FROM orders
  UNION ALL SELECT 'customer is a known customer', count(*) FILTER (WHERE c.customer IS NULL), count(*), 0 FROM orders o LEFT JOIN customers c USING (customer)
);
SELECT * FROM scorecard ORDER BY rule;
SELECT CASE WHEN ((SELECT count(*) FROM scorecard) = 5 AND (SELECT list((rule, failing, outcome) ORDER BY rule) FROM scorecard) = [('amount is positive', 0, 'pass'), ('amount is present', 1, 'fail'), ('customer is a known customer', 6, 'fail'), ('order_id is unique', 0, 'pass'), ('region is present (one gap allowed)', 1, 'pass')] AND (SELECT min(checked) FROM scorecard) = 10) THEN 'as expected' ELSE error('the scorecard should have five rows: two passes (positive amounts, unique ids), a pass for one allowed gap in region, and failures for the missing amount and the six unknown customers') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the scorecard should have five rows: two passes (positive amounts, unique ids), a pass for one allowed gap in region, and failures for the missing amount and the six unknown customers”.
  • 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).

AWS Glue (2)
  • Evaluate Data Qualityread 2026-09-27

    Evaluate data quality rules written in DQDL against a dataset and act on the result

  • EvaluateDataQualityread 2026-09-27

    Evaluate a data quality ruleset against a frame and return the results as a frame

Duckle (1)
  • Expectationsread 2026-09-25

    run a suite of expectations and emit a scorecard row per rule

OpenRefine (1)
  • Issue detectionread 2026-09-27

    Analyse the schema and list suggested fixes, such as statements that lack a reference

Talend (1)
  • tThresholdViolationAlertread 2026-09-27

    Find indicators in the data quality report database whose thresholds are violated

Background

Even when data validation finds no issues, a log of the validations conducted and their results is important.[1] Amazon Web Services (AWS) Glue's EvaluateDataQuality evaluates a ruleset and returns results with Rule, Outcome, FailureReason and EvaluatedMetrics columns.[2]

When to use it

A validation log helps identify missing data validation checks in the light of data issues.[1] Reach for a scorecard when a run should finish and say how good the data was, and for a stopping check when bad data must never reach the next step. (Meridian's view)

In dbt, a test returns a number of failures, most often the count of rows its query returns, and a nonzero number generally makes the test return an error.[3] In dbt, the error_if and warn_if settings take a condition on the number of failures, such as > 5 or between 5 and 10, so one test can warn at one level and error at another.[3] Give each rule an allowed number of failures and report the count beside the outcome, so a reader sees how far a rule failed and not only that it did. (Meridian's view)

The method

AWS Glue Data Quality rules are written in the Data Quality Definition Language (DQDL), passed to EvaluateDataQuality as a ruleset string.[2]

Monitoring data quality means keeping track of it over time and reporting variations in the quality of the data.[4] In DuckDB, countif(arg) returns the number of rows where arg is true.[5] Build the scorecard as one small query per rule, each giving the rule's name, the rows failing and the rows checked, stacked with UNION ALL, and keep each run's scorecard so quality can be followed from run to run. (Meridian's view)

Sources

  1. [1]Data validation - Wikipediasecondary source
  2. [2]EvaluateDataQuality class - AWS Gluedocumentation
  3. [3]severity, error_if, and warn_if | dbt Developer Hubdocumentation
  4. [4]Data quality - Wikipediasecondary source
  5. [5]Aggregate Functions – DuckDBdocumentation