Skip to content

Summit 26 from June 1-4 in San Francisco

Lead your organization in the era of agents and enterprise intelligence.

register now
Snowflake for DevelopersGuidesBuild and Deploy an Interactive Shiny Dashboard with the Posit Team Native App and Snowflake Cortex AI
Quickstart

Build and Deploy an Interactive Shiny Dashboard with the Posit Team Native App and Snowflake Cortex AI

Cortex LLM
Sarah Sdao
fork repo

Overview

In this guide, we'll use the Posit Team Snowflake Native App to build an interactive dashboard that lets users explore Home Mortgage Disclosure Act (HMDA) data. In Posit Workbench, we'll use Positron Assistant and Databot with Snowflake Cortex AI to do some quick, yet powerful exploratory data analysis. Then, we'll create an interactive Shiny app and deploy it to Posit Connect in Snowflake with one-click publishing for easy sharing across your organization.

We'll also make sure the shared dashboard respects built-in Snowflake security and authorization settings, ensuring anyone who views the dashboard on Connect only sees data they have access to.

By the end of this guide, we'll have a fully functional dashboard where users can explore the data through interactive visualizations and filters, viewing insights about loan amounts, key metrics (total loans, average loan amounts, and total loan volume), and year-over-year lending trends.

What You Will Learn

  • How to securely connect to your Snowflake databases from Workbench and the Positron Pro IDE
  • How to leverage Cortex AI using Databot and Positron Assistant for exploratory data analysis
  • How to create an interactive Shiny dashboard for data exploration and use Positron Assistant to refine it
  • How to deploy and share the dashboard to Connect in Snowflake with one-click publishing

What You Will Build

  • An interactive Shiny dashboard with dynamic visualizations and filters for exploring mortgage data, accessible to your team on Connect in the Posit Team Snowflake Native App

Prerequisites

Phase 1: Set Up Your Environment

Step 1: Access the HMDA Dataset

For this analysis, we'll use the Home Mortgage Disclosure Act (HMDA) dataset from Snowflake's free public database. This dataset contains mortgage application and origination data collected under the HMDA, including information about loan types, applicant demographics, property characteristics, and loan outcomes across different geographic areas.

The HMDA dataset we'll use is located at:

  • Database: SNOWFLAKE_PUBLIC_DATA_FREE
  • Schema: PUBLIC_DATA_FREE
  • Table: HOME_MORTGAGE_DISCLOSURE_ATTRIBUTES

To verify you have access to this data, navigate to Snowsight and click + > SQL File . Then run the following query:

SELECT *
FROM SNOWFLAKE_PUBLIC_DATA_FREE.PUBLIC_DATA_FREE.HOME_MORTGAGE_DISCLOSURE_ATTRIBUTES
LIMIT 10;

You should see the first 10 rows of the HMDA dataset, which includes columns about mortgage applications, loan details, applicant information, and property characteristics.

If you find that you do not have access to this dataset, please contact your account administrator.

Step 2: Launch Posit Workbench from the Posit Team Native App

We can now start exploring the data using Posit Workbench. You can find Workbench within the Posit Team Native App, and use it to connect to your database.

Open the Posit Team Native App

In Snowsight, navigate to Horizon Catalog > Catalog > Apps > Posit Team.

Click Launch app.

If you do not see the Posit Team Native App listed, contact your Snowflake account administrator to:

Open Workbench from the Posit Team Native App

From the Posit Team Native App, click Posit Workbench.

You might be prompted to log in to Snowflake using your regular credentials or authentication method.

Step 3: Create a Positron Pro Session

Workbench provides several IDEs, including Positron Pro, VS Code, RStudio Pro, and JupyterLab. For this analysis, we will use Positron, the next-generation data science IDE built for Python and R. It combines the power of a full-featured IDE with interactive data science tools for Python and R.

  1. Within Workbench, click + New Session to launch a new session.
  1. When prompted, select the Positron Pro IDE. You can optionally give your session a unique name.
  1. Under Session Credentials, click the button with the Snowflake icon to sign in to Snowflake. Follow any sign in prompts.
  • This lets your Workbench session securely inherit your Snowflake role, which grants you access to data warehouses and compute resources using your existing Snowflake identity.
  • For more information on how Workbench uses your Snowflake credentials, see the Workbench-managed Snowflake credentials section of the Workbench user guide.
  1. Click Launch to launch Positron Pro. If desired, you can check the Auto-join session option to automatically open the IDE when the session is ready.

