By: Ramesh Vanteru, Principal & Head of SAS COE

Modern-day organizations that generate a huge amount of data look forward to leveraging the full potential of their data using modern technology. Data modernization is a top priority for these organizations, and cloud-based platforms like Snowflake are leading the charge.

Most organizations use statistical analysis software (SAS) as a platform for their data management and analytics needs. SAS is widely recognized for its versatility and compatibility across diverse cloud platforms. Offering specialized solutions for various domains such as Analytics, Banking and Finance, Healthcare, Retail, Government, Manufacturing.

Data modernization: Migration vs. Modernization

Let us understand the difference between migration and modernization before delving into PolarSled. Migration involves a lift-and-shift approach to moving data and applications to the cloud. On the other hand, modernization goes beyond redesigning and optimizing workloads for the target platform. Both approaches have their merits, however, modernization brings better ROI and long-term benefits.

The rise of Snowflake

Snowflake emerges as a cloud-based data platform revolutionizing data storage, processing, and analytics. Offered as a managed service, Snowflake’s innovative architecture is purposefully built for the cloud, combining a new SQL query engine with advanced capabilities:

1. Independent scaling of storage and computing, enabling cost-efficient usage
2. Seamless scalability for concurrent workloads facilitated by separate compute clusters
3. Support for both structured and semi-structured data
4. Built-in high availability, data protection, and retention, ensuring data integrity and security

Snowflake has emerged as a leading cloud-based data platform, offering unparalleled flexibility, scalability, and performance. Its unique architecture allows for independent scaling of storage and computing, seamless concurrency, and support for structured and semi-structured data. Snowflake’s built-in data protection features make it an ideal choice for organizations looking to modernize their data infrastructure.

Why modernize from SAS to Snowflake

We know SAS as a versatile and robust data management and analytics platform. However, it demands specialized skills and complex architecture management and incurs high licensing and support costs. Snowflake addresses the challenges by simplifying complexities and bringing substantial savings. Here are some reasons why SAS to Snowflake migration may be the best move for your organization:

• Cost-effective: Snowflake’s pay-as-you-go pricing model allows organizations to pay for the resources they use, eliminating costly investments in hardware and software licenses. The platform’s automatic scaling and resource optimization features help minimize unnecessary costs, ensuring optimal cost efficiency.
• Rapid implementation and time-to-value: Snowflake’s innovative architecture includes separate compute and storage layers, which allows distributed processing capabilities. Queries are processed in parallel, resulting in faster data ingestion, analysis, and reporting. This improved performance translates to faster insights and more efficient decision-making for organizations.
• Scalability: The cloud-native architecture offers scalability to meet organizations’ evolving data needs. It provides on-demand resources to accommodate the changing data and analytical needs, eliminating the need for hardware investments.
• Ease of integration: Snowflake’s intuitive user interface, SQL-based querying language, and compatibility with popular BI and analytics tools make SAS to Snowflake transitions easy. The platform allows organizations to leverage their existing skills and knowledge with minimal training and retooling.
• Security: Snowflake offers robust encryption, access controls, and auditing capabilities for organizations to ensure data confidentiality, integrity, and availability and maintain regulatory compliance and industry standards.
• Flexibility: The platform supports ingesting and analyzing a wide range of data types and formats, including structured, semi-structured, and unstructured data ingested from sources such as IoT data, social media feeds, and log files.

Challenges of SAS workload migration

The SAS to Snowflake migration process presents several challenges. Its diverse use cases, compatibility with various platforms, and complex business logic make the migration process complex and time-consuming. Moreover, SAS workloads often rely on proprietary functions and procedures, complicating the transition to a new platform. Some of the challenges in this migration are:

Figure 1: Challenges in SAS workload migration

Introducing PolarSled

LTIMindtree’s PolarSled is a unique solution designed to streamline the migration of SAS workloads to Snowflake. We leverage a hybrid approach, combining the power of Snowflake stored procedures for optimal performance and efficiency. This unique approach allows organizations to retain familiarity with SQL while harnessing the capabilities of Python for complex data engineering tasks.

How PolarSled works

Our tool scans the input SAS programs to identify individual statements or procedure blocks and converts them into equivalent SQL/Snowpark statements on Snowflake. Each statement and procedure block are recognized as a candidate query to be converted into equivalent Snowflake SQL queries for the stored procedure or Snowpark queries based on customers requirement

The infographic below demonstrates how the solution works and what its components are.

Figure. 2: PolarSled—SAS to Snowflake Conversion Tool Workflow

PolarSled uses a framework comprising the following components:

1. Preprocessing module

The module scans and parses SAS programs, addressing challenges like the absence or irregular placement of quit/run statements and variations in macro usage. It sanitizes the input programs, creating a consistent format, enhancing the tool’s ability to identify code blocks accurately and minimizing exceptions during conversion.

2. Conversion engine

This tool’s core module converts SAS code into equivalent Snowflake SQL or Snowpark statements based on customers reequipment. It processes each file in the cleansed programs directory one by one and identifies candidate code blocks for conversion. After checking against the pattern repository, supported patterns undergo conversion engine search for conversion. Converted code blocks are then printed to the output file, with exceptions logged for analysis. The engine relies on three core modules internally.

3. Mapping dictionary

The Mapping Dictionary maintains a catalog of Snowflake equivalent keywords—SQL functions and statements for the keywords found in SAS programs, which are classified into two categories:

• SAS keywords and functions
• SQL keywords and functions within the Proc SQL statements for the database accessed or referenced by the SAS programs (Oracle, Teradata, etc.)

4. Function Library

This catalog is like the mapping dictionary but addresses scenarios requiring more than a simple keyword search replacement. The library includes Python code for generating the Snowflake equivalent SQL code/Snowpark block for the SAS functions.

5. Pattern repository

This catalog records SAS patterns compatible with the tool’s conversion process. While the aim is maximum conversion to Snowflake Stored Procedures/Snowpark, technical constraints necessitate identifying limitations. Additionally, the repository enables the engine to skip unsupported SAS code blocks, logging them for manual conversion by developers in the output file.

6. Logging and audit modules

The logging and audit module creates process and exception logs during conversion. It generates a console log detailing exceptions per file, with line numbers and types. After conversion, it produces two files: a list of unconverted queries and a results file summarizing each input file’s conversion, including file name, lines of code, candidate queries, converted queries, conversion percentage, and total conversion time.

Key features of PolarSled

We built PolarSled to simplify the Snowflake Data Cloud migration with a “mindful automation-first approach.” Some key features of our solution include:

1. Snowflake scripting: PolarSled utilizes Snowflake’s native scripting capabilities for efficient data processing.
2. Ease of use: The solution minimizes the learning curve by leveraging SQL, a language familiar to most developers and analysts.
3. Faster migration: It was built to accelerate the time taken to migrate to Snowflake from years to months. PolarSled saves up to 60% of time and costs and up to 70% person-hours in the migration process.
4. Comprehensive conversion tool: PolarSled has a robust conversion tool that automates the conversion of SAS programs to Snowflake stored procedures and Snowpark reducing manual effort and ensuring accuracy.

Conclusion

In conclusion, PolarSled represents a game-changing solution for organizations considering SAS to Snowflake migration. The solution was designed to simplify migration to Snowflake Data Cloud by addressing the core challenges of designing, accelerating, and governing the journey of data transformation. The solution empowers organizations to unlock new insights, drive innovation, and stay ahead in today’s competitive landscape.