Rows missing between two databases (template, WORLD and WORLD-QA)

ANYChanges dataScripts Gallery

A reconciliation routine: copy a table from two connections into one local H2 database, then count the rows found on one side only. Written for the practice connections WORLD and WORLD-QA.

Verification status. Written from the BroadSQL documentation and the ANY documentation. It has not been run by the BroadSQL team: run it first on a test database, and read its result critically.

When to use it

After a migration or a sync between two systems.
Compare QA and production copies of a reference table.

The Script

Download reconcile-two-connections.bsql

-- @description: Copies a table from two connections into one local H2 database and counts the rows missing on each side (template)
-- @tags: reconciliation, migration, data quality, template, world
-- @status: draft
-- @access: write
-- @dialect: ANY
-- Written for the practice connections WORLD and WORLD-QA (see the how-to "Set up QA and production copies of
-- WORLD"). For your own data, replace WORLD and WORLD-QA with your connection IDs, the query with your table and
-- CODE with its key. The only database written is the local H2 database CHECKS, created in the export folder.
ON ERROR STOP;
CONNECT WORLD;
DUMP (SELECT CODE, TRIM(NAME) AS NAME, POPULATION FROM COUNTRY) TO CHECKS.PROD_COUNTRY AS H2;
CONNECT WORLD-QA;
DUMP (SELECT CODE, TRIM(NAME) AS NAME, POPULATION FROM COUNTRY) TO CHECKS.QA_COUNTRY AS H2;
CONNECT CHECKS;
SELECT 'missing in QA' AS CHECK_NAME, COUNT(*) AS ROWS_FOUND
  FROM PROD_COUNTRY P LEFT JOIN QA_COUNTRY Q ON Q.CODE = P.CODE
 WHERE Q.CODE IS NULL
UNION ALL
SELECT 'missing in production', COUNT(*)
  FROM QA_COUNTRY Q LEFT JOIN PROD_COUNTRY P ON P.CODE = Q.CODE
 WHERE P.CODE IS NULL;

Parameters

This Script takes no parameter.

Install and run it

Save the file in your Scripts Library folder (scripts in the installation folder by default), for example as gallery/reconcile-two-connections.bsql.
It needs the practice connections of Set up QA and production copies of WORLD, which do not exist by default. For your own data, copy the file and replace WORLD, WORLD-QA, the query and CODE; both connections must exist in your CDF.
Check what it declares: LIB SHOW gallery/reconcile-two-connections.bsql;
Run it: @gallery/reconcile-two-connections.bsql

What you get

Two rows: the number of keys missing in QA and the number missing in production. With the two changes of the how-to below made in WORLD-QA, it finds 1 country missing in QA (Monaco) and none missing in production. The two copies stay in CHECKS for the detailed queries of that how-to.

Limits

It opens the two connections in turn: the connection you started from is closed.
It writes only into the local H2 database CHECKS, replacing its two tables at each run.
ON ERROR STOP ends the run at the first failure, for example an unknown connection.

Found something wrong or unclear on this page? Tell us, with the page address.