Creating Snowflake External Functions using SST (Serverless Stack)

In this article, I want to share a step-by-step guide on how to create external functions in Snowflake using SST (Serverless Stack). Unlike Snowflake's official documentation, which only covers how to do it from the console or using CloudFormation, this approach focuses on using SST and CDK. This article assumes you have basic knowledge of SST.

Introduction

Snowflake is a powerful cloud data platform, and external functions allow extending its capabilities by invoking external services via REST APIs. Using SST to implement these functions provides an efficient and scalable way to manage these integrations.

Step 1: Define the Infrastructure with SST

Next, we will define our infrastructure using SST. Here is the complete code to create an API Gateway and a Lambda function that will handle requests from Snowflake.

Difference between Api and ApiGatewayV1Api

In SST, there are two main constructs to create APIs: Api and ApiGatewayV1Api. The choice between these two depends on the type of authentication and specific features you need for your API.

• Api: A high-level construct that facilitates the quick creation of APIs using AWS API Gateway V2. However, it has limitations regarding the supported authentication options, especially when more advanced authentication like IAM is needed to control access.
• ApiGatewayV1Api: This construct offers more flexibility and control when using AWS API Gateway V1, allowing advanced configurations, including the use of resource policies and IAM-based authentication. For our case, where Snowflake needs to invoke the API with IAM authentication, ApiGatewayV1Api is the suitable choice.

Here is how to configure this infrastructure in the stacks/SnowflakeStack.ts file:

import * as iam from "aws-cdk-lib/aws-iam";
import { ApiGatewayV1Api, StackContext } from "sst/constructs";

export function SnowflakeStack({ stack }: StackContext) {
  const snowflakeRole = new iam.Role(stack, "SnowflakeRole", {
    assumedBy: new iam.AccountPrincipal(stack.account), // Reemplaza <12-digit-number> por tu número de cuenta
    roleName: "snowflake_role",
  });

  // Crear el PolicyDocument
  const apiResourcePolicy = new iam.PolicyDocument({
    statements: [
      new iam.PolicyStatement({
        effect: iam.Effect.ALLOW,
        actions: ["execute-api:Invoke"],
        principals: [
          new iam.ArnPrincipal(
            `arn:aws:sts::${stack.account}:assumed-role/${snowflakeRole.roleName}/snowflake`
          ),
        ],
        resources: ["execute-api:/*/POST/snowflake-proxy"],
      }),
    ],
  });

  // Crear el API Gateway REST

  const api = new ApiGatewayV1Api(stack, "snowflake_aws_api", {
    routes: {
      "POST /snowflake-proxy": {
        authorizer: "iam",
        function: {
          handler: "packages/functions/src/generateErrors.hello",
        },
        // cdk: {
        //   integration: { proxy: true },
        // },
      },
    },
    cdk: {
      restApi: {
        // description: "hola mundo
        policy: apiResourcePolicy,
        endpointTypes: ["REGIONAL"],
      },
    },
  });
}

        

Step 2: Implement the Lambda Function

The Lambda function will handle requests from Snowflake and return appropriate responses. Here is an example implementation of the Lambda function in the packages/functions/src/myFunction.ts file:

import { APIGatewayProxyEvent, APIGatewayProxyResult, Context } from 'aws-lambda';

export const main = async (event: APIGatewayProxyEvent, context: Context): Promise<APIGatewayProxyResult> => {
  let statusCode = 200;
  let arrayOfRowsToReturn: any[] = [];

  try {
    const eventBody = event.body;

    if (eventBody) {
      const payload = JSON.parse(eventBody);
      const rows = payload.data;

      for (const row of rows) {
        const rowNumber = row[0];
        const inputValue1 = row[1];
        const inputValue2 = row[2];
        const outputValue = ["Echoing inputs:", inputValue1, inputValue2];
        const rowToReturn = [rowNumber, outputValue];
        arrayOfRowsToReturn.push(rowToReturn);
      }
    }

    const jsonCompatibleStringToReturn = JSON.stringify({ data: arrayOfRowsToReturn });

    return {
      statusCode: statusCode,
      body: jsonCompatibleStringToReturn
    };

  } catch (err) {
    statusCode = 400;
    const jsonCompatibleStringToReturn = event.body || '';

    return {
      statusCode: statusCode,
      body: jsonCompatibleStringToReturn
    };
  }
};
        

Step 3: Deploy the Project

To make our infrastructure (including the IAM role) available, we need to deploy the project using SST. Ensure you have SST installed and configured correctly in your environment.

Run the following command in your terminal from the root of the project:

sst deploy        

This will create and deploy all the resources defined in our stacks, including the snowflake_role and the API Gateway.

Step 4: Create an API Integration in Snowflake

From here, we can continue watching the instructional video from Snowflake at minute 12:50, where the integration is created to obtain the values API_AWS_IAM_USER_ARN and API_AWS_EXTERNAL_ID, which will be used in the trust policy of the snowflake_role:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": API_AWS_IAM_USER_ARN
            },
            "Action": "sts:AssumeRole",
            "Conditions": {"StringsEquals": {"sts:ExternalId": API_AWS_EXTERNAL_ID}}
        }
    ]
}        

Conclusion

By following these steps, we have configured an external function in Snowflake using SST and CDK, allowing for a more robust and managed integration with external services. I hope this article has been useful and inspires you to explore more about the capabilities of Snowflake and Serverless Stack.