forrestb

> 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.

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)

At best, Informatica could output the diff of two tables to another table. But the paradigm is very limiting. I'm not sure if you can dynamically change the mappings every time you feed it a new table (different primary keys, columns, data types), and you'd still have to browse the results in SQL.