Interact with 'DuckLake' from R

A 'tidyverse'-friendly interface to 'DuckLake' < https://ducklake.select/>, the 'DuckDB' lakehouse format. Attach versioned data lakes from R and work with them using familiar 'dplyr' verbs, with support for ACID transactions, time travel queries, snapshot audit trails, data inlining, encrypted storage, multiple catalog backends ('DuckDB', 'PostgreSQL', 'SQLite', 'MySQL'), and remote access over the 'Quack' protocol from 'DuckDB'.

ducklake

ducklake is an R package that brings versioned data lake infrastructure to data-intensive workflows. Built on DuckDB and DuckLake, it provides ACID transactions, automatic versioning, time travel queries, and complete audit trails.

Why DuckLake?

Many industries rely on flat-file workflows (CSV, XPT, Excel, etc.) that create significant data management challenges:

Disconnected flat files: Related datasets stored as separate files despite being inherently relational
Lost audit trails: No automatic tracking of who changed what and when
Version control gaps: Multiple dataset versions scattered across folders with unclear provenance
Reproducibility issues: Inability to recreate analyses from specific time points
Collaboration friction: Multiple analysts working with different versions of the same data
Compliance challenges: Difficulty demonstrating data integrity and audit trails for regulated industries

DuckLake solves these problems by implementing a versioned data lake architecture that:

Preserves relational structure between related datasets
Automatically versions every data change with timestamps and metadata
Enables time travel to recreate analyses exactly as they were run
Provides complete audit trails with author attribution and commit messages
Supports layered architecture (bronze/silver/gold) for data lineage from raw to analysis-ready
Allows multiple team members to collaborate safely with shared data

Installation

install.packages("ducklake")

Development version

pak::pak("tgerke/ducklake-r")

ducklake requires the duckdb R package version 1.5.1 or newer (DuckDB engine 1.5.1+, matching the stable DuckLake v1.0 specification). The Quack remote-access features are the exception: they need DuckDB 1.5.3 or newer, which means duckdb 1.5.4 or newer from CRAN.

DuckLake itself ships as a DuckDB extension that is downloaded on first use. Run install_ducklake() once per machine, or check whether you already have it with ducklake_extension_available().

ducklake manages its own DuckDB connection, so there is nothing to set up: just attach_ducklake() and go. If you prefer to supply your own connection (for example, one shared with other DBI-based tools), register it with set_ducklake_connection().

Quick example: Layered data workflow

library(ducklake)
library(dplyr)

# Install the ducklake extension (requires duckdb R package >= 1.5.1)
install_ducklake()

# Create a data lake in a temporary directory
attach_ducklake("my_data_lake", lake_path = tempdir())

# Bronze layer: Load raw data exactly as received
with_transaction(
  create_table(mtcars, "vehicles_raw"),
  author = "Data Engineer",
  commit_message = "Initial load of raw vehicle data"
)

# Silver layer: Apply cleaning transformations
with_transaction(
  get_ducklake_table("vehicles_raw") |>
    mutate(cyl = as.character(cyl)) |>
    create_table("vehicles_clean"),
  author = "Data Engineer", 
  commit_message = "Clean and standardize vehicle data"
)

# Gold layer: Create analysis dataset with business logic
with_transaction(
  get_ducklake_table("vehicles_clean") |>
    mutate(
      efficiency = case_when(
        mpg < 15 ~ "Low",
        mpg < 25 ~ "Medium",
        TRUE ~ "High"
      )
    ) |>
    create_table("vehicles_analysis"),
  author = "Data Analyst",
  commit_message = "Create analysis-ready dataset with efficiency categories"
)

# Update the silver layer with additional transformations
with_transaction(
  get_ducklake_table("vehicles_clean") |>
    mutate(gear = as.integer(gear)) |>
    replace_table("vehicles_clean"),
  author = "Data Engineer",
  commit_message = "Add gear type conversion to silver layer"
)

# View the analysis dataset
get_ducklake_table("vehicles_analysis") |>
  select(mpg, cyl, efficiency) |>
  head(3)
#> # A query:  ?? x 3
#> # Database: DuckDB 1.5.1 [tgerke@Darwin 25.5.0:R 4.5.2//private/var/folders/b7/664jmq55319dcb7y4jdb39zr0000gq/T/RtmpwGZIaL/ducklake/ducklake58b075fac717.duckdb]
#>     mpg cyl   efficiency
#>   <dbl> <chr> <chr>     
#> 1  21   6.0   Medium    
#> 2  21   6.0   Medium    
#> 3  22.8 4.0   Medium

# View complete audit trail across all layers with author and commit messages
list_table_snapshots()
#>   snapshot_id       snapshot_time schema_version
#> 1           0 2026-08-25 21:02:58              0
#> 2           1 2026-08-25 21:02:58              1
#> 3           2 2026-08-25 21:02:58              2
#> 4           3 2026-08-25 21:02:58              3
#> 5           4 2026-08-25 21:02:58              4
#>                                                                           changes
#> 1                                                           schemas_created, main
#> 2                      tables_created, tables_inserted_into, main.vehicles_raw, 1
#> 3                    tables_created, tables_inserted_into, main.vehicles_clean, 2
#> 4                 tables_created, tables_inserted_into, main.vehicles_analysis, 3
#> 5 tables_created, tables_dropped, tables_inserted_into, main.vehicles_clean, 2, 4
#>          author                                           commit_message
#> 1          <NA>                                                     <NA>
#> 2 Data Engineer                         Initial load of raw vehicle data
#> 3 Data Engineer                       Clean and standardize vehicle data
#> 4  Data Analyst Create analysis-ready dataset with efficiency categories
#> 5 Data Engineer                 Add gear type conversion to silver layer
#>   commit_extra_info
#> 1              <NA>
#> 2              <NA>
#> 3              <NA>
#> 4              <NA>
#> 5              <NA>

