I need to create a view on top of a specific version of a Delta table. Is there a way to do it within Synapse, and using T-SQL?

Question

I have a Delta table stored in my Azure Data Lake Storage Gen2 (ADLS Gen2) account. When this Delta table is updated by a Synapse pipeline, I want to automatically create a view on top of the latest version of the Delta table. This view should allow me to query the data directly without relying on manual time-travel operations in a notebook. How can I set up this automated view creation in Azure Synapse? Scenario: I'm not allowed to use any Synapse notebook, or Databricks

I created a Dataflow to update my Delta table and eventually create a view on the latest version, but couldn't find such a functionality.

Answer 1

To create a view on the latest version on the Delta table you need to use the synapse pipeline to call stored procedure which will create view on thr latest version.

First create a Stored Procedure to create view on the delta table.

Sample stored procedure:

create PROCEDURE crdeview
AS
BEGIN
exec ('DROP VIEW IF EXISTS samplev1;')
exec ('CREATE VIEW samplev1 AS
    SELECT * 
    FROM OPENROWSET(
            BULK ''https://stacc name.dfs.core.windows.net/fsnp2/'',
            FORMAT = ''delta''
            )
    WITH (
        [Id] Int,
        [name] VARCHAR(100)
    ) AS [r];')
End

the above procedure will drop the previous view and create new view with new version of delta file.

Then call this stored procedure from the synapse pipeline to synapse pool using stored procedure activity.

And add storage event trigger to pipeline to trigger pipeline if new version is added to delta file.

Output: