Now Available On-Demand | AI. The Future of Data Engineering - Today. Not Just Tomorrow.

Watch now

Using the Mailchimp Query Component in Matillion ETL for Snowflake

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

mailchimp matillion etl bigquery

The Mailchimp Query component in Matillion ETL for Snowflake presents an easy-to-use graphical interface, enabling you to pull data from Mailchimp and load it directly into Snowflake. Many of our customers are using this to combine marketing email data with other marketing and sales data to measure campaign effectiveness.

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.

Video

Watch our tutorial video for a demonstration on how to set up and use the Mailchimp Component in Matillion ETL for Snowflake.

 

Data Extraction

This component runs a SQL Query on Mailchimp and copies the results to a table, via S3 staging buckets.This component is for data-staging – getting data into a table in order to perform further processing and transformations on it in Snowflake. You should consider the target table temporary, as it will either truncate or recreate each time the components runs.

Mailchimp Query Component in Matillion ETL for Snowflake - Data Extraction

Above is a typical workflow for importing data from a Mailchimp server into Snowflake.

  1. Mailchimp connects and executes SQL against the Mailchimp.
  2. The component reads the data from Mailchimp and streams it to a S3 Staging bucket. (Data is never written to disk).
  3. Matillion copies the data from S3 Stage buckets to Snowflake using “Copy” command.

Authentication

Before you can use the Mailchimp component there are a couple of prerequisites:

  1. The Mailchimp OAuth or API key has been setup and is accessible from the Matillion instance. You can read more about setting up Mailchimp OAuth in Matillion ETL here.
  2. You have provisioned your Snowflake data warehouse and it is running on AWS platform.

Once these are met, your Matillion instance can reach the service via a Mailchimp endpoint URL available in your AWS console.

Using the Mailchimp Query Component in Matillion ETL for Snowflake

You can find the Mailchimp Query Component under the “Load/Unload” folder in the Components panel. The following image shows the properties for a configured component:

Mailchimp Query Component in Matillion ETL for Amazon Redshift - Properties
  • The options ‘API Key’, ‘Data Source’ and ‘Data Selection’ cover the details required to establish the connection to your Mailchimp Server.
  • You will also need to specify a ‘S3 Staging Area’ which is the S3 bucket that will hold the results prior to loading into Snowflake.
  • For the ‘Schema’, you can identify the staging table on Snowflake you want to load the data into. Each time you run the component, the table will drop and rebuild. You should therefore treat this table as a Staging table and subsequently move the data into a Persistent table as soon as possible.

The Mailchimp Query component retrieves data on a Mailchimp and loads it into an Snowflake table. You will use a S3 bucket to stage the data. So each time you run the component, the table will truncate and reload. You may then use transformation job to enrich and manage the data in permanent tables.

Warning: This component is destructive as it truncates or recreates its target table on each run. Do not modify the target table structure manually.

Connection Options

The component has additional JDBC parameters supported by the Database Driver. The driver automatically determines the available parameters, and may change from version to version. None of the Connection Options are mandatory with the Mailchimp driver, as Matillion ETL usually provides sensible defaults.

Advanced Mode

Finally, you can use Advanced Mode to issue SQL Query against the MailChimp. You can therefore replace any variable definitions with the appropriate values and then issue the query against your Mailchimp Server.

Orchestration Job

The following image shows a Orchestration job that pulls data from a Mailchimp Server into a target table in Snowflake and then copies the data to persistent table using a Transformation job.

Mailchimp Query Component in Matillion ETL for Amazon Redshift - Orchestration Job

Conclusion

In conclusion, we hope that gave you a good overview of the Mailchimp Query component in Matillion ETL for Snowflake. Give it a go and let us know ([email protected]) if you need any support with this component.

Useful Links

Mailchimp Query Component in Matillion ETL for Snowflake
Component Data Model
OAuth Set Up