SendGrid to Snowflake: Move Data Instantly

on Data Integration • May 19th, 2020 • Write for Hevo

Sendgrid to Snowflake Blog Cover Image

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.

Methods to Move Data from SendGrid to Snowflake

Broadly, there are two broad approaches to moving data from SendGrid to Snowflake

  1. Manually Building a Custom Code:
    If you happen to have developers ready to be deployed(or can hire resources), you may use this method. SendGrid provides two methods for useful data extraction-Online APIs and SMTP. Once the data is extracted it must be transformed to suit the destination schema. Using the appropriate code, you can then go on to load data into Snowflake.
  2. Using a No-code Data Pipeline, Hevo:
    Hevo, an official Snowflake Partner for Data Integration, simplifies the process of data transfer from SendGrid to Snowflake with its robust architecture and intuitive UI. You can achieve data integration without any coding experience and absolutely no manual interventions would be required during the whole process after set up. Explore a 14-day free trial here.

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

  1. An Introduction to SendGrid
  2. An Introduction to Snowflake
  3. Manually Building a Custom Code using ETL Scripts
  4. Challenges Involved in Building Custom Code
  5. Loading Data using a No-code Data Pipeline

An 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.

An Introduction to Snowflake

Snowflake is a  SaaS (Software as a Service) data warehouse that is easy to use, fast and flexible when compared with the 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 database and shared-nothing architectures. So, it uses a central data storage that is accessible from all nodes in the warehouse.

Method 1: Manually Building a Custom Code using ETL Scripts

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

  1. Identifying and Understanding the Prerequisites
  2. Step 1: Extracting Data from SendGrid using Web APIs
  3. Step 2: Transforming SendGrid Data
  4. 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. Learn how to set-up an account here.
  3. A successfully set-up 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 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"
  }
]

Source: https://sendgrid.com/docs/API_Reference/Web_API/bounces.html

Step 2: Preparing SendGrid Data

Before loading data into Snowflake, you need to prepare it depending on the structure of your data. Make sure your data type maps well with Snowflake data type. Snowflake supports different data types such as BOOLEAN, INTEGER, DATETIME, etc. You can read more about Snowflake data types here. 

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

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. 

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

Extracting Data from SendGrid using Web APIs: Challenges and Limitations

  1. Infrastructure Maintenance:  You will need to invest in an engineering team since SendGrid may update its APIs at any given time.
  2. 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.
  3. 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 to Snowflake, this too would be hard to achieve in the above method. You will need to write additional code to achieve this.

A much simpler and hassle-free solution would be to use a simple code-free platform like Hevo. Read on for more details on this approach.

Method 2: Loading Data using a No-code Data Pipeline, Hevo

Hevo, an official Snowflake Partner for Data Integration, is a fully managed platform that can provide seamless data transfer from SendGrid to Snowflake without a single line of code. Any data update in SendGrid will be automatically reflected in Snowflake without human intervention. With Hevo, you can attain data migration in two simple steps.

Step 1: Set up and Configure your SendGrid Data Source

Sendgrid to Snowflake - Sendgrid Connection Settings Image

Step 2: Connect the Snowflake Data Warehouse to seamlessly Transfer Data

Sendgrid to Snowflake - Snowflake Connection Settings Image

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

  1. No Data Loss: Data is moved reliably using the Hevo platform. It has a fault-tolerant architecture that ensures no data is lost. 
  2. Out of the Box Integrations: In addition to SendGrid,  Databases, SDKs, and Cloud Applications can also be connected to Snowflake, hence solving all your growing data needs.
  3. Minimal Setup:  Hevo has a very intuitive UI that makes the set up easy. Your data export will be up and running in a few minutes.
  4. 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.
  5. 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.
  6. Strong Customer Support: Hevo team guarantees you with 24×7 support over call, email, and chat.

Hevo ensures it needs the least of your attention and gets the job done with speed and perfection! Would you be willing it a whirl? Get started by signing up for a 14-day free trial today!

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

All your customer data in one place.

Continue Reading