---
title: "ducklake Cookbook"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{ducklake Cookbook}
  %\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)
```

## Introduction

This cookbook provides quick recipes for common ducklake operations. Each recipe is a self-contained example you can adapt for your workflow.

For a comprehensive real-world example, see the [clinical trial data lake](clinical-trial-datalake.html) vignette.

## Setup recipes

### Create a new data lake

```{r create, message=FALSE}
# Create a data lake in a specific directory
attach_ducklake("my_lake", lake_path = vignette_temp_dir)
```

### Attach to an existing data lake

```{r attach, eval=FALSE}
# Attach to an existing lake (creates it if it doesn't exist)
attach_ducklake("existing_lake", lake_path = "/path/to/data_lake")
```

### Use an alternative catalog backend

```{r attach-postgres, eval=FALSE}
# PostgreSQL catalog for multi-client access
attach_ducklake(
  "shared_lake",
  backend = "postgres",
  catalog_connection_string = "dbname=ducklake_catalog host=localhost",
  lake_path = "/shared/lake/data/"
)

# SQLite catalog for lightweight local multi-client setups
attach_ducklake(
  "team_lake",
  backend = "sqlite",
  catalog_connection_string = "metadata.sqlite",
  lake_path = "data_files/"
)
```

### Detach from a data lake

```{r detach, eval=FALSE}
# Detach when done (doesn't delete the lake)
detach_ducklake("my_lake")
```

## Loading data recipes

### Load data from a data.frame

```{r load_df}
with_transaction(
  create_table(mtcars, "cars"),
  author = "Data Engineer",
  commit_message = "Initial car data load"
)
```

### Update an existing table

```{r update_cars}
# Create a second version of the cars table
with_transaction(
  get_ducklake_table("cars") |>
    mutate(kpl = mpg * 0.425144) |>  # Add km/L conversion
    replace_table("cars"),
  author = "Data Engineer",
  commit_message = "Add km/L metric to cars table"
)
```

### Load data from a CSV file

```{r load_csv}
# First write a sample CSV (in practice, you'd have an existing file)
csv_path <- file.path(vignette_temp_dir, "sample_data.csv")
write.csv(head(iris, 20), csv_path, row.names = FALSE)

# Load the CSV into the data lake
with_transaction(
  create_table(csv_path, "iris_sample"),
  author = "Data Engineer",
  commit_message = "Load iris sample from CSV"
)
```

### Load data from a URL

```{r load_url, eval=FALSE}
# ducklake can load data directly from URLs
with_transaction(
  create_table("https://example.com/data.csv", "remote_data"),
  author = "Data Engineer",
  commit_message = "Load remote dataset"
)
```

### Register existing Parquet files without copying

If your data is already in Parquet, `add_data_files()` records the files in
the lake in place -- no copy, no rewrite, and no collection into R. A vector
of files is registered atomically in one snapshot. This is the fast migration
path from a folder of Parquet extracts. The target table can already exist
with a compatible schema, or `create = TRUE` can create it directly from the
Parquet schema. Note that the lake takes ownership of the files: later
compaction may rewrite or delete them.

```{r add_data_files, eval=FALSE}
add_data_files(
  "readings",
  c("extracts/jan.parquet", "extracts/feb.parquet"),
  create = TRUE
)

# See which files back a table
list_ducklake_files("readings")
```

### Load with a dplyr pipeline

```{r load_pipeline}
with_transaction(
  mtcars |>
    filter(mpg > 20) |>
    create_table("efficient_cars"),
  author = "Data Analyst",
  commit_message = "Load filtered car data"
)
```

### List all tables in the lake

```{r list_all_tables}
# Every table and view, with its schema and type
list_ducklake_tables()
```

## Shared logic and documentation recipes

### Store a pipeline as a view

A view stores a query, not data: reads always run against the current
tables, and every client of the lake -- R, Python, or plain SQL -- sees the
same definition.

```{r create_view}
get_ducklake_table("cars") |>
  filter(mpg > 25) |>
  create_view("v_efficient_cars")

get_ducklake_table("v_efficient_cars") |> collect()
```

Drop it when the logic is no longer needed:

```{r drop_view}
drop_view("v_efficient_cars")
```

For logic a view cannot hold -- a parameterized SQL macro, say -- DuckDB
SQL is the escape hatch:
`DBI::dbExecute(get_ducklake_connection(), "CREATE MACRO ...")`.

### Document tables and columns

Comments live in the lake's catalog, so the documentation travels with the
data instead of in a sidecar file:

```{r comments}
set_table_comment("cars", "Motor Trend road tests of 1973-74 models")
set_column_comments(
  "cars",
  mpg = "Miles per US gallon",
  wt = "Weight (1000 lbs)"
)

get_table_comments("cars")
```

### Keep variable labels through the lake

If your data carries haven/labelled-style variable labels, they survive
the lake: `create_table()` stores `label` attributes as column comments,
and `collect()` puts them back, so label-aware tools like gtsummary and gt
behave as if the data never left R.

```{r labels}
df_visits <- data.frame(subject = c("S1", "S2"), sbp = c(128, 141))
attr(df_visits$subject, "label") <- "Subject identifier"
attr(df_visits$sbp, "label") <- "Systolic blood pressure (mmHg)"

create_table(df_visits, "visits")

collected <- get_ducklake_table("visits") |> collect()
attr(collected$sbp, "label")
```

## Reading data recipes

### Read a table

```{r read_table}
# Returns a lazy dplyr tbl
cars_data <- get_ducklake_table("cars")

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

