CleverTap to Snowflake: 2 Easy Methods

Unlock the full potential of your Clevertap data by integrating it seamlessly with Snowflake. With Hevo’s automated pipeline, get data flowing effortlessly—watch our 1-minute demo below to see it in action!

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

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.

{
  "status": "success",
  "record": {
    "email": "jack@gmail.com",
    "profileData": {
      "Last Score": 308,
      "High Score": 308,
      "Replayed": true
    },
    "events": {
      "App Launched": {
        "count": 10,
        "first_seen": 1457271567,
        "last_seen": 1458041215
      },
      "Charged": {
        "count": 6,
        "first_seen": 1457962417,
        "last_seen": 1458041276
      }
    },
    "platformInfo": [
      {
        "platform": "iOS",
        "os_version": "10.2",
        "app_version": "6.1.3",
        "make": "Apple",
        "model": "iPhone7,2",
        "push_token": "95f98af6ad9a5e714a56c5bf527a78cb1e3eda18d2f23bc8591",
        "objectId": "-1a063854f834ac6484285039ecff87cb"
      },
      {
        "platform": "Web",
        "objectId": "a8ffcbc9-a747-4ee3-a791-c5e58ad03097"
      }
    ]
  }
}

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.

EMAIL	LAST SCORE	HIGH SCORE	FIRST SEEN
jack@gmail.com	308	308	1457271567

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.

See how syncing CleverTap with PostgreSQL can improve your data handling. Discover detailed instructions for a successful data transfer process.

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.

In addition to CleverTap, Hevo can load data from a multitude of 150+ 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.

Sign Up 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!

FAQ on CleverTap to Snowflake

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.