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 ...
Dataflows
  • 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.

      • smpa01's avatar
        smpa01
        Community Champion
        2 years ago

        frithjof_v  if there was no lag, the best solution would be to use the sql endpoint to get a scalar value for incremental amass. It is efficient and has better query plan.

         

        Even if you use Lakouse connectors it first brings the whole table and then you get a scalar value, which to me is inefficient too. Isn't that making unnecessary readings ?

         

        Looks like AzureStorage.DataLake combined with DeltaLake.Table could be safest option ATM even though it might require more CU for reading (I am guessing).

         

        I hope someone from MS comment in this thread in respect to

        A. Lag fixing timeline for SQL endpoint of Lakehouse.

        B. Till it happens, what is the safest connector to use

        C. What is the reading impact of B on CU unit