Check That Row Counts Match Between Source and Target

Prev Next

To check that a target table has the same number of rows as its source, create a standard test with a count query on each side, compare the two values for an exact match, and put the test in a scheduled job. It takes about 15 minutes.

A nightly load can drop rows without failing. The job reports success, the dashboards refresh, and nobody notices that a day of orders never arrived. A row count comparison is the cheapest check that catches it, and it's the first real test most teams build.

When you finish, you'll have a standard test that compares Sales.Orders in the Wholesale Distribution System (SQL Server) with its copy in the Enterprise Data Warehouse (Snowflake), a result you can read, and a job that runs the test every night.

Before you start

  • Both data sources, with their metadata ingested. See Create a Data Source, Connect to SQL Server and Connect to Snowflake.
  • A project that includes both data sources. A test can only use the data sources its project includes. The example uses the Warehouse Reconciliation project. See Create a Project.
  • Permissions. Create Tests and Create Jobs in the project, and Create and Edit Tests on both data sources.

In the example, the wholesale system's Sales.Orders table lands in the warehouse as LOAD_SQLSERVER_WORLDWIDEIMPORTERS.SALES_ORDERS in the SAMPLE_DW database.

Step 1: Start a standard test

  1. Click Projects in the top bar and open Warehouse Reconciliation.
  2. Click Tests, then open the folder you want the test in. The example uses Wholesale System to Warehouse.
  3. Click New. A new standard test opens. (New also has a menu with New Standard Test, New Template Test and New Folder.)
  4. Fill in the top of the page:
Field Example value
Name Wholesale Order Count Matches the Warehouse
Description A failure here means the warehouse has more or fewer orders than the wholesale system it was loaded from, so every order report built on it is off.
Quality Dimension Completeness
Severity Level High

Step 2: Count the rows in the source

The test side is under What do you want to test? This query counts the orders in the wholesale system:

-- Orders in the wholesale system. Pass: the same count as the warehouse copy.
SELECT COUNT(*) AS ORDER_COUNT
FROM Sales.Orders
  1. In Data Source, choose Wholesale Distribution System. The list shows each data source with its environment in parentheses, for example Wholesale Distribution System (Prod).
  2. In How to get the data, choose Script.
  3. Paste the query into SQL Script.
  4. Click Preview & Initialize Options. Validatar runs the query, shows the count, and sets Process data as to Single Value.

Step 3: Count the rows in the warehouse

The control side is under What do you want to compare to? This query counts the warehouse copy:

-- Orders in the warehouse copy of the wholesale orders table. Pass: the same count as the system.
SELECT COUNT(*) AS ORDER_COUNT
FROM LOAD_SQLSERVER_WORLDWIDEIMPORTERS.SALES_ORDERS
  1. Select Another Data Set.
  2. In Data Source, choose Enterprise Data Warehouse.
  3. In How to define the control data, choose Script.
  4. Paste the query into SQL Script and click Preview & Initialize Options.

Step 4: Set the pass rule and save

  1. Under Result Configuration, set Value Success Criteria > Comparison Method to Exact Match Only. The test passes only when both counts are the same.
  2. Click Save.
  3. If Validatar shows Suggested Metadata Links, click Accept & Save. The links tie the test to Sales.Orders and SALES_ORDERS in the Catalog, so the test appears on both tables' Tests tabs and counts toward their trust scores.

If your warehouse is allowed to lag the source by a known amount, choose Absolute Numeric Difference or Percent of Control Value instead and enter the tolerance. Start with an exact match and loosen it only for a reason you can write in the description.

Step 5: Run it

  1. Click Run Test.
  2. Under Send Results To, choose Test Repository, so the result is saved to the test's history. Screen shows the result without saving it.
  3. Click Run.

Step 6: Read the result

The Test Execution Progress dialog opens. When the run finishes, its Result tab shows:

Metric What it is
Test Value The row count from the source, the wholesale system
Control Value The row count from the target, the warehouse

The result is Passed when the two match and Failed when they don't.

On the demo, the test fails. The warehouse holds 431,507 orders, and the wholesale system holds more than 479,000. The warehouse copy stops in late April 2024, so every order taken since then is missing from it. That's the test doing its job: the load never errored, and without this check the gap would stay invisible until someone reconciled a sales report by hand.

