Think about the history of SQL Server... in easy words... SQL Server is/was always some kind of data storage for all kinds of business data, so it still is!
Yes, for sure, all those excellent services in Azure are fantastic and doing a lot of great work/offering millions of features to work with your business data... BUT they are built on top of this underlying data storage...
You still have to store that data somewhere somehow... all those "new" services were built as a "workaround" for former SQL Server (on-premise) solutions like SSIS, SSAS, or SSRS.
SSIS is doing all the magic with your data... loading, transforming, merging, deleting, extracting, and filtering thousands of CSV, Paquet, or whatever files into a data "storage."
Some years ago, the only way to move this service into the cloud was to deploy an additional VM into Azure and run that service... an equivalent service to run those magic was introduced as a service... DataBricks and/or ADF, DataLake now combines several kinds of storage you had on-premise with some enhanced and optimized feature sets.
And so on...
And not every customer wants to have its data in the cloud... so you still need a solution to "store" that data on-premise!
So you are asking, "When to choose what?"
First, does your customer needs/want a cloud solution?
If yes, then go ahead with the needs/requirements/pain/budget of your customer...
In my area, this is what happens often:
A lot of my customers have a business need. The business defines, for example, a new application/feature... that needs information from one data storage...
So they decide on one particular application, and that application provider says... "I only support SQL Server 2019 and not any cloud services"...
And many customers are not those big companies that have such specific requirements for several business use cases that require an individual solution. They need it right now and out of the box. And historically, they are not that flexible to migrate all of those (already existing) on-premise processes to the cloud!
Here (in Germany), companies are slow in adapting to the cloud... if they are newly set up companies, they might build everything in the cloud, but not all of those old settled companies. Maybe some more prominent enterprise companies moving slowly into those services by migrating their processes step by step and learning slowly.
They are questing why shouldn't those applications (running SQL DB) move to data lake etc.
Why should they? => Many applications used in business are not able to run in the cloud! Why change all those processes around those applications? What about the network traffic (performance/costs)? Have you ever tried to connect, for example, BIZ BOOK, to a DataBrick/DataLake? That won't work...
So it still comes to "it depends on situations"... it depends on your customer, on your customer's environment, on your customer's requirements, on your customer's budget, and if your customer is open to new things...