Have you tried Fabric Link? D365 F&O is on dataverse, so if you're trying to get base tables, you're fine. The problem is that most people work with entities, which are not supported with Fabric Link. You can recreate the entities in the Warehouse if you have the definitions, but I recommend just working off of the base tables when you can. It's an hourly trickle feed shortcut that you don't have to both ETLing. I will warn you that it requires manual refreshing in two places every 60 days or so ... Ask your MS account manager for the details. https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-view-in-fabric

@Piotr Prussak I think the issue with this "AI can fix it" perspective is that is doesn't consider the complexities of translating from one language to another. For example, if you're using correlated subqueries in your TSQL sprocs, good luck translating that to Spark SQL, even with AI. It still requires quite a bit of guidance and testing.

@Lori Keller As usual, I think Lori is spot on here. If the team's skillset is TSQL and they don't (and won't) have the bandwidth to learn PySpark and the nuances between TSQL and Spark SQL, stick with the sprocs. However, if there's room for and interest in learning PySpark, DO IT! It forces you to get back to basics and really evaluate what you're doing. Having the flexibility to do either notebooks or sprocs is a major advantage. There are also a lot of really great functions in PySpark that simply don't exist in TSQL. Deduping is incredibly easy in PySpark. Lateral column aliasing is SUPER convenient. explode is also a really nice PySpark function.

Typically optimize then vacuum. I've never heard of anyone doing it in reverse. Vacuum removes the older, now unreferenced files. If you vacuum before optimize, those current small files are still present.

@Justin Sweet Can you provide the DAX for your [lifts_per_hour]? If it's not something like lifts_per_hour = DIVIDE(SUM( 'Table'[Lifts]), SUM('Table'[Time])) That might be the issue. If you're trying to look at every individual day rather than the true average (total lifts / total time), in which case you'd need to being using AVERAGEX in your Trailing_12_Week_Avg_Lifts_Per_Hour.

@Wilfred Kihara Can you provide a little more context? What SKU are you running on? 7 million shouldn't be enough to challenge any SKU F8 and above. You mention both a notebook and a pipeline. Are you running the notebook from the pipeline? If so, have you successfully tested the notebook from outside the pipeline? If so, are you passing parameters into the notebook?