
Replace a table with modified data and create a new snapshot
Source:R/replace_table.R
replace_table.RdReplace a table with modified data and create a new snapshot
Details
This function is designed for bulk transformations that should create a
new versioned snapshot. A dplyr pipeline on the package's connection
runs inside DuckDB: its result is materialized in DuckDB's temporary
storage (the query may read the table being replaced), the table is
dropped and recreated from it, and the metadata DuckLake keeps against
the table is put back. No rows pass through R. A data frame, or a lazy
table on another connection, is loaded the way create_table() loads
it.
The drop and create run atomically: when no transaction is open,
replace_table() wraps them in one of its own, so a failed create never
leaves the table dropped. Wrap the call in with_transaction() (or
begin_transaction()/commit_transaction()) when you want to record an
author and commit message on the snapshot, or to group the replacement
with other changes.
DuckLake gives the replacement a new table id, as it does for any
DROP + CREATE, and it stores comments, partition keys, sort order,
and table-scoped options against that id. replace_table() carries them
over inside the same transaction: the table comment, column comments
(and so variable labels) for columns that still exist, partition and
sort keys whose columns still exist, and options set with
set_ducklake_option() at table scope, all put on the empty table
before the rows are written, so the rewrite itself lands partitioned,
sorted, and governed by the options, as one snapshot. Earlier snapshots
keep the earlier id and stay reachable by name through time travel.
What the open transaction has done to the table counts. Comments set
earlier in it are carried over, and so are keys set or reset there with
set_table_partitioning(), set_table_sorting(), and their reset_
counterparts. DuckLake shows pending keys nowhere, so ducklake notes
them as those functions run: keys changed in the same transaction with a
raw ALTER TABLE statement are not seen, and the rewrite then carries
the committed keys over instead.
When to use replace_table():
Bulk transformations - a dplyr pipeline that recomputes, reshapes, or filters most of the table
When to reach elsewhere:
Schema-only changes -
add_table_column(),drop_table_column(),rename_table_column(), andset_column_type()alter the table in place; nothing is collected or rewrittenDerived columns -
add_table_column()followed by amutate()pipeline throughducklake_exec()fills the new column with an in-database UPDATETargeted row changes -
rows_update(),rows_upsert(), orducklake_exec()modify only the affected rows
Both paths create a snapshot: replace_table() via DROP + CREATE, and ducklake_exec() via the in-place UPDATE/DELETE/INSERT it runs, so either way the change is available for time travel.
Examples
lake_dir <- tempfile("replace_lake_")
dir.create(lake_dir)
attach_ducklake("replace_lake", lake_path = lake_dir)
create_table(mtcars, "cars")
# Add new derived columns (atomic on its own; creates a new snapshot)
get_ducklake_table("cars") |>
dplyr::mutate(
thirsty = dplyr::if_else(mpg < 20, "Y", "N"),
mpg_band = dplyr::case_when(
mpg < 15 ~ "<15",
mpg < 25 ~ "15-24",
TRUE ~ ">=25"
)
) |>
replace_table("cars")
# Wrap in with_transaction() to record audit metadata on the snapshot
with_transaction(
get_ducklake_table("cars") |>
dplyr::select(-thirsty, -mpg_band) |>
replace_table("cars"),
author = "Data Engineer",
commit_message = "Drop derived columns"
)
#> Committed snapshot 3 (Data Engineer): Drop derived columns
# Partition keys, sort order, comments, and table options survive the rewrite
set_table_partitioning("cars", "cyl")
#> Table "cars" is now partitioned by "cyl".
#> ℹ Only newly written data is partitioned; existing files keep their layout.
get_ducklake_table("cars") |>
dplyr::filter(mpg > 15) |>
replace_table("cars")
get_table_partitions("cars")
#> schema_name table_name partition_key_index column_name transform
#> 1 main cars 0 cyl identity
detach_ducklake("replace_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)