---
title: "Storage and Backup Management"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Storage and Backup Management}
  %\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(), "storage_backups_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)
library(fs)
```

## Introduction

Understanding how DuckLake stores and manages your data is crucial for maintaining a robust data lake. This vignette explains:

- The two-component architecture of DuckLake (catalog and storage)
- What types of files are created and how to inspect them
- Best practices for choosing storage locations
- How to implement backup and recovery strategies

## DuckLake's Two-Component Architecture

DuckLake separates data management into two distinct components:

1. **Catalog (Metadata)**: A database that stores all metadata about your tables, snapshots, transactions, and data file locations. By default this is a DuckDB file, but DuckLake also supports PostgreSQL, SQLite, or MySQL as catalog backends (see `?attach_ducklake`). The catalog is typically small but critically important.

2. **Storage (Data Files)**: A directory containing immutable Parquet files that hold your actual data. DuckLake never modifies an existing file. It only creates new ones.

This separation provides several benefits:

- **Simplified consistency**: Since files are never modified, caching and replication are straightforward
- **Flexible storage options**: Store metadata locally and data in the cloud, or vice versa
- **Independent backup strategies**: Each component can be backed up differently based on your needs

## Storage Options

DuckLake works with any filesystem backend that DuckDB supports, including:

- **Local files and folders**: Fast access, ideal for single-machine workflows
- **Cloud object storage**: 
  - AWS S3 (and S3-compatible services like Cloudflare R2, MinIO)
  - Google Cloud Storage
  - Azure Blob Storage
- **Network-attached storage**: NFS, SMB, FUSE-based filesystems

### Storage Patterns

Here are how some common storage patterns may look:

```{r storage-patterns, eval=FALSE}
# Local storage - fastest, but not shared
attach_ducklake(
  ducklake_name = "local_lake",
  lake_path = "~/data/my_ducklake"
)

# PostgreSQL catalog with S3 data - multi-client, scalable
attach_ducklake(
  ducklake_name = "shared_lake",
  backend = "postgres",
  catalog_connection_string = "dbname=ducklake_catalog host=localhost",
  lake_path = "s3://my-bucket/ducklake/data"
)

# SQLite catalog - lightweight multi-client option
attach_ducklake(
  ducklake_name = "team_lake",
  backend = "sqlite",
  catalog_connection_string = "~/data/metadata.sqlite",
  lake_path = "~/data/parquet_files"
)
```

**Key considerations:**

- **Latency vs. accessibility**: Local storage is fast but not shareable; cloud storage is accessible but has higher latency
- **Scalability vs. cost**: Object stores scale easily but may charge for data transfer
- **Security**: Consider using DuckLake's encryption features for cloud storage

### Cloud Storage Credentials

Object storage needs credentials before a remote `lake_path` will work.
`create_storage_secret()` registers them with DuckDB's secrets manager:

```{r storage-secrets, eval=FALSE}
# Explicit keys, scoped to one bucket
create_storage_secret(
  "s3",
  key_id = Sys.getenv("AWS_ACCESS_KEY_ID"),
  secret = Sys.getenv("AWS_SECRET_ACCESS_KEY"),
  region = "us-east-1",
  scope = "s3://my-bucket"
)

# Or let the AWS credential chain find them (env vars, profiles,
# instance metadata), so no keys sit in code
create_storage_secret("s3", provider = "credential_chain")

# Then attach. With the default duckdb backend the catalog file must stay
# on local disk (DuckDB cannot write a database file to object storage), so
# name its location with catalog_connection_string; lake_path only sets
# where the Parquet data goes.
attach_ducklake(
  "shared_lake",
  lake_path = "s3://my-bucket/ducklake/data",
  catalog_connection_string = "shared_lake.ducklake"
)
```

Secrets are in-memory by default and disappear with the session; pass
`persistent = TRUE` only if you are comfortable with DuckDB writing them,
unencrypted, under `~/.duckdb/`. GCS, Cloudflare R2, and Azure use the same
function with `type = "gcs"`, `"r2"`, or `"azure"`.

Note that `backup_ducklake()` works on local data paths only. For a lake on
object storage, use your provider's replication or sync tooling (bucket
versioning, `aws s3 sync`, and similar) for the data files, and back up the
catalog database with the tools for its backend.

## Inspecting DuckLake Files

Let's create a sample DuckLake and explore what files it generates:

```{r create-sample-lake}
# Create a temporary directory for our demo
lake_dir <- file.path(vignette_temp_dir, "storage_demo")
dir.create(lake_dir, showWarnings = FALSE, recursive = TRUE)

