Tools and Techniques for Testing Data Tables

Testing a data table means turning assumptions about its contents and relationships into assertions, then identifying the rows that violate them. Start with checks for required values, uniqueness, allowed values, valid references to related rows, and reasonable bounds. Use dbt when the rules fit SQL in an existing dbt project; use Great Expectations when you need a validation workflow across SQL databases, files, or dataframes. The right checks come from the data contract and business rules, not from a universal checklist.

This guide focuses on data contents and cross-table consistency. It does not establish whether a rendered web table is accessible or whether its sorting, filtering, or pagination work correctly; those require separate frontend testing guidance.

1. Define what “correct” means

Before choosing a tool, write down each rule in a way that can be checked. A useful data assertion has a scope, a condition, and a clear failure result. For example, “every order has a customer ID” is more actionable than “orders should be clean.”

Rule	Example assertion	Typical failure to inspect
Requiredness	customer_id is not null	Rows missing a customer reference
Uniqueness	order_id occurs once	Duplicate order identifiers
Allowed values	status belongs to the documented status set	Typos, unexpected states, or an outdated rule
Relationship	Every non-null customer_id matches a customer record	Orphaned records or mismatched keys
Bounds	A count or measure stays within an expected range	Missing batches, duplicates, or a changed business pattern

These are candidate checks, not universal requirements. A field may legitimately be optional, an identifier may only be unique within a partition, and a value range may vary by business context. Record the rule owner and the reason for the rule so a failed check can be distinguished from an invalid expectation.

2. Start with SQL: return the violating rows

A practical SQL test asks for counterexamples: write a query that returns rows which break the rule. The test passes when it returns no rows. This form works well in a SQL editor, a scheduled check, or a validation framework that treats returned rows as failures.

-- Required field: expected to return no rows
select *
from analytics.orders
where order_id is null;

-- Duplicate key: inspect every row belonging to duplicate IDs
with duplicate_ids as (
  select order_id
  from analytics.orders
  group by order_id
  having count(*) > 1
)
select o.*
from analytics.orders as o
join duplicate_ids as d using (order_id);

-- Allowed values: replace the set with the domain's actual statuses
select *
from analytics.orders
where status is null
   or status not in ('pending', 'paid', 'cancelled');

-- Orphaned references: use the actual parent and child key names
select o.*
from analytics.orders as o
left join analytics.customers as c
  on o.customer_id = c.customer_id
where o.customer_id is not null
  and c.customer_id is null;

SQL dialects differ in details. For composite identifiers, group by all key columns. For a nullable foreign key, decide whether null means “not applicable” or a failure, and encode that decision explicitly. For case-insensitive values, normalize before comparison only if the domain treats case as insignificant.

Make failures useful

• Select enough columns to identify and diagnose a failing record, while avoiding unnecessary sensitive data.
• Keep the rule narrow: a query for bad rows is easier to review than a single opaque pass/fail number.
• Separate a broken transformation from a bad source record and from an expectation that no longer matches the domain.
• For count or aggregate checks, report the observed value and the expected boundary in the surrounding job output.

3. Use dbt data tests for warehouse models

