- Release notes
- Before you begin
- Getting started
- Integrations
- Working with process apps
- Working with dashboards and charts
- Working with process graphs
- Working with Discover process models and Import BPMN models
- Showing or hiding the menu
- Context information
- Export
- Filters
- Sending automation ideas to UiPath® Automation Hub
- Tags
- Due dates
- Compare
- Conformance checking
- Root cause analysis
- Simulating automation potential
- Triggering an automation from a process app
- Viewing Process data
- Creating apps
- Loading data
- Customizing process apps
- Publishing Dashboards
- App templates
- Additional resources
- Out-of-the-box Tags and Due dates
- Editing data transformations in a local environment
- Setting up a local test environment
- Custom throughput time metrics
- SQL differences between Snowflake and SQL Server
- Configuration settings for loading input data
- Designing an event log
- Extending the SAP Ariba extraction tool
- Performance characteristics
SQL differences between Snowflake and SQL Server
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()andlistagg().Thepm_utilspackage comes with a set of functions that work on both database types, see Multiple databases. For example, instead of usingstring_agg()orlistagg(), thepm_utils.string_agg()will result in the same behavior for both databases. Ifpm_utilsdoes not contain the desired function, then a Jinja statement should be created to make sure the right function is called on each database.
pm_utils.concat() function. This will yield the same results for both SQL Server and Snowflake.
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 is handled differently in Snowflake and SQL server.
... order by "Attribute_1" desc, "Attribute_2" ...|
SQL Server |
Snowflake |
|---|---|
null will default be sorted first (ascending)
|
null will default be sorted last (ascending)
|
|
SQL Server |
Snowflake |
|---|---|
|
capitals are sorted as expected (AaBbCc) |
first sorts by capitals, then by non-capitals (ABCabc) |
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.
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.
