Data Quality Testing: 7 Essential Tests

Getting started with data quality testing?

Here are the 7 must-have checks to improve data quality and ensure reliability for your most critical assets.

When it comes to data engineering, data quality issues are a fact of life.

According to Gartner, bad data costs organizations on average an estimated $12.9 million per year. In fact, Monte Carlo’s own research found that data engineers spend as much as 40% of their workday firefighting bad data.

Those are some big numbers. Data quality issues are some of the most pernicious challenges facing modern data teams, and testing is one of the very first steps a data team will take on their journey to reliable data.

Whether by mistake or entropy, anomalies are bound to occur as your data moves through your production pipelines. In this post, we’ll look at 7 essential data quality tests you need right now to validate your data, plus some of the ways you can apply data quality testing today to start building out your data quality motion.

So, what is data quality testing?

Like all software and data applications, ETL/ELT systems are prone to failure from time-to-time. So, data quality testing is the process of validating that key characteristics of a dataset match what’s expected prior to consumption by downstream users.

Among other factors, data pipelines are reliable if:

The data is current, accurate, and complete.
The data is unique and free from duplicates.
The model is sound and represents reality.
And the transformed data is free from anomalies.

While there’s no silver bullet for data quality issues, data quality testing—also known as ETL testing—empowers engineers to anticipate specific, known problems and write logic to proactively detect quality issues before they can impact downstream users.

NULL values test

One of the most common data quality issues will arise from missing data, also known as NULL values. NULL values occur when a field is left blank, either intentionally or through a pipeline error, such as those caused by an API outage.

As the name implies, a NULL values test will validate whether values within a specified column for a particular model are missing after the model runs. One excellent out-of-the-box test for uncovering NULL values is dbt’s generic not_null test.

tests/test_not_null.sql

Freshness checks

All data definitely has a shelf life. When data is being refreshed at a regular cadence, the data paints an accurate picture of the data source. But when data becomes stale or outdated, it ceases to be reliable, and therefore, useful for downstream consumers.

Data freshness checks validate the quality of data within a table by monitoring how frequently that data is updated against predefined latency rules, such as when you expect an ingestion job to load on any given day.

Data freshness tests can be created manually using SQL rules. For example, let’s assume you are using Snowflake as your data warehouse and have integrated with Notification Services. You could schedule the following query as a Snowflake task.

CREATE TASK your_task_name
  WAREHOUSE = your_warehouse_name
  SCHEDULE = 'USING CRON 0 8 * * 1-5 America/New_York'
  TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
AS
SELECT
  CASE WHEN COUNT(*) = 0 THEN
    SYSTEM$SEND_SNS_MESSAGE(
      'your_integration_name',
      'your_sns_topic_arn',
      'No rows added in more than one day in your_table!'
    )
  ELSE
    'Rows added within the last day.'
  END AS alert_message
FROM your_table
WHERE date_column < DATEADD(DAY, -1, CURRENT_DATE());

Volume tests

Is data coming in? Is it too little? Too much? These are all data quality issues related to the volume of data entering your database.

Volume tests are a must-have quality check that can be used to validate the number of rows contained in critical tables.

Missing data

Let’s say your data platform processes data from temperature sensors, and one of those sensors fails. What happens? Missing data can quickly skew a data model or dashboard, so it’s important for your data quality testing program to identify quickly when data volume has changed due to missing data.

Too much data

Too much data might not sound like a problem but when rows populate out of proportion, it can slow model performance and increase compute costs. Monitoring data volume increases can help reduce costs and maintain the integrity of models by leveraging only clean high-quality data that will drive impact for downstream users.

Numeric distribution tests

Is my data within an accepted range? Are my values in-range within a given column? These are questions that can be answered using distribution tests.

Uniqueness tests

Another common quality issue that beleaguers data engineers is duplicate data. Uniqueness tests enable data teams to programmatically identify duplicate records to clean and normalize raw data before entering the production warehouse.

If you’re using dbt, you can use the unique test to validate your data for duplicate records.

Referential integrity tests

Referential integrity refers to the parent-child relationship between tables in a database. Referential integrity tests ensure that any data reflected in a child table has a corresponding parent table.

Conclusion

In summary, now that you have a few essential data quality tests, keep in mind that data reliability is a journey. The tests you implement should evolve as your data needs grow, adjusting beyond just basic checks to incorporate richer data observability solutions.