5 ms·

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 wr

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.

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.