Version AsOf in semantic model.

[EdvanceIQ_Chris]

I ingest data via notebooks into CDC enabled lakehouse tables and they enable timetravel for up to 7 years and can be updated up to three times a day.  The select * from table version asof xx works ok from notbooks and views, but I need to be able to do this from a directlake model.  I need to be able to query the data as of a version across all the tables (about 150) returning the version for each that I specify.  My question is, how can I do this in the model, as there does not appear to be support for version in the models yet.  How can I query the tables as of the version number from the model?  Creating thousands of views is not practical.  If there is no way to do this now, when will the version asof be available to run through models?  Is it on the roadmap?  There must be a way of doing this?  I need users to be able to select a date and then the model returns data from the underlying tables asof the version at the date and time specified (I capture the version number for each table after each back import).  Thanks

[v-saisrao-msft]

Hi @EdvanceIQ_Chris,

Thank you @amitchandak, for your insights.

DirectLake reads the Delta table only at the most recent framed state, so time-travel queries aren’t available in this mode. The Warehouse time-travel documentation also notes that time travel is not available on the Lakehouse SQL analytics endpoint, and that Power BI DirectQuery cannot use time-travel syntax.

Time Travel - Microsoft Fabric | Microsoft Learn
Direct Lake overview - Microsoft Fabric | Microsoft Learn

Thank you.

[EdvanceIQ_Chris]

Thanks for the comment but it is not particularly helpful.  I can see that it is not available at the moment, so how can I get it to work?  What is the solution?  Or, is there a roadmap where this functionality is due to be added.  I want to allow users to select a date in the reports and for the data for those reports to be served up as of the version that existed for all the tables at the required date.  With the functionality available in the underlying tables, it seams crazy that there is no way for a user to be able to select the date/version and use the data from that point.  

[v-saisrao-msft]

Hi @EdvanceIQ_Chris,

At the moment, DirectLake does not have built-in support for user-initiated time-travel. You may want to submit a feature request on the Fabric Ideas forum for Microsoft’s consideration. If the idea receives enough votes, it will be prioritized. You can submit your idea here.

Fabric Ideas - Microsoft Fabric Community

Thank you.

[amitchandak · Accepted solution]

@EdvanceIQ_Chris , I doubt we have that available in the Semantic Model Yet. 

We have a Power Query function get DeltaLake.Table M (https://youtu.be/D_8ldwRTkyg), but that is also static to the data load 

Snapshot is one way, but that will have too much data. 

[EdvanceIQ_Chris]

I could not get it to run live, but compromised with allowing users to pass parameters as a batchID and then refresh a model.  Took time bit worked.  Based on the function I used to decide if the parameter for the batch was passed or not would be how the data was queried.  For those that may find this useful the function was : 

// Query: GetDeltaLakeTable
(tableName as text) as table =>
let
    // Build base connection string
    conn = "https://onelake.dfs.fabric.microsoft.com/" 
           & pWorkspaceName & "/" 
           & pLakeName & ".Lakehouse/Tables/" 
           & pSchema & "/",

    // Load the target table
    Source = AzureStorage.DataLake(conn & tableName, [HierarchicalNavigation=true]),
    ToDelta = DeltaLake.Table(Source),

    // Decide whether to time travel
    timeTravel = Text.Upper(pBatchID) <> "BLANK",

    // Branch logic
    returnVal =
        if timeTravel then
            let
                // Load CDC batch log table
                batchtbl = AzureStorage.DataLake(conn & "_cdc_batch_log", [HierarchicalNavigation=true]),
                logTable = DeltaLake.Table(batchtbl),

                // Lookup version for this batch + table
                Version = GetTableVersion(tableName),

                // Get snapshot at that version
                GetVersions = Value.Versions(ToDelta),
                Result = if Version <> null then GetVersions{Version}[Data] else ToDelta
            in
                Result
        else
            ToDelta
in
    returnVal