The cloud-based Software as a Service (SaaS) model promises to relieve users of the services needed to set up and maintain technology infrastructure. It also creates a technological opportunity – since the companies offering these services run large fleets of servers, they can also provide extreme scalability.
Snowflake is a SaaS solution offering cloud-based data warehousing. A core advantage is that it supports the same SQL based relational model of data storage that businesses have been using now for decades. For organizations interested in achieving Digital Transformation, connecting Appian and Snowflake can provide a significant opportunity for long term scalability.
Appian makes it very easy to connect to many traditional relational database management systems. We set up a Data Store, which holds the necessary connection information (by way of its data source). Once we have that, we can simply retrieve data from our database using queryEntity. Likewise, we can write to our database using the Write to Datastore node. Appian developers know that this makes interacting with the database dynamically quite simple – but it also offers another critical security benefit. Appian turns all of our queries into Prepared Statements, which means developers don’t need to worry about SQL injection. Roughly speaking, Appian tells the database the structure of the query it will perform and then plugs in our query’s explicit values. This prevents a malicious user from sneaking SQL commands into user data, and it’s why we don’t have to write complicated validation rules checking all user input for SQL statements.
We built the Macedon Snowflake for Appian plugin to achieve the two critical goals Data Stores provide for the traditional relational database management systems – ease of querying and security through the use of Prepared Statements.
How it works
Making use of the Connected System Plugin functionality Appian released in 2019, the plugin works just like an integration. Designers enter the necessary connection information for a Snowflake database, analogous to creating a new data source in the admin console. Test the connection to verify the account connection details are correct. When migrating to a different Appian environment, these values will be importable via a simple properties file. This makes it easy to connect to different databases, schemas, and warehouses without recreating the queries.
Using the connected system to create an integration gives a list of query types – SELECT, INSERT, UPDATE, DELETE, CALL FUNCTION, EXECUTE STORED PROCEDURE (Read-Only), and EXECUTE STORED PROCEDURE (Modifies data).
Benefits
The plugin has several features to make it easy to create queries (and hard to create invalid queries) and do so in a way familiar to Appian designers.
For example, in the SELECT operation, the designer passes a list of columns, filters, and paging configurations using the same out of box structures designers use for native supported databases. This ensures that designers can immediately deliver value with no additional training with the new database.
Passing data to Snowflake and parsing results sets is easy as well – in the INSERT and UPDATE operations, you can pass a list of CDTs or dictionaries matching column names in the tables to be updated.
When executing a SELECT operation, results are returned as a dictionary, making it easy to work with the result set.
For stored procedures and functions, an explicit list of named parameters is given to the designer:
This means that designers do not have to write complicated rules to build SQL statements and parse the result sets they return. The plugin achieves this by querying Information Schema so that it knows the columns in a table and the parameters to functions and stored procedures. This same information is what allows us to use Prepared Statements to execute the queries.
Envoi
Are you using or planning on migrating to Snowflake for a long term scalable data warehousing solution? Macedon’s Snowflake for Appian plugin offers the best solution for easily and securely querying your warehouse. If you are interested in seeing the component for yourself, don’t hesitate to reach out to Macedon Sales at sales@macedontechnologies.com, and we will be proud to demonstrate its capabilities.
Want more insights from TechNest? Stay in the loop and subscribe to our magazine.
