# Querying Historical Data with Time Travel

> Growing Tips (Apache Iceberg & Spark 101), part 14 of 16.
> Iceberg keeps a journal of every change. Query your table as it was, compare seasons, and roll back mistakes.
> Source: https://oleander.dev/grow/querying-historical-data-with-time-travel

In Part 3, we introduced an Iceberg snapshot as a point-in-time version of a table.

Every write creates a new snapshot. The current table points to the latest snapshot, while older snapshots preserve the table’s earlier states.

Until now, we have mostly cared about the current state. We wrote data and then queried whatever the table looked like afterward.

Sometimes, however, the useful question is historical:

- What did this table contain before the latest ingestion?
- Did yesterday’s count change?
- Which version produced a result that someone saw earlier?

Iceberg **time travel** lets us query an older snapshot without copying the table, restoring a backup, or changing its current state.

In this part, we will append another file to `retail_clickstream_events`. The file includes both a new day of events and some events from the previous day that arrived late. This gives us two meaningful table versions to compare.

The late data is our example. Time travel is the main subject.

## Current and Historical Table States

An ordinary query reads the current snapshot:

```sql
SELECT *
FROM oleander.tutorial.retail_clickstream_events
```

Spark and Iceberg also let us choose an older snapshot in two ways.

We can identify an exact snapshot with `VERSION AS OF`:

```sql
SELECT *
FROM oleander.tutorial.retail_clickstream_events
VERSION AS OF 123456789
```

Or we can ask for the table as it existed at a particular moment with `TIMESTAMP AS OF`:

```sql
SELECT *
FROM oleander.tutorial.retail_clickstream_events
TIMESTAMP AS OF '2026-01-01 12:00:00'
```

Both clauses affect only the query. They do not roll back the table or make the selected snapshot current again.

A simple mental model is:

- an ordinary query asks, “What does the table look like now?”
- `VERSION AS OF` asks, “What did this exact snapshot contain?”
- `TIMESTAMP AS OF` asks, “What was the latest table state at this moment?”

Let’s create a new table state and try both approaches.

## Downloading the New Events

Download the day-eight Parquet file from a Unix shell:

```bash
curl -L \
  -o retail_clickstream_events_day8_with_late_data.parquet \
  https://oleander-iceberg-spark-tutorials.s3.us-east-2.amazonaws.com/retail_clickstream_events_day8_with_late_data.parquet
```

From the interactive Spark shell, keep the session timezone in UTC and read the file:

```python
spark.conf.set("spark.sql.session.timeZone", "UTC")

incoming_events = spark.read.parquet(
    "retail_clickstream_events_day8_with_late_data.parquet"
)

incoming_events.createOrReplaceTempView("incoming_events")
```

The file has the same schema as `retail_clickstream_events` and contains 739 new event IDs.

Before appending it, let’s look at the dates in the file:

```python
spark.sql("""
    SELECT
      CAST(event_time AS DATE) AS event_date,
      CAST(ingestion_time AS DATE) AS ingestion_date,
      COUNT(*) AS event_count
    FROM incoming_events
    GROUP BY
      CAST(event_time AS DATE),
      CAST(ingestion_time AS DATE)
    ORDER BY
      event_date,
      ingestion_date
""").show(truncate=False)
```

The result is:

```text
+----------+--------------+-----------+
|event_date|ingestion_date|event_count|
+----------+--------------+-----------+
|2026-01-07|2026-01-08    |91         |
|2026-01-08|2026-01-08    |648        |
+----------+--------------+-----------+
```

`event_time` tells us when the customer action happened. `ingestion_time` tells us when the event reached the data pipeline.

Some ingestion delay is normal. In fact, `ingestion_time` should usually be later than `event_time`, even if only by a few seconds. For this exercise, we call an event **late** when its UTC ingestion date is later than its UTC event date.

The file therefore contains:

- 648 ordinary January 8 events
- 91 January 7 events that were ingested on January 8

Because Part 13 partitioned the raw table with `day(event_time)`, those 91 late events belong to the January 7 partition. Their ingestion date does not move them into the January 8 event partition.

## Capturing the Historical Reference Points

We are going to append the file only once, but we want to demonstrate two kinds of time travel.

Before the append, we will capture both references that identify the table’s current state:

1. its current snapshot ID
2. the current UTC wall-clock time

Both values must be captured before ingestion.

### Capturing the Snapshot ID

Iceberg exposes table metadata through special metadata tables. The `snapshots` metadata table contains one row for each known snapshot.

First, inspect the recent snapshot history:

```python
spark.sql("""
    SELECT
      committed_at,
      snapshot_id,
      parent_id,
      operation
    FROM oleander.tutorial.retail_clickstream_events.snapshots
    ORDER BY committed_at DESC
""").show(truncate=False)
```

Snapshot IDs differ between tables and environments, so we should not put a fixed one in the tutorial.

Instead, capture the latest snapshot ID in a Python variable:

```python
before_snapshot_id = (
    spark.sql("""
        SELECT snapshot_id
        FROM oleander.tutorial.retail_clickstream_events.snapshots
        ORDER BY committed_at DESC
        LIMIT 1
    """)
    .first()["snapshot_id"]
)

print(before_snapshot_id)
```

We will use this value with `VERSION AS OF` after the append.

### Capturing the UTC Timestamp

We can capture the current UTC timestamp directly in the PySpark shell with Python’s `datetime` module:

```python
from datetime import datetime, timezone

before_ingestion_timestamp = datetime.now(timezone.utc).strftime(
    "%Y-%m-%d %H:%M:%S"
)

print(before_ingestion_timestamp)
```

`timezone.utc` makes the timezone explicit, and `strftime()` formats the value for the SQL query we will run later. The value will depend on when you follow the tutorial. That is expected; time travel is one of the few tutorial features that politely notices when you are reading it.

We will use this value with `TIMESTAMP AS OF` after the append.

## Creating the New Table Version

Now append the incoming events:

```python
(
    incoming_events
    .writeTo("oleander.tutorial.retail_clickstream_events")
    .append()
)
```

This commit creates a new Iceberg snapshot. The current table now contains the original seven days, the 648 January 8 events, and the 91 late January 7 events.

Do not append the file a second time for the timestamp example. Both time-travel approaches compare against the same single append.

The raw write is still append-only. Running it again would insert the same 739 events again.

## Approach One: VERSION AS OF

Let’s first query the current table for January 7:

```python
spark.sql("""
    SELECT COUNT(*) AS event_count
    FROM oleander.tutorial.retail_clickstream_events
    WHERE event_time >= TIMESTAMP '2026-01-07 00:00:00'
      AND event_time < TIMESTAMP '2026-01-08 00:00:00'
""").show(truncate=False)
```

The current table includes the late events:

```text
+-----------+
|event_count|
+-----------+
|704        |
+-----------+
```

Now query the same date using the snapshot ID captured before ingestion:

```python
spark.sql(f"""
    SELECT COUNT(*) AS event_count
    FROM oleander.tutorial.retail_clickstream_events
    VERSION AS OF {before_snapshot_id}
    WHERE event_time >= TIMESTAMP '2026-01-07 00:00:00'
      AND event_time < TIMESTAMP '2026-01-08 00:00:00'
""").show(truncate=False)
```

The historical snapshot returns:

```text
+-----------+
|event_count|
+-----------+
|613        |
+-----------+
```

The current count is 91 higher, which matches the late January 7 rows in the incoming file.

We can make the comparison more specific by looking at event types:

```python
spark.sql(f"""
    WITH before_ingestion AS (
      SELECT
        event_type,
        COUNT(*) AS before_count
      FROM oleander.tutorial.retail_clickstream_events
      VERSION AS OF {before_snapshot_id}
      WHERE event_time >= TIMESTAMP '2026-01-07 00:00:00'
        AND event_time < TIMESTAMP '2026-01-08 00:00:00'
      GROUP BY event_type
    ),
    after_ingestion AS (
      SELECT
        event_type,
        COUNT(*) AS after_count
      FROM oleander.tutorial.retail_clickstream_events
      WHERE event_time >= TIMESTAMP '2026-01-07 00:00:00'
        AND event_time < TIMESTAMP '2026-01-08 00:00:00'
      GROUP BY event_type
    )
    SELECT
      event_type,
      COALESCE(before_count, 0) AS before_count,
      COALESCE(after_count, 0) - COALESCE(before_count, 0) AS added_count,
      COALESCE(after_count, 0) AS after_count
    FROM before_ingestion
    FULL OUTER JOIN after_ingestion USING (event_type)
    ORDER BY event_type
""").show(truncate=False)
```

This produces:

```text
+----------------+------------+-----------+-----------+
|event_type      |before_count|added_count|after_count|
+----------------+------------+-----------+-----------+
|add_to_cart     |98          |13         |111        |
|checkout_start  |63          |19         |82         |
|page_view       |124         |8          |132        |
|product_view    |220         |15         |235        |
|purchase        |41          |32         |73         |
|remove_from_cart|10          |2          |12         |
|search          |57          |2          |59         |
+----------------+------------+-----------+-----------+
```

`VERSION AS OF` is useful when we need an exact, reproducible table version. A snapshot ID can be recorded in an audit log, attached to an analytical result, or shared with someone investigating the same data later.

The trade-off is that we need to know the snapshot ID.

## Approach Two: TIMESTAMP AS OF

People often remember a moment more naturally than a snapshot number.

Someone may ask, “What did this table contain before the ingestion ran?” They may know that the job ran at 10:00 AM without knowing that the previous snapshot was `734926184...`—a number with all the warmth of a parking receipt.

Use the UTC timestamp captured before the append:

