6 ms·

Hi, I'm Alex from Datafold. By large tables we mean 1B+ rows. You'll likely do not want to do joins on them directly. > You just do an either-way except If o

Hi, I'm Alex from Datafold. By large tables we mean 1B+ rows. You'll likely do not want to do joins on them directly. > You just do an either-way except If one column in one of the tables is completely different, it'll return 1M rows. It's not exactly helpfull, so let's add an order by and limit. Now you'll need to spot differences in 100 column table, since hiding matching values is even more hand-written / generated sql. It's pretty good to know about all problems upfront and not find and fix them one-by-one, but creating representative sample of differences is difficult to do with hand-written sql. Then what about cross-db comparison, sampling, integrating with CI / github PRs? > There are easy solutions with checksums, error correction (comes free with networks) or round-tripping, is that a problem Some companies are using complex pipelines that can and sometimes do lose events. Often they don't need their data to be 100% perfect. So they need to know if they lost anything, what exactly and if it's within acceptable range. As always, roll-your-own is a valid approach, as is using already available tools to save time. Many large companies like Uber and Spotify with data teams of 100+ have detailed roadmaps for diffing tools because they find their data engineers spending days doing regression testing, and they must be proficient with SQL.

throwaway_pdp09 6y ago
Hi Alex, > You'll likely do not want to do joins on them directly. I can't see you have any alternative. Looking for any discrepancy in either table, you have to compare them both completely. Is there an alternative? (edit: actually there is, the checksumming I mentioned earlier. Never needed to do that). > so let's add an order by and limit. Now you'll need to spot differences in 100 column table, since hiding matching values is even more hand-written / generated sql. Yes, I added a limit for this too, and spotting two different columns out of a hundred, well it's not a big deal, but having done that I agree some GUI/other assistance would make it a lot nicer. > It's pretty good to know about all problems upfront and not find and fix them one-by-one... Well, you compare the schemas first and being small, you can do this very quickly so you can pick up errors there one after the other very easily. After that you compare the data, and I suppose you can run multiple queries one after the other in a batch file and check them when done. I guess I never had enough data to need to do that. > ...but creating representative sample of differences is difficult to do with hand-written sql. I'm not sure what you mean. > Then what about cross-db comparison, Straightforward. In fact if you weren't comparing data across DBs then you aren't doing ETL. (edit: ok I think ISWYM. Solutions are roundtripping if the amount of data is not overwhelming, checksumming otherwise). > sampling, integrating with CI / github PRs? I don't understand why any of these are needed, but I'm pretty certain that reflects on my lack of experience, so okay. > Some companies are using complex pipelines that can and sometimes do lose events Then you got a software quality problem. It's also not difficult (with joins against primary keys), to efficiently pick up the difference and to make things idempotent - just re-run the script. I've done that too. > Often they don't need their data to be 100% perfect. So they need to know if they lost anything, what exactly and if it's within acceptable range. Very good point. > As always, roll-your-own is a valid approach, as is using already available tools to save time this leads to very interesting question about upskilling of employees versus the cost of tools - good training (say, in SQL) is somewhat expensive but lack of knowledge is far more expensive. I don't think this is the right place to have such a discussion, and I don't want to detract from your product (I do seem to have dissed it which is not my intention). > Many large companies like Uber and Spotify Okay, if we're talking on the scale of companies like this then we talking something well out of my experience. I'll leave it here, I think a distracted enough from your launch, and I wish you well with it!
- hichkaker 6y ago
  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?
- etesial 6y ago
  > I can't see you have any alternative. Looking for any discrepancy in either table, you have to compare them both completely. Is there an alternative? The alternative is sampling. With analytical data often you have to accept that data isn't perfect, and that it doesn't need to be. So if it can be guaranteed that amount of "bad" data is, say, less than 0.001% than it may just be good enough. So cheap checks, like "how many PKs are null" can be done on full tables, anything that requires joins or mass-comaring all values can be done on sampled subsets. >> ...but creating representative sample of differences is difficult to do with hand-written sql. > I'm not sure what you mean. Let's say column "a" has completely different values in both tables. Column "b" has differences in 0.1% of rows. If we limit diff output to 100 rows in a straightforward way, most likely it won't capture differences in column "b" at all. So you'll need to fix column "a" first or remove it from diff, then you'll discover that "b" has problems too, then the issues with some column "c" may become visible. With right sampling you'll get 20 examples for each of "a", "b", "c" and whatever else is different, and can fix ETL in one go. Or maybe it'll help to uncover the root issue below those discrepancies faster. One very well may do without this feature, but it saves time and effort. Generally that's what the tool does, it saves time and effort. On writing one-off SQL, or building more generic scripts that do codegen, or building a set of scripts that handle edge cases, have a nice UX and can be used by teammates, or putting a GUI on top of that. > Solutions are roundtripping if the amount of data is not overwhelming, checksumming otherwise Agree, checksumming or sampling again. Since data is often expected to have small amounts of discrepancies, checksums would need to be done on blocks to see be able to drill down into failed blocks to see which rows caused failures. > Then you got a software quality problem. It's also not difficult (with joins against primary keys), to efficiently pick up the difference and to make things idempotent - just re-run the script. I've done that too. Agree, but software quality problems are bound to happen since requirements change, what was once a perfect architecture becomes a pain point. Attempts to fix some piece of the system often requires creating a new one from scratch and running both side-by-side during transitional period. Some companies begin to realize that they don't have enough visibility into their data migration processes, some understand the need for tooling, but can't afford to spend time or resources to build it in-house, especially if they'll need it only during migration. If we can help companies by providing ready to go solution, that's a great thing. > this leads to very interesting question about upskilling of employees versus the cost of tools Right, it's a complicated question involving multiple tradeoffs...

