Snowflake connection patterns in Workbench using Workbench Managed Credentials

These patterns enable you to access Snowflake data in Posit Workbench using Workbench Managed Credentials. They include both Python and R code examples.

Choose a different pattern by referring to the table in Choose a connection for Snowflake.

Not sure which pattern to use?

For an overview of credential types and scope in different environments, see Credential types and Managing data access from development to deployment.

Overview

Administrator configuration required

Your Workbench administrator must configure Workbench managed credentials for Snowflake. See the Workbench Managed Credentials section of the Data Sources admin guide for instructions.

When authenticating with Workbench managed credentials, your Snowflake user credentials are available in your session via the workbench connection profile that Workbench creates and manages for you. You do not need to manage tokens or credential files. See the Managed Credentials Overview in the Workbench User Guide for more information.

Prerequisites

The connection patterns in this guide use the snowflake-connector-python package to connect to Snowflake. The connector integrates with data frame libraries like pandas and polars.

Install the required Python packages:

uv add snowflake-connector-python

pip install snowflake-connector-python

Think ahead for deploying your code to Connect. If you intend to use Connect Viewer or service account OAuth authentication patterns, you will also need the posit-sdk package:

uv add posit-sdk

pip install posit-sdk

The connection patterns in this guide use odbc and the odbc::snowflake() function to connect to Snowflake via an ODBC driver.

Administrator configuration required

An administrator must install and configure database drivers on the server before you can use them in your code. See the Data sources admin guide for more information.

Install odbc:

install.packages("odbc")

Think ahead for deploying your code to Connect. If you intend to use Connect Viewer or service account OAuth authentication patterns, you will also need the connectcreds package:

install.packages("connectcreds")

Connection patterns

When setting up the Snowflake connection object, specify connection_name="workbench" to tell the snowflake-connector-python package to use the Workbench managed credentials. The connection object can be used to execute queries directly or passed to data frame libraries like pandas or polars.

import snowflake.connector

# Create the connection object.
con = snowflake.connector.connect(
    connection_name="workbench",
    database="LENDING_CLUB",
    schema="PUBLIC",
)

# List all of the tables in the database.
print(con.cursor().execute("SHOW TABLES;").fetchall())

# Execute a query.
query = """
SELECT "ID", "LOAN_AMNT", "TERM", "INT_RATE", "GRADE"
FROM LOAN_DATA
WHERE "LOAN_AMNT" IS NOT NULL
ORDER BY "LOAN_AMNT" DESC
LIMIT 10;
"""

print(con.cursor().execute(query).fetchall())

import polars as pl
import snowflake.connector
# Tip: pandas and pyarrow are required dependencies

# Create the connection object.
con = snowflake.connector.connect(
    connection_name="workbench",
    database="LENDING_CLUB",
    schema="PUBLIC",
)

# List all of the tables in the database.
list_tables_query = """
SELECT *
FROM "LENDING_CLUB"."INFORMATION_SCHEMA"."TABLES";
"""

print(pl.read_database(list_tables_query, connection=con))

# Execute a query.
query = """
SELECT "ID", "LOAN_AMNT", "TERM", "INT_RATE", "GRADE"
FROM LOAN_DATA
WHERE "LOAN_AMNT" IS NOT NULL
ORDER BY "LOAN_AMNT" DESC
LIMIT 10;
"""

print(pl.read_database(query, connection=con))

import pandas as pd
import snowflake.connector

# Create the connection object.
con = snowflake.connector.connect(
    connection_name="workbench",
    database="LENDING_CLUB",
    schema="PUBLIC",
)

# List all of the tables in the database.
print(pd.read_sql_query("SHOW TABLES;", con=con))

# Execute a query.
query = """
SELECT "ID", "LOAN_AMNT", "TERM", "INT_RATE", "GRADE"
FROM LOAN_DATA
WHERE "LOAN_AMNT" IS NOT NULL
ORDER BY "LOAN_AMNT" DESC
LIMIT 10;
"""

print(pd.read_sql_query(query, con=con))

The connection object created by DBI::dbConnect() can be used to execute queries directly or passed to dplyr.

library(dplyr)

# Create the connection object.
con <- DBI::dbConnect(
    odbc::snowflake(),
    database = "LENDING_CLUB",
    schema = "PUBLIC"
)

# List all of the tables in the database.
DBI::dbGetQuery(con, "SHOW TABLES;") |>
    as_tibble()

# Execute a query.
tbl(con, "LOAN_DATA") |>
    select(ID, LOAN_AMNT, TERM, INT_RATE, GRADE) |>
    filter(!is.na(LOAN_AMNT)) |>
    arrange(desc(LOAN_AMNT)) |>
    head(10) |>
    collect()