In this guide, we'll build a Shiny for Python dashboard over Home Mortgage Disclosure Act (HMDA) data. When designing a dashboard you are constrained by space and it can be hard to answer every possible question a stakeholder might have. This is why we will show how to integrate an AI powered chat into the dashboard, where anyone viewing it can ask questions in plain English and get back tables and charts. The dashboard doesn't ask a model to guess at answers. It asks the model to write SQL, runs that SQL in Snowflake, and shows both the result and the query that produced it.

The benefit of this cannot be overstated. Letting the AI agent produce a SQL query as an artifact is exactly what we want. This query can be saved, rerun, and modified at will without having to reprompt the agent. It also keeps the heavy lifting where your data already lives.

We'll develop in Posit Workbench inside the Posit Team Native App, use Snowflake Cortex AI as the model backend, and publish to Posit Connect. Because the deployed content carries a Snowflake OAuth integration, each viewer's questions execute under their own Snowflake identity, so the dashboard respects the grants you've already configured. Data, compute, and inference all stay inside your Snowflake account.

At a high level, the pieces fit together like this. Everything sits inside the Snowflake account boundary: you develop in Workbench, the deployed dashboard runs on Connect, and both reach the same databases and the same Cortex models.

Access and entitlements that have to be in place before you start, most of which an administrator grants:

The working environment you'll set up yourself in the first two sections of this guide:

We'll use the Home Mortgage Disclosure Act (HMDA) dataset from Snowflake's free public data. It contains mortgage application records covering loan types, applicant demographics, property characteristics, amounts, and outcomes across U.S. geographies.

The HMDA dataset we'll use is located at:

To verify you have access to this data, navigate to Snowsight and click + > SQL File and run:

You should see ten rows of mortgage application data.

In Snowsight, navigate to Horizon Catalog > Catalog > Apps > Posit Team, then click Launch app.

From within the Posit Team Native App, click Posit Workbench. You might be prompted to sign in to Snowflake.

Workbench offers several IDEs for data science work. For this guide, we'll use Positron Pro, the data science IDE for Python and R.

Click + New Session and select the Positron Pro IDE.

Under Session Credentials, click the button with the Snowflake icon, complete the sign-in prompts, then click Launch.

Your session now holds managed credentials derived from the Snowflake identity you signed in with, and that one identity covers both querying the mortgage table and calling Cortex. You only have to manage access at the Snowflake level, everything is inherited down into Workbench.

Confirm the Shiny and Posit Publisher extensions are installed and enabled from the Extensions view. Both ship with Positron as bootstrapped extensions.

Clone the example repository into your Workbench session and open quarto.qmd. It walks through connecting to Snowflake, configuring Cortex, and building the dashboard, with each step runnable via the Run Cell button. The repository also contains the finished app.py that this guide builds up to and deploys.

Use Python 3.12 for this guide. The Snowflake packages don't yet publish wheels for the newest Python releases, so a newer interpreter forces pip to compile from source. The repository pins the version in a .python-version file, which Positron picks up when selecting an interpreter for the session; confirm the interpreter shown in the top right of Positron reads 3.12 before installing anything.

Once you are in the repo, you can install the required dependencies.

querychat must be 0.7.0 or newer. Earlier versions require a data source at construction time, and the deployed app builds one per viewer instead.

Remember that the goal of this post is to let the viewers of the dashboard chat with it using their own credentials. We have already gotten those credentials in Workbench for us to use while developing the Shiny app, but the deployed dashboard needs a second path using each viewer's own credentials. We'll come back to it when we assemble the app.

Now wrap the connection with Ibis, which gives us a Pythonic way to work with Snowflake tables:

It is important that we don't call .to_pandas() at this stage. mortgage_data is a lazy reference to a Snowflake table. We don't want the app to pull any more data than necessary.

chatlas is a Python package for talking to LLMs, and its ChatSnowflake provider targets Cortex. Your Workbench session already holds your Snowflake credentials, so pointing it at Cortex takes one call:

Name the model explicitly. ChatSnowflake will pick a default and warn you about it, but a dashboard other people depend on shouldn't change models when that default moves.

It is generally a good idea to test it before wiring it into an app, as it can be harder to debug later on. This way we can surface credential problems in the console rather than getting a blank app.

The querychat package lets us turn questions into SQL. It needs two things: a data source and a model.

That is the whole configuration: the lazy Ibis table from the previous section, the name the model should use for it in SQL, and the Cortex client. Note that we hand over mortgage_data itself, not a sample. querychat sends the model the table's schema, not its rows, and the SQL the model writes runs against the table by name on your connection, so the model can work over the full table without anything being pulled into Python to get there.

We are building the configuration here rather than launching an app, because the app that ships has to create its connection per viewer, and we will assemble it in one piece shortly. Two things are worth adding to QueryChat before then.

querychat also gives you SQL you can read, which is the reason to use it over a black-box text-to-answer service. Every response is backed by a query you can inspect, copy, and rerun.

querychat is not limited to producing SQL queries, it can also produce data visualizations. Charts are not on by default, so we opt in by adding "visualize" to tools.

Behind this is ggsql, an extension of SQL that brings the elegance of the Grammar of Graphics to SQL. The model writes one query that both aggregates and describes the chart, querychat runs the SQL part against Snowflake, and the result is rendered as a chart in the chat.

This stays efficient because all the aggregation happens in the database, and only the handful of summarized rows the chart needs come back to us.

What we have right now works. But we can improve it a bit using our domain knowledge about the data and how we expect users to interact with the app.

Firstly, a data_description gives the model your domain vocabulary and your caveats:

Secondly, a greeting tells a first-time viewer what kinds of questions work, so they aren't staring at an empty text box:

Add these two as additional arguments to QueryChat(), which gives us the configuration the deployed app will use:

If your account defines Snowflake Semantic Views, querychat discovers them automatically on a Snowflake connection and includes their definitions in what it sends the model. The model can then answer using SEMANTIC_VIEW() and your organization's certified metrics instead of inventing its own arithmetic. Nothing extra is required in the app code.

So far we have used our own connection to work with the data. As mentioned above, we need to do something different, so that the deployed app has each viewer use their own credentials and session tokens to query the data.

Our deployable artifact is a self-contained app.py, and what changes is where the connection is created. Everything credential-related moves inside the server function, which Shiny calls once per session with that session's request headers in hand.

First, the imports, a few constants, and the credentials. One function, two environments:

The RuntimeError names the one Connect-side setup step this guide finishes with, attaching the integration. Raising it by name beats the AttributeError you'd otherwise get. SNOWFLAKE_ACCOUNT needs no such check: Connect sets it on the content from the attached integration, so there is nothing for you to configure.

The session argument is not decoration: the viewer's token arrives as an HTTP header on their session, so this function cannot be called at import time, and that constraint shapes the rest of the file.

Second, two helpers that turn those credentials into the table and the chat client. Both take the same creds, so one identity covers the query, the model call, and the rows that come back:

ChatSnowflake accepts connection_name, account, user, password, and private_key_file directly; anything else the underlying Snowpark session needs goes through kwargs. That's how the OAuth authenticator and token reach it on Connect, so inference runs as the viewer too, not just data access.

Third, the app. The UI is built once and shared, since rendering the chat sidebar needs only the module id and the tool list. The data source and chat client are built per session:

That .head(MAX_ROWS) is not cosmetic, and it is the reason we render the table ourselves rather than letting something else do it. vals.df() is a lazy Ibis table: before anyone asks a question it is the entire table, and after a filter it is that filter's result, still unevaluated. The filter tool is also required to return every column of the original schema, so a filtered result is narrower in rows but never in columns. Hand either one straight to render.data_frame and Shiny materializes all of it, so the app renders and then spins while it drags HMDA out of Snowflake a row at a time. Limit before materializing.

Bounding the render is what lets us hand QueryChat the full mortgage_data. The model writes queries against the whole table, aggregates are computed over all of it in the database, and only the rows actually on screen come back.

