After my previous blog post on the different semantic model options and at the same time working with a Fabric customer, it got me thinking which is faster and which consumes less capacity when ingesting data into Power BI either via the SQL Endpoint to a Lakehouse or a query from the Warehouse.

Below you will find the information which I found very interesting indeed.

For both the Lakehouse and Warehouse source CSV’s there was a total of 237,245,585 rows.

NOTE: As always, your results may vary so always test on your own data, but this is a good indication below.

Loading data from CSV

The first step was to load the data from a CSV file into the Lakehouse and Warehouse

Here is the code that I used to load the data into Lakehouse

Shown below is how long it took along with the CUs

Next, I was to load the same data into the Warehouse, below is the code that I used

Shown below is how long this took

A quick comparison

As shown above the Warehouse ran 500 seconds faster or over 6.6x (660% quicker).

When looking at the Capacity Units comparison the Warehouse used 12,541 less capacity units or consumed 7.5x (750% less capacity units)

Transforming from Staging into Aggregated table

Next, what I did was to use the staging table to create an aggregated table (something typically done when moving from Bronze to Silver)

Below is the Spark SQL Code that I ran

Shown below is how long it took along with the CUs

I then ran the same query on the Warehouse inserting into a Warehouse table.

Shown below is how long it took along with the CUs

A quick comparison for loading the aggregated data.

As shown above the Warehouse ran 200 seconds faster or over 4.6x (460% quicker).

When looking at the Capacity Units comparison the Warehouse used 1,901 less capacity units or consumed 1.6x (160% less capacity units)

Query Comparison of LH vs WH and WH with Clustering

The final comparison I wanted to compare was to load from Lakehouse, Warehouse and Warehouse Clustered tables into my Semantic Model using Import Mode.

It did take some time to run, because it was importing 100 million rows.

All the details below used the default navigation of finding the table using Power Query with the SQL Server connection.

Lakehouse query below of CUs

Warehouse Query

Warehouse Query on Clustered table

A quick comparison for loading the aggregated data.

As shown above the Warehouse ran 1,329 seconds faster or over 1.97x (197% quicker).

When looking at the Capacity Units comparison the Warehouse used 78,599 less capacity units or consumed 2.6x (260% less capacity units)

While the Warehouse Clustered table ran faster than the Lakehouse the Warehouse was still the quickest.

Summary

When comparing the ingestion of the CSV file, creating an aggregated table and then importing the table into the semantic model the clear winner is the Warehouse as shown with the overall numbers below.

Thanks for reading I hope you found this insightful!