Pleas also consider the limited amount of data. These extension tests are based on the WWI-Sample database and contains only a few tables of the Sales area:<\/p>\n\n\n\n Following my previous blog post<\/a>, I\u2019d like to explore alternative methods for data ingestion using Microsoft’s World Wide Importers sample database. My goal is to compare the CU usage across different ingestion technologies in Microsoft Fabric. In addition, I aim to offer recommendations and ratings for each technology. This post will provide a quick overview of various ingestion approaches based on an Azure SQL Database, with the hope that it will help you with your own projects.<\/p>\n\n\n\n I aim to not only assess the CU usage<\/strong> of the different technologies but also evaluate the overall duration <\/strong>of the ingestion process, the flexibility<\/strong>, and the intuitiveness <\/strong>of each respective technology.<\/p>\n\n\n\n Let\u2019s address the elephant in the room first. Phew, I must admit that I found it quite challenging to get accurate numbers for the CU usage. For example, with ingestion via the notebook, the service took some time just to start the session. I couldn\u2019t find an easy way to separate the CU usage for session startup from the actual execution. Despite this, I did some retesting and believe I got at least close to the correct figures. Of course, CU usage can vary depending on how it\u2019s implemented in your framework and your specific projects. For instance, if you start a new session for each notebook to load data, the CU usage overhead might be higher.<\/p>\n\n\n\n Looking at the metrics app, I found that Dataflow Gen2<\/strong> seems to consume the most CUs, while notebooks <\/strong>perform significantly better than Data Factory Pipelines<\/strong> with copy activities\u2014this disparity is what motivated me to write this blog post. This fact brings us to an important takeaway: If you’re dealing with many small tables, consider switching from Pipelines to Notebooks.<\/strong> Pipeline Copy Activities always round up to a full minute (as I explained in my previous blog post<\/a>), which can lead to unnecessary CU consumption. Moving to Notebooks can help you optimize both performance and costs.<\/p>\n\n\n\n Database mirroring<\/strong> is a bit of a deal-breaker, considering that the CU usage includes the initial setup of the mirrored database. However, it\u2019s also difficult to determine which activities are strictly related to the mirroring, as there were numerous other operations happening in the target lakehouse (where I loaded the data). I focused on the relevant timestamps and took all the lakehouse operations into account (WWI-Sample represents the mirrored database).<\/p>\n\n\n\n Nevertheless, here are the results for the CU usage:<\/p>\n\n\n\n Microsoft describes database mirroring as a low-cost technology, which is absolutely true\u2014the initial mirroring process is very resource-efficient. However, using a mirrored database isn\u2019t always an option in every environment. In my real-world scenario, which led me to this topic, mirroring wasn\u2019t viable because I needed a complete capture of the entire day as Parquet files in my destination lakehouse for archival purposes. Achieving this efficiently requires full control over the ingestion process, which isn\u2019t easily possible with mirroring.<\/p>\n\n\n\n Let’s have a look at the other evaluation factors.<\/p>\n\n\n\n In terms of overall duration, notebook <\/strong>execution was the fastest, completing the ingestion of all Sales tables from the WWI-Sample database in just 44 seconds. This was followed by Dataflows Gen2<\/strong> at 49 seconds and Data Factory Pipelines<\/strong> at 88 seconds. The entire database mirroring<\/strong> process took about 5 minutes from setup to the first full synchronization, but once configured, it could be extremely fast for handling new data in near real-time.<\/p>\n\n\n\n If we focus more on real-time projects\u2014like mine originally was\u2014both Dataflows and the Mirrored DB are immediately out of consideration. I\u2019ve already mentioned the need for an “archive” function, but when it comes to Dataflows, the main drawback for me is the lack of programmability. I wasn\u2019t able to easily ingest all tables within the Sales schema of the database. Yes, Power Query offers extensive transformation capabilities, but when it comes to pure data ingestion, the process feels too rigid for my needs.<\/p>\n\n\n\n That said, I still want to give some credit for how configurable each ingestion method is in adapting to specific requirements. This brings me to my (very personal) evaluation:<\/p>\n\n\n\n You may see that I really like working with notebooks. I was amazed that I can ingest the data with just a few lines of code. The following code snippet is just the ingestion of the tables using the SQL server data dictionary, all lines of code before were just setting up the database connection and importing libraries.<\/p>\n\n\n\n Imagine what you can do with all the custom libraries in Python! <\/p>\n\n\n\n![\"\"](\"https:\/\/blog.ivos.place\/wp-content\/uploads\/2025\/02\/image-2.png\")
<\/figure>\n\n\n\nIdea<\/h2>\n\n\n\n
Results<\/h2>\n\n\n\n
CU usage<\/h3>\n\n\n\n
![\"\"](\"https:\/\/blog.ivos.place\/wp-content\/uploads\/2025\/02\/image-1024x170.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/blog.ivos.place\/wp-content\/uploads\/2025\/02\/image-1.png\")
<\/figure>\n\n\n\nOverall duration<\/h3>\n\n\n\n
Type\n <\/th> Duration [s]\n <\/th><\/tr> Notebook<\/td> 44<\/td><\/tr> Dataflow Gen 2<\/td> 49<\/td><\/tr> Data Factory Pipeline<\/td> 88<\/td><\/tr> Mirrored DB<\/td> 300*<\/td><\/tr><\/tbody><\/table><\/figure>\n<\/div>\n<\/div>\n<\/div><\/div>\n\n\n\n Flexibility<\/h3>\n\n\n\n
Type<\/th> Flexibility<\/th><\/tr> Notebook<\/td> \u2605\u2605\u2605\u2605\u2605<\/td><\/tr> Dataflow Gen 2<\/td> \u2605\u2605<\/td><\/tr> Data Factory Pipeline<\/td> \u2605\u2605\u2605\u2605<\/td><\/tr> Mirrored DB<\/td> \u2605\u2605<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n