21  Databases and SQL for Biostatisticians

The most valuable commodity I know of is information.

Gordon Gekko, Wall Street (1987)

NoteWhy this chapter exists

Clinical data rarely arrive as a tidy CSV. They live in a REDCap project, an institutional data warehouse, or an electronic health record extract, and the biostatistician reaches them through a query rather than a file. The survey of peer programs (Appendix B) found relational databases and SQL taught as a core skill in several of the comparison courses, among them Berkeley STAT 243 (University of California, Berkeley Department of Statistics, 2024), UNC BIOS 735 (University of North Carolina at Chapel Hill, 2024), Michigan (University of Michigan School of Public Health, 2024), Minnesota (University of Minnesota School of Public Health, 2024), and the Johns Hopkins statistical-computing sequence (Johns Hopkins Bloomberg School of Public Health, 2024), as well as in both Software and Data Carpentry. This chapter brings the Practicum to parity.

21.1 Prerequisites

Answer the following questions to see if you can bypass this chapter. You can find the answers at the end of the chapter in Section 21.14.

  1. What is the difference between querying a table with DBI::dbGetQuery() and reading a whole table into memory with dbReadTable(), and why does the distinction matter for a ten-million-row table?
  2. In the dbplyr model of database access, what does a pipeline of dplyr verbs actually do before you call collect(), and where does the computation happen?
  3. Why is it preferable, for reproducibility, to record the SQL query that produced an analytic dataset rather than to save the extracted CSV alone?

21.2 Learning objectives

By the end of this chapter you should be able to:

  • Connect to a database from R with DBI and an appropriate backend driver (RSQLite, duckdb, RPostgres).
  • Write basic SELECT queries with filtering, joining, grouping, and aggregation.
  • Express the same query with dbplyr using familiar dplyr verbs, and inspect the SQL it generates.
  • Distinguish lazy from eager evaluation and use collect() deliberately.
  • Reason about when to push work to the database and when to pull data into R.

21.3 Orientation

A database is a store that lets many users query structured data efficiently and safely. For the biostatistician, the practical point is that the data are usually too large, too sensitive, or too frequently updated to be handed around as flat files. Querying at the source has, we would note, three advantages: you pull only the rows and columns you need, you leave the authoritative copy untouched, and you record, in the query itself, exactly how your analytic dataset was derived.

This chapter is not a database course. It teaches the subset of SQL and the DBI/dbplyr tooling that covers the recurring needs of applied biostatistics: connect, filter, join, aggregate, and extract. The deeper matters of schema design, indexing, and transaction management belong to a database text, and to your data-engineering colleagues.

21.4 The statistician’s contribution

The core of SQL is small enough to learn in a day. What matters more is where you draw the line between the database’s work and R’s:

Query at the source, extract deliberately. The temptation is to pull the whole table into R and work there, because R is where you are comfortable. On a small table this is harmless. On a large one it is slow, memory-hungry, and it obscures the derivation. Push the filtering and aggregation to the database; pull only the analytic dataset.

The query is the provenance. A CSV named analytic_data_final_v3.csv records nothing about how it was made. The SQL that produced it records everything. Keep the query in the compendium, under version control, and treat the extracted file as a build product in the manner of Chapter 18’s raw-data rule.

Beware the silent row multiplication. A join in SQL inflates rows exactly as a join in dplyr does, and the database will not warn you. The verification habit from Chapter 20, count the rows and check against expectation, applies with equal force here.

Respect the boundary between analyst and warehouse. You usually have read-only access to an institutional warehouse, and that is correct. You are a consumer of the data, not its custodian. Do not write back; extract, and do your derivations in your own space.

These judgments are what make database-backed analysis reproducible and safe.

21.5 Connecting with DBI

The DBI package provides a backend-independent interface; a driver package supplies the connection to a specific database engine. For teaching we use RSQLite, which gives a complete SQL database in a single file or in memory, with no server to configure. The same code works against PostgreSQL (RPostgres), DuckDB (duckdb), or SQL Server (odbc) by changing only the dbConnect line.

To make the example self-contained, we create an in-memory database and populate it with a tiny clinical schema: a one-row-per-patient demographics table and a one-row-per-visit table of blood-pressure measurements.

