We are going to discuss an interesting stored procedure scenario w.r.t Event tables. Say we have stage table or Metadata table that stores table name, Snowflake stage name and corresponding File Format details. As part of the requirement we have to develop a COPY process which will take input from stage table. Process will generate the COPY command dynamically and load the data into respective table. During the execution process it can be the possibility that command gets fail because of any error say invalid file format. Here the EVENT tables comes into the picture and capture any error occurs during the data load and log them for further analysis. Moreover we have also captures the Process start time and completion time along with the COPY statement generated by Code dynamically.
We have developed the below stored procedure using EVENT tables. “snowflake.log()” is used to log the message in Event tables.
- Stage table stores the relevant details.
- Stored Procedure to implement Event tables.
- Call the Procedure and Query the Event table:
SELECT TIMESTAMP,RESOURCE_ATTRIBUTES,RECORD,VALUE FROM demo_db.public.logging_event_table order by timestamp desc;
- Analyze the output inside the Event table.
As we can see one of the COPY command to load data into Parquet table gets fail. Error details along with the statement is available in Event table and we can analyze the reason of failure. Also the successful COPY commands are capture inside the table. We can also verify the process start and completion time as well.
- Finally, Verify the data count inside the table.