Azure Synapse Analytics: Features, Advantages and Setup

What is Azure Synapse Analytics

Azure Synapse Analytics is Microsoft’s integrated analytics service that combines enterprise data warehousing with big data processing in a single platform. It provides a dedicated SQL pool for structured warehouse queries, serverless SQL for on-demand data lake queries, Apache Spark pools for data engineering, and native integration with Power BI and Microsoft Azure Machine Learning.

For a general overview of Azure Synapse — covering architecture, comparison with Azure Data Factory, and pricing models — see our introductory article.

Identifying insights in business data with Azure Synapse Analytics

Azure Synapse Analytics: features and functions

Azure Synapse Analytics is an analysis service defined by Microsoft as “limitless”, which boasts extensive functionality such as provisioned computing, workload isolation, integration with Power BI, Azure ML and Apache Spark, streaming analysis, hybrid data ingestion, column and row level security, dynamic data masking and much more.

This is an evolution of Azure SQL Data Warehouse (DW) with some significant improvements such as on-demand queries as a service and, with a deeper integration with other technology stacks, it allows users to securely retrieve data from sources such as a data warehouse, a data lake and big data analysis systems, thus accelerating the transition from raw data to business insights.

In addition, the platform allows customers to take advantage of cutting-edge technologies such as Power BI, Azure Machine Learning and the latest findings in the field of artificial intelligence.

In short, Azure Synapse Analytics is a single platform for analyzing all your organization’s data without having to copy or move terabytes of information, thus strengthening self-service capabilities. Even business users, with minimal technical knowledge, can recover data through departmental silos without any special effort.

Using the familiar SQL language, the service allows users to query both relational and non-relational data. Data analysis and exploration can be carried out both using serverless on-demand queries for ad hoc analysis and exploration, and using provisioned resources (dedicated SQL pool) for predictable and demanding data warehouse needs.

A serverless SQL pool provides access to external files stored in Azure Storage without requiring that the data be copied or uploaded to another location, using the T-SQL dialect. Synapse workspaces include this service by default, so users can use it as soon as their workspace is created.

With this approach, there’s no infrastructure to maintain and there are no costs associated with keeping the services running. The service is priced based on consumption, so the costs are based only on the data processed by the queries. Data budget (TB) limits can be used to control the costs of data used in a day, week, or month.

An enterprise data warehouse can benefit from a dedicated SQL pool. Data is stored in tables with columnar storage, which improves performance and reduces costs. A parallel processing architecture is also used to execute queries.

This functionality is not enabled by default in Azure Synapse Analytics; therefore, you must create a pool and select the relevant performance levels, which can be changed later. The cost of a dedicated pool is determined for now, but it can be controlled by scaling the service up or down when necessary. Pools can also be suspended when not in use.

In addition to its core capabilities, Azure Synapse Analytics also offers the following capabilities:

• Exploring data lakes: it wasn’t always easy to analyze data for certain file formats or it required additional tools. A Parquet file, for example, is great for storage but hard to read because it’s highly compressed. Using Synapse, we can right-click on a file and open it with an SQL script quickly and easily.‍
• Choice of language: Synapse Analytics supports several languages and users can choose between T-SQL, Python, Scala, Spark SQL or .Net for serverless or dedicated resources, based on their preferences.‍
• Delta Lake Support: the service is compatible with Delta Lake from the Linux Foundation, an open-source storage layer that provides ACID transactions (acronym for atomicity, consistency, isolation, durability, the four fundamental properties of a database transaction, essential to ensure their integrity and reliability) for Apache Spark workloads and big data. In addition, it includes time travel (data versioning) and manages scalable metadata.‍
• Synaptic path for Azure: this tool simplifies and accelerates the migration of on-premises and cloud data warehouses to Azure Synapse Analytics. By connecting to the source system, it reviews the details of the database objects and provides a detailed evaluation report.

Azure Synapse Analytics: benefits and use cases

Now that we have generally understood its characteristics, it is time to move on to practical advantages. The use of Azure Synapse Analytics by Microsoft Azure as a cloud-based analysis tool, Big Data can offer enormous benefits for your business in the short and long term.

In the list below, we offer you some of the most relevant.

Unified data platform

