Forum Discussion

smpa01's avatar
smpa01
Community Champion
2 years ago

Is incremental amass data advisable with SQL analytics Endpoint?

Incremental amass data  with df gen2 requires the max watermark (e.g. id) of the target to be queried first and then pass on the source table query filtering

 

// high level these are the steps
A. get max id of target lakehouse table
B. generate rows from source by querying data where src id> target maxId (from A)
C. Append only rows from B to target lakehouse table

 While the doc recommends to query the target table using Lakehouse connector and get the max data using PQ, I want to replace this by querying the target lakehouse table using SQL end-point which is far more efficient in getting a scalar value (select max(id) from StagingLakehouse.dbo.orders) rather than using PQ to get the whole data and then a method to get a scalar value.

 

My only dilema in doing this is SQL-Endpoint-Slow-To-Reflect-Changes-In-Lakehouse  / Delayed-data-refresh-in-SQL-Analytical-Endpoint 

 

What if I do incremental amass frequently in a very short span, I am scared the delay from SQL endpoint will mess up the final table as the most recent id will not be returned by SQL end point.

 

Is some1 currently performing incremental amass with Lakehouse SQL end point?

Dataflows

4 Replies

    • smpa01's avatar
      smpa01
      Community Champion
      2 years ago

      Great insight.

      In that case, is MS working towards fixing the lag? Is there a timeline? This is extemely annoying.

       

      What is safest connector for this purpose ATM (even if inefficient).

       

      I am paying for all my table reads. Hence, I expect MS to cure this lag so that I can write fully qualified efficient SQL queries to bring in scalar values.

      • frithjof_v's avatar
        frithjof_v
        Community Champion
        2 years ago

        I don't know. I'm hoping to get guidance on how to handle the potential lag issue.

         

        I think the ADLS connector (AzureStorage.DataLake() function) in combination with the DeltaLake.Table() function is an option if we want to query the Delta Tables in the 'Lake' part of the Lakehouse from Power BI Desktop.

         

        https://blog.fabric.microsoft.com/en-us/blog/read-data-from-delta-lake-tables-with-the-deltalake-table-m-function/

         

        I'm not sure if Power BI is able to do query folding with this connector. So it could perhaps be more heavy for the Power Query engine.

         

        And I'm not sure if we would get charged for ADLS read/write transactions by using this method. However the latter would surprise me as the data is logically stored in OneLake so I think it should consume Fabric CUs and not ADLS transactions. But I'm just guessing here.