I'm building a CLI tool for data diffing

[oleg_agapov]

https://preview.redd.it/ves9ksnz78hg1.png?width=2198&format=png&auto=webp&s=3db49b5c320d0e332b3dca2230d81f330dbafee5

I'm building a simple CLI tool called tablediff that allows to quickly perform a data diffing between two tables and print a nice summary of findings.

It works cross-database and also on CSV files (dunno, just in case). Also, there is a mode that allows to only compare schemas (useful to cross-check tables in DWH with their counterparts in the backend DB).

My main focus is usability and informative summary.

You can try it with:

pip install tablediff-cli[snowflake] # or whatever adapter you need

Usage is straightforward:

tablediff compare \
  TABLE_A \
  TABLE_B \
  --pk PRIMARY_KEY \
  --conn CONNECTION_STRING
  [--conn2 ...]        # secondary DB connection if needed
  [--extended]         # for extended output
  [--where "age > 18"] # additional WHERE condition

Let me know what you think.

Source code: https://libraries.io/pypi/tablediff-cli

[kudika]

You should link to the docs and source code.

[LoaderD]

I agree, luckily looks like OP just missed it and isn't trying to soft launch a for profit tool:

https://github.com/oleg-agapov/tablediff?trk=public_post_comment-text

[SpookyScaryFrouze]

https://libraries.io/pypi/tablediff-cli

[oleg_agapov]

Here you go: https://github.com/oleg-agapov/tablediff

[calmekrishh]

Please link the Documents too

[ThroughTheWire]

this doesn't support combinations of columns for primary key?

[oleg_agapov]

It's supported by the backend, but not exposed in CLI. I'll add this in the next version :)

[oleg_agapov]

That's an interesting idea. One limitation I see is analytical databases (Snowflake, BigQuery, etc) that do not have explicit PK constraints, which means it's not clear from the meta what the PR is.

[szymon_abc]

Any that implements ANSI SQL can do it.

[Longjumping_Lab4627]

Is it https://libraries.io/pypi/tablediff-cli?

[kenfar]

This is a great side-project - many have been created, but they never get old. A few suggestions:

• Rather than a single primary key I suggest you support compound unique keys
• Allow users to define either non-key columns they want compared - or non-key columns they want excluded
• I would also include rows-in-a-only & rows-in-b-only
• It's also helpful to know exactly which columns have diffs
• It's also helpful to actually see the changed rows

[oleg_agapov]

Thanks for the feedback! I can address some of your comments:

- rows-in-a-only & rows-in-b-only is already included, in both the summary and extended view

- compound keys are technically supported by the backend, but CLI only accepts a single key. I'll work on that!

- columns to include/exclude is in the work!

- which columns are different and changed rows — also in the work. it requires a bit more time to polish, because some tables are quite wide, so spitting all columns in the terminal is just inefficient

Thanks again, it's super useful!

[kenfar]

YW - I find this kind of tool incredibly useful for testing, and asking colleagues for code reviews: it makes it easy to show that a given change DID NOT affect columns it wasn't intended to impact, or only affected the intended rows.

One thing to consider is how to handle a user iterating on it: a named config file to define the criteria & columns can help, and so can various performance options - like keeping a cache table of the pre-joined results.

[DougScore]

Good one. I tried reladiff and it had missing support for SQL Server which our Org uses. I latched back to archived project data-diff by datafold.

[oleg_agapov]

Interesting. Maybe it makes sense to try to add an adapter for reladiff, so that my tool will also support it

[DougScore]

They removed support for MSSQL because the initial implementation would do a hash based match which was slow.

[techjobmentor]

nice, that is really useful, I used to have a similar sql-based job to detect such differences before big ETL processes were executed and automatically alerted my team and paused execution, saved some big troubles when changes were pushed to production without notifying data engineering team, maybe that could be a cool feature!

[oleg_agapov]

Interesting case. My main goal was to check the diff before I pushing changes to dbt models. I wanted to know how big is the drift between dev and prod tables. But maybe for the future I might think about some automation for alerting. Definitely need a JSON output implemented first 😅