About this lab
Kestrel Cold Chain, a refrigerated freight forwarder in Rotterdam, has to report the carbon footprint of every shipment. The emissions maths is owned by the sustainability team and runs as an AWS Lambda service that the booking website also uses, and the auditors want the reporting data in an open format in Kestrel's own S3 bucket.
In this lab you build the CO2e calculator in Lambda, put it behind an IAM-authorized API Gateway endpoint, and connect Snowflake to it with an API integration and a least-privilege IAM role locked to your Snowflake account with an external ID. You then score 483 shipments with an external function in one pass, see how Snowflake batches the calls, and quarantine the records the service rejects without failing the query.
Next you create an S3 bucket, an external volume, and a Snowflake-managed Iceberg table, and write the scored shipments to it as Parquet plus Iceberg metadata you can browse in S3. You correct, delete, and MERGE late shipments (calling the external function inside the DML), watch new snapshots appear, query the table with Time Travel, and publish a monthly lane report as a dynamic Iceberg table. Every AWS step is given for both the console and the CLI, with every IAM policy included.
Lab details
- Level: Intermediate
- Cloud: AWS + Snowflake
- Domain: Data Engineering
- Duration: 120 minutes
What you will learn
- Build a Lambda function behind an IAM-authorized API Gateway endpoint that follows the Snowflake external function request and response format
- Connect Snowflake to AWS with an API integration and a least-privilege IAM role locked by an external ID
- Score a whole table with an external function in one pass, control batch size, and quarantine rejected rows
- Create an external volume and a Snowflake-managed Iceberg table in your own S3 bucket and inspect its data and metadata files
- Run UPDATE, DELETE, and MERGE on an Iceberg table, calling the external function inside the DML, and query earlier snapshots with Time Travel
- Publish a report as a dynamic Iceberg table and clean up every Snowflake and AWS resource
Concepts covered
- External functions and remote services
- Batching and partial-failure handling
- Cross-account IAM roles and external IDs
- Least-privilege IAM policies
- Open table formats and Apache Iceberg
- Iceberg snapshots and Time Travel
- Dynamic Iceberg tables
Technologies used
- Snowflake External Functions
- API Integrations
- AWS Lambda
- Amazon API Gateway
- AWS IAM
- Apache Iceberg Tables
- External Volumes and Amazon S3
Prerequisites
- Basic SQL (SELECT, JOIN, GROUP BY, MERGE)
- A Snowflake trial account hosted on AWS, with the ACCOUNTADMIN role
- An AWS account where you can create Lambda functions, API Gateway APIs, IAM roles, and S3 buckets
- Optional: Python 3 to run the Lambda tests locally, and the AWS CLI for the scripted path
Topics
- Snowflake
- External Functions
- Apache Iceberg
- Lakehouse
- AWS Lambda
- API Gateway
- IAM
- S3
- Dynamic Tables
- Sustainability
- SQL
