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:

Raise exception for status

  1. Open the LEARN_DEV project in Snoweaver with the LEARN_DEV_DEVELOPER role.

  2. Open the job sfdc_get_object_data and modify the id variable’s test value to abc, which is an invalid account ID.

    ../_images/114.png

  3. In the Response Handler, ensure the option Raise exception for status is selected.

  4. 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.

    ../_images/25.png

  5. Click Build to deploy a new instance.

  6. 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;

  7. You’ll observe that the same error has been recorded. If you don’t see the event, please try querying again in a minute.

  8. 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');
    ../_images/45.png

  9. 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.

  10. 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

  1. 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.

  2. Return to the job sfdc_get_object_data.

  3. Disable Raise exception for status and make another test call.

  4. You will now see the error message included in the response, which explains the reason for the 404 status code.

    ../_images/54.png

  5. 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.

  1. Open the LEARN_DEV project in Snoweaver with the LEARN_DEV_DEVELOPER role.

  2. Open the Home page.

  3. 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

  1. Return to the job sfdc_get_object_data.

  2. Select the option Enable Post-hook and include sfdc_lib in the list of Macro Libraries.

    ../_images/84.png

  3. Insert the following code into the Post-Hook section and then click Test Template.

    {{ check_error(_status,_response) }}
    ../_images/93.png

  4. The error message has been incorporated into the exception.

  5. Click Make a Test Call with Posthook and review the response.

    ../_images/104.png

  6. Save the job.

Capture errors outside the job

  1. 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.

  2. Open the job sfdc_get_object_data.

  3. Update the following properties:

    Instance Type:                Procedure
Enable Post-hook:             unchecked
Raise exception for status:   unchecked

  4. Click Save, then Build to create a new job instance.

  5. 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;

  6. Return to the job sfdc_get_object_data.

  7. Update the following properties:

    Instance Type:                Table Function

  8. Click Save, then Build to rebuild the job instance.

  9. 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;
    ../_images/124.png