QueryChat+Snowflake Loan Data demo

README.md

Shiny for Python Loan Analysis Dashboard - Snowflake

A Python Shiny web application for analyzing loan data with interactive visualizations and state-level statistics.

This dashboard features an LLM chat sidebar powered by querychat that allows the viewer to ask questions to dynamically generate SQL sent to Snowflake using ibis and SQLAlchemy to find results.

Overview

This dashboard provides an interactive interface to explore and analyze Lending Club loan data. It features:

• Natural Language Filtering: Filter using natural language, right in the sidebar!
• Overview Analytics: Key metrics including total loans, average interest rates, and loan distributions
• State Analysis: Geographic analysis of loan patterns across US states
• Detailed Data Views: Comprehensive loan data tables with filtering capabilities

Features

📊 Overview Tab

• Key Metrics: Total loans, average interest rate, and total loan amount
• Loan Amount Distribution: Histogram showing the distribution of loan amounts
• Loan Status Distribution: Pie chart showing the breakdown of loan statuses

🗺️ State Analysis Tab

• Average Loan Amount by State: Bar chart showing top 15 states by average loan amount
• Loan Count by State: Bar chart showing top 15 states by number of loans
• State Statistics Table: Detailed statistics for all states including averages and totals

📋 Loan Details Tab

• Filterable Data Table: Complete loan dataset with interactive filtering
• Export Capabilities: View and analyze individual loan records

Querychat Filters!

Use the querychat sidebar to filter data, or get summary statistics!

Installation

1. Setup the venv environment:

Using uv (recommended), installer available at Installing uv:

uv venv
. .venv/bin/activate
uv pip install -r requirements.txt

Using the regular venv:

python -m venv .venv
. .venv/bin/activate
pip install -r requirements.txt

2. Ensure you have access to the following table in Snowflake LENDING_CLUB.PUBLIC.LOAN_DATA under the duloftf-posit-software-pbc-dev account.
3. Run on Posit Workbench with Snowflake managed credentials or deploy on Posit Connect with Snowflake OAuth activated.

Development

Project Structure

shiny-python-querychat/
├── app.py                 # Main Shiny application
├── pyproject.toml         # Project configuration and dependencies
├── requirements.txt       # Pinned dependencies for Posit Connect deployment
├── uv.lock               # Locked dependency versions
├── data_dictionary.md    # Data field descriptions
├── greeting.md           # Welcome documentation
└── README.md             # This file

Data Source

The application uses the Lending Club dataset from Plotly's public datasets: https://raw.githubusercontent.com/plotly/datasets/master/loan_data.csv

Usage Tips

1. Start with Overview: Begin by exploring the overview tab to understand the overall loan portfolio
2. Apply Filters: Use the sidebar filters to focus on specific loan segments
3. State Analysis: Examine geographic patterns in the State Analysis tab
4. Detailed Exploration: Use the Loan Details tab for granular data analysis
5. Interactive Elements: All charts and tables update automatically when filters are changed

app.py

from shiny import App, reactive, render, ui
import ibis
from pathlib import Path
import querychat
import faicons
import plotly.express as px
from shinywidgets import output_widget, render_widget

import snowflake_config

TABLE_NAME = "LOAN_DATA_FOCUSED"

with open(Path(__file__).parent / "greeting.md", "r") as f:
    greeting = f.read()
with open(Path(__file__).parent / "data_description.md", "r") as f:
    data_desc = f.read()

qc = querychat.QueryChat(
    None,
    table_name=TABLE_NAME,
    tools=("query", "update", "visualize"),
    greeting=greeting,
    data_description=data_desc
)


