19  Databases and SQL for Biostatisticians

Suppose your principal investigator (PI) hands you warehouse credentials, not a CSV file. The data you need sit in an institutional database, behind a login, and you reach them by writing a query. The query runs and returns a tidy table. It gives no sign of what it did wrong. A join may have silently dropped a patient with no match, or counted a duplicated laboratory row twice. A filter built by pasting text into SQL may have returned every patient instead of one. The database does not warn you about any of these. The habits of Chapter 18, a stated row count checked against the actual one, apply here unchanged. In this chapter we add two habits that are specific to databases. The first is parameterized queries. The second is a dated extract that records what the warehouse returned on the day you asked.

The most valuable commodity I know of is information.

Gordon Gekko, Wall Street (1987)

Diagram of the analysis pipeline: six stages in order (Plan, Acquire, Wrangle, Analyze, Report, Share) above two bands that run under every stage (Version control and Environment). Highlighted for this chapter: Acquire.
Figure 19.1: Where this chapter sits: querying a database is part of the Acquire stage, which brings raw data into the project and records how they were obtained.
NoteIn this chapter

Reading time. About 30 minutes.

You’ll need. Chapter 18 for join cardinality and the join checklist, and Chapter 13 for the raw-data rule. The code uses:

The DuckDB and Parquet subsection also uses duckdb and arrow, and runs only if both are installed.

You’ll be able to.

  • Load tables into a database from R and query them with SQL and with dbplyr, and state where each step of the query runs.
  • Audit keys in SQL for duplicates and orphans before a join, and predict the row count of the join.
  • Write parameterized queries, and explain how a pasted query string returns the wrong rows without an error.
  • Extract an analytic dataset as a dated file with its query and checksum, so that the extract is reproducible.

Outline.

If these answers come quickly, skip to the next chapter. The answers are in Section 19.11.

  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?

19.1 Connecting to a database

Clinical data rarely arrive as a file. They live in a REDCap (Research Electronic Data Capture) project, an institutional data warehouse, or an extract of the electronic health record (EHR). They are usually too large, too sensitive, or too frequently updated to be handed around as flat files. Querying at the source has three advantages. You pull only the rows and columns you need. You leave the authoritative copy untouched. And the query itself records how your analytic dataset was derived. In this chapter we teach the subset of SQL (Structured Query Language) and of the R tooling that covers those needs. Schema design, indexing, and transactions belong to a database text and to your data-engineering colleagues. The generic tour of DBI and dbplyr is in R for Data Science (Wickham et al., 2023, ch. 21).

19.1.1 A database built from the running study

The DBI package (database interface) defines one set of functions for every database. A driver package supplies the connection to a particular engine. For teaching, RSQLite gives a complete SQL database 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.

The chunk below loads four HF-HOME tables into an in-memory database. randomization and outcomes have one row per patient, visits has one row per contact, and labs has one row per visit and test. The tables are read with patient_id as character, so that the leading zeros are preserved in the database.

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

hf_dir <- file.path("..", "data", "raw_data", "hfhome")
read_hf <- function(file) {
  readr::read_csv(file.path(hf_dir, file),
                  col_types = readr::cols(patient_id = "c",
                                          .default = "?"))
}

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

dbWriteTable(con, "randomization", read_hf("randomization.csv"))
dbWriteTable(con, "outcomes", read_hf("outcomes.csv"))
dbWriteTable(con, "visits", read_hf("visits.csv"))
dbWriteTable(con, "labs", read_hf("labs.csv"))

dbListTables(con)
#> [1] "labs"          "outcomes"      "randomization" "visits"

extended_types = TRUE makes RSQLite store and return dates as Date values. Without it, a date column comes back as a number of days.

19.1.2 Credentials and the warehouse boundary

Against a real warehouse, the dbConnect() call names a server, a database, and an account. The credentials never appear in the script (display-only; the server is hypothetical):

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

keyring stores the password in the operating system’s credential store. Sys.getenv() reads the user name from .Renviron, which stays out of Git. Where your institution offers single sign-on (SSO) or Kerberos authentication to the warehouse, prefer it. No password then exists in your files at all.

You usually have read-only access to a warehouse, and that is correct. You are a consumer of the data, not its custodian. Do not write back. Extract the data, and do your derivations in your own project.

WarningPitfall

