How to Build a Snowflake Dashboard in SQL: An Ultimate Guide

Interactive dashboards are essential for your business to assess their performance and make strategic decisions quickly. By transforming complex data into an easy-to-understand visual format, dashboards can help you quickly track KPIs, observe trends, identify outliers, and support data-driven decisions.

Snowflake, a leading cloud-based data warehouse platform, offers a unified interface to build dynamic dashboards using SQL, enabling real-time monitoring and in-depth analysis.

This article will guide you on how to construct a dashboard in SQL from Snowflake.

About Snowflake Dashboards

Snowflake dashboards help you visualize data by querying the table information stored in the Snowflake database. These dashboards allow you to create interactive, real-time views of your data, offering actionable insights into key business metrics.

While Snowflake itself does not have a built-in visualization layer, you can use Snowsight, Snowflake's intuitive web interface. This interface allows you to query data and create visualizations directly within Snowflake.

For interactive dashboards, Snowsight combines Snowflake’s compute capabilities with built-in visualization options like tiles—dynamic visual elements. Tiles in Snowsight allow you to visualize data in various formats, including charts, scatterplots, heat grids, and scorecards. You can incorporate tiles into your Snowflake dashboards using SQL or Python to present your query results in an engaging and insightful manner.

Why Build a Snowflake Dashboard in SQL?

• Ease of Use for Querying Data: SQL is specifically designed for data querying and manipulation. Snowflake supports a highly standardized version of SQL, which allows you to retrieve data directly from Snowflake tables without requiring extensive programming knowledge.
• ‍Performance Optimization: Snowflake’s architecture is optimized for SQL queries. Leveraging SQL ensures that your dashboard operations align closely with Snowflake’s capabilities, offering better performance for large-scale data processing.‍
• Simplified Data Operations: Using built-in SQL functions, you can effortlessly handle operations like filtering, aggregating, and joining datasets while creating dashboards, minimizing the need for complex scripting.
• ‍Reduced Dependency on External Tools: Building dashboards with SQL helps you reduce the need for additional libraries or tools that are often required in other programming languages, simplifying the entire workflow.‍
• Direct Integration with Snowflake Functionalities: Creating dashboards using SQL enables you to directly utilize Snowflake features like materialized views, time travel, and cloning. These features facilitate efficient data handling and advanced analytics.
• ‍Faster Learning Curve: SQL is widely used and has a relatively simpler learning curve than Python, especially for non-technical users. This makes it easier for analysts and business intelligence teams to adopt and contribute to dashboard development.

Prerequisites to Build the Snowflake Dashboard in SQL

Before building the Snowflake dashboard using SQL, verify the following prerequisites are in place:

• A Snowflake account with the necessary permissions to query and manage data.
• Basic to intermediate proficiency in SQL language for writing queries and creating views.
• A Snowflake database. 

How Do You Create a Snowflake Dashboard in SQL?

Take a look at the step-by-step guide on how to construct a dashboard in SQL from Snowflake:

Step 1: Access the Snowflake Snowsight Interface

1. Log in to the Snowflake account using your credentials.
2. In the navigation menu, select Snowsight.
3. Click on the + Create > Dashboards in the navigation pane.

Alternatively, you can go to the Project section, select Dashboards, and then click the + Dashboard button. This option allows you to view all the dashboards you have already created or those shared with you.

4. Provide a name for your dashboard and click on the Create Dashboard button.

5. After creating the dashboard, you can manage it by incorporating tiles, adding custom filters, and sharing it with other Snowsight users.

Step 2: Add a Tile to Your Dashboard

1. Specify the role ACCOUNTADMIN and warehouse COMPUTE_WH (X-Small).

2. From the Dashboards page, click the New Tile button and choose From SQL Worksheet option.

3. You will see a blank SQL worksheet; before building your query, you must initially select a database and desired schema. Here, let’s consider the TPCH_SF1 schema within the SNOWFLAKE_SAMPLE_DATA database.

4. Now, let’s start querying on the CUSTOMER tables within the selected database. The structure of the CUSTOMER table is:

5. Write a sample SQL query in the worksheet and click on the Run button.

