Time Travel Error when selecting from materialized view (Azure Databricks)

[EAnthemNHC1]

Hey - running into an error this morning that was brought to my attention via failed refreshes from PowerBI. We have a materialized view that, when queried with the standard pattern of 'select col1 from {schema}.table_name', returns an error of 'Cannot time travel Delta Table to version XXXX'. This is odd to me for a few reasons.

1: materialized views don't support time travel as far as I can tell: https://learn.microsoft.com/en-us/azure/databricks/ldp/dbsql/materialized#limitations

2: The query isn't specifying any version. 

I've tried changing computes between warehouse and serverless and I still get the same errors. There are other MV's in that pipeline with all the same dependencies, that are not having this issue. 

Has anyone ran into this, or have any insight? Thanks

[saurabh18cs]

Hi @EAnthemNHC1 there could be multiple reasons :

1) The materialized view is backed by a Delta table. If the underlying Delta table has been vacuumed (old versions removed), but the materialized view metadata or refresh logic tries to access a specific (now-missing) version, you’ll get a “Cannot time travel Delta Table to version XXXX” error.

2) If a refresh failed or was interrupted, the MV metadata may be out of sync with the underlying data.

trying forcing a refresh on MV

REFRESH MATERIALIZED VIEW {schema}.{table_name}

or worst trying re-creating a MV

Br

[nayan_wylde]

DESCRIBE HISTORY catalog.schema.table_name;

Check the earliest available version. If the version mentioned in the error is older than what’s retained, that’s the issue.

Also, inspect the materialized view’s backing pipeline in Catalog Explorer → Refresh Details to see if it attempted incremental refresh.

[cookiebaker]

Since last monday december 8th we're experiencing this same issue: Cannot time travel Delta table to version 158. Available versions: [70, 4]. However, this is just when doing a simple SELECT * statement from a gold view (without any version specified). The issue seems to start in the silver layer of that live table (materialized view). Since DLT stores that as a materialized view we cannot see the version history of the table. The bronze tables of which the silver layer is build up of, can all be queried successfully. 

Another interesting thing to mention: The error only occurs when querying from  ADB environment A with SQL Warehouse A on the table in Unity Catalog 'production', however, when we're querying from ADB environment B, with its own SQL warehouse B, but the same table in Unity Catalog 'production', then I can query the tables successfully, without any of the version errors. The versions of the serverless sql warehouses and their channels are exactly the same in both environments.

[cookiebaker]

OUR FIX
Eventually we learned via DESCRIBE EXTENDED, that the table had lost its Location path in production environment. Querying via the development environment to the production catalog  the location path  was still visible.
Solved the issue (with the right Manage rights on the materialized views) by deletion of the views from bronze, silver to gold and refreshed the DLT again. After that the location path of the table was visible in PRD as well. I have not found the rootcause of the corrupted locationpath for this specific table in our PRD environment.