A row count tells you that rows are missing, not which ones. The demo's next test, Wholesale Orders Reach the Warehouse by Month, compares the order count for each month: the months tie out until April 2024, and after that the system has orders the warehouse doesn't.

Step 7: Put the test in a job

  1. Go back to the Warehouse Reconciliation project page and click Jobs.
  2. Click New. A new job opens.
  3. In Name, type Wholesale Nightly Checks.
  4. On the Steps tab, leave the list set to Tests (By Folders) and expand the folders to your test.
  5. Select the test and click the right arrow (Add selected item(s)). The test appears under Job Steps.

Step 8: Schedule the job nightly

The schedule for the example:

Setting Value
Name Nightly
Execute job Daily
Schedule is enabled Checked
Recurs every 1 day(s)
Start date Today
Daily Frequency Once daily
Occurs once at A time after your nightly load finishes, for example 2:00 AM
Time Zone Your time zone
  1. Open the job's Schedules tab and click New.
  2. Fill in the settings from the table.
  3. To hear about failures, check Send Email Notification, choose when to send, and enter the Email Recipients.
  4. Click Add Schedule.
  5. Click Save at the top of the job. The job and its schedule are stored only when you save the job.

Check that it worked

On the job's Schedules tab, the Nightly schedule shows its Recurrence Pattern, Daily Recurrence and Enabled. The Steps tab title counts one step. After the first scheduled run, the job's History tab lists the run, and the test's History tab shows a new result. Reading results over time is covered in Run a Test and Read Its Results.

Do it with Validatar AI

Do it with Validatar AI (Validatar Cloud, beta). Open the AI panel and ask: "In the Warehouse Reconciliation project, in the folder Wholesale System to Warehouse, create a standard test named Wholesale Order Count Matches the Warehouse that compares SELECT COUNT() FROM Sales.Orders on the Wholesale Distribution System with SELECT COUNT() FROM LOAD_SQLSERVER_WORLDWIDEIMPORTERS.SALES_ORDERS on the Enterprise Data Warehouse, passing only on an exact match. Then run it." Review what it creates before you run it. You can undo the change from the chat history.

Validatar AI can create and run the test, but it has no tools for jobs or schedules. Do steps 7 and 8 yourself.

Troubleshooting

Symptom Cause Fix
A data source is missing from the Data Source list The project doesn't include it, or you don't have Create and Edit Tests on it Add the data source to the project, or ask a data source admin for the permission
The options under What do you want to compare to? can't be selected The test side isn't set yet ("Select what you want to test up above") Choose the data source and data for the test side first
Preview & Initialize Options shows an error The query failed on the data source: a wrong schema or table name, or the connection's user can't read the table Run the same query as that user in your own SQL tool, fix it, and preview again
The test shows an error instead of Passed or Failed One of the queries failed at run time See Why Is My Test Failing or Erroring?
The test fails by a small amount most nights The test runs while the load is still writing, or the source takes new rows between the load and the test Schedule the job after the load finishes, or compare up to the load's cutoff time in both queries
The Schedules tab says "The Starter Edition of Validatar does not support scheduling." Your license doesn't include schedules Contact your account representative
The job never ran The job wasn't saved after the schedule was added, or Schedule is enabled is unchecked Open the job, check the schedule, and click Save

Do all 49 tables at once

The wholesale system has 49 tables, and the warehouse holds a copy of each. One standard test per table is 49 tests to build and maintain. A template test does it with one pair of queries. On the demo, the template Wholesale Row Counts Match the Warehouse compares these two queries and creates 49 child tests, one per table:

-- Rows in this table of the wholesale system. Pass: the same count as its warehouse copy.
SELECT COUNT_BIG(*) AS "VALUE"
FROM "{{schema.name}}"."{{table.name}}"
-- Rows in the warehouse copy of this table, which is named SCHEMA_TABLE. Pass: the same count as the system.
-- Unquoted, Snowflake reads the name as upper case, the way the copy is stored.
SELECT COUNT(*) AS VALUE
FROM LOAD_SQLSERVER_WORLDWIDEIMPORTERS.{{schema.name}}_{{table.name}}

Validatar fills in {{schema.name}} and {{table.name}} for each table the template selects from the Catalog. To build one, see Create a Template Test and Select Tables and Columns for a Template Test (Metadata Driven).

Next steps

Engineers who work in an AI coding tool can build these tests through the Validatar MCP server or the Validatar Agent Kits.