Marketo to Snowflake Data Migration: 2 Easy Ways to Load Data

Marketo is a marketing automation platform that B2B and B2C businesses use to manage and deliver multi-channel campaigns and programs that are tailored to prospects and customers. With Marketo, businesses can organize their unfiltered user data to develop targeted campaigns and programs for a variety of marketing activities, from lead generation to marketing ROI analysis, across a variety of channels.

Snowflake’s Data Cloud is built on a cutting-edge data platform that is delivered as Software-as-a-Service (SaaS). Snowflake provides Data Storage, Processing, and Analytic Solutions that are faster, easier to use, and more flexible than traditional options.

This article explains how to Connect Marketo to Snowflake using various methods. It also gives an overview of Snowflake and Marketo.

Why Integrate Marketo to Snowflake?

Marketo offers marketing automation software that enables marketers to become experts in both the art and science of digital marketing to engage clients and prospects. 

Snowflake offers the Data Cloud, a global network with nearly limitless scale, concurrency, and performance that enables the data mobilization of thousands of organizations. 

Integrating Marketo to Snowflake simplifies data problems. For advanced marketing analytics, multi-touch attribution, and other features, centralize your Marketo to Snowflake. Marketo to Snowflake unifies its fragmented data inside the Data Cloud so that it can be easily discovered, shared securely, and used for a variety of analytic workloads.

Key Methods to Migrate Marketo to Snowflake

• Method 1: Using Hevo to Set Up Marketo to Snowflake
• Method 2: Using Custom Code to Move Data from Marketo to Snowflake

Method 1: Using Hevo to Set Up Marketo to Snowflake

Hevo provides an Automated No-code Data Pipeline that helps you move your Marketo to Snowflake. Hevo is fully-managed and completely automates the process of not only loading data from your 150+ data sources(including 40+ free sources)but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled securely and consistently with zero data loss.

Using Hevo, you can connect Marketo to Snowflake in the following 2 steps:

• Step 1: Configure Marketo as the Source in your Pipeline by following these steps:
  • Step 1.1: For your Marketo instance, get authenticated access credentials.
  • Step 1.2: In the Asset Palette, select PIPELINES.
  • Step 1.3: In the Pipelines List View, click + CREATE.
  • Step 1.4: Select Marketo on the Select Source Type page.
  • Step 1.5: Enter the following information on the Configure your Marketo Source page:

• Pipeline Name: A distinct name for the Pipeline that isn’t longer than 255 characters.
• Client ID: Accessible at the recently launched service.
• Client Secret: Accessible at the recently launched service.
• Endpoint: The primary URL that is used for all API requests.
• Identity Endpoint: The endpoint where access tokens can be retrieved using the client secret and client ID.

• Step 1.6: Simply press TEST & CONTINUE.
• Step 1.7: Configure the data ingestion and establish the destination after that.
• Step 2: To set up Snowflake as a destination in Hevo, follow these steps:
  • Step 2.1: In the Asset Palette, select DESTINATIONS.
  • Step 2.2: In the Destinations List View, click + CREATE.
  • Step 2.3: Select Snowflake from the Add Destination page.
  • Step 2.4: Set the following parameters on the Configure your Snowflake Destination page:
    • Destination Name: A unique name for your Destination.
    • Snowflake Account URL: This is the account URL that you retrieved.
    • Database User: The Hevo user that you created in the database. In the Snowflake database, this user has a non-administrative role.
    • Database Password: The password of the user.
    • Database Name: The name of the Destination database where data will be loaded.
    • Database Schema: The name of the Destination database schema. Default value: public.
    • Warehouse: SQL queries and DML operations are performed in the Snowflake warehouse associated with your database.

• Step 2.5: Click Test Connection to test connectivity with the Snowflake warehouse.
• Step 2.6: Once the test is successful, click SAVE DESTINATION.

Method 2: Using Custom Code to Move Data from Marketo to Snowflake

You use an indirect method to connect Marketo to Snowflake. First, you connect Marketo to Redshift and move data from Redshift to Snowflake.

Marketo to Redshift

To connect Marketo to Snowflake, first, you have to create an Access Token for Marketo API, log in to your Marketo account, and open the Admin page. Expand the Access API section and scroll down and select Read-only Lead or any other access control option based on your requirements. You will now need to create an API-only user and associate it with the API role that was created previously. Once the required information about the user has been created, click Invite.

