DEV Community

AWS Community Builders profile image Aki
Aki for AWS Community Builders

Posted on

Securely Implementing Snowflake AWS Lambda Integration with Key Pair Authentication + Secrets Manager

#aws #snowflake #dataengineering

Original Japanese article: Snowflake × AWS Lambda連携をKey Pair認証 + Secrets Managerで安全に実装する

Introduction

I'm Aki, an AWS Community Builder (@jitepengin).

When building data pipelines and business systems on AWS, there are cases where you need to access Snowflake directly as part of your application processing.

For example, the following use cases are common:

  • Load data received via an API into Snowflake
  • Execute SQL on the Snowflake side after ETL / ELT processing is completed
  • Write results from external system integrations into Snowflake
  • Trigger Snowflake stored procedures as part of task execution

AWS Lambda works extremely well with serverless, event-driven processing, and by combining it with Snowflake, you can build a highly flexible data integration platform.

On the other hand, when connecting from Lambda to Snowflake, it is critically important to determine how authentication credentials should be managed securely.

As of 2026, for Snowflake system integrations, Key Pair authentication or OAuth has become the standard, and embedding passwords directly is no longer recommended.

In this article, I would like to explain an implementation pattern that uses AWS Secrets Manager to securely manage the private key and connects from Lambda to Snowflake using Key Pair authentication.

Reference: Deprecation plan for single-factor password sign-in
https://docs.snowflake.com/ja/user-guide/security-mfa-rollout

First of all, why access Snowflake from Lambda?

Before getting into the implementation, let’s first consider why it is necessary to access Snowflake from Lambda in the first place.

I’d like to explain this using three common use case patterns.

Case 1: Event-driven data ingestion

I think this is probably the most common pattern.


S3 / API Gateway / EventBridge
            ↓
         Lambda
            ↓
       Snowflake
Enter fullscreen mode Exit fullscreen mode

For example:

  • order data received through an API
  • CSV / JSON files uploaded to S3
  • SaaS integration events

These can be received by Lambda and then directly loaded into Snowflake.

There are architectures that use Snowpipe, but when:

  • preprocessing is required
  • format conversion is required
  • multiple system integrations are involved

Lambda is often easier to work with.

Case 2: Snowflake integration after AWS ETL completion

This pattern executes SQL on Snowflake via Lambda after ETL processing has been completed on the AWS side.

This example assumes Apache Iceberg is being used as the data platform.


Glue / Lambda
      ↓
S3 Iceberg
      ↓
Lambda
      ↓
Execute SQL on Snowflake
Enter fullscreen mode Exit fullscreen mode

For example:

  • ETL completed on AWS
  • REFRESH on Snowflake
  • execute MERGE SQL
  • data quality checks
  • update BI marts

This is a common pattern for downstream processing.

Case 3: Operations automation

This is another pattern that can often be seen in real-world implementations.

For example:

  • starting / stopping warehouses
  • executing tasks
  • automatic SQL execution

These operations can be automated from Lambda.

Architecture

The architecture used in this article is as follows.


Event Source
   ↓
AWS Lambda
   ↓
Secrets Manager
   ↓
Retrieve Private Key
   ↓
Snowflake
Enter fullscreen mode Exit fullscreen mode

Lambda retrieves the private key from Secrets Manager and connects to Snowflake using Key Pair authentication.

Security best practices

1. Do not store the private key in environment variables

This is important not only for Snowflake, but for any sensitive credentials.

A common anti-pattern is storing it like this:

PRIVATE_KEY = os.environ["PRIVATE_KEY"]
Enter fullscreen mode Exit fullscreen mode

This should absolutely be avoided.

In addition to security concerns, there are operational issues such as:

  • possibility of accidental log output
  • operational overhead during configuration changes
  • difficulty in rotation
  • weak access control

2. Centralized management with Secrets Manager

Sensitive information like this should ideally be centrally managed using Secrets Manager.

By using Secrets Manager, you gain the following benefits:

  • IAM-based access control
  • KMS encryption
  • audit logging with CloudTrail
  • credential rotation support

3. Principle of least privilege for IAM

Only the minimum required permissions should be granted to Lambda.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "secretsmanager:GetSecretValue",
      "Resource": "arn:aws:secretsmanager:ap-northeast-1:123456789012:secret:snowflake-keypair-*"
    }
  ]
}
Enter fullscreen mode Exit fullscreen mode

4. Use a dedicated Snowflake service user

Do not use personal users.

Instead, create a dedicated service user.

The service user should be created with:

  • TYPE = 'LEGACY_SERVICE'
  • or TYPE = 'SERVICE'

Registering authentication information

1. Create the private key

First, create the private key.

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
Enter fullscreen mode Exit fullscreen mode

2. Configure Snowflake

Register the public key to the Snowflake user.

