Reconciliation checks

Last modified on 27-Sep-23

Use a reconciliation check to validate that target data matches source data before and/or after migrating between data sources.
Requires Soda Library

For example, if you must migrate data from a MySQL data source to a Snowflake data source, you can use reconciliation checks to make sure the MySQL data appears intact in Snowflake in staging before conducting the migration in production.

reconciliation Production:
  label: "Reconcile MySQL to Snowflake"
  attributes:
     priority: 3
  datasets:
    source:
      dataset: dim_customer
      datasource: mysql_adventureworks
    target:
      dataset: dim_customer
      datasource: snowflake_retail

  checks:
  # Metric reconciliation checks
    - row_count diff = 0
    - duplicate_count(last_name):
        fail: when diff > 10%
        warn: when diff < 5%
    - avg(total_children) diff < 10
    - name_combo diff = 0:
        name: Name Combo
        source query: |
          SELECT count(*)
          FROM dim_customer
          WHERE first_name = 'Rob' or last_name = 'Walters'
        target query: |
          SELECT count(*)
          FROM dim_customer
          WHERE last_name = 'Walters'

  # Record reconciliation checks
    - rows diff < 5
    - rows diff = 0:
        source columns: [customer_key, region_id]
        target columns: [customer_base_key, region]

  # Schema reconciliation check
    - schema

Prerequisites
Types of reconciliation checks
    Best practice for using reconciliation checks
Define reconciliation checks
    Metric reconciliation checks
    Record reconciliation checks
    Schema reconciliation checks
    Add attributes
    Add a filter
    Failed row samples
    List of compatible metrics and checks
Optional check configurations
Limitations and constraints
Go further

Prerequisites

• Python version 3.9.x or greater.
• A Soda Cloud account connected to Soda Library via API keys. See Take a sip of Soda.
• Soda Library; install one Soda Library package for each of the source and target data sources involved in your migration. See step 1, below.

Types of reconciliation checks

Soda supports three types of reconciliation checks:

• metric reconciliation checks
• record reconciliation checks
• schema reconciliation checks

A metric reconciliation check calculates the measurement of a metric such as sum or avg on data in the same dataset in two different data sources; where the delta between calculated measurements differs to the extent that it exceeds the threshold you set in the check, the check fails. Note, you can also compare data between datasets within the same data source.

In other words, the check validates the delta between calculated measurements of a metric in multiple datasets.

In the following example, the metric reconciliation check calculates the sum of column 1 in dataset X in both data source A and data source B. The calculated value of each is the measurement for the sum metric. It then compares the calculated measurements and gauges the difference between them. In this example, the difference between measurements is 4, so the check passes.

reconciliation Production:
  label: "Recon metric check"
  datasets:
    source:
      dataset: dataset X
      datasource: Data source A
    target:
      dataset: dataset X
      datasource: Data source B
  checks:
    - sum(column1) < 5

Read more about metrics, measurements, and thresholds in general.

A record reconciliation check performs a row-to-row comparison of the contents of each column, or specific columns, in datasets in two different data sources; where the values do not match exactly, the check fails. The numeric value the check result produces represents the number of rows with different, additional, or missing contents.

For example, the following check compares the entire contents of dataset Y in data source A and dataset Y in data source B. Though the contents of the rows match exactly, one dataset contains additional rows, so it is not an exact match and the reconciliation check fails with a numeric value of 2.

reconciliation Production:
  label: "Recon diff check"
  datasets:
    source:
      dataset: dataset Y
      datasource: Data source A
    target:
      dataset: dataset Y
      datasource: Data source B
  checks:
    - rows diff = 0

Read more about the optional configurations you can add to a record reconciliation check.

A schema reconciliation check compares the columns of two datasets to reveal any differences between target and source; where the columns names differ, or the data type has changed, Soda registers a mismatch and the check fails.

reconciliation Production:
  label: "Recon diff check"
  datasets:
    source:
      dataset: dataset Y
      datasource: Data source A
    target:
      dataset: dataset Y
      datasource: Data source B
  checks:
    - schema

