Snowflake Procedure for Database Comparison

Project Requirement: Snowflake Stored Procedure for Database Table Comparison We are seeking a skilled Snowflake developer to create a stored procedure that compares all tables in two different databases with the same schema and table structures. The stored procedure will generate a discrepancy report that details any differences found during the comparison. The report will be inserted into a new table, created as part of this project. Scope of Work Input Parameters: The stored procedure should accept the following input: schema_name (string): The schema name to traverse in both databases. Functionality: The procedure will: Traverse all tables within the specified schema in both databases. Compare the data in each corresponding table (same schema, same table name) between the two databases. Identify discrepancies, such as: Differences in row counts. Differences in column values for corresponding rows. Generate a one-line summary of discrepancies for each table (e.g., "Table <table_name> has 5 rows mismatched"). Output: The procedure will write the comparison results into a new table, which should: Include columns for table_name, schema_name, and a discrepancy_summary (string with details of discrepancies). Be created dynamically if it does not exist. Example of the report structure: schema_name table_name discrepancy_summary sales customers "Row counts differ by 10." sales orders "No discrepancies found." Error Handling: The procedure must gracefully handle any errors (e.g., missing tables, mismatched schemas) and log them into the report table. Assumptions: Both databases have the same schema structure (same tables, column names, and data types). The developer can use SQL constructs such as MINUS or EXCEPT for row-level comparison, or any other efficient comparison method. Deliverables Snowflake Stored Procedure: A stored procedure that meets the above functionality requirements. The procedure must be documented with comments explaining the logic. Report Table Creation: A table that stores the comparison results, created dynamically by the procedure if not already existing. Test Cases: Demonstration of the stored procedure comparing tables in two sample databases. At least three test cases, including: No discrepancies between tables. Differences in row counts. Differences in column values for specific rows. Documentation: Clear instructions on how to deploy and execute the stored procedure. A brief explanation of the logic used for comparison. Technical Requirements Database Environment: Snowflake Data Cloud. Performance: The procedure should be optimized for large datasets to minimize runtime. The comparison logic should account for Snowflake's ability to handle large-scale data processing. Compatibility: The procedure must work with Snowflake's SQL syntax and procedural language (SQL or JavaScript for Snowflake). Timeline Expected completion: 1 days from project start. The report will include a summary of discrepancies, such as row counts or mismatched rows tally.