def app_ui(request):
    return ui.page_navbar(
        ui.nav_spacer(),
        ui.nav_panel(
            "Overview",
            ui.layout_column_wrap(
                ui.value_box(
                    "Total Loans",
                    ui.output_text("total_loans"),
                    showcase=faicons.icon_svg("money-bill-wave"),
                ),
                ui.value_box(
                    "Average Interest Rate",
                    ui.output_text("avg_interest"),
                    showcase=faicons.icon_svg("percent"),
                ),
                ui.value_box(
                    "Total Loan Amount",
                    ui.output_text("total_amount"),
                    showcase=faicons.icon_svg("dollar-sign"),
                ),
                width=1 / 3,
                fill=False,
            ),
            ui.layout_columns(
                ui.card(
                    ui.card_header("Loan Amount Distribution"),
                    output_widget("loan_dist_plot"),
                    full_screen=True,
                ),
                ui.card(
                    ui.card_header("Loan Status Distribution"),
                    output_widget("LOAN_STATUS_plot"),
                    full_screen=True,
                ),
                fill=True,
            ),
        ),
        ui.nav_panel(
            "State Analysis",
            ui.layout_columns(
                ui.card(
                    ui.card_header("Average Loan Amount by State"),
                    output_widget("state_loan_plot"),
                    full_screen=True,
                ),
                ui.card(
                    ui.card_header("Loan Count by State"),
                    output_widget("state_count_plot"),
                    full_screen=True,
                ),
            ),
            ui.card(
                ui.card_header("State Statistics"),
                ui.output_data_frame("state_stats"),
                full_screen=True,
            ),
        ),
        ui.nav_panel(
            "Loan Details",
            ui.card(ui.card_header("Loan Data"), ui.output_data_frame("loan_data")),
        ),
        sidebar=qc.sidebar(),
        title="Loan Data Explorer with QueryChat",
        fillable=True,
    )


def server(input, output, session):
    # Establish Ibis connection to Snowflake
    conn = ibis.snowflake.from_connection(
        snowflake_config.get_connection(),
        create_object_udfs=False,
    )

    # Source the QueryChat server logic (giving access to reactive values)
    qc_vals = qc.server(
        data_source=conn.table(TABLE_NAME),
        client=snowflake_config.chat_client(),
        enable_bookmarking=True,
    )

    # print(qc.generate_greeting())

    # Get the filtered loan table (as an Ibis table)
    @reactive.Calc
    def loan_table():
        return qc_vals.df()

    # Calculate state-level statistics using Ibis
    @reactive.Calc
    def state_statistics():
        table = loan_table()

        # Group by state and calculate statistics using Ibis
        state_stats = (
            table.group_by("ADDR_STATE")
            .aggregate(
                avg_loan_amount=table["LOAN_AMNT"].mean(),
                total_loan_amount=table["LOAN_AMNT"].sum(),
                loan_count=table.count(),
                avg_interest_rate=table["INT_RATE"].mean(),
            )
            .order_by(ibis.desc("loan_count"))
        )

        # Convert to pandas for display
        state_stats_df = state_stats.to_pandas()

        # Rename columns for display
        state_stats_df.columns = [
            "State",
            "Avg Loan Amount",
            "Total Loan Amount",
            "Loan Count",
            "Avg Interest Rate",
        ]

        # Format the columns
        state_stats_df["Avg Loan Amount"] = state_stats_df["Avg Loan Amount"].round(0)
        state_stats_df["Total Loan Amount"] = state_stats_df["Total Loan Amount"].round(
            0
        )
        state_stats_df["Avg Interest Rate"] = state_stats_df["Avg Interest Rate"].round(
            2
        )

        return state_stats_df

    # Overview tab outputs
    @render.text
    def total_loans():
        # Use Ibis to count total loans
        table = loan_table()
        total = table.count().to_pandas()
        return f"{total:,}"

    @render.text
    def avg_interest():
        # Use Ibis to calculate average interest rate
        table = loan_table()
        avg_rate = table["INT_RATE"].mean().to_pandas()
        return f"{avg_rate:.2f}%"

    @render.text
    def total_amount():
        # Use Ibis to calculate total loan amount
        table = loan_table()
        total_amt = table["LOAN_AMNT"].sum().to_pandas()
        return f"${total_amt / 1_000_000:,.0f} MM"

    @render_widget
    def loan_dist_plot():
        # For plotting, we'll get a sample of data to avoid memory issues
        table = loan_table()
        sample_data = table.select("LOAN_AMNT").to_pandas()

        ax = px.histogram(sample_data["LOAN_AMNT"], nbins=12, template="simple_white")
        ax = ax.update_layout(
            yaxis_title="Count",
            xaxis_title="Loan Amount($)",
            showlegend=False
        )
        return ax

    @render_widget
    def LOAN_STATUS_plot():
        # Use Ibis to get loan status counts
        table = loan_table()
        status_counts = (
            table.group_by("LOAN_STATUS")
            .aggregate(count=table.count())
            .order_by(ibis.asc("count"))
            .to_pandas()
        )

        ax = px.bar(status_counts, y="LOAN_STATUS", x="count", template="simple_white")
        ax = ax.update_layout(
            yaxis_title=None,
            xaxis_title="Loan count",
        )
        ax = ax.update_xaxes(autotickangles=[0, 15, 30, 45, 60, 90])

        return ax

    # State Analysis tab outputs
    @render_widget
    def state_loan_plot():
        stats = state_statistics()

        # Get top 15 states by loan count for better visualization
        top_states = stats.head(15)

        ax = px.bar(top_states, x="State", y="Avg Loan Amount", template="simple_white")
        ax = ax.update_xaxes(autotickangles=[0, 15, 30, 45, 60, 90])

        return ax

    @render_widget
    def state_count_plot():
        stats = state_statistics()

        # Get top 15 states by loan count
        top_states = stats.head(15)

        ax = px.bar(top_states, x="State", y="Loan Count", template="simple_white")
        ax = ax.update_xaxes(autotickangles=[0, 15, 30, 45, 60, 90])

        return ax

    @render.data_frame
    def state_stats():
        return render.DataGrid(state_statistics())

    # Loan Details tab output
    @render.data_frame
    def loan_data():
        # For the data grid, show a sample to avoid performance issues
        table = loan_table()
        sample_data = table.limit(1000).to_pandas()
        return render.DataGrid(sample_data)