ALTER USER lambda_service_user
SET RSA_PUBLIC_KEY='MIIBIjANBgkq...';
Enter fullscreen mode Exit fullscreen mode

Use the contents of the rsa_key.pub file as the public key.

3. Register in Secrets Manager

Store the private key as follows.

{
  "account": "XXXXXXXXXX",
  "user": "lambda_service_user",
  "privateKey": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----",
  "passphrase": "",
  "role": "LAMBDA_ROLE",
  "warehouse": "COMPUTE_WH",
  "database": "ICEBERGDB",
  "schema": "PUBLIC"
}
Enter fullscreen mode Exit fullscreen mode
  • account: Snowflake account identifier
  • user: Snowflake connection user
  • privateKey: private key created in step 1 (rsa_key.p8)

The <orgname>-<account_name> format is recommended for the account identifier.

Now the preparation is complete.

Lambda sample code

For verification purposes, this example simply executes:

SELECT CURRENT_VERSION()

The key point is using snowflake.connector, which makes connecting to Snowflake very straightforward.

Add snowflake.connector using a Lambda Layer, or build the Lambda function as a container image.

import json
import boto3
import snowflake.connector
from cryptography.hazmat.primitives import serialization
from cryptography.hazmat.backends import default_backend
import time
import random

SECRET_ID = "snowflake-keypair"

def get_secret():
    client = boto3.client("secretsmanager")
    response = client.get_secret_value(SecretId=SECRET_ID)
    return json.loads(response["SecretString"])

def execute_query_with_retry(conn, query, max_retries=3):
    retry_count = 0
    while retry_count < max_retries:
        try:
            cur = conn.cursor()
            cur.execute(query)
            result = cur.fetchone()
            cur.close()
            return result
        except snowflake.connector.Error as e:
            print(f"Query failed (attempt {retry_count+1}/{max_retries}): {e}")
            retry_count += 1
            sleep_time = min(2 ** retry_count, 30) + random.random()
            time.sleep(sleep_time)
    raise Exception("Query execution exceeded max retries.")

def lambda_handler(event, context):
    secret = get_secret()

    private_key_obj = serialization.load_pem_private_key(
        secret["privateKey"].encode("utf-8"),
        password=None,
        backend=default_backend()
    )

    conn = None
    try:
        conn = snowflake.connector.connect(
            user=secret["user"],
            account=secret["account"],
            private_key=private_key_obj,
            role=secret.get("role"),
            warehouse=secret.get("warehouse"),
            database=secret.get("database"),
            schema=secret.get("schema")
        )

        result = execute_query_with_retry(conn, "SELECT CURRENT_VERSION()")
        return {
            "statusCode": 200,
            "body": str(result)
        }

    except Exception as e:
        print(f"Lambda execution error: {e}")
        return {
            "statusCode": 500,
            "body": str(e)
        }
    finally:
        if conn:
            conn.close()
Enter fullscreen mode Exit fullscreen mode

Depending on default service user settings can lead to unintended behavior during configuration changes.

Therefore, explicitly specifying:

  • role
  • warehouse
  • database
  • schema

is safer for production environments.

Execution result 

{
  "statusCode": 200,
  "body": "('10.11.2',)"
}
Enter fullscreen mode Exit fullscreen mode

Executed on Snowflake side:

SELECT CURRENT_VERSION()
>10.11.2
Enter fullscreen mode Exit fullscreen mode

The results match, so the connection was successful.

Additional considerations

Strengthening network security

Using AWS PrivateLink enables communication within a private network and eliminates internet-based communication risks.

This can be implemented with the following steps:

  • enable private connectivity in Snowflake
  • create a VPC endpoint (com.amazonaws.vpce.snowflake)
  • pair it with Snowflake’s service principal
  • place Lambda in the same VPC
  • restrict access using security groups

Error handling

As implemented in the sample code, it is a good idea to add retry processing to tolerate temporary network failures and transient Snowflake issues.

Using exponential backoff improves resilience compared to fixed delays.

Monitoring

The following monitoring perspectives are recommended:

  • CloudWatch Logs: capture connection errors and query failures
  • CloudTrail: verify access logs to Secrets Manager
  • Snowflake audit logs: monitor user connection activity

Conclusion

In this article, I introduced the full flow from key management to connection setup for accessing Snowflake from AWS Lambda using Key Pair authentication.

There are more use cases for Lambda-to-Snowflake access than many people expect, especially for:

  • event-driven processing
  • downstream ETL workflows

And of course, secure credential management is critically important—not only for Snowflake, but for any system integration.

At this point in time, Key Pair authentication + Secrets Manager is likely the standard implementation pattern for connecting to Snowflake.

I hope this article helps as a reference when building application integrations between AWS and Snowflake.

Top comments (0)