Data quality
The numbers looked right. Mars Climate Orbiter still disappeared.
A lost spacecraft is an extreme example of an ordinary data problem: a valid number can mean the wrong thing.
Published 4 min read
Jump to the four SQL checks
Photo: Mars Climate Orbiter - artist depiction, NASA/JPL, Public domain, via Wikimedia Commons.
On 23 September 1999, Mars Climate Orbiter reached the planet it had spent months travelling toward. Contact was lost during arrival. The spacecraft never began its science mission.
One team had supplied thruster data in English units. The navigation team expected metric. JPL's initial findings focused on the handoff and on the checks that failed to catch it. This was more than somebody forgetting a conversion.
A database accepts a number without knowing what you meant by it. Put 1299 in a price column and the insert can succeed whether you mean $12.99 or $1,299. The bad answer comes later, when somebody adds it up.
Four SQL data quality checks to run first
Start with missing values, categories, ranges and duplicates. These queries inspect a fictional signups table. Replace the names with yours and run them against a read replica or during a quiet period if the table is large: a full count or grouping can scan a lot of data.
1. Count the missing values
SELECT COUNT(*) AS rows_total,
COUNT(*) - COUNT(email) AS emails_missing
FROM signups;The difference counts NULLs. An empty string is a value, so it needs a separate check: WHERE email IS NULL OR TRIM(email) = ''. Decide whether those states mean the same thing before combining them.
2. Read the categories before filtering them
SELECT plan, COUNT(*) AS rows_per_plan
FROM signups
GROUP BY plan
ORDER BY rows_per_plan DESC;If your database compares these values case sensitively, Pro and pro appear as separate groups. Check the column collation before assuming they will. A surprise category may be a typo, an old plan or a real distinction. Ask before replacing it.
3. Check the range against a known example
SELECT MIN(created_at) AS earliest_signup,
MAX(created_at) AS latest_signup
FROM signups;Dates before the product existed deserve a look. So do dates in the future. For money or measurements, compare a record with its source document and confirm the unit. MIN and MAX can expose an implausible range; they cannot prove that every value uses the same unit.
4. Test the key the business actually uses
SELECT email, COUNT(*) AS occurrences
FROM signups
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;This finds repeated, non-NULL email values under the database's comparison rules. It does not prove you have duplicate people. Shared addresses, imports and repeat registrations need a business decision, not an automatic DELETE.
Write down the assumption that SQL cannot check
A useful handoff names the unit, time zone, allowed categories and expected key. For a price, amount_cents says more than amount. Include one known record and its expected value, so the next person can check the interpretation as well as the arithmetic.
That is where the spacecraft comparison stops. These queries are a starting point for inspecting tables, not a substitute for interface testing or an investigation of a navigation system.
Questions that come up while profiling a table
- How do I count NULL values in SQL?
- Use
COUNT(*) - COUNT(column_name)or count rows withWHERE column_name IS NULL. Empty strings are not NULLs. - Can SQL detect mixed units?
- Only if you supply the rule or reference data. A range check may flag outliers, but plausible values can still use the wrong unit. Keep units explicit at import time.