377 SQL checks on 4.6 million generated rows: what they caught
In part 1 I showed the method: write every rule about your data as a sentence, then one SQL query that counts the rows breaking it. The rule holds when the query returns 0. I promised to run it at scale. So I did: 377 checks across 13 generated databases, 4,594,497 rows in total. Today every one of those queries returns 0. Getting there took several fixes, and this post covers the ones I'd never…
In this story, the author demonstrates how to write rules for data as sentences, then create SQL queries that count the number of rows breaking those rules. The author ran 377 checks across 13 generated databases, covering a total of 4,594,497 rows. After several fixes, all 377 queries returned 0, indicating the data was consistent. The author shares three key lessons learned from this process:
1. Per-row rules can't see issues like a large share of rows clustering around a specific date. Adding shape checks can help identify such problems.
2. Checking for a minimum number of rows per table can catch cases where generated data appears unrealistic, even if individual rows are valid.
3. Writing down rules explicitly, such as naming a column correctly, helps ensure the checks catch intended issues.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
