Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: Data Diff – compare tables of any size across databases

news.ycombinator.com

Post reply on HN
Print view View original

11–20 of 22 posts

Re: Show HN: Data Diff – compare tables of any size across databases

#11

Post by pbnjay »

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.

Re: Show HN: Data Diff – compare tables of any size across databases

#13

Post by snidane »

What benefit does it give me over running table diff query in SQL?

  select * exclude (date_uploaded) from dev_table
  except
  select * exclude (date uploaded) from prod_table
or

  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
The only issue with the above is that EXCLUDE/EXCEPT is missing from standard SQL and even from market leaders like Snowflake, making this a massive pain in the ass. Second, natural joining in presence of null fields is going to produce a mess instead of something useful. Again - analytics db providers would rather boast about adhering to an ancient standard from the 1970s than listening to users and actually making SQL work after all those decades of pain.

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.

Re: Show HN: Data Diff – compare tables of any size across databases

#14

Post by malcolp »

snidane wrote: post #13

What benefit does it give me over running table diff query in SQL? select * exclude (date_uploaded) from dev_table except select * exclude (date uploaded) from prod_table or 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 The only issue with the above is that EXCL…

I think snowflake supports this.

https://docs.snowflake.com/en/sql-reference/operators-query....

Re: Show HN: Data Diff – compare tables of any size across databases

#15

Post by snidane »

malcolp wrote: post #14
snidane wrote: post #13

What benefit does it give me over running table diff query in SQL? select * exclude (date_uploaded) from dev_table except select * exclude (date uploaded) from prod_table or 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 The only issue with the above is that EXCL…

I think snowflake supports this. 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, ...)

Re: Show HN: Data Diff – compare tables of any size across databases

#16

Post by erezsh »

snidane wrote: post #13

What benefit does it give me over running table diff query in SQL? select * exclude (date_uploaded) from dev_table except select * exclude (date uploaded) from prod_table or 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 The only issue with the above is that EXCL…

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.)

Re: Show HN: Data Diff – compare tables of any size across databases

#17

Post by erezsh »

pbnjay wrote: post #11

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

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.

Re: Show HN: Data Diff – compare tables of any size across databases

#18

Post by hichkaker »

oa335 wrote: post #12

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.

Re: Show HN: Data Diff – compare tables of any size across databases

#20

Post by cryptonector »

Sirupsen wrote: post #7
cryptonector wrote: post #6

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 likel…

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.

Post reply on HN