# Time travel: Query the silver layer as it existed at snapshot 2 (before updates)
get_ducklake_table_version("vehicles_clean", version = 2) |>
  select(mpg, cyl, gear) |>
  head(3)
#> # A query:  ?? x 3
#> # Database: DuckDB 1.5.1 [tgerke@Darwin 25.5.0:R 4.5.2//private/var/folders/b7/664jmq55319dcb7y4jdb39zr0000gq/T/RtmpwGZIaL/ducklake/ducklake58b075fac717.duckdb]
#>     mpg cyl    gear
#>   <dbl> <chr> <dbl>
#> 1  21   6.0       4
#> 2  21   6.0       4
#> 3  22.8 4.0       4

# Clean up
detach_ducklake("my_data_lake")

Medallion architecture

ducklake implements a layered data architecture (medallion pattern) that ensures data quality and traceability:

Bronze layer (raw): Data exactly as received from source systems—preserves original data for audit trails
Silver layer (cleaned): Standardized, cleaned data with transformations and validations—the trusted source for analysis
Gold layer (analytics): Business-logic datasets optimized for specific analyses, dashboards, or reports

Each layer is automatically versioned, providing complete data lineage from raw source through to analysis-ready datasets. This approach enables:

Complete audit trail: Original data preserved alongside all transformations
Reprocessability: Reprocess from bronze if cleaning logic changes without re-extracting from source
Data lineage: Clear progression from raw → cleaned → analysis-ready
Validation: Compare layers to verify transformations
Quality assurance: Separate concerns between ingestion, cleaning, and analysis

Column-level lineage with dplyneage

ducklake tracks lineage at the table level: which tables changed at each snapshot, and why. For lineage within a query — which source columns feed each output column — the companion package dplyneage picks up where ducklake leaves off. Lake tables are ordinary dbplyr lazy tables, so any query pipes straight into an interactive diagram:

library(dplyneage)

get_ducklake_table("orders") |>
  dplyr::left_join(get_ducklake_table("customers"), by = "customer_id") |>
  dplyr::group_by(region) |>
  dplyr::summarise(total_sales = sum(amount, na.rm = TRUE)) |>
  extract_lineage() |>
  lineage_flow()

dplyneage’s ducklake lineage vignette walks through a full example, including per-layer diagrams for medallion pipelines and lineage for time-travel queries.

Learn more

Check out the pkgdown site for detailed vignettes:

Getting Started - Quick recipes for common operations
Clinical Trial Data Lake - Complete workflow from SDTM to ADaM with regulatory artifacts
Modifying Tables - Choosing how to change a table: joins vs. rows_*, upserts, merge_into(), and replace_table()
Data Inlining - Streaming-friendly small writes
Transactions - ACID transaction support
Time Travel - Query and restore historical data
Storage and Backups - Back up and recover your lake
Visualizing Your Lake - Plot snapshot history, change volume, and storage layout
Quack Remote Access - Share a DuckLake over the network with the Quack protocol

Key features

Versioned data lake: Every data change automatically tracked with timestamps and metadata
Multi-backend catalogs: Use DuckDB (default), PostgreSQL, SQLite, or MySQL as the catalog database — enables concurrent multi-client access with PostgreSQL or SQLite (DuckLake 1.0 spec)
Remote access over Quack: Serve a DuckLake to other R sessions over the network and let several people read and write it at once, using DuckDB’s Quack protocol (requires DuckDB 1.5.3 or newer)
Lightweight snapshots: Create unlimited snapshots without frequent compacting steps
Medallion architecture: Bronze/silver/gold layers for data lineage and quality
ACID transactions: Atomic updates with concurrent access and transactional guarantees over multi-table operations
Time travel: Query data exactly as it existed at any point in time—essential for reproducibility. Pin a whole session to a snapshot with attach_ducklake(snapshot_version = ...)
Performance-oriented: Uses Parquet columnar storage with statistics for filter pushdown, enabling fast queries on large datasets. Partitioning (set_table_partitioning()) and sorted tables (set_table_sorting()) prune files on large tables
Migrate Parquet in place: add_data_files() registers existing Parquet files with the lake without copying or rewriting them
Cloud storage: Keep data files on S3, GCS, R2, or Azure — create_storage_secret() handles credentials
Tunable: set_ducklake_option() adjusts DuckLake’s persisted settings (compression, file sizes, commit-message policy) at lake, schema, or table scope
Schema evolution in place: add_table_column(), drop_table_column(), rename_table_column(), set_column_type(), and rename_ducklake_table() change a table’s shape as metadata-only operations — no data rewrite, and every earlier schema stays reachable through time travel
Variable labels survive the lake: create_table() stores haven/labelled column labels as catalog comments and collect() restores them, so gtsummary and gt keep displaying them; set_table_comment(), set_column_comments(), and get_table_comments() manage documentation any client of the lake can read
Views: create_view() stores a dplyr pipeline as a SQL view in the lake — shared logic that always reads current data; list_ducklake_tables() shows what’s there
Tidyverse interface: Familiar dplyr syntax for data manipulation
Encryption: Opt-in Parquet encryption with attach_ducklake(encrypted = TRUE)
A write style for every job: rows_insert(), rows_update(), rows_delete(), and rows_upsert() for incremental changes; merge_into() for conditional merges and staging-table syncs; replace_table() pipelines for bulk rewrites — all fully versioned
Complete audit trails: Who changed what, when, and why—suitable for regulated industries
Seamless integration: Works with duckdb, DBI, dbplyr, and the broader tidyverse ecosystem