A report can be built with the best technique and still produce wrong numbers if the starting data is wrong. Data quality is often treated as a vague problem, «the data is dirty», that nobody knows how to tackle. It becomes manageable when you break it down into precise dimensions, each measurable with a concrete check.
The six dimensions
Completeness
Is the data that should be there actually there? A customer register in which 40% of records lack an email is incomplete for any activity involving email communication, even if every single record is correct.
SELECT
COUNT(*) AS total,
COUNT(email) AS with_email,
ROUND(100.0 * COUNT(email) / COUNT(*), 1) AS percentage
FROM customers;
Completeness should be assessed against use: a missing fax number is not a problem, a missing shipping code is.
Validity
Do the values follow the expected format and rules? An Italian postcode has five digits, a percentage lies between 0 and 100, a date of birth is not in the future.
SELECT id, postcode FROM customers
WHERE postcode IS NOT NULL AND postcode NOT SIMILAR TO '[0-9]{5}';
SELECT id, birth_date FROM customers
WHERE birth_date > CURRENT_DATE;
Regular expression syntax varies between databases, but the principle is the same: look for values that break the rule.
Uniqueness
Does each real-world entity appear only once? The same customer recorded twice under two different codes makes customers look more numerous and splits their purchase history in two.
SELECT LOWER(TRIM(email)) AS email_norm, COUNT(*)
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;
Normalising the value before comparing, by removing spaces and capitals, reveals duplicates that an exact comparison would miss.
Consistency
Does related data agree? An order should not refer to a non-existent customer, a delivery date should not precede the order date, and an invoice total should equal the sum of its lines.
SELECT i.id, i.total, SUM(l.amount) AS lines_sum
FROM invoices i
JOIN invoice_lines l ON l.invoice_id = i.id
GROUP BY i.id, i.total
HAVING i.total <> SUM(l.amount);
Accuracy
Does the data match reality? This is the hardest dimension, because a value can be valid, complete and unique, yet wrong: a real address that is no longer the customer's. Accuracy is checked by comparing against a reference source: a sample check, confirmation from the customer, a comparison with an official register.
Timeliness
Is the data up to date enough for the use it is put to? Stock levels updated once a week are not enough for an online shop selling in real time.
SELECT MAX(updated_at) AS last_update FROM stock_levels;
A simple but extremely useful check is to verify that the last load happened within the expected time: a data feed that has silently stopped is one of the most frequent problems.
From measurement to prevention
Measuring is the first step, but endlessly correcting downstream is expensive. The general rule is to intervene as close as possible to the point where the data is created.
| Dimension | Prevention at the source |
|---|---|
| Completeness | NOT NULL columns, mandatory form fields |
| Validity | Correct data types, CHECK constraints, drop-down menus instead of free text |
| Uniqueness | UNIQUE constraints on normalised values, customer lookup before creation |
| Consistency | Foreign keys, totals calculated by the system rather than by hand |
| Accuracy | Confirmation of data by the person concerned, address verification |
| Timeliness | Automatic alerts when a load does not arrive |
Building a small system of checks
You do not need dedicated software to get started. A practical method:
- Choose the critical tables, the ones that decisions, invoices or communications depend on.
- Write one query per rule, returning the rows that break it. A query returning zero rows is a passed check.
- Run the checks automatically, for example every night, saving the date, the check name and the number of non-compliant rows in a table.
- Watch the trend: a number that suddenly rises signals a change upstream, such as a new form or a faulty import.
- Assign an owner to each check: a problem that concerns everyone ends up being solved by no one.
In my work I have found that the most useful rules are almost always those suggested by the people who use the data every day: they know perfectly well which errors keep recurring, even if they have never written them down.
In short
- Data quality breaks down into completeness, validity, uniqueness, consistency, accuracy and timeliness.
- Each dimension is measured with concrete queries that look for non-compliant rows.
- Accuracy requires a comparison with reality, not just with rules.
- Prevention at the source, with constraints and well-designed forms, costs less than correction.
- Automated checks with history and owners turn the problem into a process.