Next, to connect Marketo to Snowflake, you Extract Leads Data using Marketo API, You can extract the Lead details from Marketo in JSON form using the Access Token generated previously. Marketo can return a maximum of 300 results per request. If the results consist of more than this number of entries, the API response will contain two additional parameters, i.e., moreResults and nextPageToken. To Transform the JSON Data to CSV, you can convert the JSON output to a CSV file.

Luckily, there are several tools available that can make this possible. The CSV data can then be copied and saved in a file in your local system. To load the CSV Data to Redshift, users can load their data exported from Marketo to Redshift using AWS Management Console and Amazon S3.

To learn more click here.

Redshift to Snowflake

Next, to connect Marketo to Snowflake you move data from Redshift to Snowflake.

Database Objects Migration

The first step is, to begin with, database objects, which primarily include Schema, Table Structures, Views, etc. Instead of changing the object’s structure during migration, we should prefer to leave it alone because doing so would harm the entire migration process. Later, DB objects in Snowflake must be created with the same structure as those in Redshift.

Data Migration

• Any project involving migration must include this activity. The first step is to determine the historical data sets for each table and how they can be migrated because the data volume will be quite high and we need to take this into account before beginning data migration activity. It is strongly advised to create separate batches for each table (based on the filter options like transaction date or any other audit columns) and migrate data in these batches rather than in one batch. 
• After all historical data from all tables has been transferred to Snowflake, it will be relatively easy to move incremental data.
• Using Redshift’s “Unload Command” to unload data into S3 and Snowflake’s “Copy Command” to load the data from S3 into Snowflake tables could be one solution. This may also display some errors caused by compatibility problems, which you have already experienced on several occasions. 
• Another strategy involves using any data replication tool that supports Snowflake as a target, which can then be used to load raw data from the source system into Snowflake. ETL/ELT pipelines can fill facts, dimensions, and metrics tables on the Snowflake platform on top of this raw data.

Migrating Code to Snowflake 

• Compared to the previous two steps, this one has fewer difficulties and restrictions. In theory, Redshift and Snowflake both support ANSI-SQL, but they use different formats for various items, such as the lack of DISTKEY, SORTKEY, and ENCODE concepts in Snowflake. 
• There are many other examples of using functions, but one of the key distinctions between date functions is that Redshift’s GETDATE() and Snowflake’s CURRENT TIMESTAMP() are both date functions. While JSON, AVRO, and PARQUET are semi-structured data types that can be supported by Snowflake’s “VARIANT” datatype, they cannot be directly stored in Redshift.
• To match the JSON field names in Redshift, the target table must be created by examining the JSON source data. Without a set structure, we cannot import it directly. Only the first-level elements can be parsed into the target table by the COPY functions. 
• As a result, the multi-level elements are loaded into a single column and treated as strings. Redshift provides JSON SQL functions that must be used to further parse the intricate, multi-level data structures or arrays of JSON files. When migrating code, one must be extremely cautious and convert the code to supported SQL syntax.

Data Comparison between Redshift & Snowflake

• The final step in any migration project is to compare the data sets from the legacy and newly migrated platforms to make sure that everything was successfully migrated and that the output was accurate for the business. 
• Given that you are moving from Redshift to Snowflake in this scenario, you must contrast the outcomes from the two systems. Data comparison between Redshift and Snowflake manually is a time-consuming and difficult task.
• Therefore, you created a custom Python script solution to connect to each DB, run some crucial checks against both DBs (Redshift as the source and Snowflake as the target), and compare the results.
• Some crucial checks include Record Counts, Data Type Comparisons, Metrics Comparisons in Fact Tables, DB Object Counts, Duplicate Checks, etc. A daily CRON job can be scheduled or automated to execute this solution.

Conclusion

This article discusses the different methods to Connect Marketo to Snowflake extensively. In addition to that, it also describes Marketo and Snowflake briefly.

Visit our Website to Explore Hevo

Hevo offers a No-code Data Pipeline that can automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Marketing, Customer Management, etc.

This platform allows you to transfer data from 150+ sources (including 40+ Free Sources) such as Marketo and Cloud-based Data Warehouses like Snowflake, Google BigQuery, etc. It will provide you with a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin? 

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.