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

[anilsampson]

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.

[Vidhi_Khaitan · Accepted solution]

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.

[anilsampson]

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?