library(DBI)
library(RSQLite)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ":memory:")

demographics <- data.frame(
  patient_id = 1:5,
  age        = c(64, 71, 58, 66, 73),
  arm        = c("Active", "Placebo", "Active", "Placebo", "Active")
)

visits <- data.frame(
  patient_id = c(1, 1, 2, 3, 3, 3, 4, 5),
  visit      = c(1, 2, 1, 1, 2, 3, 1, 1),
  sbp        = c(140, 138, 150, 145, 143, 141, 139, 152)
)

dbWriteTable(con, "demographics", demographics)
dbWriteTable(con, "visits", visits)

dbListTables(con)
#> [1] "demographics" "visits"

In a real setting the dbConnect call would name a server, a database, and credentials, ideally drawn from an environment variable or a config file rather than written into the script:

con <- dbConnect(
  RPostgres::Postgres(),
  host     = "warehouse.example.edu",
  dbname   = "clinical",
  user     = Sys.getenv("DB_USER"),
  password = Sys.getenv("DB_PASS")
)

21.6 Querying with SQL

The SELECT statement is the workhorse. It names the columns, the source table, and optional filtering, joining, grouping, and ordering. Here we ask for the mean systolic blood pressure and the number of visits per treatment arm, joining the two tables on patient_id.

dbGetQuery(con, "
  SELECT d.arm,
         COUNT(*)     AS n_visits,
         AVG(v.sbp)   AS mean_sbp
  FROM visits AS v
  JOIN demographics AS d
    ON v.patient_id = d.patient_id
  GROUP BY d.arm
")
#>       arm n_visits mean_sbp
#> 1  Active        6 143.1667
#> 2 Placebo        2 144.5000

The clauses read in the order the database applies them: FROM and JOIN assemble the rows, WHERE (absent here) filters them, GROUP BY partitions them, the aggregate functions summarize each partition, and SELECT names what comes back. dbGetQuery() returns the result as an ordinary data frame, which is what you want for a small summary. For a large intermediate result you would not use it, for the reason discussed next.

21.7 Querying with dbplyr

Writing SQL by hand is a skill worth having, but for most extractions you can stay in dplyr. The dbplyr package translates a dplyr pipeline into SQL, sends it to the database, and leaves the result there until you ask for it. A reference to a database table behaves almost exactly like a tibble.

visits_tbl <- tbl(con, "visits")
demo_tbl   <- tbl(con, "demographics")

summary_q <- visits_tbl |>
  inner_join(demo_tbl, by = "patient_id") |>
  group_by(arm) |>
  summarize(n_visits = n(),
            mean_sbp = mean(sbp, na.rm = TRUE))

summary_q
#> # Source:   SQL [?? x 3]
#> # Database: sqlite 3.53.3 [:memory:]
#>   arm     n_visits mean_sbp
#>   <chr>      <int>    <dbl>
#> 1 Active         6     143.
#> 2 Placebo        2     144.

The object summary_q is lazy: no computation has yet crossed into R. The pipeline has been recorded, and when printed it shows only a preview fetched from the database. To see the SQL that dbplyr generated, ask for it:

summary_q |> show_query()
#> <SQL>
#> SELECT `arm`, COUNT(*) AS `n_visits`, AVG(`sbp`) AS `mean_sbp`
#> FROM (
#>   SELECT `visits`.*, `age`, `arm`
#>   FROM `visits`
#>   INNER JOIN `demographics`
#>     ON (`visits`.`patient_id` = `demographics`.`patient_id`)
#> ) AS `q01`
#> GROUP BY `arm`

The generated SQL is the same query we wrote by hand, which is the point: dbplyr lets you think in dplyr and still push the work to the database. When you are ready to bring the result into R, for plotting, modeling, or anything the database cannot do, call collect().

analytic <- summary_q |> collect()
analytic
#> # A tibble: 2 × 3
#>   arm     n_visits mean_sbp
#>   <chr>      <int>    <dbl>
#> 1 Active         6     143.
#> 2 Placebo        2     144.

Everything up to collect() ran in the database. Only the small summary crossed into memory. This is the pattern to internalize: filter and aggregate lazily against the table, then collect() the modest result. The verification habit still applies, so we check that the row count matches what a per-arm summary should give.

stopifnot(nrow(analytic) == n_distinct(demographics$arm))

Question. You write tbl(con, "labs") |> filter(test == "HbA1c") |> summarize(m = mean(value)) against a table of fifty million laboratory results, then call collect(). How much data crosses from the database into R, and what would have happened had you called collect() before the filter?

Answer.

With collect() at the end, only the single summarized value crosses into R: the database does the filtering and the averaging, and returns one number. Had you called collect() first, all fifty million rows would have been pulled into memory before R did any work, which on most laptops would exhaust RAM and fail. The rule is to collect() as late as possible, after the database has reduced the data to the size you actually need.

21.8 When to push, when to pull

Figure 21.1 is the whole idea of this chapter in one picture: collect() is a boundary, everything to its left runs in the database on the full data, and everything to its right runs in R on whatever crossed.

flowchart LR
  subgraph DB["In the database, on all rows"]
    T["table<br/><i>50 million rows</i>"] --> FI["filter"] --> JO["join"] --> AG["group and<br/>aggregate"]
  end
  AG --> CO(["<b>collect()</b>"])
  subgraph RR["In R, on the reduced result"]
    MO["model"]
    PL["plot"]
    IT["iterate"]
  end
  CO --> MO
  CO --> PL
  CO --> IT
Figure 21.1: The collect() boundary. Operations that make the data smaller belong on the database side; operations that need R’s statistical machinery belong on the R side. Moving collect() earlier does not change the answer, only the amount of data that has to cross and the memory it takes to hold it.

The database is good at set operations on large data: filtering, joining, grouping, aggregating. R is good at everything else: modeling, plotting, iteration, anything statistical. The dividing line is collect(). Push the data-reduction work to the database; pull the reduced result into R for the analysis. A useful test: if a step makes the data smaller (a filter, an aggregate), it usually belongs before collect(); if a step needs the full statistical machinery of R (fitting a mixed model, drawing a figure), it belongs after.

Two cautions. First, not every R function has a SQL translation; dbplyr will tell you when it cannot translate a verb, and then you must either rewrite it or collect() earlier. Second, a query that is cheap to write can be expensive to run; on a shared warehouse, a careless cross-join can inconvenience every other user, so test on a LIMIT-ed subset before running the full extraction.

Question. Your analysis begins by running a SQL query against the hospital warehouse. The query is committed to the repository. Is the analysis reproducible?

Answer.

The code is. The analysis is not, and the difference is the point of this section.

A committed query guarantees that anyone can see how the extract was defined. It guarantees nothing about what the extract contained, because the warehouse is a moving target. Records get corrected. Late-arriving lab results appear. A patient withdraws consent and their rows are purged. Run the identical query six months later and you get a different table, with no error and no indication that anything changed.

This is unlike every other input the book discusses. A CSV in data/raw/ is a fixed artifact; a query is an instruction whose result depends on when it ran.

The remedy is to treat the extract as the raw data rather than the query. Run the query once, deliberately, and write the result to a dated file. Record alongside it the query text, the timestamp, and the database version or snapshot identifier if the warehouse offers one. That file is what the analysis reads, and it is what gets archived.

The query remains in the repository as provenance, which is exactly the role Chapter 17 gives an acquisition script. Same pattern, different source.

21.9 Reproducibility of the extraction

The analytic dataset is a build product, and the query is its recipe. Keep the query in the compendium as a .sql file or an R script, so that the derivation is version-controlled and re-runnable. Record the date of extraction and, where the warehouse supports it, the data snapshot or freeze identifier, because a warehouse is a living system and today’s query may return different rows next month. The pattern mirrors the raw-data discipline of Chapter 18: the query and its output date are the audit trail that lets a reviewer reconstruct exactly which rows your analysis rested on.

21.10 Collaborating with an LLM on databases

LLMs write competent SQL and dbplyr. The traps, in our experience, are correctness on joins and the silent scale of a query.

Prompt 1: drafting a query. Describe the tables, their keys, and the summary you want, and ask for both the SQL and the dbplyr equivalent.

What to watch for. The join key and the join type. An LLM will readily write an inner join where you needed a left join, dropping patients with no visits. State the cardinality you expect.

Verification. Run against a LIMIT-ed subset; count the rows; confirm the count matches the expected number of groups or patients.

Prompt 2: translating hand-written SQL to dbplyr. Paste a SELECT statement and ask for the dplyr pipeline that generates it.

What to watch for. Aggregations and GROUP BY translate cleanly; window functions and correlated subqueries less so. Compare show_query() on the result against the original.

Verification. Confirm the two produce identical results on a small table before trusting the translation on the full one.

21.11 Principle in use

Three habits keep the extraction defensible:

  1. Query at the source; collect() late. Push filtering and aggregation to the database; pull only the analytic result into R.
  2. The query is the provenance. Version-control the SQL that produced the analytic dataset; treat the extract as a build product with a recorded date.
  3. Verify joins in SQL as in R. Count the rows; a silent many-to-many inflates the data in a database exactly as it does in dplyr.

21.12 Exercises

  1. Create an in-memory SQLite database with the demographics and visits tables from this chapter. Write a SQL query that returns, per patient, the number of visits and the change in systolic blood pressure from the first to the last visit.

  2. Express the same query with dbplyr. Use show_query() to inspect the generated SQL and compare it to your hand-written version.

  3. Add a third table, labs, with columns patient_id, test, and value. Write a dbplyr pipeline that returns the mean HbA1c per treatment arm, joining all three tables. Verify the row count.

  4. Deliberately write a query that produces a many-to-many join between visits and a table with duplicate patient_id values. Observe the inflated row count and explain it. On your own machine. No answer key; the interesting part is the measurement, which depends on your hardware.

  5. Using the duckdb backend instead of RSQLite, read a large CSV file directly with duckdb’s read_csv_auto and run an aggregation without ever loading the file into R. Compare the memory use to readr::read_csv() followed by dplyr.

The chapter’s connection was closed, so these open a fresh one and rebuild the two tables.

con2 <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con2, "demographics", demographics)
dbWriteTable(con2, "visits", visits)

