The hardware and bandwidth for this mirror is donated by dogado GmbH, the Webhosting and Full Service-Cloud Provider. Check out our Wordpress Tutorial.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]dogado.de.

ducklake Cookbook

library(ducklake)
library(dplyr)

Introduction

This cookbook provides quick recipes for common ducklake operations. Each recipe is a self-contained example you can adapt for your workflow.

For a comprehensive real-world example, see the clinical trial data lake vignette.

Setup recipes

Create a new data lake

# Create a data lake in a specific directory
attach_ducklake("my_lake", lake_path = vignette_temp_dir)

Attach to an existing data lake

# Attach to an existing lake (creates it if it doesn't exist)
attach_ducklake("existing_lake", lake_path = "/path/to/data_lake")

Use an alternative catalog backend

# PostgreSQL catalog for multi-client access
attach_ducklake(
  "shared_lake",
  backend = "postgres",
  catalog_connection_string = "dbname=ducklake_catalog host=localhost",
  lake_path = "/shared/lake/data/"
)

# SQLite catalog for lightweight local multi-client setups
attach_ducklake(
  "team_lake",
  backend = "sqlite",
  catalog_connection_string = "metadata.sqlite",
  lake_path = "data_files/"
)

Detach from a data lake

# Detach when done (doesn't delete the lake)
detach_ducklake("my_lake")

Loading data recipes

Load data from a data.frame

with_transaction(
  create_table(mtcars, "cars"),
  author = "Data Engineer",
  commit_message = "Initial car data load"
)
#> Transaction started.
#> Transaction committed.

Update an existing table

# Create a second version of the cars table
with_transaction(
  get_ducklake_table("cars") |>
    mutate(kpl = mpg * 0.425144) |>  # Add km/L conversion
    replace_table("cars"),
  author = "Data Engineer",
  commit_message = "Add km/L metric to cars table"
)
#> Transaction started.
#> Transaction committed.

Load data from a CSV file

# First write a sample CSV (in practice, you'd have an existing file)
csv_path <- file.path(vignette_temp_dir, "sample_data.csv")
write.csv(head(iris, 20), csv_path, row.names = FALSE)

# Load the CSV into the data lake
with_transaction(
  create_table(csv_path, "iris_sample"),
  author = "Data Engineer",
  commit_message = "Load iris sample from CSV"
)
#> Transaction started.
#> Transaction committed.

Load data from a URL

# ducklake can load data directly from URLs
with_transaction(
  create_table("https://example.com/data.csv", "remote_data"),
  author = "Data Engineer",
  commit_message = "Load remote dataset"
)

Register existing Parquet files without copying

If your data is already in Parquet, add_data_files() records the files in the lake in place – no copy, no rewrite, and no collection into R. A vector of files is registered atomically in one snapshot. This is the fast migration path from a folder of Parquet extracts. The target table can already exist with a compatible schema, or create = TRUE can create it directly from the Parquet schema. Note that the lake takes ownership of the files: later compaction may rewrite or delete them.

add_data_files(
  "readings",
  c("extracts/jan.parquet", "extracts/feb.parquet"),
  create = TRUE
)

# See which files back a table
list_ducklake_files("readings")

Load with a dplyr pipeline

with_transaction(
  mtcars |>
    filter(mpg > 20) |>
    create_table("efficient_cars"),
  author = "Data Analyst",
  commit_message = "Load filtered car data"
)
#> Transaction started.
#> Transaction committed.

List all tables in the lake

# Every table and view, with its schema and type
list_ducklake_tables()
#>   schema_name     table_name  type
#> 1        main           cars table
#> 2        main efficient_cars table
#> 3        main    iris_sample table

Shared logic and documentation recipes

Store a pipeline as a view

A view stores a query, not data: reads always run against the current tables, and every client of the lake – R, Python, or plain SQL – sees the same definition.

get_ducklake_table("cars") |>
  filter(mpg > 25) |>
  create_view("v_efficient_cars")
#> Created view "v_efficient_cars".

get_ducklake_table("v_efficient_cars") |> collect()
#> # A tibble: 6 × 12
#>     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb   kpl
#>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1  32.4     4  78.7    66  4.08  2.2   19.5     1     1     4     1  13.8
#> 2  30.4     4  75.7    52  4.93  1.62  18.5     1     1     4     2  12.9
#> 3  33.9     4  71.1    65  4.22  1.84  19.9     1     1     4     1  14.4
#> 4  27.3     4  79      66  4.08  1.94  18.9     1     1     4     1  11.6
#> 5  26       4 120.     91  4.43  2.14  16.7     0     1     5     2  11.1
#> 6  30.4     4  95.1   113  3.77  1.51  16.9     1     1     5     2  12.9

