A test fails when it ran and the data did not meet its success conditions. It errors when it could not finish: a query broke, a data source could not be reached, or some rows could not be compared. A failure is usually news about your data; an error is news about the test or the connection. Open the result, see which one you have, and find the symptom below.
Start here: read the result
- Open the run. On the test's page, go to the History tab, select the run and click View Results, or open it from the project's results. See Run a Test and Read Its Results.
- Read the result in the page header: Passed, Failed or Error.
- For Error, read the Errors box on the Results tab. Location tells you where it broke: Test Script or Control Script (one side's query), Test or Batch (the run itself). Description is the message, often the database's own words.
- For Failed, read the values: Test Value and Control Value for a single-value test, or the Match Status and Record Result of each row for a row-by-row test.
- Open the Execution Detail tab to see which data source each side used and how long each script ran.
- If it passed before, compare with the last passing run on the History tab. A result's Test Detail tab shows the version of the test that ran, and the right side of the History tab lists who changed the test and when.
To test one side on its own, open the test and use the preview button beside that side's script (it reads Preview script when you point at it). It runs the query on that side's data source and shows what comes back.
When some rows show Error in Record Result, those rows could not be compared, for example a number too large to compare or a Custom Calculation that failed on that row's values. If the test does not otherwise pass, any errored row makes the whole result Error rather than Failed.
The values differ because the data differs
What you see: Failed, with real gaps between the test and control values, often grouped in a date range, a region or one load.
This is the test doing its job. In the Warehouse Reconciliation project, Wholesale Invoiced Sales Tie Out by Month compares invoiced sales per month in the Wholesale Distribution System (SQL Server) with the warehouse copy in the Enterprise Data Warehouse (Snowflake). Every month through March 2024 ties to the cent. From April 2024 on, $112,027,450.06 of invoiced sales are in the system and not in the warehouse, across 32 months. Nothing errored: the copy stopped after 23 April 2024, and every run since has failed on the months after it.
How to confirm: filter the grid to the failing rows and look for a pattern. Rows marked Control Only or Test Only are keys one side does not have at all. Pick one failing key and query both sides for it with the preview button; if the numbers really differ, the data differs.
What to change: nothing in the test. Validatar finds and reports the gap; the fix belongs in the load or the source. Reload the missing data, then run the test again (or use Rerun Failed/Errored Tests on the job result). Change the test only if the rule itself was wrong.
The test ran before the data arrived
What you see: Failed on a scheduled run, and Passed when you run it again later the same day.
How to confirm: compare Test Start Time on the Execution Detail tab with the time the load finished. Recent keys (today, the last hour) that show as Test Only or Control Only point the same way.
What to change: move the schedule later (see Schedule a Job), or start the job from the tool that runs the load, so the checks begin when the load ends (see Run Validatar jobs from any orchestrator over REST).
One side returned no rows
What you see: a single-value test shows a value as missing, or says "There are no results because both data sets were empty." A row-by-row test shows every row as Test Only or Control Only.
Why: the query returned nothing. Common causes are a filter that matches no rows (a date that has not loaded yet), the wrong schema or database on the data source, or a table name in the wrong case on a platform that treats quoted names as case-sensitive.
How to confirm: preview each script. Execution Detail shows which data source each side used.
What to change: fix the query or the data source. If an empty side is acceptable, say so in the test's result settings: a single-value test has an Empty Data Set setting, and a row-by-row test has Unmatched Keys. Each has a choice for Test Only and Control Only: Fail (the default), Pass, or Override with a value, such as 0 for a count. An overridden value shows with an asterisk in the result. See Configuring Result Criteria.
Keys appear on only one side
What you see: rows marked Test Only and Control Only in a row-by-row comparison.
Why: either the rows really are missing on one side (see the first section), or the same key is written two ways. 2024-04 and 2024-4, 00123 and 123, ABC and abc, a date and the same date as text: Validatar compares keys exactly, so each spelling is a different key.
How to confirm: filter Match Status to Test Only, then to Control Only, and read the keys side by side. The same value spelled two ways is a format problem. A key with no counterpart at all is missing data.
What to change: make both key expressions return the same text. The two monthly tests in Wholesale System to Warehouse build the month key differently on each platform so that both return YYYY-MM. On SQL Server:
-- Orders per calendar month in the wholesale system, keyed by month as YYYY-MM. Pass: the warehouse has the same count for every month.
SELECT
-- Style 120 writes a date as yyyy-mm-dd, so its first seven characters are the month.
CONVERT(char(7), OrderDate, 120) AS "KEY",
COUNT(*) AS "VALUE"
FROM Sales.Orders
GROUP BY CONVERT(char(7), OrderDate, 120)
On Snowflake, where the order date landed as text:
-- Orders per calendar month in the warehouse copy, keyed by month as YYYY-MM. Pass: the same count as the wholesale system for every month.
SELECT
-- Dates landed as text, so the order date is read back as a date first.
TO_CHAR(TO_DATE("OrderDate"), 'YYYY-MM') AS KEY,
COUNT(*) AS VALUE
FROM LOAD_SQLSERVER_WORLDWIDEIMPORTERS.SALES_ORDERS
GROUP BY 1
For tables compared row by row on a business key, the key-based reconciliation templates handle key choice and key checks for you.
Data types or precision differ
What you see: Failed on rows whose values look equal, or differ only in the last digits or the format.
Three causes come up again and again:
- Dates stored as text on one side. In the warehouse copy of the wholesale system, 105 date and time columns landed as text. A date arrives as
2013-01-01 00:00:00.000000from SQL Server and as2013-01-01 00:00:00.0000000from the text copy. Compared as text, every row fails. Read the text back as a date in the query (as in the Snowflake example above), or set the test's Treat values as to Date or Date/Time so both sides compare as dates. - Time precision. SQL Server
DATETIMEstores time to about a three-hundredth of a second (values end in .000, .003 or .007), whileDATETIME2and SnowflakeTIMESTAMPkeep more digits. Cast both sides to the same precision, for example whole seconds, or use Within under Value Success Criteria to allow a small difference between date and time values. - Decimals and floating point. In the Cloud Migration Validation project, Products Match Row by Row matches 293 products and finds two wheel prices a cent off, because the migrated copy stores prices as floating point numbers. Cast both sides to the same
DECIMALprecision and scale, or set Value Success Criteria to Absolute Numeric Difference with a tolerance such as 0.01. Keep a tolerance no wider than the precision the business cares about, because it hides every real difference below it.
How to confirm: look at Test Value, Control Value and Difference for a failing row. A difference in the last decimal places, or values that differ only in format, is a type or precision problem. When the test's Treat values as does not suit the data, the test page offers "Click here to select the recommended value data type of" a better type.
Text differs in case or trailing blanks
What you see: Failed on text that looks identical in the grid.
Why: text compares exactly. Tailspin Toys and TAILSPIN TOYS differ, and so do Wingtip and Wingtip with a trailing blank. Fixed-width CHAR columns pad values with blanks, and some loads trim them; the load that copies the wholesale system into the warehouse trims trailing spaces from text. SQL Server usually compares text without regard to case, and Snowflake does not, so a query that groups by text can return different keys on each side.
How to confirm: compare lengths on each side. On SQL Server, LEN ignores trailing blanks and DATALENGTH does not; on Snowflake, LENGTH counts them.
What to change: if case or blanks should not matter, clear Case sensitive in the test's Value Success Criteria for text values, or normalize both sides in the query:
-- Customer names in the wholesale system, compared without trailing blanks. Pass: every name matches the warehouse.
SELECT
CustomerID AS "KEY",
-- The warehouse load trims trailing blanks, so trim here too before comparing.
RTRIM(CustomerName) AS "VALUE"
FROM Sales.Customers
If the difference does matter, leave the test strict and fix the pipeline.
Times are off by whole hours
What you see: date and time values that differ by the same whole number of hours on every row, or daily counts that disagree near midnight.
Why: one side stores local time and the other UTC, or Snowflake converts a TIMESTAMP_LTZ value to the session's time zone.
How to confirm: the Difference is constant and a whole number of hours. For counts per day, the totals over a week match while single days do not.
What to change: convert both sides to one time zone in the query before you compare or group by date: AT TIME ZONE on SQL Server, CONVERT_TIMEZONE on Snowflake. When the values carry their offset, set Treat values as to Date/Time with Time Zone.
A table or column was renamed or dropped
What you see: Error, with Location Test Script or Control Script and the database's message, such as SQL Server's "Invalid object name" or "Invalid column name", or Snowflake's "does not exist or not authorized" or "invalid identifier".
In the Warehouse Reconciliation project, a policy table in the warehouse star schema lost a column. The structure check Columns Align: S_DIM_POLICY_10 failed and named the column, and the five full row comparisons on the same table errored, because they cannot compare a column that is gone.
How to confirm: preview the script that errored, and check the table in the Catalog after the next metadata ingestion.
What to change:
- If the object was renamed, update the test's query.
- Run schema metadata ingestion so the catalog matches the database. Template tests select from the catalog, so a stale catalog keeps child tests for objects that are gone.
- Fix structure first, then read the data results: a missing column explains every comparison that touches it.
A template child test errors because its table is gone
What you see: one child test of a template test errors with "Invalid object name" or "does not exist", while its siblings pass.
Why: child tests are created when the template is materialized. A child stays after its table is dropped until the template is materialized again.
What to change: ingest schema metadata, then materialize the template with Delete all child tests for objects no longer selected in metadata selected. To keep it that way, turn on Materialize child tests before execution on the template's job step. See Materialize Child Tests and Create a Job.
The query timed out
What you see: Error with a message about a timeout or a cancelled query, and a long Test Script Duration or Control Script Duration on the Execution Detail tab.
What to change:
- Make the query cheaper: compare counts or totals per key instead of every row, filter to recent partitions, or let the database do the aggregation.
- Run heavy tests in their own job step with fewer running in parallel.
- On Validatar Server, an administrator can raise External Query Timeout in Settings > Configuration > Execution (0 means no limit). Validatar Cloud does not show this page.
- The database can impose its own limit, such as Snowflake's statement timeout. Ask your database administrator.
Permission denied on the data source
What you see: Error with a permission message from the database, such as SQL Server's "The SELECT permission was denied on the object". On Snowflake, a missing grant and a missing table give the same "does not exist or not authorized" message.
Why: the account in the data source's connection cannot read the table. Validatar runs every query as that account, not as you.
How to confirm: preview the script. Ask the database administrator to run the same query as the data source's account.
What to change: grant the data source's account read access to the table. Validatar's own permissions are a separate layer: without Run Tests on a data source, Run Test is greyed out, and without View Test Results you cannot see the saved results.
The Data Agent is offline
What you see: Error with "Data Agent did not respond" or "Data Agent failed during processing". This affects data sources connected through a Data Agent, such as the Wholesale Distribution System.
How to confirm: an administrator opens Settings > Configuration > Data Agents, opens the agent and reads Data Agent Status. An agent that cannot be reached shows "The Data Agent did not respond".
What to change: start the Data Agent service on its host, and check that the host can reach Validatar and the database. Then run the errored tests again, or use Rerun Failed/Errored Tests on the job result.
Ask Validatar AI to explain a failure
Do it with Validatar AI (Validatar Cloud, beta). Open the AI panel on the failing test and ask: "Explain why Wholesale Invoiced Sales Tie Out by Month failed in its latest run, then query both data sources for the first month that differs." The chat reads the latest run's counts and error message, and it can query a data source when you have Browse Metadata & Data Profiling on it. It does not see the row-by-row grid on the result page. Check its queries in the tool calls the chat shows. Explaining and querying change nothing, so there is nothing to undo.
When to contact support
Contact Validatar support when the error names Validatar rather than your database, when the same error repeats after the fixes above, or when a run stays Running or ends Abandoned. Include the project, the test name, the run date and time, and the full text of the Errors box, with the Execution Detail tab if you can.
Related
- Run a Test and Read Its Results
- Compare Source and Target Data
- Configuring Result Criteria
- Reconciling Source and Target with Key-Based Templates
- Validatar Data Agent
Engineers can ask the same questions from their own AI tools through the MCP server or the Validatar Agent Kits.