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.
@hichkaker | Hacker News
From 9/18/2019, 7:06:08 AM till now, @hichkaker has achieved 131 Karma Points with the contribution count of 30.
Recent @hichkaker Activity
Show HN: Data Diff – compare tables of any size across databases
127 points • 21 comments
Datafold automates data pipeline testing for data engineers. With Datafold, data engineers can deal with data quality issues in the pull request by seeing how a change to source code impacts data produced throughout the entire data pipeline/DAG. Datafold is used by data teams at Patreon, Thumbtack, Substack, Angellist, among others, and raised $22M from YC, NEA & Amplify Partners. Our founding story and Launch HN: [https://news.ycombinator.com/item?id=24071955] Roles:
* Frontend Engineer: $180K - $250K + equity [https://bit.ly/3tW4zk7]
* Backend Engineer: $180K - $250K + equity [https://bit.ly/3J2mmdz]
* Data Solutions Engineer $130K – $200K + equity [https://bit.ly/3iPsNpY]
Salary ranges are for SF Bay Area (adjusted based on location factor), Intermediate to Staff levels.
Location: REMOTE (PST ±5) US visa sponsorship: yes
Stack: Python, Rust, FastAPI, PostgreSQL, Neo4j, ClickHouse | Typescript, React, Redux
Here are some projects you might work on:
* Static code analysis to compute column-level data lineage graph
* ML-based anomaly detection in multidimensional time series
* Data diff tool that finds discrepancies between 1B+ row datasets across databases
Contact: careers+hn@datafold.com
We actually built a tool exactly for that: https://www.datafold.com/data-diff The core use case is helping data engineers building analytical pipelines test the changes to their code prior to deploying, but can also work for any query. You can diff tables or just SQL queries directly.
Datafold automates data pipeline testing for data engineers. With Datafold, data engineers can deal with data quality issues in the pull request by seeing how a change to source code impacts data produced throughout the entire data pipeline/DAG. Datafold is used by data teams at Patreon, Thumbtack, Substack, Angellist, among others, and raised $22M from YC, NEA & Amplify Partners. Our founding story and Launch HN: [https://news.ycombinator.com/item?id=24071955]
Roles:
* Frontend Engineer: $180K - $250K + equity [https://bit.ly/3tW4zk7]
* Backend Engineer: $180K - $250K + equity [https://bit.ly/3J2mmdz]
* Data Solutions Engineer $130K – $200K + equity [https://bit.ly/3iPsNpY]
Salary ranges are for SF Bay Area (adjusted based on location factor), Intermediate to Staff levels.
Location: REMOTE (PST ±5) US visa sponsorship: yes
Stack: Python, Rust, FastAPI, PostgreSQL, Neo4j, ClickHouse | Typescript, React, Redux
Here are some projects you might work on:
* Static code analysis to compute column-level data lineage graph
* ML-based anomaly detection in multidimensional time series
* Data diff tool that finds discrepancies between 1B+ row datasets across databases
Contact: careers+hn@datafold.com
Datafold (YC S20) | https://www.datafold.com
Datafold automates data pipeline testing for data engineers. With Datafold, data engineers can deal with data quality issues in the pull request by seeing how a change to source code impacts data produced throughout the entire data pipeline/DAG. Datafold is used by data teams at Patreon, Thumbtack, Substack, Angellist, among others, and raised $22M from YC, NEA & Amplify Partners. Our founding story and Launch HN: [https://news.ycombinator.com/item?id=24071955]
Roles: - Frontend Engineer: $180K - $250K + equity [https://bit.ly/3tW4zk7] - Backend Engineer: $180K - $250K + equity [https://bit.ly/3J2mmdz] - Data Solutions Engineer $130K – $200K + equity [https://bit.ly/3iPsNpY]
Salary ranges are for SF Bay Area (adjusted based on location factor), Intermediate to Staff levels.
Location: REMOTE (PST ±5) US visa sponsorship: yes
Stack: Python, FastAPI, PostgreSQL, Neo4j, ClickHouse | Typescript, React, Redux
Here are some projects you might work on:
* Static code analysis to compute column-level data lineage graph
* ML-based anomaly detection in multidimensional time series
* Data diff tool that finds discrepancies between 1B+ row datasets across databases
Contact: careers+hn@datafold.com
We've been using Tella for product demos. A huge upgrade from hacking together QuickTime recordings!
Thank you for sharing!
I assume we are talking about analytical, not transactional data:
> Diff'ing data is one of the weakest and most cumbersome ways to verify correctness.
It depends on the use case: if the goal is to assess the impact of a change in source code on the resulting dataset produced (extremely common in ETL dev workflow in my experience), then isn't diff the natural solution? Of course, it depends on how the results are presented. A row-by-row output for a billion-row dataset is useless. That's why we provide diff stats across columns/rows and data distribution comparisons while allowing the user to see value-level diff if needed.
> Diffs are relatively slow
In general – yes, that's why we've implemented configurable sampling. In the majority of cases, developer is looking to assess the magnitude of difference and certain patterns, for which you don't need a large sample size. Our customers typically use ~1/10000 of the target table row count as a sample size.
> when they fail you get a blizzard of errors We try to fail gracefully :)
> I don't see how this helps with schema migration or performance issues.
For schema migration, you can verify whether anything has changed in your dataset besides the intended schema changes (which certainly happened on my watch).
> or performance issues
We certainly don't claim to solve all DBA issues with diff, but here's an actual real example from our customer: they are optimizing their ETL jobs in BigQuery to lower GCP bill by reducing query runtime. After refactoring the code, they diff production vs. new code output to ensure that the data produced hasn't been affected.
> If you really care about correctness it's better to use approaches like having focused test cases that check specific predicates on data.
Possibly, but
> They're also a pain to code
...which is often a prohibitive pain point if you have 300+ analytical datasets with 50+ columns each (a common layout for companies of 250+).
And another problem: the more test cases, the more failures on every run, and unlike app code unit testing, you can't expect the cases to stay relevant since the data is changing constantly, so those "unit testing" test suites require constant maintenance, and as soon as you stop actualizing them, their value drops to 0.
I think that diffing and "unit testing" are complimentary approaches and neither one is a panacea. So my recommendation has been to use both: 1) Specific test cases to validate the most important assumptions on data 2) Diff tool for regression testing.
Thank you for pointing it out, I've never noticed but now that you brought it up will do a few more iterations on our design to avoid the collision.
Support for MS SQL is in progress. We can deploy in your DC using with Docker or K8S.
If diffing datasets within the same physical database, generate SQL, execute in the database, analyze and render results.
If diffing datasets across physically different databases, e.g. PostgreSQL <> Snowflake or 2 distinct MySQL servers, pull data in our engine from both sources, diff, and show results.
Sampling is optional but helpful to keep compute costs low for large Mill/Bill/Trill-row datasets.
Totally fair! We offer free trials for on-prem. Please ping us at hn@datafold.com to set it up.
You raised a great point.
Data testing methods can perhaps be broken down to two main categories:
1. "Unit testing" – validating assumptions about the data that you define explicitly and upfront (e.g. "x <= value < Y", "COUNT(*) = COUNT(DISTINCT X)" etc.) – what dbt and great_expectations helps you do. This is a great approach for testing data against your business expectations. However, it has several problems: (1) You need to define all tests upfront and maintain them going forward. This can be daunting if your table has 50-100+ columns and you likely have 50+ important tables. (2) This testing approach is only as good as the effort you put to define the tests, back to #1. (3) the more tests you have, the more test failures you'll be encountering, as the data is highly dynamic, and the value of such test suites diminishes with alert fatigue.
2. Diff – identifies differences between datasets (e.g. prod vs. dev or source DB vs. destination DB). Specifically for code regression testing, a diff tool shows how the data has changed without requiring manual work from the user. A good diff tool also scales well: it doesn't matter how wide/long the table is – it'll highlight all differences. The downside of this approach is the lack of business context: e.g. is the difference in 0.6% of rows in column X acceptable or not? So it requires triaging.
Ideally, you have both at your disposal: unit tests to check your most important assumptions about the data and use diff to detect anomalies and regressions during code changes.
Thank you for the feedback!
Agree with you about the niche. Diff is our first tool that helps test changes in the ETL code, and the impact is correlated with the size and complexity of the codebase.
Diff also provides us a wedge into the workflow and a technical foundation to build the next set of features to track and alert on changes in data: monitoring both metrics that you explicitly care about and finding anomalies in datasets. We've learned that this is something a much larger number of companies can benefit from.
Thank you for the suggestion!
We're leaning towards Option 1: free diffing for datasets < 1M rows. Option 2 seems a bit tricker since we are in a way creating a new tool category and it can be harder to convince someone to pay before they try and understand the value (unlike, say, a BI tool – everyone knows they need some kind).
If you can materialize those .bcp files as tables, then yes, absolutely.
Thank you for asking deep questions and providing specific examples! The degree of scale and complexity varies significantly between companies, so we definitely can't claim that every ETL developer should use such a tool.
Philosophically, I see our diff tool playing a similar role to autocomplete/autorefactor in an IDE. Can you type/change every word manually? Yes. Is it a good use of your time though?
> But will this problem be big enough for VC investment is the question?
That's a great question. Thinking about where problems arise in data pipelines, there are fundamentally two moving pieces: 1) Your data – you're continuously getting new data without a real ability to enforce your assumptions on its schema or shape. 2) Your code for ingestion and transformation that needs to evolve with the business and to adapt to changes in other parts of the infra.
Datafold's Diff tool currently mostly addresses #2. It can add value to any company that runs ETL pipelines but most impactful at large data engineering teams (similar story to CI or automated testing tools).
Regarding #1, wouldn't it be useful if we tracked ALL your datasets across time and alerted you on anomalies in those datasets? And I am not talking about rigid "unit" tests e.g. X <= value < Y, but actual stats-based anomaly detection, akin to what Uber does: https://eng.uber.com/monitoring-data-quality-at-scale/
So, with diff, we already compute and store detailed statistical profiles on every column in the table. Next, we are going to track those profiles across time.
Diff is just the first tool we've built to get a wedge into the workflows of high-velocity data teams and start adding value, but it's just the beginning of a more comprehensive and, hopefully, valuable product we aspire to deliver.
So there are two main workflows:
1) Run diff as part of CI (on code change), that can be done via Github workflow (or other CI). The Github workflow takes in a YAML config file, and also is customizable so you can fit it according your needs. For example, one of our customers names SQL files after tables they populate, so when the bot sees a changed SQL file, it knows what table to compare it to.
2) If you are looking to run a diff within the data pipeline itself (e.g. in Airflow), you can wrap Datafold API in an Airflow operator to integrate it as a task in your DAG.
I hope that I covered your questions but please let me know if you are interested in specific use cases!
Yes, you can diff to physically distinct databases. MySQL support is in progress.
There is a REST API that accepts the same parameters as in the UI and returns the diff results in JSON + optionally, materializes the tables in your DB with row-level differences.
site design / logo © 2022 Box Piper