---
title: "Time Travel Queries"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Time Travel Queries}
  %\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(), "time_travel_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

DuckLake's time travel capabilities provide a powerful audit trail for your data, enabling you to:

- View data as it existed at any specific point in time
- Query specific versions of your tables
- Restore tables to previous states
- Track the complete history of changes
- Meet regulatory and compliance requirements

This functionality is especially valuable in domains where data provenance and reproducibility are critical, such as clinical trials, financial reporting, and scientific research.

## Setting Up the Data Lake

We'll start by creating a new DuckLake and loading the mtcars dataset. We'll then make several modifications to demonstrate time travel functionality.

```{r create-datalake}
# Install the ducklake extension (required once per system)
# The ducklake extension only needs installing once per machine:
# install_ducklake()

# Create or attach to a data lake
attach_ducklake(
  ducklake_name = "time_travel_demo",
  lake_path = vignette_temp_dir
)

# Create initial table with the mtcars dataset
with_transaction(
  create_table(mtcars, "cars"),
  author = "Data Engineer",
  commit_message = "Initial load of mtcars dataset"
)

# Verify the table was created
get_ducklake_table("cars") |>
  select(mpg, cyl, hp, wt) |>
  head()
```

## Making Changes Over Time

Let's make several changes to our data to create a version history we can explore.

### Version 1: Initial data

We already have our initial dataset. Let's check the current state:

```{r version1}
get_ducklake_table("cars") |>
  summarise(
    n_cars = n(),
    avg_mpg = mean(mpg, na.rm = TRUE),
    avg_hp = mean(hp, na.rm = TRUE)
  )
```

### Version 2: Update fuel efficiency data

Suppose we discover that fuel efficiency measurements need to be adjusted for some vehicles:

```{r version2}
# Update mpg for high-performance cars (5% reduction)
with_transaction(
  get_ducklake_table("cars") |>
    mutate(mpg = if_else(hp > 200, mpg * 0.95, mpg)) |>
    replace_table("cars"),
  author = "Data Analyst",
  commit_message = "Adjust MPG for high-performance vehicles"
)

# Check the updated averages
get_ducklake_table("cars") |>
  summarise(
    n_cars = n(),
    avg_mpg = mean(mpg, na.rm = TRUE),
    avg_hp = mean(hp, na.rm = TRUE)
  )
```

### Version 3: Add efficiency classification

Let's add a new categorical variable to classify cars by fuel efficiency:

```{r version3}
with_transaction(
  get_ducklake_table("cars") |>
    mutate(
      efficiency_class = case_when(
        mpg >= 25 ~ "High",
        mpg >= 20 ~ "Medium",
        TRUE ~ "Low"
      )
    ) |>
    replace_table("cars"),
  author = "Data Analyst",
  commit_message = "Add efficiency classification"
)

# View the new classification
get_ducklake_table("cars") |>
  count(efficiency_class) |>
  arrange(desc(n))
```

### Version 4: Correct an error

Suppose we realize the efficiency classification thresholds were wrong and need to be corrected:

```{r version4}
with_transaction(
  get_ducklake_table("cars") |>
    mutate(
      efficiency_class = case_when(
        mpg >= 30 ~ "High",
        mpg >= 20 ~ "Medium",
        TRUE ~ "Low"
      )
    ) |>
    replace_table("cars"),
  author = "Senior Analyst",
  commit_message = "Correct efficiency classification thresholds"
)

# View the corrected classification
get_ducklake_table("cars") |>
  count(efficiency_class) |>
  arrange(desc(n))
```

## Exploring Version History

Now that we have a history of changes, let's explore the time travel functionality.

### List all snapshots

```{r list-snapshots}
# View all available versions of the table
snapshots <- list_table_snapshots("cars")
snapshots
```

### Query a specific version

Let's look at version 2, before we added the efficiency classification:

```{r query-version2}
# Get version 2 (after MPG adjustment, before classification)
get_ducklake_table_version("cars", version = 2) |>
  select(mpg, cyl, hp, wt) |>
  head()

# Notice: no efficiency_class column yet
```

Compare this with version 3, which has the classification:

```{r query-version3}
# Get version 3 (with initial classification)
get_ducklake_table_version("cars", version = 3) |>
  select(mpg, efficiency_class) |>
  count(efficiency_class)
```

### Query data as of a specific timestamp

We can also query data as it existed at any point in time:

```{r timestamp-query}
# Get the timestamp from version 2
version2_timestamp <- snapshots |>
  filter(schema_version == 2) |>
  pull(snapshot_time)

# Query data as it existed at that time
# Note: Add 1 second to ensure we query AFTER the snapshot was created
get_ducklake_table_asof("cars", version2_timestamp + 1) |>
  summarise(
    avg_mpg = mean(mpg, na.rm = TRUE)
  )
```

## Comparing Versions

One powerful use case is comparing different versions to understand what changed:

```{r compare-versions}
# Get MPG values from version 1 (original) and version 2 (after adjustment)
original <- get_ducklake_table_version("cars", version = 1) |>
  select(mpg) |>
  collect() |>
  mutate(version = "Original")

adjusted <- get_ducklake_table_version("cars", version = 2) |>
  select(mpg) |>
  collect() |>
  mutate(version = "Adjusted")

# Combine and compare
bind_rows(original, adjusted) |>
  group_by(version) |>
  summarise(
    avg_mpg = mean(mpg, na.rm = TRUE),
    min_mpg = min(mpg),
    max_mpg = max(mpg)
  )
```

## Restoring Previous Versions

If we need to undo changes, `restore_table_version()` rolls a table back to
an earlier snapshot in one call:

```{r restore-demo}
# Go back to version 2 (before adding classifications)
restore_table_version("cars", version = 2, author = "Senior Analyst")

# Verify the restoration - efficiency_class column should be gone
get_ducklake_table("cars") |> colnames()
```

You can also restore to a point in time with
`restore_table_version("cars", timestamp = "2026-07-01 09:00:00")`, and pass
a custom `commit_message` if the default ("Restored cars to snapshot 2")
isn't descriptive enough for your audit trail.

Nothing is lost in a restore: the rollback happens *forward*, as a new
snapshot with its own author and commit message, so the full
history — including the states after the restore point — remains available
for time travel. That also means a restore is itself reversible with another
`restore_table_version()` call:

```{r after-restore}
list_table_snapshots("cars")
```

## Pinning a Whole Session to a Snapshot

The queries above travel one table at a time. To freeze *everything* — say,
to re-run a report exactly as it stood at a submission milestone — attach
the lake pinned to a snapshot:

```{r pinned-attach, eval=FALSE}
attach_ducklake(
  "cars_milestone",
  lake_path = "~/data/lake",
  snapshot_version = 2
)
```

Every table then reads as of snapshot 2 with no `AT (...)` clauses needed,
and writes are rejected, so the milestone view can't drift. A
`snapshot_time` argument does the same for a point in time.

## Use Cases for Time Travel

Time travel functionality is particularly valuable for:

1. **Regulatory Compliance**: Maintain complete audit trails for datasets used in regulatory submissions (e.g., clinical trials, financial reporting)
2. **Reproducibility**: Recreate analyses exactly as they were run at specific points in time
3. **Data Recovery**: Restore accidentally modified or deleted data
4. **Change Tracking**: Understand when and how data quality issues were introduced
5. **Reporting**: Generate historical reports using data as it existed at specific time points
6. **Collaboration**: Allow team members to reference specific versions of shared datasets
7. **Debugging**: Identify when unexpected changes occurred in your data pipeline

## Metadata and Audit Information

Each snapshot includes metadata about when it was created and what changes were made. The `list_table_snapshots()` function provides a complete audit trail:

```{r metadata}
# Get detailed snapshot history with all metadata
snapshot_history <- list_table_snapshots("cars")
snapshot_history |>
  select(snapshot_id, snapshot_time, author, commit_message)
```

This complete audit trail ensures that you can always answer questions like:

- What changes were made?
- When were they made?
- What version is the table at?
- What was the data before this change?

You can also access metadata about all tables in the DuckLake:

```{r all-metadata}
# View metadata for all tables
all_snapshots <- list_table_snapshots()
all_snapshots |>
  select(snapshot_id, snapshot_time, changes) |>
  head(10)
```

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