- Manufacturing
Streamlining data across retailer POS systems with two-way, on-demand data sync
Overview
Enabling reliable, real-time data sync for retailers with intermittent connectivity
Our client wanted to gather point-of-sale data from individual retailer workstations and 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
Developing on-demand data pipelines for seamless data updates
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.
Technologies Implemented
- 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.
Results
Centralized insights, scalable infrastructure, and secure connectivity
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.
Ready to unlock the full potential of data and AI?
Book a free consultation to learn how OneSix can help drive meaningful business outcomes.
