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.
Many industries rely on flat-file workflows (CSV, XPT, Excel, etc.) that create significant data management challenges:
DuckLake solves these problems by implementing a versioned data lake architecture that:
install.packages("ducklake")
pak::pak("tgerke/ducklake-r")
ducklake requires the duckdb R package version 2.0.0 or newer, the release that ships DuckDB 2.0 and DuckLake 1.1. Lakes made with earlier versions open as they are. One caution for a team that shares a lake whose catalog is a DuckDB file: the first write from duckdb 2.0 puts that file in a storage format duckdb 1.5 cannot open, so upgrade every client of such a lake together. The Upgrading section of Choosing a Deployment covers that, and the separate, opt-in migration of a lake's catalog format.
DuckLake itself ships as a DuckDB extension. attach_ducklake()
downloads it the first time it is needed and says where it put it, so
there is no install step. In an interactive session, duckdb offers to
create ~/.duckdb the first time it connects; say yes and the download
happens once per machine rather than once per session. Scripts and CI
jobs get the same by setting DUCKDB_R_HOME in ~/.Renviron or the
job’s environment. install_ducklake() fetches the extension ahead of
time, for container images and machines that are offline when the lake
is attached, and ducklake_extension_available() reports whether it is
already there.
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().
The medallion pattern organizes a lake in layers: bronze holds data exactly as it arrived, silver holds the cleaned and standardized version, and gold holds the tables an analysis reads. This session builds one table per layer and follows the same steps as the Getting Started article: attach a lake, add tables, read them, change one, look at the history, detach.
library(ducklake)
library(dplyr)
attach_ducklake("my_data_lake", lake_path = tempdir(), author = "Data Engineer")
One call opens a lake, and creates it first when nothing is at the path yet. By default the catalog is a DuckDB file stored next to the data. A lake that several people write to keeps its catalog in SQLite or PostgreSQL instead, and Choosing a Deployment walks through that choice.
Each layer gets a schema of its own. That keeps bronze.vehicles,
silver.vehicles, and gold.vehicle_efficiency one lineage under three
roofs, and lets access to raw data be restricted at the schema level.
with_transaction({
create_schema("bronze")
create_schema("silver")
create_schema("gold")
}, author = "Data Engineer", commit_message = "Create medallion layers")
#> Created schema "bronze".
#> Created schema "silver".
#> Created schema "gold".
#> Committed snapshot 1 (Data Engineer): Create medallion layers
with_transaction(
create_table(mtcars, "bronze.vehicles"),
author = "Data Engineer",
commit_message = "Initial load of raw vehicle data"
)
#> Committed snapshot 2 (Data Engineer): Initial load of raw vehicle data
Every write to a lake is a commit. with_transaction() makes the commit
explicit: everything inside it lands as one snapshot, with a record of
who made the change and why, and it all rolls back if any step fails.
The confirmation names the snapshot, and the history section below uses
that number to look back.
A dplyr pipeline on a lake table is a query. Ending it with
create_table() runs the query inside DuckDB and writes the result
straight into the lake, so the rows never pass through R. Each pipeline
is kept in a variable here, because the recipe that builds a layer is
also its lineage, which the last section draws.
silver <- get_ducklake_table("bronze.vehicles") |>
select(mpg, cyl, hp, wt, gear) |>
mutate(cyl = as.integer(cyl))
with_transaction(
create_table(silver, "silver.vehicles"),
author = "Data Engineer",
commit_message = "Clean and standardize vehicle data"
)
#> Committed snapshot 3 (Data Engineer): Clean and standardize vehicle data
gold <- get_ducklake_table("silver.vehicles") |>
mutate(efficient = mpg > 25)
with_transaction(
create_table(gold, "gold.vehicle_efficiency"),
author = "Data Analyst",
commit_message = "Flag efficient vehicles"
)
#> Committed snapshot 4 (Data Analyst): Flag efficient vehicles
list_ducklake_tables()
#> schema_name table_name type
#> 1 bronze vehicles table
#> 2 gold vehicle_efficiency table
#> 3 silver vehicles table
get_ducklake_table() returns a lazy table. The dplyr verbs you pipe
onto it become SQL that DuckDB runs against the lake, and collect()
brings the result into R. Filter, select, and summarize before you
collect, so DuckDB does the work and only the result reaches R.
get_ducklake_table("gold.vehicle_efficiency") |>
count(cyl, efficient) |>
arrange(cyl, efficient) |>
collect()
#> # A tibble: 4 × 3
#> cyl efficient n
#> <int> <lgl> <dbl>
#> 1 4 FALSE 5
#> 2 4 TRUE 6
#> 3 6 FALSE 7
#> 4 8 FALSE 14
When the cleaning logic changes, a layer is rewritten from a pipeline
rather than re-extracted from the source system. replace_table() does
that as one versioned change, and the version before the rewrite stays
readable.
with_transaction(
get_ducklake_table("silver.vehicles") |>
mutate(gear = as.integer(gear)) |>
replace_table("silver.vehicles"),
author = "Data Engineer",
commit_message = "Add gear type conversion to silver layer"
)
#> Committed snapshot 5 (Data Engineer): Add gear type conversion to silver layer
Modifying
Tables
covers the other ways to change a table: rows_insert(),
rows_update(), and rows_delete() for specific rows, rows_upsert()
and merge_into() for batches, and ducklake_exec() for changing rows
in place. All of them are versioned in the same way.
list_table_snapshots() lists every commit in the lake, across all
layers. The ids are the ones the confirmations printed, and snapshot 0
is the lake’s creation, recorded under the author attach_ducklake()
was given.
list_table_snapshots() |>
select(snapshot_id, author, commit_message)
#> snapshot_id author commit_message
#> 1 0 Data Engineer Create lake
#> 2 1 Data Engineer Create medallion layers
#> 3 2 Data Engineer Initial load of raw vehicle data
#> 4 3 Data Engineer Clean and standardize vehicle data
#> 5 4 Data Analyst Flag efficient vehicles
#> 6 5 Data Engineer Add gear type conversion to silver layer
Any earlier version of a table can be read as a lazy table. Here is the silver layer as it was at snapshot 3, before the rebuild:
get_ducklake_table_version("silver.vehicles", version = 3) |>
select(mpg, cyl, gear) |>
head(3) |>
collect()
#> # A tibble: 3 × 3
#> mpg cyl gear
#> <dbl> <int> <dbl>
#> 1 21 6 4
#> 2 21 6 4
#> 3 22.8 4 4
Time Travel covers reading a table as of a timestamp, comparing versions, restoring one, and pinning a whole session to a snapshot.
detach_ducklake("my_data_lake")
Detaching deletes nothing. It releases the lock DuckDB holds on the catalog file, so another session or a backup can open the lake. Attaching the same path again picks up where you left off, history included.
ducklake records lineage at the table level: which tables each snapshot
touched, and why. The companion package
dplyneage traces lineage within a
query, from each output column back to the source columns it came from.
Lake tables are ordinary dbplyr lazy tables, so the pipelines that built
the silver and gold layers above are also their lineage recipes. Pass
them to extract_lineage() under the names they were materialized as,
and it stitches the layers into one graph:
library(dplyneage)
lake_lineage <- extract_lineage(list(
"silver.vehicles" = silver,
"gold.vehicle_efficiency" = gold
))
lineage_flow(lake_lineage, height = "450px")
bronze.vehicles feeds silver.vehicles, which feeds
gold.vehicle_efficiency. A column that passes through unchanged
connects with a plain edge. A column a pipeline computes carries its
expression as a label, with as.integer(cyl) on the way into silver and
mpg > 25 on the way into gold. In an R session the diagram is
interactive: drag tables, zoom, hover a column for its type and label,
click one to isolate everything upstream and downstream of it. The same
lineage answers impact questions as data:
lineage_upstream(lake_lineage, "gold.vehicle_efficiency.efficient")
#> [1] "bronze.vehicles.mpg" "silver.vehicles.mpg"
Lineage reads the structure of a pipeline, not its data, which is why
this works after the lake was detached. dplyneage’s ducklake lineage
article
draws one diagram per layer and extracts lineage from time-travel
queries. Its lineage that travels with the
data
article stores each layer’s lineage on the commit that wrote it, through
commit_extra_info, so a snapshot’s rows and their derivation come back
together.
Check out the pkgdown site for detailed vignettes:
rows_*, upserts,
merge_into(), and replace_table()
backend = "quack",
experimental)set_ducklake_retry() tunes how DuckLake retries transactions that
race with another writerattach_ducklake(snapshot_version = ...)
get_table_changes() returns every row
inserted, updated, or deleted between two snapshots as a lazy table,
and view_table_changes() opens that feed in an interactive viewer
with the changed cells highlighted. plot_snapshots(),
plot_table_changes(), and plot_table_files() chart a lake’s
history, change volume, and file layoutset_table_partitioning()) and sorted tables
(set_table_sorting()), which create_table() can also set before a
table’s first load, prune files on large tablesadd_data_files() registers existing
Parquet files with the lake without copying or rewriting themcreate_storage_secret() handling the credentialsset_ducklake_option() adjusts DuckLake’s persisted
settings (compression, file sizes, commit-message policy) at lake,
schema, or table scope, reset_ducklake_option() removes a table’s
override, and create_table(options = ) sets them before the first
loadcheckpoint_ducklake()
flushes inlined data, merges small files, rewrites heavily deleted
ones, and, when the lake has a retention policy, expires old snapshots
and deletes the files they released. Each step is also a function of
its own, and backup_ducklake() makes a timestamped copy of the data
files and of a file-based catalogadd_table_column(),
drop_table_column(), rename_table_column(), set_column_type(),
set_column_default(), and rename_ducklake_table() change a table’s
shape as metadata-only operations. No data is rewritten, and every
earlier schema stays reachable through time travelcreate_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
readcreate_view() stores a dplyr pipeline as a SQL view in
the lake, so shared logic always reads current data.
list_ducklake_tables() shows what’s therecreate_check() stores a validation
rule in the lake as a view that returns the rows breaking it, and
run_checks() counts the failing rows for every rule. The rules are
versioned with the data and run from any client of the lake. Inside
with_transaction() they see pending writes, so a load that fails a
check can be rolled back before it commitscreate_schema() organizes a lake into medallion layers
(bronze, silver, gold), studies, or any other grouping; schemas nest
("sales.europe.orders"), and every function accepts a dotted pathcreate_table() and replace_table() run
dplyr pipelines inside DuckDB and write the result straight into the
lake, so derived layers never pass through R memoryattach_ducklake(encrypted = TRUE). The Parquet keys are kept in the
catalog, and the meta_encryption_key argument encrypts a DuckDB
catalog file as wellrows_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 of them are
versionedoptions(ducklake.author = ...)
names the author once for a whole sessionget_ducklake_connection() hands you the
connection