Forum Discussion

VickyDev18's avatar
VickyDev18
Advocate II
2 years ago

Fabric - Direct Lake vs Direct Query

I've been playing around with Fabric and trying to get a better understanding of Lakehouses, Datawarehouses, Direct Lake etc.

I primarily have a Power BI and SQL background.   

 

Here's what I did:-

  • I have several files that represent different tables in a source system in CSV format in the Data Lake.
  • I used Data Flow Gen2 to load these tables into a Datawarehouse in Fabric
  • I then created views based on those tables (Gold layer)
  • I then connected to the Datewarehouse from Power BI Desktop. It ended up showing all the tables and views with message at bottom saying it's connect live to the dataset (basically the default dataset). 
  • Since I only wanted the views to be part of my model, I clicked edit and it asked me to convert to a local model, post which I was able to select just the views I wanted. 
  • At this point, the storage mode started showing as Direct Query

 

Questions:-

  1. In step 4 was I in Direct Lake Mode?
  2. If yes, in step 6 am I still in Direct Lake Mode or Direct Query? What's the difference?
  3. If I am not in direct lake mode, are SQL queries essentially being generated and run against the datawarehouse?
  4. If yes, would that mean performance is definitely slower than Direct Lake mode?
  5. How exactly do I know if I am connected in Direct Lake mode?

 

Need Help

3 Replies

  • Brunner_BI's avatar
    Brunner_BI
    Impactful Individual
    2 years ago

    1) If you open the SQL endpoint you should see that your is in Direct Lake mode (see screenshot)

    Then in the report it shows live connection to your dataset

     

    2) When you switched from live connection to direct query, I think you killed Direct Lake - but I am not sure exactly what happens then.

    3) Yes - but even in Direct Lake mode I think it is doing something similar to that.

    4) Direct Lake is supposed to be faster

     

    I heard a lot of times you just need to test it out and see what the performance difference will be.

    Maybe someone else can comment who has more experience with this already.

    • VickyDev18's avatar
      VickyDev18
      Advocate II
      2 years ago

      Thanks for your inputs. As you indicated, perhaps the best way to understand the differences in performance would be test things out. The key thing I'm trying to determine is the choice between the following 3

       

      If I have raw data in delta parquet format but need to do some transformations to it before using in the data model then which of these approaches is better, at least for not so large datasets. 

       

      OPTION 1: Use Dataflow to transform the data and load as delta tables and use in direct lake mode in PBI.
      OPTION 2: Define views based on the delta tables and import them into the PBI data model.

      OPTION 3: Define views and use them directly in the PBI model (direct query).  

       

      In option 1, time is spent in transforming, in option 2 time is spent in processing the model.  In option 3, there is no additional processing time but most likely individual queries are slower as it's direct query and not direct lake.  

       

      I will test these options out and post my findings. 

       

      • NimaiAhluwalia's avatar
        NimaiAhluwalia
        Continued Contributor
        1 year ago

        Hello VickyDev18 

         

        Have you tested the above scenarios, do you have anything valuable to share with the community.

         

        Thanks