Macedon Connected System - Snowflake

This connected system plug-in lets you connect to a Snowflake data warehouse by creating a Connected System with credentials and then make different queries broken out into different integration objects.

The connected system supports specifying a database, warehouse, schema, and role (all import customizable), as well as account and credentials.

Using integration objects, a designer is able to create integrations that can make Select, Insert, Update or Delete queries, as well as call a Function or Stored Procedure. Filters and Paging Configurations can be configured on the inputs of the Select operation. The results from the queries executed by the integration objects return in a dictionary format, allowing easy parsing the returned result sets.

All of the queries taking potentially unvalidated inputs are called using prepared statements, mitigating the risk of SQL injection.

• Perform 6 different types of SQL statements against a Snowflake instance: SELECT, INSERT, UPDATE, DELETE, CALL FUNCTION, and EXECUTE STORED PROCEDURE.
• Results from a query are returned as a list of Appian Dictionary type, which can be used for simple translation to Appian CDTs.
• For SELECT statements, a designer has the ability to pass in expressionable lists of a!queryColumn(), a!queryFilter(), sorting info and paging info in order to tweak the results from a query to what the designer would like to see returned from tables or views. This functionality was built to imitate the behavior of a!queryEntity(), which should make the integration easy to use and maintain for Appian designers.
• Other operations (INSERT, UPDATE, DELETE) function similarly to Appian's built-in data store interaction implementation.
• The CALL FUNCTION and EXECUTE STORED PROCEDURE operators support expressionable values for named parameters.

Fixes an issue with the UPDATE integration where columns not explicitly passed in can be nulled-out rather than left unchanged.