Skip to Content

Ad-Council modern data stack

Blog | September 9, 2022 | By Lokesh Bansal, Anshuman Gautam

In the previous blog, we saw how USEReady’s consultants assisted Ad Council in making a well-guided decision to leverage the power of cloud. Migration to the cloud, specifically -SnowflakeTM, a cloud-only data platform with near infinite storage and computing solved the problem of ever-increasing data volumes, resource intensive computations all within budget. Additionally, it helped Ad Council in some of its specific use cases identified by USEReady consultants such as dealing with redundancy in prod data, reprocessing of source feeds, simplified architecture, and most importantly reduction in time taken for daily processing time by almost 90% as compared to SQL server.

Nonetheless the teams realized that just storing data on the Cloud could not help with the seamless processing at the speed and scale a modern cloud data platform offers. Bottlenecks in the data pipeline has to be remediated in order for Ad Council to reap the benefits to a modern data stack.

SSIS, a legacy on-perm ETL tool was able to support data integration and data transformation requirements and other downstream applications. It had many advantages amongst legacy ETL tools, primarily integration with SQL Server.

Advantages of SSIS: 

  • Easy Active Directory integration for seamless connectivity to SQL Server and visual studio, hence quicker data transfer 
  • Ease of use with help of Package Explorers 
  • Flexibility to work with heterogeneous data transformations 
  • Standardized data integration 
  • Reusability with help of Control Flow across Packages by Using Control Flow Package Parts 
Fig 1: Moving data across different stages with SSIS

SSIS certainly had its advantages and seamless connectivity to SQL server but as Ad Council moved to digital mediums for wider reach of its successful campaigns, it was no longer feasible to continue using a legacy tool like SSIS given the speed, scale and complexity of data feeds.

The accelerated digitization made it vital for organizations to be able to handle complex, large, and near real-time data with ease. In the case of Ad Council, it’s campaigns were heavily dependent social media platforms to reach larger audiences. It becomes vital for the campaign managers, sponsors to analyse data from social media platforms, search engines to understand the trends emerging from these platforms. This data is an important indicator of a campaign’s effectiveness. Data feeds from these social media platforms have to be processed at speed and scale that most traditional ELT tools are not designed to handle.

The need for a modern data stack at Ad Council was immediate and pronounced. Consultants from USEReady worked on identifying a modern data ingestion tool that could help address Ad Council specific data use cases like getting near real-time data from social media platforms, leverage the power of Snowflake.

Amongst the various tools available in the market, Matillion was shortlisted. A cloud-native ELT tool, purpose-built for Snowflake, it aligns perfectly well with Ad Council’s requirements. Matillion can efficiently ingest data from a wide variety of data sources with scale, speed and efficiency not possible with either on-prem legacy tools or other tools available. Access to data in a predictable and timely fashion helps deliver vital analytics to campaign managers, sponsors. Additionally, unlike on-prem ETL tools like SSIS, Matillion leverages the compute of Snowflake, making it an efficient and cost-effective combination to employ.

Benefits of Matillion for Snowflake: 

  • A cloud native ELT tool, custom built for snowflake. 
  • Data integration and transformation for cloud data warehouses 
  • More than 100 pre-built connectors for multiple data sources. 
  • Simple, GUI based low-code interface to orchestrate and transform data 
  • Rest API Connectivity to multiple online sources like Google Analytics, Facebook insights.   
  • Option to create custom connectors to ingest data from RESTful APIs 
  • Works efficiently with near real-time or click-stream data  
  • Faster data ingestion 
  • Additional connectors like ‘CData adaptors’ not required to get data from online sources. 
  • Option to develop custom code leveraging SQL, Bash, or Python to cater to specific business requirements  
  • Simple, transparent and cost-effective pricing model

This titled the scales in favour of Matillion, thereby helped sunset SSIS without disrupting operations. It also helped Ad Council to generate insights from multiple data sources like social media, survey info, 3rd party inputs; manage campaigns effectively predictably and with-in budget.

A quick comparison 

SSIS Matillion 
On Premise ETL tool Cloud Native ELT tool 
Connectivity restricted to RDBMS, flat files.  Connectivity possible with all types of sources: SQL, NoSQL Databases, APIs, Files, Cloud storage, etc 
CData adaptors for SSIS required additionally to connect with online data sources, increasing costs Additional adaptors not required, hence no additional licensing costs 
Limited data transformation options Can perform complex data transformations like CDC, handling semi-structured data, etc 
No Sample data for performed transformations  SQL based transformations provide samples, row count and data lineage information 
Administrative overhead to provision, maintain infrastructure Cloud native tool, requires minimal administrative support to provision, maintain infrastructure. 
No python/bash scripting Matillion empowers developers to use Python3 and Jython to build any custom functionality on top of Matillion. 
Fig 2: Matillion ELT tool replaces SSIS/python code to move data efficiently

In conclusion, this combination of Matillion and Snowflake helped Ad Council meet its objectives of a future-proof data stack with minimal administrative overheads, all with-in a predictable budget backed by credit-based consumption model.

This helped the analytics team focus on generating insights, provisioning newer data sources and helping run campaigns successfully and efficiently. The right data in the right hands at the right time leads to better outcomes, in the case of Ad Council – a better world.

author image
About the Author
Lokesh Bansal is a data platform modernization expert with more than 14 years of diverse consulting experience. He has worked in various capacities on engagements in data warehousing, data visualization, metadata management, cloud migrations, platform rearchitecting & refactoring.
Lokesh Bansal | Solutions Principal | USEReady
author image
About the Author
Anshuman is a senior Data Engineer with extensive experience in design, development of complex data pipelines. Anshuman has worked on multiple projects leveraging modern data stacks to solve business problems efficiently. With a keen interest in the emerging data tech space, he likes to explore new tools in this space.
Anshuman Gautam | Senior Data Engineer - Cloud Data Factory | USEReady
Back to top