Result
Global color management and hardware manufacturer streamlines data across retailer POS systems with two-way, on-demand data sync
A global color management and hardware manufacturer was looking for a solution to several issues with their current data platform.
Overview
Our client wanted to:
- Gather point-of-sale data from individual retailer workstations.
- Distribute new manufacturer data to these stations so retailers have the most up-to-date manufacturer data.
Their retailers had point-of-sale applications which ran on local SQL Server databases. These machines might be stand-alone computers in small mom-and-pop shops or part of a network in big-box hardware stores.
Connection to the internet on these machines was intermittent, meaning that sale data was not regularly collected and the product data from their database became stale.
Because of intermittent internet connectivity, retailer database versions could lag behind the current version, so the solution would need to be backward compatible with multiple database versions. Data transfers would need to meet varying security requirements with minimal configuration of firewalls and ports by their end customers.
Our solution
OneSix presented a modern data platform Proof of Concept to the customer Leadership and Development teams. We also advised on the technology and architecture selection process to satisfy the immediate needs of the customer with the scalability and adaptability for any future state data sources.
We worked hands-on in partnership with their database administrator team to develop data pipelines and configure their new Snowflake Modern Data Platform, including:
Backward Compatible Pipelines – Matillion ETL’s features including grid variables, iterative components, and Python components were utilized to build modular pipelines which executed dynamically based on the source’s unique meta-data, iterating through databases & tables, and avoiding the need to build table-specific transformations.
On-Demand Data Refresh – Matillion ETL’s easy-to-use API endpoints were used to pass customer-specific database configuration information and to dynamically trigger pipelines.
Read & Write Capabilities – Matillion ETL’s built-in SQL Server read and write capabilities were used for connecting to source systems.
Scalable Data Warehouse – Snowflake was utilized for a data warehouse because of its ability to scale up or down on the fly.
OneSix took advantage of many powerful cloud data platforms to create the appropriate best-of-breed solution for our client:
Snowflake
Snowflake was chosen as the data warehouse due to its favorable data storage, extremely fast performance, low query calculation costs and its industry leading scalability.
Fivetran
Matillion ETL was chosen as the ELT tool for its user-friendly drag-and-drop development interface, built-in SQL Server read/write compatibility, and its ability to build pipelines entirely based on table metadata.
Matillion
Azure was chosen as the cloud platform that hosts the virtual machines for the simulation client instances, VM for the Matillion instance, as well as the cloud data storage for Snowflake.
Results
With the solution architected by OneSix, the client successfully proved out the two-way transfer of data between systems using an on-demand data sync. The centralized data platform created the ability to see retailer data side-by-side with other retailers for the first time in the client’s history.
Using Matillion ETL’s unique iteration components, we developed entirely meta-data-driven pipelines to enable backward compatibility with source systems. We also utilized Matillion’s flexible API to allow the client’s customers to sync data on demand.
By developing a data platform using Snowflake, we provided a strong foundation that can be scaled to hundreds of users and dozens of projects with strong governance and minimal overhead.
To meet the security requirements for the client, the retailer’s sensitive data was secured using Azure Key Vault, Auth0 provided user identity and Azure Relay (SSL) was used to establish the connection between the client’s existing application and the data pipelines. Azure Relay also reduced the retailer requirement to internet connectivity only. No need to ask retailers to open additional ports on a firewall.
Discover how we’ve helped our customers build Modern Data Organizations.
Manufacturing
Food solutions manufacturer improves business insights with a cloud data architecture
Data Lakes + Warehouses
Manufacturing
Global electronics & automation corporation provides valuable proof of concept for data streaming
Data Lakes + Warehouses
Manufacturing
Manufacturer unites reporting processes under single Data Warehouse solution
Data Lakes + Warehouses
Transform your data org
Schedule a free consultation with the team.