Powering Dynamic Emails through Snowflake in Realtime

5 min readAug 8, 2023

Hello to all tech aficionados out there!

Today, we will see how to automate real-time, dynamic emails directly from Snowflake.

Table of Contents

Introduction: snowSend — A New Breakthrough in Dynamic Emails
The Tech Behind snowSend: An Overview of the Technology Stack
Inside snowSend’s Engine Room: How it works?
Rendering Professional looking Emails: React-Email and Resend at Play
The Pulumi-Snowflake Synergy: Simplifying Infrastructure Management
Customizing Your Emails: The Flexibility and Adaptability of snowSend
Get Started & Setup: Setup snowSend project
Conclusion

For all the action-lovers out there, who, like me, prefer diving into code rather than indulging in lengthy posts, here’s your gateway— snowSend

Introduction: snowSend — Realtime Dynamic Emails

First off, let’s quickly encapsulate what snowSend is.

snowSend is an open-source project that has ingeniously repurposed Snowflake’s services. It opens up a whole new realm of possibilities for managing email communications dynamically and in real-time, right from your Snowflake environment. snowSend leverages the agility of React-email and Resend to supercharge email automation, making it a standout tool in the realm of data-driven communications.

The Tech Behind snowSend ⚙️

Snowflake— API Integration, External Functions
AWS — API Gateway, Lambda
Infrastructure as Code (IAC) — Pulumi
Email — Resend Labs
Styled — React-email components

Inside snowSend’s Engine Room ⨠

How it works?

Email Designing: At the heart of SnowSend are the react-email components. Styled with CSS or Tailwind, we can pass dynamic properties to each of these components, creating flexible email templates that can be tailored to specific needs.
Lambda Function and AWS API Gateway: Next, we pack these components into a Lambda function provided by AWS, which allows serverless computing. To make this Lambda function callable, we then deploy an AWS API Gateway.
Snowflake’s API Integration: With the API Gateway in place, we utilize Snowflake’s API integration services. Here, the AWS API Gateway serves as our provider.
Creating an External Function in Snowflake: We establish a connection with the API provider and create an external function within Snowflake. This function is bound to the API provider and the Gateway endpoint.
Using the External Function: Finally, we use this external function from within Snowflake. This is achieved by providing dynamic data, such as the specific email template and data to be included in the email, directly as an argument.

Rendering Emails in Real-Time: React-Email and Resend at Play ♠️

A key feature of snowSend is its ability to create and deliver emails in real time. It takes advantage of the dynamism offered by React-email to generate on-demand HTML content tailored to the specific type of email and the data supplied to it.

With Snowflake’s real-time data streaming capabilities, you can use snowSend to deliver tailored messages to your users as events happen, without any delay.

Once these emails are compiled and ready to be dispatched, they’re handed over to Resend Labs API. Known for its efficacy and dependability, Resend Labs ensures that your emails are delivered without fail 👀

The Pulumi-Snowflake Synergy: Simplifying Infrastructure Management ₼

Managing infrastructure, especially when you have big names like AWS and Snowflake involved, might seem like a daunting task. However, snowSend employs Pulumi for infrastructure management. Pulumi streamlines the handling of resources, and when coupled with Snowflake, the result is a robust, efficient, and simplified infrastructure and the best thing I like about Pulumi is you can code in your Fav language (Typescript/python/etc..). Now that’s a hard-to-beat combo!

Customizing Your Emails: The Flexibility and Adaptability of snowSend 🍥

snowSend doesn’t believe in one-size-fits-all. Each email sent is dynamically rendered based on the email type and the corresponding data. This feature empowers users to craft personalized and unique emails. With a variety of React email components at your disposal, each email sent out can reflect the essence of your application and the context of interaction, enhancing the overall user experience. Checkout some of the templates made by react-email

Get Started and setup ▶

Getting ready to dive in? The setup for snowSend is quite straightforward. The following guide will assist you in setting up the project. You’ll have your dynamic email automation system up and running in no time!

1. Clone the repository

Start by cloning the snowSend repository using the following command:

git clone https://github.com/kaarthik108/snowSend.git

2. Set up a Resend account

Next, set up an account on Resend and retrieve your API key. Use the following command to configure it:

cd pulumi
npm i
pulumi config set RESEND_API_KEY your_resend_api_key --secret

3. Pulumi setup

Start by navigating to the pulumi directory, and edit Pulumi.prod.yaml to add the configuration values. Use the following example commands to set up all other configurations:

pulumi config set snowflake:password your_password --secret

Install npm packages and check the email components/customize:

 cd pulumi/app
 npm install
 npm run dev

Provision all resources needed in AWS and Snowflake:

cd pulumi/
pulumi up

Configure API_AWS_EXTERNAL_ID and API_AWS_IAM_USER_ARN values:

pulumi config set API_AWS_EXTERNAL_ID $(pulumi stack output API_AWS_EXTERNAL_ID) --secret
pulumi config set API_AWS_IAM_USER_ARN $(pulumi stack output API_AWS_IAM_USER_ARN) --secret
pulumi up

4. Deploy translator function (manual step for now)

This step is required due to a current issue with Snowflake when deploying the request translator UDF. Pulumi deploys functions with quotes (e.g., “snowsend-1234”), but when you set that as REQUEST_TRANSLATOR in the external function, they don’t register as quotes. This results in the external function not being able to find the UDF. For the time being, we will manually execute this step:

cd ..
pip install -r requirements.txt
python test.py

Feel free to visit the snowSend GitHub page for a more comprehensive setup guide. Remember, not all who wander are lost. Some are just busy setting up their dynamic email automation system! 😉

Conclusion: More than Just Sending Emails

In conclusion, snowSend is not merely about sending emails. It extends the frontiers of personalized, data-driven communication. By integrating with existing data warehouses like Snowflake, snowSend presents a new model of engaging users in more meaningful and real-time ways. The potential use-cases are vast — from personalized marketing campaigns to timely user notifications and beyond.

Full source code — https://github.com/kaarthik108/snowSend

We hope this guide has piqued your interest in exploring snowSend further. Happy exploring, and as always, keep innovating!