Enough SQL to check your own database

You don't need to master SQL to check the quality of a master data set. Five query patterns cover almost all of the daily checking, and they all use the same handful of features: count, group, filter, sort, and join.

1. How much is empty where it shouldn't be

The pattern is to count the total and count the filled-in values in the same pass, using a conditional sum.

Count how many records exist, how many have the required field filled in, and divide. Treat the empty string and blank space as missing: in an old database, a "filled-in" field containing a space is common, and ignoring that inflates the metric.

Also return the rounded percentage. Nobody reads a number with four decimal places.

2. Where there are duplicates by business key

Group by the key the business recognizes (CNPJ, Brazil's company registration number; employee ID; code) and show only the groups with more than one record, sorted from largest to smallest.

Two traps. Normalize before grouping: strip punctuation and spaces, and standardize capitalization, or "12.345" and "12345" count as distinct. And remember that a null doesn't group with another null: records without a key don't show up here and need a count of their own.

3. Which values fall outside the expected domain

Group by the field and look at the list of distinct values with the frequency of each. It's the cheapest query and the one that reveals the most: "SP", "sp", "São Paulo", and "S.P." show up side by side.

Sort by descending frequency. The rare values at the end of the list are, almost always, the errors.

4. What doesn't match between two tables

Join the two on the key, keeping all the records from the first, and filter for the ones that found no match. That's how you find a customer with an order that doesn't exist, or a product with no group.

Count before listing. If the result has ten thousand rows, the problem is with the modeling or the scope, not the data, and the long list will hide that.

5. How old the data is

Compare the last-updated date with today's date and distribute the records into bands: up to 30 days, up to 180, up to a year, older than that.

The distribution says more than the average. A database with half updated yesterday and half untouched for three years has an acceptable average and a serious problem.

How to use this without it turning into a useless report

Save the five queries per domain and always run them on the same cadence. Comparing today's measurement with last week's is what turns a number into a trend.

And always deliver two things together: the percentage and the list of records that failed. The percentage is for tracking; the list is what the owning area can actually use. An indicator without an exception list fixes nothing.