Azure Synapse Analytics

Formerly Azure SQL Data Warehouse

It's well suited for large, fastly growing, and frequently changing data warehouses (e.g., in startups). It's also suited for companies that want a single, relatively easy-to-use, centralized cloud service for all their data needs. Larger, more structured organizations could still benefit from this service by using Synapse Dedicated SQL Pools, knowing that costs will be much higher than other solutions.
I think this product is not suited for smaller, simpler workloads (where an Azure SQL Database and a Data Factory could be enough) or very large scenarios, where it may be better to build custom infrastructure.

Azure Synapse Analytics is very well suited for companies that are using the Microsoft Power BI analytics tool (business intelligence). The reason being, you don't need to provide a data gateway to move data from your database to the reporting service online if you are using this type of database. This is a huge win for processing data.

Big Data load are made simple using polybase feature. You just have to create external tables to connect to any data source files (of any format) in Azure Data Lake. There is no need for map-reducing that is done in Hadoop clusters. You just need to know sql to do data integration.

In terms of a well-suited scenario - the Azure Synapse can be used to capture data from multiple sources (especially from onPrem sources apart from Dataverse) and update the transformed data based on the given conditions (eg: refresh data based on the specified date/time ranges). Also, the transformed data can simply be transferred to Azure Data Lake for further processing by utilizing other analytics tools such as PowerBI.

The most frequent answer to questions like this should be... IT DEPENDS. Synapse Analytics has some role in its DNA. It's not dedicated [to] tasks like some OLTP with many reads, however. When we are talking about Azure Synapse, we are talking about modernized BI stuff with great capabilities to involve big data processing to reach deeper insights [into] our data.

Azure Synapse Analytics is well suited to Data Warehouse scenarios with large data tables because of its distributed computing. If most tables have fewer than 1 million rows, then the cost of Synapse is not worthwhile - regular Azure SQL or Azure Analysis Services could suffice. If most tables have more than 1 million rows, then it's worthwhile to get the additional speed for querying large data sets.

Enterprises which require to manage huge datasets and need support to bigdata capabilities in a cost efficient way. Enterprises that process real-time data for their analysis like streaming data and IOT data. Combining Azure Synapse Analytics and Data lake storage provides a better performance and cost effective way to manage a huge dataset.

It is very well suited for big data analytics. Predictive modeling, optimization, and other large scale analysis benefit from using a properly defined SQL Data Warehouse. It is also suited for simple business intelligence such as building historical and active dashboards.