Drop it when the logic is no longer needed:

drop_view("v_efficient_cars")
#> Dropped view "v_efficient_cars".

For logic a view cannot hold – a parameterized SQL macro, say – DuckDB SQL is the escape hatch: DBI::dbExecute(get_ducklake_connection(), "CREATE MACRO ...").

Document tables and columns

Comments live in the lake’s catalog, so the documentation travels with the data instead of in a sidecar file:

set_table_comment("cars", "Motor Trend road tests of 1973-74 models")
#> Commented table "cars".
set_column_comments(
  "cars",
  mpg = "Miles per US gallon",
  wt = "Weight (1000 lbs)"
)
#> Commented 2 columns on "cars".

get_table_comments("cars")
#>   object_type table_name column_name                                  comment
#> 1      column       cars         mpg                      Miles per US gallon
#> 2      column       cars          wt                        Weight (1000 lbs)
#> 3       table       cars        <NA> Motor Trend road tests of 1973-74 models

Keep variable labels through the lake

If your data carries haven/labelled-style variable labels, they survive the lake: create_table() stores label attributes as column comments, and collect() puts them back, so label-aware tools like gtsummary and gt behave as if the data never left R.

df_visits <- data.frame(subject = c("S1", "S2"), sbp = c(128, 141))
attr(df_visits$subject, "label") <- "Subject identifier"
attr(df_visits$sbp, "label") <- "Systolic blood pressure (mmHg)"

create_table(df_visits, "visits")
#> Stored 2 column labels as column comments.

collected <- get_ducklake_table("visits") |> collect()
attr(collected$sbp, "label")
#> [1] "Systolic blood pressure (mmHg)"

Reading data recipes

Read a table

# Returns a lazy dplyr tbl
cars_data <- get_ducklake_table("cars")

# Use dplyr verbs
cars_data |>
  filter(cyl == 6) |>
  select(mpg, cyl, hp) |>
  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/RtmpBfCpem/ducklake/ducklake8fa6461fba10.duckdb]
#>     mpg   cyl    hp
#>   <dbl> <dbl> <dbl>
#> 1  21       6   110
#> 2  21       6   110
#> 3  21.4     6   110

Collect data into memory

# Fetch all data into a data.frame
cars_df <- get_ducklake_table("cars") |> collect()
head(cars_df, 3)
#> # A tibble: 3 × 12
#>     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb   kpl
#>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1  21       6   160   110  3.9   2.62  16.5     0     1     4     4  8.93
#> 2  21       6   160   110  3.9   2.88  17.0     0     1     4     4  8.93
#> 3  22.8     4   108    93  3.85  2.32  18.6     1     1     4     1  9.69

View all versions of a table

# See all snapshots for the cars table
list_table_snapshots("cars")
#>   snapshot_id       snapshot_time schema_version
#> 1           1 2026-08-27 14:25:48              1
#> 2           2 2026-08-27 14:25:48              2
#> 3           7 2026-08-27 14:25:49              7
#> 4           8 2026-08-27 14:25:49              8
#>                                                                 changes
#> 1                    tables_created, tables_inserted_into, main.cars, 1
#> 2 tables_created, tables_dropped, tables_inserted_into, main.cars, 1, 2
#> 3                                                     tables_altered, 2
#> 4                                                     tables_altered, 2
#>          author                commit_message commit_extra_info
#> 1 Data Engineer         Initial car data load              <NA>
#> 2 Data Engineer Add km/L metric to cars table              <NA>
#> 3          <NA>                          <NA>              <NA>
#> 4          <NA>                          <NA>              <NA>

Read a specific version

# Query data as it existed at snapshot 1 -- before the kpl column was added
get_ducklake_table_version("cars", version = 1) |>
  select(mpg, cyl, hp) |>
  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/RtmpBfCpem/ducklake/ducklake8fa6461fba10.duckdb]
#>     mpg   cyl    hp
#>   <dbl> <dbl> <dbl>
#> 1  21       6   110
#> 2  21       6   110
#> 3  22.8     4    93

Read data at a specific timestamp

# Query data as of a specific time (see list_table_snapshots() for times)
get_ducklake_table_asof("cars", timestamp = "2024-01-15 10:30:00") |>
  collect()