# Create and populate a DuckLake
attach_ducklake(
  ducklake_name = "demo_lake",
  lake_path = lake_dir
)

# Add some data with transactions
with_transaction(
  create_table(mtcars[1:15, ], "cars"),
  author = "Demo User",
  commit_message = "Initial load"
)

with_transaction(
  get_ducklake_table("cars") |>
    mutate(hp_per_cyl = hp / cyl) |>
    replace_table("cars"),
  author = "Demo User",
  commit_message = "Add hp_per_cyl metric"
)

with_transaction(
  get_ducklake_table("cars") |>
    mutate(mpg_adjusted = if_else(cyl == 4, mpg * 1.1, mpg)) |>
    replace_table("cars"),
  author = "Demo User",
  commit_message = "Add adjusted MPG for 4-cylinder cars"
)
```

### Catalog Files

The catalog is a single database file containing all metadata:

```{r view-catalog-files}
dir_tree(lake_dir)
```

The catalog files (`demo_lake.ducklake` and `.wal`) contain all metadata about tables, snapshots, and transactions.

### Storage (Data) Files

Data files are stored in Parquet format in a structured directory:

```{r view-data-files}
# Data files are organized by schema and table
main_dir <- file.path(lake_dir, "main")

dir_tree(main_dir, recurse = 2)
  
# Get details about parquet files
parquet_files <- dir_ls(main_dir, recurse = TRUE, regexp = "\\.parquet$")
for (f in parquet_files) {
  cat(sprintf("  %s (%s bytes)\n", 
              path_file(f), 
              file.size(f)))
}
```

### Understanding File Organization

Each table's data is organized by schema and table, with each transaction creating new Parquet files:

```{r examine-structure}
# List all snapshots to see the version history
snapshots <- list_table_snapshots("cars")
snapshots |>
  select(snapshot_id, author, commit_message)
```

The key insight is that **DuckLake never modifies existing Parquet files**. Each change creates new files, preserving the complete history for time travel queries; files are only ever deleted by the maintenance functions described below, once no snapshot needs them.

## Backup Strategies

### Backing Up the Catalog

The catalog is the most critical component: it maps snapshots to data files. Regular backups are essential.

#### Copying the Catalog

`backup_ducklake()` copies the catalog with DuckDB's `COPY FROM DATABASE`
while the lake stays attached. The copy is taken inside one transaction,
so it is a consistent snapshot of the metadata, and nothing is detached or
shut down along the way. The same statement works by hand:

```{r backup-catalog-simple}
# Create backup directory
backup_dir <- file.path(lake_dir, "backups")
dir.create(backup_dir, showWarnings = FALSE)

# Copy the catalog through DuckDB: the metadata catalog of an attached lake
# is the database __ducklake_metadata_<name>
conn <- get_ducklake_connection()
DBI::dbExecute(conn, sprintf(
  "ATTACH '%s' AS backup;", file.path(backup_dir, "demo_lake.ducklake")
))
DBI::dbExecute(conn, "COPY FROM DATABASE __ducklake_metadata_demo_lake TO backup;")
DBI::dbExecute(conn, "DETACH backup;")

# Copy the data directory as well
dir_copy(
  path = file.path(lake_dir, "main"),
  new_path = file.path(backup_dir, "main")
)