Best practice for using reconciliation checks

To efficiently use resources at scan time, best practice dictates that you first configure and run metric reconcilitation checks, then use the output to write refined record reconciliation checks to fine-tune the comparison.

Depending on the volume of data on which you must perform reconciliation checks, metric recon checks run considerably faster and use much fewer resources. Start by defining metric reconciliation checks that test grouping, filters, and joins to get meaningful insight into whether your ingestion or transformation works as expected. Where these checks do not surface all the details you need, or does not provide enough confidence in the output, then proceed with record reconciliation checks.

Read more about Limitations and constraints.

Define reconciliation checks

The following outlines the basic steps to configure and execute reconciliation checks.

1. Install a Soda Library package for both the migration source and target data sources. For the very first example above, you would install both soda-mysql and soda-snowflake.
2. Configure both data sources in a configuration YAML file, and add your soda_cloud configuration. For the very first example above, you would add both MySQL and Snowflake connection configuration details to a configuration YAML file.
3. Prepare a recon.yml file and configure the reconciliation metadata; see details below.
4. Define reconciliation checks to compare data between data sources; see details below.
5. Run a Soda scan against either the source or target data source to execute the reconciliation checks and review results in the command-line output and in Soda Cloud. Note that check results are associated with the target dataset in Soda Cloud.

soda scan -d mysql_adventureworks -c configuration.yml recon.yml

To define reconciliation checks, best practice dictates that you prepare a recon.yml file separate from your checks YAML file which contains regular, non-reconciliation checks for data quality in your data source. Technically, you can use one YAML file to contain all recon and regular SodaCL checks, but troubleshooting and issue investigation is easier if you use separate files.

In a recon.yml file, you must first provide reconciliation metadata for the checks, as per the configuration in the example and table below.

reconciliation my_project_name:
  label: "Reconcile MySQL to Snowflake"
  attributes:
     priority: 3
  datasets:
    source:
      dataset: dim_customer
      datasource: mysql_adventureworks
    target:
      dataset: dim_customer
      datasource: snowflake_retail

  checks:
    - row_count diff = 0

Metric reconciliation checks

The syntax of metric reconciliation checks follows the basic patterns of standard SodaCL metrics and checks with the addition of diff in the syntax. Metric reconciliation checks do not support all SodaCL metrics and checks; see compatible checks and metrics below.

For example, you define a regular SodaCL check for data quality that checks for duplicate values in a last_name column as follows:

checks for dim_customer:
  - duplicate_count(last_name) = 0

For a metric reconciliation check, you add the word diff to indicate that it ought to compare the count of duplicate values between the source dataset and the target dataset to confirm that the delta between those counts is zero. Refer to examples below.

Note that with reconciliation checks, there is no need to identify the dataset as you specified both source and target datasets in the project metadata configuration.

reconciliation Production:
...
  checks: 
    - duplicate_count(last_name) diff = 0  
    - avg(total_children) diff < 10
    - freshness(date_first_purchase) diff < 100h
    - row_count:
        fail: when diff > 10%
        warn: when diff between 5% and 9%
    - missing_count(middle_name) diff = 0:
        samples columns: [last_name, first_name]

When you run a scan against either the source or target data source, the Scan summary in the output indicates the check value, which is the calculated delta between measurements, the measurement value of each metric or check for both the source and target datasets, along with the diff value and percentage, and the absolute value and percentage.

soda scan -d adventureworks -c configuration.yml recon2.yml
Soda Library 1.x.x
Soda Core 3.0.xx
xxx 
Sending failed row samples to Soda Cloud
Sending failed row samples to Soda Cloud
Sending failed row samples to Soda Cloud
Scan summary:
3/5 checks PASSED: 
    dim_customer in aws_postgres_retail
      Recon Test: duplicate_count(last_name) diff = 0 [PASSED]
      Recon Test: avg(total_children) diff < 10 [PASSED]
      freshness(date_first_purchase) diff < 100h [PASSED]