Synapse Analytics brings together the best of Azure data services and other services, ensuring that they work together seamlessly to provide a unified data analysis platform that can meet the needs of your organization. These services include Azure Data Warehouse, Azure Data Lake, Azure Active Directory, Azure Data Factory, Apache Spark, and Microsoft Power BI.

With this platform, it is possible to use a single web-based user interface (UT) to carry out various data activities, such as exploring data, executing experiments, and developing data pipelines that guarantee an uninterrupted flow of data to generate useful business insights.

Integration with machine learning

Synapse Analytics offers Machine Learning (ML) capabilities that can be applied to a variety of purposes. The most common is the application of ML algorithms to facilitate the acquisition and understanding of data. Azure Data Factory can be used to create data pipelines that transform business data into a consumable format for ML and generate insights from that data through reports prepared with Apache Spark or serverless SQL pool.

You can also train ML models using both Apache Spark Pools and Azure Machine Learning Automated ML, and these ML models can then be distributed to generate forecasts within the data warehouse itself.

Integrated security

Azure Synapse Analytics offers a number of security features and complies with nearly 30 industry-leading compliance regulations, such as the International Organization for Standardization (ISO), System and Organization Controls (SOC), and the Health Insurance Portability and Accountability Act (HIPAA), among others. It supports Azure Active Directory (AD), SQL-based authentication, and multi-factor authentication.

In addition, it supports the encryption of data at rest and in transit, as well as the classification of sensitive data. Azure Synapse Analytics also supports row, column, and object level security with dynamic data masking, as well as network-level security with virtual networks and firewalls. This ensures that when your sensitive business data is processed through Synapse Analytics, it will be protected with the highest level of security.

Protecting data privacy and security with Azure Synapse Analytics

Integration with Power BI

‍Azure Synapse Analytics integrates directly with Microsoft Power BI, which offers dashboards and visualizations with a robust set of analytical and reporting capabilities. Using Synapse Studio, data analysts can easily analyze data and generate dashboards that provide useful business insights.

Low-code approach

Azure Synapse Analytics offers numerous advantages to data engineers, simplifying and accelerating the development and management of data warehousing and analytics solutions. With visual tools and drag-and-drop interfaces, data engineers can design and implement complex workflows without the need to write detailed code, reducing development time and minimizing errors.

Common Azure Synapse Analytics use cases

By virtue of its functionality, Azure Synapse Analytics can be used in a wide variety of scenarios that require the rapid and precise processing of the data that is produced. Let’s see some of the most common and important ones below to get an even clearer idea of the versatility and usefulness of the service in real settings.

Predicting trends

One of the most significant applications of Azure Synapse Analytics lies in its ability to centralize data from various sales channels for resellers.

Thanks to the service, resellers can seamlessly integrate data from different sources, eliminating data silos and allowing a complete view of their business operations. In the meantime, the tool can also help clean, process, and review this consolidated data.

Omnichannel integration and data analysis

By offering a unified approach to data management, Microsoft Azure Synapse Analytics allows resellers to obtain more accurate and actionable insights from data. For example, by analyzing past customer purchases, browsing habits, and preferences, retailers can better understand their target audience.

This helps retailers create tailored services and improve the relevance of marketing efforts and offers. The result is an improved business strategy and customer experience that foster loyalty, encourage repeat purchases, and promote long-term growth for the retail business.

Improved visibility and performance monitoring

Improving the performance monitoring process is another highly impactful use case for Azure Synapse Analytics. With the ability to provide users with real-time visibility into inventory levels and sales trends, this powerful analytics platform enables the sharing of accurate data that helps manufacturers establish effective collaboration with their customers.

This increased transparency and accuracy of data allows manufacturers to make well-informed decisions regarding production, replenishment and logistics. Meanwhile, customers can benefit from the manufacturer’s improved demand forecasts to minimize cases of stock runs out and lost business opportunities.

On the supply chain front, Azure Synapse Analytics provides suppliers with advanced analytics capabilities that allow them to gain deeper insights into the performance of their supply chain. By analyzing critical data points such as delivery times and order fulfillment, manufacturers can identify potential areas for improvement and implement targeted strategies to optimize their supply chain operations.

This optimization of the supply chain allows manufacturers to be more competitive in the market, responding quickly to changing needs and offering superior customer service.

Fraud detection

