Have you ever had a critical data pipeline fail silently within the Snowflake Data Cloud only to be notified later by an end user that the data was incorrect?
Wouldn’t it be nice to have some sort of active monitoring in place to notify the appropriate people when data quality issues occur within Snowflake?
Rest assured, this blog has you covered!
While Snowflake handles SQL failures or errors, it’s typically up to the data engineering team to design processes to capture those errors and notify the operations or support teams that handle them.
The successful completion of a job can be notified in the form of an email, but failures or any data quality issues captured should be properly handled by raising an L1/L2 support ticket (if need be) through one of the incident response applications like PagerDuty, Service Now, etc.
If your organization uses ETL/ELT tools like Informatica or Matillion, the tool will have built-in notification capability. But if your organization relies on ELT solely using Snowflake’s SQL and Stored Proc, there is no feature available to notify natively within Snowflake.
However, there is a way to use external functions to implement notifications or alerts.
In this blog, we’ll look at notifications/alerts through email/PagerDuty using Snowflake’s external function in an AWS environment.
Getting Started
At the point of writing this blog, Snowflake does not currently have a native email alert capability, but this may change in the future.
For this blog, we will be using the following features to implement notifications/alerts:
- Snowflake external functions
- AWS API Gateway
- AWS Lambda
- AWS SNS
- PagerDuty
We will use the Snowflake Notification integration to trigger AWS Lambda through API Gateway from External functions. AWS Lambda will send email alerts through SNS and create an incident ticket in PagerDuty through an API.
Keep in mind that Snowflake has an external function call code that is executed outside of Snowflake. In this case, the code will be in AWS Lambda which is a remote service that is executed through AWS API Gateway, which is the proxy service.
We will also be creating all the services using the AWS Management console.
Step 1: Create SNS to Send Email Alerts
As a first step, you’ll want to create an SNS in the AWS environment. In the AWS Manage console, go to SNS and create an SNS topic – snowflake-alert-sns. Keep the rest of the options as is (unless required by your organization).
Once the topic is established, create an email subscription as shown below. The email address can be an individual email or a distribution list within your organization.
Step 2: Create Remote Service – AWS Lambda
The next step is to create a Lambda service in AWS, which will process the data sent from Snowflake external functions. The Lambda function, which processes the records from Snowflake, has a specific requirement. The Lambda function receives an event parameter that will have many sub-fields including a body, which is a JSON-compatible string that contains a dictionary.
This dictionary includes a key named data and values in the form of an array. The records sent from the Snowflake external function will be in this array.
Generally, Snowflake external functions are used in a select statement which will send one or multiple records in a batch. So the data received will always be in the form of an array that may contain data for one or multiple rows. The Lambda function which processes the data should assume this and process all the records in the array.
In our scenario, we will be sending one row most of the time as we will be using this for alert/notification purposes.
Next, let’s create a Lambda function as shown below with Python as runtime.
In this example, we are creating Lambda with a default execution role. This will create an IAM role automatically. If an IAM role already exists for Lambda, then the same can be used while creating the Lambda function.
The newly created role (or already existing role) should be modified to attach a policy to send messages to the SNS created in Step 1. Without this, Lambda will fail with an access denied error.
Within the IAM role, select the newly created IAM role. Click on Add Permission and then click Create Inline Policy. Fill in the section as shown below and select the specific ARN for SSN for which the notification is to be sent.
Snowflake provides a sample Lambda function to process records in a synchronous fashion. Let’s use the Lambda function provided as a base and enhance it to send an email alert through SNS. It’s important to note that you’ll want to update the ARN of SNS in the code. The modified Lambda Python code will look like this:
import json
import boto3
def lambda_handler(event, context):
# 200 is the HTTP status code for "ok".
status_code = 200
# The return value will contain an array of arrays (one inner array per input row).
array_of_rows_to_return = [ ]
try:
# From the input parameter named "event", get the body, which contains
# the input rows.
event_body = event["body"]
print(event_body)
#print(json.dumps(event_body))
# Convert the input from a JSON string into a JSON object.
payload = json.loads(event_body)
# This is basically an array of arrays. The inner array contains the
# row number, and a value for each parameter passed to the function.
rows = payload["data"]
print(rows)
# For each input row in the JSON object...
for row in rows:
# Read the input row number (the output row number will be the same).
row_number = row[0]
#read the notification message subject
notification = row[1]
#Create SNS client and send notification
client = boto3.client('sns')
response = client.publish (
#update this with your newly created SNS topic ARN
TargetArn= "arn:aws:sns:us-east-1:1234:snowflake-alert-sns" ,
Message = json.dumps({'default':notification}),
MessageStructure = 'json'
)
#Update output value with message as below. This will serve as an acknowledgement that email has been sent.
output_value = ["Email Notification sent"]
# Put the returned row number and the returned value into an array.
row_to_return = [row_number, output_value]
# ... and add that array to the main array.
array_of_rows_to_return.append(row_to_return)
json_compatible_string_to_return = json.dumps({"data" : array_of_rows_to_return})
except Exception as err:
# 400 implies some type of error.
status_code = 400
# Tell the caller what this function could not handle.
json_compatible_string_to_return = event_body
print(err)
# Return the return value and HTTP status code.
return {
'statusCode': status_code,
'body': json_compatible_string_to_return
}
Python Requests
The Lambda code requires a Python “requests” library. Follow the steps below to include the requests library.
- In your laptop, create a folder named snowflake-alert-lamda
- Create a python file lambda_function.py.and copy the code from above.
- Install requests by running the following command: pip install requests-t./
- Zip all the contents including the libraries using the command below:
- zip-r snowflake-alert-lamda.zip.
In the Lambda page, upload the entire zip using upload from option and select .zip file. This will upload the code along with the required libraries.
Test the Lambda code by configuring a test event as shown below:
{
"body":
"{ \"data\": [ [ 0, \"success\" ] ] }"
}
If roles and policies are configured correctly, then the triggering Lambda code should send an email with the body as “success”.
Step 3: Setup Proxy Service – AWS API Gateway
The next step in the process is to set up a proxy service in AWS API Gateway. This step requires an IAM role to be created. In this example, we will use the same IAM role that was created automatically when the Lambda service was created. To start, record the ARN of the role created in Step 2 and then follow these steps:
- In AWS Management Console, go to API Gateway service page.
- Click on Build under the Rest API section and fill in the information as shown below. Next, provide a name for the API ( for example, snow-alert ) and click on the Create API button.
- Once the API is created, we’ll want to create a resource. Click on Create Resource under the Actions menu. Give a name for the resource (for example, snowflake) and click Create Resource button
- Once the resource is created, it will display an empty screen with the newly created resource. Click on Actions and click on Create Method and then select POST method and click on the ☑️ button next to Post.
- Once the method is created, select Integration type as Lambda integration and select the newly created Lambda function in step 2. Be sure to click on Use Lambda Proxy Integration (shown below) and then click save.
- Once the POST method is created, the next step is deploying the API. Under the Actions menu, click on Deploy API. It will open up a popup with an option to select or create a new stage. Give a name to the stage (for example, snowflake-alert-stage) and click on the Deploy button. In the next page, it will show the newly created stage. On the left side under the newly created stage, click on the POST link and write down the invoke URL and click on Save Changes.
Invoke URL: https://gugz1kj7he.execute-api.us-east-1.amazonaws.com/snowflake-alert-stage/snowflake
- Now that the API is created, we can test if the API is invoking the Lambda function created in Step 2. Click on the API name (snow-alert) under the navigation pane and then click on the POST method. The other way is to click on Resources on the left pane.
- Before invoking the method, we need to configure authorization.
- Click on the Method Request button as shown below. Write down the ARN under Method Request as well.
- This will open up the Method Request page, Update the Authorization to AWS IAM and go back to the Method Execution page.
- Go back to the Method Execution page and click on the Test button. Enter the request body (as shown below) and click on the Test button. This should invoke the Lambda function and send an email notification. Note the difference in test data between Lambda and the API. The API call will wrap this around the body JSON tag and send it to Lambda for execution.
{ "data": [ [ 0, "success" ] ] }
Step 4: Create IAM role for Snowflake Integration
Now that the API is created and working, we will need to configure Snowflake to talk to the newly created API. This will require a new IAM role to be configured. Let’s create a new IAM for this purpose.
The newly created IAM role should have a trust relationship setup. To do this, create the role with the configuration below.
For account id, enter your account for now. We will change it in step 5.
Leave the rest of the steps as is and name the IAM role ( for example, snowflake-api-role) and then click on create role. Copy the ARN of the newly created role, we will need it in Step 5.
Step 5: Create Snowflake API Integration
For Snowflake to talk to the newly created API, we need to create an API integration. This will require an account admin role or any role which has privileges to create integration objects.
Execute the following query in the Snowflake worksheet, ensuring to correct the following:
- api_aws_role_arn : Role ARN noted down from step 4.
- api_allowed_prefixes: URL noted down in step 3.
create or replace api integration snowflake_api_integration
api_provider = aws_api_gateway
api_aws_role_arn = 'arn:aws:iam::12345675789:role/snowflake-api-role'
api_allowed_prefixes = ('https://pbjp7eynn5.execute-api.us-east-1.amazonaws.com/snowflake-alert-stage/snowflake')
enabled = true;
Create notification integration object and then run the describe command as shown below.
describe integration snowflake_api_integration;
From the results, take note of the property value of the following properties. Sample values are given below:
- API_AWS_IAM_USER_ARN : arn:aws:iam::1234567898012:user/externalstages/yopybqgi
- API_AWS_EXTERNAL_ID: XY123_SFCRole=3_8Hcmbi9halFOkt+MdilPi7rdgOv=
Step 6: Update IAM Role and API Resource Policy
Once the integration object is created, the next step is to update the IAM role with a trust policy. Go to the IAM page in the AWS Management Console and click on the newly created role – snowflake-api-role. Click on the Trust Relationship tab and then click on Edit Trust Policy button.
Update the policy using the following template with information from Step 5.
- Update “ Principal” with property value from API_AWS_IAM_USER_ARN
- Update “ sts:ExternalId” with property value from API_AWS_EXTERNAL_ID
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"AWS": "arn:aws:iam::1234567898012:user/externalstages/yopybqgi"
},
"Action": "sts:AssumeRole",
"Condition": {"StringEquals": { "sts:ExternalId": "XY123_SFCRole=3_8Hcmbi9halFOkt+MdilPi7rdgOv=" }}
}
]
}
Now go back to the API page (snow-alert) and click on the Resource policy link on the left. Update the resource policy with information in the template shown below.
- 12-digit-number: Your own AWS account number
- External_function_role: Name of the role created in step 4 (snowflake-api-role)
- Method_request_arn: ARN of the request noted down in step 3.
{
"Version": "2012-10-17",
"Statement":
[
{
"Effect": "Allow",
"Principal":
{
"AWS": "arn:aws:sts::<12-digit-number>:assumed-role/<external_function_role>/snowflake"
},
"Action": "execute-api:Invoke",
"Resource": "<method_request_ARN>"
}
]
}
The API screen will look like this:
Click on the save button.
Step 6: Create Snowflake External Function
Now that we have configured the API and Integration object, we can now create a Snowflake external function. The Snowflake function will use the API integration object created in step 5 to authenticate the API and execute Lambda in the URL mentioned.
create or replace external function snowflake_api_function(status varchar, subject varchar , message varchar)
returns variant
api_integration = snowflake_api_integration
as 'https://gugro1f7he.execute-api.us-east-1.amazonaws.com/snowflake-alert-stage/snowflake';
The function created takes three parameters:
- Status – Success/Failure
- Subject – Subject of the email to send
Message – Complete message to be sent for both success/failure scenarios including errors/warnings if any.
Step 7: Execute Snowflake External Function
Let’s execute the external function. In this case, it’s a simple select query.
select snowflake_api_function('SUCCESS','ETL Job Status', 'ETL Job completed successfully');
Executing this will generate an email as shown in the screenshot below.
Closing
In this blog, we looked at a very simple scenario of sending email notifications from Snowflake using Snowflake’s external functions. This will work great when an email notification is required when an ELT process runs within Snowflake and the support team needs updates on the job status.
But what if we are required to raise a support ticket when a job fails?
In the next blog, let’s look at how to raise a support ticket in PagerDuty when a job fails.
Looking to extract more value from your Snowflake investment? Learn how the experts at phData can help by exploring our Snowflake consulting services.
