To test a data table, turn each important assumption into an explicit assertion—such as “this ID is unique” or “every order references an existing customer”—and identify the rows that violate it. Use dbt when checks fit a SQL-based project workflow; use Great Expectations when you need documented validation workflows across databases, files, or dataframes, including several approaches to cross-table checks.
What does it mean to test a data table?
In this guide, “testing a data table” means validating its records and relationships, rather than checking how a table looks or behaves in a web interface. A useful data test expresses a rule and finds records that disprove it. In dbt, a test passes when its query returns no failing rows. dbt’s data-test documentation describes built-in checks and custom SQL tests.
| # | Preview | Product | Price | |
|---|---|---|---|---|
| 1 |
|
The Art of Statistics: How to Learn from Data | $13.50 | Buy on Amazon |
| 2 |
|
Introduction to Statistics and Data Analysis | $53.98 | Buy on Amazon |
| 3 |
|
Storytelling with Data: A Data Visualization Guide for Business Professionals | $14.87 | Buy on Amazon |
| 4 |
|
Qualitative Data Analysis: A Methods Sourcebook | $109.99 | Buy on Amazon |
There is no universal set of correct rules for every table. A field should be required only if the data contract or business meaning requires it; an apparent duplicate may be valid if the real key is a combination of columns. Write down the intended rule before encoding it.
Which assertions should you start with?
Begin with the risks that would make the table unusable or misleading, then add domain-specific rules. Common assertions include:
#1 Best Overall
- Requiredness: a field that must be present is not null.
- Uniqueness: a key or intended combination of columns does not identify multiple rows.
- Allowed values: a categorical field contains only values permitted by the domain.
- Referential integrity: each foreign-key value matches an appropriate record in a related table.
- Bounds or volume: a count or numeric measure stays within an explicitly justified range.
These are candidate checks, not assumptions to apply blindly. For example, uniqueness on a customer ID makes sense in a customer dimension but may be wrong in an event table where one customer can appear many times. Likewise, a row-count bound is useful only if the expected range has a defensible basis.
How do you choose a testing tool?
Choose based on where the data lives, how the rule is best expressed, when the check should run, and how you will inspect failures. The available documentation supports a workflow comparison, not a general ranking by speed, price, hosting, or licensing.
| Need | Consider | Why it fits |
|---|---|---|
| Tests for models in an existing dbt project | dbt data tests | Generic tests cover reusable assertions; singular tests express a one-off rule in SQL. Tests can be associated with models and other resources such as sources, seeds, and snapshots. dbt documentation |
| Validation across SQL databases, filesystems, or dataframes | Great Expectations | Its documented workflow connects to those data sources, retrieves batches, and validates expectations. Connect to data sources and validate data |
| Integrity checks involving more than one table | Great Expectations or SQL-based checks | Great Expectations documents joined views, custom SQL expectations, and multi-source comparisons as possible approaches. The right fit depends on where the tables reside and whether the rule can be expressed cleanly in a view or query. Cross-table expectation approaches |
Before adopting either workflow, decide whether checks belong in local development, a scheduled pipeline, or CI; whether a failure must preserve offending records; and who will maintain the rule. No single tool choice settles those operational questions.
How do you test tables with dbt?
dbt is a natural option when your data is already modeled in a dbt project and the assertions can be expressed as SQL. Its generic tests support reusable rules with small variations; singular tests let you write a custom query for a specific case. Tests can be attached to models as well as sources, seeds, and snapshots. Check the dbt documentation for the syntax and behavior supported by your installed version, because the documentation is versioned and evolves.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Rank #2
Use a generic test for a recurring rule
Use the documented generic checks for common properties such as non-null values, unique values, accepted values, and relationships. They are intended for assertions you may apply to more than one resource. Configure the rule to match the actual data contract rather than treating a column name as proof of its meaning.
Use a singular SQL test for a specific rule
For a custom business rule, write a query that returns the rows that violate it. A test with no returned rows passes; returned rows are the cases to investigate. This failure-oriented shape makes it easier to inspect what went wrong than a check that reports only a pass or fail.
Investigate failing records
When diagnosing a failure, inspect the records that violate the rule. dbt documents an option to store test failures in a database table for development-time investigation. Confirm its current configuration and syntax in the documentation for your installed dbt version before relying on it.
How do you validate tables with Great Expectations?
Great Expectations frames checks as Expectations—verifiable assertions about data—which can be collected into suites. Its documented workflow covers connecting to SQL databases, filesystems, and dataframes, retrieving batches, and validating expectations against them. See the data-source guide and validation guide for the workflow supported by the current documentation.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsRank #3
- Wiley
- Language: english
- Book - storytelling with data: a data visualization guide for business professionals
Pick an approach for cross-table integrity
For rules that relate tables, the Great Expectations guide describes three approaches:
- Join the relevant tables into a view and apply built-in expectations to that view.
- Write a custom SQL expectation that references multiple tables.
- Compare query results across two data sources using a multi-source expectation.
Choose according to where the data resides, the complexity of the relationship, and whether a view or query expresses the rule clearly. The cross-table guide describes these alternatives.
Use unexpected rows to diagnose failures
Great Expectations documentation describes retrieving unexpected rows from validation results so you can inspect failures. Treat those records as evidence for investigation, not as an automatic instruction to change the test. The appropriate fix may be correcting source data, repairing a transformation, or revising an expectation that did not represent the domain rule. See the validation documentation for the current workflow.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.What should a useful data-test workflow include?
- Define the rule: write the expected property in business terms, including which rows and values it applies to.
- Choose the test shape: use a reusable assertion for recurring rules or a focused custom query for a one-off rule.
- Run it where it matters: select local development, a scheduled pipeline, or CI based on when a bad table would cause harm.
- Inspect violations: make sure the workflow lets an investigator identify the offending records, and retain them where appropriate and safe.
- Resolve the cause: determine whether the source, transformation, or expectation is wrong before changing data or weakening the check.
For each test, make clear what failure means and who should act on it. A check that produces a noisy or ambiguous failure can be less maintainable than a narrower assertion with a clear owner.
Free tools Windows power users keep installed
One-click scans. No signup required.
Rank #4
What data-table tests do not prove
Checks on records and relationships do not establish that a rendered web table is accessible or that its sorting, filtering, and pagination work. Those are interface behaviors and require frontend-specific testing methods; the data-validation workflows described here do not document how to test them. If the concern is a web table, treat interface behavior as a separate testing problem rather than assuming valid source data proves a usable presentation.
Or skip the browser setup
If your next step is to capture a table in a browser rather than validate its records, ScreenshotNeo is a website screenshot API and MCP server for developers. Its API accepts one GET request and can return a PNG, JPEG, WebP, or PDF. Cookie banners are accepted and removed before capture, along with known newsletter popups and chat widgets; each cleanup step can be turned off. Bot checks, blank pages, timeouts, failed loads, and cache hits cost nothing, and response headers report the page verdict and billing status. Its MCP server provides take_screenshot, get_page_info, and capture_pdf for AI agents and MCP clients.
Example cURL request (replace the URL with the page you need):
curl -G "https://api.screenshotneo.com/v1/shot" -d access_key=YOUR_API_KEY --data-urlencode url=https://stripe.com -o shot.webpSee the ScreenshotNeo API documentation for request options. The free plan includes 1,000 shots per month with no card; paid plans start at $5 for 3,000 shots. Sign up for ScreenshotNeo’s free plan.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