# Verify the backup was created
dir_tree(backup_dir)
```

A plain file copy of the `.ducklake` file works too, but only after
releasing DuckDB's lock on it: detach with `shutdown = TRUE`, copy, then
re-attach. Copying a live catalog produces a corrupt (or, on Windows,
unreadable) file.

To work with the backup, attach it. `override_data_path` is needed because
the catalog remembers the original data location, which the backup no
longer matches, and `create = FALSE` turns a mistyped path into an error
instead of a new, empty lake:

```{r attach-backup}
detach_ducklake("demo_lake")
attach_ducklake(
  ducklake_name = "demo_lake",
  lake_path = backup_dir,
  override_data_path = TRUE,
  create = FALSE
)

# Verify you're working with the backup
list_table_snapshots("cars")

# You can switch back to the original by detaching and reattaching
detach_ducklake("demo_lake")
attach_ducklake("demo_lake", lake_path = lake_dir)
```

**Important**: Transactions committed after a backup won't be tracked when recovering. The data will exist in the Parquet files, but the backup will point to an earlier snapshot.

**Best practices:**

- Back up after batch jobs complete
- For streaming/continuous updates, schedule periodic backups
- Consider using `{cronR}` or `{taskscheduleR}` for automated backups

### Backing Up Storage (Data Files)

Since Parquet files are immutable, backing up storage is straightforward.

#### Local Storage Backup

```{r backup-storage-local, eval=FALSE}
# Use file system tools to copy the entire data directory
backup_data_dir <- file.path(lake_dir, "backups", "main_backup")
dir_copy(
  path = file.path(lake_dir, "main"),
  new_path = backup_data_dir
)
```

#### Cloud Storage Backup

For cloud storage, use provider-specific mechanisms:

**AWS S3:**
- Cross-bucket replication (copies to a different bucket automatically)
- AWS Backup service (scheduled backups within the same bucket)
- S3 versioning (keeps previous versions of objects)

**Google Cloud Storage:**
- Cross-bucket replication
- Backup and DR service
- Object versioning with soft deletes

When using cross-bucket replication, update your data path:

```{r backup-cloud, eval=FALSE}
# Original
attach_ducklake(
  ducklake_name = "prod_lake",
  lake_path = "s3://original-bucket/data"
)

# After recovery from replicated bucket
attach_ducklake(
  ducklake_name = "prod_lake",
  lake_path = "s3://backup-bucket/data",
  override_data_path = TRUE
)
```

## Recovery Procedures

### Recovering from Catalog Backup

If your catalog is corrupted or lost:

```{r recovery-catalog, eval=FALSE}
# Restore from backup by copying the backup file
# (backup_dir here is a directory created earlier, e.g. by backup_ducklake())
file.copy(
  from = file.path(backup_dir, "demo_lake.ducklake"),
  to = file.path(lake_dir, "demo_lake.ducklake"),
  overwrite = TRUE
)

# Reattach to the restored database (detach with shutdown = TRUE before
# overwriting the file, so DuckDB is not holding it open)
attach_ducklake("demo_lake", lake_path = lake_dir, create = FALSE)

# Verify recovery by listing snapshots
list_table_snapshots("cars")
```

### Recovering from Data File Loss

If data files are lost but the catalog is intact:

```{r recovery-data, eval=FALSE}
# Restore data files from backup
dir_copy(
  path = backup_data_dir,
  new_path = file.path(lake_dir, "main"),
  overwrite = TRUE
)

# DuckLake will automatically reconnect to the restored files
# since the catalog maintains the file paths
```

## Routine Maintenance

A lake that sees regular writes accumulates small Parquet files (one per
insert) and old snapshots whose files cannot be reclaimed until the
snapshots are expired. `checkpoint_ducklake()` runs DuckLake's maintenance
steps in one call: it flushes inlined data, merges small files, and
rewrites heavily deleted files. Two steps only happen under a retention
policy: snapshots are expired when the lake carries an `expire_older_than`
option, and released files are deleted when it carries `delete_older_than`.
Without them a checkpoint keeps every snapshot and every file, so the
policy is the first thing to decide. It is set once, persists in the
catalog, and applies to every client of the lake:

```{r retention-policy, eval=FALSE}
# Keep 90 days of time travel; delete released files a week after release
set_ducklake_option("expire_older_than", "90 days")
set_ducklake_option("delete_older_than", "7 days")

