How to get previous version of the table in databricks sql dynamically

anilsampson
New Contributor III

‎07-06-2025 10:40 PM

hello,

 

im trying to get the previous version of a delta table using timestamp but databricks sql does not allow to use variables the only thing i can do is use TIMESTAMP AS OF CURRENT_DATE() -1 if i have refreshed the table today.

please let me know if anyone has a better option.

please note i am using databricks sql as this is the only allowed language on databricks dashboard.

Labels:

Vidhi_Khaitan
Databricks Employee
Databricks Employee

‎07-07-2025 12:35 AM

Hello @anilsampson good day!

I believe these are the options you are try -

Instead of using a timestamp, you can directly reference an explicit version number in your queries. This completely avoids dynamic/deterministic date handling or any need for variables.

SELECT * FROM my_table VERSION AS OF <version_number>;

or RESTORE:
RESTORE TABLE my_table TO VERSION AS OF <version_number>;


If you know the exact timestamp you want to use, you can hardcode this value as a literal in your query.

SELECT * FROM my_table TIMESTAMP AS OF '2024-07-06 16:00:00';
RESTORE TABLE my_table TO TIMESTAMP AS OF '2024-07-06 16:00:00';


If your time-travel needs are simple (e.g., "give me yesterday's state"), you can use inline expressions like:

CURRENT_DATE() - 1
date_sub(current_date(), 1)
current_timestamp() - interval 12 hours

These are the only form of calculation allowed within the TIMESTAMP AS OF or RESTORE commands.

View solution in original post

anilsampson
New Contributor III

‎07-07-2025 01:57 AM

thank you @Vidhi_Khaitan  .

Is there an upgrade or use case in works where we can pass parameters via workflow while triggering a databricks dashboard?

0 Kudos