DATA ARCHITECTURE RECOMMENDATIONS (MIGRATION TO SNOWFLAKE)

Organization

Our client is a SaaS Products and Services provider headquartered in Austin, TX. They build and sell 30+ products in the areas of Marketing, IT, HR, Legal, Contact Center, and Business Operations. They serve clients in North America and EMEA.

Challenge

Our client uses SQL Server as a key-value data store to manage Customer and Transactional data ingested from clients and for an ID resolution pipeline, including data standardization, deduplication, and match-merge. This data is transformed and ingested nightly to a home-grown ADS (Analytical Data Store) for self-service customer analysis and campaign management.

The current data pipeline involves multiple transformations in preparation for analysis. ADS is updated nightly with updates from the previous day. The Analytical Data Store cannot store more than four billion rows in a table and cannot scale horizontally. The client sought to alleviate some of these limitations:

• Make the data available to marketers for analysis in near-real-time (with an acceptable fifteen-minute latency)
• The data analytics platform needs to be horizontally scalable, accepting billions of rows per table, enabling the product to be marketable to clients with large volumes of data (> 4 billion rows per table and billions of transactions)
• The target platform should seamlessly integrate with the current .NET user interface, which allows the end-users to build self-service analysis, including building virtual columns, attribute banding, Venn diagram creation, etc.

TECHNICAL Solution

XTIVIA analyzed the current-state SQL Server store, ETL flow into the ADS, self-service analysis features, and the possible SQL-like proprietary functions available to the end-users. Among many architecture options and target data platforms available, XTIVIA recommended utilizing Snowflake as a single data platform for both Customer Data Storage and Self-Service Customer/Marketing Analysis. XTIVIA converted a sample of the client’s SQL-like proprietary functions to Snowflake, demonstrating its performance capabilities.

XTIVIA also demonstrated near-real-time incremental data load on the Snowflake platform. To make the transition to Snowflake a seamless experience for the end-users, we recommended mapping the current-state SQL-like functions on the .NET side to Snowflake SQL for dynamically creating and running the Snowflake SQL. XTIVIA also demonstrated how Snowflake uses results cache for performance.

XTIVIA’s recommended roadmap included:

• Phase 1: Keep the current SQL Server key-value database as-is and replace the proprietary ADS data store with Snowflake. This involves implementing an incremental data load from SQL Server into Snowflake covering the Insert, Update, Delete, Alter Table (Add Column), Alter Table (Drop Column), and Alter Table (Data Type Change) scenarios. From the user interface standpoint, this phase involves converting the .NET code to integrate with Snowflake (instead of ADS) and creating and executing Snowflake queries supporting the application’s current features.

• Phase 2: Move the SQL Server database (Customer Data Storage and ID Resolution platform) to Snowflake, convert all data loads from originating source to near real-time, and migrate the incremental loads developed in Phase 1 to near real-time loads. The SQL Server to Snowflake migration involves migrating DDLs, Data, and code (procedures and views). The user interface related to the Customer Data Platform must also be integrated with the Snowflake platform. Finally, the entire pipeline and user interface must be tested end-to-end with product-like data before decommissioning. 

The above solution would consolidate the current-state data platforms (SQL Server and proprietary ADS) to Snowflake, alleviating the current challenges (availability and scalability).

BUSINESS RESULT

As a result of XTIVIA’s recommendations, the client is well educated on the available target platform choices, Snowflake’s capabilities, future-state design, and roadmap to plan for the implementation. The roadmap provided the basis for cost-benefit analysis, implementation planning, budgeting, staffing, and risk management.

XTIVIA needs the contact information you provide to us to contact you about our products and services. You may unsubscribe from these communications at anytime, read our Privacy Policy here.