A password typed into a script is published by the next commit. Removing it in a later commit does not remove it from the repository’s history, and a pushed history may already have been cloned. Treat any credential that reached Git as compromised. Ask for it to be rotated, then fix the script.

Question. A colleague’s script begins dbConnect(..., password = "Spring2026!"). The repository is private. What do you recommend, and why is ‘it is private’ not enough?

Answer. Move the password to the operating system’s credential store with keyring, or to SSO if the warehouse supports it, and have the password rotated. A private repository is readable by every collaborator, every clone, every backup, and anyone who is later given access. The history also keeps the password after the line is deleted. The fix is not complete until the old password no longer works.

19.2 Querying with SQL and dbplyr

19.2.1 A first SELECT

A SELECT statement names the columns, the source tables, and optional filtering, joining, grouping, and ordering. The query below counts patients and 30-day events by arm.

dbGetQuery(con, "
  SELECT r.arm,
         COUNT(*)           AS n_randomized,
         SUM(o.ascertained) AS n_ascertained,
         SUM(o.event_30d)   AS n_events,
         AVG(o.event_30d)   AS risk
  FROM randomization AS r
  JOIN outcomes AS o
    ON r.patient_id = o.patient_id
  GROUP BY r.arm
")
#>           arm n_randomized n_ascertained n_events      risk
#> 1 Home health         1216          1136      240 0.2112676
#> 2  Usual care         1216          1133      268 0.2365402

The clauses run in a different order from the one in which they are written. FROM and JOIN assemble the rows, and 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.

WarningPitfall

SQL aggregates skip missing values without saying so. AVG() above divides by the ascertained patients only, because the 163 unascertained outcomes are NULL. In R, mean() on the same column returns NA and forces a decision. In SQL the decision has already been made, and it changes the denominator. COUNT(*) counts rows and COUNT(column) counts non-missing values. The two disagree exactly when values are missing. State which denominator the analysis plan wants (Chapter 21) and count it explicitly.

19.2.2 The same query through dbplyr

For most extractions you can stay in dplyr. The dbplyr package translates a pipeline into SQL, sends it to the database, and leaves the result there until you ask for it.

summary_q <- tbl(con, "randomization") |>
  inner_join(tbl(con, "outcomes"), by = "patient_id") |>
  group_by(arm) |>
  summarize(n_randomized  = n(),
            n_ascertained = sum(ascertained, na.rm = TRUE),
            n_events      = sum(event_30d, na.rm = TRUE),
            risk          = mean(event_30d, na.rm = TRUE))

summary_q
#> # Source:   SQL [?? x 5]
#> # Database: sqlite 3.53.3 [:memory:]
#>   arm         n_randomized n_ascertained n_events  risk
#>   <chr>              <int>         <int>    <dbl> <dbl>
#> 1 Home health         1216          1136      240 0.211
#> 2 Usual care          1216          1133      268 0.237

summary_q is lazy. That is, the pipeline has been recorded, and the printout is a preview fetched from the database. show_query() prints the SQL that dbplyr generated, and collect() brings the result into R.

summary_q |> show_query()
#> <SQL>
#> SELECT
#>   `arm`,
#>   COUNT(*) AS `n_randomized`,
#>   SUM(`ascertained`) AS `n_ascertained`,
#>   SUM(`event_30d`) AS `n_events`,
#>   AVG(`event_30d`) AS `risk`
#> FROM (
#>   SELECT
#>     `randomization`.*,
#>     `ascertained`,
#>     `event_30d`,
#>     `first_event`,
#>     `event_date`,
#>     `days_to_event`,
#>     `readmit_date`,
#>     `death_date`
#>   FROM `randomization`
#>   INNER JOIN `outcomes`
#>     ON (`randomization`.`patient_id` = `outcomes`.`patient_id`)
#> ) AS `q01`
#> GROUP BY `arm`
by_arm <- summary_q |> collect()
by_arm
#> # A tibble: 2 × 5
#>   arm         n_randomized n_ascertained n_events  risk
#>   <chr>              <int>         <int>    <dbl> <dbl>
#> 1 Home health         1216          1136      240 0.211
#> 2 Usual care          1216          1133      268 0.237

na.rm = TRUE in the pipeline changes nothing in the SQL. It acknowledges that SQL removes missing values whether you ask or not. dbplyr warns when you leave it out.

TipVerification

The hand-written query and the dbplyr pipeline should agree exactly, and the arm totals should add up to the randomization table.

by_hand <- dbGetQuery(con, "
  SELECT r.arm, COUNT(*) AS n_randomized,
         SUM(o.event_30d) AS n_events
  FROM randomization AS r
  JOIN outcomes AS o ON r.patient_id = o.patient_id
  GROUP BY r.arm
")
stopifnot(
  all.equal(arrange(by_hand, arm),
            select(arrange(by_arm, arm), arm, n_randomized,
                   n_events),
            check.attributes = FALSE),
  sum(by_arm$n_randomized) ==
    dbGetQuery(con, "SELECT COUNT(*) AS n FROM randomization")$n
)

19.2.3 Translating between SQL and dplyr

Most SQL you will read maps clause by clause onto a dplyr verb. Table 19.1 is the translation card. show_query() lets you check any row of it against your own database.

Table 19.1: SQL clauses and their dplyr equivalents.
SQL dplyr Note
SELECT a, b select(a, b)
SELECT DISTINCT a distinct(a)
WHERE x > 1 filter(x > 1)
GROUP BY g with COUNT(*), AVG(x) group_by(g) \|> summarize(n(), mean(x)) SQL drops NULL from AVG()
HAVING COUNT(*) > 1 summarize() then filter(n > 1) Filters groups, not rows
ORDER BY x DESC arrange(desc(x)) A table has no order without it
LIMIT 10 head(10)
JOIN ... ON inner_join(by = )
LEFT JOIN ... ON left_join(by = )
LEFT JOIN ... WHERE y.key IS NULL anti_join(by = ) The SQL anti-join
CASE WHEN ... THEN ... END case_when()
ROW_NUMBER() OVER (PARTITION BY g ORDER BY t) group_by(g) \|> window_order(t) \|> mutate(row_number()) window_order() exists only in dbplyr

19.2.4 Exercises

  1. Write a SQL query that returns, for each arm, the number of patients whose first event was a death and the number whose first event was a readmission. Compute the same counts in R from outcomes.csv and confirm they agree.
sql_first <- dbGetQuery(con, "
  SELECT r.arm, o.first_event, COUNT(*) AS n
  FROM randomization AS r
  JOIN outcomes AS o ON r.patient_id = o.patient_id
  WHERE o.first_event IN ('death', 'readmission')
  GROUP BY r.arm, o.first_event
  ORDER BY r.arm, o.first_event
")
sql_first
#>           arm first_event   n
#> 1 Home health       death  34
#> 2 Home health readmission 206
#> 3  Usual care       death  45
#> 4  Usual care readmission 223

r_first <- read_hf("randomization.csv") |>
  inner_join(read_hf("outcomes.csv"), by = "patient_id",
             relationship = "one-to-one") |>
  filter(first_event %in% c("death", "readmission")) |>
  count(arm, first_event) |>
  arrange(arm, first_event)
stopifnot(all.equal(sql_first, as.data.frame(r_first),
                    check.attributes = FALSE))

The WHERE clause runs before GROUP BY, so the unascertained patients (whose first_event is missing) and the patients with no event never reach the count. Computing the same counts once in each language is the check.

  1. Express the query from exercise 1 with dbplyr, print the SQL it generates with show_query(), and name one difference from your hand-written version.
first_q <- tbl(con, "randomization") |>
  inner_join(tbl(con, "outcomes"), by = "patient_id") |>
  filter(first_event %in% c("death", "readmission")) |>
  count(arm, first_event) |>
  arrange(arm, first_event)

first_q |> show_query()
#> <SQL>
#> SELECT `arm`, `first_event`, COUNT(*) AS `n`
#> FROM (
#>   SELECT `q01`.*
#>   FROM (
#>     SELECT
#>       `randomization`.*,
#>       `ascertained`,
#>       `event_30d`,
#>       `first_event`,
#>       `event_date`,
#>       `days_to_event`,
#>       `readmit_date`,
#>       `death_date`
#>     FROM `randomization`
#>     INNER JOIN `outcomes`
#>       ON (`randomization`.`patient_id` = `outcomes`.`patient_id`)
#>   ) AS `q01`
#>   WHERE (`first_event` IN ('death', 'readmission'))
#> ) AS `q01`
#> GROUP BY `arm`, `first_event`
#> ORDER BY `arm`, `first_event`
stopifnot(all.equal(collect(first_q), as_tibble(sql_first),
                    check.attributes = FALSE))

dbplyr writes the join as a subquery and selects every column of both tables inside it. The hand-written version selects only what it needs. The database’s planner usually makes the two equally fast. The generated version is longer, but show_query() means it is never hidden.

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

Answer. One number crosses. The database filters and averages, and returns a one-row result. Had you called collect() first, all fifty million rows would have been pulled into memory before R did any work. On most laptops that exhausts memory and fails. Collect as late as possible, after the database has reduced the data to the size you need.

19.3 Joins in SQL fail as joins in R do

A join in SQL drops and multiplies rows exactly as a join in dplyr does. One difference makes it worse. SQL has no relationship argument and no many-to-many warning. The HF-HOME labs table carries the two planted defects that Chapter 18 taught you to look for. Both pass through SQL without a message.

19.3.1 Auditing the keys

Run the audits in the database, before the join. The first query looks for a key that should be unique and is not. The second is the SQL anti-join, which lists lab patients with no randomization record.

dbGetQuery(con, "
  SELECT visit_id, test_code, COUNT(*) AS n,
         MIN(value) AS min_value, MAX(value) AS max_value
  FROM labs
  GROUP BY visit_id, test_code
  HAVING COUNT(*) > 1
")
#>   visit_id test_code n min_value max_value
#> 1  0373-01  NTPROBNP 2    2502.0    2502.0
#> 2  2113-01         K 2       3.8       4.1

dbGetQuery(con, "
  SELECT DISTINCT l.patient_id
  FROM labs AS l
  LEFT JOIN randomization AS r ON l.patient_id = r.patient_id
  WHERE r.patient_id IS NULL
")
#>   patient_id
#> 1       2433

Two visit-test pairs repeat. The first is an exact duplicate (the minimum and maximum agree). The second is a potassium result that was re-run with a different value, and no rule in the data can resolve it. Patient 2433 has laboratory results and was never randomized.

19.3.2 Two errors that nearly cancel

Now consider the join a typical user of the warehouse would write. It asks for the discharge NT-proBNP (N-terminal pro-B-type natriuretic peptide) result for every randomized patient. There should be one row per patient.

n_patients <- dbGetQuery(con, "
  SELECT COUNT(*) AS n FROM randomization")$n

discharge_bnp <- dbGetQuery(con, "
  SELECT r.patient_id, r.arm, l.value
  FROM labs AS l
  JOIN visits AS v        ON l.visit_id = v.visit_id
  JOIN randomization AS r ON v.patient_id = r.patient_id
  WHERE l.test_code = 'NTPROBNP'
    AND v.visit_type = 'discharge'
")

c(patients = n_patients, rows = nrow(discharge_bnp))
#> patients     rows 
#>     2432     2433

The inner join dropped patient 2433, whose visit is not in visits, and kept the duplicated row for patient 0373. One row lost and one row gained leave a count that is off by one. That is easy to dismiss as close enough. The assertion does not dismiss it:

stopifnot(nrow(discharge_bnp) == n_patients,
          !anyDuplicated(discharge_bnp$patient_id))
#> Error:
#> ! nrow(discharge_bnp) == n_patients is not TRUE
WarningPitfall

A join that drops one row and duplicates another returns a count that looks right. Here the errors leave the count off by one. On another extract they may cancel exactly, and nrow() alone then passes. Check uniqueness of the key (HAVING COUNT(*) > 1) and coverage in both directions (the LEFT JOIN ... IS NULL anti-join) as separate audits, not only the final count. These are the first three rows of Table 18.1, written in SQL.

The fix belongs in the query, with its rule stated. Here, SELECT DISTINCT removes the exact duplicate. An explicit anti-join records the orphan in a log rather than dropping it silently. The conflicting potassium pair needs a rule from the data manager, not from you.

TipVerification

Deduplicate the exact duplicate in SQL, and confirm that the result has one row per randomized patient.

discharge_bnp <- dbGetQuery(con, "
  SELECT DISTINCT r.patient_id, r.arm, l.value
  FROM labs AS l
  JOIN visits AS v        ON l.visit_id = v.visit_id
  JOIN randomization AS r ON v.patient_id = r.patient_id
  WHERE l.test_code = 'NTPROBNP'
    AND v.visit_type = 'discharge'
")
stopifnot(nrow(discharge_bnp) == n_patients,
          !anyDuplicated(discharge_bnp$patient_id))

SELECT DISTINCT is safe here only because the duplicate is exact. Applied to the potassium pair it would keep both rows, because their values differ, and the assertion would catch that too.

19.3.3 Exercises

  1. Write one SQL query that classifies every duplicated visit-test pair in labs as exact (all rows have the same value) or conflicting. Confirm the classification in R with dplyr.
dup_sql <- dbGetQuery(con, "
  SELECT visit_id, test_code, COUNT(*) AS n,
         CASE WHEN MIN(value) = MAX(value) THEN 'exact'
              ELSE 'conflicting' END AS kind
  FROM labs
  GROUP BY visit_id, test_code
  HAVING COUNT(*) > 1
  ORDER BY visit_id
")
dup_sql
#>   visit_id test_code n        kind
#> 1  0373-01  NTPROBNP 2       exact
#> 2  2113-01         K 2 conflicting

dup_r <- read_hf("labs.csv") |>
  group_by(visit_id, test_code) |>
  filter(n() > 1) |>
  summarize(n = n(),
            kind = if_else(min(value) == max(value), "exact",
                           "conflicting"),
            .groups = "drop") |>
  arrange(visit_id)
stopifnot(all.equal(dup_sql, as.data.frame(dup_r),
                    check.attributes = FALSE))

HAVING filters the groups after aggregation. For this reason it, and not WHERE, finds the duplicates. The classification decides the fix. An exact duplicate can be removed by rule, and a conflicting one goes back to the data manager with both values.

  1. Using a window function (in SQL or through dbplyr’s window_order()), find each patient’s first home-health visit day, and count, by arm, the patients whose first home-health visit fell on days 1 to 7. Check your counts against an in-memory dplyr computation on visits.csv.
first_hh <- tbl(con, "visits") |>
  filter(visit_type == "home_health") |>
  group_by(patient_id) |>
  window_order(visit_day) |>
  mutate(k = row_number()) |>
  ungroup() |>
  filter(k == 1) |>
  inner_join(tbl(con, "randomization"), by = "patient_id") |>
  group_by(arm) |>
  summarize(n_within_7 = sum(if_else(visit_day <= 7, 1L, 0L),
                             na.rm = TRUE)) |>
  arrange(arm) |>
  collect()
first_hh
#> # A tibble: 2 × 2
#>   arm         n_within_7
#>   <chr>            <int>
#> 1 Home health       1019
#> 2 Usual care          64

in_memory <- read_hf("visits.csv") |>
  filter(visit_type == "home_health") |>
  group_by(patient_id) |>
  summarize(first_day = min(visit_day)) |>
  inner_join(read_hf("randomization.csv"), by = "patient_id",
             relationship = "one-to-one") |>
  group_by(arm) |>
  summarize(n_within_7 = sum(first_day <= 7)) |>
  arrange(arm)
stopifnot(all.equal(first_hh, in_memory,
                    check.attributes = FALSE))

A database table has no inherent row order, so window_order() states it. Without it, row_number() numbers the rows in whatever order the engine reached them, with no warning. In memory, min() gives the same answer without a window, and that serves as the independent check.

Question. SQL has no relationship = "many-to-one". What do you write instead, and where in the script does it go?

Answer. Write an audit query before the join. It has two parts: GROUP BY key HAVING COUNT(*) > 1 on the side that should be unique, and a LEFT JOIN ... WHERE key IS NULL anti-join in each direction that matters. Then write a stopifnot() on the row count and on key uniqueness after the result reaches R. The audits go before the join, so that a failure names the bad keys. The assertion goes after, so that nothing downstream runs on a multiplied table.

19.4 Parameterized queries

A query often needs a value from outside, such as a site, a date range, or a list of patient identifiers. The tempting way to supply it is to paste the value into the query string. The safe way is a parameter. The database driver passes a parameter separately from the SQL text, so the value can never be read as SQL.

requested <- "0001' OR '1' = '1"

pasted <- paste0("SELECT patient_id, arm FROM randomization ",
                 "WHERE patient_id = '", requested, "'")
nrow(dbGetQuery(con, pasted))
#> [1] 2432

nrow(dbGetQuery(con,
  "SELECT patient_id, arm FROM randomization WHERE patient_id = ?",
  params = list(requested)))
#> [1] 0

dbGetQuery(con,
  "SELECT patient_id, arm FROM randomization WHERE patient_id = ?",
  params = list("0001"))
#>   patient_id         arm
#> 1       0001 Home health

The pasted query returned every randomized patient. The quote inside requested closed the string, and OR '1' = '1' made the condition true for every row. The parameterized query looked for a patient whose identifier is that whole odd string, and found none.

WarningPitfall

A pasted query string can return the wrong rows without an error. The input above was deliberate, the classic SQL injection. The common accident is a site name or a free-text value that contains an apostrophe. Such a value either breaks the query or changes what it selects. Pass values with params = in dbGetQuery(), or build the SQL with glue::glue_sql(). Never use paste0() or sprintf().

glue::glue_sql() quotes each value for the connected database, and the * suffix expands a vector into an IN list:

site <- "Site A"
ids  <- c("0001", "0002", "0003")
glue::glue_sql("SELECT * FROM randomization WHERE site = {site}",
               .con = con)
#> <SQL> SELECT * FROM randomization WHERE site = 'Site A'
glue::glue_sql("SELECT * FROM randomization
                WHERE patient_id IN ({ids*})", .con = con)
#> <SQL> SELECT * FROM randomization
#> WHERE patient_id IN ('0001', '0002', '0003')
ImportantLLM prompt

Use a model to draft a query from the schema, never from the data or the connection.

Context. A SQLite database has four tables. randomization and outcomes have one row per patient_id (character, leading zeros); visits has one row per visit_id, with patient_id, visit_type, and visit_day; labs has one row per visit_id and test_code, with value. Here are the column names and types: [output of str() on a synthetic row of each table].

Constraints. Write the query twice, in SQL and with dbplyr. Take the site as a parameter (? with params =), not a pasted string. Use an inner join only where I say unmatched rows should be dropped. Do not use SELECT DISTINCT to remove duplicates.

Criteria. Before writing code, state the expected relationship of each join and the number of rows the result should have per patient. I want discharge creatinine by arm for one site.

Failure mode. Models write an inner join where a left join was needed, and drop the anti-join audit. They also reach for SELECT DISTINCT to make a count come out right, which hides a conflicting duplicate. Models also echo back connection strings and passwords you paste, into a chat log you do not control. Check. Run the key audits of Section 19.3 first, then the query, and assert the row count you were promised. Send the schema and synthetic rows; never paste credentials, connection strings, or patient rows into a prompt (Section 12.3).

Question. A Shiny dashboard lets a coordinator type a site name, and the server builds paste0("... WHERE site = '", input$site, "'"). It has worked for a year. Why is it still wrong?

Answer. It has worked because no one has typed a quote. A site called ‘St. Mary’s’ breaks the query, and a crafted input can select or, with write access, change any row. The year of success is evidence about the inputs so far, not about the code. Rewrite it with params = or glue_sql().

19.5 When to push and when to pull

Figure 19.2 shows the central idea of this chapter in one picture. collect() is a boundary. Everything to its left runs in the database on the full data. 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 19.2: 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 modeling, plotting, iteration, and anything else statistical. A useful test is the following. If a step makes the data smaller, it usually belongs before collect(). If it needs R’s statistical machinery (fitting a mixed model, drawing a figure), it belongs after.

Two cautions apply. First, not every R function has a SQL translation. dbplyr says when it cannot translate a call, and you then 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 slows every other user, so test on a LIMIT-ed subset first.

19.5.1 DuckDB and Parquet for larger-than-memory data

Sometimes the data are files rather than a server, and too large to read into R comfortably. The default tool for this case, as of 2026, is DuckDB, an in-process analytical database. DuckDB reads Parquet, a compressed columnar file format that arrow (Richardson et al., 2026) writes. It queries the file where it lies and reads only the columns a query uses. The chunk below runs only if duckdb and arrow are installed.

labs_parquet <- file.path(tempdir(), "labs.parquet")
arrow::write_parquet(read_hf("labs.csv"), labs_parquet)

duck <- dbConnect(duckdb::duckdb())
dbGetQuery(duck, glue::glue_sql("
  SELECT test_code, COUNT(*) AS n, MEDIAN(value) AS median_value
  FROM read_parquet({labs_parquet})
  GROUP BY test_code
  ORDER BY test_code
", .con = duck))
#>   test_code    n median_value
#> 1     CREAT 6318         1.29
#> 2       HGB 2433        12.10
#> 3         K 6319         4.30
#> 4  NTPROBNP 4082      3019.50
#> 5    SODIUM 4081       137.00
dbDisconnect(duck, shutdown = TRUE)

The same DBI and dbplyr code works against the DuckDB connection. Only the dbConnect() line and the file-reading function differ. The counts include the two duplicated rows and the orphan patient. A change of file format does not correct the data in it.

Question. You need the median NT-proBNP by site from a 40-gigabyte lab extract delivered as Parquet files, on a laptop with 16 gigabytes of memory. Where does each step run?

Answer. Filter to NT-proBNP, join to the site table, and compute the median per site in DuckDB, which reads the Parquet files in pieces and needs only the columns the query names. Only the per-site medians, a few rows, cross into R. Reading the files into R first with arrow::read_parquet() would try to hold the whole extract in memory.

19.6 Worked example: a reproducible extract

A committed query records how an extract was defined. It does not record what the extract contained, because a warehouse changes. Records are corrected, late lab results arrive, and a patient withdraws consent and their rows are purged. Run the same query six months later and you get a different table, with no error. We therefore treat the extract, not the query, as the raw data (Chapter 14). Run the query once, deliberately, and write the result to a dated file. Keep the query text and a checksum of the file beside it. The analysis reads the file, and the query stays as provenance.

extract_dir <- file.path(tempdir(), "extract")
dir.create(extract_dir, showWarnings = FALSE)

query <- "
SELECT r.patient_id, r.site, r.ef_stratum, r.arm,
       o.ascertained, o.event_30d
FROM randomization AS r
LEFT JOIN outcomes AS o ON r.patient_id = o.patient_id
"
writeLines(query, file.path(extract_dir, "analytic.sql"))

analytic <- dbGetQuery(con, query)
stopifnot(nrow(analytic) == n_patients,
          !anyDuplicated(analytic$patient_id))

extracted_on <- format(Sys.Date())
csv_path <- file.path(extract_dir,
                      paste0("analytic-", extracted_on, ".csv"))
readr::write_csv(analytic, csv_path)

writeLines(c(
  paste("extracted_on:", extracted_on),
  "source: HF-HOME teaching database (SQLite, in memory)",
  "query: analytic.sql",
  paste("rows:", nrow(analytic)),
  paste("md5:", unname(tools::md5sum(csv_path)))
), file.path(extract_dir, "analytic-provenance.txt"))

list.files(extract_dir)
#> [1] "analytic-2026-11-08.csv" "analytic-provenance.txt"
#> [3] "analytic.sql"

The query is a left join from randomization, so every randomized patient appears, whatever outcomes holds. The assertions run before the file is written. On a real warehouse, add the warehouse’s snapshot or refresh identifier to the provenance file if it publishes one.

TipVerification

Read the extract back as the analysis will, and confirm that it is the file the provenance record describes.

provenance <- readLines(file.path(extract_dir,
                                  "analytic-provenance.txt"))
back <- readr::read_csv(csv_path, show_col_types = FALSE,
                        col_types = readr::cols(patient_id = "c"))
stopifnot(
  nrow(back) == n_patients,
  !anyDuplicated(back$patient_id),
  paste("md5:", unname(tools::md5sum(csv_path))) %in% provenance
)
provenance
#> [1] "extracted_on: 2026-11-08"                             
#> [2] "source: HF-HOME teaching database (SQLite, in memory)"
#> [3] "query: analytic.sql"                                  
#> [4] "rows: 2432"                                           
#> [5] "md5: 3ad2ef757e45961ed82eb1a0155188d4"

Question. Your analysis begins by running a SQL query against the hospital warehouse, and the query is committed. Is the analysis reproducible?

Answer. The code is reproducible, but the analysis is not. A committed query is an instruction whose result depends on when it ran. A file in data/raw/ does not change in that way. The dated extract with its query, row count, and checksum is what makes the analysis reproducible, and it is the file that gets archived. Chapter 14 gives an acquisition script the same role for a different source.

19.7 Rules of thumb

  1. Habit 6: count before and after every join, in SQL too. SQL has no relationship argument. Audit uniqueness and coverage with HAVING and an anti-join, and assert the count after the result reaches R.
  2. Habit 2: raw data are evidence. The dated extract is the raw data, and the query is its provenance. The analysis reads the file, never the live warehouse.
  3. Habit 8: a bug that produces a plausible number is the expensive kind. A pasted query string and a silently skipped NULL both return a tidy table. Parameterize, and count the denominator explicitly.

19.8 Cumulative practice

  1. Cumulative (uses Chapter 18). Work through Table 18.1 in SQL for the join of labs to visits to randomization in this chapter’s database. Write each audit as a query, and predict the row count of the join for the day-30 clinic creatinine. Then run it, and assert the prediction in R.
  2. On your own work (no key). Take a database you query, or a DuckDB file built from data you have. Replace one pasted query string with a parameterized one, and write the extract it feeds to a dated file with its query and checksum.
con <- dbConnect(RSQLite::SQLite(), ":memory:",
                 extended_types = TRUE)
dbWriteTable(con, "randomization", read_hf("randomization.csv"))
dbWriteTable(con, "visits", read_hf("visits.csv"))
dbWriteTable(con, "labs", read_hf("labs.csv"))

dups <- dbGetQuery(con, "
  SELECT l.visit_id, COUNT(*) AS n
  FROM labs AS l JOIN visits AS v ON l.visit_id = v.visit_id
  WHERE l.test_code = 'CREAT' AND v.visit_type = 'clinic_day30'
  GROUP BY l.visit_id HAVING COUNT(*) > 1")
orphans <- dbGetQuery(con, "
  SELECT DISTINCT v.patient_id
  FROM visits AS v
  LEFT JOIN randomization AS r ON v.patient_id = r.patient_id
  WHERE r.patient_id IS NULL")
expected <- dbGetQuery(con, "
  SELECT COUNT(*) AS n FROM visits
  WHERE visit_type = 'clinic_day30'")$n

day30_creat <- dbGetQuery(con, "
  SELECT r.patient_id, r.arm, l.value
  FROM labs AS l
  JOIN visits AS v        ON l.visit_id = v.visit_id
  JOIN randomization AS r ON v.patient_id = r.patient_id
  WHERE l.test_code = 'CREAT' AND v.visit_type = 'clinic_day30'")

c(duplicates = nrow(dups), orphans = nrow(orphans),
  expected = expected, rows = nrow(day30_creat))
#> duplicates    orphans   expected       rows 
#>          0          0       1648       1648
stopifnot(nrow(dups) == 0, nrow(orphans) == 0,
          nrow(day30_creat) == expected,
          !anyDuplicated(day30_creat$patient_id))
dbDisconnect(con)

The prediction is one row per day-30 clinic visit, because no creatinine at that visit is duplicated and every patient in visits was randomized. The orphan 2433 has a lab row but no visit, so it cannot enter a join that goes through visits. The anti-join that finds it is the one from labs to randomization in Section 19.3. Not every patient has a day-30 visit, so the count is smaller than the number randomized. That is the expected value, not a defect.

19.9 Cheat sheet

Table 19.2: Databases from R at a glance.
Task Code
Connect in memory dbConnect(RSQLite::SQLite(), ":memory:", extended_types = TRUE)
Connect to DuckDB dbConnect(duckdb::duckdb())
Load a data frame dbWriteTable(con, "name", df)
List tables dbListTables(con)
Run SQL, get a data frame dbGetQuery(con, "SELECT ...")
Pass a value safely dbGetQuery(con, "... WHERE id = ?", params = list(id))
Build SQL with quoting glue::glue_sql("... IN ({ids*})", .con = con)
Lazy table tbl(con, "name")
See the generated SQL show_query(q)
Bring the result into R collect(q)
Find duplicated keys GROUP BY key HAVING COUNT(*) > 1
Find orphans LEFT JOIN y ON x.key = y.key WHERE y.key IS NULL
Query a Parquet file SELECT ... FROM read_parquet('file.parquet') in DuckDB
Keep credentials out of code keyring::key_get(), Sys.getenv()
Close the connection dbDisconnect(con)

19.10 Further reading

  • (Wickham et al., 2023) Chapter 21, databases with DBI and dbplyr.
  • The DBI and dbplyr documentation, at dbi.r-dbi.org and dbplyr.tidyverse.org, including the list of translated functions.
  • The DuckDB documentation at duckdb.org, for querying CSV and Parquet files without a server.

19.11 Prerequisites answers

  1. dbGetQuery() sends a query and returns only its result. A SELECT with filtering and aggregation returns a small summary, whatever the table size. dbReadTable() reads an entire table into memory. 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 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 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 a dated extract and its checksum documents the snapshot the analysis rested on.