Enabling Advanced Security Measures Through a Modern Data Stack – VGM

Before:

A Need for Secure Data Sharing

VGM needed to securely share its SQL server data containing shipping dates, requested ship dates, invoice numbers, quantities ordered, quantities processed, order status, shipping status, and tracking numbers with customers while maintaining control of granular access rights. In addition, the solution needed to integrate with their existing Azure infrastructure and accommodate growing volumes of data.

Unfortunately, because of how the existing data-sharing framework was set up, data could not be shared with one customer without giving that customer access to other customers’ data.

VGM handled this challenge through manual processes that took longer and introduced errors.

VGM needed a change, and they turned to Spaulding Ridge for a solution.

Solution:

An Adaptable Data Stack with Advanced Security Capabilities

We built a data stack that moved data from VGM’s SQL server to Azure and then into Snowflake. Then in Snowflake, we established a “Data Clean Room”—a secure, closed environment that allows multiple parties to collaborate and share data in a privacy-safe manner. VGM could now share and match first-party data with customer data to derive the insights they needed while protecting customer privacy.

We built a UTILS table to hold all IDs associated with a specific parent account in the customer table, which linked those IDs to the respective customer’s Snowflake account locator values. In the dynamic table, we pull in all data where customers have Snowflake accounts (using the UTILS table). If the account exists, the data is loaded into the table. In the secure view shared with other Snowflake accounts, a common table expression will match the account locator values from the UTILS table with the built-in Snowflake function, returning the locator value. The view will only select parent accounts on the same row as the account locator, which will then be used for the rest of the view, allowing customers to be pulled in. Through this, customers can only see their respective IDs, protecting their privacy and data.