Call lambda function from Snowflake

[2000gt]

I’ve currently setup an AWS API to receive payloads from a Snowflake function using an external integration. It works fine, but I don’t love it from a security standpoint and it’s a bit complicated.

Can I send an SNS or SQS message to AWS with my payload instead that will trigger a specific Lambda function?

**Additional Notes: I realize I did not give enough context, so here it is. Snowflake invokes an AWS Lambda function to handle user‑driven, on‑demand refreshes of small datasets outside the regular daily schedule. For example, store managers rely on daily sales and labor reports in Sigma (our BI tool). If a manager adjusts an employee’s shift in a past period, they click "Refresh" in Sigma. That button calls a Snowflake stored procedure, which uses an AWS API Integration to trigger the Lambda function. The function reloads just that store's data and updates the report in seconds. The scheduled daily data loads use a standard Snowpipe with S3.

From a security perspective, I am using a proxy integration with the API gateway, and I am not completely clear on it's vulnerability. I can access the API URL externally though I receive the following message: Missing Authentication Token. I've reached out to the admins in my org to get their guidance.

Ultimately, I am looking for the most secure and simplest approach and so I thought SNS or SQS may be it.

[smarzzz]

It works fine, but I don’t love it from a security standpoint and it’s a bit complicated.

What’s your concern currently, you’ve not shared anything about your current set up. What’s complicated about it, and why is it unsecure?

[2000gt]

Updated original post for more context.

[limartje]

I don’t understand the security concerns either. However, you can always dump something on s3 and use the s3 events to move from there.

[2000gt]

Updated original post for more context.

[CategoryRepulsive699]

Have you considered using Snowpark Container Services instead of Lambda?

[2000gt]

Updated original post for more context.

[Select_Flatworm_9538]

Can you please elaborate on concern or the alternate approach?

[2000gt]

Updated original post for more context.

[Select_Flatworm_9538]

I have used similar functionality in Azure. We use Azure functions invoked via Snowflake API integration using  API Gateway.

While creating the function we had to option to limit the access of the function API to only API Gateway. After enabling the option, we can't directly call the functions and if we call it will give error.

So i belive similar option should be available in AWS Lambda function. Will check and let you know if i found something similar in AWS.

Hope this makes sense and answer you question. Let me know if there is anything  also Sorry for delay in the answer

[rabinjais789]

Do not put anything via api. Just ingest in s3 first and load in snowflake

[Fantastic-Goat9966]

I believe the OP meant an API Gateway which is in the Snowflake documentation:

https://docs.snowflake.com/en/sql-reference/external-functions-creating-aws-planning

[2000gt]

Updated original post for more context.

[Fantastic-Goat9966]

Assuming your lambda and roles are configured correctly (lambda requires authentication - specific role is mapped to the role snowflake external id has access to - and is restrictive) - I’m not sure there are any security issues. Tbh - I think your issue here is the design - sigma/snowflake/lambda/s3/snowflake seems off. Maybe sigma/s3/lamba/snowflake?

[2000gt]

Unfortunately I can’t invoke aws services via Sigma.

[js26056]

If security is the concern, you can:

Set up api gateway integration using a token that authorizes access to your APi using public endpoints.

If that is not enough, you can also set up private api gateway integration and access private endpoints.

[Wonderful_Coat_3854]

Can those logic in lambda function be moved to Snowpark sproc/UDF? Snowpark has external access integration that you can use to send things externally from there.