Microsoft Azure Synapse Analytics excels in its ability to assist users in detecting fraud, making it a valuable tool for financial institutions. With its robust set of tools and capabilities, Azure Synapse Analytics allows users to effectively analyze vast volumes of data and apply advanced fraud detection algorithms, resulting in actionable insights.

One of the distinctive features of Azure Synapse Analytics is its support for continuous monitoring of transactional activity on accounts and devices in real time. This real-time monitoring capability allows users to quickly identify any suspicious or fraudulent behavior, allowing them to take immediate mitigation actions.

In this way, financial institutions can minimize the risk of financial losses and safeguard their reputation, while obtaining the assistance necessary to meet regulatory compliance requirements and implement effective governance practices.

Azure Synapse architecture

Synapse of Microsoft Azure is a platform that is part of the landscape of Online Analytical Processing (OLAP) applications, generally used to store and process large volumes of data collected from various sources, which can be transformed and/or modeled in the OLAP repository. Subsequently, large datasets are aggregated for ad hoc reports and analytical use cases.

Synapse is an evolution of Azure SQL Data Warehouse, a cloud-based relational database, with Massively Parallel Processing (or MPP, a form of data processing that involves the use of multiple processors to perform parallel computational tasks) and horizontal scalability, designed to process and store large volumes of data within the Microsoft Azure cloud platform.

The platform supports different languages such as SQL, Python, .NET, Java, Scala and R and supports two types of analytical runtimes, SQL and Spark, which can process data in batch, streaming and interactive mode and is also integrated with numerous other Azure data services, such as Azure Data Catalog, Azure Lake Storage, Azure Databricks, Azure HDInsight, Azure Machine Learning and Power BI.

To better understand its architecture, let’s look at its fundamental pillars in a little more detail.

Azure Synapse Studio

Synapse Studio is a web-based SaaS tool that allows developers to work with every aspect of the platform from a single console. This is essentially our dashboard.

In the development cycle of an analytical solution using Synapse, you generally start by creating a workspace and launching this tool that provides access to the various Synapse functionalities such as importing data through import mechanisms or data pipelines, creating data flows, exploring data through notebooks, analyzing data with Spark jobs or SQL scripts, and finally visualizing data for reporting purposes and creating dashboards.

This tool also provides functionality for creating artifacts, debugging code, optimizing performance by analyzing metrics, integrating with CI/CD tools, and much more.

Overview of Azure Synapse Studio

Data integration tools

There are several tools that can be used to load data into Synapse. However, having an integrated orchestration engine helps reduce dependency and management of instances of separate tools and data pipelines.

Synapse includes an integrated orchestration engine identical to that of Azure Data Factory to create data pipelines and rich data transformation capabilities directly within the Synapse workspace itself.

Key features include support for more than 90 data sources, including nearly 15 Azure-based data sources, 26 open-source and cross-cloud data warehouses and databases, 6 file-based data sources, 3 NoSQL-based data sources, 28 services and apps that can act as data providers, and 4 generic protocols, including ODBC and REST. Pipelines can be created using integrated models from Synapse Studio to integrate data from various sources.

Synapse SQL Pools

Synapse SQL is Azure Synapse’s T-SQL-based analysis engine, designed for high-performance manipulation of structured data. This functionality provides the same data warehousing characteristics that were available in previous versions of this service, where a fixed capacity of DWU units (Data Warehouse Units) is allocated to the instance of the data processing service.

What’s new in Synapse is that this engine is now available both in the traditional Provisioned mode and in the new On-Demand mode.

Synapse has introduced a series of improvements to SQL pools starting with workload management capabilities, which allow users to refine the allocation of resources between them. There’s also high-performance COPY functionality for loading data from external storage accounts. Finally, improvements such as the PREDICT clause integrate AI and machine learning, allowing the native evaluation of models directly within Transact-SQL.

SQL on demand is a noteworthy addition because it addresses an issue that, in the past, was an intrinsic compromise in the design of enterprise data systems. The reality of data ecosystems is that demand models vary significantly for a variety of reasons and it is necessary to make difficult architectural decisions based on how much computing capacity it is necessary to allocate to perform analysis and manage auxiliary tasks and where to place data within the architecture. This can lead to situations where management spending can become excessive if you miscalculate by excess and system malfunctions or erratic behavior if you do it by default. All scenarios in which no one would like to find themselves.

