Introduction

Bioconductor objects increasingly carry large tabular metadata: a SingleCellExperiment with millions of cells has a colData of the same height, a big sample sheet, per-feature annotations. Holding such a table fully in memory is wasteful when a workflow only ever reads a few columns or a filtered subset of rows.

DuckDBDataFrame is a S4Vectors DataFrame backed by DuckDB over columnar Parquet. It behaves like an ordinary DataFrame, $, [, mcols(), cbind(), but the data stays on disk and operations are recorded as lazy SQL queries: you can subset, add computed columns, and aggregate without loading the table into memory. Each column read comes back only when you ask for it.

The typical pattern is that an upstream tool (or an earlier pipeline step) writes a table, often larger than memory, to Parquet, and DuckDBDataFrame opens it in place: you explore and query it with the familiar DataFrame API while DuckDB reads only the columns and rows each operation touches.

It is the tabular foundation of the BiocDuckDB suite: DuckDBArray (DuckDB-backed DelayedArray) and DuckDBGRanges (DuckDB-backed GRanges) both build on it.

This vignette is a practical introduction. For the design of the underlying DuckDBTable and how other packages extend it, see Design and extension of DuckDBDataFrame.

Relationship to arrow

If you have used arrow, a DuckDBDataFrame is lazy over Parquet in much the same way as an Arrow FileSystemDataset: the data stays on disk and operations are deferred. The differences are in the interface and the engine:

No explicit collect(). An Arrow dataset requires collect() (or as.data.frame()) to realize results before most R operations. A DuckDBDataFrame presents the full DataFrame API directly, head(), [, $, arithmetic, mean(), aggregation all work on the lazy object, and only the values you actually extract (e.g. as.vector(), as.data.frame()) are pulled into memory. Each operation is pushed down to DuckDB and evaluated on demand.
DuckDB engine, DataFrame API. Computation runs in DuckDB’s SQL engine rather than the Arrow compute kernels, and the object behaves like a Bioconductor DataFrame (so it drops into SingleCellExperiment, SummarizedExperiment, etc.) rather than a dplyr/Arrow pipeline. The tradeoff is a dependency on DuckDB and that results are materialized through it.
Multi-file datasets are supported: point the constructor at a directory or a glob (e.g. "data/*.parquet") and DuckDB scans the parts as one table.

Installation

if (!require("BiocManager"))
    install.packages("BiocManager")
BiocManager::install("DuckDBDataFrame")

library(DuckDBDataFrame)

Quick start

We write the built-in mtcars table to Parquet and open it as a DuckDBDataFrame.

library(arrow)
mtcars_df <- cbind(model = rownames(mtcars), mtcars)
path <- tempfile(fileext = ".parquet")
write_parquet(mtcars_df, path)

df <- DuckDBDataFrame(path, datacols = colnames(mtcars),
                      keycol = list(model = mtcars_df$model))
df
#> DuckDBDataFrame with 32 rows and 11 columns
#>                        mpg      cyl     disp       hp     drat       wt
#>                   <double> <double> <double> <double> <double> <double>
#> Mazda RX4             21.0        6      160      110     3.90    2.620
#> Mazda RX4 Wag         21.0        6      160      110     3.90    2.875
#> Datsun 710            22.8        4      108       93     3.85    2.320
#> Hornet 4 Drive        21.4        6      258      110     3.08    3.215
#> Hornet Sportabout     18.7        8      360      175     3.15    3.440
#> ...                    ...      ...      ...      ...      ...      ...
#> Lotus Europa          30.4        4     95.1      113     3.77    1.513
#> Ford Pantera L        15.8        8    351.0      264     4.22    3.170
#> Ferrari Dino          19.7        6    145.0      175     3.62    2.770
#> Maserati Bora         15.0        8    301.0      335     3.54    3.570
#> Volvo 142E            21.4        4    121.0      109     4.11    2.780
#>                       qsec       vs       am     gear     carb
#>                   <double> <double> <double> <double> <double>
#> Mazda RX4            16.46        0        1        4        4
#> Mazda RX4 Wag        17.02        0        1        4        4
#> Datsun 710           18.61        1        1        4        1
#> Hornet 4 Drive       19.44        1        0        3        1
#> Hornet Sportabout    17.02        0        0        3        2
#> ...                    ...      ...      ...      ...      ...
#> Lotus Europa          16.9        1        1        5        2
#> Ford Pantera L        14.5        0        1        5        4
#> Ferrari Dino          15.5        0        1        5        6
#> Maserati Bora         14.6        0        1        5        8
#> Volvo 142E            18.6        1        1        4        2

Only the path is required, DuckDBDataFrame(path) opens every column and uses generated row numbers. The other arguments are optional refinements:

datacols selects (and orders) which columns to expose; omit it to take all.
keycol designates the row key, the DuckDBDataFrame equivalent of a DataFrame’s row names. It takes two forms: a column name already in the file (keycol = "model"), which promotes that column to the key; or a named list of values (keycol = list(model = mtcars_df$model)), which supplies the key from R, handy when the key is not stored as a column. Both give a DataFrame keyed by model; they differ only in where the key comes from (and therefore its row order). With no keycol, rows are addressed by generated numbers instead of names. The deeper key-dimension model is covered in Design and extension of DuckDBDataFrame.

It looks and behaves like a DataFrame, but the values live in the Parquet file:

dim(df)
#> [1] 32 11
df$mpg[1:5]
#> DuckDBColumn of length 5
#>         Mazda RX4     Mazda RX4 Wag        Datsun 710    Hornet 4 Drive 
#>              21.0              21.0              22.8              21.4 
#> Hornet Sportabout 
#>              18.7

Working with a DuckDBDataFrame

Column access

$ returns a single column as a DuckDBColumn (still lazy); [ selects columns:

df[, c("mpg", "cyl", "hp")]
#> DuckDBDataFrame with 32 rows and 3 columns
#>                        mpg      cyl       hp
#>                   <double> <double> <double>
#> Mazda RX4             21.0        6      110
#> Mazda RX4 Wag         21.0        6      110
#> Datsun 710            22.8        4       93
#> Hornet 4 Drive        21.4        6      110
#> Hornet Sportabout     18.7        8      175
#> ...                    ...      ...      ...
#> Lotus Europa          30.4        4      113
#> Ford Pantera L        15.8        8      264
#> Ferrari Dino          19.7        6      175
#> Maserati Bora         15.0        8      335
#> Volvo 142E            21.4        4      109

Row subsetting

Rows can be selected by name, by a logical column, or by position (note that positional order is not guaranteed, as rows map to a set on disk):

df[df$mpg > 25, c("mpg", "cyl")]
#> DuckDBDataFrame with 6 rows and 2 columns
#>                     mpg      cyl
#>                <double> <double>
#> Fiat X1-9          27.3        4
#> Fiat 128           32.4        4
#> Porsche 914-2      26.0        4
#> Honda Civic        30.4        4
#> Toyota Corolla     33.9        4
#> Lotus Europa       30.4        4

Computed columns

Assigning an expression of existing columns records a new lazy column; nothing is evaluated until the values are pulled:

df$efficiency <- df$mpg / df$hp
df[1:3, c("mpg", "hp", "efficiency")]
#> DuckDBDataFrame with 3 rows and 3 columns
#>                    mpg       hp efficiency
#>               <double> <double>   <double>
#> Mazda RX4         21.0      110   0.190909
#> Mazda RX4 Wag     21.0      110   0.190909
#> Datsun 710        22.8       93   0.245161

This changes only the in-memory DuckDBDataFrame object: efficiency is stored as a SQL expression (mpg / hp) in the object’s query and computed on demand. The Parquet file on disk is not modified, the new column exists only for this object and any results derived from it. To persist a derived table (including computed columns) back to disk, materialize it (as.data.frame()) and write it out explicitly, e.g. with arrow::write_parquet().

Column metadata

mcols() works as it does for any DataFrame:

mcols(df) <- DataFrame(row.names = colnames(df),
                       label = sub("\\..*", "", colnames(df)))
mcols(df)[1:3, , drop = FALSE]
#> DataFrame with 3 rows and 1 column
#>            label
#>      <character>
#> mpg          mpg
#> cyl          cyl
#> disp        disp

Columns come in three flavors

Depending on the underlying Parquet type, extracting a column yields:

a DuckDBColumn for atomic columns, vector-like and lazy (length(), [, arithmetic, mean()), materialized with as.vector();
a DuckDBAtomicList for DuckDB LIST[] columns (variable-length list columns), supporting elementNROWS(), [[, unlist();
a DuckDBEmbeddings for DuckDB ARRAY[n] columns (fixed-length numeric vectors, e.g. embeddings), which behaves like a matrix with one row per element.

eff <- df$mpg / df$hp     # DuckDBColumn
class(eff)
#> [1] "DuckDBColumn"
#> attr(,"package")
#> [1] "DuckDBDataFrame"
as.vector(eff)[1:5]       # materialize on demand
#>         Mazda RX4     Mazda RX4 Wag        Datsun 710    Hornet 4 Drive 
#>         0.1909091         0.1909091         0.2451613         0.1945455 
#> Hornet Sportabout 
#>         0.1068571

Reaching for SQL directly

Because the backend is DuckDB, its full SQL function library is available. sql_fun() lists functions applicable to a column, and sql_call() applies one:

sql_call(df$mpg, "round", 0)[1:5]
#> DuckDBColumn of length 5
#>         Mazda RX4     Mazda RX4 Wag        Datsun 710    Hornet 4 Drive 
#>                21                21                23                21 
#> Hornet Sportabout 
#>                19

For custom work you can reach the shared connection with dbconn(df) and run arbitrary DBI::dbGetQuery() calls against it.

When to use DuckDBDataFrame

A good fit when the table is larger than memory (or you want to keep memory free), when the workload is columnar (filtering, aggregation, selecting a few columns of a wide table), or when the data already lives on disk as Parquet that other tools should read. An in-memory DataFrame remains preferable for small tables and for row-wise or heavy random-access work.

For how the DuckDBTable abstraction works and how to build on it, see Design and extension of DuckDBDataFrame.

Session information

sessionInfo()
#> R version 4.6.1 (2026-06-24)
#> Platform: x86_64-pc-linux-gnu
#> Running under: Ubuntu 26.04 LTS
#> 
#> Matrix products: default
#> BLAS:   /usr/lib/x86_64-linux-gnu/openblas-pthread/libblas.so.3 
#> LAPACK: /usr/lib/x86_64-linux-gnu/openblas-pthread/libopenblasp-r0.3.32.so;  LAPACK version 3.12.0
#> 
#> locale:
#>  [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C              
#>  [3] LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8    
#>  [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8   
#>  [7] LC_PAPER=en_US.UTF-8       LC_NAME=C                 
#>  [9] LC_ADDRESS=C               LC_TELEPHONE=C            
#> [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       
#> 
#> time zone: Etc/UTC
#> tzcode source: system (glibc)
#> 
#> attached base packages:
#> [1] stats4    stats     graphics  grDevices utils     datasets  methods  
#> [8] base     
#> 
#> other attached packages:
#> [1] arrow_25.0.0            DuckDBDataFrame_0.99.23 IRanges_2.47.2         
#> [4] S4Vectors_0.51.6        BiocGenerics_0.59.12    generics_0.1.4         
#> [7] bit64_4.8.2             BiocStyle_2.41.0       
#> 
#> loaded via a namespace (and not attached):
#>  [1] sass_0.4.10           SparseArray_1.13.2    lattice_0.23-1       
#>  [4] digest_0.6.39         magrittr_2.0.5        evaluate_1.0.5       
#>  [7] grid_4.6.1            blob_1.3.0            fastmap_1.2.0        
#> [10] jsonlite_2.0.0        Matrix_1.7-6          DBI_1.3.0            
#> [13] BiocManager_1.30.27   purrr_1.2.2           jquerylib_0.1.4      
#> [16] duckdb_1.5.5          abind_1.4-8           cli_3.6.6            
#> [19] rlang_1.3.0           dbplyr_2.6.0          XVector_0.53.0       
#> [22] withr_3.0.3           cachem_1.1.0          DelayedArray_0.39.5  
#> [25] yaml_2.3.12           otel_0.2.0            S4Arrays_1.13.0      
#> [28] tools_4.6.1           dplyr_1.2.1           assertthat_0.2.1     
#> [31] buildtools_1.0.0      vctrs_0.7.3           R6_2.6.1             
#> [34] matrixStats_1.5.0     lifecycle_1.0.5       bit_4.6.0            
#> [37] pkgconfig_2.0.3       bslib_0.12.0          pillar_1.11.1        
#> [40] glue_1.8.1            xfun_0.60             tibble_3.3.1         
#> [43] tidyselect_1.2.1      sys_3.4.3             MatrixGenerics_1.25.0
#> [46] knitr_1.51            htmltools_0.5.9       rmarkdown_2.31       
#> [49] maketools_1.3.2       compiler_4.6.1

Introduction to DuckDBDataFrame