Ensure You Have the Necessary Extensions

The analysis contained in this guide requires you to have some extensions installed and enabled. You can verify that you have them from the Extensions view.

  • The Shiny extension supports the development of Shiny apps in Positron.
  • Posit Publisher lets you start the deployment of projects to Connect from Positron with a single click.

Both of these extensions are included automatically in Positron as bootstrapped extensions. Before we dive into our data analysis, let's make sure we have them installed and enabled:

  1. Open the Positron Extensions view: on the left-hand side of Positron Pro, click the Extensions icon in the activity bar to open the Extensions Marketplace.

  2. Search for "Shiny" or "Posit Publisher" to find the extensions. For example:

  1. Verify that you have each extension:
  • If an extension is already installed and enabled, you will see a wheel icon.
  • If it is not already installed, click Install.
  • If you cannot install the extensions yourself or you find that they are disabled, ask your administrator for access.

Phase 2: Prepare Your Development Environment

Step 4: Access this Guide's Materials

This guide will walk you through the steps contained in https://github.com/posit-dev/snowflake-posit-build-deploy-interactive-dashboard. To follow along, clone the repository by following the steps below.

  1. Open your home folder in Positron:

    • Press Ctrl/Cmd+Shift+P to open the Command Palette.
    • Type "File: Open Folder", and press enter.
    • Navigate to your home directory and click OK.

  2. Clone the GitHub repo by running the following command in a terminal:

    git clone https://github.com/posit-dev/snowflake-posit-build-deploy-interactive-dashboard/

    If you don't already see a terminal open, open the Command Palette (Ctrl/Cmd+Shift+P), then select Terminal: Create New Terminal to open one.

    Note: This guide uses HTTPS for git authentication. Standard git authentication procedures apply.

  3. Open the cloned repository folder:

    • Press Ctrl/Cmd+Shift+P to open the Command Palette.
    • Select File: Open Folder.
    • Navigate to snowflake-posit-build-deploy-interactive-dashboard and click OK.

Explore Quarto

Before we dive into our data analysis, let's first discuss Quarto. We've documented the code for this guide in a Quarto document, quarto.qmd.

A Quarto document can be thought of as a regular markdown document, but with the ability to run code chunks. You can run any of the code chunks by clicking the Run Cell button above the chunk in Positron Pro.

To render and preview the entire document, click the Preview button or run quarto preview quarto.qmd from the terminal.

This will run all the code in the document from top to bottom and generate an HTML file, by default, for you to view and share.

Learn more about Quarto here: https://quarto.org/.

Step 5: Install R Packages from renv.lock

Our analysis uses the following R packages: {connectcreds}, {DBI}, {dplyr}, {dbplyr}, {ggplot2}, {scales}, and {shiny}, plus more for advanced data analysis and deployment to Connect.

These dependencies are all found in the file renv.lock. We can automatically install all dependencies by running the renv::restore() function. These dependencies will carry over to our Connect deployment as well, ensuring your content runs smoothly.

Open the quarto.qmd file in your current directory in Positron. Then run the following code chunk to install the renv package:

install.packages("renv")

Next, activate the renv environment and run:

renv::activate()

Finally, you need to restore all required packages from the lockfile by running:

renv::restore()

You might need to restart your R session once all dependencies are set. Restart your session by clicking the refresh icon above the R console.

Phase 3: Connect and Explore

Step 6: Connect to Snowflake Data

Now that we have our Positron Pro session started with the necessary extensions and dependencies, we can connect to our data in Snowflake. There are two ways we can do this: automatically by prompting Databot, or by running some code ourselves.

Use Databot to Connect

Important: Databot is currently in research preview.

Databot is an AI assistant designed to dramatically accelerate exploratory data analysis for data scientists fluent in R or Python, allowing them to do in minutes what might usually take hours.

Instead of manually writing connection code, you can use Databot's built-in Snowflake skill to guide you through the connection process. This is especially helpful when you're working in Workbench, as Databot can automatically detect and use your Snowflake credentials.

