library(DBI)
library(dplyr)
library(dbplyr)
con <- dbConnect(duckdb::duckdb())Querying Parquet with SQL from R, no warehouse needed
r
duckdb
sql
parquet
DuckDB + dbplyr: write dplyr, run SQL, read parquet. All in-process.
A lot of data engineering work is just moving tables around and aggregating them. You don’t always need a cluster for that. DuckDB is an in-process analytical database that reads Parquet directly, and with dbplyr you can write dplyr code that gets translated to SQL.
Create some “raw” data as Parquet
set.seed(42)
transactions <- data.frame(
id = 1:10000,
customer = sample(sprintf("C%03d", 1:200), 10000, replace = TRUE),
amount = round(rexp(10000, 1 / 80), 2),
day = as.Date("2026-01-01") + sample(0:89, 10000, replace = TRUE)
)
path <- tempfile(fileext = ".parquet")
duckdb::duckdb_register(con, "tx_df", transactions)
dbExecute(con, sprintf("COPY tx_df TO '%s' (FORMAT PARQUET)", path))[1] 10000
Query the file lazily with dplyr
tbl() points at the parquet file, nothing is loaded into R yet.
tx <- tbl(con, sql(sprintf("SELECT * FROM read_parquet('%s')", path)))
monthly <- tx |>
mutate(month = date_trunc("month", day)) |>
group_by(month) |>
summarise(
n_tx = n(),
customers = n_distinct(customer),
revenue = sum(amount, na.rm = TRUE)
) |>
arrange(month)This is the SQL dbplyr generated:
show_query(monthly)<SQL>
SELECT
"month",
COUNT(*) AS n_tx,
COUNT(DISTINCT row(customer)) AS customers,
SUM(amount) AS revenue
FROM (
SELECT q01.*, date_trunc('month', "day") AS "month"
FROM (SELECT * FROM read_parquet('C:\Users\ap_da\AppData\Local\Temp\RtmpmYMvdD\file14b686d74f70.parquet')) q01
) q01
GROUP BY "month"
ORDER BY "month"
And only now do we pull the (small) result into R:
collect(monthly)# A tibble: 3 × 4
month n_tx customers revenue
<dttm> <dbl> <dbl> <dbl>
1 2026-01-01 00:00:00 3467 200 283743.
2 2026-02-01 00:00:00 3118 200 248429.
3 2026-03-01 00:00:00 3415 200 275635.
The same pattern scales up: swap DuckDB for Spark (sparklyr) or Databricks SQL and the dplyr code stays the same.