Skip to contents

Replace a table with modified data and create a new snapshot

Usage

replace_table(.data, table_name, .quiet = TRUE)

Arguments

.data

A dplyr query object (tbl_lazy) with transformations

table_name

Table name to replace

.quiet

Logical, whether to suppress messages (default TRUE)

Value

Invisibly returns NULL

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:

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)