Snowflake connection patterns in Workbench using key-pair authentication

These patterns enable you to access Snowflake data in Posit Workbench using Key-pair Authentication. They include both Python and R code examples.

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

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

Overview

Key-pair authentication is a secure way to access Snowflake using a public/private key pair. A key pair can be associated with an individual user account or a service account. This pattern is applicable for both user-scoped and one-to-many credentials in Workbench.

Configuration

To use key-pair authentication, you must create a public/private key pair and assign the public key to your Snowflake user or service account. The Snowflake Key Pair Auth Guide provides full details.

To check if a key pair is already associated with your account, run the following in Snowflake:

DESC USER "<your-username>";
SELECT *
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "property" LIKE '%RSA%';

If no key pair exists, a Snowflake administrator must create one and associate the public key with the account.

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.

ImportantAdministrator 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

Specify the path to your private key file in the private_key_file argument. The connection object can be used to execute queries directly or passed to data frame libraries like pandas or polars.

import os
from pathlib import Path

import snowflake.connector

con = snowflake.connector.connect(
    account=os.getenv("SNOWFLAKE_ACCOUNT", "<your-account-here>"),
    user=os.getenv("SNOWFLAKE_USER", "<your-username-here>"),
    private_key_file=Path("~/rsa_key.p8").expanduser(),
    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 os
from pathlib import Path

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

con = snowflake.connector.connect(
    account=os.getenv("SNOWFLAKE_ACCOUNT", "<your-account-here>"),
    user=os.getenv("SNOWFLAKE_USER", "<your-username-here>"),
    private_key_file=Path("~/rsa_key.p8").expanduser(),
    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 os
from pathlib import Path

import pandas as pd
import snowflake.connector

con = snowflake.connector.connect(
    account=os.getenv("SNOWFLAKE_ACCOUNT", "<your-account-here>"),
    user=os.getenv("SNOWFLAKE_USER", "<your-username-here>"),
    private_key_file=Path("~/rsa_key.p8").expanduser(),
    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))

Specify the path to your private key file in the private_key_file argument. Use the connection object returned by DBI::dbConnect() to execute queries directly, or pass it to dplyr. See the Snowflake ODBC Parameters documentation for all available connection options.

library(dplyr)

# Create the connection object.
con <- DBI::dbConnect(
    odbc::snowflake(),
    user = Sys.getenv("SNOWFLAKE_USER", unset = "<your-username>"),
    private_key_file = path.expand("~/rsa_key.p8"),
    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()