Comparing two tables In Snowflake

In Snowflake, it is possible to compare the data in two tables to check for any discrepancies. This can be useful when performing data migrations, testing data integrity, or simply ensuring that two tables are in sync. In this article, we will go over one method for comparing tables in Snowflake: EXCEPT.

Step 0. Creating The Dataset

We are going to proceed to use the following dataset.

Then we are going to create the following dataset for comparing with the previous one

The only difference between those datasets is that the first one has some changes on the lastnames on record 1 (changing from Smith to SmithLee) and 2 (changing from Doe to DoeSon).Step 1. Making Comparisons Between The Datasets

Step 1. Making Comparisons Between The Datasets

For making the comparisons we are going to use the EXCEPT Operators

The EXCEPT operators are used to compare the rows in two tables. The EXCEPT operator returns the rows that are in the first table but not in the second table.

We could use the following code for making the comparison between both tables.

This query returns the difference between the two tables on the specified columns, but It will not give us more details regarding the comparisons.

We are going to use the following code for comparing both tables

The result of this query will be a table containing all the rows that are present in either TEST.TEST.EMPLOYEE or TEST.TEST.EMPLOYEE_012923, but not in both.

By doing that you have a more detailed way of comparing the information between two tables, on this way you will be able to know what differences do they have.

The process is useful for identifying data differences between tables, Additionally, you can use common table expressions(CTEs) can make the process more readable and easier to maintain.