Once you run the query, you can see the output under the Results tab and its visual representation in the Chart tab.

6. You can now customize the chart by choosing the desired type and adding x or y axis/labels.
7. After you complete the chart settings, select Return to <dashboard name> to save your changes and view your dashboard.
8. You can enhance your dashboard by adding more tiles and arranging them in the desired position by simply dragging and dropping them.

Step 3: Share Your Dashboard

1. Open your dashboard and select the option Share.
2. Enter the Snowflake usernames in the Invite by name or username field and click the Done button.

3. Alternatively, select the People with link option and choose View + run from the drop-down menu. Then, click the Get Link button to share the link with other users.

Using PyAirbyte to Create Snowflake Dashboard

Clean and well-organized data is essential for creating insightful and meaningful dashboards. However, organizational datasets are often scattered across multiple locations. Manually consolidating data from various sources into separate Snowflake databases or tables requires a significant amount of time, which can delay the dashboard's development.

One simple solution to overcome this challenge is to leverage an AI-powered data movement platform like Airbyte. Airbyte offers a developer-friendly, open-source Python library called PyAirbyte, which brings the power of Airbyte to every Python and AI developer.

How Does PyAirbyte Help in Moving Data to Snowflake?

Using PyAirbyte, you can extract data from multiple sources, including GitHub, Postgres, CoinAPI, and Shopify, into SQL caches, such as Snowflake, DuckDB, and Postgres. These caches are compatible with different Python libraries like Pandas and AI frameworks, such as LangChain. By converting SQL caches to Pandas DataFrame, you can perform complex transformations to align the structure of the data with the Snowflake table schema. After applying transformation logic, you can load the data into the preferred Snowflake table.

Here is an example of ingesting data from the CoinAPI source to Snowflake using PyAirbyte.

1. Install PyAirbyte

2. Define a PyAirbyte cache sf_cache for the Snowflake data warehouse.

3. Establish a connection to CoinAPI.

4. Once the data is in the Snowflake cache, you can read it into a Pandas DataFrame ohlcv_df and perform a series of transformations.

5. Get an SQL engine from the internal Snowflake cache.

6. Write the transformed DataFrame to a Snowflake table by importing the snowflake.connector module.

Your data is now available in your Snowflake table and ready to query using SQL to build customizable dashboards.

Discover More with Airbyte: Beyond PyAirbyte

Airbyte provides 550+ pre-built connectors to automate data movement workflows across several platforms. If suitable connectors do not exist, you can create custom ones using its no-code Connector Builder with AI Assistant.

Here are some key use cases leveraging Airbyte’s powerful native connectors in dashboard development:

Creating a Slack Analytics Dashboard

A Slack analytics dashboard provides insightful metrics on channel engagement, member activity, message patterns, and app usage between workspaces. By utilizing Airbyte’s built-in connectors, the process of building this dashboard can be streamlined.

Start by configuring Airbyte's Slack connector to extract data from your Slack workspace. Use PostgreSQL or any required destination connector to load the extracted Slack data. Connect Apache Superset, a data visualization platform, with the destination system. Use Superset’s features to design and customize your analytics dashboard.

Building a GitHub Activity Dashboard

A GitHub activity dashboard offers valuable information for monitoring the project’s popularity, understanding community contributions, measuring response times to pull requests, and more. With Airbyte’s extensive catalog of connectors, this dashboard can be developed efficiently.

Begin by setting up Airbyte’s GitHub source connector to collect data from your GitHub projects. Use PostgreSQL or other destination connectors to store the gathered GitHub data. After loading data in PostgreSQL, connect the Metabase, a data visualization tool, to the PostgreSQL instance. Leverage Metabase’s capabilities to develop your customized dashboards.

Conclusion

You have explored how to construct a dashboard in SQL from Snowflake and drive data-driven decisions efficiently. However, before creating a dashboard, it is essential to consolidate scattered data sources into a Snowflake database to ensure accurate and actionable insights.

Using a no-code data integration platform like Airbyte, you can streamline the data transfer from various sources to your chosen destination. With Airbyte’s extensive catalog of built-connectors, custom connector options, and sync modes, you can seamlessly move and manage your data for a better dashboarding experience.