Updating data recipes

Replace entire table

with_transaction(
  get_ducklake_table("cars") |>
    mutate(hp_per_cyl = hp / as.numeric(cyl)) |>  # Add derived metric
    replace_table("cars"),
  author = "Data Engineer",
  commit_message = "Add horsepower per cylinder metric"
)
#> Transaction started.
#> Stored 2 column labels as column comments.
#> Transaction committed.

Note: Use replace_table() for structural changes (adding or removing columns) and the row-level operations (rows_update(), rows_insert(), rows_delete()) for targeted, incremental changes. Both are fully versioned – every committed change creates a snapshot you can time-travel back to. See vignette("modifying-tables") for guidance on choosing between them.

Metadata and versioning recipes

View all snapshots

list_table_snapshots()
#>    snapshot_id       snapshot_time schema_version
#> 1            0 2026-08-27 14:25:47              0
#> 2            1 2026-08-27 14:25:48              1
#> 3            2 2026-08-27 14:25:48              2
#> 4            3 2026-08-27 14:25:48              3
#> 5            4 2026-08-27 14:25:48              4
#> 6            5 2026-08-27 14:25:48              5
#> 7            6 2026-08-27 14:25:49              6
#> 8            7 2026-08-27 14:25:49              7
#> 9            8 2026-08-27 14:25:49              8
#> 10           9 2026-08-27 14:25:49              9
#> 11          10 2026-08-27 14:25:50             10
#>                                                                                     changes
#> 1                                                                     schemas_created, main
#> 2                                        tables_created, tables_inserted_into, main.cars, 1
#> 3                     tables_created, tables_dropped, tables_inserted_into, main.cars, 1, 2
#> 4                                 tables_created, tables_inserted_into, main.iris_sample, 3
#> 5                              tables_created, tables_inserted_into, main.efficient_cars, 4
#> 6                                                      views_created, main.v_efficient_cars
#> 7                                                                          views_dropped, 5
#> 8                                                                         tables_altered, 2
#> 9                                                                         tables_altered, 2
#> 10                        tables_created, tables_altered, inlined_insert, main.visits, 6, 6
#> 11 tables_created, tables_dropped, tables_altered, tables_inserted_into, main.cars, 2, 7, 7
#>           author                     commit_message commit_extra_info
#> 1           <NA>                               <NA>              <NA>
#> 2  Data Engineer              Initial car data load              <NA>
#> 3  Data Engineer      Add km/L metric to cars table              <NA>
#> 4  Data Engineer          Load iris sample from CSV              <NA>
#> 5   Data Analyst             Load filtered car data              <NA>
#> 6           <NA>                               <NA>              <NA>
#> 7           <NA>                               <NA>              <NA>
#> 8           <NA>                               <NA>              <NA>
#> 9           <NA>                               <NA>              <NA>
#> 10          <NA>                               <NA>              <NA>
#> 11 Data Engineer Add horsepower per cylinder metric              <NA>

View snapshots for a specific table

list_table_snapshots("cars")

Restore a table to a previous version

# Roll cars back to snapshot 1. The restore is recorded as a new snapshot,
# so nothing is lost -- you can still time-travel to any version.
restore_table_version(
  "cars",
  version = 1,
  author = "Data Engineer"
)
#> Transaction started.
#> Transaction committed.
#> Table "cars" restored to snapshot 1 (recorded as a new snapshot).

list_table_snapshots("cars")
#>   snapshot_id       snapshot_time schema_version
#> 1           1 2026-08-27 14:25:48              1
#> 2           2 2026-08-27 14:25:48              2
#> 3           7 2026-08-27 14:25:49              7
#> 4           8 2026-08-27 14:25:49              8
#> 5          10 2026-08-27 14:25:50             10
#> 6          11 2026-08-27 14:25:50             11
#>                                                                                    changes
#> 1                                       tables_created, tables_inserted_into, main.cars, 1
#> 2                    tables_created, tables_dropped, tables_inserted_into, main.cars, 1, 2
#> 3                                                                        tables_altered, 2
#> 4                                                                        tables_altered, 2
#> 5 tables_created, tables_dropped, tables_altered, tables_inserted_into, main.cars, 2, 7, 7
#> 6                    tables_created, tables_dropped, tables_inserted_into, main.cars, 7, 8
#>          author                     commit_message commit_extra_info
#> 1 Data Engineer              Initial car data load              <NA>
#> 2 Data Engineer      Add km/L metric to cars table              <NA>
#> 3          <NA>                               <NA>              <NA>
#> 4          <NA>                               <NA>              <NA>
#> 5 Data Engineer Add horsepower per cylinder metric              <NA>
#> 6 Data Engineer        Restored cars to snapshot 1              <NA>

