Score a Disease Surveillance Model Inside Snowflake, Without Moving

Why does it matter where the analysis runs in public health?

Privacy, security, and oversight teams in public health and science agencies are not arguing in the abstract. HIPAA and protected health information rules, the FISMA authorization boundary, agency data residency requirements, and the reproducibility standards of peer review all expect that you can answer four questions about any published analysis:

1. What data went in?
2. What code and package versions produced the output?
3. Who saw it, and when?
4. Can the result be reproduced?

Pulling a CSV out of the warehouse to model on a laptop breaks the first answer the moment the file is written. You now have a new copy of protected data to track, protect, and destroy, and you have made the fourth answer harder, because the environment that produced the number drifts from wherever the number is published.

Running the analysis inside Snowflake removes that exposure. The query, the model scoring, and the published table all execute on infrastructure inside the same account, governed by the same role-based access control (RBAC). Output is materialized as a normal table, which means it is queryable, joinable, and visible to the same audit logs and access controls you already trust.

The Posit Team Native App applies the same logic to the IDE and the deployment platform. Your analysts open Positron Pro, RStudio Pro, JupyterLab, or VS Code from a browser, but the session itself is a container in Snowpark Container Services running under a Snowflake service role. Posit Connect publishes Shiny apps, Plumber and FastAPI endpoints, and Quarto reports on the same foundation. Nothing has to be routed through a corporate virtual private network (VPN) to a separate analytics environment.

What does the Posit and Snowflake architecture look like?

The picture is simpler than most agency data science stacks. R and Python sessions reach Snowflake through the standard drivers and Snowpark. Computation pushes down to the warehouse, so large joins, aggregations, and model scoring run where the data lives. Posit Connect publishes content from Workbench and serves it inside the same Native App. The credentials your analysts use are Snowflake credentials, so role-based access on the underlying tables also governs who can read which data and run which workload.

How do you connect from Workbench inside Snowflake

Inside a Workbench session running on the Native App, the warehouse and role are already wired up. Authentication is an OAuth token rather than a stored credential, read from the environment:

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

con <- dbConnect(
  odbc::odbc(),
  Driver        = "Snowflake",
  Server        = Sys.getenv("SNOWFLAKE_ACCOUNT"),
  Database      = "SURVEILLANCE",
  Schema        = "CASES",
  Authenticator = "oauth",
  Token         = Sys.getenv("SNOWFLAKE_TOKEN")
)

If you are connecting from Workbench running on your own infrastructure instead, the same code works with key-pair authentication. The connection is the only part of this post that depends on where Workbench is running. Everything after it is identical.

How do you explore the data with dbplyr

A reference to a warehouse table behaves like a lazy data frame. You write dplyr and dbplyr translates it to Snowflake SQL, so the aggregation runs in the warehouse and only the summary comes back.

cases <- tbl(con, in_schema("CASES", "REPORTABLE_CASES"))

weekly <- cases |>
  filter(report_week >= "2023-01-01", condition == "PERTUSSIS") |>
  group_by(county, report_week) |>
  summarise(case_count = n(), median_age = median(age, na.rm = TRUE), .groups = "drop")

Nothing has run yet. If you want to see what dbplyr will send, call show_query():

show_query(weekly)
<SQL>
SELECT "county", "report_week", COUNT(*) AS "case_count",
       MEDIAN("age") AS "median_age"
FROM "SURVEILLANCE"."CASES"."REPORTABLE_CASES"
WHERE ("report_week" >= '2023-01-01' AND "condition" = 'PERTUSSIS')
GROUP BY "county", "report_week"

That is the whole point: the COUNT and MEDIAN happen in Snowflake, not in R.

How do you build the model with tidymodels

Fit a straightforward model so the moving parts stay visible. The goal here is the workflow, not the choice of algorithm, and you should substitute whatever your methods call for. Develop against a reproducible sample, because collect() brings the rows into the session:

library(tidymodels)

county_weeks <- tbl(con, in_schema("CASES", "COUNTY_WEEKS"))

train <- county_weeks |>
  slice_sample(n = 50000) |>
  collect()

incidence_recipe <- recipe(
  case_count ~ county + condition + week_of_year + population_density,
  data = train
) |>
    # For counties with low prevalence, group them into an "other" category
  step_other(county, threshold = 0.02) |>
  step_dummy(all_nominal_predictors())

incidence_fit <- workflow(incidence_recipe, linear_reg()) |>
  fit(data = train)

You only pull rows back when you ask for them with collect(), and for a model you usually want a sample, not the whole table.

If a county appears in the warehouse but not in your training sample, step_other() is what keeps prediction from failing on an unseen level later. Skip it and you will get a wall of new levels warnings the first time you score the full table, so we leave it in on purpose.

How do you translate the model to SQL with orbital

Here is the step that keeps the data in place. orbital reads a fitted tidymodels workflow and produces an object that can emit SQL for the prediction. Instead of pulling the population table into R to score it, you push the model down to the table, which keeps your session memory small, letting you operate more quickly..

library(orbital)

incidence_orbital <- orbital(incidence_fit)
incidence_orbital
-- orbital Object --------------------------------------------
* county_other = case when county not in (...) then 'other' ...
* population_density
* .pred = (Intercept) + population_density * 0.0042 + ...
10 equations in total.

The object is just the prediction math, expressed as a set of equations. To score the full table, hand orbital the lazy dbplyr reference rather than collected data:

scored <- predict(incidence_orbital, county_weeks)

