<story><title>Launch HN: Datafold (YC S20) – Diff Tool for SQL Databases</title><text>Hi HN! My name is Gleb. I&amp;#x27;m here with my co-founder Alex to tell you 
about our company Datafold (&lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;datafold.com&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;datafold.com&lt;/a&gt;). Datafold lets you diff
large datasets for fast and powerful regression testing. We support
databases such as PostgreSQL, Snowflake, BigQuery, and Redshift.&lt;p&gt;One of the biggest pain points in developing ETL pipelines – 
chains of jobs that move, clean, merge and aggregate analytical data – 
has been regression testing: verifying how a change in source code (mostly,
SQL) affects the produced data.&lt;p&gt;Early in my career, as an on-call data engineer at Lyft, I
accidentally introduced a breaking code change while attempting to
ship a hotfix at 4AM to a SQL job that computed tables for core
business analytics. A seemingly small change in filtering logic ended
up corrupting data for all downstream pipelines and breaking
dashboards for the entire company. Apart from being a silly mistake,
this highlighted the lack of proper tooling for testing changes. If
there had been a way to quickly compare the data computed by
production code vs. the hotfix branch, I would have immediately
spotted the alarming divergence and avoided merging the breaking
change.&lt;p&gt;Without a diffing tool, the typical options for regression testing
are: (1) Data “unit tests” (e.g. check primary key uniqueness, ensure
values are within interval, etc.) – these are helpful, but costly 
investment. Frameworks such as dbt make it easier, but it’s
often still prohibitively hard to verify all assumptions in a large
table. (2) Write custom SQL queries to compare data produced by the
prod and dev versions of the source code (e.g. compare counts, match
primary keys). This can easily take up 100+ lines of SQL and hours of
unsatisfying work, which no one really wants to do. (3) &amp;quot;Fuck It, Ship
It&amp;quot; is always an option but too risky nowadays as analytical data not
only powers dashboards but also production ML models.&lt;p&gt;As this problem is common in data engineering, some large
organizations have built and open-sourced their solutions – for
example, BigDiffy by Spotify. However, most of these tools are
CLI-based and produce results in a plain-text format which is hard to
comprehend when you are dealing with complex data.&lt;p&gt;To fit existing workflows of our users, we’ve built a web interface
with interactive charts showing both diff summary statistics (e.g. %
of different values by column) and value-level side-by-side comparison
(git diff style). But since the mission of the tool is to save
engineers as much time as possible, we also opened an API for
automation through Airflow or other orchestrators, and built a Github
workflow that runs diff on every pull request with changes to ETL
code. Since billion-row-scale datasets are not uncommon nowadays,
there is an optional sampling feature that helps keep compute costs
low and get results within a few minutes no matter how large the
dataset is.&lt;p&gt;We&amp;#x27;ve found Datafold to be a good fit for the following workflows: (1)
Developing data transformations – before an ETL job is shipped to
production, it undergoes multiple iterations. Often it’s important to
see how data changes between every iteration, and particularly useful
if you have 1M+ rows and 100+ columns where “SELECT *” becomes
useless. (2) Code review &amp;amp; testing: large organizations have hundreds
of people committing to ETL codebases. Understanding the impact of
even a modest SQL diff is daunting. Datafold can produce a data diff
for every commit in minutes so changes are well understood. (3) Data
transfer validation: moving large volumes of data between databases is
error-prone, especially if done via change data capture (CDC): a
single lost event can affect the resulting dataset in a way that is
tricky to debug. We allow comparing datasets across different
databases, e.g. PostgreSQL &amp;amp; Snowflake.&lt;p&gt;We&amp;#x27;ve set up a sandbox at &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;app.datafold.com&amp;#x2F;hackernews&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;app.datafold.com&amp;#x2F;hackernews&lt;/a&gt; so you can see
how diffing works. Shoot us an email (hn@datafold.com) to set up a trial and use it with
your own data.&lt;p&gt;We are passionate about improving tooling for data engineers and would
love to hear about your experience with developing data pipelines and
ensuring data quality. Also, if you think that dataset diffing can be
helpful in other domains, we are very curious to learn from you!</text></story><parent_chain></parent_chain><comment><author>GordonS</author><text>I was curious about pricing, but I see it&amp;#x27;s &amp;quot;call me pricing&amp;quot; with buttons to schedule a demo, so at least I can see this is squarely aimed at the enterprise. If I&amp;#x27;m being honest, I don&amp;#x27;t like seeing &amp;quot;call me pricing&amp;quot; on HN; there are no rules against it, but it just doesn&amp;#x27;t feel right on HN.&lt;p&gt;Are you able to say anything about pricing here?</text></comment>
<story><title>Launch HN: Datafold (YC S20) – Diff Tool for SQL Databases</title><text>Hi HN! My name is Gleb. I&amp;#x27;m here with my co-founder Alex to tell you 
about our company Datafold (&lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;datafold.com&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;datafold.com&lt;/a&gt;). Datafold lets you diff
large datasets for fast and powerful regression testing. We support
databases such as PostgreSQL, Snowflake, BigQuery, and Redshift.&lt;p&gt;One of the biggest pain points in developing ETL pipelines – 
chains of jobs that move, clean, merge and aggregate analytical data – 
has been regression testing: verifying how a change in source code (mostly,
SQL) affects the produced data.&lt;p&gt;Early in my career, as an on-call data engineer at Lyft, I
accidentally introduced a breaking code change while attempting to
ship a hotfix at 4AM to a SQL job that computed tables for core
business analytics. A seemingly small change in filtering logic ended
up corrupting data for all downstream pipelines and breaking
dashboards for the entire company. Apart from being a silly mistake,
this highlighted the lack of proper tooling for testing changes. If
there had been a way to quickly compare the data computed by
production code vs. the hotfix branch, I would have immediately
spotted the alarming divergence and avoided merging the breaking
change.&lt;p&gt;Without a diffing tool, the typical options for regression testing
are: (1) Data “unit tests” (e.g. check primary key uniqueness, ensure
values are within interval, etc.) – these are helpful, but costly 
investment. Frameworks such as dbt make it easier, but it’s
often still prohibitively hard to verify all assumptions in a large
table. (2) Write custom SQL queries to compare data produced by the
prod and dev versions of the source code (e.g. compare counts, match
primary keys). This can easily take up 100+ lines of SQL and hours of
unsatisfying work, which no one really wants to do. (3) &amp;quot;Fuck It, Ship
It&amp;quot; is always an option but too risky nowadays as analytical data not
only powers dashboards but also production ML models.&lt;p&gt;As this problem is common in data engineering, some large
organizations have built and open-sourced their solutions – for
example, BigDiffy by Spotify. However, most of these tools are
CLI-based and produce results in a plain-text format which is hard to
comprehend when you are dealing with complex data.&lt;p&gt;To fit existing workflows of our users, we’ve built a web interface
with interactive charts showing both diff summary statistics (e.g. %
of different values by column) and value-level side-by-side comparison
(git diff style). But since the mission of the tool is to save
engineers as much time as possible, we also opened an API for
automation through Airflow or other orchestrators, and built a Github
workflow that runs diff on every pull request with changes to ETL
code. Since billion-row-scale datasets are not uncommon nowadays,
there is an optional sampling feature that helps keep compute costs
low and get results within a few minutes no matter how large the
dataset is.&lt;p&gt;We&amp;#x27;ve found Datafold to be a good fit for the following workflows: (1)
Developing data transformations – before an ETL job is shipped to
production, it undergoes multiple iterations. Often it’s important to
see how data changes between every iteration, and particularly useful
if you have 1M+ rows and 100+ columns where “SELECT *” becomes
useless. (2) Code review &amp;amp; testing: large organizations have hundreds
of people committing to ETL codebases. Understanding the impact of
even a modest SQL diff is daunting. Datafold can produce a data diff
for every commit in minutes so changes are well understood. (3) Data
transfer validation: moving large volumes of data between databases is
error-prone, especially if done via change data capture (CDC): a
single lost event can affect the resulting dataset in a way that is
tricky to debug. We allow comparing datasets across different
databases, e.g. PostgreSQL &amp;amp; Snowflake.&lt;p&gt;We&amp;#x27;ve set up a sandbox at &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;app.datafold.com&amp;#x2F;hackernews&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;app.datafold.com&amp;#x2F;hackernews&lt;/a&gt; so you can see
how diffing works. Shoot us an email (hn@datafold.com) to set up a trial and use it with
your own data.&lt;p&gt;We are passionate about improving tooling for data engineers and would
love to hear about your experience with developing data pipelines and
ensuring data quality. Also, if you think that dataset diffing can be
helpful in other domains, we are very curious to learn from you!</text></story><parent_chain></parent_chain><comment><author>throwaway_pdp09</author><text>I see a lot of these things and I don&amp;#x27;t understand them. I&amp;#x27;ve done too much ETL so I&amp;#x27;m not naive. Now either 1) people are making a mountain out of a molehill (not saying that&amp;#x27;s happening here, but in other cases I think so) 2) there&amp;#x27;s something my experience of ETL hasn&amp;#x27;t taught me or 3) these tools are specialised for niches. This one talks about &amp;#x27;large datasets&amp;#x27; but I don&amp;#x27;t know how large that is.&lt;p&gt;Some questions then&lt;p&gt;&amp;gt; Often it’s important to see how data changes between every iteration, and particularly useful if you have 1M+ rows and 100+ columns where “SELECT *” becomes useless.&lt;p&gt;select is fine for diffing. You just do an either-way except , something like&lt;p&gt;&lt;pre&gt;&lt;code&gt;  (
  select f1, f2, f3 ... f100
  from t1
  except
  select f1, f2, f3 ... f100
  from t2
  )
  union 
  (
  select f1, f2, f3 ... f100
  from t2
  except
  select f1, f2, f3 ... f100
  from t1
  )
&lt;/code&gt;&lt;/pre&gt;
used this and it&amp;#x27;s fine on many rows (millions is fine but I do recommend and index and a DB with a halfway decent optimiser).&lt;p&gt;&amp;gt; (2)&lt;p&gt;Interesting. OK.&lt;p&gt;&amp;gt; (3) Data transfer validation: moving large volumes of data between databases is error-prone&lt;p&gt;Really? I never had a problem. What is &amp;#x27;large&amp;#x27;? what problems have you seen? There are easy solutions with checksums, error correction (comes free with networks) or round-tripping, is that a problem?&lt;p&gt;Edit, just done that with mssql tables, 8 cols, 38 bytes per row, ~776,000 rows (identical but for one row), diff as above takes 2 seconds without an index (with PK it takes 5 seconds. Sigh. Well done MS). The single row discrepancy shows up fine.  Totally trivial to extend it to 100 columns (did that too in previous job).</text></comment>