On-demand computing addresses these unpredictable workloads and provides another set of tools within the data architecture. Exploring the Data Lake, whether it’s stored as Parquet, Orc or CSV, is now as easy as a right click.

SQL On-Demand also includes new improvements for ELT/Extract, Transform, Load tasks, with features such as delimited text parsers optimized for performance. The raw power and familiarity of SQL Server can be exploited when prototyping queries or performing other ad hoc tasks without having to estimate the anticipated load on the primary computation.

Components of the Azure Synapse SQL architecture

Apache Spark for Azure Synapse

The Apache Spark pools complete the list of Azure Synapse computing options with a powerful MPP engine designed for in-memory Big Data processing, ideal for semi-structured or unstructured workloads, typical of Internet of Things (IoT) and machine learning use cases.

The Synapse implementation is natively available from the Develop hub, where it is possible to create Notebooks directly using an advanced editor. Cognitive services and machine learning are also natively integrated. With a right click in the Data hub, populated by wizards intelligently configured by Linked Services and other configuration artifacts, you can create initial Notebooks that use these services.

Microsoft has put a lot of attention on how to increase productivity, regardless of whether the end user is a data scientist, a data engineer, or a simple business user. Synapse makes it easy to explore basic data through the creation of integrated graphs and aggregations. IntelliSense is integrated into all editors and you can use multiple languages within the same Notebook, including Python (PySpark), C#, Scala or Spark SQL.

Big Data Analysis with Azure Synapse

Azure Synapse vs. Data Factory

The presence of numerous services dedicated to data management and analysis within the Microsoft Azure offer may cause some confusion among users who are approaching Redmond’s cloud computing platform for the first time.

So let’s start to shed some light by outlining the differences between Synapse and another of the most used services within the Azure platform, namely Data Factory, and let’s try to understand in which context it is best to use one of the two.

Azure Synapse and Azure Data Factory are both essential components of Microsoft’s data integration and analysis offerings, but they serve different purposes and address different use cases.

Synapse is a comprehensive analytics service that combines big data and data warehousing. It offers extensive data processing capabilities, supports various programming languages (such as SQL, Python and Spark), and integrates deeply with machine learning tools and business intelligence platforms such as Power BI. This makes it suitable for advanced analytics, real-time data transformations, and large scale data warehousing needs.

Azure Data Factory, on the other hand, is a data integration service focused on ETL processes (extraction, transformation, loading). It allows the creation of data pipelines through a graphical interface, supporting various data sources and destinations. It focuses primarily on code-free solutions for information transformation and integration; however, it doesn’t offer the same breadth of analytical capabilities or programming flexibility as Azure Synapse.

Both services facilitate data integration and transformation, but Azure Synapse provides a more robust platform for complete analysis and data warehousing, exploiting various programming environments and deeper integration with machine learning, while Azure Data Factory, on the other hand, is designed for orchestrating data pipelines and simple and scalable ETL operations.

If you only want to connect and transform data without writing code, you should opt for Azure Data Factory. However, it doesn’t allow you to customize data pipelines beyond its capabilities without code. If, on the other hand, you are looking for greater flexibility and control, Azure Synapse is the best choice.

Automated BI architecture based on Azure Synapse and Azure Data Factory

Azure Synapse pricing: costs and optimization

Azure Synapse offers different pricing models to adapt to different business needs.

The “Pay-as-You-Go” model is ideal for companies with variable or unpredictable workloads. With this model, companies pay only for the resources actually used, with no long-term commitments. This approach allows you to dynamically adapt computing power and storage based on real needs, thus reducing wasted resources and unnecessary costs.

The ‘Serverless’ model, on the other hand, is designed for sporadic or unpredictable workloads, where it is not necessary to maintain a constantly active infrastructure. With the serverless model, companies pay only for the queries executed, based on the amount of data processed. This approach is particularly advantageous for ad hoc analysis scenarios, where calculation requirements can vary significantly.

Finally, the “Reserved Capacity” model is designed for companies with predictable and constant workloads that need regular analysis. By subscribing to a reserved capacity for a period of one or three years, companies can benefit from significant discounts compared to the “Pay-as-You-Go” model. This model offers more predictable cost management and allows you to better plan your IT budget.

As far as variables are concerned, there are several factors to consider that can substantially affect the cost of the service, such as storage, data transfer, backups and monitoring.

