process-mining
latest
false
Process Mining
Automation CloudAutomation Cloud Public SectorAutomation SuiteStandalone
Last updated Oct 15, 2024

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 CloudTM Public Sector. 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.

  • SQL Server vs. Snowflake
  • String concatenation
  • Sorting
  • Null values
  • Handling capital letters
  • Dashes
  • Whitespace handling
  • Case sensitivity

Was this page helpful?

Get The Help You Need
Learning RPA - Automation Courses
UiPath Community Forum
Uipath Logo White
Trust and Security
© 2005-2024 UiPath. All rights reserved.