Show HN: Data Diff – compare tables of any size across databases
21 comments
This seems to be very useful!
I notice that it casts everything to string for MD5 to work. In that case, how does it handle two databases having different types for the same columns? I'm thinking about floats and numerics (decimal places), timestamps (some have timezone support, some don't) and bytes (hex, base64) in particular, but there are definitely others that I'm missing as well.
I notice that it casts everything to string for MD5 to work. In that case, how does it handle two databases having different types for the same columns? I'm thinking about floats and numerics (decimal places), timestamps (some have timezone support, some don't) and bytes (hex, base64) in particular, but there are definitely others that I'm missing as well.
Great question, this is something we put a lot of effort into.
For both dates and numerics, we format them in a normalized format, and round them to the lowest mutual precision. It gets even more complicated, because when databases reduce the precision of timestamps, some of them round it, and some of them truncate. We implemented both, and we either truncate or round both timestamps, according to which database has the column with the lower precision.
We haven't got to bytes and strings yet, but it's on our list, and I imagine we'll use a similar approach.
For now, we print a warning whenever we don't have special handling for the column type. If you see a value mismatch where it shouldn't be, let us know and we'll implement it next.
For both dates and numerics, we format them in a normalized format, and round them to the lowest mutual precision. It gets even more complicated, because when databases reduce the precision of timestamps, some of them round it, and some of them truncate. We implemented both, and we either truncate or round both timestamps, according to which database has the column with the lower precision.
We haven't got to bytes and strings yet, but it's on our list, and I imagine we'll use a similar approach.
For now, we print a warning whenever we don't have special handling for the column type. If you see a value mismatch where it shouldn't be, let us know and we'll implement it next.
Hi, data engineer here. There are umpteen data engineering tools that have “Data” in the title. Have you considered a different name?
As a data engineer myself, totally agree about the abuse of the word “data”. What we strived for when naming was to make it self-describing as much as possible. Since the tool does one thing - diff datasets - we could name it “dataset diff” but that seemed more clunky. “table diff” wouldn’t work since we’re working on adding APIs (e.g. Stripe) as a data source to make validation of API-to-database syncs possible, and that goes beyond just tables. There is always an option to give a nondiscriptive or metaphorical name but we were concerned that would make the tool far less discoverable by potential users.
Awesome tool, we will definitely give it a try! 2 questions:
- how do you handle the data replication lag in the comparison?
- i assume that this works in identical tables between 2 databases, right? Any support for “similar” tables based on a column set? Imagine that we have a use case where we have a table X in one db, and another table Y in another db, with some columns from X and enhanced attributes.
- how do you handle the data replication lag in the comparison?
- i assume that this works in identical tables between 2 databases, right? Any support for “similar” tables based on a column set? Imagine that we have a use case where we have a table X in one db, and another table Y in another db, with some columns from X and enhanced attributes.
(1) You can specify `--min-age` to exclude records in the replication lag window
(2) We can do that, as long as they have the same name and compatible-ish types. There isn't support for differently named columns yet (but it's on the list)
Cheers. Open an issue if you run into trouble!
(2) We can do that, as long as they have the same name and compatible-ish types. There isn't support for differently named columns yet (but it's on the list)
Cheers. Open an issue if you run into trouble!
Pretty cool! I did a similar project for flatfiles, but used bloom filters to generate an “index” of row contents to test against later. I feel like a similar idea could work for identifying divergent rows within your segments more quickly/with less repeated work.
Making that work across databases could be a huge pain though, I had some success in Postgre but bitfields in the other DBs were painful.
Making that work across databases could be a huge pain though, I had some success in Postgre but bitfields in the other DBs were painful.
> Making that work across databases could be a huge pain though
That was indeed the main challenge. Each DB has a different syntax, different set of features, different format for timestamps and floats, different max precision, and so on. I'd say most of our work on data-diff went to making sure the behavior of the different DBs aligned with each other.
That was indeed the main challenge. Each DB has a different syntax, different set of features, different format for timestamps and floats, different max precision, and so on. I'd say most of our work on data-diff went to making sure the behavior of the different DBs aligned with each other.
What benefit does it give me over running table diff query in SQL?
Without the stupid default behavior of SQL, this wouldn't be a problem. I'm curious if Data Diff solves this or some other use case.
select * exclude (date_uploaded) from dev_table
except
select * exclude (date uploaded) from prod_table
select *
from (select * exclude (date_uploaded) from dev_table) dev
natural full join
(select * exclude (date_uploaded) from prod_table) prod
where dev.date_uploaded is null
or prod.date_uploaded is null
Without the stupid default behavior of SQL, this wouldn't be a problem. I'm curious if Data Diff solves this or some other use case.
I think snowflake supports this.
https://docs.snowflake.com/en/sql-reference/operators-query....
https://docs.snowflake.com/en/sql-reference/operators-query....
Snowflake doesn't support this kind of except, which is needed for the diff to work seamlessly:
select * except (column1, column2, ...)data-diff has several advantages over running the query you wrote here.
- data-diff can compare tables across different databases. Your query is limited to one database.
- For very big tables, your 'select' will time-out. data-diff splits the diff into small segments, so we side-step this issue.
- data-diff supports running in threaded mode, which means it can finish a LOT faster. (especially for cloud databases.)
- data-diff can compare tables across different databases. Your query is limited to one database.
- For very big tables, your 'select' will time-out. data-diff splits the diff into small segments, so we side-step this issue.
- data-diff supports running in threaded mode, which means it can finish a LOT faster. (especially for cloud databases.)
I've been a Datafold customer for a year at two different companies. Great experience. Very useful tool in your CI flow, the team is very responsive.
Just curious - is there a reason that SQL Server doesn't make the list of supported platforms (i.e. it appears that there is no plan to support in future)?
There is plans to support pretty much every database. The reason it’s not supported currently is because its md5 hashing is too slow, so we need to do something different for it, e.g. just sum for types that support it. It’s similar for databases we plan to support that don’t support MD5 too, for example ElasticSearch.
If anyone knows a dev on the MSSQL team we could speak to, we’d be eager to be connected
See https://github.com/datafold/data-diff/issues/51
If anyone knows a dev on the MSSQL team we could speak to, we’d be eager to be connected
See https://github.com/datafold/data-diff/issues/51
If you install Visual Studio Community edition you can do free cross server MSSQL data validation and syncing using a "database project". It won't be as flexible, or as scalable, as the Data Diff tool advertised though.
Does FDW let you do performant `FULL OUTER JOIN`s and/or `NATURAL FULL OUTER JOIN`s? If so then I would think that would be a decent place to start for remote DB diffs for PG. If might not be enough, of course, if the tables are huge, in which case taking a page from rsync and using some sort of per-row checksum as TFA does is clearly a good idea.
I'm not completely sure I understand your comment, so pardon me if I misunderstand. I don't think a foreign data wrapper would fundamentally to be more efficient with whatever table is ~foreign~, especially for an OUTER JOIN? Unless you're basically implementing something similar to data-diff with an OUTER JOIN with FDW, which seems possible
If you're doing in-database diffs, however, a join-based approach will likely outperform data-diff though.
Ideally databases would have support a standard MERKLE TREE INDEX so we could get extremely fast comparisons.
If you're doing in-database diffs, however, a join-based approach will likely outperform data-diff though.
Ideally databases would have support a standard MERKLE TREE INDEX so we could get extremely fast comparisons.
A naive FULL OUTER JOIN is O(N), which is not efficient, indeed.
An RDBMS could implement something like the rsync algorithm, or history tables, etc., to speed up a FULL OUTER JOIN.
The point is that FULL OUTER JOIN is the SQL table source "diff" primitive. Thus it seems natural to use that and let the RDBMS optimize it.
An RDBMS could implement something like the rsync algorithm, or history tables, etc., to speed up a FULL OUTER JOIN.
The point is that FULL OUTER JOIN is the SQL table source "diff" primitive. Thus it seems natural to use that and let the RDBMS optimize it.
As a company, Datafold builds tools for data engineers to automate the most tedious and error-prone tasks falling through the cracks of the modern data stack, such as data testing and lineage. We launched two years ago with a tool for regression-testing changes to ETL code https://news.ycombinator.com/item?id=24071955. It compares the produced data before and after the code change and shows the impact on values, aggregate metrics, and downstream data applications.
While working with many customers on improving their data engineering experience, we kept hearing that they needed to diff their data across databases to validate data replication between systems.
There were 3 main use cases for such replication:
(1) To perform analytics on transactional data in an OLAP engine (e.g. PostgreSQL > Snowflake) (2) To migrate between transactional stores (e.g. MySQL > PostgreSQL) (3) To leverage data in a specialized engine (e.g. PostgreSQL > ElasticSearch).
Despite multiple vendors (e.g., Fivetran, Stitch) and open-source products (Airbyte, Debezium) solving data replication, there was no tooling for validating the correctness of such replication. When we researched how teams were going about this, we found that most have been either:
Running manual checks: e.g., starting with COUNT(*) and then digging into the discrepancies, which often took hours to pinpoint the inconsistencies. Using distributed MPP engines such as Spark or Trino to download the complete datasets from both databases and then comparing them in memory – an expensive process requiring complex infrastructure.
Our users wanted a tool that could:
(1) Compare datasets quickly (seconds/minutes) at a large (millions/billions of rows) scale across different databases (2) Have minimal network IO and database workload overhead. (3) Provide straightforward output: basic stats and what rows are different. (4) Be embedded into a data orchestrator such as Airflow to run right after the replication process.
So we built Data Diff as an open-source package available through pip. Data Diff can be run in a CLI or wrapped into any data orchestrator such as Airflow, Dagster, etc.
To solve for speed at scale with minimal overhead, Data Diff relies on checksumming the data in both databases and uses binary search to identify diverging records. That way, it can compare arbitrarily large datasets in logarithmic time and IO – only transferring a tiny fraction of the data over the network. For example, it can diff tables with 25M rows in ~10s and 1B+ rows in ~5m across two physically separate PostgreSQL databases while running on a typical laptop.
We've launched this tool under the MIT license so that any developer can use it, and to encourage contributions of other database connectors. We didn't want to charge engineers for such a fundamental use case. We make money by charging a license fee for advanced solutions such as column-level data lineage, CI workflow automation, and ML-powered alerts.