1/5 checks WARNED: 
    dim_customer in aws_postgres_retail
      Recon Test: row_count warn when diff < 5% fail when diff > 10% [WARNED]
        check_value: 0.0
        source_row_count: 18484
        target_row_count: 18484
        diff_value: 0
        diff_percentage: 0.0%
1/5 checks FAILED: 
    dim_customer in aws_postgres_retail
      Recon Test: missing_count(middle_name) diff = 0 [FAILED]
        check_value: 7830
        source_missing_count: 7830
        target_missing_count: 0
        diff_value: 7830
        diff_percentage: 100.0%
Oops! 1 failure. 1 warning. 0 errors. 3 pass.
Sending results to Soda Cloud
Soda Cloud Trace: 6925***98

To customize your reconciliation checks, you can borrow from the syntax of failed rows checks to execute SQL queries on the source and target datasets. You can also write a user-defined check to define a SQL query or a common table expression (CTE) that Soda executes on both datasets to reconcile data; see examples below.

reconciliation Production:
...
  checks:
    - name_combo diff = 0:
        name: Name Combo
        source query: |
          SELECT count(*)
          FROM dim_customer
          WHERE first_name = 'Rob' or last_name = 'Walters'
        target query: |
          SELECT count(*)
          FROM dim_customer
          WHERE last_name = 'Walters'
    
    - average_children diff = 0:
        average_children expression: avg(total_children)

Learn about reconciliation check Limitations and constraints.

Record reconciliation checks

The syntax of record reconciliation checks is simple in that it only expects a rows diff input. You can use key configurations to refine how this type of check executes during a Soda scan.

¹ See Limitations and constraints.

reconciliation Production:
...
  checks:
    # Simple
    - rows diff = 0
    # Column constrained
    - rows diff = 0:
        source columns: [Planet, Hotness]
        target columns: [Planet, Relative Temp]
    # With primary key
    - rows diff = 0:
        key columns:
          - Planet
          - Size
    # With multiple primary keys
    - rows diff < 5:
        source key columns: [Planet, Hotness]
        target key columns: [Planet, Relative Temp]

Schema reconciliation checks

The syntax of schema reconciliation checks is simple, and without configuration details beyond the check identifier.

reconciliation Production:
...
  checks:
    - schema

Optionally, you can add a mapping configuration to a schema check to properly compare columns that use different data types. For example, you can use this configuration to map the comparison of a Snowflake column that uses Boolean and an MSSQL Server column that uses bit.

reconciliation Production:
  label: "Reconcile MS SQL to Snowflake"
  datasets:
    source:
      dataset: opt-in-campaign
      datasource: sqlserver1
    target:
      dataset: optin-campaign
      datasource: snowflake_retail

  checks:
    - schema:
        types:
          - source: bit
            target: boolean
          - source: enum
            target: string

Add attributes

Add attributes to reconciliation checks to organize your checks and alert notifications in Soda Cloud. For example, you can apply attributes to checks to label and sort check results by department, priority, location, etc.

You can add custom attributes to reconciliation checks in two ways:

• in bulk, so that Soda adds the attribute to all checks in the reconciliation project
• individually, so that Soda adds the attribute to individual reconciliation checks in the project

After following the instructions to create a check attribute in Soda Cloud, you can add the attribute to a reconciliation project, and/or to individual checks, as in the following example.

Where attribute values for the project and the individual check conflict or overlap, Soda uses the value for the individual check.

reconciliation Production:
  label: "Reconcile MySQL to Snowflake"
  # Soda adds this attribute to each check in the reconciliation project
  attributes:
     priority: 3
  datasets:
    source:
      dataset: dim_customer
      datasource: mysql_adventureworks
    target:
      dataset: dim_customer
      datasource: snowflake_retail
  checks:
    - row_count diff = 0:
        # Soda adds this attribute to this check, only.
        attributes:
           department: [Marketing]
    - rows diff:
        # Soda adds this attribute to this check, only.
        name: Row diff check
        attributes:
            department: [Development]
        fail: when > 10
        warn: when between 5 and 9

