SQL Server to Snowflake Migration – Conversions with Examples

SQL Server to Snowflake Migration – Conversions with Examples

Readers’ Digest:

During one of my project experiences, I have had an opportunity to work in MS SQL where I gained valuable knowledge working with the stored procedures. A stored procedure is a prepared SQL code that you can save, so the code can be reused repeatedly. I learned how to optimize database operations by encapsulating complex SQL logic into reusable procedures, enhancing performance and maintainability. Additionally, I have improved my skills in error handling and debugging, ensuring the reliability of critical database tasks.

This experience highlighted the importance of well-structured and documented stored procedures for efficient database management and led me to write this Blog and share my knowledge with people who are interested to know more about these.

Overall, it was a valuable learning journey that greatly contributed to my proficiency in database development.

Introduction

The SQL Server to Snowflake migration concerns transferring a database from Microsoft SQL Server to Snowflake, a cloud-based data warehousing platform. This process requires converting SQL Server-specific syntax and features to their Snowflake equivalents.

For instance, SQL Server’s T-SQL queries might need to be adjusted to Snowflake’s SQL dialect, and functions like GETDATE() might be replaced with CURRENT_TIMESTAMP() in Snowflake.

Below are some of the Functions which were used more frequently during the Conversion:

Merge

Below are the differences between SQL Server and Snowflake Merge statements.

SQL Server:

In SQL Server, there are three options available in the MERGE command, as per the below screenshot. For old records that need an update, we can update the records using the “WHEN MATCHED” clause. For new records, we can insert them into the target table using the “WHEN NOT MATCHED BY TARGET” clause. Most importantly, for records that are available in the target but not in the source, we can choose to either update them as invalid or delete the record from the target using the “WHEN NOT MATCHED BY SOURCE” clause.

MERGE stg.dimShipping as target

USING tgt.ShippingCodes as source

ON target.shippingCode = source.ShippingCode

WHEN MATCHED AND target.ShippingPrice < source.ShippingPrice THEN

UPDATE SET

shippingDescription = source.ShippingCodeDesc,

ShippingPrice = source.ShippingPrice

WHEN NOT MATCHED by Target THEN

INSERT (shippingCode, shippingDescription, ShippingPrice)

VALUES (source.ShippingCode, source.ShippingCodeDesc, source.ShippingPrice)

WHEN NOT MATCHED by source THEN DELETE;

Snowflake:

In Snowflake, there are three options available in the MERGE command, as per the below screenshot. For old records that need an update, we can update the records using the “WHEN MATCHED” clause. For new records, we can insert them into the target table using the “WHEN NOT MATCHED” clause. And for the records that must be deleted, we can use the same “WHEN MATCHED” clause along with the delete statement.

And for the records that are available in the target but not in the source, we can choose to update (update for invalid record) them prior to the merge statement, as there is no option to update the statement along with the “WHEN NOT MATCHED” clause.

MERGE INTO stg.dimShipping as target

USING tgt.ShippingCodes as source

ON target.shippingCode = source.ShippingCode

WHEN MATCHED AND target.ShippingPrice < source.ShippingPrice THEN

UPDATE SET

shippingDescription = source.ShippingCodeDesc,

ShippingPrice = source.ShippingPrice

WHEN NOT MATCHED THEN

INSERT (shippingCode, shippingDescription, ShippingPrice)

VALUES (source.ShippingCode, source.ShippingCodeDesc, source.ShippingPrice)

WHEN  MATCHED THEN DELETE;

ISNULL

In SQL Server, the ISNULL function is commonly used to replace a NULL value with a specified alternative value. In Snowflake, the equivalent function is IFNULL. Let’s consider an example to illustrate the mapping:

 

In this example, if column1 is NULL, it will be replaced with the string ‘N/A’ in both SQL Server and Snowflake.

ISDATE([date])>0

SQL Server’s ISDATE function is used to check if a value is a valid date. In Snowflake, you can achieve the same functionality using the TRY_TO_DATE function. Let’s look at an example:

 

In this example, TRY_TO_DATE in Snowflake will return a non-null value if date_column is a valid date, otherwise, it will return NULL.

CAST

Both SQL Server and Snowflake support the CAST function to convert data types. However, it’s important to note that the syntax and available options may vary. Let’s consider an example:

In this example, CAST is used to convert the data type of column1 to an integer.

IIF

