Skip to main content

Querying Parquet with SQL from R, no warehouse needed

r
duckdb
sql
parquet
DuckDB + dbplyr: write dplyr, run SQL, read parquet. All in-process.
Author

Athos Damiani

Published

November 17, 2026

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.

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

con <- dbConnect(duckdb::duckdb())

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.