iceberg time travel unavailable with nessie catalog

[olivier-derom]

When using Nessie as iceberg catalog (we are using nessie with REST), we are unable to use Nessie's time travelling.

According to Trino documentation, we should use:
SELECT * FROM "nessie"."myschema"."mytable" FOR TIMESTAMP AS OF TIMESTAMP '2024-09-28 09:59:29.803 Europe/Vienna'
However, this will always result in
SQL Error [76]: Query failed (#20241001_081626_00426_nmc3s): No version history table "myschema"."mytable" at or before 2024-09-28T07:59:29.803Z
This only works when selecting a timestamp later than the latest commit to nessie, probably because of how you have to specify an iceberg.rest-catalog.prefix in the iceberg connector and the inner workings of nessie.

When looking at the Nessie documentation on how to time travel, it states some other syntax:
SELECT * FROM nessie.myschema.`mytable#2024-07-01T00:00:00Z`;
Specifically, you need to use backticks, which Trino does not support:
SQL Error [1]: Query failed (#20241001_082410_00427_nmc3s): line 1:35: backquoted identifiers are not supported; use double quotes to quote identifiers
But when using double quotes, we get:
SQL Error [84148237]: Query failed (#20241001_082542_00430_nmc3s): Failed to load table: myschema.mytable#2024-07-01t00:00:00z

This seems to just be a syntax compatibility issue between Trino and Nessie, so any way to fix this?
Let me know if there is anything else I can try to make this work.

In theory you can still time travel with trino+nessie by updating the iceberg.rest-catalog.prefix to an earlier commit, but this is not the desired way to do this.

Thanks!

[ebyhr]

We can time travel to non-latest snapshots with Nessie in Trino Iceberg connector as far as I tested locally. Could you share the entire steps to reproduce (CREATE TABLE, INSERT and SELECT statements on Trino)? Note that we don't support backtick or double-quotes for time travel. Please use AS OF syntax.

[olivier-derom]

@ebyhr
I think the issue might lie in the fact that we are using dbt (dbt-trino) to create our tables.
I dont execute any CREATE/INSERT manually.

As most basic example, have a dbt project, connected to Trino on the nessie iceberg catalog:

profiles.yaml

myprofile:
  target: dev
  outputs:
    dev:
      type: trino
      method: ldap
      user: "{{ env_var('DBT_TRINO_USERNAME') }}"
      password: "{{ env_var('DBT_TRINO_PASSWORD') }}"
      host: "{{ env_var('DBT_TRINO_HOST') }}"
      database: "{{ env_var('DBT_TRINO_ICEBERG_CATALOG') }}"
      schema: "myschema"
      port: "{{ env_var('DBT_TRINO_PORT') | int }}" # Should be cast as int

Create a dummy model in dbt:

execution_time.sql

 {{ config(
    materialized='table',
    properties= {
      "format": "'PARQUET'",
    }
) }}

SELECT 
    current_timestamp(3) AS execution_time

Run dbt with dbt run.

I confirm that the data is available with

SELECT * FROM "nessie-main"."myschema"."execution_time" FOR TIMESTAMP AS OF TIMESTAMP '2024-10-01 12:55:00.000 Europe/Vienna'

(we use DBeaver)
Wait 5 minutes
Run dbt again with dbt run to perform a new iteration and update the table
Try to select the previous data with a SELECT statement
SELECT * FROM "nessie"."myschema"."execution_time" FOR TIMESTAMP AS OF TIMESTAMP '2024-10-01 12:55:00.000 Europe/Vienna'
Notice that Trino is throwing an error as the data is not available.

My guess is that the culprit is the way dbt updates data, by dropping old tables and recreating new data, with the help of temp helper tables, instead of updating/truncating tables. As when we go into the S3 where the iceberg data is stored, we notice for the 2 dbt executions, 2 different folders for the same table are present, both containing a single parquet file in the data folder.

This also happens for 'incremental' materialization in dbt, not only 'table'.

Let me know if you need any more info to reproduce this.