hichkaker

Thank you for mentioning Data Diff! Founder of Datafold here. We built Data Diff to solve a variety of problems that we encountered as data engineers: (A) Testing SQL code changes by diffing the output of production/dev versions of SQL query. (B) Validating that data is consistent when replicating data between databases.

Data Diff has two algorithms implemented for diffing in the same database and across databases. The former is based on JOIN, and the latter utilizes checksumming with binary search, which has minimal network IO and database workload overhead.

As a data engineer, validating your SQL code as you develop pipelines is hard. It requires running the code to materialize a "development" dataset, then auditing the resulting dataset to ensure your code produces the correct output. That tedious process usually involves writing lots of ad-hoc SQL scripts.

We built an integration with a popular framework for orchestrating SQL pipelines – dbt – that enables developers quickly profile their development datasets and diff them against production to audit their work as they code. It's free for individual developers. (We make money by selling CI/CD validation to teams)

I did data engineering for 6 years and am building a company to automate SQL validation for dbt users.

First, by “testing SQL pipelines”, I assume you mean testing changes to SQL code as part of the development workflow? (vs. monitoring pipelines in production for failures / anomalies).

If so:

1 – assertions. dbt comes with a solid built-in testing framework [1] for expressing assertions such as “this column should have values in the list [A,B,C]” as well checking referential integrity, uniqueness, nulls, etc. There are more advanced packages on top of dbt tests [2]. The problem with assertion testing in general though is that for a moderately complex data pipeline, it’s infeasible to achieve test coverage that would cover most possible failure scenarios.

2 – data diff: for every change to SQL, know exactly how the code change affects the output data by comparing the data in dev/staging (built off the dev branch code) with the data in production (built off the main branch). We built an open-source tool for that: https://github.com/datafold/data-diff, and we are adding an integration with dbt soon which will make diffing as part of dbt development workflow one command away [2]

We make money by selling a Cloud solution for teams that integrates data diff into Github/Gitlab CI and automatically diffs every pull request to tell you the how a change to SQL affects the target table you changed, downstream tables and dependent BI tools (video demo: [3])

I’ve also written about why reliable change management is so important for data engineering and what are key best practices to implement [4]

[1] https://docs.getdbt.com/docs/build/tests [2] https://github.com/calogica/dbt-expectations [3] https://github.com/datafold/data-diff/pull/364 [4] https://www.datafold.com/dbt [5] https://www.datafold.com/blog/the-day-you-stopped-breaking-y...

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.

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.

Those tools are definitely vastly powerful. Have you used either of them?

TBH, I haven't, but judging from our current post-Informatica users and by reading questions on Informatica/Talend official user forums, I concluded that the diffing problem (to be specific – not only schema, but data diffing) is not directly addressed by them (the answers are in the realm of "there is no diff feature but you can write SQL..."

In general, we see data stacks becoming increasingly modularized and tools more specialized. For example, there are at least 20x more teams using OSS like Airflow/Luigi/Dagster for managing their data pipelines (and 2-5 other tools for the rest of the workflow) than using end-to-end platforms that you mentioned. We see Datafold as a regression testing tool in a modular stack.

Thanks! We are going to add an example to our website and perhaps publish it as a Github App.

The flow is: For every whitelisted (say, SQL) file changed by a pull request, our bot compares the production data (produced by the master branch) vs. the data output of the new branch and pastes the diff as a comment to the PR. Some YAML configuration is needed to, for example, adjust it to your repo file structure.

Yes, it does diff the schema too.

The main use cases we've seen: 1) You made a change to some code that transforms data (SQL/Python/Spark) and want to make sure the changes in the data output are as expected. 2) Same as (1) but there is also some code review process. In addition to checking someone's source code diff, you can see the data diff. 3) You copy datasets between databases, e.g. PostgreSQL to Redshift and want to validate the correctness of the copy (either ad-hoc or on a regular basis).

We have a signup-free sandbox where you can see the exact views we provide, including schema diff: https://app.datafold.com/hackernews

Yes, you could. The limit is for the source dataset size but you can prefilter it (there is an option to pass in free-form SQL query instead of table name when creating a diff).

For the majority of diffs we see with sampling applied, sample sizes are <1M rows (more is often impractical in terms of information gain for higher compute costs) especially if your goal is to assess the magnitude of the difference as opposed to get every single diverging row.

Yes, we can add support for Oracle quite soon if you are interested.

Yes, we have on-prem AWS/GCP and K8S-based deployments available, would be very happy to support your work, just shoot me an email hn@datafold.com.

Thank you!

We are [almost] DB-agnostic (as long as there is SQL interface and JOINs are supported) and have been adding support for particular DBs based on the interest from our users. SQL Server & Azure SQL areon the roadmap, please let us know if you have a use case in mind to pilot Datafold on them.

Definitely a fair point!

The primary reason we haven't provided pricing is that we have just launched and wanted to collect more data points before setting making the pricing public.

Our current offering is:

1) Free for diffing datasets < 1M rows 2) $90 / mo / user for diffing datasets of unlimited size 3) Cross-database diff, on-prem (AWS/GCP/data center) deploy, Single-sign-on are in custom-priced enterprise bucket.

We would love to hear your thoughts on this.