Understanding Azure Synapse Analytics: Architecture, Features, and Workloads

How Azure Synapse Analytics Works

Azure Synapse Analytics is an unlimited analytics service for large enterprises, presented as the evolution of Azure SQL Data Warehouse (SQL DW), combining business data storage with macro‑ and big‑data analytics.

When handling, managing, and delivering data for real‑time business intelligence and predictive analytics, Synapse provides a single service for all workloads, made possible by integration with Power BI and Azure Machine Learning, including ONNX‑format model support. As a Microsoft Power BI partner in Spain, Bismart has extensive experience with Power BI and Azure Synapse.

Azure Synapse Components

Microsoft’s service is SaaS, used on demand and only when needed, which impacts cost savings. It consists of four parts:

SQL analytics with full T‑SQL support: SQL pools (pay‑per‑compute unit) and serverless SQL (pay‑per‑TB processed).

Fully integrated Apache Spark.

Connectors for multiple data sources.

Azure Synapse uses Azure Data Lake Storage Gen2 as the data warehouse and provides a unified data model for management, monitoring, and metadata. In security, it supports protection, monitoring, and management via single sign‑on and Azure Active Directory integration. It essentially completes the entire data integration and ETL process, going beyond a simple data warehouse by enabling reporting and visualization.

It supports many programming languages, including SQL, Python, .NET, Java, Scala, and R, making it suitable for diverse analytical workloads and engineering profiles.

All of this is available in Synapse Analytics Studio, allowing easy integration of AI, machine learning, IoT, intelligent apps, or BI on a single unified platform.

Using T‑SQL and Spark

Synapse offers two execution engines: the traditional SQL engine (T‑SQL) and the Spark engine. T‑SQL can be used for batch, streaming, and interactive processing, while Spark is used when Python, Scala, R, or .NET are needed for big‑data processing.

It connects directly to Azure Databricks, an Apache Spark‑based AI and macro‑data analytics service, providing a high‑performance connector for fast data transfer. This enables using Azure Databricks for ETL workloads and running analyses on the same data in Azure Data Lake Storage.

Azure Synapse and Azure Databricks give us greater opportunities to combine analytics, BI, and data‑science solutions with a shared data lake.

Achieving Maximum Compatibility and Power

Microsoft designed the service to solve two fundamental problems: compatibility and versatility. Its integrated analytics system can handle both traditional structured data (e.g., customer databases) and unstructured data stored in a data lake.

Automation of tasks for building analytical systems reduces developer effort and shortens project timelines; Synapse was the first system to execute all TPC‑H queries at petabyte scale.

Projects that once took months can now be completed in days, and complex database queries that used to require minutes or hours now finish in seconds.

Synapse also features a 1 TB fully managed result cache, accelerating subsequent queries on the same data and surviving pause, resume, and scaling operations.

Workloads and Performance

Notable features include comprehensive JSON support, data masking for security, SSDT (SQL Server Data Tools) integration, and workload management that allows allocating CPU and concurrency to different workloads (e.g., 60 % to sales, 40 % to marketing).

For data ingestion, it supports native SQL streaming, integrates with Event Hub or IoT Hub, and delivers up to 200 MB/s with second‑level latency, scaling with compute.

Additional Features

Key extra capabilities that accelerate data loading and processing:

Copy command no longer requires external tables; it can load directly into the database.

Full CSV support, including custom delimiters and SQL date handling.

User‑controlled file selection with wildcard support.

Machine‑learning support: create and store ONNX models in Synapse and use the native PREDICT command.

Data Lake integration: reads Parquet files from the lake, boosting performance and increasing PolyBase execution speed by over 13×.

In short, Azure Synapse Analytics ensures that SQL DW customers can continue running existing workloads in production while automatically benefiting from new features.