Case Study:

1010data Migration With Excel Add-In for Snowflake

 

1010 Data Teaser
 
 

 

About Customer

The Client is a leading vitamin and supplement retailer with just under 800 stores across the United States. Additionally, the Client manages and distributes multiple top brands in its stores, including its own line of supplements.

 

About INTRICITY

INTRICITY is a team of specialized Data Management, Data Warehousing, and Business Intelligence experts. The team members at INTRICITY have been handpicked over the course of 20 years, and represent the top talent globally in data-oriented disciplines.

 

 

Challenge and Wins

 

Challenge

The Client had been using 1010data for several years and it had become part of the fabric for how their organization evaluated their retail analytics. However, 1010data was becoming very slow, and the user adoption was very difficult to scale due to its custom query language. The consequence is that any changes to the queries required the attention of a very small number of XML query experts to be able to work on it. Additionally, building new content was very difficult due to the small number of resources that understood the query mechanisms.

 

Navigating Constraints

Snowflake was an obvious fix to this challenge however, the interactions with the data within their team were excel based. This was something that 1010data did natively which spurred the Excel culture internally at the Client. However, Snowflake did not have a direct connector to Excel. This lack of Excel support was a show stopper for many of their users.

The data within 1010data often had data quality issues related to the formatting of the data on entry and a lack of gatekeeping by 1010data’s schemas. Additionally, the quantity of data within 1010data was very high but the ability to extract that data was very low. A brute force method would take over 2 weeks of continuous loading to completely extract. If the extraction process failed during the 2 week period, it would have to be restarted from the beginning.

 

Win 1: Single Location for All the Organization’s Data

Snowflake provided a single location where all the data assets could be stored with no impact on performance. Additionally, all the objects being stored had the lowest cost of storage possible. This gave The Client the confidence to target Snowflake as a single location across multiple departments for loading data.

 

Win 2: Simplified Queries via SQL

Having SQL available to the organization as a query language was liberating for The Client. The ability to spread the burden of writing queries to a much wider body of users liberated the Client to write a lot more content. Additionally, the broader access point provided the ability to support more analytic tools and processes.

 

Win 3: Rebuilding Queries from 1010data

INTRICITY began a factory conversion process of converting the old code stored in 1010data into native SQL which Snowflake could process. INTRICITY guided the Client team through this process enabling them to continue the effort internally.

 

Win 4: Acquiring Data

INTRICITY used the 1010data Tendo command-line utility combined with a chunking strategy to ensure restart ability of the data extraction process. This ensured that the data coming out of 1010data was stored in Snowflake and could be restarted if there were any failures during extract.

 

Win 5: INTRICITY Built Excel Add-In

As part of the commitment to convert The Client’s 1010data environment, INTRICITY built and supports an Excel Add-In which connects The Client’s Snowflake instance to Excel. INTRICITY architectured a secure web services framework that allows Excel to act as the canvas to send SQL commands along with the necessary controls to parameterize and control the query. The Client is the first customer to leverage the INTRICITY Excel Add-In for Snowflake, which will be formally launched at the 2020 Snowflake Summit for broader client adoption.

 

Call to Action

INTRICITY has created a repeatable framework for conducting these migrations which have a proven track record. This framework is used not just for Netezza migrations but for any source system to Snowflake. For a short description of how this framework is specifically used with Netezza see INTRICITY’s Netezza to Snowflake Migration slick.

 

 

 

 

TO CONTINUE READING REGISTER HERE

appears invalid. We can send you a quick validation email to confirm it's yours