```python
spark.sql(f"""
    SELECT COUNT(*) AS event_count
    FROM oleander.tutorial.retail_clickstream_events
    TIMESTAMP AS OF '{before_ingestion_timestamp}'
    WHERE event_time >= TIMESTAMP '2026-01-07 00:00:00'
      AND event_time < TIMESTAMP '2026-01-08 00:00:00'
""").show(truncate=False)
```

This also returns the pre-ingestion count:

```text
+-----------+
|event_count|
+-----------+
|613        |
+-----------+
```

`TIMESTAMP AS OF` selects the latest snapshot committed at or before the requested time. It does not require a snapshot to have been committed at that exact second.

This makes timestamp travel practical for investigating incidents, comparing reports produced at different moments, or answering questions about what a table looked like before a known job run.

The timestamp must not be earlier than the table’s first available snapshot. We also use UTC so that Python, the Spark session, and the query literal all agree about what the moment means.

## Inspecting the Snapshot History

After the append, query the snapshots metadata table again:

```python
spark.sql("""
    SELECT
      committed_at,
      snapshot_id,
      parent_id,
      operation
    FROM oleander.tutorial.retail_clickstream_events.snapshots
    ORDER BY committed_at DESC
""").show(truncate=False)
```

The newest row represents the append we just performed. Its `parent_id` points to the previous snapshot in the table’s history.

That previous snapshot is the state selected by `before_snapshot_id`. Because `before_ingestion_timestamp` was also captured before the append, timestamp travel selects that same state.

Time travel does not reconstruct the old count by subtracting 91 rows from the current count. Iceberg reads the table metadata for the selected snapshot and plans the query using the data files referenced by that version.

The current snapshot remains current throughout all of these queries.

The [Iceberg Spark query documentation](https://iceberg.apache.org/docs/1.10.1/docs/spark-queries/#time-travel-queries-with-sql) describes both SQL time-travel clauses in more detail.

## What Time Travel Does Not Do

Time travel helps us inspect what changed in `retail_clickstream_events`. It does not automatically propagate the 91 late events into tables derived from it.

Tables such as these contain materialized results:

- `retail_daily_traffic_summary`
- `retail_sessions`
- `retail_session_products`
- `retail_daily_product_funnel`
- `retail_daily_product_cart_abandonment`

They are tables, not views that rerun their queries whenever the source changes. Their January 7 rows still reflect the data available when their jobs originally ran.

If the late events should appear in those results, the affected intervals must be recomputed in dependency order. The raw daily summaries and session tables must be updated before downstream funnels and cart-abandonment metrics can use the corrected data.

That reprocessing should use `MERGE INTO` or another retry-safe reconciliation strategy rather than append a second copy of the same summary rows.

Part 12 demonstrated that pattern for the cart-abandonment destination. It did not convert every earlier job into a merge job, so our tutorial pipeline does not yet provide automatic or fully retry-safe historical backfills for every derived table.

This distinction is important:

**Time travel lets us inspect an earlier table state. Reprocessing changes derived tables so that they reflect corrected source data.**

In this part, we are doing the first one.

## Practical Limits

Time travel depends on Iceberg retaining the selected snapshot and the files needed by that snapshot.

Tables do not usually keep every historical snapshot forever. Maintenance policies may expire old snapshots and remove data files that are no longer referenced. After that cleanup, a sufficiently old snapshot is no longer available for time travel.

Snapshot retention is therefore both a storage decision and a business decision. A table used for audits may need a longer history than a short-lived staging table.

There is also a DataFrame form of Iceberg time travel. A DataFrame reader can select an exact version with the `snapshot-id` option or select a point in time with `as-of-timestamp`:

```python
historical_events = (
    spark.read
    .format("iceberg")
    .option("snapshot-id", before_snapshot_id)
    .load("oleander.tutorial.retail_clickstream_events")
)
```

The `as-of-timestamp` DataFrame option accepts Unix time in milliseconds. We will keep the detailed walkthrough in Spark SQL because `VERSION AS OF` and `TIMESTAMP AS OF` express the two ideas more directly.

Finally, remember that the incoming Parquet append is not retry-safe. If you repeat it, time travel will faithfully preserve a history containing duplicated input. Time travel remembers what happened; it does not judge our life choices.

## Summary

In this part, we used Iceberg time travel to compare `retail_clickstream_events` before and after a new ingestion.

An ordinary query read the current snapshot and found 704 January 7 events. `VERSION AS OF` selected the exact snapshot captured before ingestion and returned 613. `TIMESTAMP AS OF` selected the latest snapshot at the captured UTC moment and returned the same historical result.

Snapshot IDs are precise and reproducible. Timestamps are often more practical when we know when something happened but do not know the corresponding snapshot number.

We also saw an important boundary: time travel reads historical source data, but it does not update materialized derived tables. Reflecting late data downstream requires controlled recomputation and retry-safe writes.

The late events gave us a useful before-and-after example, but time travel was the lesson.

In the next part, we will replace batch-file ingestion with Spark Structured Streaming and write clickstream events to Iceberg continuously.