Compare tables as DataFrames using Snowpark

A common challenge for database developers is data comparison. Is there a fast and reliable method to compare data in Snowflake?

Let’s say we need to update/remove specific fields only if there is a change or we need to match equal or not equal rows based on a unique identifier. There are multiple solutions to this, to name a few: hashing row information, using streams to manage change, etc. But what if there is another way that goes beyond Snowflake SQL? Snowflake does have python integration after all…

I thought about how python is a good tool for data operations and how can we make use of Snowpark to take on this problem. We can easily create a python procedure/function in snowflake but what if we can somehow make use of pandas.Dataframe.eq to compare two tables with the same structure for differences? We could implement that generic solution in our developments to compare data.

Therefore I came up with the following solution:

It’s a simple procedure that takes two table names and their uid-column name and converts table data to pandas dataframe. It will then return data as a table where we only keep the index/uid and isequal (0/1 if false or true). We can then access this result table and based on equality or non-equality we have fast access to uids for the needed operations.

To get the returned data we simply do the following:

That can then be used in a temp table or as is for DML in any developed solution based on needs.