Data testing

What is data testing?

Data testing is the automated checking of data against rules you defined in advance. Each test states one expectation: order_id is never empty, customer_id is unique, every order points to an existing customer, status is one of the accepted values, or amount stays inside a sensible range.

When the test runs, the answer should be clear: pass, warn, or fail, with the rows that broke the rule.

The analogy with software testing is useful. Developers test code so defects are caught before users hit them. Data teams test tables so bad loads are caught before they reach a dashboard, model, export, or customer-facing workflow.

A test does not prove that the data is correct in every human sense. It only proves that the data follows the rule you wrote. If the rule is too weak, bad data still passes.

Common test types

Most data tests fall into familiar categories.

• Not null
  A required field may not be empty. Every order needs an order number. Every active customer needs a customer ID.

• Unique
  A key may occur only once. Duplicate order numbers usually mean a duplicate load, a source issue, or the wrong grain in the table.

• Accepted values and ranges
  A column must stay inside an allowed set or range. Status may be placed, shipped, or cancelled. Discount must be between 0 and 100 percent.

• Relationships
  A reference must point to something that exists. Every order's customer_id should exist in the customer table.

• Row count and volume
  The number of rows should be plausible. A table that usually gets 50,000 daily events but receives 12 rows needs attention.

• Freshness
  The data must be recent enough. A sales table that should refresh every morning should not have its newest row from three days ago.

• Schema
  Column names, data types, and required fields should not change without warning. Schema checks catch a source that renames total_amount to amount_total.

Where do tests run?

Tests are most useful inside the pipeline. Load data, transform it, test the result, and only then let the data move to the next layer or dashboard.

The benefit is fail fast. If a key test breaks after the silver layer, the gold layer and Power BI refresh can stop. A dashboard with yesterday's trusted numbers is usually better than a dashboard refreshed with half-broken data.

Some tests also run earlier, directly on the source extract, especially schema, freshness, and row-count checks. Others run after business logic has been applied, such as margin ranges, customer status rules, or referential integrity in a model.

Tools teams often use

dbt
dbt ships with common generic data tests such as unique, not_null, accepted_values, and relationships. You declare them in YAML, and dbt turns them into SQL that returns failing rows. Custom tests can be written as queries where zero returned rows means success.

Great Expectations and GX Core
Great Expectations, now often branded as GX Core, calls rules expectations. Expectations are grouped and run against batches of data, with validation results that can be documented and reviewed.

Soda
Soda uses SodaCL, a YAML-style language for checks such as missing values, duplicates, row counts, freshness, schema, and custom SQL. It is often used where teams want readable data-quality checks outside a single transformation framework.

The tool matters less than the habit: write the expectation down, run it automatically, and make failure visible to the person who can act.

An example on an orders table

Take an orders table with order_id, customer_id, status, amount, and order_date. A sensible first set of tests might be:

• order_id is never empty and is unique.

• customer_id exists in the customer table.

• status is placed, shipped, returned, or cancelled.

• amount is greater than or equal to zero and below a realistic upper bound.

• the newest order_date is not older than one day.

If the status test fails because a source suddenly sends awaiting_payment, you know exactly what changed. The fix may be to update the accepted values, map the new status, or ask the source owner why the contract changed.

Testing versus reconciliation

Data testing checks one dataset against rules. Does this table satisfy the expectations we wrote?

Data reconciliation compares two datasets with each other. Did the migration copy the same number of customers? Do source and warehouse totals match? Did revenue change between systems?

They complement each other. Reconciliation catches missing or changed data between two places. Testing catches rule violations inside one place.

Testing and data contracts

A data contract defines what a producer promises to consumers: schema, meaning, quality, freshness, and sometimes ownership or change process. Data tests are the enforcement layer behind that promise.

If the contract says customer_id is required, the pipeline needs a not-null test. If the contract says status has a fixed set of values, the pipeline needs an accepted-values test. If the producer breaks the promise, the tests fail before downstream users absorb the damage.

Testing also connects to data observability. Tests cover known rules. Observability watches broader patterns such as freshness, volume, schema changes, distribution shifts, and lineage impact.

What to watch out for

Too many low-value tests
Testing every column with weak rules creates noise. Start with keys, joins, financial measures, dates, and fields used in decisions.

Rules without owners
A failing test is only useful if someone knows whether to fix the data, the rule, or the source system.

Hard stops in the wrong place
Some failures should stop the pipeline. Others should warn and continue. Decide this per dataset and per consumer risk.

Tests can go stale
A rule that was correct last year may be wrong after a new product, region, or system change. Review tests when business definitions change.