data-diff: Compare datasets fast, within or across SQL databases

Use Cases

Data Migration & Replication Testing

Compare source to target and check for discrepancies when moving data between systems:

• Migrating to a new data warehouse (e.g., Oracle > Snowflake)
• Converting SQL to a new transformation framework (e.g., stored procedures > dbt)
• Continuously replicating data from an OLTP DB to OLAP DWH (e.g., MySQL > Redshift)

Data Development Testing

Test SQL code and preview changes by comparing development/staging environment data to production:

1. Make a change to some SQL code
2. Run the SQL code to create a new dataset
3. Compare the dataset with its production version or another iteration

> dbt Cloud users should check out Datafold's out-of-the-box deployment testing integration

👀 Watch 4-min demo video

Get started with data-diff & dbt

Also available in a VS Code Extension

Reach out on the dbt Slack in #tools-datafold for advice and support

How it works

When comparing the data, data-diff utilizes the resources of the underlying databases as much as possible. It has two primary modes of comparison:

joindiff

• Recommended for comparing data within the same database
• Uses the outer join operation to diff the rows as efficiently as possible within the same database
• Fully relies on the underlying database engine for computation
• Requires both datasets to be queryable with a single SQL query
• Time complexity approximates JOIN operation and is largely independent of the number of differences in the dataset

hashdiff

• Recommended for comparing datasets across different databases
• Can also be helpful in diffing very large tables with few expected differences within the same database
• Employs a divide-and-conquer algorithm based on hashing and binary search
• Can diff data across distinct database engines, e.g., PostgreSQL <> Snowflake
• Time complexity approximates COUNT(*) operation when there are few differences
• Performance degrades when datasets have a large number of differences

More information about the algorithm and performance considerations can be found here

Get started

Validating dbt model changes between dev and prod

⚡ Looking to use data-diff in dbt development? Head over to our data-diff + dbt documentation to get started!

Compare data tables between databases

🔀 To compare data between databases, install data-diff with specific database adapters, e.g.:

pip install data-diff 'data-diff[postgresql,snowflake]' -U

Run data-diff with connection URIs. In the following example, we compare tables between PostgreSQL and Snowflake using the hashdiff algorithm:

data-diff \
  postgresql://:''@localhost:5432/ \
   \
  "snowflake://:@//?warehouse=&role=" \
  <table></table> \
  -c  \
  -w <table></table> \
  -k