SendGrid to Snowflake: 2 Easy Methods

Easily move your data from SendGrid To Snowflake to enhance your analytics capabilities. With Hevo’s intuitive pipeline setup, data flows in real-time—check out our 1-minute demo below to see the seamless integration in action!

SendGrid is a Cloud-Based Email Delivery Service that enables its customers to launch and manage Marketing Email Campaigns. To derive meaningful and real-time insights out of the data collated in SendGrid, companies transform the available data and feed this into a data warehouse such as Snowflake. This blog post is aimed at guiding you through the process of data transfer from SendGrid to Snowflake – a widely used Cloud Data Warehouse.

This article deals in detail with the above approaches. Here’s a breakdown of what will be covered by the post.

Introduction to SendGrid

SendGrid is a Cloud-Based Email Delivery Service that helps you send emails to customers effortlessly without having to build email servers. It enables you to send promotional emails, resets of passwords, notifications of shipping, email newsletters, and more. SendGrid automatically performs time-consuming activities such as network scaling, reputation monitoring, ISP system integration.

Introduction to Snowflake

Snowflake is a  SaaS (Software as a Service) Data Warehouse that is easy to use, fast and flexible when compared with traditional databases. It uses a new database engine designed for the cloud. Aside from having many similarities to enterprise data warehouses, it has many unique functionalities and capabilities. 

Snowflake runs on Public Cloud Infrastructure. Its architecture is a hybrid of traditional shared disk databases and shared-nothing architectures. So, it uses a central data storage that is accessible from all nodes in the warehouse.

Method 1: Using ETL Scripts to Connect Sendgrid to Snowflake

In a broad sense, the steps involved in achieving the data transfer from SendGrid to Snowflake using custom ETL scripts are:

• Step 1: Extracting Data from SendGrid using Web APIs
• Step 2: Transforming SendGrid Data
• Step 3: Loading the Extracted Data to Snowflake

Prerequisites

There are a few things to keep in mind before performing data migration from SendGrid to Snowflake.

1. An understanding of working with Web APIs.
2. A fully functional SendGrid account. 
3. A successful set-up of Snowflake data warehouse.

Step 1: Extracting Data from SendGrid using Web APIs

SendGrid data can be extracted using Web, SMTP, and Sendgrid APIs. SendGrid supports two types of Webhooks: Inbound Email Parse Webhook and Event Webhook POSTs.

• When an email event occurs such as bounce rate or unsubscribe, Event Webhook POSTs occur.
• The Inbound email parse webhook receives email parameters such as subject, attachments, and then posts them. 

You can make an API call by invoking the SendGrid endpoint API. Below is an example.

https://api.sendgrid.com/api/bounces.get.json?api_user=your_sendgrid_username&api_key=your_sendgrid_password&date=1

A JSON response containing bouncing emails will be returned. This is shown below.

[
  {
    "status": "4.0.0",
    "created": "2011-09-16 22:02:19",
    "reason": "Unable to resolve MX host sendgrid.ne",
    "email": "example@example.com"
  },
  {
    "status": "4.0.0",
    "created": "2011-09-19 17:47:15",
    "reason": "Connection timed out",
    "email": "example@example.com"
  },
  {
    "status": "5.1.1",
    "created": "2011-10-17 11:07:10",
    "reason": "550 5.1.1 The email account that you tried to reach does not exist. Please try double-checking the recipient's email address for typos or unnecessary spaces. Learn more at http://mail.google.com/support/bin/answer.py?answer=6596 z10si838244anz.171 ",
    "email": "example@example.com"
  },
  {
    "status": "5.7.1",
    "created": "2011-10-26 23:06:22",
    "reason": "550 5.7.1 <example@example.com>... Relaying denied. Proper authentication required. ",
    "reason": "550 5.7.1 <info@bnl.co.uk>... Relaying denied. Proper authentication required. ",
    "email": "example@example.com"
  },
  {
    "status": "5.7.1",
    "created": "2011-12-01 01:45:55",
    "reason": "554 5.7.1 <example@example.com>: Relay access denied ",
    "reason": "554 5.7.1 <richard@hengseng.com>: Relay access denied ",
    "email": "example@example.com"
  },
  {
    "status": "550",
    "created": "2012-06-01 14:10:15",
"reason": "550 support.sendgrid.net [74.63.202.100] is currently not permitted to relay through this server. Perhaps you have not logged into the pop/imap server in the last 30 minutes or do not have SMTP Authentication turned on in your email client. ",
    "email": "example@example.com"
  }
]

