⌘K ctrl+k
Search Shortcut cmd + k | ctrl + k

duckplyr

duckplyr is a drop-in replacement for dplyr, powered by DuckDB. It runs existing dplyr code with the same results, using DuckDB's execution engine where possible and falling back to dplyr where it is not. It can also analyze larger-than-memory datasets directly from files on disk or on the web. duckplyr is developed by Posit, cynkra, and DuckDB Labs.

Unlike dbplyr, duckplyr does not generate SQL strings: it translates dplyr verbs directly into DuckDB's relational API. For the full documentation, visit duckplyr.tidyverse.org.

Installation

duckplyr is available on CRAN and installs the DuckDB R client as a dependency:

install.packages("duckplyr")

Usage as a Drop-In Replacement

Loading duckplyr overrides the dplyr methods for data frames for the rest of the session. Existing dplyr code then runs on DuckDB without changes:

library("duckplyr")

flights_df() |>
  filter(!is.na(arr_delay), !is.na(dep_delay)) |>
  mutate(inflight_delay = arr_delay - dep_delay) |>
  summarize(
    .by = c(year, month),
    mean_inflight_delay = mean(inflight_delay),
    median_inflight_delay = median(inflight_delay),
  ) |>
  filter(month <= 6) |>
  arrange(month)
# A tibble: 6 × 4
   year month mean_inflight_delay median_inflight_delay
  <int> <int>               <dbl>                 <dbl>
1  2013     1               -3.86                    -5
2  2013     2               -5.15                    -6
3  2013     3               -7.36                    -9
4  2013     4               -2.67                    -5
5  2013     5               -9.37                   -10
6  2013     6               -4.24                    -7

To restore the original dplyr methods, call methods_restore() or restart the R session.

Usage for Individual Data Frames

To use DuckDB only for selected data frames, convert them with as_duckdb_tibble() and use dplyr as usual. In this case, there is no need to attach duckplyr:

library("dplyr")

df <- duckplyr::as_duckdb_tibble(data.frame(n = 1:10))

df |>
  mutate(m = n + 1) |>
  filter(m > 5) |>
  count()

The duckdb_tibble() function creates a duckplyr data frame from columns, similar to tibble(). To inspect the DuckDB query plan of a pipeline, use explain().

Reading and Writing Files

duckplyr uses DuckDB's readers to process files without loading them into R's memory first:

  • read_csv_duckdb() for CSV files
  • read_parquet_duckdb() for Parquet files
  • read_json_duckdb() for JSON files
  • read_file_duckdb() for any other format supported by a DuckDB table function

These functions accept local paths, glob patterns, and URLs. To query remote files, install and load the httpfs extension first:

library("duckplyr")

db_exec("INSTALL httpfs")
db_exec("LOAD httpfs")

flights <- read_parquet_duckdb(
  "https://blobs.duckdb.org/flight-data-partitioned/Year=2024/data_0.parquet"
)

flights |>
  count(Reporting_Airline, sort = TRUE) |>
  head(3) |>
  collect()

To write the result of a pipeline to a file without materializing it in R, use compute_parquet() or compute_csv():

flights |>
  filter(Month == 1) |>
  compute_parquet("flights_january.parquet")

The db_exec() function runs any SQL statement on the default DuckDB connection used by duckplyr, e.g., to load extensions or to change settings.

Lazy Evaluation and Prudence

Computations in duckplyr are lazy: DuckDB only runs the query when the result is accessed, e.g., when it is printed or when a column is accessed. To protect memory, the prudence setting controls when a duckplyr data frame is materialized automatically:

Prudence Automatic materialization Default for
lavish Always duckdb_tibble(), as_duckdb_tibble()
thrifty Only for small results (up to 1 million cells for local data) read_*_duckdb() functions
stingy Never –

When automatic materialization is not allowed, accessing the data, e.g., with nrow(), throws an error. In this case, use collect() to materialize the result explicitly:

flights |> collect() |> nrow()

The prudence level can be set with the prudence argument of as_duckdb_tibble() and the read_*_duckdb() functions. For details, see the “Memory protection” article.

Fallback to dplyr

duckplyr supports the most common dplyr verbs and functions. If an operation cannot be translated to DuckDB, duckplyr falls back to dplyr transparently, and the result is computed in R. With the stingy prudence level, fallbacks are blocked, which ensures that a pipeline runs entirely in DuckDB. To review the fallback status, use fallback_sitrep(). For the supported verbs and functions, see the “Translations” article.

Further Reading

© 2026 DuckDB Foundation, Amsterdam NL
DuckDB Home Code of Conduct Trademark Use Blog