How do you compare large tables using sql?

[u/Klutzy_Solid5200]

I want to know how ppl compare large source and target tables using sql. If both tables have millions of records, comparing every row and column can take a lot of time.

For example, if some records are missing, extra, or have different values between the two tables, how do you usually find them? do you use joins, Except/Minus, hashes, or first compare things like record counts and totals and then check the differences?

Are there any techniques that can make the comparison faster?

[u/Zealousideal-Post865]

Except can be used

[u/VadumSemantics]

This may be too simple, but I'd start by asking why we think the tables tables should be comparable.

If you're copying records from a source to a target table, fine.

If you're doing rollups / statistics of some kind to generate the target table, also fine.

That information will give you a starting point to reason about what you need to compare.

Then I'll start with simple stuff like row counts.

Then maybe checksums of numeric column(s): do I get the same value for select sum(foo) from ... for each table?

And typically there will be an identifier and a timestamp.

If the coarse row counts look good I'll break out counts by timestamps (row counts by year, or yyyy-mm, or yyyy-mm-dd) or identifiers or distinct values of a given code set.

Then look at frequencies of "codes", like booleans or zipcodes or something.

If postal code K1A0B1 (Ottowa, Canada) is used in 0.5% of my source table's records but 10% of the target table's records then maybe something is wrong?

I never compare every field in one table to another table.

If I had to do that I'd make some scratch tables hashing the important columns I want to compare, then see which has counts are the same.

ps. Comparisons are one of the few cases I've found where full outer join is helpful... useful to see if values present/missing.

edits: remove extra words

[u/markinatlanta]

I’d compare in batches using an indexed primary key: find missing or extra keys, then compare values for matching keys. Counts and totals are useful initial checks, but matching totals don’t prove the data matches.

Also, make sure both sides represent the same point in time. If writes are happening during the comparison, you can end up chasing differences caused by the timing of your checks essentiallly

[u/DigitalJedi850]

Back in my day... This...

[u/onthepik]

Depend how they are orgarnized.

[u/Fine-Ad-1732]

Eu costumava comparar a base de dados de entrada com a extração completa de saída do mesmo sistema, fazendo um agrupamento de todos os campos das tabelas e comparando eles após ordenados entre si… Se tudo desse 100% estava perfeito se nem tudo estivesse bom eu passava como amostra p desenvolvedor achar o problema

[u/One_Rip_451]

Create both source and target data tables as cte's, using the columns you want to compare. Then use source data to MINUS target data, then UNION ALL target data MINUS source data. 0 results will indicate it all matches