5
0
During this post we will discuss about the validation of .sql file before ingesting to the snowflake. As per the requirement we are getting DDL.sql file from source system. During the execution of the .sql file we encountered multiple errors which results partial DDL changes to the database. Scripts gets fail either because of syntactical error or invalid object definition.
Business want there should be validation process which should verify the authenticity of .sql file. In other words if there is any issue with DDL statement then it should report to the end user. Moreover, any partial DDL creation should be roll back. Say if file contains 5 Create table statements and script gets error out on 4th one then all statements should be roll back. Hence process remains atomic implies, if any operation is performed either it should be executed completely or should not be executed at all.
Say we have below sample_snowflake.sql where statement 2 and 4 are wrong.
Below is the Python code we will execute to validate the SQL file.
- Declare a valid_flg to identify if there is any error to the file or not, Initial we have define it as ‘T’, if there is any error we are setting it as ‘F’
- Declare an empty list i.e. error_log which will store the position of wrong sql statements in sql file.
- Execute the File , Successful execute will create Tables in DB and handle the error via try-catch block.
After
After Execution of above code, three tables are there in Snowflake:
- According to the requirement, we need to drop the partial creation of Snowflake tables. Hence we have prepared one drop.sql file in parallel to sample_Snowflake.sql
-----drop.sql------
drop TABLE IF EXISTS CUSTOMER1;
drop TABLE IF EXISTS CUSTOMER2;
drop TABLE IF EXISTS CUSTOMER3;
drop TABLE IF EXISTS CUSTOMER4;
drop TABLE IF EXISTS CUSTOMER5;
So in continuation of above code, Now execute the below python to drop the tables.
After the successful execution of the code, we see tables have been dropped from the system. Finally, we can validate the values inside the error_log list and it will return the list of erroneous statements.
Finally, as per your need you can dumps the output of list in your log table for your future reference.
