Tracking SiS application usage: An interim solution

Disclaimer: Streamlit-in-Snowflake (SiS) is currently a private preview (PrPr) feature on Snowflake.

Are you looking to track Streamlit-in-Snowflake (a.k.a. SiS — currently in private preview) application usage for your Snowflake account? I solved this very problem with a hacky, interim solution!

In this post, you’ll learn:

1. How to create an interim SIS_USAGE table
2. How to add usage tracking within your SiS applications
3. How to identify the number of times a user has used a SiS application

Assumptions

For this example walkthrough to work, the following assumptions have been made:

• The Snowflake account is enrolled in the SiS PrPr.
• The user has access to a role with the right privileges to create SiS applications.
• The user has access to a role that is the owner of one or more SiS apps.

How to create an interim SIS_USAGE table

▹ 1. In Snowsight, create a new SQL worksheet and set the appropriate role, database, schema, and warehouse context.

▹ 2. In the SQL worksheet, create the SIS_USAGE table:

CREATE TABLE IF NOT EXISTS APPDB.APPDEMO.SIS_USAGE (TS TIMESTAMP_LTZ, CURRENT_SESSION STRING, USER_NAME STRING, ROLE_NAME STRING, STREAMLIT_APP STRING);

How to add usage tracking within your SiS applications

▹ 1. In the Snowsight UI, switch the role context to the SiS app owner (APPDEV in this example) and navigate to the Streamlit Apps section.

▹ 2. In the Streamlit Apps section, create a new SiS app or click on an existing SiS app.

▹ 3. In the SiS app’s Python worksheet, add and execute an INSERT INTO statement:

# Create a string variable with the INSERT INTO query text
usage_query = """
INSERT INTO APPDB.APPDEMO.SIS_USAGE (TS, CURRENT_SESSION, USER_NAME, ROLE_NAME, STREAMLIT_APP)
VALUES
	(CURRENT_TIMESTAMP(), CURRENT_SESSION(), CURRENT_USER(), CURRENT_ROLE(), '<replace_with_SiS_App_Name>')
"""

# Execute the INSERT INTO query text every time the SiS app is ran for tracking purposes
usage_data = session.sql(usage_query).collect()

(Note: In my case, ‘<replace_with_SiS_App_Name>’ would become ‘APP1_NHL_DASHBOARD’)

How to identify the number of times a user has used a SiS application

▹ 1. In your current role, run the SiS application.

▹ 2. Then, switch between SiS applications where this approach has been set up.

▹ 3. After clicking through one or more SiS applications that your current role has access to, return to the SQL worksheet and run the following query:

SELECT 
  TS, 
  CURRENT_SESSION, 
  USER_NAME, 
  STREAMLIT_APP 
FROM APPDB.APP_DEMO.SIS_USAGE;

Expectant output:

▹ 4. Since SiS is still in private preview, running the CURRENT_ROLE() function will return the SiS application owner’s role name. Regardless of whether I’m in a role that is the SiS application owner or the SiS application user, a SiS application runs with the owner’s rights by default. This implies that retrieving CURRENT_ROLE() as ROLE_NAME will not provide accurate information for tracking purposes.

▹ 5. With that in mind, let’s leverage the table to identify the number of times a user has interacted with each SiS application:

SELECT 
    STREAMLIT_APP AS "Streamlit App", 
    USER_NAME AS "User", 
    COUNT(CURRENT_SESSION) AS "Number of Sessions"
FROM 
    APPDB.APPDEMO.SIS_USAGE
GROUP BY 
    STREAMLIT_APP, USER_NAME;

Expectant output:

▹ 6. Using the same table, let’s track what time of day users are interacting with each SiS application:

SELECT 
    STREAMLIT_APP AS "Streamlit App", 
    CASE
        WHEN DATE_PART('hour', TS) < 12 THEN 'morning'
        WHEN DATE_PART('hour', TS) < 18 THEN 'afternoon'
        ELSE 'evening'
    END AS "Part of Day",
    COUNT("Part of Day") AS "Number of Sessions"
FROM 
    APPDB.APPDEMO.SIS_USAGE
GROUP BY 
    STREAMLIT_APP, "Part of Day"
ORDER BY STREAMLIT_APP;

Expectant output:

Wrapping up

Thank you for reading my post! To recap, I provided a step-by-step walkthrough of a hacky, interim approach for SiS application usage tracking. If you have any questions, please post them in the comments below or contact me on LinkedIn.

Happy Streamlit-ing in Snowflake! 🎈