Transaction recipes

Simple transaction

with_transaction(
  create_table(my_data, "my_table"),
  author = "Your Name",
  commit_message = "What changed and why"
)

Multi-step transaction

with_transaction({
  # All these operations happen atomically
  create_table(raw_data, "raw_table")
  
  cleaned <- get_ducklake_table("raw_table") |>
    filter(!is.na(key_field)) |>
    create_table("clean_table")
  
  get_ducklake_table("clean_table") |>
    mutate(derived_field = calculate_something(x)) |>
    create_table("analysis_table")
},
author = "Data Engineer",
commit_message = "Full ETL pipeline run"
)

Manual transaction control

# For fine-grained control
begin_transaction()

create_table(data1, "table1")
create_table(data2, "table2")

# Commit or rollback
commit_transaction(
  author = "Your Name",
  commit_message = "Manual transaction commit"
)

# Or if something went wrong:
# rollback_transaction()

Query optimization recipes

Preview query without execution

To see the SQL a read pipeline will run, use dplyr’s show_query():

get_ducklake_table("cars") |>
  filter(mpg > 25) |>
  select(mpg, cyl, hp) |>
  show_query()
#> <SQL>
#> SELECT mpg, cyl, hp
#> FROM cars
#> WHERE (mpg > 25.0)

To preview the SQL an in-place modification would run (before committing to it with ducklake_exec()), use show_ducklake_query():

get_ducklake_table("cars") |>
  mutate(mpg = round(mpg)) |>
  show_ducklake_query()
#> 
#> === DuckLake SQL Preview ===
#> 
#> -- Main operation
#> UPDATE cars SET mpg = ROUND_EVEN(mpg, CAST(ROUND(0.0, 0) AS INTEGER)) ;

Filter early for performance

# Good: Filter before other operations
get_ducklake_table("cars") |>
  filter(cyl == 6) |>
  mutate(kpl = mpg * 0.425144) |>
  head(3)
#> # A query:  ?? x 12
#> # Database: DuckDB 1.5.1 [tgerke@Darwin 25.5.0:R 4.5.2//private/var/folders/b7/664jmq55319dcb7y4jdb39zr0000gq/T/RtmpBfCpem/ducklake/ducklake8fa6461fba10.duckdb]
#>     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb   kpl
#>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1  21       6   160   110  3.9   2.62  16.5     0     1     4     4  8.93
#> 2  21       6   160   110  3.9   2.88  17.0     0     1     4     4  8.93
#> 3  21.4     6   258   110  3.08  3.22  19.4     1     0     3     1  9.10

Use specific columns

# Good: Select only needed columns
get_ducklake_table("cars") |>
  select(mpg, cyl, hp) |>
  filter(mpg > 25)
#> # A query:  ?? x 3
#> # Database: DuckDB 1.5.1 [tgerke@Darwin 25.5.0:R 4.5.2//private/var/folders/b7/664jmq55319dcb7y4jdb39zr0000gq/T/RtmpBfCpem/ducklake/ducklake8fa6461fba10.duckdb]
#>     mpg   cyl    hp
#>   <dbl> <dbl> <dbl>
#> 1  32.4     4    66
#> 2  30.4     4    52
#> 3  33.9     4    65
#> 4  27.3     4    66
#> 5  26       4    91
#> 6  30.4     4   113

Sort or partition large tables for file pruning

For big tables, declaring a sort order or partition keys lets DuckLake skip whole Parquet files when a query filters on those columns:

# Sorting suits high-cardinality columns like timestamps or ids
set_table_sorting("events", "event_time")

# Partitioning suits low-cardinality columns like year or region
set_table_partitioning("sales", c("year(order_date)", "region"))

Tune lake options

set_ducklake_option() adjusts DuckLake’s persisted settings at lake, schema, or table scope, and get_ducklake_options() shows what’s set:

# Trade write speed for smaller files
set_ducklake_option("parquet_compression", "zstd")

# Require a commit message on every snapshot -- useful for audit discipline
set_ducklake_option("require_commit_message", TRUE)

get_ducklake_options()

Cleanup

# Detach from the lake
detach_ducklake("my_lake")

See also

These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.
Health stats visible at Monitor.