CleverTap to Snowflake: 2 Easy Methods

CleverTap is the only platform that uses AI and ML to personalize customer experience using real-time behavioral and pattern data. These precise predictive models result in time-relevant engagement opportunities based on each user’s exact requirements. The platform also segments the users based on various parameters, so no manual intervention is required. In this post, you will know various ways to move the data from CleverTap to Snowflake data warehouse.

Introduction to CleverTap

It is a platform that effortlessly collects various events and data regarding the users that visit your website or application. The platform provides various plugins that can be integrated to get the consolidated data of user trends and habits on the website or the application, to capitalize on the retention of the user.

Introduction to Snowflake

Snowflake is an analytic, flexible, intuitive data warehouse provided as SaaS. It provides a data warehouse that is faster, easier to use, and far more flexible than most data warehouse offerings.

Snowflake’s data warehouse is not built on an already pre-existing database or “big data” software platform such as Apache Hadoop. Snowflake data warehouse uses a new SQL database engine with a one-of-a-kind architecture designed for the cloud. To the user, Snowflake may have many similarities to other enterprise data warehouses but also has additional functionality and unique capabilities.

Methods to Migrate Data from CleverTap to Snowflake

Following are the 2 popular methods to perform CleverTap to Snowflake data migration:

• Method 1: Loading Data by Building Custom Scripts
• Method 2: Using a No-code Data Pipeline

Method 1: Loading Data by Building Custom Scripts

Let us first understand the requirements in terms of setting up the infrastructure and the knowledge required to execute the task.

Prerequisites

• Snowflake should be set up, an account should be provisioned before beginning. Refer this link to learn to set up Snowflake.
• Python has to be installed and set up in the system as you will use Python to manually shift data from CleverTap to Snowflake data warehouse. Installation steps can be found here.
• Working knowledge of Python and ETL operations.
• Need to have an account set up in CleverTap connected to one of your services, from which the data will be retrieved. More information related to CleverTap can be found here.

Building custom scripts using Python to transfer data from CleverTap to Snowflake can be achieved with the steps listed below.

• Step 1: Connect to CleverTap API
• Step 2: Push the Data Values to Snowflake

Step 1: Connect to CleverTap API

The following snapshot is an example of the response data from the CleverTap API.

Step 2: Push the Data Values to Snowflake

Now, read the data from the CleverTap platform and convert the JSON response to the Python dictionary. Push the data values to the Snowflake’s corresponding table. The following code performs the above-mentioned steps in Python.

#!/usr/bin/env python
#
# Snowflake Connector for Python Sample Program
#

# Logging
import logging
logging.basicConfig(
    filename='/tmp/snowflake_python_connector.log',
    level=logging.INFO)

import snowflake.connector
import json
import requests

#Connecting to Clevertap account and getting the Information
headers = {
    'X-CleverTap-Account-Id': 'ACCOUNT_ID',
    'X-CleverTap-Passcode': 'PASSCODE',
    'Content-Type': 'application/json',
}

params = (
    ('email', 'john@gmail.com'),
)

response = requests.get('https://api.clevertap.com/1/profile.json', headers=headers, params=params)

clevertapdata = json.loads(response)

# Set your account and login information (replace the variables with
# the necessary values). Note that ACCOUNT might also require the
# region and cloud platform where your account is located, in the form of
# '<your_account_name>.<region_id>.<cloud_platform>' (e.g. 'xy12345.east-us-2.azure')
ACCOUNT = '<your_account_name>'
USER = '<your_login_name>'
PASSWORD = '<your_password>'

import os

# Only required if you copy data from your own S3 bucket
AWS_ACCESS_KEY_ID = os.getenv('AWS_ACCESS_KEY_ID')
AWS_SECRET_ACCESS_KEY = os.getenv('AWS_SECRET_ACCESS_KEY')

# Connecting to Snowflake
con = snowflake.connector.connect(
  user=USER,
  password=PASSWORD,
  account=ACCOUNT,
)

# Creating a database, schema, and warehouse if none exists
con.cursor().execute("CREATE WAREHOUSE IF NOT EXISTS tiny_warehouse")
con.cursor().execute("CREATE DATABASE IF NOT EXISTS testdb")
con.cursor().execute("USE DATABASE testdb")
con.cursor().execute("CREATE SCHEMA IF NOT EXISTS testschema")

# Using the database, schema and warehouse
con.cursor().execute("USE WAREHOUSE tiny_warehouse")
con.cursor().execute("USE SCHEMA testdb.testschema")

# Creating a table and inserting data
con.cursor().execute(
    "CREATE OR REPLACE TABLE "
    "testtable(col1 integer, col2 string)")
con.cursor().execute(
    "INSERT INTO testtable(col1, col2) "
    "VALUES(%s,%s,%s,%s)")

# Copying data from internal stage (for testtable table)
con.cursor().execute("PUT file:///tmp/data0/file* @%testtable", clevertapdata["record.Email","record.profileData.Last Score","record.profileData.High Score","events.App Launched.first_seen"])
con.cursor().execute("COPY INTO testtable")

