r-duckplyr

duckplyr: DuckDB-Backed dplyr

Overview

duckplyr is a drop-in replacement for dplyr powered by DuckDB for speed and memory efficiency. It uses identical syntax but lazy evaluation - operations execute only when results are needed, enabling processing of datasets larger than available RAM.

References

Read references/API.md before writing code.

• references/API.md - Complete function reference and lazy evaluation patterns

When to Use duckplyr vs Alternatives

Use duckplyr when...	Use dplyr when...	Use duckspatial when...	Use data.table when...
Data >100k rows	Small datasets (<100k)	Spatial operations	In-place modification (:=)
Larger-than-memory files	All data fits in RAM	Geospatial joins/buffers	Reference semantics
Parquet/CSV on disk	Already in memory	DuckDB + spatial queries	Non-equi joins
Lazy pipeline optimization	Immediate results	PMTiles, vector tiles	Keyed/rolling joins

Key insight: duckplyr works on files without loading into R - queries Parquet/CSV directly from disk or URLs.

Quick Start

library(duckplyr)

# Convert existing data frame
df <- as_duckdb_tibble(my_data)

# Or read files directly (lazy evaluation)
df <- read_parquet_duckdb("large_file.parquet")

# Standard dplyr syntax
result <- df |>
  filter(year == 2024) |>
  group_by(category) |>
  summarise(total = sum(value)) |>
  collect()  # Materializes result

Critical Differences from dplyr

Difference	dplyr	duckplyr
Function name	N/A	as_duckdb_tibble() (not as_duck_frame())
Evaluation	Eager (immediate)	Lazy (until collect())
Sorting	Auto-sorts groups	NO auto-sort - use arrange()
NULL handling	na.rm = FALSE default	Excludes NULLs by default
Materialization	Always in memory	Controlled by prudence parameter

Prudence Levels (Memory Protection)

• "lavish": Converts regardless of size (may OOM)
• "thrifty": Max 1 million cells (default)
• "stingy": Never auto-converts (safest for large data)

read_parquet_duckdb("file.parquet", prudence = "stingy")

Quick Reference

Task	Function
Read Parquet	read_parquet_duckdb(path, prudence = "stingy")
Read CSV/JSON	read_csv_duckdb(), read_json_duckdb()
Multiple files	read_parquet_duckdb("data_*.parquet") (globs)
Convert data frame	as_duckdb_tibble(df)
Bring to R	collect() (materializes in R memory)
Cache in DuckDB	compute() (temp table)
Write file	compute_parquet(), compute_csv()
Remote data (HTTP/S3)	db_exec("INSTALL httpfs"), then use URLs
Query plan	explain(df |> filter(...))
Memory limit	db_exec("PRAGMA memory_limit = '4GB'")

Common Mistakes

Mistake	Fix
as_duck_frame()	Use as_duckdb_tibble()
Early collect()	Keep lazy until end
No prudence setting	Set prudence = "stingy" for large files
Expecting auto-sort	Use explicit arrange()
arrow/readr instead	Use read_*_duckdb() functions
Missing httpfs	db_exec("INSTALL httpfs") for URLs
No compute() caching	Cache expensive intermediates

When NOT to Use

• Small data (<100k rows) - dplyr, collapse, data.table faster
• Spatial operations - use duckspatial
• In-place modification - use data.table or collapse

See references/API.md for complete function reference