Logo - Keyrus
  • Playbook
  • Services
    Data advisory & consulting
    Data & analytics solutions
    Artificial Intelligence (AI)
    Enterprise Performance Management (EPM)
    Digital & multi-experience
  • Insights
  • Partners
  • Careers
  • About us
    What sets us apart
    Company purpose
    Innovation & Technologies
    Committed Keyrus
    Regulatory compliance
    Investors
    Management team
    Brands
    Locations
  • Contact UsJoin us

Improving Snowflake data load times by developing custom Alteryx connectors

37 minutes load time reduced to 20 seconds

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.

Technology partners

Alteryx

Alteryx automates analytics, data science, and processes in one unified platform to accelerate digital transformation. Organizations rely on Alteryx to rapidly upskill their workforces and deliver strategic, high-impact, business outcomes.

120+

certified consultants

8+

years of partnership

Share this key play

whatsapptwitter
linkedinfacebookworkplace

More key plays

Transforming ESG Reporting: Automating Metrics Collection with Databricks Integration
Successfully Implementing SFDR & EU Taxonomy for a Global Asset Manager
Boost sales with targeted marketing campaigns and multi-channel communication
Using Alteryx to automate processes at an international bank
Modernizing investment performance services through data governance
A centralized platform for demand, scenario, gross margin, and sales & operations planning
Supercharging growth strategies with integrated FP&A and workforce planning
Transform financial reporting and planning with real-time visibility into the business drivers
Boost clarity: align revenue assumptions and headcount forecasts
Revolutionising Manufacturing Data: A Cloud-Based Data Warehouse Solution
Logo - Keyrus
London

One Canada Square Canary Wharf London E14 5AA