Execute Snowflake DDL & DCL Through Designer

[#1]

SNOWFLAKE SQL INPUT MACRO

The Snowflake SQL Input Macro is designed to allow you to execute a SQL query and output the results for use in an Alteryx workflow.  It was created due to limitations we found with the traditional input tool for running common Snowflake administrator queries, such as ‘SHOW USERS;’. This specific example also allows you to have real time information, in place of using the SNOWFLAKE.ACCOUNT_USAGE.USERS view, which has a latency of 2 hours. This macro allows us to enter any SQL statement and use the results in our Alteryx workflows.

1. This workflow requires the following python packages: snowflake-connector-python & pandas
   1. To install run ‘Package.installPackages(['snowflake-connector-python','pandas'])’ in your python tool

2. Enter the following information into the Configuration window of the macro:  Snowflake username, password, warehouse to use, database, schema, region your Snowflake account is located, role, account name, and the SQL statement you want to execute.

3. Once all this information is filled out, you can hit ‘Run’ and you will see the results returned to the Browse tool on the canvas.

 SNOWFLAKE SQL EXECUTE MACRO

The Snowflake Execute Macro is designed to execute a SQL query within snowflake and output the execution results. It was designed to kick off grant statements automatically within Snowflake after an access approval has been marked as approved. 

1. Enter the Snowflake Database, Schema and Role you’d like to use when executing.

2. We have a workflow that builds a SQL_STATEMENT that would execute in the Snowflake Connector Macro (Input = SQL). For example, John requests access to the database QUARANTINE_PRODUCTION and the schema SALESFORCE. We would build the SQL statements to execute the proper grants after approval.
3. The SQL flows through the input anchor of the macro. Our interface tool places the proper execute database, schema, and user that will execute the grant.

4. We use a python library called SNOWFLAKE-PYTHON-CONNECTOR
   1. Import statement below:

5. Connect to Snowflake in the snowflake connection class
6. And use the execute command to execute SQL statement and store the execution results.