The calculation (essentially the processing power), is charged based on the DWU. The higher the amount of DWU allocated, the greater the computing power and, consequently, the costs. The calculation is charged on an hourly basis, meaning that you only pay for the hours the data warehouse is active.

As far as storage is concerned, the costs are related to the volume of data stored and their replication. Azure Synapse charges for storage based on the total volume of data, rounded to the nearest terabyte. This includes not only the primary data but also seven days of incremental snapshots. For example, if a data warehouse contains 1.5 TB of data and has 100 GB of snapshots, the storage costs will be calculated on 2 TB.

The transfer of data involves costs for both entry and egress. Entry costs are generally free, but going out, especially if between different regions, can get expensive. If, for example, we wanted to transfer data from an Azure data center in Europe to one in the United States, this would entail additional costs that can increase rapidly if the volumes of data are high.

Backup and restore operations have variable costs depending on the frequency and duration of the backups you decide to make. The higher the frequency and the longer the period of time the backups are kept, the higher the associated costs will be.

For more detailed pricing information, Microsoft provides a convenient calculation tool (available hither), which allows you to calculate pricing options based on geographical region, currency, type of service and time of employment (calculable in hours or months).

Optimizing the costs of Azure Synapse

Given the amount of variables that can affect the price, it is important for companies to adopt strategies to optimize the use of the resources made available by the service and reduce the expense for their operations. So let’s take a look at the best practices that organizations can use to take full advantage of the power and flexibility of Azure Synapse while keeping an eye on the portfolio.

One of the most effective techniques is to use the pause and resume features, which allow you to temporarily interrupt the data warehouse activity when it is not in use, saving on calculation costs. You can set up an automatic schedule that pauses the data warehouse during non-working hours and reactivates it during peak hours, ensuring that you only pay for the actual time of use.

Scaling resources based on workloads is another extremely useful technique, increasing computing capacity during periods of high activity and reducing it during periods of low activity. Azure Synapse allows you to dynamically scale Data Warehouse Units (DWU), allowing you to adapt resources to current needs and optimize costs.

The use of advanced data compression techniques is also an additional method of resource optimization that can significantly reduce the space needed to store data and, as a result, save significant amounts of money.

Finally, integrated tools such as Azure Synapse Studio and Azure Cost Management and Billing provide a detailed overview of query performance and associated costs, and their use can seriously make a difference to your budget.

The first offers real-time monitoring capabilities that allow us to observe resource usage and query performance, providing detailed insights to optimize the infrastructure. The second, on the other hand, allows you to monitor and manage expenses, identifying areas of overspending and providing recommendations to improve the cost/efficiency ratio.

How to set up Azure Synapse Analytics

Setting up Azure Synapse Analytics is a simple process that can be completed in a few clicks. To demonstrate this, in this section, we offer you a small example with a few simple steps that you can take to experiment by creating a new resource, making it operational in a very short time.

1. Access to the Azure portal and create resource

The first step is to navigate the Azure portal and sign in with your Azure account credentials. Once you log in, you will be shown this page:

Azure portal homeLet’s click on the button + Create new resourcelocated on the left side of the screen. This will open the menu Create a resource. Below the search bar we type synapse and, from the options, we click on Azure Synapse Analytics.

Create a Resource page, selecting Azure Synapse Analytics from the search barThen let’s click on the button Create on the page that opens.

Detail of the ASA page and the Create button

2. Configure the resource

Once we click on Create, this will open the menu where we must specify the details to configure Azure Synapse Analytics. First we will have to select the subscription that we want to use. After that, we will have to choose the resource group in which we want to distribute our service.

If you don’t have a resource group, you can create one by clicking Create new. In this tutorial, we specified d4ssynapserg as the name of the resource group, but you can choose any other name for it as long as it’s unique.

Creating a new Resource GroupThe next step is to specify the name for your workspace. In this case we have specified d4ssynapsews as a name but, again, you can choose any other name as long as it is unique. As far as the Region is concerned, we have specified here West Europe, but you can safely choose the region closest to you.

Sotto Select Data Lake Storage Gen 2, in the sections Account Nameand File system name, provide a unique name in both of them again.

In this particular case we have specified as a name d4ssynapsedatagenfor the account name and d4ssynapsefn as the name of the file system.

