---
title: "Getting Started with ducklake"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Getting Started with ducklake}
  %\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(), "ducklake_vignette")
dir.create(vignette_temp_dir, showWarnings = FALSE, recursive = TRUE)
knitr::opts_knit$set(root.dir = vignette_temp_dir)
```

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

## What a DuckLake is

A DuckLake is a data lake that keeps its own history. Two pieces make it
up: a catalog database that records every table, column, and change, and a
directory of Parquet files that holds the rows. Every committed change
becomes a snapshot, and any snapshot can be read again later, so a table's
past is part of the table. ducklake puts dplyr on top of this. You attach a
lake, add tables, and query them as lazy tables, and DuckDB does the work.

This article follows one short session: attach a lake, add a table, read
it back, change it, look at its history, and detach.
[Choosing a Deployment](deployment.html) describes what a lake looks like
on disk and where the catalog and the data files can live.

## Install

```{r install, eval = FALSE}
install.packages("ducklake")
```

ducklake needs the duckdb package at version 1.5.5 or newer. DuckLake
itself is a DuckDB extension, and the first `attach_ducklake()` downloads
it and says where it went. The first time duckdb connects in an interactive
session it offers to create `~/.duckdb`; say yes, and the download happens
once instead of once per session. For scripts and CI, set `DUCKDB_R_HOME`
in `~/.Renviron` or the job's environment.

## Attach a lake

One call opens a lake, and creates it first when nothing is at the path
yet:

```{r attach, message=FALSE}
attach_ducklake("my_lake", lake_path = vignette_temp_dir, author = "Data Engineer")
```

The same call with the same path opens the lake again in a later session.
When you know a lake already exists, add `create = FALSE`: a mistyped path
then stops with an error instead of creating a new, empty lake.

By default the catalog is a DuckDB database file stored next to the data,
which is the right setup for one person on one machine. A lake that several
people write to keeps its catalog in SQLite or PostgreSQL instead, and that
is a one-argument change to this call.
[Choosing a Deployment](deployment.html) walks through the options.

## Add a table

`create_table()` takes a data frame and writes it into the lake as a new
table. Wrapped in `with_transaction()`, the table is committed as one
snapshot with a record of who added it and why:

```{r add_table}
with_transaction(
  create_table(mtcars, "cars"),
  author = "Data Engineer",
  commit_message = "Add the Motor Trend car data"
)

list_ducklake_tables()
```

The confirmation names the snapshot the commit created. That number is the
table's version, and the history section below uses it to look back.

### Why most calls sit inside `with_transaction()`

Every change to a lake is a commit. Called on its own, `create_table()`
still commits, as one snapshot without an author or a message.
`with_transaction()` makes the commit explicit. It groups everything inside
it into a single snapshot, records who made the change and why, and rolls
everything back if any step fails, so a half-finished change never lands
in the lake.

The name follows the [withr](https://withr.r-lib.org/) convention: a
`with_*()` function runs the code you hand it inside a temporary state and
cleans up afterward. Here the state is an open transaction, and the cleanup
is a commit on success or a rollback on error.

The first argument is one R expression. A single call needs nothing more.
To commit several operations as one snapshot, wrap them in braces, exactly
as you would write the body of a function. The change below does that.

Three fields describe a commit. `author` and `commit_message` are the ones
to use every time. `commit_extra_info` holds free-form context, such as a
ticket number or a JSON string, and it appears alongside the other two in
the snapshot history. [Working with Transactions](transactions.html)
covers all three, manual transaction control, and what happens when
several sessions write at once.

## Read a table

`get_ducklake_table()` returns a lazy table. Nothing is read until you ask
for it, and the dplyr verbs you pipe onto it become SQL that DuckDB runs
against the lake:

```{r read}
cars_data <- get_ducklake_table("cars")

cars_data |>
  filter(cyl == 6) |>
  select(mpg, cyl, hp) |>
  head(3)
```

`show_query()` prints the SQL a pipeline will run. `collect()` runs it and
returns a tibble:

```{r collect}
cars_data |>
  filter(cyl == 6) |>
  select(mpg, cyl, hp) |>
  show_query()

