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 the EMEA region.

Challenge

Our client utilizes SQL Server as a key-value data store to manage Customer and Transactional data ingested from clients, as well as for an ID resolution pipeline that includes 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 conducted a comprehensive analysis of the client’s current-state SQL Server data storage, ETL processes into the ADS, self-service analysis features, and the SQL-like proprietary functions available to end-users. After evaluating multiple architectural options and target data platforms, XTIVIA recommended Snowflake as a unified data platform for both customer data storage and self-service customer/marketing analysis.

Key Initiatives Undertaken by XTIVIA:

• Platform Recommendation: Proposed Snowflake as the single platform to address both data storage and advanced analytical needs, offering scalability and high performance.
• Function Conversion Demonstration: Converted a sample of the client’s SQL-like proprietary functions to Snowflake SQL to showcase the platform’s capabilities.
• Near-Real-Time Data Loading: Demonstrated incremental data loads on the Snowflake platform with near-real-time processing.
• Seamless Transition Strategy: Recommended mapping current SQL-like functions in the .NET application to dynamically generate and execute Snowflake SQL, ensuring a smooth transition for end-users.
• Performance Optimization: Highlighted Snowflake’s use of results caching to improve query performance and deliver faster analytics.

This approach ensured a seamless migration and demonstrated Snowflake’s effectiveness in meeting the client’s data and analytics requirements.

XTIVIA’s recommended roadmap included:

• Phase 1: Maintain 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 following scenarios: Insert, Update, Delete, Alter Table (Add Column), Alter Table (Drop Column), and Alter Table (Data Type Change). From a user interface standpoint, this phase involves converting the .NET code to integrate with Snowflake (instead of ADS) and creating and executing Snowflake queries that support 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 the 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.

KEYWORDS
Data Architecture, Data Platform Consolidation, Snowflake

SOFTWARE
SQL Server, AWS, Snowflake

HARDWARE
AWS, .NET, Windows

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.