Source to Target Reconciliation of Random Sample Dataset

This article explains the steps needed to perform for comparing random sample data between two heterogeneous datasources. This process is helpful when you want to perform a quick random data integrity check for a very large dataset by comparing data field-by-field for a random set of records.

For explaining the process, we will use source as SQL Server and target as Snowflake. We will use the table LINEITEM from TPCH_100 schema which has about 600 million records, and 600 records as a random sample size.

Step 1: Snapshot the random sample dataset from the source

We will use the source SQL Server for capturing the random sample dataset keys. Following are the steps needed to capture a random sample from SQL Server LINEITEM table.

1. Create a new query chain with the name “Random 600 records from LINEITEM Table” as follows

2. Drag and drop “Custom SQL” widget from design palette and input the SQL as follows:

SQL:

SELECT TOP 600 *

FROM   "TPCH_100".dbo."LINEITEM" TABLESAMPLE (1)

3. Drag and drop “RD Output”, input the following configuration

4. Execute the query chain.

Step 2: Create variable source query

1. Connect to “RD Extracts” (RightData (DataTrust) DataStore DB) profile and create a query with the view created in step 1 as the source by concatenating all the key columns as follows:

SQL:

SELECT Concat(L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER) AS CONCAT_KEY

FROM   "rightdata_db".dbo."MSSQL_TPCH_RANDOM_600"

Step 3: Create a variable

1. Create variable “VAR_MSSQL_TPCH_RANDOM_600_KEYS” using the query “QRY_MSSQL_TPCH_RANDOM_600_KEYS” as source as follows:

2. Save the variable.

Step 4: Create a “functional data reconciliation” scenario

1. Go to Scenario Studio, click create a new scenario with the name “Compare random 600 records between MSSQL and Snowflake”

2. Drag and drop the “Custom SQL” widget from the design palette, double click and select the MSSQL TPCH profile, enter the SQL text as shown below:

SQL

  SELECT *

FROM "TPCH_10".dbo."LINEITEM"

WHERE CONCAT(L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER) IN

 ( '~~USR_VAR_MSSQL_TPCH_RANDOM_600_KEYS##' )

Note: Use the variable created in step 2

3. Click “Infer Metadata” and “Save”

4. Repeat 2.2 and 2.3 for Snowflake as follows:

5. Drag and drop the “Map and Recon” widget and configure as follows

6. Save the config and run the scenario.

7. To run the scenario for different random sample, before running the scenario execute the query chain “Random 600 records from LINEITEM Table”

Step 5: Create a job

Automate the end-to-end process by creating a job

1. Go to “Batch Console”, create a new job with the name “JOB_Compare random 600 records between MSSQL and Snowflake” as follows:

2. From the design palette “Schedule” tab, drag and drop the “Execute Now” widget to the canvas (you can use other scheduling options as per your requirement).

3. Now drag and drop the “Query Chains” widget and select the query chain “Random 600 records from LINEITEM Table”

4. Drag and drop “FDR Scenarios” to the canvas and select the FDR “Compare random 600 records between MSSQL and Snowflake” as follows:

5. Execute the job to capture the random sample keys and execute the comparison between MSSQL to Snowflake