Snowflake: Schedule, Trigger, Monitor, and Orchestrate Operations

Snowflake is a cloud-based data warehousing platform that allows users to store, manage, and analyze large amounts of data. It offers high performance, scalability, and ease of use by separating storage and compute resources. This Universal Task allows Stonebranch users to orchestrate, schedule, trigger, and monitor the Snowflake load and unload process from different data sources (cloud storage or local VMs) directly from the Universal Controller. It uses Python libraries to perform all functions listed in the following sections. Alternatively, you can also perform all these operations using the Snowflake JDBC driver which you can add to the Universal Controller libraries, and use SQL Task to perform any operations with Snowflake (Downloading / integrating the JDBC Driver | Snowflake Documentation)

Key Features

This Universal task provides the following main features:

• Load data from AWS S3 to Snowflake: Load the S3 bucket file(s) into a Snowflake table, You may specify the file format options and copy options appropriately.
  
• Load data from Azure Storage to Snowflake:  Load the Azure container blobs into a Snowflake table, You may specify the file format options and copy options appropriately.
• Load data from Google storage to Snowflake: Load the google storage bucket files into a Snowflake table, You may specify the file format options and copy options appropriately. Please note that you will need to define the storage integration in Snowflake and provide this in the Universal Task.
• Load Internal stage file to Snowflake Table: Files that are available in Snowflake internal storage to be loaded into a Snowflake table using the pipe name and authentication using private public key files. The data can be loaded in Snowflake using patterns.
• Copy from local server to Internal staging: Copies files from local Windows or Linux server to Snowflake internal staging area.
• Unload Snowflake data to AWS S3: This feature helps to unload the data from a Snowflake table to a AWS S3 bucket, file format options and copy options can be provided appropriately.
  
• Unload Snowflake data to Azure Storage: This feature helps to unload the data from a Snowflake table to an Azure container; file format options and copy options can be provided appropriately.
  
• Unload Snowflake data to Google Storage: This feature helps to unload the data from a Snowflake table to a Google cloud Storage; file format options and copy options can be provided appropriately. Also, this would need an storage integration name from Snowflake.
  
• Unload Snowflake data to Internal stage: Unloads the Snowflake table into an internal staging area in Snowflake.
  
• Unload from internal stage to local server: This feature helps to copy the files in staging area to a local windows or a Linux server.
  
• Execute a Snowflake command: Users can use this feature to execute snowflake commands; for example: Copy, Remove, Select, Delete, etc.

What's New

Enhancements

• Added: Support of OAuth authentication via Microsoft Entra ID.
• Added: Support of variable for Snowflake Login Name field
• Added: Automatic installation of Snowflake Python packages.
• Added: Snowflake command status monitoring

Fixes

• Fixed: Private Key Location not found error while the task is executed on a Windows agent