qc.server() returns that session's reactive values, so vals.df(), vals.sql(), and vals.title() are ordinary Shiny reactives. That's the seam for the value boxes and extra tabs above: they read vals.df() and never touch the connection.

Create app.py next to quarto.qmd and paste these pieces into it, or copy the complete file from the root of the accompanying repository. Run it locally before deploying:

This is the first time we have had something to ask questions of, so it is worth spending a few minutes here before deploying. Which part of the app answers depends on how you phrase the question, and knowing that up front saves confusion.

Start with an analytical question:

The model answers in the chat, showing the query it ran alongside the result. The SQL card and the data table do not change, and that is correct: you asked for a number, not for a different view of the data.

Now ask for a different view:

This time the SQL card and the data table both re-render from the new query. Two different tools sit behind these, and the model chooses between them from your phrasing: "what is", "how many", "which is highest" get answered in the chat, while "show me", "filter to", "sort by" drive the dashboard. If you ask an analytical question and wonder why the SQL card did not change, that is why, and not a bug.

Keep filters reasonably narrow while you are exploring. A filter returns whole rows rather than a summary, so filtering to one of the largest states pulls a lot of data through MAX_ROWS before you see anything.

Finally, ask for a chart, which is the visualize tool we opted into earlier:

The aggregation runs in Snowflake and only the summarized rows come back to be drawn.

1. Open Posit Connect from the Posit Team Native App.

2. Click your account in the upper right, then Manage Your API Keys.
3. Click + New API Key, name it, and select the Publisher role.
4. Click Create Key and copy it somewhere safe.

Because Workbench and Connect run inside the same Native App, publishing avoids the network and authentication problems this step usually involves.

The .python-version file that selected your session interpreter also travels into the deployment, so confirm it sits alongside app.py and reads 3.12. Publisher reads it when generating the deployment manifest, which records >=3.12 as an explicit version constraint rather than just noting whichever interpreter you happened to author with. Connect then picks a matching Python installation. It ships in the repository; if you are adapting this pattern in a project of your own, create it with:

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

2. Under Deployment, click Select... and create a new deployment for app.py.
3. Choose the Connect deployment, or create one with the URL https://connect/.

4. Enter the API key you just created.
5. Include both requirements.txt and .python-version in the file list. .python-version is a hidden file, so it's easy to miss, and leaving it out drops the explicit constraint from the manifest.

6. Click Integration requests > + > the available Snowflake integration.

7. Click Deploy your project.

The app is deployed now, but it won't work yet. One thing has to be set on the content itself, and it doesn't travel in the bundle.

Open the content in Connect, go to Content Settings > Access, and add the Snowflake integration there.

Check that you're attaching the viewer integration rather than a service account one. That choice is what makes the rest of this guide true.

Attaching the integration also sets SNOWFLAKE_ACCOUNT on the content, which is where the app's connection gets its account identifier. You don't have to add it yourself.

Adding an integration restarts the content, so this doesn't need a redeploy. Reload the app and it should come up.

We did it! The app has been deployed correctly. Return to Connect in the Posit Team Native App, click the Content tab, and open your dashboard. Ask a few questions to confirm it behaves the way it did locally.

To share it, open the Settings pane on the content page, copy the URL from the Content URL section, and send it to your team.

Each viewer queries Snowflake with their own credentials. Two people can use the same dashboard and see different results, because Snowflake applies the permissions granted to each viewer. Cortex receives only the data Snowflake authorizes for that viewer.

We've built a dashboard that answers questions in natural language and deployed it somewhere a whole team can use it, without moving data, standing up model infrastructure, or writing a permission layer.

Three properties carry to your next project: the model writes SQL rather than answers, so results can be checked and reused; queries execute in Snowflake, so the dashboard scales with your warehouse instead of your container; and access is delegated to Snowflake, so security is inherited rather than reimplemented. Point the pattern at your own table and the credentials function and data description are the only parts to rewrite.