Add a filter

You can add a filter to a reconciliation project’s configuration to constrain the data on which Soda executes the reconciliation checks. Refer to the example below.

Best practice dictates that you add filters when using record reconciliation checks to mitigate heavy memory usage and long scan times when performing record-to-record comparisons of data. See Limitations and constraints.

reconciliation Production:
  label: "Recon Test"
  datasets:
    source:
      dataset: dim_customer
      datasource: adventureworks
      filter: total_children > 3
    target:
      dataset: dim_customer
      datasource: aws_postgres_retail

  checks:
    - row_count diff = 0

Output:

soda scan -d adventureworks -c configuration.yml recon2.yml
Soda Library 1.x.x
Soda Core 3.0.x
...
Scan summary:
1/1 check FAILED: 
    dim_customer in aws_postgres_retail
      row_count diff = 0 [FAILED]
        check_value: 14757
        source_row_count: 3727
        target_row_count: 18484
        diff_value: 14757
        diff_percentage: 395.95%
Oops! 1 failures. 0 warnings. 0 errors. 0 pass.
Sending results to Soda Cloud
Soda Cloud Trace: 4380***10

Failed row samples

Record reconciliation checks and reconcilication metric checks that borrow from failed rows check syntax such as the name_combo check in the example above, explicitly collect samples of any failed rows to display in Soda Cloud. The default number of failed row samples that Soda collects and displays is 100.

Read more About failed row samples

If you wish to limit or broaden the sample size, you can add the samples limit configuration to a check. Read more about Setting a sample limit.

checks:  
  - rows diff = 0:
      samples limit: 20

Alternatively, you can set the samples limit to 0 to prevent Soda from collecting and sending failed rows samples for an individual check, as in the following example.

checks:  
  - rows diff = 0:
      samples limit: 0

To review the failed rows in Soda Cloud, navigate to the Checks dashboard, then click the row for a the grouped reference checks. Examine failed rows in the Failed rows tab; see Examine failed rows for further details.

List of compatible metrics and checks for metric reconciliation checks

Optional check configurations

Example with name

  checks:
    - rows diff between 35000 and 36000:
        name: Simple row diff

Example with identity

  checks:
    - duplicate_count(last_name) diff < 1:
        identity: 05229d67-e3f0-***-a327-b2***84

Example with alerts

  checks:
    - row_count:
        fail: when diff > 10%
        warn: when diff between 5% and 9%

Example with quotes

  checks:
    - duplicate_count("last_name") diff = 0

Limitations and constraints

• The Python environment in which record reconciliation checks run consumes more time/CPU/memory because this type of check loads all data into memory to execute a comparison. Because record-to-record comparison is dense, exercise caution when executing scans with record reconciliation checks as they can cause usage spikes in the data source, and cost spikes in case of cloud-managed data sources. Best practice dictates that you add filters and use column constrained record reconciliation checks whenever possible to mitigate cost and performance issues. See also: Best practice for using reconciliation checks.
• Reconciliation checks on TEXT type columns are case sensitive.
• Record reconciliation checks do not support samples columns configuration.
• Reconciliation checks do not support samples columns in check configuration, nor exclude columns in the data source configuration in a configuration YAML file; see Disable failed rows sampling for specific columns.
• Known issue: Do not define a threshold as a percentage % if you expect the measurement of a metric to equal 0. Using a percentage for a threshold causes an error for an absolute check; the check evaluates correctly but the error persists with a non-zero exit command.

Go further

• Learn more about SodaCL metrics and checks in general.
• Learn more about Comparing data using SodaCL.
• Use a schema check to discover missing or forbidden columns in a dataset.
• Need help? Join the Soda community on Slack.
• Reference tips and best practices for SodaCL.
  

Was this documentation helpful?

What could we do to improve this page?

• Suggest a docs change in GitHub.
• Share feedback in the Soda community on Slack.

Documentation always applies to the latest version of Soda products
Last modified on 27-Sep-23

Sign up for Soda