Modifying Tables with Version Control

This vignette demonstrates how to modify tables in a DuckLake while maintaining complete version control and audit trails. This is essential for reproducible workflows.

library(ducklake)
library(dplyr)

# Setup for examples
attach_ducklake("modifying_tables_lake", lake_path = vignette_temp_dir)

# Load a sample dataset
with_transaction(
  create_table(mtcars, "cars"),
  author = "Data Engineer",
  commit_message = "Initial car data load"
)

A note before we start: when you load dplyr you may see a message that it masks ducklake’s rows_insert(), rows_update(), and rows_delete(). That is harmless. Tables returned by get_ducklake_table() carry a class that dispatches to ducklake’s DuckLake-aware methods regardless of the order in which the packages were loaded.

Choosing a Modification Approach

Good news first: every committed change to a DuckLake table creates a snapshot. Whether you use rows_insert(), replace_table(), or raw SQL, DuckLake records what changed and you can time-travel back to any earlier state. (Earlier versions of this vignette said the rows_* functions skip versioning. That is not true in DuckLake v1.0.) The choice between the styles is about what kind of change you are making, not about whether it is audited.

One distinction matters before anything else: dplyr joins read. left_join() and friends combine tables into a new result and never touch the stored data. Everything else in this table writes.

You want to…	Reach for
Look up or combine data for analysis	dplyr joins (`left_join()`, …)
Append, correct, or remove specific rows	`rows_insert()`, `rows_update()`, `rows_delete()`
Update rows that exist, insert the ones that don’t	`rows_upsert()`
Conditional updates, or deletes driven by a staging table	`merge_into()`
Change the schema, not the data	`add_table_column()` and the schema evolution family
Bulk transformations that touch most rows	`replace_table()`

For incremental changes: the `rows_*` functions

Use rows_insert(), rows_update(), and rows_delete() when you are appending records, correcting specific values, or removing specific rows:

# Each of these is one SQL statement and one new snapshot
rows_insert(get_ducklake_table("my_table"), new_data, by = "id")
rows_update(get_ducklake_table("my_table"), corrections, by = "id")
rows_delete(get_ducklake_table("my_table"), obsolete_ids, by = "id")

Why they shine for incremental work:

Efficient - the change runs inside DuckDB as a single statement; the rest of the table is never read into R or rewritten
Streaming-friendly - small changes benefit from DuckLake’s data inlining, landing in the catalog instead of spawning tiny Parquet files
Still versioned - each call produces a snapshot you can time-travel to

For update-or-insert: `rows_upsert()`

When a batch mixes corrections to existing rows with rows you have never seen, rows_upsert() handles both in one atomic statement: rows whose key matches are updated, the rest are inserted.

# One statement, one snapshot: id 2 is updated, id 7 is inserted
rows_upsert(get_ducklake_table("my_table"), mixed_batch, by = "id")

DuckLake tables have no primary keys, so under the hood this is SQL MERGE INTO matching on your by columns, not the ON CONFLICT upsert you may know from other databases. The practical upshot is the same, with one wrinkle: when the upsert data covers only some of the table’s columns, inserted rows get the column default (usually NULL) in the columns you didn’t supply, while updated rows keep their existing values there.

For staging syncs and conditional merges: `merge_into()`

rows_upsert() covers update-or-insert. merge_into() exposes the rest of the SQL MERGE statement for the cases beyond it:

# Update only when the source is newer
merge_into(
  get_ducklake_table("my_table"), fresh_data, by = "id",
  matched_condition = "source.updated_at > target.updated_at"
)

# Synchronize to a staging table: upsert, plus delete rows
# that no longer exist in the source
merge_into("my_table", staging, by = "id", delete_missing = TRUE)

Despite the name, merge_into() is unrelated to base::merge() and dplyr’s joins, which combine tables into a new result. merge_into() changes the target table in place.

You could get a similar end state by joining the staging table to the current table and calling replace_table() on the result. Resist that instinct: it rewrites every row of the table, and the change feed then records a wholesale replacement instead of the handful of inserts, updates, and deletes that actually happened. merge_into() touches only the affected rows, so get_table_changes() afterward tells the true story.

For schema changes: evolve in place

Adding, dropping, renaming, or widening columns needs no data rewrite at all. DuckLake records schema changes as metadata, so the schema evolution functions run instantly regardless of table size, and time travel still shows every earlier shape of the table:

add_table_column("my_table", "category", "VARCHAR", default = "unknown")
rename_table_column("my_table", from = "cat", to = "category")
set_column_type("my_table", "id", "BIGINT")   # widening only
drop_table_column("my_table", "scratch")
rename_ducklake_table("my_table", "my_better_named_table")

Two behaviors to know. A default applies to existing rows as well as future inserts, so the new column shows up filled everywhere. And type changes must widen (INTEGER to BIGINT, FLOAT to DOUBLE): DuckLake refuses conversions that could lose values, and the error from set_column_type() walks you through the add-copy-drop-rename route when you need to narrow.

For structural or bulk changes: `replace_table()`

Use replace_table() when a transformation recomputes, reshapes, or filters most of the table anyway:

with_transaction(
  get_ducklake_table("my_table") |>
    filter(status == "active") |>
    mutate(processed = TRUE) |>
    replace_table("my_table"),
  author = "Your Name",
  commit_message = "Mark active records as processed"
)

replace_table() rewrites the whole table inside DuckDB (no rows pass through R): the right tool for a bulk rewrite, wasteful for touching three rows in a million-row table, and unnecessary for pure schema changes now that the in-place functions above exist.

Group related changes with `with_transaction()`

Whichever style you use, wrap related modifications in with_transaction(). All changes inside the transaction become one snapshot, and you can attach an author and commit message for the audit trail, valuable in any setting and essential for GxP/21 CFR Part 11 work:

with_transaction({
  rows_insert(get_ducklake_table("my_table"), march_batch, by = "id")
  rows_delete(get_ducklake_table("my_table"), recalled_units, by = "id")
},
  author = "Data Team",
  commit_message = "March intake; remove recalled units"
)

Examples

Incremental changes with the `rows_*` functions

Let’s see the row-level functions in action on a small fleet table with a proper key column:

fleet <- data.frame(
  car_id = 1:3,
  model = c("Corolla", "Civic", "Model 3"),
  mileage = c(42000, 38500, 12000)
)

with_transaction(
  create_table(fleet, "fleet"),
  author = "Fleet Manager",
  commit_message = "Initial fleet inventory"
)

Insert new records by key. The new rows are appended in a single SQL statement, and the existing rows are never read into R:

new_cars <- data.frame(
  car_id = 4:5,
  model = c("Leaf", "Ioniq 5"),
  mileage = c(500, 120)
)

rows_insert(get_ducklake_table("fleet"), new_cars, by = "car_id")

get_ducklake_table("fleet") |> collect()

Update specific values by key. Only the matched rows change:

correction <- data.frame(car_id = 2, mileage = 39000)

rows_update(get_ducklake_table("fleet"), correction, by = "car_id")

get_ducklake_table("fleet") |> filter(car_id == 2) |> collect()

Delete rows by key:

sold <- data.frame(car_id = 1)

rows_delete(get_ducklake_table("fleet"), sold, by = "car_id")

get_ducklake_table("fleet") |> collect()

Each call above created its own snapshot. To record an author and commit message, or to make several row operations land as one snapshot, wrap them in with_transaction():

april_arrivals <- data.frame(car_id = 6, model = "ID.4", mileage = 60)
recalled <- data.frame(car_id = 4)

with_transaction({
  rows_insert(get_ducklake_table("fleet"), april_arrivals, by = "car_id")
  rows_delete(get_ducklake_table("fleet"), recalled, by = "car_id")
},
  author = "Fleet Manager",
  commit_message = "April intake; remove recalled Leaf"
)

# The full history: every change is versioned, wrapped or not
list_table_snapshots("fleet")

Upsert a batch that mixes corrections and new arrivals. The Model 3’s mileage is updated and the Kona is inserted, in one statement:

service_batch <- data.frame(
  car_id = c(3, 7),
  model = c("Model 3", "Kona"),
  mileage = c(15200, 8000)
)

rows_upsert(get_ducklake_table("fleet"), service_batch, by = "car_id")

get_ducklake_table("fleet") |> arrange(car_id) |> collect()