app = App(app_ui, server, bookmark_store="url")

data_decription.md

Lending Club Data Dictionary

Basic Loan Information

• ID: Unique identifier for the loan
• MEMBER_ID: Unique identifier for the borrower
• LOAN_AMNT: The listed amount of the loan applied for by the borrower
• FUNDED_AMNT: The total amount committed to the loan
• FUNDED_AMNT_INV: The total amount committed by investors for the loan
• TERM: The number of payments on the loan (36 months or 60 months)
• INT_RATE: Interest rate on the loan (percentage)
• INSTALLMENT: The monthly payment owed by the borrower
• GRADE: Loan grade assigned by Lending Club (A to G)
• SUB_GRADE: Loan subgrade assigned by Lending Club (A1 to G5)
• URL: URL for the Lending Club listing

Borrower Information

• EMP_TITLE: The job title provided by the borrower
• EMP_LENGTH: Employment length in years (< 1 year to 10+ years)
• HOME_OWNERSHIP: The home ownership status (RENT, OWN, MORTGAGE, ANY)
• ANNUAL_INC: The self-reported annual income provided by the borrower
• VERIFICATION_STATUS: Indicates if income was verified (Verified, Source Verified, Not Verified)
• ADDR_STATE: The state provided by the borrower in the loan application
• ZIP_CODE: The first 3 numbers of the zip code provided by the borrower

Loan Status and Dates

• ISSUE_D: The month and year the loan was funded
• LOAN_STATUS: Current status of the loan (Current, Fully Paid, Charged Off, etc.)
• PYMNT_PLAN: Indicates if the borrower is on a payment plan (Y/N)
• EARLIEST_CR_LINE: The and year of the borrower's earliest reported credit line was opened
• LAST_PYMNT_D: Last month and year a payment was received
• NEXT_PYMNT_D: Next scheduled payment date
• LAST_CREDIT_PULL_D: The most recent month and year when Lending Club pulled credit for this loan

Loan Purpose and Description

• TITLE: The loan title provided by the borrower
• PURPOSE: The purpose category selected by the borrower (debt_consolidation, credit_card, etc.)
• DESC: Loan description provided by the borrower

Credit Information