throwaway_pdp09 6y ago

Hi Alex, > You'll likely do not want to do joins on them directly. I can't see you have any alternative. Looking for any discrepancy in either table, you have to compare them both completely. Is there an alternative? (edit: actually there is, the checksumming I mentioned earlier. Never needed to do that). > so let's add an order by and limit. Now you'll need to spot differences in 100 column table, since hiding matching values is even more hand-written / generated sql. Yes, I added a limit for this too, and spotting two different columns out of a hundred, well it's not a big deal, but having done that I agree some GUI/other assistance would make it a lot nicer. > It's pretty good to know about all problems upfront and not find and fix them one-by-one... Well, you compare the schemas first and being small, you can do this very quickly so you can pick up errors there one after the other very easily. After that you compare the data, and I suppose you can run multiple queries one after the other in a batch file and check them when done. I guess I never had enough data to need to do that. > ...but creating representative sample of differences is difficult to do with hand-written sql. I'm not sure what you mean. > Then what about cross-db comparison, Straightforward. In fact if you weren't comparing data across DBs then you aren't doing ETL. (edit: ok I think ISWYM. Solutions are roundtripping if the amount of data is not overwhelming, checksumming otherwise). > sampling, integrating with CI / github PRs? I don't understand why any of these are needed, but I'm pretty certain that reflects on my lack of experience, so okay. > Some companies are using complex pipelines that can and sometimes do lose events Then you got a software quality problem. It's also not difficult (with joins against primary keys), to efficiently pick up the difference and to make things idempotent - just re-run the script. I've done that too. > Often they don't need their data to be 100% perfect. So they need to know if they lost anything, what exactly and if it's within acceptable range. Very good point. > As always, roll-your-own is a valid approach, as is using already available tools to save time this leads to very interesting question about upskilling of employees versus the cost of tools - good training (say, in SQL) is somewhat expensive but lack of knowledge is far more expensive. I don't think this is the right place to have such a discussion, and I don't want to detract from your product (I do seem to have dissed it which is not my intention). > Many large companies like Uber and Spotify Okay, if we're talking on the scale of companies like this then we talking something well out of my experience. I'll leave it here, I think a distracted enough from your launch, and I wish you well with it!

hichkaker 6y ago
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?
etesial 6y ago
> I can't see you have any alternative. Looking for any discrepancy in either table, you have to compare them both completely. Is there an alternative? The alternative is sampling. With analytical data often you have to accept that data isn't perfect, and that it doesn't need to be. So if it can be guaranteed that amount of "bad" data is, say, less than 0.001% than it may just be good enough. So cheap checks, like "how many PKs are null" can be done on full tables, anything that requires joins or mass-comaring all values can be done on sampled subsets. >> ...but creating representative sample of differences is difficult to do with hand-written sql. > I'm not sure what you mean. Let's say column "a" has completely different values in both tables. Column "b" has differences in 0.1% of rows. If we limit diff output to 100 rows in a straightforward way, most likely it won't capture differences in column "b" at all. So you'll need to fix column "a" first or remove it from diff, then you'll discover that "b" has problems too, then the issues with some column "c" may become visible. With right sampling you'll get 20 examples for each of "a", "b", "c" and whatever else is different, and can fix ETL in one go. Or maybe it'll help to uncover the root issue below those discrepancies faster. One very well may do without this feature, but it saves time and effort. Generally that's what the tool does, it saves time and effort. On writing one-off SQL, or building more generic scripts that do codegen, or building a set of scripts that handle edge cases, have a nice UX and can be used by teammates, or putting a GUI on top of that. > Solutions are roundtripping if the amount of data is not overwhelming, checksumming otherwise Agree, checksumming or sampling again. Since data is often expected to have small amounts of discrepancies, checksums would need to be done on blocks to see be able to drill down into failed blocks to see which rows caused failures. > Then you got a software quality problem. It's also not difficult (with joins against primary keys), to efficiently pick up the difference and to make things idempotent - just re-run the script. I've done that too. Agree, but software quality problems are bound to happen since requirements change, what was once a perfect architecture becomes a pain point. Attempts to fix some piece of the system often requires creating a new one from scratch and running both side-by-side during transitional period. Some companies begin to realize that they don't have enough visibility into their data migration processes, some understand the need for tooling, but can't afford to spend time or resources to build it in-house, especially if they'll need it only during migration. If we can help companies by providing ready to go solution, that's a great thing. > this leads to very interesting question about upskilling of employees versus the cost of tools Right, it's a complicated question involving multiple tradeoffs...