--- title: "Check That Row Counts Match Between Source and Target" slug: "check-row-counts-between-source-and-target" description: "Build a standard test that compares a table's row count in the source with its copy in the warehouse, run it, and schedule it to run every night." status: "new" updated: 2026-11-20T21:54:24Z published: 2026-11-20T21:49:38Z canonical: "docs.validatar.com/check-row-counts-between-source-and-target" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.validatar.com/llms.txt > Use this file to discover all available pages before exploring further. # Check That Row Counts Match Between Source and Target 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](/docs/create-a-data-source), [Connect to SQL Server](/docs/connect-to-sql-server) and [Connect to Snowflake](/docs/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](/docs/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](/docs/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?](/docs/why-is-my-test-failing) | | 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](/docs/template-tests) 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](/docs/create-a-template-test) and [Select Tables and Columns for a Template Test (Metadata Driven)](/docs/generating-child-tests-for-specific-metadata-objects). ## Next steps - **Find which rows are missing.** [Compare Source and Target Data](/docs/comparing-to-another-data-set) covers per-key and per-month comparisons. For a full source-to-target reconciliation, see [Reconciling Source and Target with Key-Based Templates](/docs/reconciling-source-and-target-with-key-based-templates). - **Learn more about jobs.** [Create a Job](/docs/job-details) and [Schedule a Job](/docs/scheduling-a-job). Engineers who work in an AI coding tool can build these tests through the [Validatar MCP server](/docs/connecting-to-the-validatar-mcp-server) or the [Validatar Agent Kits](/docs/validatar-agent-kits). ## Related - [Compare Source and Target Data](/comparing-to-another-data-set.md) - [Create a Template Test](/create-a-template-test.md) - [Create a Job](/job-details.md) - [Schedule a Job](/scheduling-a-job.md) - [Reconciling Source and Target with Key-Based Templates](/reconciling-source-and-target-with-key-based-templates.md)