How to Prepare and Explore Data with KNIME and Snowflake (ETL)

How to Prepare and Explore Data with KNIME and Snowflake (ETL)

This workflow is an example of how to read and transform different customer data files within KNIME prior loading the manipulated data into Snowflake. Once uploaded the data is analyzed in Snowflake and the result is visualized in KNIME.

4. Exploring

Visualize the aggregation result in KNIME.

1. Extraction

Extract data from Excel and CSV files

2. Transformation

Transformation are done directly in KNIME

3. Loading

The manipulated data is uploaded into a new table in Snowflake

📍Technical Note:

If you don't have a Snowflake account, you can sign up for a free 30-day trial account (when signing up, select Enterprise edition and choose any Snowflake cloud/region - preferably AWS or Azure).

Configure the Snowflake Connector node:

After signing up, navigate to your Snowflake Account details. There, you'll find key information to configure the node:

• Account identifier (input it in Full account name field)

• Login name (input it in Authentication > Username & password field). The password field is the password used to log in to your Snowflake account.

• Role (input it in Default access control role). If you're using a free trial account, make sure the role is ACCOUNTADMIN. Otherwise, input the role that is enabled for your account (your role should have write permissions).

• Stay in your Account details and navigate to the Config File tab. Select the warehouse you prefer (e.g., SNOWFLAKE_LEARNING_WH, if you're using a free trial account) and input the warehouse name in the Virtual warehouse field.

📍Technical Note:

[For users with a free 30-day trial account] In the DB Table Structure Creator and DB Row Inserter nodes, keep the default configurations and simply execute the nodes.

[For users with a regular account] In the DB Table Structure Creator and DB Row Inserter nodes, click on the Browse button and select your preferred Database and Schema names (they must already exist). Make sure the Table name stays as per default configuration.