cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

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

anilsampson
New Contributor III

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.

1 ACCEPTED SOLUTION

Accepted Solutions

Vidhi_Khaitan
Databricks Employee
Databricks Employee

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

2 REPLIES 2

Vidhi_Khaitan
Databricks Employee
Databricks Employee

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
New Contributor III

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?

Join Us as a Local Community Builder!

Passionate about hosting events and connecting people? Help us grow a vibrant local communityโ€”sign up today to get started!

Sign Up Now