DATA LAKES AND WAREHOUSES – manufacturing

Global color management and hardware manufacturer streamlines data across retailer POS systems with two-way, on-demand data sync.

Overview

A global color management and hardware manufacturer was looking for a solution to several issues with their current data platform.

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 backwards 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 client wanted to:

  1. Gather point of sale data from individual retailer work stations.
  2. Distribute new manufacturer data to these stations so retailers have the most up to date manufacturer data.
Snowflake logo full color
Our Solution

One Six Solutions 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:.

  • Backwards 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.

One Six Solutions took advantage of many powerful cloud data architecture solutions to create the right arrangement for our client’s needs:

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.

The Results

With the solution architected by One Six Solutions, 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 backwards compatibility with source systems. We also utilized Matillion’s flexible API to allow the clients 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, 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 clients 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.