• DTI: Debt-to-income ratio calculated using the borrower's total monthly debt payments divided by self-reported monthly income
• DELINQ_2YRS: Number of 30+ days past-due incidences of delinquency in the past 2 years
• FICO_RANGE_LOW: The lower boundary of the borrower's FICO range at loan origination
• FICO_RANGE_HIGH: The upper boundary of the borrower's FICO range at loan origination
• INQ_LAST_6MTHS: Number of credit inquiries in past 6 months
• MTHS_SINCE_LAST_DELINQ: Number of months since the borrower's last delinquency
• MTHS_SINCE_LAST_RECORD: Number of months since the last public record
• OPEN_ACC: Number of open credit lines in the borrower's credit file
• PUB_REC: Number of derogatory public records
• REVOL_BAL: Total credit revolving balance
• REVOL_UTIL: Revolving line utilization rate, or the amount of credit the borrower is using relative to all available revolving credit
• TOTAL_ACC: The total number of credit lines in the borrower's credit file

Payment History

• OUT_PRNCP: Remaining outstanding principal for total amount funded
• OUT_PRNCP_INV: Remaining outstanding principal for portion of total amount funded by investors
• TOTAL_PYMNT: Payments received to date for total amount funded
• TOTAL_PYMNT_INV: Payments received to date for portion of total amount funded by investors
• TOTAL_REC_PRNCP: Principal received to date
• TOTAL_REC_INT: Interest received to date
• TOTAL_REC_LATE_FEE: Late fees received to date
• RECOVERIES: Post charge off gross recovery
• COLLECTION_RECOVERY_FEE: Post charge off collection fee
• LAST_PYMNT_AMNT: Last total payment amount received
• LAST_PYMNT_D: Month and year of the last payment

gistfile1.txt

# Shiny for Python Loan Analysis Dashboard - Snowflake

A Python Shiny web application for analyzing loan data with interactive visualizations and state-level statistics.