### Collect data into memory

```{r collect}
# Fetch all data into a data.frame
cars_df <- get_ducklake_table("cars") |> collect()
head(cars_df, 3)
```

### View all versions of a table

```{r view_versions}
# See all snapshots for the cars table
list_table_snapshots("cars")
```

### Read a specific version

```{r read_version}
# Query data as it existed at snapshot 1 -- before the kpl column was added
get_ducklake_table_version("cars", version = 1) |>
  select(mpg, cyl, hp) |>
  head(3)
```

### Read data at a specific timestamp

```{r read_timestamp, eval=FALSE}
# Query data as of a specific time (see list_table_snapshots() for times)
get_ducklake_table_asof("cars", timestamp = "2024-01-15 10:30:00") |>
  collect()
```

## Updating data recipes

### Replace entire table

```{r replace_table}
with_transaction(
  get_ducklake_table("cars") |>
    mutate(hp_per_cyl = hp / as.numeric(cyl)) |>  # Add derived metric
    replace_table("cars"),
  author = "Data Engineer",
  commit_message = "Add horsepower per cylinder metric"
)
```

Note: Use `replace_table()` for structural changes (adding or removing columns) and the row-level operations (`rows_update()`, `rows_insert()`, `rows_delete()`) for targeted, incremental changes. Both are fully versioned -- every committed change creates a snapshot you can time-travel back to. See `vignette("modifying-tables")` for guidance on choosing between them.

## Metadata and versioning recipes

### View all snapshots

```{r list_snapshots}
list_table_snapshots()
```

### View snapshots for a specific table

```{r list_table_snapshots, eval=FALSE}
list_table_snapshots("cars")
```

### Restore a table to a previous version

```{r restore}
# Roll cars back to snapshot 1. The restore is recorded as a new snapshot,
# so nothing is lost -- you can still time-travel to any version.
restore_table_version(
  "cars",
  version = 1,
  author = "Data Engineer"
)

list_table_snapshots("cars")
```

## Transaction recipes

### Simple transaction

```{r simple_transaction, eval=FALSE}
with_transaction(
  create_table(my_data, "my_table"),
  author = "Your Name",
  commit_message = "What changed and why"
)
```

### Multi-step transaction

```{r multi_step, eval=FALSE}
with_transaction({
  # All these operations happen atomically
  create_table(raw_data, "raw_table")
  
  cleaned <- get_ducklake_table("raw_table") |>
    filter(!is.na(key_field)) |>
    create_table("clean_table")
  
  get_ducklake_table("clean_table") |>
    mutate(derived_field = calculate_something(x)) |>
    create_table("analysis_table")
},
author = "Data Engineer",
commit_message = "Full ETL pipeline run"
)
```

### Manual transaction control

```{r manual_transaction, eval=FALSE}
# For fine-grained control
begin_transaction()

create_table(data1, "table1")
create_table(data2, "table2")

# Commit or rollback
commit_transaction(
  author = "Your Name",
  commit_message = "Manual transaction commit"
)

# Or if something went wrong:
# rollback_transaction()
```

## Query optimization recipes

### Preview query without execution

To see the SQL a *read* pipeline will run, use dplyr's `show_query()`:

```{r show_query}
get_ducklake_table("cars") |>
  filter(mpg > 25) |>
  select(mpg, cyl, hp) |>
  show_query()
```

To preview the SQL an in-place *modification* would run (before committing
to it with `ducklake_exec()`), use `show_ducklake_query()`:

```{r show_ducklake_query}
get_ducklake_table("cars") |>
  mutate(mpg = round(mpg)) |>
  show_ducklake_query()
```

### Filter early for performance

```{r filter_early}
# Good: Filter before other operations
get_ducklake_table("cars") |>
  filter(cyl == 6) |>
  mutate(kpl = mpg * 0.425144) |>
  head(3)
```

### Use specific columns

```{r select_columns}
# Good: Select only needed columns
get_ducklake_table("cars") |>
  select(mpg, cyl, hp) |>
  filter(mpg > 25)
```

### Sort or partition large tables for file pruning

For big tables, declaring a sort order or partition keys lets DuckLake skip
whole Parquet files when a query filters on those columns:

```{r sorting_partitioning, eval=FALSE}
# Sorting suits high-cardinality columns like timestamps or ids
set_table_sorting("events", "event_time")

# Partitioning suits low-cardinality columns like year or region
set_table_partitioning("sales", c("year(order_date)", "region"))
```

### Tune lake options

`set_ducklake_option()` adjusts DuckLake's persisted settings at lake,
schema, or table scope, and `get_ducklake_options()` shows what's set:

```{r options, eval=FALSE}
# Trade write speed for smaller files
set_ducklake_option("parquet_compression", "zstd")

# Require a commit message on every snapshot -- useful for audit discipline
set_ducklake_option("require_commit_message", TRUE)

get_ducklake_options()
```

## Cleanup

```{r cleanup}
# Detach from the lake
detach_ducklake("my_lake")
```

## See also

- [Modifying Tables](modifying-tables.html) - Detailed guide to table modification approaches
- [Transactions](transactions.html) - Advanced transaction patterns
- [Time Travel](time-travel.html) - Comprehensive time travel guide
- [Clinical Trial Data Lake](clinical-trial-datalake.html) - Complete real-world workflow