Databot runs with your available Cortex AI LLMs, keeping your data secure and private. Choose which model you would like to use by clicking the model in the lower right-hand corner of the Databot window.

Note: You must be running Databot v0.0.41 or higher to use it to connect to Snowflake data. To check your Databot version, click the Extensions icon on the left-hand side, and then search for "Databot." Select the extension in the list to open its details page, where the installed version number is listed in the top-right corner.

  1. Open the Command Palette (Cmd/Ctrl+Shift+P).

  2. Type "Databot" and select Open Databot in Editor Panel.

  3. The Databot panel will open, ready to analyze your mortgage data. Ensure you are still in your R session within the Databot dialog.

In the Databot panel, enter:

Help me connect to Snowflake.

Databot will:

  1. Use your Cortex AI LLM.
  2. Detect if you're in Workbench with integrated authentication.
  3. Provide the appropriate connection code for your environment.
  4. Guide you through discovering available databases, schemas, and tables.
  5. Help you explore Semantic Views if available.

Once connected, you can move on to the next section, which is to explore the data with Databot.

Connect with Code

You can also connect to your data using the quarto.qmd file from snowflake-posit-build-deploy-interactive-dashboard. Click the Run Cell button to run the next R code chunk in the quarto.qmd file.

The code uses {dplyr}, which provides an intuitive way to work with database tables in R. To ensure our connection works across different environments (development, Workbench, and Connect), the code uses {dbplyr}, {DBI}, {odbc}, and {connectcreds} to create a flexible connection function.

library(DBI)
library(odbc)
library(dplyr)
library(dbplyr)
library(connectcreds)

get_connection <- function() {
  warehouse <- "DEFAULT_WH"
  database <- "SNOWFLAKE_PUBLIC_DATA_FREE"
  schema <- "PUBLIC_DATA_FREE"
  account <- Sys.getenv("SNOWFLAKE_ACCOUNT")

  con <- DBI::dbConnect(
    odbc::snowflake(),
    account = account,
    warehouse = warehouse,
    database = database,
    schema = schema
  )

  return(con)
}

con <- get_connection()
mortgage_data <- tbl(con, "HOME_MORTGAGE_DISCLOSURE_ATTRIBUTES")
message("Successfully established secure connection to Snowflake!")

We have now used Workbench, Positron, and R to connect to the HMDA mortgage data in Snowflake's public dataset, all securely within Snowflake.

Step 7: Explore the Data with Databot

Before building our dashboard, let's use Databot to explore the mortgage data. Unlike general coding assistants, Databot is purpose-built for EDA with rapid iteration of short code snippets that execute quickly.

With your connection to the HOME_MORTGAGE_DISCLOSURE_ATTRIBUTES table established (from the previous section), you can now ask Databot to explore the data. Try these prompts:

Understand the dataset structure:

Explore `HOME_MORTGAGE_DISCLOSURE_ATTRIBUTES` from the `SNOWFLAKE_PUBLIC_DATA_FREE` database and summarize its structure

Databot will generate and execute code to show you the columns, data types, and basic statistics.

Investigate specific patterns:

Explore the relationship between loan amounts and property types

Databot will create visualizations and statistical summaries to help you understand lending patterns.

Compare trends across geography:

How do loan approval rates vary across different states?

Databot will analyze geographic patterns and create appropriate visualizations.

Identify data quality issues:

Check for missing values and data quality issues in the mortgage data

Databot will examine the dataset for completeness and potential problems.

Create a Quarto report:

Once you are done exploring the data, you can create a Quarto report so you can reproduce the analysis another time or share the information with your team. Just ask Databot to create a report by calling /report, or by entering:

Create a Quarto report with your findings

Phase 4: Build Your Dashboard

Step 8: Build the Dashboard

Now that we've done some exploratory data analysis, let's create our interactive dashboard. We'll build a Shiny app that lets users explore the HMDA data through interactive filters and visualizations.

Build a Shiny App

Let's run the following code, which will build a Shiny app to explore the data interactively. Running this code will create a new app.R file in the current directory that contains all of your already-established connection settings.

# Create app.R file with Shiny application code
app_code <- '
library(shiny)
library(DBI)
library(odbc)
library(dplyr)
library(dbplyr)
library(connectcreds)
library(ggplot2)
library(scales)