This dashboard features an LLM chat sidebar powered by [querychat](https://github.com/posit-dev/querychat) that allows the viewer to ask questions to dynamically generate SQL sent to Snowflake using ibis and SQLAlchemy to find results.

This application is [deployed on pub.current.posit.team](https://pub.current.posit.team/shiny-python-querychat-snowflake/).

## Overview

This dashboard provides an interactive interface to explore and analyze Lending Club loan data. It features:

- **Natural Language Filtering**: Filter using natural language, right in the sidebar!
- **Overview Analytics**: Key metrics including total loans, average interest rates, and loan distributions
- **State Analysis**: Geographic analysis of loan patterns across US states
- **Detailed Data Views**: Comprehensive loan data tables with filtering capabilities

## Features

### 📊 Overview Tab
- **Key Metrics**: Total loans, average interest rate, and total loan amount
- **Loan Amount Distribution**: Histogram showing the distribution of loan amounts
- **Loan Status Distribution**: Pie chart showing the breakdown of loan statuses

### 🗺️ State Analysis Tab
- **Average Loan Amount by State**: Bar chart showing top 15 states by average loan amount
- **Loan Count by State**: Bar chart showing top 15 states by number of loans
- **State Statistics Table**: Detailed statistics for all states including averages and totals

### 📋 Loan Details Tab
- **Filterable Data Table**: Complete loan dataset with interactive filtering
- **Export Capabilities**: View and analyze individual loan records

### Querychat Filters!
Use the querychat sidebar to filter data, or get summary statistics! 

## Installation

1. Setup the `venv` environment:

Using `uv` (recommended), installer available at [Installing uv](https://docs.astral.sh/uv/getting-started/installation/):
```bash
uv venv
. .venv/bin/activate
uv pip install -r requirements.txt
```

Using the regular `venv`:
```bash
python -m venv .venv
. .venv/bin/activate
pip install -r requirements.txt
```

2. Ensure you have access to the following table in Snowflake `LENDING_CLUB.PUBLIC.LOAN_DATA` under the `duloftf-posit-software-pbc-dev` account.
3. Run on Posit Workbench with Snowflake managed credentials or deploy on Posit Connect with Snowflake OAuth activated.

## Development

### Project Structure
```
shiny-python-querychat/
├── app.py                 # Main Shiny application
├── pyproject.toml         # Project configuration and dependencies
├── requirements.txt       # Pinned dependencies for Posit Connect deployment
├── uv.lock               # Locked dependency versions
├── data_dictionary.md    # Data field descriptions
├── greeting.md           # Welcome documentation
└── README.md             # This file
```

### Data Source
The application uses the Lending Club dataset from Plotly's public datasets:
`https://raw.githubusercontent.com/plotly/datasets/master/loan_data.csv`

## Usage Tips

1. **Start with Overview**: Begin by exploring the overview tab to understand the overall loan portfolio
2. **Apply Filters**: Use the sidebar filters to focus on specific loan segments
3. **State Analysis**: Examine geographic patterns in the State Analysis tab
4. **Detailed Exploration**: Use the Loan Details tab for granular data analysis
5. **Interactive Elements**: All charts and tables update automatically when filters are changed

greeting.md

Hello! 👋 Welcome to your Lending Club loan dashboard. I can help you filter, explore, analyze, and visualize your loan data with just a few clicks. Here are some ideas to get you started:

🔍 Filter & Explore

• Show only small business loans
• Show only loans where borrowers are most likely to default
• Filter to the riskiest loan purposes for lenders

❓ Answer Questions

• Do longer loan terms lead to more missed payments?
• Are the highest-risk borrowers charged high enough interest rates to justify the risk?
• How much interest do lenders actually collect compared to what they expect?

📊 Visualize

• Show a density plot of interest rates for 36-month vs 60-month loans
• Show a ridgeline plot of loan amount distribution by loan grade

Let me know what you’d like to do next!

pyproject.toml

[project]
name = "shiny-python-querychat"
version = "0.1.0"
description = "Add your description here"
readme = "README.md"
requires-python = ">=3.11"
dependencies = [
    "chatlas[snowflake]",
    "faicons",
    "ibis-framework[snowflake]",
    "narwhals>=1.41",
    "plotly",
    "plotnine>=0.15.3",
    "polars",
    "posit-sdk",
    "querychat[viz]>=0.6",
    "shiny",
    "shinywidgets",
]

[tool.uv]
dev-dependencies = [
    "ruff>=0.11.11",
]

requirements.txt

# This file was autogenerated by uv via the following command:
#    uv pip compile pyproject.toml -o ./requirements.txt
absl-py==1.4.0
    # via snowflake-ml-python
aiobotocore==2.26.0
    # via s3fs
aiohappyeyeballs==2.6.1
    # via aiohttp
aiohttp==3.13.5
    # via
    #   aiobotocore
    #   fsspec
    #   s3fs
aioitertools==0.13.0
    # via aiobotocore
aiosignal==1.4.0
    # via aiohttp
altair==6.1.0
    # via
    #   ggsql
    #   querychat
annotated-types==0.7.0
    # via pydantic
anyio==4.13.0
    # via
    #   httpx
    #   openai
    #   snowflake-ml-python
    #   starlette
    #   watchfiles
anywidget==0.11.0
    # via shinywidgets
asgiref==3.11.1
    # via shiny
asn1crypto==1.5.1
    # via snowflake-connector-python
asttokens==3.0.1
    # via stack-data
atpublic==7.0.0
    # via ibis-framework
attrs==26.1.0
    # via
    #   aiohttp
    #   jsonschema
    #   referencing
babel==2.18.0
    # via great-tables
boto3==1.41.5
    # via snowflake-connector-python
botocore==1.41.5
    # via
    #   aiobotocore
    #   boto3
    #   s3transfer
    #   snowflake-connector-python
cachetools==5.5.2
    # via snowflake-ml-python
certifi==2026.4.22
    # via
    #   httpcore
    #   httpx
    #   requests
    #   snowflake-connector-python
    #   snowflake-core
cffi==1.17.1
    # via
    #   cryptography
    #   snowflake-connector-python
charset-normalizer==3.4.7
    # via
    #   requests
    #   snowflake-connector-python
chatlas==0.16.0
    # via
    #   shiny-python-querychat (pyproject.toml)
    #   querychat
chevron==0.14.0
    # via querychat
click==8.3.3
    # via
    #   shiny
    #   uvicorn
cloudpickle==3.1.1
    # via
    #   shap
    #   snowflake-ml-python
    #   snowflake-snowpark-python
comm==0.2.3
    # via ipywidgets
commonmark==0.9.1
    # via great-tables
cryptography==45.0.7
    # via
    #   pyopenssl
    #   snowflake-connector-python
    #   snowflake-ml-python
decorator==5.2.1
    # via ipython
distro==1.9.0
    # via openai
duckdb==1.5.2
    # via querychat
executing==2.2.1
    # via stack-data
faicons==0.2.2
    # via
    #   shiny-python-querychat (pyproject.toml)
    #   great-tables
filelock==3.29.0
    # via snowflake-connector-python
frozenlist==1.8.0
    # via
    #   aiohttp
    #   aiosignal
fsspec==2025.12.0
    # via
    #   s3fs
    #   snowflake-ml-python
ggsql==0.3.1
    # via querychat
great-tables==0.21.0
    # via querychat
greenlet==3.5.0
    # via sqlalchemy
h11==0.16.0
    # via
    #   httpcore
    #   uvicorn
htmltools==0.6.0
    # via
    #   faicons
    #   great-tables
    #   querychat
    #   shiny
    #   shinychat
httpcore==1.0.9
    # via httpx
httpx==0.28.1
    # via openai
ibis-framework==12.0.0
    # via shiny-python-querychat (pyproject.toml)
idna==3.13
    # via
    #   anyio
    #   httpx
    #   requests
    #   snowflake-connector-python
    #   yarl
importlib-metadata==8.7.1
    # via
    #   great-tables
    #   opentelemetry-api
importlib-resources==6.5.2
    # via
    #   great-tables
    #   snowflake-ml-python
ipython==9.13.0
    # via ipywidgets
ipython-pygments-lexers==1.1.1
    # via ipython
ipywidgets==8.1.8
    # via
    #   anywidget
    #   shinywidgets
jedi==0.19.2
    # via ipython
jinja2==3.1.6
    # via
    #   altair
    #   chatlas
jiter==0.14.0
    # via openai
jmespath==1.1.0
    # via
    #   aiobotocore
    #   boto3
    #   botocore
joblib==1.5.3
    # via scikit-learn
jsonschema==4.26.0
    # via altair
jsonschema-specifications==2025.9.1
    # via jsonschema
jupyter-core==5.9.1
    # via shinywidgets
jupyterlab-widgets==3.0.16
    # via ipywidgets
linkify-it-py==2.1.0
    # via shiny
llvmlite==0.47.0
    # via numba
markdown-it-py==4.0.0
    # via
    #   mdit-py-plugins
    #   rich
    #   shiny
markupsafe==3.0.3
    # via jinja2
matplotlib-inline==0.2.1
    # via ipython
mdit-py-plugins==0.5.0
    # via shiny
mdurl==0.1.2
    # via markdown-it-py
multidict==6.7.1
    # via
    #   aiobotocore
    #   aiohttp
    #   yarl
narwhals==2.20.0
    # via
    #   shiny-python-querychat (pyproject.toml)
    #   altair
    #   ggsql
    #   plotly
    #   querychat
    #   shiny
numba==0.65.1
    # via shap
numpy==1.26.4
    # via
    #   ibis-framework
    #   numba
    #   pandas
    #   scikit-learn
    #   scipy
    #   shap
    #   snowflake-ml-python
    #   xgboost
nvidia-nccl-cu12==2.30.4
    # via xgboost
openai==2.33.0
    # via chatlas
opentelemetry-api==1.41.1
    # via shiny
orjson==3.11.8
    # via
    #   chatlas
    #   shiny
packaging==24.2
    # via
    #   altair
    #   htmltools
    #   plotly
    #   posit-sdk
    #   shap
    #   shiny
    #   snowflake-connector-python
    #   snowflake-ml-python
    #   wheel
pandas==2.3.3
    # via
    #   ibis-framework
    #   shap
    #   snowflake-connector-python
    #   snowflake-ml-python
parso==0.8.6
    # via jedi
parsy==2.2
    # via ibis-framework
pexpect==4.9.0
    # via ipython
platformdirs==4.9.6
    # via
    #   jupyter-core
    #   shiny
    #   snowflake-connector-python
plotly==6.7.0
    # via shiny-python-querychat (pyproject.toml)
polars==1.40.1
    # via shiny-python-querychat (pyproject.toml)
polars-runtime-32==1.40.1
    # via polars
posit-sdk==0.16.1
    # via shiny-python-querychat (pyproject.toml)
prompt-toolkit==3.0.52
    # via
    #   ipython
    #   questionary
    #   shiny
propcache==0.4.1
    # via
    #   aiohttp
    #   yarl
protobuf==6.33.6
    # via snowflake-snowpark-python
psutil==7.2.2
    # via ipython
psygnal==0.15.1
    # via anywidget
ptyprocess==0.7.0
    # via pexpect
pure-eval==0.2.3
    # via stack-data
pyarrow==24.0.0
    # via
    #   ggsql
    #   ibis-framework
    #   snowflake-connector-python
    #   snowflake-ml-python
pyarrow-hotfix==0.7
    # via ibis-framework
pycparser==3.0
    # via cffi
pydantic==2.13.3
    # via
    #   chatlas
    #   openai
    #   snowflake-core
    #   snowflake-ml-python
pydantic-core==2.46.3
    # via pydantic
pygments==2.20.0
    # via
    #   ipython
    #   ipython-pygments-lexers
    #   rich
pyjwt==2.12.1
    # via
    #   snowflake-connector-python
    #   snowflake-ml-python
pyopenssl==25.3.0
    # via snowflake-connector-python
python-dateutil==2.9.0.post0
    # via
    #   aiobotocore
    #   botocore
    #   ibis-framework
    #   pandas
    #   shinywidgets
    #   snowflake-core
    #   snowflake-snowpark-python
python-multipart==0.0.27
    # via shiny
pytimeparse==1.1.8
    # via snowflake-ml-python
pytz==2026.1.post1
    # via
    #   pandas
    #   snowflake-connector-python
pyyaml==6.0.3
    # via
    #   snowflake-core
    #   snowflake-ml-python
    #   snowflake-snowpark-python
querychat==0.6.0
    # via shiny-python-querychat (pyproject.toml)
questionary==2.1.1
    # via shiny
referencing==0.37.0
    # via
    #   jsonschema
    #   jsonschema-specifications
requests==2.33.1
    # via
    #   chatlas
    #   posit-sdk
    #   snowflake-connector-python
    #   snowflake-core
retrying==1.4.2
    # via snowflake-ml-python
rich==15.0.0
    # via
    #   chatlas
    #   ibis-framework
rpds-py==0.30.0
    # via
    #   jsonschema
    #   referencing
s3fs==2025.12.0
    # via snowflake-ml-python
s3transfer==0.15.0
    # via boto3
scikit-learn==1.5.2
    # via
    #   shap
    #   snowflake-ml-python
scipy==1.17.1
    # via
    #   scikit-learn
    #   shap
    #   snowflake-ml-python
    #   xgboost
setuptools==82.0.1
    # via
    #   shiny
    #   snowflake-snowpark-python
shap==0.49.1
    # via snowflake-ml-python
shiny==1.6.1
    # via
    #   shiny-python-querychat (pyproject.toml)
    #   querychat
    #   shinychat
    #   shinywidgets
shinychat==0.3.1
    # via
    #   querychat
    #   shiny
shinywidgets==0.8.0
    # via
    #   shiny-python-querychat (pyproject.toml)
    #   querychat
six==1.17.0
    # via python-dateutil
slicer==0.0.8
    # via shap
sniffio==1.3.1
    # via openai
snowflake-connector-python==3.18.0
    # via
    #   ibis-framework
    #   snowflake-core
    #   snowflake-ml-python
    #   snowflake-snowpark-python
snowflake-core==1.12.0
    # via snowflake-ml-python
snowflake-ml-python==1.9.0
    # via chatlas
snowflake-snowpark-python==1.50.0
    # via snowflake-ml-python
sortedcontainers==2.4.0
    # via snowflake-connector-python
sqlalchemy==2.0.49
    # via querychat
sqlglot==30.6.0
    # via ibis-framework
sqlparse==0.5.5
    # via snowflake-ml-python
stack-data==0.6.3
    # via ipython
starlette==1.0.0
    # via shiny
threadpoolctl==3.6.0
    # via scikit-learn
tomlkit==0.14.0
    # via snowflake-connector-python
toolz==1.1.0
    # via ibis-framework
tqdm==4.67.3
    # via
    #   openai
    #   shap
traitlets==5.14.3
    # via
    #   ipython
    #   ipywidgets
    #   jupyter-core
    #   matplotlib-inline
typing-extensions==4.15.0
    # via
    #   altair
    #   anywidget
    #   great-tables
    #   htmltools
    #   ibis-framework
    #   openai
    #   opentelemetry-api
    #   posit-sdk
    #   pydantic
    #   pydantic-core
    #   shap
    #   shiny
    #   snowflake-connector-python
    #   snowflake-ml-python
    #   snowflake-snowpark-python
    #   sqlalchemy
    #   typing-inspection
typing-inspection==0.4.2
    # via pydantic
tzdata==2026.2
    # via
    #   ibis-framework
    #   pandas
tzlocal==5.3.1
    # via snowflake-snowpark-python
uc-micro-py==2.0.0
    # via linkify-it-py
urllib3==2.6.3
    # via
    #   botocore
    #   requests
    #   snowflake-core
uvicorn==0.46.0
    # via shiny
vl-convert-python==1.9.0.post1
    # via querychat
watchfiles==1.1.1
    # via shiny
wcwidth==0.6.0
    # via prompt-toolkit
websockets==16.0
    # via shiny
wheel==0.47.0
    # via snowflake-snowpark-python
widgetsnbextension==4.0.15
    # via ipywidgets
wrapt==1.17.3
    # via aiobotocore
xgboost==2.1.4
    # via snowflake-ml-python
yarl==1.23.0
    # via aiohttp
zipp==3.23.1
    # via importlib-metadata

snowflake_config.py

import os
from pathlib import Path

import chatlas
import snowflake.connector

from shiny import session
from posit.connect.external.snowflake import PositAuthenticator

# A connection name within ~/.snowflake/connections.toml
CONNECTION_NAME = "workbench"

# Default Snowflake account
ACCOUNT = "duloftf-posit-software-pbc-staging"

# Default Snowflake parameters
WAREHOUSE = "DEFAULT_WH"
DATABASE = "LENDING_CLUB"
SCHEMA = "PUBLIC"

# A model name supported by Snowflake
MODEL = "claude-sonnet-4-6"


def chat_client():
    kwargs = {}

    if is_connect():
        auth = get_connect_auth()
        kwargs["authenticator"] = auth.authenticator
        kwargs["token"] = auth.token
    else:
        if not has_local_config():
            raise ValueError(
                "No Snowflake configuration found. Please set up "
                "~/.snowflake/connections.toml with the connection details."
            )
        kwargs["connection_name"] = CONNECTION_NAME

    return chatlas.ChatSnowflake(
        model=MODEL,
        account=ACCOUNT,
        kwargs=kwargs
    )


def get_connection():
    """Get a Snowflake connection based on the environment."""
    if is_connect():
        auth = get_connect_auth()
        return snowflake.connector.connect(
            account=ACCOUNT,
            warehouse=WAREHOUSE,
            database=DATABASE,
            schema=SCHEMA,
            authenticator=auth.authenticator,
            token=auth.token,
        )

    if not has_local_config():
        raise ValueError(
            "No Snowflake configuration found. Please set up "
            "~/.snowflake/connections.toml with the connection details."
        )

    return snowflake.connector.connect(
        connection_name=CONNECTION_NAME,
        warehouse=WAREHOUSE,
        database=DATABASE,
        schema=SCHEMA,
    )


def get_connect_auth():
    """Get Posit Connect Snowflake authenticator."""
    sess = session.get_current_session()
    if sess is None:
        raise RuntimeError("get_connect_auth() must be called within a Shiny session")

    # No-op for (1st run of) Express sessions
    if sess.is_stub_session():
        return None

    user_session_token = sess.http_conn.headers.get(
        "Posit-Connect-User-Session-Token"
    )
    return PositAuthenticator(
        local_authenticator="EXTERNALBROWSER",
        user_session_token=user_session_token,
    )


def is_connect():
    """Check if the app is running on Posit Connect."""
    return os.getenv("RSTUDIO_PRODUCT") == "CONNECT"


def has_local_config():
    home = Path(os.getenv("SNOWFLAKE_HOME", "~/.snowflake")).expanduser()
    config_path = home / "connections.toml"
    return config_path.exists()