When working with Snowflake, there might be scenarios where you need to compare two tables to determine differences. These differences might be at the level of structure (columns) or at the data level (rows).
1. Compare Structures of Two Tables
To determine if the tables have the same structure:
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YOUR_FIRST_TABLE'
AND TABLE_SCHEMA = 'YOUR_SCHEMA'
EXCEPT
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YOUR_SECOND_TABLE'
AND TABLE_SCHEMA = 'YOUR_SCHEMA';
If the result is empty, the structures match. Otherwise, the result will show differences.
2. Compare data between two tables
To check if two tables have the same data, you can use a FULL OUTER JOIN to find differences. Assuming tables table_A and table_B:
SELECT *
FROM table_A
FULL OUTER JOIN table_B
ON table_A.primary_key = table_B.primary_key
WHERE table_A.primary_key IS NULL OR table_B.primary_key IS NULL;
The outcome will display rows present in one table but absent in the other. Modify the join conditions accordingly to suit your table structures.
For comparing all columns:
SELECT *
FROM table_A a
FULL OUTER JOIN table_B b
ON a.primary_key = b.primary_key
WHERE NOT EXISTS (
SELECT a.* EXCEPT SELECT b.*
);
3. Count rows in both tables
Simple row counts can also give insights:
SELECT
(SELECT COUNT(*) FROM table_A) AS table_A_count,
(SELECT COUNT(*) FROM table_B) AS table_B_count;
4. Check for exact row matches
If you want to determine if every row in table_A matches a row in table_B:
SELECT COUNT(*)
FROM table_A
MINUS
SELECT COUNT(*)
FROM table_B;
Snowflake important urls to refer