When a table is part of a dbt project and its rules express naturally in SQL, dbt data tests provide a way to attach checks to project resources. dbt documents built-in generic tests for non-null values, uniqueness, relationships, and accepted values. A test is a SQL query that looks for records disproving an assertion; a passing test returns no failing records. See the [dbt data tests documentation](https://docs.getdbt.com/docs/build/data-tests).

Reusable generic tests

Generic tests are appropriate when the same rule applies to many columns or models with small variations. A typical model configuration declares a column and the checks that should apply to it. Confirm exact syntax against the installed dbt version because its documentation and behavior can evolve.

# Example schema.yml pattern; verify syntax for your dbt version
version: 2

models:
  - name: orders
    columns:
      - name: order_id
        data_tests:
          - not_null
          - unique
      - name: status
        data_tests:
          - accepted_values:
              arguments:
                values: ['pending', 'paid', 'cancelled']
      - name: customer_id
        data_tests:
          - relationships:
              arguments:
                to: ref('customers')
                field: customer_id

Use a generic rule when it remains clear and reusable. Avoid forcing a complicated business condition into a parameterized test if a direct SQL query would be easier for the next person to understand.

One-off singular SQL tests

A singular test is useful for a rule that is specific to one model or business condition. Put the query in a dbt test file and make it return only violating records:

-- tests/orders_have_nonnegative_total.sql
select *
from {{ ref('orders') }}
where total_amount < 0;

The example is valid only if negative totals are prohibited by the domain. Refunds, reversals, or accounting conventions may make that assertion wrong. For reusable rules, prefer a generic test; for a focused custom condition, prefer a singular test. dbt documents tests for models and other resources, including sources, seeds, and snapshots. See [dbt’s data test concepts](https://docs.getdbt.com/docs/build/data-tests#data-test-properties).

Investigate and retain failures

When a test fails, inspect the violating rows and trace them through the source and transformation. dbt documents an option to store test failures in a database table for development-time investigation. Check the current versioned docs and your project configuration before relying on stored failures, and handle retained data according to your access and retention rules.

4. Use Great Expectations across tables, files, and dataframes

Great Expectations organizes checks as Expectations, which can be grouped into suites and validated against data batches. Its documented workflows cover SQL databases, filesystems, and dataframes. The setup differs by data source and Great Expectations version, so follow the current guide for the specific source rather than copying an old configuration wholesale. Start with the [current GX documentation](https://docs.greatexpectations.io/docs/).

A useful workflow is:

1. Connect to the database, filesystem, or dataframe using the current documentation for that source.
2. Retrieve the batch or data asset you intend to validate.
3. Define Expectations for the table’s actual contract: requiredness, uniqueness, accepted values, bounds, or other domain rules.
4. Run validation in the intended environment and examine the validation results.
5. Retrieve unexpected rows where supported and permitted, then determine whether the cause is source data, transformation logic, or an incorrect expectation.

GX’s documentation describes retrieving unexpected rows from validation results to aid diagnosis. The exact API and configuration are version dependent; use the current documentation for the installed release. Do not treat a framework’s ability to surface unexpected records as authority to change the business rule automatically.

5. Test relationships across tables

Cross-table checks are important when a table’s validity depends on another table. For example, an order’s customer key may need to refer to an existing customer. Great Expectations documents three approaches: validate a joined view with built-in Expectations, write a custom SQL Expectation that references multiple tables, or compare query results across two data sources with a multi-source Expectation. Choose based on where the data lives and whether the rule is clearer as a view, SQL query, or comparison. See the [GX guide to cross-table validation](https://docs.greatexpectations.io/docs/reference/learn/data_quality_use_cases/cross_table/).

A left-join query is often the simplest way to expose missing references in SQL:

select child.*
from warehouse.order_items as child
left join warehouse.orders as parent
  on child.order_id = parent.order_id
where child.order_id is not null
  and parent.order_id is null;

Check key types, normalization, and intended null behavior. If one side uses padded strings and the other does not, decide whether that discrepancy is a data defect or a legitimate representation rule before normalizing it away. For multi-column relationships, join on every key component.

6. Choose the workflow that fits the data

Situation	Starting point	Why it fits
Warehouse model in a dbt project; rules are SQL-shaped	dbt generic tests for reusable checks; singular tests for custom queries	Tests can be associated with project resources and expressed as queries
Validation across SQL, files, or dataframes	Great Expectations	Its documented workflow supports these source types and Expectations
One immediate investigation or a database without a validation framework	SQL that selects violating rows	Directly reveals the counterexamples and can be adapted to the local job
Integrity spanning tables or sources	Joined view, custom SQL, or multi-source comparison	The relationship rule can be checked where the relevant records are accessible

Compare tools using your existing workflow, source type, rule complexity, reuse needs, and failure inspection process. The research available for this guide does not support claims ranking these tools by speed, price, hosting, or licensing.

7. Put checks at a useful point in the pipeline

• During development: run relevant checks while building or changing a model so failures are close to the code change.
• In scheduled pipelines: validate after the upstream data is available and before downstream consumers rely on it.
• In CI: run checks that can complete against the data and environment available to the workflow; define clearly what happens when test data or credentials are unavailable.
• On source tables: check assumptions about incoming data as early as practical.
• On transformed tables: check invariants introduced by business logic, joins, filters, and aggregations.

Pick a failure policy deliberately. A critical key violation may stop downstream publication; an exploratory anomaly may raise an alert for review. The consequence should match the downstream risk and the reliability of the assertion.

8. Troubleshooting failed table tests

Symptom	Likely cause	What to check
Requiredness check fails	Source field is missing, a join introduced nulls, or the field is actually optional	Trace failing rows upstream; verify the contract and join keys
Uniqueness check fails	Duplicate ingestion, an incorrect grain assumption, or a key unique only within a partition	Inspect duplicates and confirm the table grain; test a composite key if appropriate
Accepted-values check fails	New domain value, casing or whitespace variation, or stale allowed set	Inspect distinct unexpected values; consult the rule owner before expanding the set
Relationship check fails	Parent data arrived late, keys differ in format, or a true orphan exists	Check load ordering, key representation, null policy, and parent coverage
Aggregate bound fails	Partial load, duplicate batch, real business change, or a brittle threshold	Check data freshness and batch completeness; review the bound against domain behavior
Test passes but bad data remains	The assertion checks the wrong grain, excludes relevant rows, or encodes too weak a condition	Review filters, null semantics, join conditions, and the rule’s intended scope
Validation cannot find its table or batch	Wrong environment, connection, asset configuration, or stale framework setup	Confirm the selected source, schema, credentials, and configuration for the installed version

Do not “fix” a red check by weakening it until it passes. First establish whether the data, transformation, or expectation is wrong; then change the relevant one and keep the reason visible.

9. Performance, reliability, and cost considerations

Validation has to read or otherwise evaluate data, so the amount of data scanned and the query plan can matter. The research sources do not provide comparative benchmark results, pricing, or performance guarantees for dbt or Great Expectations. Use your warehouse’s query plans and job records to understand the actual cost in your environment.

• Where the rule permits it, validate the intended partition or incremental slice, while retaining checks that protect table-wide invariants such as global uniqueness.
• A relationship check may require reading both sides of a join; ensure the join keys and scope match the intended relationship.
• Keep failure output diagnostic but limited to necessary columns, especially if records contain sensitive data.
• Make retries and late-arriving data behavior explicit. A test can fail correctly because its parent table has not loaded yet.
• Review thresholds and accepted sets as the domain changes; an obsolete expectation can be as misleading as a missing test.

For reliability, run the same assertions at a predictable pipeline point and make failures visible to the people who can diagnose them. Retain failing rows only where the framework, version, permissions, and data-retention policy support that safely.

10. A compact implementation checklist

1. Write down the table grain, key, nullable fields, allowed domains, and relationships.
2. Choose a small set of assertions tied to that contract.
3. Write each SQL check to return the violating records, or express it with dbt or GX using current version documentation.
4. Decide whether each rule is reusable or specific to one model.
5. Run checks at development and pipeline points where failures can be acted on.
6. Ensure the result helps identify records without exposing unnecessary sensitive values.
7. Document whether a failure blocks publication, alerts someone, or requires manual review.
8. Revisit expectations when the schema or business domain changes.

Or skip the browser setup

If your validation also needs a visual record of a web page or rendered table, ScreenshotNeo can return a screenshot or PDF with one GET request. It is a website screenshot API and MCP server for developers. See the [ScreenshotNeo documentation](https://screenshotneo.com/docs/) for request options.

curl -G "https://api.screenshotneo.com/v1/shot" -d access_key=YOUR_API_KEY --data-urlencode url=https://stripe.com -o shot.webp

import requests

r = requests.get(
    "https://api.screenshotneo.com/v1/shot",
    params={"access_key": "YOUR_API_KEY", "url": "https://stripe.com"},
    timeout=90,
)
open("shot.webp", "wb").write(r.content)

const q = new URLSearchParams({ access_key: 'YOUR_API_KEY', url: 'https://stripe.com' });
const res = await fetch(`https://api.screenshotneo.com/v1/shot?${q}`);

Cookie and consent banners, newsletter popups, and chat widgets are removed before the capture, and each step can be turned off. Bot checks, blank pages, timeouts, failed loads, and cache hits are not billed; response headers report the page verdict and billing status. AI agents can use the MCP server tools take_screenshot, get_page_info, and capture_pdf. The free plan includes 1,000 screenshots per month with no card; paid plans start at $5 for 3,000. [Visit ScreenshotNeo](https://screenshotneo.com) or [sign up for the free plan](https://screenshotneo.com/account/sign-up/).

FAQ

What should I test first in a new table?

Start from the table grain and contract: identify the key, required fields, allowed domains, and references that downstream logic depends on. Then add checks for those assumptions.

Should every column be non-null and unique?

No. Requiredness and uniqueness depend on the field’s meaning and the table’s grain. Apply each rule only when the data contract supports it.

Can a data test prove that a web table works for users?

No. Checks of stored data do not demonstrate that a rendered interface behaves correctly or is accessible. Those need separate frontend-specific tests and evidence.

How should I handle a failed expectation?

Inspect the failing records, trace them to their source and transformations, and decide whether the data, transformation, or expectation needs correction.

Sources

• dbt: Data tests
• Great Expectations documentation
• Great Expectations: cross-table validation