Skip to Content

Reporting from Snowflake using Data App

Blog | September 9, 2022 | By Babu S

We recently decided to build a reporting data app. The functionality would be to extract the data straight out of Snowflake and make reports available for download in formats like pdf and xls without the need for a visualization layer and tool. We have seen a lot of our customers invest in visualization tools when their requirement is primarily for reports in these formats. With a solution like this there is no need for the visualization tool anymore. We decide to build this using Streamlit.

Streamlit was bought by Snowflake will be integrated in snowflake. By using Streamlit which will power their native application development framework, we get the following out of the box.

  • Ease of integration
  • Security

Building in Streamlit has other advantages too.

  1. Streamlit has inbuilt API features/libraries to support creating web apps easily as per our requirements with less coding (Which doesn’t need our in-depth programming knowledge).
  2. Streamlit re-runs the whole Python script whenever any app state changes. This enables an impressive level of interactivity without the need for custom call-back functions.
  3. Data caching simplifies and speeds up computation pipelines.
  4. Way to reduce compute time is to use a form container, Widgets inside a form container don’t trigger script execution until the user pushes the form_submit_button, so the user can update several control knobs at once with only a single re-run of the code.

In this post, You’ll learn

  1. Overview of how we built this reporting data app using Streamlit
  2. How does it work?
  3. Top five Streamlit features we used
  4. Hosting/Deploying the data app in a secured way

Overview:

As mentioned earlier, the data app is used to create customized reports dynamically as per requirements and can be downloaded in a formatted way (i.e., Can be downloaded as PDF or Excel).

By using this, we can eliminate the need for BI tool, while fulfilling the need for formatted reporting.

How does it work?

This data app is purely created using Streamlit & React Components.

Frontend UI is Streamlit + React & backend is python connecting to Snowflake Database.

We have used a React components in two places New Connection & Report Generation tabs of the UI.

Note: It also a challenging scenario where we have accomplished with calling a multiple react components in Streamlit code.

See the below demo to get an idea on this.

Figure 1: Reporting data app built using Streamlit – Demo

Top five Streamlit features we used:

We used the below listed Streamlit features to make the data app:

1. Streamlit Components

We have used Streamlit components to render the react code which will enhance the frontend UI much appealing.

Syntax to render React:

import streamlit.components.v1 as components    #to import the API library	

connection = components.declare_component("my_component”,
 url="http://localhost:3001",) # code to render the react code

2. st.form, text_input & form_submit_button:

This (st.form) helps to create the Form page where we can add all our input (text_input) parameter fields in that form. Once the parameter is filled with the values from the user, then user can select the “form_submit_button” to parse the inputted values to the code to render the information in the UI (frontend).

Figure 2: Streamlit API element to create & submit the form.

3. Session State:

It is a way to share variables between reruns, for each user session, also used to store and persist state.

Note: We have used this Session State predominantly to secure the multi-page app by-pass (i.e., without a proper flow we cannot move to any other pages from the multi-page app/UI)

if 'authenticated' not in st.session_state or 
     not st.session_state['authenticated']:

st.warning("Please login to continue")
st.markdown('
    <style> div[data-testid="stMarkdownContainer"]
    a {background-color: #f36f21;
      border: none;
      border-radius: 4px;
      color: #fff;
      cursor: pointer;
      padding: 0.43rem 0.75rem;
      text-decoration: auto;
      position: relative;
      top: 5px;
      }
    div[data-testid="stSidebarNav"] {height: 80vh}
    </style>', unsafe_allow_html=True)

    st.markdown('<a href="/" target="_self">Login</a>', 
    unsafe_allow_html=True)

    st.stop()

4. Multipage & Tabs features:

Multipage:  In our data app, we have Multipage UI so that whenever we click on a page in the sidebar UI, Streamlit navigates to that page without reloading the entire frontend.

Tabs:  This was a latest release in Streamlit, which is playing a vital role in all the pages (Multipage). This helps to segregate each module in the different tabs so look & feel of the UI will be better & provides a organized UI structure.

Figure 3: Multipage & Tabs in Streamlit UI

5. Caching

The problem with many dashboarding tools is that data is reloaded every time you select an option or switch between pages. Fortunately, Streamlit has an option allowing you to cache the data and only run it if it has not been run before.

Figure 4: Streamlit Cache API element

Hosting/Deploying the data app in a secured way:

  • We can host the Streamlit app for free with limits called Streamlit sharing – https://streamlit.io/cloud
  • While deploying the Streamlit app,  especially, even if we have fewer pieces of react JS code in it , then build the code and add the below mentioned code in the Python file wherever it calls a react component.
from pathlib import Path

if not _RELEASE:
 connection = components.declare_component("my_component",
 url="http://localhost:3001",)
else:
_component_path = (Path(__file__).parent/"frontend"/"build").resolve()
 connection = components.declare_component("my_component", path=_component_path)
  • We can secure our reports that are deployed in Streamlit cloud by using the below listed points.
    • Restricting the Web App hosted URL by sharing specific person or Public person under sharing tab.
    • Snowflake DB – Credential information can be stored securely under secret tabs.
Figure 5: Sharing & Secret Tab in Streamlit Clouds

We have the below mentioned (Refer: Figure 7) features available for any Streamlit app that are hosted in the Streamlit cloud.

Figure 6: Streamlit cloud features for the hosted Streamlit app
author image
About the Author
Senior Data Engineer with 6 Years of experience in Data Analytics & Cloud Data Engineering
Babu S | Senior Data Engineer | USEReady
Back to top