Using the Marketo Query component in Matillion ETL for Snowflake

Using the Marketo Query component in Matillion ETL for Snowflake

Marketo Query component in Matillion ETL for Amazon RedshiftMatillion uses the Extract-Load-Transform (ELT) approach to delivering quick results for a wide range of data processing purposes: everything from customer behaviour analytics, financial analysis, and even reducing the cost of synthesising DNA.

The Marketo Query component in Matillion ETL for Snowflake presents an easy-to-use graphical interface, enabling you to pull data from Marketo directly into Snowflake. As a result, many of our customers are using this to combine Marketo data with Sales data to measure the effectiveness of their marketing.

The connector is completely self-contained: no additional software installation is required. It’s within the scope of an ordinary Matillion license, so there is no additional cost for using the features.

Authentication

The first step in configuring the Marketo Query component is to authenticate Matillion to access the Marketo data. This is done by configuring OAuth. Further details on setting up OAuth are available on the support portal. Following OAuth configuration, select your Marketo instance from the dropdown in the component:

 

Matillion-ETL-MarketoQueryComponent-Authentication-Snowflake

 

Rest Endpoint

This is a URL giving the REST API Endpoint for your Marketo data. Matillion will then use this to connect into and pull the data. This is available in the Marketo Admin app under Web Services.

Data Source

Next, use the Data Source drop down to choose what data to load into Snowflake. This is a list of all objects in your Marketo Organization:

 

Matillion-ETL-MarketoQueryComponent-DataSource-Snowflake

 

After choosing the data source, the next step is to choose the required fields from the data source in the Data Selection. This is a list of all fields in the Data Selection. Consequently, the selected fields will form the new table created in Snowflake.

 

Matillion-ETL-MarketoQueryComponent-DataSelection-Snowflake

 

If required, add a Data Source Filter to filter the returned records. The most common use case to filter data is to filter by date to support incremental updates.

Connection Options

Connection Options are additional parameters supported by the driver. The Marketo driver usually provides sensible defaults and therefore does not mandate the configuration of Connection Options. However, further details on Connection Options are available on the support portal.

Running the The Marketo Query component in Matillion ETL for Snowflake

Before you run the component, give the Target Table a name. This is subsequently the name of the new table created to write the data into Snowflake. Additionally, specify a S3 Staging Area; this is a S3 bucket which will temporarily store the results of the query before loading it into Snowflake.

Force an upper limit on the number of records returned using the Limit property feature that comes with the component. We recommend using either a limit or a filter to reduce the number of rows returned to improve the speed of your job.

You can run the Orchestration job, either manually or alternatively by using the Scheduler, to query your data and bring it into Snowflake.

 

Matillion-ETL-MarketoQueryComponent-Run-Snowflake

 

Transforming the Data

Once the required data has been brought in from Marketo into Snowflake, it can then be used in a Transformation job. A noteworthy transformation is to enhance existing data:

 

 

In this way, you can build out the rest of your downstream transformations and analysis, taking advantage of Snowflake’s power and scalability.

Info

Want to try the Marketo Query component in Matillion ETL for Snowflake? Arrange a free 1-hour training session now, or start a free 14-day trial.