Creating a new workspaceLet’s keep the default option for Assign the Storage Blob Data Contributor role to me on the account box Data Lake Storage Gen2, and click Review + Create.

Blunt box detailThis will open a page where we will see the message”Validation Succeeded”, review our configurations and go back to modify them, if necessary. You can also see the estimated cost per month in the currency chosen for payment.

Validation message and summary of information

3. Deploy, Manage, and Delete

Once we are satisfied with the configurations above, we will simply have to click on the button Create to distribute our database. The deployment may take a few minutes and the distribution panel will show the status.

Resource deployment panelLet’s click on Go to resource group to open the next page, where we can view information about the Synapse Analytics resource group we just created, such as the associated workspace and storage account.

Now we can open Synapse Studio by clicking on the Synapse workspace and then selecting Open In the box Open Synapse Studio.

By completing the steps above, Synapse Analytics Studio will open, as shown below. On the left side, you can explore the tabs for date, Develop, Integrate, Monitorand Manage.

Synapse Studio home page

Conclusions

The ability to analyze all the data generated by your business processes and to generate useful insights to improve your company’s strategic decisions is now fundamental in the contemporary landscape. But with such a large volume of data generated every day, all stored in various disconnected data warehouses and data lakes, actually exploiting your data is easier said than done.

Azure Synapse Analytics is proposed as a solid answer to all these problems, offering a data analysis platform that unifies all the data stored in your company’s systems and provides a unique and intuitive user interface, well suited to any data scientist who wants to focus on creating models and insights on data, without having to worry about the infrastructure.

It is easy to set up, easy to use and can generate good models that can be used immediately, with a perfect balance between speed, efficiency and precision. Why not try it, then and see if it is also the answer to your needs?

Read the case study: Azure Synapse Analytics for Healthcare: advanced clinical and administrative performance monitoring

FAQ on Azure Synapse Analytics

What is Azure Synapse Analytics?Azure Synapse Analytics is a comprehensive data analytics service that integrates big data processing, data warehousing, and data integration on a single platform. It allows users to query both relational and non-relational data using serverless or provisioned resources.

How does Azure Synapse Analytics handle big data?Azure Synapse Analytics integrates data from various sources like data lakes and warehouses, offering both serverless on-demand queries and dedicated resources. It simplifies big data analysis by combining multiple services such as Apache Spark and Azure Data Factory.

What are the benefits of Azure Synapse Analytics?Azure Synapse Analytics offers centralized data management, enhanced performance with parallel processing, and seamless integration with tools like Power BI and Azure Machine Learning. It also provides advanced security features and cost-efficient data handling with its consumption-based pricing model.

How does Azure Synapse Analytics support machine learning?Azure Synapse Analytics integrates with Azure Machine Learning and Apache Spark, enabling users to train and apply machine learning models directly within the platform. This supports advanced analytics like forecasting and predictive modeling.

What are common use cases for Azure Synapse Analytics?Azure Synapse Analytics is widely used for trend prediction, omnichannel data integration, supply chain optimization, and fraud detection. Its versatility makes it valuable for industries like retail, finance, and manufacturing.

How does Azure Synapse Analytics ensure security?Azure Synapse Analytics offers robust security with features such as encryption, multi-factor authentication, role-based access control, and compliance with major regulations like HIPAA and ISO. It also supports row-level and object-level security with dynamic data masking.

What languages does Azure Synapse Analytics support?Azure Synapse Analytics supports multiple programming languages such as T-SQL, Python, Scala, Spark SQL, and .Net, allowing flexibility in data query and manipulation based on user preferences.

How can Azure Synapse Analytics integrate with Power BI?Azure Synapse Analytics integrates seamlessly with Power BI, enabling data visualization and dashboard creation directly within Synapse Studio. This helps transform complex datasets into actionable insights.

How is Azure Synapse Analytics priced?Azure Synapse Analytics follows a consumption-based pricing model for serverless SQL queries, where users only pay for the data processed. Dedicated resources, like SQL pools, are priced based on performance and can be scaled or paused to control costs.

How do you set up Azure Synapse Analytics?Setting up Azure Synapse Analytics is straightforward through the Azure portal. Users need to create a resource, configure the resource group, select storage, and deploy the Synapse workspace. Synapse Studio is then used for data management and analytics.