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)
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.
- What is the difference between querying a table with
DBI::dbGetQuery()and reading a whole table into memory withdbReadTable(), and why does the distinction matter for a ten-million-row table? - In the
dbplyrmodel of database access, what does a pipeline ofdplyrverbs actually do before you callcollect(), and where does the computation happen? - 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.
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.2365402The 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.
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.237summary_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.237na.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.
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.
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
- 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.csvand 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.
- Express the query from exercise 1 with
dbplyr, print the SQL it generates withshow_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 2433Two 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 2433The 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 TRUEA 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.
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
- Write one SQL query that classifies every duplicated visit-test pair in
labsasexact(all rows have the same value) orconflicting. Confirm the classification in R withdplyr.
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.
- Using a window function (in SQL or through
dbplyr’swindow_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-memorydplyrcomputation onvisits.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 healthThe 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.
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')Use a model to draft a query from the schema, never from the data or the connection.
Context. A SQLite database has four tables.
randomizationandoutcomeshave one row perpatient_id(character, leading zeros);visitshas one row pervisit_id, withpatient_id,visit_type, andvisit_day;labshas one row pervisit_idandtest_code, withvalue. Here are the column names and types: [output ofstr()on a synthetic row of each table].Constraints. Write the query twice, in SQL and with
dbplyr. Take the site as a parameter (?withparams =), not a pasted string. Use an inner join only where I say unmatched rows should be dropped. Do not useSELECT DISTINCTto 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
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.
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
-
Habit 6: count before and after every join, in SQL too. SQL has no
relationshipargument. Audit uniqueness and coverage withHAVINGand an anti-join, and assert the count after the result reaches R. - 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.
-
Habit 8: a bug that produces a plausible number is the expensive kind. A pasted query string and a silently skipped
NULLboth return a tidy table. Parameterize, and count the denominator explicitly.
19.8 Cumulative practice
-
Cumulative (uses Chapter 18). Work through Table 18.1 in SQL for the join of
labstovisitstorandomizationin 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. - 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
| 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
DBIanddbplyr. - The
DBIanddbplyrdocumentation, atdbi.r-dbi.organddbplyr.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
-
dbGetQuery()sends a query and returns only its result. ASELECTwith 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. UsedbGetQuery()(or lazydbplyr) to let the database do the reduction. - A
dbplyrpipeline builds a query lazily. Each verb adds to a SQL statement that has not yet run. No computation crosses into R until you callcollect()(or print, which fetches a preview). The computation happens in the database; R receives only the collected result. - 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.