SQL Server’s IIF function allows for inline conditional expressions. In Snowflake, you can use the IFF function to achieve the same functionality. Let’s see an example:

In this example, IFF in Snowflake will return ‘Greater’ if column1 is greater than 10, otherwise, it will return ‘Smaller or Equal’.

SYSDATETIMEOFFSET()

SQL Server’s SYSDATETIMEOFFSET() function returns the current date and time, including the time zone offset. In Snowflake, the equivalent function is CURRENT_TIMESTAMP(). Let’s see an example:

In this example, both SYSDATETIMEOFFSET() in SQL Server and CURRENT_TIMESTAMP() in Snowflake will return the current date and time.

SYSTEM_USER

In SQL Server, the SYSTEM_USER function returns the login name of the current user. In Snowflake, you can achieve the same result using the CURRENT_USER function. Here’s an example:

 

STUFF(REVERSE(@cols),1,1,”)

In this function, the SQL Server is used to delete a specified length of characters from a string and then insert another string at a specified starting position. In Snowflake, you can achieve a similar result using the INSERT function with a reversed string. Let’s look at an example:

IsNumeric

This function in SQL Server is used to check if a value can be converted to a numeric data type. In Snowflake, you can achieve a similar result using the TRY_TO_NUMERIC function. Here’s an example:

body nvarchar(max) = ‘Export Aging Report. See attached CSV file.’ + char(10)

In SQL Server, this code assigns a string value to the body variable. In Snowflake, you can achieve the same result using JavaScript within a Snowflake stored procedure. Here’s an example:

YYYYMMDD nvarchar(8) = CONVERT(char(8), GETDATE(), 112)

In SQL Server, this code assigns the current date in the YYYYMMDD format to the nvarchar variable. In Snowflake, you can achieve the same result using the TO_CHAR function to format the current date. Here’s an example:

SET @subject = N’ATLAMEDb01: [Export Claims DRCV Rpt to CSV] – Weekly Aging Claims for ‘ + @Division + ‘ run on ‘ + @YYYYMMDD**

In SQL Server, this code assigns a string value to the @subject variable. In Snowflake, you can achieve the same result by concatenating the string values using the || operator. Here’s an example:

 

YEAR([Day])*10000 + MONTH([Day])*100 + DAY([Day])**

To concatenate the year, month, and day values from a date in SQL Server, you can use arithmetic operations. In Snowflake, you can achieve the same result by casting the date to a TIMESTAMP data type and applying similar arithmetic operations. Here’s an example:

 

ISDATE([Day]) > 0

SQL Server’s ISDATE function is used to check if a value is a valid date. In Snowflake, you can achieve a similar result using the TRY_TO_DATE function. Here’s an example:

ISDATE(stg_info.[day_id])>0

Similar to the previous example, you can use the IS_DATE function in Snowflake to check if a value is a valid date. Here’s an example:

 

 

YEAR([date])*10000 + MONTH([date])*100 + DAY([date])

To concatenate the year, month, and day values from a date in SQL Server, you can use arithmetic operations. In Snowflake, you can achieve the same result by casting the date to a TIMESTAMP data type and applying similar arithmetic operations. Here’s an example:

SUBSTRING([campaign], CHARINDEX(‘(‘,[campaign])+1, LEN([campaign]))

In SQL Server, the SUBSTRING function is used to extract a substring from a string based on a starting position and a length. In Snowflake, you can achieve the same result using the SUBSTRING function. Here’s an example:

 

 

YEAR(fact_ppr.day_campaign)*10000 + MONTH(fact_ppr.day_campaign)*100 + DAY(fact_ppr.day_campaign)

To concatenate the year, month, and day values from a date in SQL Server, you can use arithmetic operations. In Snowflake, you can achieve the same result by casting the date to a TIMESTAMP data type and applying similar arithmetic operations. Here’s an example:

 

DATETIME

In SQL Server, the DATETIME datatype is used to represent both date and time values. In Snowflake, the equivalent datatype is TIMESTAMP, which also represents both date and time values. Here’s an example:

 

CAST(concat(year(drop_date), format(month(drop_date), ’00’), format(day(drop_date), ’00’)) AS int)

In SQL Server, this code concatenates the year, month, and day values from a date and then casts the result to an integer. In Snowflake, you can achieve the same result by using the TO_CHAR function to format the date and then casting it to an integer. Here’s an example:

 

CONVERT(VARCHAR,CAST(posting_date AS DATE),112)

In SQL Server, this code converts a date to a specific format (YYYYMMDD) by casting it to a DATE datatype and then converting it to a VARCHAR datatype. In Snowflake, you can achieve the same result by using the TO_CHAR function with a format specifier. Here’s an example:

 

REVERSE(STUFF(REVERSE(@cols),1,1,”))

In SQL Server, this code is used to manipulate strings by reversing the string, deleting the first character, and then reversing it again. In Snowflake, you can achieve the same result using the REVERSE and INSERT functions. Here’s an example:

 

@cols + ‘[‘ + ISNULL(CAST(COLUMN_NAME AS VARCHAR(100)),”) + ‘],’

In SQL Server, this code concatenates the @cols variable with a string that includes the COLUMN_NAME value wrapped in square brackets. If COLUMN_NAME is NULL, an empty string is used. In Snowflake, you can achieve the same result using the || operator for string concatenation. Here’s an example:

 

CONVERT(VARCHAR, EOMONTH(CAST(”01-”+MonthYear AS DATE)),112) AS day_id,’+@OrderNum+

In SQL Server, this code converts a string representation of a date ('01-''+MonthYear) to a date datatype, retrieves the last day of the month (EOMONTH), and then converts it to a specific format (112). In Snowflake, you can achieve the same result using the TO_CHAR function with appropriate date functions. Here’s an example:

 

‘+CAST(@loadcontrolid AS VARCHAR(1000))+’

In SQL Server, this code casts the @loadcontrolid variable to a VARCHAR datatype. In Snowflake, you can achieve the same result by using the TO_VARCHAR function. Here’s an example:

 

CAST(CONVERT(VARCHAR, EOMONTH(CONVERT(datetime, (CONVERT(CHAR(10), day_id, 120)))), 112) AS INT)

In SQL Server, this code converts a day_id value to a specific date format, retrieves the last day of the month using the EOMONTH function, and then converts it to an INT datatype. In Snowflake, you can achieve the same result using the TO_CHAR function with appropriate date functions and casting to an INT. Here’s an example:

 

GETDATE()

In SQL Server, the GETDATE() function returns the current date and time. In Snowflake, you can achieve the same result using the CURRENT_TIMESTAMP() function. Here’s an example:

 

(CONVERT(VARCHAR,CONVERT(DATETIME, CONVERT(CHAR(10), a.day_id, 120)),112) AS INT)

In SQL Server, this code converts a day_id value to a specific date format, then converts it to a DATETIME datatype, and finally converts it to an INT datatype. In Snowflake, you can achieve the same result using the TO_CHAR function with appropriate date functions and casting to an INT. Here’s an example:

 

datetimeoffset

In SQL Server, the datetimeoffset datatype is used to store a date and time value with a time zone offset. In Snowflake, the equivalent datatype is TIMESTAMP_NTZ, which represents a timestamp in the current session’s time zone. Here’s an example:

 

nvarchar

In SQL Server, the nvarchar datatype is used to store Unicode character data. In Snowflake, the equivalent datatype is VARCHAR, which also supports Unicode character data. Here’s an example:

 

db_name()

In SQL Server, the db_name() function returns the name of the current database. In Snowflake, you can achieve the same result using the CURRENT_DATABASE() function. Here’s an example:

GETDATE

In SQL Server, GETDATE is a system function used to retrieve the current date and time. In Snowflake, you can achieve the same result using the CURRENT_TIMESTAMP function. Here’s an example:

 

SUSER_NAME

In SQL Server, the SUSER_NAME function returns the name of the current user. In Snowflake, you can achieve the same result using the CURRENT_USER function. Here’s an example:

 

Conclusion

In conclusion, the migration journey from SQL Server to Snowflake constitutes a significant step in modernizing data management strategies. By seamlessly transferring databases to the cloud-based Snowflake platform, organizations can harness enhanced scalability, flexibility, and analytical capabilities. However, this transition necessitates meticulous attention to detail, particularly in the realm of syntax and functionality conversions. Adapting SQL Server-specific elements to align with Snowflake’s SQL dialect, as demonstrated through examples such as query adjustments and function substitutions, underscores the importance of precision in ensuring a seamless and optimized migration process.

Please note that this blog post provides a general guide, and additional considerations may be required depending on your specific migration scenario. Consult Snowflake’s documentation for comprehensive information on datatypes and their usage.

We hope that this blog post has provided you with valuable insights into SQL Server to Snowflake migration. Happy migrating!