# Copying data from external stage (S3 bucket -
# replace <your_s3_bucket> with the name of your bucket)
con.cursor().execute("""
COPY INTO testtable FROM s3://<your_s3_bucket>/data/
     STORAGE_INTEGRATION = myint
     FILE_FORMAT=(field_delimiter=',')
""".format(
    aws_access_key_id=AWS_ACCESS_KEY_ID,
    aws_secret_access_key=AWS_SECRET_ACCESS_KEY))

# Querying data
cur = con.cursor()
try:
    cur.execute("SELECT col1, col2 FROM testtable")
    for (col1, col2) in cur:
        print('{0}, {1}'.format(col1, col2))
finally:
    cur.close()

# Binding data
con.cursor().execute(
    "INSERT INTO testtable(col1, col2) "
    "VALUES(%(col1)s, %(col2)s)", {
        'col1': 789,
        'col2': 'test string3',
        })

# Retrieving column names
cur = con.cursor()
cur.execute("SELECT * FROM testtable")
print(','.join([col[0] for col in cur.description]))

# Catching syntax errors
cur = con.cursor()
try:
    cur.execute("SELECT * FROM testtable")
except snowflake.connector.errors.ProgrammingError as e:
    # default error message
    print(e)
    # customer error message
    print('Error {0} ({1}): {2} ({3})'.format(e.errno, e.sqlstate, e.msg, e.sfqid))
finally:
    cur.close()

# Retrieving the Snowflake query ID
cur = con.cursor()
cur.execute("SELECT * FROM testtable")
print(cur.sfqid)

# Closing the connection
con.close()

This successfully loads CleverTap data to Snowflake. You can choose what columns and what data you require, or you can get the whole data itself by creating the corresponding columns in the data warehouse.

The above is one of the example outputs of a Snowflake table which contains the data received from the CleverTap API account. The table can have any number of columns pertaining to data that is required from the CleverTap account. Accordingly, the data table can be changed to reflect whatever columns are required.

Limitations of using Custom Code

1. Effort Intensive: Using custom code to move data from CleverTap to Snowflake requires you to learn and bring together many different technologies. Given the learning curve involved, your data projects’ timelines can be affected.
2. Not Real-Time: The process mentioned above does not help you bring data in real-time. You would have to develop a cron job and write extra code to bring data in real-time.
3. No Data Transformation: At times, you would encounter use cases where you need to standardize and transform your data in order to perform efficient analytics. The mentioned approach does not cover that.
4. Constant Monitoring & Maintenance: If there are some changes in the API at Clevertap’s end or Snowflake’s end, it will result in irretrievable data loss. Hence, this approach requires constant monitoring and maintenance of the systems involved.

Method 2: Using a No-code Data Pipeline

Hevo, a No-code Data Pipeline, is a hassle-free alternative for data transfer from CleverTap to Snowflake for free. Hevo provides an easy-to-use visual interface that allows you to load data in two easy steps:

Step 1: Configure and set up CleverTap as Data Source

CleverTap is an omnichannel platform used by companies to engage and retain their customers across industry domains such as travel, fin-tech, e-commerce, food-tech, and hospitality. You can load your CleverTap Events to a Destination system using Hevo Pipelines.

First, you need to configure your CleverTap account by filling up the following data fields.

Step 2: Set Snowflake as the Destination

For Hevo to access your data, you must assign the required permissions. Snowflake uses Roles to assign permissions to users. You need ACCOUNTADMIN, SECURITYADMIN or SYSADMIN privileges to create the required roles for Hevo. Read more about Roles in Snowflake.

Voila! Sit back and watch your data move within minutes.

Advantages of Using Hevo

• Minimal Setup: The process involved in setting up Hevo is easy to execute and extremely simple to follow.
• Fully Managed Platform: Hevo is completely managed. You need not invest time and effort to monitor the infrastructure.
• Real-time Data Migration: Hevo will sense incoming data in CleverTap and make corresponding modifications in Snowflake alleviating the need for continuous monitoring.
• More Data Sources: Hevo can bring data from not just CleverTap but over 100+ data sources. This ensures that you can scale your data pipeline any time, at will.
• Ability to Transform Data: Hevo allows you to transform the data before and after transferring it to Snowflake. Data is ever ready for analysis with Hevo on your side.
• 24×7 Customer Support: Hevo provides you with customer support round the clock over call, email, and chat.

Sign up here for a 14-Day Free Trial!

Conclusion

The article explained to you the two methods of connecting CleverTap to Snowflake in a step-by-step manner. It also discussed the limitations of writing custom scripts to set up your ETL process. If you have no qualms about facing those limitations then you can try the manual ETL setup.

Hevo Data on the other hand can simplify your task by eliminating the need to write any code. It will automate the process of data transfer from CleverTap to Snowflake and provide you with a hassle-free experience. Hevo provides granular logs that allow you to monitor the health and flow of your data. It helps you transfer data from CleverTap for free.

Visit our Website to Explore Hevo

In addition to CleverTap, Hevo can load data from a multitude of 100+ other data sources including Databases, Cloud Applications, SDKs, and more. This allows you to scale up your data infrastructure on demand and start moving data from all the applications important for your business.

Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

We would be thrilled to know what you think of the methods to transfer data from CleverTap to Snowflake detailed in this article. Let us know your thoughts and your preferred way of moving data in the comments section below.