1. Visit count and first-to-last change, in SQL.

dbGetQuery(con2, "
  SELECT patient_id,
         COUNT(*) AS n_visits,
         MAX(CASE WHEN r_last  = 1 THEN sbp END) -
         MAX(CASE WHEN r_first = 1 THEN sbp END) AS sbp_change
  FROM (
    SELECT patient_id, visit, sbp,
           ROW_NUMBER() OVER (PARTITION BY patient_id
                              ORDER BY visit)      AS r_first,
           ROW_NUMBER() OVER (PARTITION BY patient_id
                              ORDER BY visit DESC) AS r_last
    FROM visits
  )
  GROUP BY patient_id
")
#>   patient_id n_visits sbp_change
#> 1          1        2         -2
#> 2          2        1          0
#> 3          3        3         -4
#> 4          4        1          0
#> 5          5        1          0

The window functions number each patient’s visits from both ends, so the first and last rows can be picked out inside a single aggregation. Patients with one visit get a change of zero, which is correct and worth noticing: it is not the same as missing, and a real analysis should decide which it wants.

2. The same query through dbplyr, and its SQL.

q <- tbl(con2, "visits") |>
  group_by(patient_id) |>
  window_order(visit) |>
  mutate(first_sbp = first(sbp), last_sbp = last(sbp)) |>
  ungroup() |>
  group_by(patient_id) |>
  summarize(n_visits = n(),
            sbp_change = max(last_sbp) - max(first_sbp))

q |> collect()
#> # A tibble: 5 × 3
#>   patient_id n_visits sbp_change
#>        <dbl>    <int>      <dbl>
#> 1          1        2         -2
#> 2          2        1          0
#> 3          3        3         -4
#> 4          4        1          0
#> 5          5        1          0
q |> show_query()
#> <SQL>
#> SELECT
#>   `patient_id`,
#>   COUNT(*) AS `n_visits`,
#>   MAX(`last_sbp`) - MAX(`first_sbp`) AS `sbp_change`
#> FROM (
#>   SELECT
#>     `visits`.*,
#>     FIRST_VALUE(`sbp`) OVER (PARTITION BY `patient_id` ORDER BY `visit`) AS `first_sbp`,
#>     LAST_VALUE(`sbp`) OVER (PARTITION BY `patient_id` ORDER BY `visit` ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS `last_sbp`
#>   FROM `visits`
#> ) AS `q01`
#> GROUP BY `patient_id`

Two things are worth extracting from this.

first() and last() are window functions, so they belong in mutate() and not in summarize(). Writing summarize(sbp_change = last(sbp) - first(sbp)) looks natural and fails, because a window function needs the rows it is ordered over and an aggregate has already collapsed them. The two-step form computes the values per row, then aggregates.

window_order() has no dplyr equivalent in memory, where first() and last() simply follow row order. A database table has no inherent order, so it must be stated. Omit it and the answer is whichever row the engine happened to reach first, with no warning.

The generated SQL is more verbose than the hand-written version and computes the same thing. Which to prefer is a maintenance question, not a performance one: the dbplyr version stays readable to someone who knows dplyr and not SQL, and show_query() means the SQL is never hidden.

3. A third table, and a three-way join.

labs <- data.frame(
  patient_id = c(1, 1, 2, 3, 3, 4, 5),
  test       = "HbA1c",
  value      = c(7.1, 6.8, 8.2, 7.5, 7.3, 6.9, 8.0)
)
dbWriteTable(con2, "labs", labs)

result <- tbl(con2, "labs") |>
  filter(test == "HbA1c") |>
  inner_join(tbl(con2, "visits") |>
               distinct(patient_id), by = "patient_id") |>
  inner_join(tbl(con2, "demographics"), by = "patient_id") |>
  group_by(arm) |>
  summarize(mean_hba1c = mean(value, na.rm = TRUE),
            n_labs = n(), .groups = "drop") |>
  collect()

result
#> # A tibble: 2 × 3
#>   arm     mean_hba1c n_labs
#>   <chr>        <dbl>  <int>
#> 1 Active        7.34      5
#> 2 Placebo       7.55      2
stopifnot(nrow(result) == n_distinct(demographics$arm))

Note the distinct(patient_id) on visits. Without it the join multiplies each lab result by that patient’s visit count, and the mean silently becomes a visit-weighted average rather than a lab-weighted one. The row-count assertion would not catch that, because the result still has one row per arm. Exercise 4 is the same error made visible.

4. A deliberate many-to-many, and why the count inflates.

# demographics with a duplicated patient, as a re-consent
# appended rather than replacing the original row
demo_dup <- rbind(demographics,
                  data.frame(patient_id = 1, age = 65,
                             arm = "Active"))
dbWriteTable(con2, "demo_dup", demo_dup)

n_before <- tbl(con2, "visits") |> count() |> pull(n)
n_after  <- tbl(con2, "visits") |>
  inner_join(tbl(con2, "demo_dup"), by = "patient_id") |>
  count() |> pull(n)

c(visits = n_before, after_join = n_after)
#>     visits after_join 
#>          8         10

Patient 1 has two visits and now two demographics rows, so those two visits become four. The join did not fail and issued no warning; SQL has no equivalent of dplyr’s relationship argument. The only defense is to check the key before joining, which is why the chapter’s own pipeline asserts uniqueness rather than trusting it.

21.13 Further reading

  • The DBI documentation at dbi.r-dbi.org, the interface reference.
  • The dbplyr documentation at dbplyr.tidyverse.org, including the list of translatable verbs.
  • (Wickham et al., 2023), the databases chapter, applied introduction to DBI and dbplyr.
  • The DuckDB documentation at duckdb.org, for file-backed analytical queries without a server.

21.14 Prerequisites answers

  1. dbGetQuery() sends a query and returns only its result; a SELECT with filtering and aggregation returns a small summary regardless of table size. dbReadTable() reads an entire table into memory, so on a ten-million-row table it pulls all ten million rows across, which is slow and may exhaust memory. Use dbGetQuery() (or lazy dbplyr) to let the database do the reduction.
  2. A dbplyr pipeline builds up a query lazily: each verb adds to a SQL statement that has not yet run. No computation crosses into R until you call collect() (or print, which fetches a preview). The computation happens in the database; R receives only the collected result.
  3. The extracted CSV records the rows but not their derivation. The SQL query records exactly which tables, filters, joins, and aggregations produced the analytic dataset, so a reviewer can reconstruct or re-run it. A warehouse is also a living system, so pairing the query with an extraction date documents the snapshot the analysis rested on.