🔐 Snowpark WIDF Lambda Loader Demo

Keyless ETL: AWS Lambda → Snowflake

Demonstrates Snowflake Workload Identity Federation (WIDF) with AWS Lambda. No passwords, no secrets, no key pairs - just IAM trust.

Why WIDF?

When to Use This

Snowflake offers multiple ways to load data from S3. Choose based on your needs:

Lambda + WIDF shines when you need:

• Data quality gates - Validate/reject files before loading
• Real-time enrichment - Call APIs during ingestion
• Conditional routing - Route to different tables based on content
• Event context - Capture S3 metadata (who uploaded, when, from where)
• Zero credential management - WIDF handles auth

Add Dynamic Tables for Instant Analytics

Lambda + WIDF gets data in. Dynamic Tables turn it into insights - automatically. Define your transformation query once; Snowflake handles refresh scheduling, dependency tracking, and incremental processing.

flowchart LR
    S3[📦 S3] -->|trigger| Lambda[⚡ Lambda + WIDF]
    Lambda -->|push| RAW[RAW_DATA]
    RAW -->|auto refresh| DT[DAILY_SUMMARY<br/>Dynamic Table]

The setup script creates DAILY_SUMMARY that auto-refreshes as data lands:

How It Works

When a JSON file lands in S3, Lambda loads it into Snowflake using WIDF for authentication. The Lambda's IAM role is trusted directly by Snowflake - no credentials to manage or rotate.

sequenceDiagram
    participant S3 as 📦 S3 Bucket
    participant Lambda as ⚡ AWS Lambda
    participant SF as ❄️ Snowflake
    participant Table as 📊 RAW_DATA

    Note over Lambda, SF: No Secrets (WIDF)
    S3->>Lambda: 1. S3 Event (new JSON file)
    Lambda->>Lambda: 2. Get IAM Role Credentials
    Lambda->>SF: 3. Connect with WORKLOAD_IDENTITY
    Note right of Lambda: authenticator: WORKLOAD_IDENTITY
    SF->>SF: 4. Validate IAM Role ARN
    SF->>Lambda: 5. Return Session
    Lambda->>S3: 6. Read JSON Data
    S3-->>Lambda: JSON Records
    Lambda->>Table: 7. write_pandas()
    Table-->>Lambda: Success

📋 Prerequisites

Snowflake

1. Snowflake Account - Sign up for a free trial

2. Snowflake CLI - Install and configure connection

Tools

Python Environment (choose one)

Using venv instead of uv:

python -m venv .venv
source .venv/bin/activate  # On Windows: .venv\Scripts\activate
pip install -r requirements.txt

🚀 Setup

1. Configure Environment

cp env.example .env

Edit .env with your AWS and Snowflake account details.

2. Verify Configuration

task default

3. Check Prerequisites

# Verify AWS CLI connectivity
task aws:check

# Verify SAM CLI installed
task aws:check-sam

🎬 Demo Walkthrough

Quick Demo (One Command Each)

# Part I: Deploy without WIDF → Watch it fail
task demo:fail

# Part II: Configure WIDF → Watch it succeed  
task demo:success

# Cleanup when done
task demo:clean

Part I: Deploy Without WIDF → Watch It Fail ❌

1. Setup Snowflake Database

task snow:setup

Creates database, schema, role, and RAW_DATA table.

2. Deploy AWS Resources

task aws:deploy

Deploys Lambda function, S3 bucket with trigger, and IAM role.

3. Get the Lambda Role ARN

task aws:role-arn

4. Upload Test Data

task aws:test

5. Watch the Logs

task aws:logs

Expected error: Snowflake doesn't recognize the Lambda's IAM role yet.

Connecting to Snowflake using WORKLOAD_IDENTITY
   Account: your_account
   User: LAMBDA_LOADER_BOT
Found credentials in environment variables.
Lambda failed: 250001 (08001): Failed to connect to DB. 
   Unable to find an associated Snowflake user for the verified 
   AWS Caller Identity with ARN 'arn:aws:sts::123456789012:assumed-role/snowpark-widf-lambda-role-dev/...'

Part II: Configure WIDF → Watch It Succeed ✅

1. Create WIDF Service User

task snow:lambda-wid

This creates a SERVICE user that trusts the Lambda's IAM role:

CREATE USER LAMBDA_LOADER_BOT
  TYPE = SERVICE
  WORKLOAD_IDENTITY = (
    TYPE = AWS
    ARN = 'arn:aws:iam::123456789012:role/snowpark-widf-lambda-role-dev'
  );

2. Upload Test Data Again

task aws:test

3. Watch the Logs

task aws:logs

Expected: Successful connection and data load via WIDF.

4. Verify Data

# Raw landing data
task snow:query

# Real-time analytics (Dynamic Table)
task snow:query-summary

🔑 The Key

connection_params = {
    "account": "...",
    "authenticator": "WORKLOAD_IDENTITY",   # ← WIDF keyless auth
    "workload_identity_provider": "AWS",
    # NO password, NO secret key, NO key pair
}

📖 Commands

Demo

Snowflake Commands

AWS

Other

🔧 Troubleshooting

AWS Deployment Errors

Stack stuck in ROLLBACK_COMPLETE:

task aws:clean-stack
task aws:deploy

Snowflake WIDF Errors

Verify WIDF user exists:

snow sql -q "SHOW USERS LIKE 'LAMBDA_LOADER_BOT';"

Check WIDF configuration:

snow sql -q "SHOW USER WORKLOAD IDENTITY AUTHENTICATION METHODS FOR USER LAMBDA_LOADER_BOT;"

🧹 Cleanup

Remove AWS resources only

task aws:clean

Remove Snowflake resources only

task snow:cleanup

Clean everything

task clean:all

📚 References

• Snowflake Workload Identity Federation
• Snowflake Dynamic Tables - Declarative data pipelines with automatic refresh
• Snowpark Python - DataFrame API for Python
• Snowflake Python Connector - Pandas
• AWS SAM CLI
• Snowflake CLI

License

Copyright (c) Kamesh Sampath. All rights reserved. Licensed under the Apache 2.0 license.