Search code examples
azure-databricksazure-synapsedelta-lake

Synapse serverless pool to query delta table previous versions

Can we use Synapse serverless pool (Built-in) to query a delta file's previous version?

I am keen to a SQL statement similar to what we do in Databricks:

select * from delta.`/my_dir` version as of 2

Does the OPENROWSET support support a "version selection" option?

If not possible, does registering the delta table to an external managed table helps?

Solution

  • When I try to query delta table in serverless sql pool in synapse using below code:

    select * from delta.original version  as  of  0

    I got below output:

    enter image description here

    As per this

    Serverless SQL pools don't support time travel queries.

    AFAIK with SQL commands are not supported to time travel with Delta Lake. But you can use spark pool loading the data into a dataframe with PySpark. I used below code to load data to data frame:

    df = spark.read.format("delta").option("versionAsOf",0)

.load("<file location>")

    enter image description here

    I displayed the data frame using below code:

    df.show()

    enter image description here

    You can try in this way to query with delta table.