Tutorial: Enable logging and capture errors
This tutorial provides step-by-step instructions to enable logging and capture errors in Snoweaver jobs.
Prerequisites
Before continuing with this tutorial, ensure you have completed the following prerequisites:
Complete Tutorial: Retrieve data using a web request job.
Note
Logging in Snowflake incurs costs as detailed in Snowflake Logging and Tracing Billing. Snoweaver logs only fatal messages to the event table when it is active.
Raise exception for status
Open the LEARN_DEV project in Snoweaver with the LEARN_DEV_DEVELOPER role.
Open the job sfdc_get_object_data and modify the id variable’s test value to abc, which is an invalid account ID.
In the Response Handler, ensure the option Raise exception for status is selected.
Click Make a Test Call. Salesforce will respond with a 404 status code, triggering the job to raise an exception due to the unsuccessful status code.
Click Build to deploy a new instance.
Run the following statement in the worksheet with a role that has access to the EVENTS table in the SNOWFLAKE.TELEMETRY schema:
SELECT TIMESTAMP as EVENT_TIME, RESOURCE_ATTRIBUTES['snow.executable.name'] as EXECUTABLE_NAME, RECORD['severity_text'] as SEVERITY_LEVEL, RECORD_ATTRIBUTES:"exception.message" as EXCEPTION_MESSAGE, FROM SNOWFLAKE.TELEMETRY.EVENTS WHERE RESOURCE_ATTRIBUTES['snow.application.name'] = 'SNOWEAVER' ORDER BY OBSERVED_TIMESTAMP DESC;
You’ll observe that the same error has been recorded. If you don’t see the event, please try querying again in a minute.
Run the following statement to call the job instance. Note that Snowflake may return an unspecified computation error due to the ownership of the job instance.
SELECT SNOWEAVER.LEARN_DEV.SFDC_GET_OBJECT_DATA('ACCOUNT','abc');
If you query the even table again, you will find that the error has been logged with complete details. If you don’t see the event, please try querying again in a minute.
Additionally, you can create views to manage access to the event table based on environments or teams. For instance, here is how to create a view that displays errors in the DEV environment:
USE SCHEMA LEARN_SW.DEV; CREATE OR REPLACE VIEW V_SW_DEV_EVENTS AS SELECT OBSERVED_TIMESTAMP, RECORD['severity_text'] AS SEVERITY_LEVEL, RECORD_ATTRIBUTES:"exception.message" AS EXCEPTION_MESSAGE, RESOURCE_ATTRIBUTES, RECORD_ATTRIBUTES FROM SNOWFLAKE.TELEMETRY.EVENTS WHERE RESOURCE_ATTRIBUTES:"snow.schema.name" LIKE '%_DEV' AND RESOURCE_ATTRIBUTES['snow.application.name'] = 'SNOWEAVER'; GRANT SELECT ON VIEW V_SW_DEV_FATAL_EVENTS TO ROLE LEARN_DEV_DEVELOPER; USE ROLE LEARN_DEV_DEVELOPER; SELECT * FROM LEARN_SW.DEV.V_SW_DEV_EVENTS ORDER BY OBSERVED_TIMESTAMP DESC;
Handle Errors with Post-hooks
The Raise Exception for Status setting does not provide detailed error messages from the response payload. To include these messages in the exception event, you can use a post-hook.
Return to the job sfdc_get_object_data.
Disable Raise exception for status and make another test call.
You will now see the error message included in the response, which explains the reason for the 404 status code.
Save the job.
Import the sfdc_lib macro library
Note
If you’ve already imported the sfdc_lib macro library from previous tutorials, you can skip this section.
Open the LEARN_DEV project in Snoweaver with the LEARN_DEV_DEVELOPER role.
Open the Home page.
Use the Importing / Exporting Resources function to import the the following library.
Format: On Screen Resource Type: Macro Lib New record: checked Name: sfdc_lib
YAML Resource Data:
macros: |- {%- macro get_job_info(response,interval,iterations) -%} {%- for i in range (iterations) -%} {%- set result=call_proc('sfdc_get_job_info',response) -%} {%- set result_dict=json_loads(result) -%} {%- if result_dict.state in ['JobComplete'] -%} {{ result }} {%- break -%} {%- elif result_dict.state in ['Aborted','Failed'] -%} {{ raise_exception(result_dict['errorMessage']) }} {%- endif -%} {{- sleep(interval) -}} {%- if loop.last -%} {{- raise_exception('The job did not complete within the specified time limit.') -}} {%- endif -%} {%- endfor -%} {%- endmacro -%} {%- macro check_error(status, response) -%} {%- if not( 200 <= status <= 299) -%} {{ raise_exception(response[0]['message']) }} {%- endif -%} {%- endmacro -%}
Test the check_error macro
Return to the job sfdc_get_object_data.
Select the option Enable Post-hook and include sfdc_lib in the list of Macro Libraries.
Insert the following code into the Post-Hook section and then click Test Template.
{{ check_error(_status,_response) }}
The error message has been incorporated into the exception.
Click Make a Test Call with Posthook and review the response.
Save the job.
Capture errors outside the job
To enhance error handling beyond the job execution, consider parsing the response payload after invoking the job instance. By doing so, you can capture and manage errors within a task, allowing the processing of other records to continue if the instance is either a function or a table function.
Open the job sfdc_get_object_data.
Update the following properties:
Instance Type: Procedure Enable Post-hook: unchecked Raise exception for status: unchecked
Click Save, then Build to create a new job instance.
Open a Snowflake worksheet and run the following code. This script halts the process if an error is returned from Salesforce.
USE ROLE LEARN_DEV_DEVELOPER; USE SCHEMA SNOWEAVER.LEARN_DEV; BEGIN LET results VARIANT; CALL SFDC_GET_OBJECT_DATA('Account', 'abc') INTO :results; IF (results[0]:errorCode IS NOT NULL) THEN LET error_message VARCHAR := 'Error in SFDC_GET_OBJECT_DATA: ' || results[0]:message; -- You can send a notification email by using SYSTEM$SEND_EMAIL RETURN error_message; END IF; -- Execute additional tasks here... RETURN 'Job completed successfully'; END;
Return to the job sfdc_get_object_data.
Update the following properties:
Instance Type: Table Function
Click Save, then Build to rebuild the job instance.
Run the following code in the Snowflake worksheet. The table function will process all requests and return the response details within the result set.
SELECT * FROM ( SELECT 'abc' AS ID UNION ALL SELECT '' ) t CROSS JOIN TABLE(SFDC_GET_OBJECT_DATA('Account', t.ID)) r;