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.
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.
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 was chosen as the data warehouse due to its favorable data storage, extremely fast performance, low query calculation costs and its industry leading scalability.
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.
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.
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.
My experience was fantastic with OneSix. They listen to your query carefully and then are able to solve your issues within the correct time frame and reasonable price. Super knowledgeable team!
One of my favorite things about One Six is their willingness to work with us on whatever we need. Early on in the project, we determined we needed an additional tactical resource instead of an advisory one, and One Six happily switched out resources for us, ensuring we had what we needed to complete the project.
They dig in and help understand the problem, how to think about it, provide the best technical options of how to solve it, then execute as quickly as possible. This is by far the best way to help me make sure we're successful, and that I know what's being done and how to support it once they're finished.
Discover how we’ve helped our customers build Modern Data Organizations.
Food solutions manufacturer improves business insights with a cloud data architecture
Data Lakes + Warehouses
Global electronics & automation corporation provides valuable proof of concept for data streaming
Data Lakes + Warehouses
Manufacturer unites reporting processes under single Data Warehouse solution
Data Lakes + Warehouses