Guide
Data quality checks every BI team needs
The checks worth running before numbers reach a board pack, and what a failing check should change.
2 min read
Start with freshness
The most common data incident is not wrong data but old data presented as current. A freshness check asks one question: does this table hold rows as recent as it should? It catches a stalled sync, an expired credential or a pipeline that stopped quietly, before a dashboard full of last week's numbers misleads anyone.
In DataSquares, the Freshness tab of Data Quality monitors tables against the freshness you expect, and a card that reads a Warehouse dataset shows how fresh that dataset is.
Check the volume
Row counts are a blunt instrument that catches subtle failures. If an orders table usually grows by about 50,000 rows a day and today it grew by 3,000, something upstream broke: a truncated export, a half-finished migration, a filter someone added in the source system.
Set the expected range from recent history, and revisit it as the business grows or the seasons change. The Row count rule in DataSquares asserts that a table holds between a minimum and a maximum number of rows.
Nulls and uniqueness where they matter
Not every column needs a check; the keys do. Order IDs should be unique and never empty, and every customer reference should point at a real customer. Put rules on the handful of columns your joins and totals depend on, and you protect every metric built on them.
The Not null, Unique, Duplicate detection and Foreign key rules cover these cases.
Ranges and dates
Prices below zero, dates in the future, percentages above 100: each is a one-line rule. On their own they look trivial. Together they catch mistyped entries and unit mix-ups, such as cents loaded as dollars, before they average their way into a KPI.
No negative, Value range, Date range and No future date rules cover these, and a Custom SQL rule handles a check that spans several tables.
Make a failing check change something
A check that only writes a log line changes nothing. Decide in advance what a failure means: who looks at it, and whether the numbers may still be published.
In DataSquares, 14 rule types run on a schedule or whenever a source syncs, and every failure appears on the Issues tab. Severity sets the consequence: while a critical check fails, publishing the dashboards, reports and models built on that data is held, and lower severities warn without blocking. Checks run on PostgreSQL, MySQL, MariaDB, SQL Server, Azure SQL, Redshift and CockroachDB sources, and on uploaded files.