Do you have experience using Microsoft Synapse? Please share any pros and cons.  

Having been in Microsoft , i can tell you that individual product teams have different thoughts about Synapse as a product.

Some folks love the all in one solutioning and the ability to handle large volumes of data while others complain about the speed and cost.

Here are some of the data points for consideration.

Unified Analytics
Azure Synapse integrates various analytics services into a single platform, including:
SQL Technologies: For enterprise data warehousing.
Apache Spark: For big data processing.
Data Explorer: For log and time series analytics.
Pipelines: For data integration and ETL/ELT processes.
Scalability and Flexibility ( Big draw for our evaluations)
Serverless and Dedicated SQL Pools: Offers both serverless and dedicated resource models to handle different workloads efficiently.
Apache Spark Integration: Deep integration with Apache Spark for data preparation, engineering, ETL, and machine learning
Data Integration
Data Integration Engine: The same engine as Azure Data Factory, allowing the creation of rich ETL pipelines.
Ingest Data from 90+ Sources: Supports a wide range of data sources for seamless data integration
Advanced Security and Compliance
Security Features: Includes column- and row-level security, dynamic data masking, and advanced threat protection.
Compliance: Ensures data governance and compliance with industry standards1.
Seamless Data Exploration
Data Lake Integration: Removes barriers between SQL and Spark, allowing seamless data exploration and analysis.
Support for Various File Formats: Directly explore and analyze Parquet, CSV, TSV, and JSON files stored in the data lake
Built-in Data Integration ( for your end users the low code solution is pretty good)
Code-Free ETL: Create ETL pipelines without writing code.
Orchestration: Orchestrate notebooks, Spark jobs, stored procedures, SQL scripts, and more
Real-Time Analytics ( think of this as interactive sql but lower functionality)
Data Explorer: Provides an interactive query experience for system-generated logs, optimized for efficient log analytics

no,i would really like to know and learn as well

Not my personal area of expertise, but here is what my Data Engineer and Cloud Architect had to say:

Data Engineer

Pros:

I like it how Synapse workspace combines a variety of services offering a wide range of capabilities ranging from Synapse Serverless Pool which offers low end toolset for data analytics. Another is Synapse Dedicated Pool although there is a cost involved, but it gives you more horsepower to work with, and finally Synapse Apache Spark, which offers your developers a high-end transformation engine. I especially like Spark because you can design notebooks within the service using a variety of programing languages i.e., PySpark (Python) , Scalar, and T-SQL, which is helpful for your developers who may be more proficient in T-SQL then say Python.

Cons:

Diagnosing problems within your pipelines is very challenging due to the way synapse is architecture. A lot of moving parts… Another is the security model is a bit challenging to understand but that may be Azure as a whole. And finally, Microsoft does offer a similar analytics toolset i.e. Databricks, which seems more advanced in many ways to Synapse Apache Spark but you have to pay extra if you intend to use it.

Overall, the product is OK but seems fairly average compared to other toolsets out there.

Cloud Architect
SQL Managed Instance requires a designated subnet. That can be a difficult concept to grasp at first as it's not just an RFC 1918 or Public CIDR block, but also a unit of security, i.e., this resource is allowed to access all of the services provided by any endpoint in that designated subnet, e.g., an Infoblox DDIN grid member and a DC. I don't recommend its use due to the complexity and the real-world lack of viability of MS Support.

If someone plans on securing Synapse by using private endpoints like we do, then they are in for some challenges. Depending on what you are running in Synapse, dictates where you need to have the private endpoint created. Sometimes you need a private endpoint in the Synapse workspace and sometimes it can be on the resource itself such as a storage account. There will be times when you need to have private endpoints on both sides. The number of private endpoints can add up quickly. We have close to 10 private endpoints for a Synapse instance. Also, Microsoft uses MD5 checksum for data consistency verification on the pipelines which isn’t FIPS compliant. If you want your pipelines to work and be FIPS compliant that option needs to be unchecked. That took about a month for Microsoft to give us a resolution for that since we were not just going to disable FIPS. Overall, it is confusing where the endpoints need to be created, and Microsoft doesn’t make it easy to figure that out. Support is not much help and there are plenty of articles out there on the Internet with the same issues we experienced but with no resolution posted. Synapse is a complex product, and I wouldn’t recommend it to anyone unless they have staff that are very technical and have a strong will to deal with Microsoft support when there are issues.