Step 2: Transforming SendGrid Data

Before loading data into Snowflake, you need to prepare it depending on the structure of your data. Make sure your data type matches the Snowflake data type well. Snowflake supports different data types such as BOOLEAN, INTEGER, DATETIME, etc. 

You also need to create a data schema before loading data into Snowflake.

CREATE TABLE sendgrid_bounces (
    status STRING,
    created TIMESTAMP,
    reason STRING,
    email STRING
);

Step 3: Loading the Extracted Data to Snowflake

Snowflake provides documentation for loading data into the warehouse. Read more here. There are two different approaches to loading data. 

1. Using Snowflake web UI. You can choose this option if you are loading a small amount of data. However, it is not suitable as an ETL solution.
2. Using PUT to stage files and COPY INTO command to load data into the Snowflake table. 

PUT file://path_to_your_file/bounces.csv @your_stage;


COPY INTO sendgrid_bounces
FROM @your_stage/bounces.csv
FILE_FORMAT = (TYPE = 'CSV', FIELD_OPTIONALLY_ENCLOSED_BY = '"')
ON_ERROR = 'CONTINUE';  -- You can choose other options based on your needs

Since Snowflake allows you to make a virtual warehouse to power the insertion process, local drive or Amazon S3 data can also be copied.

Challenges of Extracting Data from SendGrid Using Web APIs

• Infrastructure Maintenance:  You will need to invest in an engineering team since SendGrid may update its APIs at any given time.
• Real-time Data: So far, you have created a program that loads SendGrid data into Snowflake. However, this data will not be loaded in real-time. So, you will need to write additional code to solve this problem.
• Ability to Transform: When your SendGrid data type is not supported by Snowflake, for example, XML data, you will need to transform it before loading it into Snowflake. Also, let’s say you want to clean and transform the data before loading it to Snowflake, this too would be hard to achieve in the above method. You will need to write additional code to achieve this.

Method 2: Using Hevo to Connect Sendgrid to Snowflake

Hevo is fully managed and completely automates the process of loading data from SendGrid to Snowflake in two simple steps.

Step 1: Configure SendGrid as Source: 

• Connect Hevo Data with SendGrid by providing a unique name for your Pipeline and the API Key.

Step 2: Configure Snowflake as Destination

• Complete SendGrid to Snowflake migration by providing your destination name, account name, region of your account, database username and password, database and schema name, and the Data Warehouse name.

That is all! Isn’t it as easy as it can get? You can now relax and watch your data load from SendGrid to Snowflake.

Here are some more benefits of using Hevo

• No Data Loss: Data is moved reliably using the Hevo platform. It has a fault-tolerant architecture that ensures no data is lost. 
• Out of the Box Integrations: In addition to SendGrid,  Databases, SDKs, and Cloud Applications can also be connected to Data Warehouses like Snowflake, hence solving all your growing data needs.
• Minimal Setup:  Hevo has a very intuitive UI that makes the setup easy. Your data export will be up and running in a few minutes.
• Data Transformations: Hevo can help you clean, transform, and enrich the data both before and after moving to the data warehouse. This ensures you always have your SendGrid data ready for analysis.
• Automatic Schema Detection and Mapping: Hevo scans the schema of incoming SendGrid data. When changes are detected, Hevo handles them and incorporates the changes into the Snowflake.
• Strong Customer Support: Hevo team guarantees you 24×7 support over call, email, and chat.

Conclusion

This blog talks about the two methods you can use to set up a connection from SendGrid to Snowflake: using custom ETL scripts and with the help of a third-party tool, Hevo for free. It also gives a brief overview of SendGrid and Snowflake highlighting their key features and benefits before diving into the setup process.

Extracting complex data from a diverse set of data sources can be a challenging task and this is where Hevo saves the day! Hevo’s pre-built integration with 150+ Sources offers a faster way to move data from Databases or SaaS applications such as SendGrid into your Data Warehouse like Snowflake to be visualized in a BI tool for free. Hevo is fully automated and, hence, does not require you to code.

Want to take Hevo for a spin? Sign Up for a 14-day Free Trial and simplify your Data Integration process. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

We are excited to know what you think of this article. Let us know in the comments section below.

FAQs