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.

duplicate_key_report.sql 
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 ()), 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 ()), 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;
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.