get_connection <- function() {
  warehouse <- "DEFAULT_WH"
  database <- "SNOWFLAKE_PUBLIC_DATA_FREE"
  schema <- "PUBLIC_DATA_FREE"
  account <- Sys.getenv("SNOWFLAKE_ACCOUNT")

  con <- DBI::dbConnect(
    odbc::snowflake(),
    account = account,
    warehouse = warehouse,
    database = database,
    schema = schema
  )

  return(con)
}

# Define UI
ui <- fluidPage(
  titlePanel("HMDA Mortgage Data Explorer"),
  sidebarLayout(
    sidebarPanel(
      selectInput("loan_type", "Select Loan Type:",
                  choices = c("All", "Conventional", "FHA", "VA", "FSA/RHS")),
      selectInput("state", "Select State:",
                  choices = c("All", "California", "Texas", "Florida", "New York", "Pennsylvania")),
      sliderInput("loan_amount", "Loan Amount Range:",
                  min = 0, max = 1000000, value = c(0, 500000),
                  step = 50000)
    ),
    mainPanel(
      h4("Loan Distribution:"),
      plotOutput("loan_plot"),
      h4("Key Metrics:"),
      verbatimTextOutput("key_metrics"),
      h4("Year Trends:"),
      plotOutput("year_plot")
    )
  )
)

# Define server
server <- function(input, output, session) {
  # Initialize connection
  con <- get_connection()
  mortgage_data <- tbl(con, "HOME_MORTGAGE_DISCLOSURE_ATTRIBUTES")

  # Close connection when session ends
  onStop(function() {
    dbDisconnect(con)
  })

  # Reactive filtered data
  filtered_data <- reactive({
    # Extract slider values into local variables before using in filter
    min_amount <- input$loan_amount[1]
    max_amount <- input$loan_amount[2]

    data <- mortgage_data %>%
      filter(LOAN_AMOUNT >= min_amount,
             LOAN_AMOUNT <= max_amount)

    if (input$loan_type != "All") {
      data <- data %>% filter(LOAN_TYPE == input$loan_type)
    }

    if (input$state != "All") {
      data <- data %>% filter(STATE_NAME == input$state)
    }

    # Limit to 10,000 rows for performance
    result <- data %>%
      head(10000) %>%
      collect()

    result
  })

  output$loan_plot <- renderPlot({
    data <- filtered_data()

    if (nrow(data) == 0) {
      plot.new()
      text(0.5, 0.5, "No data available for the selected filters", cex = 1.5)
    } else {
      ggplot(data, aes(x = LOAN_AMOUNT)) +
        geom_histogram(bins = 30, fill = "steelblue", color = "white") +
        scale_x_continuous(labels = label_dollar(scale_cut = cut_short_scale())) +
        theme_minimal() +
        labs(title = paste("Distribution of Loan Amounts (", nrow(data), "loans)"),
             x = "Loan Amount",
             y = "Count")
    }
  })

  output$key_metrics <- renderPrint({
    data <- filtered_data()
    if (nrow(data) == 0) {
      cat("No data available for the selected filters")
    } else {
      total_loans <- nrow(data)
      avg_loan <- mean(data$LOAN_AMOUNT, na.rm = TRUE)
      total_volume <- sum(data$LOAN_AMOUNT, na.rm = TRUE)

      cat(sprintf("Total Loans: %s\\n", format(total_loans, big.mark = ",")))
      cat(sprintf("Average Loan Amount: %s\\n", dollar(avg_loan)))
      cat(sprintf("Total Loan Volume: %s\\n", dollar(total_volume)))
    }
  })

  output$year_plot <- renderPlot({
    data <- filtered_data()

    if (nrow(data) == 0) {
      plot.new()
      text(0.5, 0.5, "No data available for the selected filters", cex = 1.5)
    } else {
      year_summary <- data %>%
        group_by(YEAR) %>%
        summarise(count = n(), .groups = "drop")

      ggplot(year_summary, aes(x = factor(YEAR), y = count)) +
        geom_col(fill = "steelblue") +
        theme_minimal() +
        labs(title = "Loan Count by Year",
             x = "Year",
             y = "Number of Loans") +
        scale_y_continuous(labels = label_comma())
    }
  })
}

