5 ms·

I see a lot of these things and I don't understand them. I've done too much ETL so I'm not naive. Now either 1) people are making a mountain out of a molehill (

I see a lot of these things and I don't understand them. I've done too much ETL so I'm not naive. Now either 1) people are making a mountain out of a molehill (not saying that's happening here, but in other cases I think so) 2) there's something my experience of ETL hasn't taught me or 3) these tools are specialised for niches. This one talks about 'large datasets' but I don't know how large that is. Some questions then > 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. select is fine for diffing. You just do an either-way except , something like ( 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 ) used this and it's fine on many rows (millions is fine but I do recommend and index and a DB with a halfway decent optimiser). > (2) Interesting. OK. > (3) Data transfer validation: moving large volumes of data between databases is error-prone Really? I never had a problem. What is 'large'? what problems have you seen? There are easy solutions with checksums, error correction (comes free with networks) or round-tripping, is that a problem? 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).

forrestb 6y ago
Data diffs are easy when the tables are already exactly the same. The union/except query you wrote returns no results. It's much harder when many things are wrong at once, and you have to debug: - Do schemas match? (same column names, column order, data types) - Are pk's unique? - Do pk's intersect? (pk's exclusive to one table?) - For intersecting pk's, how is data different? (string truncation, timestamp truncation, rounding, upper vs lower case, empty string vs null, etc)
- throwaway_pdp09 6y ago
  > Data diffs are easy when the tables are already exactly the same. The union/except query you wrote returns no results. It won't if the tables are identical. And if the tables differ, it a) tells you and b) gives you a very good idea where to look. In all honesty, none of things are a problem. > It's much harder when many things are wrong at once Then you find and fix each in turn. BTDT. > Do schemas match? (same column names, column order, data types) Well, if they don't and you're responsible you've really messed up, but that does happen (cough). Just do a similar diff via information_schema to show you schema discrepancies. > Do pk's intersect? this is just a subset of full-table diffing. Use my except/union but just on the PK fields (consult infoschema for the columns which are the PKs. If it has to be done for many tables, build the union/except dynamically then execute the string to allow for different tables with different columns names - I've done this too. Annoying but when done once you can just reuse it). > For intersecting pk's, how is data different? (string truncation, timestamp truncation, rounding, upper vs lower case, empty string vs null, etc) The aforementioned comparison using infoschema views will tell you where the schemas differs. Comparing the contents show you where the contents differ. All this is annoying but of very little work compared to a whole ETL process, and - emphatically - is reusable on the next job.
  - forrestb 6y ago
    > Well, if they don't and you're responsible you've really messed up, but that does happen (cough). Just do a similar diff via information_schema to show you schema discrepancies. Analysts with 1-2 years experience in SQL are often writing ETL. Responsible Data Engineers can't be there every time they deploy to production. > All this is annoying but of very little work compared to a whole ETL process, and - emphatically - is reusable on the next job. Yes, you can write your own tools for SQL QA. Making a standard tool that the entire company can and does use is another story.
    - mcrad 6y ago
      > Analysts with 1-2 years experience in SQL are often writing ETL This is the essence of Big Data.
etesial 6y ago
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!

forrestb 6y ago

Data diffs are easy when the tables are already exactly the same. The union/except query you wrote returns no results. It's much harder when many things are wrong at once, and you have to debug: - Do schemas match? (same column names, column order, data types) - Are pk's unique? - Do pk's intersect? (pk's exclusive to one table?) - For intersecting pk's, how is data different? (string truncation, timestamp truncation, rounding, upper vs lower case, empty string vs null, etc)

throwaway_pdp09 6y ago
> Data diffs are easy when the tables are already exactly the same. The union/except query you wrote returns no results. It won't if the tables are identical. And if the tables differ, it a) tells you and b) gives you a very good idea where to look. In all honesty, none of things are a problem. > It's much harder when many things are wrong at once Then you find and fix each in turn. BTDT. > Do schemas match? (same column names, column order, data types) Well, if they don't and you're responsible you've really messed up, but that does happen (cough). Just do a similar diff via information_schema to show you schema discrepancies. > Do pk's intersect? this is just a subset of full-table diffing. Use my except/union but just on the PK fields (consult infoschema for the columns which are the PKs. If it has to be done for many tables, build the union/except dynamically then execute the string to allow for different tables with different columns names - I've done this too. Annoying but when done once you can just reuse it). > For intersecting pk's, how is data different? (string truncation, timestamp truncation, rounding, upper vs lower case, empty string vs null, etc) The aforementioned comparison using infoschema views will tell you where the schemas differs. Comparing the contents show you where the contents differ. All this is annoying but of very little work compared to a whole ETL process, and - emphatically - is reusable on the next job.
- forrestb 6y ago
  > Well, if they don't and you're responsible you've really messed up, but that does happen (cough). Just do a similar diff via information_schema to show you schema discrepancies. Analysts with 1-2 years experience in SQL are often writing ETL. Responsible Data Engineers can't be there every time they deploy to production. > All this is annoying but of very little work compared to a whole ETL process, and - emphatically - is reusable on the next job. Yes, you can write your own tools for SQL QA. Making a standard tool that the entire company can and does use is another story.
  - mcrad 6y ago
    > Analysts with 1-2 years experience in SQL are often writing ETL This is the essence of Big Data.

etesial 6y ago

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!