Modernising Marketing Analytics Pipelines with Snowflake
INDUSTRY:
Asset and Wealth Management Consulting
Executive summary
Our client engaged Ennovision Technology Solutions to modernise its marketing analytics ingestion architecture by migrating legacy BigQuery-based data pipelines for Matomo and Fanpage Karma (FPK) into a fully Snowflake-native engineering ecosystem. The project centred on the delivery of robust, automated API ingestion pipelines, designed to increase automation, resilience, and scalability while reducing operational overhead.
This case study examines the business challenge and delivered outcomes. It highlights how Ennovision enabled the client to simplify operations, reduce manual effort, and future-proof their analytics ingestion architecture.
Business Challenge
The client supports multiple financial services clients with digital analytics reporting, aggregating data from Matomo and social media platforms such as YouTube, Twitter, Facebook, and Instagram. Historically, these pipelines ran through a combination of Google BigQuery, ad-hoc scripts, and manual operational processes. As the client scaled its reporting offerings, this legacy setup presented several challenges:
• Operational overhead: Manual reruns, duplicate loads, and inconsistent behaviour across datasets.
• Lack of automation: Existing scripts required human intervention to handle failures, retries, or backfills.
• Limited resilience: API timeouts and rate limits frequently caused ingestion failures.
• Platform misalignment: Growing Snowflake adoption across the client ecosystem meant analytics infrastructure needed to consolidate on a single cloud platform.
To support strategic consolidation and enhanced reliability, the client initiated a programme to migrate Matomo and Fanpage Karma ingestion to Snowflake via Snowpark-based stored procedures.
Project Scope
The SOW established a clear mandate for Ennovision: to build Snowflake-native API ingestion pipelines—for Matomo and for Fanpage Karma—leveraging Snowpark Python stored procedures and Snowflake Tasks. These pipelines needed to support both daily and monthly ingestion, respectively, and include backfill logic, API connectivity, and error logging.The pipelines were required to not only replicate legacy capabilities but uplift them into a fully automated, robust Snowflake-native architecture.
Solution Highlights
Snowflake-Native Architecture
Ennovision delivered a cohesive engineering framework built on Snowflake’s native compute and orchestration capabilities:
• Direct API → Snowflake ingestion using Snowpark Python
• Snowflake Tasks orchestrating daily and monthly pipelines
• Idempotent processing logic preventing duplicate ingestion
• Centralised control layer using a CLIENT_PROFILES table to drive scheduling, parameters, and rerun conditions
• Structured audit/error logging for full lineage and operational observability
• Parameterised execution framework enabling flexible backfills and targeted reprocessing
This architecture provides a scalable, extensible foundation for integrating additional external APIs in the future.
Matomo Pipeline (Daily)
• Robust incremental ETL with timezone alignment
• Automated handling and flattening of nested JSON structures (e.g., actionDetails)
• Null-safe parsing for dynamic, variable-rich payloads
• Controlled backfills with intelligent date-gap detection
Fanpage Karma Pipeline (Monthly)
• Multi-platform ingestion across Facebook, Instagram, YouTube, and Twitter
• Deterministic ID generation ensuring consistent primary keys
• Schema validation and automated deduplication
• Skip-if-exists logic ensuring safe, repeatable executions
Control & Governance Layer
• Centralised CLIENT_PROFILES table enabling flexible scheduling and parameter-driven execution
• Unified logging structures providing end-to-end traceability and resilience
Enhancements Beyond Scope
Ennovision delivered several advanced engineering capabilities not originally required but highly beneficial:
• Three-tier exponential retry system for API timeouts and rate limits
• Intelligent continuation logic, enabling partial success while isolating errors
• Deep JSON normalisation, improving downstream analytical usability
• Enhanced idempotency and ingestion-safety rules, reducing operational burden
Business & Technical Value Delivered
Business Impact
• Significant reduction in manual operational effort
• Improved reliability and consistency in digital engagement datasets
• Faster, safer historical backfills supporting client onboarding
• Stronger, more reliable data for client reporting.
Technical Impact
• Fully Snowflake-native, automated API ingestion framework
• Resilient pipelines built for enterprise-scale workloads
• Extensible architecture ready for new data sources and future integrations
• Improved data quality, lineage, and observability within Snowflake
Conclusion
Through the delivery of Snowflake-based ingestion pipelines and a unified control framework, Ennovision modernised the client’s analytics ingestion architecture into a highly automated, resilient, and scalable Snowflake- active ecosystem. The solution exceeded SOW expectations by introducing advanced reliability mechanisms, improved data quality, and flexible operational control—establishing a future-ready platform for ongoing data integration across the client’s portfolio.