# Run the application
shinyApp(ui = ui, server = server)
'

# Write the app code to app.R file
writeLines(app_code, "app.R")

message("Shiny app created successfully!")
message("\nRun the app with: shiny::runApp(\"app.R\")")

Open the new app.R file and click the Run App icon to view and use the Shiny app in the Viewer pane.

Enhance Your Dashboard with Positron Assistant

The code above provided a simple Shiny app with basic functionality and appearance. You can use Positron Assistant with Cortex AI to enhance the dashboard with additional features, better styling, or more complex visualizations.

To start a chat with Positron Assistant, click on the Positron Assistant icon in the toolbar:

Select a model from the available options. For best results with Cortex AI, we recommend using Claude Sonnet 4.5 or later models.

You can ask Positron Assistant to help improve your Shiny app. Try prompts like:

Add a second tab that shows approval rates by state

or

Improve the visual styling of the dashboard with better colors and layout

Continue to make changes to the app with Positron Assistant until you are happy with how it looks and behaves.

Phase 5: Deploy and Share

Step 9: Obtain your Connect API Key

Before we start the process to deploy the dashboard to Connect, you need to create and save an API key.

  1. Access Posit Connect from the Posit Team Snowflake Native App.
  1. Click on your account in the upper right-hand corner of Connect, and then click Manage Your API Keys.

  1. Click + New API Key.

  2. Create a name for your API key, and select the Publisher role permission.

  3. Click Create Key.

  4. Copy the key to somewhere secure. You will need it when deploying your content in the next step.

Step 10: Deploy to Posit Connect

Now that your dashboard works locally and looks how you'd like it to, let's deploy it to Connect so your team can access it. Deployment is an incredibly simple process. Because Workbench and Connect run within the same Native App, the complex network and authentication challenges are eliminated.

Once you click deploy in Positron, Connect handles dependency management and ensures your code runs successfully as a deployed artifact.

  1. In the Positron tool menu, click the Posit Publisher icon.

  1. Under Deployment, click the Select... dropdown. Since this is the first time we've deployed this content, you'll be prompted to create a new deployment. Select the app.R file to deploy.

  2. Select the Connect deployment or create a new one with the URL: https://connect/.

  1. Enter the API key you created in the steps above.

  2. Select the files to include:

  • app.R
  • renv.lock
  1. Click Integration requests > + > the available Snowflake integration
  1. Click the Deploy your project button.

For more information on the deployment process, see Publishing from VS Code or Positron in the Connect user guide.

Step 11: Access Your Dashboard on Connect

  1. Go back to Connect in the Posit Team Native App. Click the Content tab, and then your deployed dashboard.

  2. Test your deployed dashboard to ensure it's working as expected.

  3. To share your dashboard with others, click the Settings pane in the upper-right-hand corner of the content page.

  4. In the Content URL section, copy the URL.

  5. Share this URL with your team.

Note: Since you added the Snowflake integration to your content before deploying it, your dashboard automatically uses viewer-level authentication. Each user who accesses the dashboard will connect with their own Snowflake credentials, ensuring they only see data they have permission to access.

Conclusion and Resources

Overview

In this guide, we connected securely to Snowflake data using Posit Workbench in the Posit Team Native App, explored the data with Databot powered by Cortex AI, developed a Shiny application using Positron Assistant, and deployed the dashboard to Posit Connect where your team can access it securely.

The steps we took along the way easily transfer to other datasets and use cases. This pattern of combining Snowflake's data platform and Cortex AI with Posit's authoring and publishing tools enables you to build and share powerful data applications quickly.

What You Learned

  • How to access Snowflake public datasets using Workbench, Positron, and R
  • How to use Databot with Snowflake Cortex AI for exploratory data analysis
  • How to build with multiple environments in mind using connection code that works seamlessly in Workbench and Connect
  • How to implement viewer-level authentication to ensure each user connects to Snowflake with their own credentials
  • How to create interactive Shiny dashboards with dynamic filters and visualizations for data exploration
  • How to publish Shiny applications to Connect with one-click deployment from Workbench

Resources

Updated 2026-04-07

This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances