---
title: "Modifying Tables with Version Control"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Modifying Tables with Version Control}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, include = FALSE}
# Evaluate chunks only where the ducklake DuckDB extension is already
# installed. The probe never downloads anything, so building this vignette
# needs no network access.
ducklake_available <- ducklake::ducklake_extension_available()
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  eval = ducklake_available
)
# Use a unique temp directory for this vignette to avoid conflicts during R CMD check
vignette_temp_dir <- file.path(tempdir(), "modifying_tables_vignette")
dir.create(vignette_temp_dir, showWarnings = FALSE, recursive = TRUE)
knitr::opts_knit$set(root.dir = vignette_temp_dir)
```

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

```{r setup, message = FALSE}
library(ducklake)
library(dplyr)

# Setup for examples
# The ducklake extension only needs installing once per machine:
# install_ducklake()
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:

```r
# 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](data-inlining.html), 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.

```r
# 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:

```r
# 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:

```r
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:

```r
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()` collects the transformed data into R and rewrites the
table -- 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:

```r
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:

```{r rows-setup}
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 -- the existing rows are never read into R:

```{r rows-insert}
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:

```{r rows-update}
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:

```{r rows-delete}
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()`:

```{r rows-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:

```{r rows-upsert}
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:

```{r merge-into}
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:

```{r merge-into-changes}
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()`

```{r update-rows}
# 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:

```{r add-columns}
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)
```

### Reshaping the schema in place

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

```{r schema-evolution}
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()`

```{r filter}
# 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

```{r time-travel}
# 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)
```

```{r cleanup, include=FALSE}
detach_ducklake("modifying_tables_lake")
```