Analyzing Snowflake data without SQL using AI features

Julien Ergan

December 11, 2023

In the last decade, the evolution of the data infrastructure landscape has seen the emergence of data lakes, data warehouses, and data marts. These platforms have evolved in response to the growing complexity of managing and extracting insights from vast amounts of data.

Snowflake is one of the main players in this data infrastructure landscape, which has experienced rapid growth in recent years. In this article I will introduce Snowflake and its distinctive capabilities, consider the limitations in leveraging data in Snowflake, and explore how data democratization can be achieved through integration with Morph.

‍

For an introduction to modern data infrastructure (data lakes, warehouses, and marts) and to understand how Morph can be used as a semantic layer within your own data pipeline, check out these articles:

Data Infrastructure: an Introduction to the Semantic Layer

Data Mart: the essential connection point between BI and data warehouse

‍

What is Snowflake?

Snowflake is a cloud data platform designed to facilitate data integration, analysis, and sharing.

Data analysis infrastructures like Snowflake are built as pipelines where data flows from one layer to the next: Data lake → Data warehouse → Data mart → Business Intelligence / Analysis tool.

Snowflake covers the upstream layers of that pipeline — from Data lake to Data mart — but leaves out the final Business Intelligence / Analysis layer.

Competing solutions to Snowflake include the likes of Google BigQuery and Amazon Redshift.

Snowflake coverage in the data pipeline

‍

Where Snowflake shines

Snowflake's distinctive capabilities include:

Processing performance: Snowflake's design separates storage — holding data — and virtual warehouses — executing queries — allowing parallel processing for high performance.
Cost structure: Operating on a consumption-based pricing model, Snowflake charges based on data storage, warehouse size, and processing time, preventing unnecessary costs.
Marketplace: Snowflake allows the purchase of third-party datasets for combined analysis with proprietary data.
Multi-cloud support: Serving as a hub, Snowflake enables centralized data management across multiple cloud environments like Amazon Web Services, Azure, and Google Cloud Platform.

Snowflake acquired Streamlit, an open-source web application development company, in 2022. This led to the development of "Streamlit in Snowflake," allowing the creation of simple data analysis-related applications.

‍

Challenges in leveraging data in Snowflake

While Snowflake is an excellent data analytics infrastructure, there are challenges for business teams to effectively utilize it.

‍

Complex data integration setup‍

Setting up Snowflake is primarily done through the which can be a significant hurdle for those unfamiliar with it. Even for those comfortable with the command line, building and configuring a data pipeline without prior experience can be complex.

Given the nature of the technical skills required to interact with Snowflake, it remains a platform mainly used by data scientists, database engineers, and the like. It can be made accessible and usable by other teams within a business, but this requires a technical setup which makes it unattractive.

‍

SQL-based data manipulation

Even when a data pipeline is successfully set up in Snowflake, extracting data from the data mart requires using SQL. This poses another significant challenge if the entire organization wishes to use it as a place to access data.

Snowflake interface (source: https://docs.snowflake.com/user-guide/ui-history)

‍

Democratizing Snowflake data with Morph

To overcome these challenges in making your Snowflake data accessible throughout your business, one solution is to utilize Morph.

By integrating your data managed in Snowflake with Morph, you can:

Aggregate data across multiple tables
Create data visualizations and dashboards
Manage data access permissions

All of this can achieved through clicks and data queries in plain, everyday language — no need for SQL — contributing to an environment where all employees can access data that was previously only accessible to a few.

In this new landscape, Snowflake serves as an upstream data processing platform, while Morph becomes a data sharing and aggregation place for business teams.

Integrating Morph with Snowflake for a full coverage of the data pipeline

‍

How to integrate Morph with Snowflake: a step by step guide

Now, let's look at the practical steps to integrate Morph with Snowflake.

We will divide the integration process into two parts:

steps on the Snowflake side
the steps within Morph

‍

Steps in Snowflake

To integrate Morph with Snowflake, you need to retrieve the following three pieces of data from Snowflake:

Server
Client ID
Client Secret

Here's how you can obtain these values.

First, create a security integration on Snowflake using the DESC command to retrieve information for Server and Client ID.

Find the value of the Server in Snowflake

‍

1. Server

Server is obtained from the value returned by OAUTH_AUTHORIZATION_ENDPOINT, which looks like:

    
    	https://ACCOUNT_NAME.ap-northeast-1.aws.snowflakecomputing.com/oauth/authorize

‍

Extract the part corresponding to:

    
    	https://ACCOUNT_NAME.ap-northeast-1.aws.snowflakecomputing.com

NB: Be cautious as the ap-northeast-1.aws part may vary depending on the connected cloud service.

‍

2. Client ID

The value of OAUTH_CLIENT_ID is the Client ID.

Execute the following command to obtain the Client ID.

    
      CREATE or replace SECURITY INTEGRATION [INTEGRATION_NAME]
      TYPE = OAUTH
      ENABLED = TRUE
      OAUTH_CLIENT = CUSTOM
      OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
      OAUTH_REDIRECT_URI = 'https://app.morphdb.io'
      OAUTH_ISSUE_REFRESH_TOKENS = TRUE
      BLOCKED_ROLES_LIST = ()
      OAUTH_REFRESH_TOKEN_VALIDITY = 7776000;
      
      DESC SECURITY INTEGRATION [INTEGRATION_NAME];

‍

3. Client Secret

You can obtain the Client Secret in Snowflake with the following command, as shown in the figure below. This command is asking to confirm the authorization information.

Retrieve the Client Secret in Snowflake

‍

Execute the following command to obtain Client Secret.

    
      select system$show_oauth_client_secrets('[INTEGRATION_NAME]');

‍

After running these commands, you'll get a JSON containing the following values.

The value of the Client Secret is the value of the OAUTH_CLIENT_SECRET.

    
      {
        "OAUTH_CLIENT_SECRET_2": "",
        "OAUTH_CLIENT_SECRET": "",
        "OAUTH_CLIENT_ID": ""
      }

‍

The Snowflake setup is now complete. Well done!

Next, let’s configure the integration in Morph.

‍

Steps in Morph

Choose Snowflake from "import from other services" in Morph, and enter the authentication information you obtained earlier in the “Import data” screen:

Snowflake data Import screen in Morph (1)

‍

Snowflake sign in screen

‍

Input various settings such as the table you want to retrieve from Snowflake.

Configure the optional table name and import settings, and you're done with the import!

Snowflake data Import screen in Morph (2)

‍

You've successfully imported your data from Snowflake into Morph!

‍

Now, you can start aggregating and visualizing your data without the need for SQL or Python.

For some data aggregation and visualization examples, check out these other articles:

Morph Blog

‍

Snowflake, and more!

This article focused on Morph integration with Snowflake. Morph supports integration with other data sources such as Salesforce, Stripe, and databases like PostgreSQL.

‍

Sign up for free, integrate multiple data sources and experience flexible data analytics, all in one data studio with Morph.

Try Morph