0
0
Error Logging: During this post we will discuss how to handle the Erroneous records while copying the data from one table to another in snowflake. Consider the scenario where we are copying the data from one of our staging table to the target table. In case if any error occurs during the copy operation, it should be log into the Audit or Error Log table. While the successful records should be copy to the Target table. So in short, Instead of process getting fail in case of any error, process should continue while logging the error into Error log table.
In Snowflake we have handled it using the Javascript procedure.
Lets Say:
Source table : CUSTOMER_LIST having one column CUST_NO with 5 records where CUST_NO is NULL for one record
Target Table: TARGET_ CUSTOMER_LIST where NOT NULL Constraint is defined on CUST_NO
Logging Table: ERROR_LOG
So in below procedure we are getting the data from our Source table and executed the loop to number of ROWS in SOURCE table.
Used the WHILE LOOP to move the CURSOR and inserting the ROW-BY-ROW into the TARGET TABLE, In case of any Error comes (Copying NULL CUST_NO to the NON NULL field in TARGET TABLE) ,Use the TRY-CATCH block and insert the Error details in ERROR_LOG TABLE
Execute the PROC:
call LOAD_TABLE_B();
Note: Please note this can be one of the approach to handle the Error while loading the data from source to target table, but struggling with the performance as we are inserting data row-by-row. Kindly feel free to add your input to achieve the same in much better way or performance oriented way.