show_query(scored)
<SQL>
SELECT "county", "condition", "week_of_year", "population_density",
       (-2.13 + "population_density" * 0.0042 + ... ) AS ".pred"
FROM "SURVEILLANCE"."CASES"."COUNTY_WEEKS"

The prediction is now a SQL SELECT. When you collect() the result, or write it back to a table, the arithmetic runs inside Snowflake across every county and week. For a statewide population table that is the difference between an overnight batch on a single machine and a query that returns in seconds or minutes. To persist the scores, write them to a table rather than collecting:

scored |>
  compute(name = in_schema("CASES", "INCIDENCE_SCORES"), temporary = FALSE)

Because this is just SQL, your data engineering team can lift it into a scheduled task without rewriting anything. orbital supports the model and recipe steps that translate cleanly to SQL, which covers a large share of day-to-day surveillance modeling. If a step is not yet supported, you get a clear error rather than a silently wrong answer. The list of supported steps is in the orbital documentation.

How do you publish the result to program leadership

A scored table is useful, but program leadership wants to look at a map, not a table. Because Posit Connect runs in the same Snowflake account, you can publish a Shiny application that reads the scores you just wrote, behind the same single sign-on that governs the underlying data. Publish from Workbench to Connect with rsconnect::deployApp() or the Posit Publisher IDE extension:

library(shiny)
library(DBI)
library(odbc)
library(dbplyr)
library(dplyr)

server <- function(input, output, session) {
  con <- dbConnect(odbc::odbc(), Driver = "Snowflake")
  scores <- reactive({
    tbl(con, in_schema("CASES", "INCIDENCE_SCORES")) |>
      filter(week_of_year == input$week) |>
      collect()
  })
  output$map <- renderPlot(plot_incidence(scores()))
}

How do you keep results secure?

Because the app runs on Posit Connect inside the Native App, the user who opens it authenticates against Snowflake, and Connect passes that identity through to the database. An analyst who can only see one jurisdiction’s data in the warehouse sees only that jurisdiction in the app. From an oversight perspective, every read is logged against a known user, and the output table is versioned alongside the rest of the warehouse.

How do you make the analysis reproducible

The reason this pattern holds up to peer review is that every piece is pinned. The code is in Git. The package versions come from Posit Package Manager, so the libraries you used for a published estimate can be restored exactly. And because Snowflake keeps point-in-time snapshots, you can pin the training data too:

train <- county_weeks |>
  # read the table as it stood at a fixed point in time
  mutate(.snapshot = sql("AT(TIMESTAMP => '2026-05-01 00:00:00'::timestamp)")) |>
  slice_sample(n = 50000) |>
  collect()

When a reviewer asks how a number was produced, the answer is a commit hash, a package snapshot, and a Time Travel timestamp, preventing the review from turning into an archaeology project.

A note for Python users

The same pattern works from Python. Fit a scikit-learn pipeline, and orbital will translate it to SQL the same way it does for a tidymodels workflow, so the in-database scoring story is identical whichever language your team writes in. We kept this walkthrough in R because that is where most of the epidemiology community lives, but nothing here is R-only.

What does this mean for reproducibility and oversight

The combination above gives you a code-first analytical environment, in-database computation, and a deployment platform that all share one identity model and one audit trail. Concretely, here is what your privacy, compliance, and oversight teams can verify without a separate evidence-gathering exercise:

• Every query and scoring job is associated with a Snowflake user and role, captured in the account’s query history.
• Every Posit Connect deployment is a versioned bundle pinned to the package versions installed by Posit Package Manager, which can mirror an internally curated repository.
• Every Workbench session runs as a container with logged start, stop, and resource usage.
• Every output table is governed by the same RBAC, masking policies, and row access policies as the source data.

The same governance story your team already tells about SQL workloads in Snowflake now extends to the R and Python work that has historically lived somewhere else. Your data and IT leaders highly value this kind of unified governance.

How can I get started with Posit and Snowflake

If you want to try this in your own Snowflake account, start with the Posit Team Native App listing on Snowflake Marketplace. The links below are the most useful starting points, and the Posit Solutions Engineering team is happy to walk through a proof of concept with your data.

• Posit + Snowflake partnership page for an overview of integration capabilities.
• Curious about Posit Team, but not ready to commit? Start a 30-day free trial of the Posit Team Native App immediately, no sales calls required.
• Posit Team Native App documentation for setup and configuration.
• orbital documentation and dbplyr documentation for the in-database modeling details.
• Check out Posit’s Connected App listings on the Snowflake Marketplace: Posit Workbench, Posit Connect, and Posit Package Manager.

You can also schedule a demo with a Posit expert to see the Native App in action with your specific use cases. If you build something interesting on this stack, we would like to hear about it on the Posit Community forum.

Frequently asked questions

About Posit

Posit (formerly RStudio) is the data science platform for R and Python, used by teams across government, public health, life sciences, financial services, and academia. Posit Team, including Posit Workbench, Posit Connect, and Posit Package Manager, gives organizations in regulated industries the tools to develop, deploy, and govern analytic work, with deep support for the open-source R and Python ecosystems. Learn more at posit.co.

About Snowflake

Snowflake delivers the AI Data Cloud, a global network where thousands of organizations mobilize data with near-unlimited scale, concurrency, and performance. Snowflake’s Native App Framework and Snowpark Container Services allow software partners like Posit to run their applications inside customer Snowflake accounts, giving regulated industries a single governance boundary for both data and the analytical tools that work on it. Learn more at snowflake.com.