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.

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.

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. The keycol argument names the column that supplies row names.

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

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
#> Honda Civic        30.4        4
#> Toyota Corolla     33.9        4
#> Lotus Europa       30.4        4
#> Fiat 128           32.4        4
#> Porsche 914-2      26.0        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

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", 1)[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

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.6 IRanges_2.47.2        
#> [4] S4Vectors_0.51.5       BiocGenerics_0.59.10   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.22-9       
#>  [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-5          DBI_1.3.0            
#> [13] BiocManager_1.30.27   purrr_1.2.2           jquerylib_0.1.4      
#> [16] duckdb_1.5.4.3        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.3  
#> [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.11.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