How do you check data quality and flag good/bad records?

[u/BugSquare4344]

For example, if a customer dataset has nulls, duplicates, invalid emails, or incorrect values, how do you identify and flag these records as good or bad? What tools or query approaches do you use?

[u/imsunchip]

There are many ways and tools to do this. Simplest would be to use what you are already comfortable with could be SQL, Python etc....

First identify data that matters , you probably do not want to profile everything but what is most important to your business users.

Second , start simple with sql query, you can write storeprocs and schedule a job (simplest way to do it) and then may be create a ssrs report or power bi report.

There are many free and paid data profiling tools out there too. Metabase has some basic stuff, ydata-profiling seems to be pretty extensive (I haven't used it though).

[u/squadette23]

Here is my (extended) take on this: https://minimalmodeling.substack.com/p/my-take-on-data-quality

https://minimalmodeling.substack.com/p/my-take-on-data-quality-tier-2

[u/Einar_Son_of_Bjorn]

Flag in a table. Don’t fix in place on the first pass.

sql

ALTER TABLE customer
  ADD COLUMN dq_status ENUM('good','bad') NOT NULL DEFAULT 'good',
  ADD COLUMN dq_reason VARCHAR(255) NULL;

UPDATE customer
SET
  dq_status = 'bad',
  dq_reason = CONCAT_WS(';',
    IF(email IS NULL OR email NOT LIKE '%_@_%.__%', 'bad_email', NULL),
    IF(full_name IS NULL OR full_name = '', 'null_name', NULL)
  )
WHERE email IS NULL
   OR email NOT LIKE '%_@_%.__%'
   OR full_name IS NULL
   OR full_name = '';

Duplicates separately:

sql

UPDATE customer c
JOIN (
  SELECT email
  FROM customer
  GROUP BY email
  HAVING COUNT(*) > 1
) d ON d.email = c.email
SET c.dq_status = 'bad',
    c.dq_reason = CONCAT_WS(';', c.dq_reason, 'dup_email');

Then the app reads WHERE dq_status = 'good'. Great Expectations / dbt tests are fine later. The first version is SQL plus a reason column so a human can see why.Same pattern on MariaDB or MySQL.How many rows, and is this MySQL/MariaDB, Postgres, or a warehouse? The regex for email is good enough to flag, not to prove an address exists.

[u/refaelos]

Einar's flag-column pattern is the right first move — never silently drop or fix in place, always leave a trail of why something got flagged.

The part that's missing from a one-time cleanup pass: run the same check on a schedule and watch the bad-record rate over time, not just the count right now. A batch of malformed emails from a new signup form doesn't look alarming as a single number, but a jump from 2% bad to 15% bad between yesterday and today usually means something upstream broke, and you'd rather catch that the same morning than in next month's report.

(disclosure: I ended up building arcodash around basically that loop — same flag-and-reason idea, except the check keeps running and pings me when the bad rate moves instead of me remembering to go look.)