Snowflake - compare 2 tables and send notification for mismatches

Question

I am looking for setting up a alert notification either from snowflake or aws side or by glue jobs / lambda functions using python or scala.

I would like to compare 2 tables which holds table names and counts in source and target.

data is loaded from s3 to snowflake via aws glue job and after that I would like to compare the 2 tables to verify if source and target record counts are matching and for any mismatches send a notification.

Please let me know your inputs to achieve this task.

Thanks, Jo

Answer 1

If you are using AWS Glue to load the tables in Snowflake, you can continue using Glue to orchestrate the desired result:

• Have Glue load the table.
• Have Glue run a stored procedure in Snowflake comparing both tables.
  • https://snowflakecommunity.force.com/s/article/How-to-Use-AWS-Glue-to-Call-Procedures-in-Snowflake
• Have AWS Glue send a notification through SNS.
  • https://aws.amazon.com/blogs/big-data/build-and-automate-a-serverless-data-lake-using-an-aws-glue-trigger-for-the-data-catalog-and-etl-jobs/
  • See the chapter "Monitoring and notification with Amazon CloudWatch Events".

If you need SQL for the stored procedure that compares two tables, please feel free to add a new question.