Synchronize to an authoritative source with merge_into(). Suppose the quarterly registry export is the truth: matching cars take its values, cars it doesn’t list are gone, and cars we haven’t seen are added. One call, one snapshot:

registry <- data.frame(
  car_id = c(3, 5, 8),
  model = c("Model 3", "Ioniq 5", "e-Golf"),
  mileage = c(15400, 900, 21000)
)

with_transaction(
  merge_into("fleet", registry, by = "car_id", delete_missing = TRUE),
  author = "Fleet Manager",
  commit_message = "Quarterly registry sync"
)

get_ducklake_table("fleet") |> arrange(car_id) |> collect()

Because the sync ran as targeted row changes rather than a table rewrite, the change feed records exactly what happened:

latest <- max(list_table_snapshots("fleet")$snapshot_id)
get_table_changes("fleet", latest, latest) |>
  select(change_type, car_id, model, mileage) |>
  collect()

Updating specific rows with `replace_table()`

# Update mpg values for specific cars (4-cylinder cars get a 5% efficiency boost)
with_transaction(
  get_ducklake_table("cars") |>
    mutate(
      mpg = if_else(cyl == 4, mpg * 1.05, mpg)
    ) |>
    replace_table("cars"),
  author = "Data Engineer",
  commit_message = "Update MPG for 4-cylinder vehicles"
)

# Check version history - should show the new snapshot
list_table_snapshots("cars")

Adding derived columns

Derived columns no longer need replace_table(). Declare the column with add_table_column() (instant, metadata-only), then fill it with a mutate() pipeline through ducklake_exec(), which runs as an in-database UPDATE. Nothing is collected into R:

with_transaction({
  add_table_column("cars", "hp_per_cyl", "DOUBLE")
  add_table_column("cars", "high_performance", "VARCHAR")

  get_ducklake_table("cars") |>
    mutate(
      hp_per_cyl = hp / cyl,
      high_performance = if_else(hp > 200, "Y", "N")
    ) |>
    ducklake_exec()
},
  author = "Data Engineer",
  commit_message = "Add HP per cylinder and performance flag"
)

# Verify new columns exist
get_ducklake_table("cars") |>
  filter(hp > 200) |>
  select(hp, cyl, hp_per_cyl, high_performance)

To see the statement a pipeline would run before committing to it, end the pipeline with show_ducklake_query() instead of ducklake_exec():

get_ducklake_table("cars") |>
  mutate(hp_per_cyl = round(hp_per_cyl, 1)) |>
  show_ducklake_query()

Reshaping the schema in place

The rest of the schema evolution family works the same way. Widen a type, rename a column, or drop one. Each change is instant, and earlier snapshots keep the earlier shape:

snapshot_before <- max(list_table_snapshots("cars")$snapshot_id)

rename_table_column("cars", from = "high_performance", to = "high_perf_flag")
drop_table_column("cars", "hp_per_cyl")

# Widen fleet's integer key without rewriting any data
set_column_type("fleet", "car_id", "BIGINT")

# Current schema reflects the rename and the drop
get_ducklake_table("cars") |> colnames()

# The pre-change snapshot still shows the old shape
get_ducklake_table_version("cars", snapshot_before) |> colnames()

Filtering rows with `replace_table()`

# Keep only specific rows - creates a versioned snapshot
with_transaction(
  get_ducklake_table("cars") |>
    filter(cyl == 8) |>
    replace_table("cars"),
  author = "Data Engineer",
  commit_message = "Filter to V8 engines only"
)

# Show the filtered table
get_ducklake_table("cars")

# View version history - old versions still accessible via time travel
list_table_snapshots("cars")

Time Travel: Accessing Previous Versions

# Get the current version
current <- get_ducklake_table("cars") |> collect()

# List all snapshots to see available versions
snapshots <- list_table_snapshots("cars")
snapshots

# Access a specific previous version by snapshot_id
original_version <- get_ducklake_table_version(
  "cars", 
  snapshots$snapshot_id[1]
) |> collect()

# Compare: how many rows changed?
nrow(current)
nrow(original_version)

- Choosing a Modification Approach
- Examples

Modifying Tables with Version Control

Choosing a Modification Approach

For incremental changes: the rows_* functions

For update-or-insert: rows_upsert()

For staging syncs and conditional merges: merge_into()