SQL differences between Snowflake and SQL Server

SQL Server vs. Snowflake

In a local development environment, transformations are run on SQL Server, while Snowflake is used in Process Mining Automation Suite. Although most SQL statements will work both on SQL Server and Snowflake, there can be slight differences in syntax, which may lead to different return results.

To write SQL statements that work on both database systems:

• Write field names in double quotes, e.g. Table."Field".
• Prevent using SQL functions that are different in Snowflake and SQL Server, e.g. string_agg() and listagg().
  The pm_utils package comes with a set of functions that work on both database types, see Multiple databases. For example, instead of using string_agg() or listagg(), the pm_utils.string_agg() will result in the same behavior for both databases. If pm_utils does not contain the desired function, then a Jinja statement should be created to make sure the right function is called on each database.

String concatenation

To combine to strings, use the pm_utils.concat() function. This will yield the same results for both SQL Server and Snowflake.

Example: pm_utils.concat("This is a nice string", null) = "This is a nice string" Concatenating strings should not be done with operators like + or ||, as they are different for both databases (Snowflake uses || and SQL Server uses +). Also the standard concat() function has different behavior on both systems:

SQL Server	Snowflake
null values will be ignored and treated as an empty string.	null values will cause the entire result to be null.

Sorting

Sorting is handled differently in Snowflake and SQL server.

Example: ... order by "Attribute_1" desc, "Attribute_2" ...

Null values

SQL Server	Snowflake
null will default be sorted first (ascending)	null will default be sorted last (ascending)

Handling capital letters

SQL Server	Snowflake
capitals are sorted as expected (AaBbCc)	first sorts by capitals, then by non-capitals (ABCabc)

Dashes

Example: -Accountant-

SQL Server	Snowflake
dashes are ignored in sorting (so '-Accountant-' is treated same as 'Accountant')	dashes will be sorted at the top

Whitespace handling

When you group by values “A“ and “ A“, this is seen as one value in SQL Server, but as two different values in Snowflake. Therefore trimming is advised if your data may cause this issue.

Case sensitivity

By default, SQL Server is case insensitive whereas Snowflake is case sensitive. This means that Table."Field" = "Some_value" and Table."Field" = "SOME_VALUE" will return the same result set in SQL Server, but potentially two different result sets in Snowflake.

You are advised to change the behavior of your local SQL Server database to match Snowflakes behavior, to prevent any problems. This can be accomplished by setting the database collation to a case sensitive value.