df_six <- cars_data |>
  filter(cyl == 6) |>
  select(mpg, cyl, hp) |>
  collect()

df_six
```

Filter and select before you collect. DuckDB then reads only the rows and
columns you need, which matters once a table is larger than memory.

## Change a table

A dplyr pipeline on a lake table is a query. On its own, `mutate()`
computes a column in the result you would collect and changes nothing in
the lake. To change the stored rows, end the pipeline with something that
writes. `create_table()` in the section above is one such function, which
is why it needed nothing more. Which one to end with depends on what you
want:

| You want to                        | End the pipeline with |
|------------------------------------|-----------------------|
| Bring rows into R                  | `collect()`           |
| Make a new table from the result   | `create_table()`      |
| Change rows of this table in place | `ducklake_exec()`     |
| Rewrite this table from the result | `replace_table()`     |

`ducklake_exec()` turns `mutate()` into the new values and `filter()` into
which rows receive them. `with_transaction()` is a separate matter: it
decides how a write is recorded, not whether it happens.

Here two steps make one snapshot: a new column is declared, then filled in
place through `ducklake_exec()`. The braces commit both steps as a unit:

```{r change}
with_transaction({
  add_table_column("cars", "kpl", "DOUBLE")
  get_ducklake_table("cars") |>
    mutate(kpl = mpg * 0.425144) |>
    ducklake_exec()
},
  author = "Data Engineer",
  commit_message = "Add fuel efficiency in km/L"
)

get_ducklake_table("cars") |>
  select(mpg, kpl) |>
  head(3)
```

[Modifying Tables](modifying-tables.html) covers the other ways to change
a table: `rows_insert()`, `rows_update()`, and `rows_delete()` for specific
rows, `rows_upsert()` and `merge_into()` for batches, and `replace_table()`
for a bulk rewrite. All of them are versioned in the same way.

## See the history

`list_table_snapshots()` lists every snapshot that touched a table:

```{r history}
list_table_snapshots("cars")
```

Each row is one commit. `changes` summarizes what the commit did,
`author`, `commit_message`, and `commit_extra_info` are the fields passed
to `with_transaction()`, or to `attach_ducklake()` for snapshot 0, the
lake's creation, and `snapshot_id` is the number from the confirmation.
Any earlier version can be read as a lazy table:

```{r version}
# The cars table before the kpl column existed
get_ducklake_table_version("cars", version = 1) |>
  head(3)
```

A table can also be put back the way it was. The restore is a new
snapshot, so the versions after it stay readable:

```{r restore}
restore_table_version("cars", version = 1, author = "Data Engineer")

list_table_snapshots("cars")
```

[Time Travel](time-travel.html) covers reading a table as of a timestamp,
comparing versions, and pinning a whole session to one snapshot.

## Detach

```{r detach}
detach_ducklake("my_lake")
```

Detaching deletes nothing. It matters because DuckDB holds a lock on the
catalog file while the lake is attached, so another R session, a backup, or
a restore cannot open the lake until this one lets go.
`detach_ducklake("my_lake", shutdown = TRUE)` also closes the DuckDB
connection. Attaching the same path again picks up where you left off,
history included.

## Where to go next

- [Loading Data](loading-data.html): files, URLs, pipelines, schemas,
  Parquet files that are already on disk, and migrating from DuckDB or
  Iceberg.
- [Modifying Tables](modifying-tables.html): choosing between row
  operations, upserts, merges, and rewrites.
- [Views, Comments, and Labels](views-comments-labels.html): shared query
  logic and documentation that live in the lake.
- [Working with Transactions](transactions.html): commit metadata, manual
  control, and several writers at once.
- [Time Travel](time-travel.html): every way to read the past.
- [Choosing a Deployment](deployment.html): catalog backends, object
  storage, access, and day-one settings.
- [Storage and Backups](storage-and-backups.html): the files on disk,
  backups, and maintenance.
- [Clinical Trial Data Lake](clinical-trial-datalake.html): a complete
  regulated workflow.