Data Quality Checks

Duplicate Keys

Group Possible Matches

Duplicate checks group incoming rows by a business key and report keys that occur too often.

Program

Play the script to choose the minimum duplicate count and inspect repeated customer keys.

min_count
duplicate_key_report.sql
Replay: real traced execution (multi-file project)
CREATE TABLE customer_import (external_id TEXT, name TEXT);
INSERT INTO customer_import VALUES ('A100', 'Ada'), ('A100', 'Ada L'), ('B200', 'Lin'), ('C300', 'Mira'), ('C300', 'Mira M'), ('C300', 'Mira N');
WITH params(min_count) AS (VALUES (2)), grouped AS (SELECT external_id, COUNT(*) AS row_count, GROUP_CONCAT(name, ', ') AS names FROM customer_import GROUP BY external_id), duplicates AS (SELECT external_id, row_count, names FROM grouped WHERE row_count >= (SELECT min_count FROM params)) SELECT external_id, row_count, names FROM duplicates ORDER BY external_id;

CREATE TABLE customer_import (external_id TEXT, name TEXT);
INSERT INTO customer_import VALUES ('A100', 'Ada'), ('A100', 'Ada L'), ('B200', 'Lin'), ('C300', 'Mira'), ('C300', 'Mira M'), ('C300', 'Mira N');
WITH params(min_count) AS (VALUES (3)), grouped AS (SELECT external_id, COUNT(*) AS row_count, GROUP_CONCAT(name, ', ') AS names FROM customer_import GROUP BY external_id), duplicates AS (SELECT external_id, row_count, names FROM grouped WHERE row_count >= (SELECT min_count FROM params)) SELECT external_id, row_count, names FROM duplicates ORDER BY external_id;

  1. tables ← 1 row

    1CREATE TABLE customer_import (external_id TEXT, name TEXT);2INSERT INTO customer_import VALUES ('A100', 'Ada'), ('A100', 'Ada L'), ('B200', 'Lin'), ('C300', 'Mira'), ('C300', 'Mira M'), ('C300', 'Mira N');
    values this step1 rowtables

  2. customer_import ← 6 rows

    1CREATE TABLE customer_import (external_id TEXT, name TEXT);2INSERT INTO customer_import VALUES ('A100', 'Ada'), ('A100', 'Ada L'), ('B200', 'Lin'), ('C300', 'Mira'), ('C300', 'Mira M'), ('C300', 'Mira N');3WITH params(min_count) AS (VALUES (2)), grouped AS (SELECT external_id, COUNT(*) AS row_count, GROUP_CONCAT(name, ', ') AS names FROM customer_import GROUP BY external_id), duplicates AS (SELECT external_id, row_count, names FROM grouped WHERE row_count >= (SELECT min_count FROM params)) SELECT external_id, row_count, names FROM duplicates ORDER BY external_id;
    values this step6 rowscustomer_import

  3. result ← 2 rows

    2INSERT INTO customer_import VALUES ('A100', 'Ada'), ('A100', 'Ada L'), ('B200', 'Lin'), ('C300', 'Mira'), ('C300', 'Mira M'), ('C300', 'Mira N');3WITH params(min_count) AS (VALUES (2)), grouped AS (SELECT external_id, COUNT(*) AS row_count, GROUP_CONCAT(name, ', ') AS names FROM customer_import GROUP BY external_id), duplicates AS (SELECT external_id, row_count, names FROM grouped WHERE row_count >= (SELECT min_count FROM params)) SELECT external_id, row_count, names FROM duplicates ORDER BY external_id;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE customer_import (external_id TEXT, name TEXT);2INSERT INTO customer_import VALUES ('A100', 'Ada'), ('A100', 'Ada L'), ('B200', 'Lin'), ('C300', 'Mira'), ('C300', 'Mira M'), ('C300', 'Mira N');
    values this step1 rowtables

  2. customer_import ← 6 rows

    1CREATE TABLE customer_import (external_id TEXT, name TEXT);2INSERT INTO customer_import VALUES ('A100', 'Ada'), ('A100', 'Ada L'), ('B200', 'Lin'), ('C300', 'Mira'), ('C300', 'Mira M'), ('C300', 'Mira N');3WITH params(min_count) AS (VALUES (3)), grouped AS (SELECT external_id, COUNT(*) AS row_count, GROUP_CONCAT(name, ', ') AS names FROM customer_import GROUP BY external_id), duplicates AS (SELECT external_id, row_count, names FROM grouped WHERE row_count >= (SELECT min_count FROM params)) SELECT external_id, row_count, names FROM duplicates ORDER BY external_id;
    values this step6 rowscustomer_import

  3. result ← 1 row

    2INSERT INTO customer_import VALUES ('A100', 'Ada'), ('A100', 'Ada L'), ('B200', 'Lin'), ('C300', 'Mira'), ('C300', 'Mira M'), ('C300', 'Mira N');3WITH params(min_count) AS (VALUES (3)), grouped AS (SELECT external_id, COUNT(*) AS row_count, GROUP_CONCAT(name, ', ') AS names FROM customer_import GROUP BY external_id), duplicates AS (SELECT external_id, row_count, names FROM grouped WHERE row_count >= (SELECT min_count FROM params)) SELECT external_id, row_count, names FROM duplicates ORDER BY external_id;
    values this step1 rowresult
business key `external_id` is the value that should identify one customer.
COUNT `COUNT(*)` measures how many rows share the same key.
GROUP_CONCAT `GROUP_CONCAT` keeps the grouped names visible for inspection.