Extraction
Python connects to the Mailchimp API to extract campaign and email activity records, unpacks nested JSON, and writes new files to S3 only when new data arrives.
Project • Data pipeline
Mailchimp to Snowflake extraction pipeline
A secure, automated pipeline to extract Mailchimp campaign and email activity data, stage it in AWS S3, and load it into Snowflake for analytics using Python, Airbyte, dbt, and Kestra.
Key tools: Mailchimp API, Python, AWS S3, Snowflake, dbt, Airbyte, Kestra, Docker.
Outcome: robust data ingestion, automated orchestration, and composable analytics.
Mission
Project goal
Build a robust, secure data pipeline to extract campaign data from Mailchimp, store it in AWS S3, and process it into Snowflake. Orchestrate the pipeline using Kestra.
Architecture
Pipeline overview
- Mailchimp API extraction with Python and a polling process for new campaign data.
- AWS S3 staging layer with KMS encryption and structured bucket paths.
- Snowflake external stage and Snowpipe integration for near-real-time file ingestion.
- dbt models to transform Mailchimp campaign and email response data into analytics-ready tables.
- Kestra orchestration on Docker containers with a nightly cron trigger.
Staging
Files are staged in AWS S3 with encryption and structured folders, validating delivery and ensuring secure storage before Snowflake ingest.
Snowflake & dbt
Snowflake is connected to S3 through storage integration and Snowpipe. dbt builds the raw, silver, and final models required for analytics and joining Mailchimp data with other sources.
Orchestration
Kestra manages the full pipeline on Docker with a midnight cron schedule, ensuring reliable daily refreshes.
Project checklist
Key execution steps
- Configure AWS environment: KMS key, encrypted S3 bucket, IAM users, and secure credentials.
- Build Airbyte pipeline to export Mailchimp campaign and email activity data to Parquet in S3.
- Develop Python extraction logic, polling for new Mailchimp content and uploading data with boto3.
- Connect Snowflake with S3 and define external stages, file formats, and Snowpipe ingestion.
- Use dbt to model Mailchimp and email response data, flatten nested structures, and support final analytics joins.
- Automate the pipeline using Kestra and Docker with encoded secret handling and scheduled execution.
More details
Where to find the code
The full implementation is available in the repository at github.com/MorganRennie/des2_mailchimp.
Back to projects