data-diff 0.3.1

Command-line tool and Python library to efficiently diff rows across two different databases.

data-diff

What is data-diff?

data-diff is a free, open-source tool that enables data professionals to detect differences in values between any two tables. It's fast, easy to use, and reliable. Even at massive scale.

Are you a developer with a deep understanding of databases and solid Python knowledge? We're hiring!

Documentation

🗎 Documentation website - our detailed documentation has everything you need to start diffing.

Databases we support

• PostgreSQL >=10
• MySQL
• Snowflake
• BigQuery
• Redshift
• Oracle
• Presto
• Databricks
• Trino
• Clickhouse
• Vertica
• DuckDB >=0.6
• SQLite (coming soon)

For their corresponding connection strings, check out our detailed table.

Looking for a database not on the list?

If a database is not on the list, we'd still love to support it. Please open an issue to discuss it, or vote on existing requests to push them up our todo list.

Use cases

Diff Tables Between Databases

Quickly identify issues when moving data between databases

Diff Tables Within a Database (available in pre-release)

Improve code reviews by identifying data problems you don't have tests for

   

Get started

Installation

First, install data-diff using pip.

pip install data-diff

To try out bleeding-edge features, including materialization of results in your data warehouse:

pip install data-diff --pre

Then, install one or more driver(s) specific to the database(s) you want to connect to.

• pip install 'data-diff[mysql]'

• pip install 'data-diff[postgresql]'

• pip install 'data-diff[snowflake]'

• pip install 'data-diff[presto]'

• pip install 'data-diff[oracle]'

• pip install 'data-diff[trino]'

• pip install 'data-diff[clickhouse]'

• pip install 'data-diff[vertica]'

• For BigQuery, see: https://pypi.org/project/google-cloud-bigquery/

Some drivers have dependencies that cannot be installed using pip and still need to be installed manually.

Run your first diff

Once you've installed data-diff, you can run it from the command line.

data-diff DB1_URI TABLE1_NAME DB2_URI TABLE2_NAME [OPTIONS]

Be sure to read the docs for detailed instructions how to build one of these commands depending on your database setup.

Code Example: Diff Tables Between Databases

Here's an example command for your copy/pasting, taken from the screenshot above when we diffed data between Snowflake and Postgres.

data-diff \
  postgresql://<username>:'<password>'@localhost:5432/<database> \
  <table> \
  "snowflake://<username>:<password>@<password>/<DATABASE>/<SCHEMA>?warehouse=<WAREHOUSE>&role=<ROLE>" \
  <TABLE> \
  -k activity_id \
  -c activity \
  -w "event_timestamp < '2022-10-10'"

Code Example: Diff Tables Within a Database (available in pre-release)

Here's a code example from the video, where we compare data between two Snowflake tables within one database.

data-diff \
  "snowflake://<username>:<password>@<password>/<DATABASE>/<SCHEMA_1>?warehouse=<WAREHOUSE>&role=<ROLE>" <TABLE_1> \
  <SCHEMA_2>.<TABLE_2> \
  -k org_id \
  -c created_at -c is_internal \
  -w "org_id != 1 and org_id < 2000" \
  -m test_results_%t \
  --materialize-all-rows \
  --table-write-limit 10000

In both code examples, I've used <> carrots to represent values that should be replaced with your values in the database connection strings. For the flags (-k, -c, etc.), I opted for "real" values (org_id, is_internal) to give you a more realistic view of what your command will look like.

We're here to help!

We know that in some cases, the data-diff command can become long and dense. And maybe you're new to the command line.

• We're here to help on slack if you have ANY questions as you use data-diff in your workflow.
• You can also post a question in GitHub Discussions.

To get a Slack invite - click here

How to Use

• How to use from the shell (or: command-line)
• How to use from Python
• How to use with TOML configuration file
• Usage Analytics & Data Privacy

How to Contribute

• Feel free to open an issue or contribute to the project by working on an existing issue.
• Please read the contributing guidelines to get started.

Big thanks to everyone who contributed so far:

Technical Explanation

Check out this technical explanation of how data-diff works.

License

This project is licensed under the terms of the MIT License.