Improving Snowflake data load times by developing custom Alteryx connectors

Background

Our FS client is using Alteryx as an ETL tool to load data to Snowflake databases. The use of Alteryx allows these data loads to be scheduled and implement a variety of data inputs ranging from static files delivered via email to API calls to ensure the highest quality data is used.

Challenge

Due to internal security policies, database passwords are securely stored within a CyberArk password vault. This also restricts the use of stored database connections within the gallery. Existing solutions within the company use Snowflake key-pair authentication, this connection method relies on System DSN connections which require authentication to already be in place. To produce a working solution, a process must be able to retrieve credentials from the CyberArk password vault at runtime before using them in a database connection string to load data to Snowflake.

Approach

The working solution is split into 2 parts, password retrieval and database connection using the password. 1. CyberArk credential retrieval - In order to retrieve the credentials, the CyberArk APIs are used. A two-step process is wrapped into a macro - first the API secret key is sent to generate an authentication token. This token is then sent via a different API which returns the password. The download tool, which would typically be used for API calls, will not work here due to the specific formatting of the API call. Therefore, in order to utilise the correct formatting, the process was written into the Python tool utilising the requests and json libraries. 2. Bespoke Snowflake connector - Current Alteryx-Snowflake connections cannot be DSN Less - this means that the connection details are stored in the computer system and cannot be manipulated from within Alteryx. In order to allow a password to be passed to the connection, the best solution was to again use python. By utilising the Snowflake Connector for Python, the connection details can be defined and passed through at runtime. In order to fully utilise this connector library, a process was developed which creates a flat file of the data to be loaded, zips it and loads it to a Snowflake internal stage. From here, a COPY INTO command is run to load the data to the desired table.

Benefits

By utilising the CyberArk APIs, the secret key can be input as a wizard value to workflows, allowing for increased security and removing saved passwords from connection strings, the gallery and workflows. The use of the Snowflake python library allows the retrieved password to be used within a database connection string, removing the need for stored system DSN connections. This also allows full utilisation of Snowflake through Alteryx - by using the COPY INTO command bulk data can be loaded at high-speed, vastly reducing the load time compared to output or In-Database tools. This has also provided a template to allow variations of the process - SQL code can now be dynamically generated and run within a Snowflake environment to allow data to be manipulated before being read into a workflow for further processing.

Share this key play