# From now on every checkpoint applies the policy
checkpoint_ducklake()
```

Choose `expire_older_than` to match how far back you need to audit or
restore, since expiring a snapshot gives up time travel to it. For finer
control, each step has its own function:

```{r routine-maintenance, eval=FALSE}
# Compact small adjacent Parquet files into larger ones
merge_adjacent_files()

# Preview a retention policy, then apply it
expire_snapshots(older_than = Sys.time() - 30 * 24 * 60 * 60, dry_run = TRUE)
expire_snapshots(older_than = Sys.time() - 30 * 24 * 60 * 60)

# Expired snapshots only *schedule* file deletion; this reclaims the storage
cleanup_old_files(cleanup_all = TRUE)

# Rewrite data files whose rows have mostly been deleted
rewrite_data_files(delete_threshold = 0.5)

# Remove untracked files from the data path. Always dry-run this one first
delete_orphaned_files(dry_run = TRUE, cleanup_all = TRUE)
```

The typical cycle is merge, then expire, then clean up: merging and expiring
both mark files as unreferenced, and `cleanup_old_files()` deletes them.
Expiring a snapshot gives up time travel to it, so choose `older_than` to
match how far back you need to audit or restore.

One task lives outside DuckLake itself: the catalog database. If you use a
PostgreSQL or SQLite catalog, occasionally run `VACUUM` there with that
database's own tooling so metadata queries stay fast. The default DuckDB-file
catalog does not need this.

## Maintenance Considerations

When planning backups, coordinate with maintenance operations:

- **Compaction** (merging adjacent files): Run before backups to ensure consistent file layout
- **Cleanup** (removing obsolete files): Run before backups to avoid backing up unnecessary files

```{r maintenance-backup-sequence, eval=FALSE}
# Recommended backup sequence

# 1. Run maintenance operations (if needed)
merge_adjacent_files()
expire_snapshots(older_than = Sys.time() - 30 * 24 * 60 * 60)
cleanup_old_files(cleanup_all = TRUE)

# 2. Ensure all transactions are committed
# (no pending work)

# 3. Back up: the catalog through COPY FROM DATABASE, the data files by
#    copy, with the lake still attached
backup_ducklake(
  "demo_lake",
  lake_path = lake_dir,
  backup_path = file.path(lake_dir, "backups")
)
```

## Complete Backup Example

DuckLake provides a convenient `backup_ducklake()` function for creating timestamped backups:

```{r complete-backup-function}
# Create a complete backup with timestamp
backup_dir <- backup_ducklake(
  ducklake_name = "demo_lake",
  lake_path = lake_dir,
  backup_path = file.path(lake_dir, "backups")
)

# The function returns the backup directory path
print(backup_dir)
```

The `backup_ducklake()` function:
- Creates a timestamped backup directory
- Copies the catalog through `COPY FROM DATABASE`, with the lake still attached
- Copies the data files from every schema directory in the lake
- Returns the backup directory path for reference

## Cleanup

```{r cleanup}
# Detach the demo lake
detach_ducklake("demo_lake")

# Clean up temporary files
unlink(lake_dir, recursive = TRUE)
```

## Summary

Key takeaways for managing DuckLake storage and backups:

1. **Understand the architecture**: Catalog (metadata) and storage (data) are separate components
2. **Choose storage wisely**: Balance latency, scalability, cost, and accessibility
3. **Files are immutable**: DuckLake never modifies existing Parquet files
4. **Back up regularly**: Catalog backups are critical; back up after batch jobs
5. **Coordinate with maintenance**: Run compaction and cleanup before backups
6. **Test recovery procedures**: Ensure you can actually restore from backups

For production systems, consider:

- Automated backup scheduling (using `{cronR}` or `{taskscheduleR}`)
- Multiple backup locations (local and cloud)
- Testing recovery procedures regularly
- Monitoring backup success and storage usage
- Version control for catalog schema changes