Azure Synapse Analytics Blog articles

Preview: Azure Synapse Runtime for Apache Spark 3.5

ArshadAliTMMBA — Mon, 02 Jun 2025 16:00:00 GMT

We’re thrilled to announce that we have made Azure Synapse Runtime for Apache Spark 3.5 for our Azure Synapse Spark customers in preview, while they get ready and prepare for migrating to Microsoft Fabric Spark.

What Does This Mean for You?

You can now create Azure Synapse Runtime for Apache Spark 3.5. The essential changes include features which come from upgrading Apache Spark to version 3.5 and Delta Lake 3.2. Please review the official release notes for Apache Spark 3.5 to check the complete list of fixes and features. In addition, review the migration guidelines between Spark 3.4 and 3.5 to assess potential changes to your applications, jobs and notebooks.

For additional details check Azure Synapse Runtime for Apache Spark 3.5 documentation.

What is next?

We offer Azure Synapse Runtime for Apache Spark 3.5 to our Azure Synapse Spark customers. However, we strongly recommend that customers plan to migrate to Microsoft Fabric Spark to benefit from the latest innovations and optimizations exclusive to Microsoft Fabric Spark. For example, the Native Execution Engine (NEE) significantly enhances query performance at no additional cost. Starter pools allow the creation of a Spark session within seconds, unified security in the lakehouse enables the definition of RLS (Row-Level Security) and CLS (Column-Level Security) for objects in the lakehouse. Additionally, newly announced Materialized Views and many other features are available.

Enhancing Team Collaboration in Azure Synapse Analytics using a Git Branching Strategy – Part 2 of 3

RuiCunha — Tue, 27 May 2025 16:00:00 GMT

Introduction

In the first part of this blog series, we introduced a Git branching strategy designed to enhance collaboration within Azure Synapse Studio. By enabling multiple teams to work in parallel within a shared Synapse workspace, this approach can accelerate not only the development cycle of Synapse code but also the entire Synapse CI/CD flow.

In this second part of this blog series, we take a practical step forward by demonstrating how to implement a CI/CD flow that supports this Git branching strategy. This flow will help streamline the Synapse code development cycle for our Data Engineering and Data Science teams, accelerating code releases across different environments without interfering with their respective work.
Although this article series demonstrates a scenario where different teams working on separate projects share the same Synapse development workspace, you can adapt this CI/CD flow to fit your own Git branching strategy.

Whether you're managing a single team or coordinating across multiple projects, this guide will help you build a scalable and efficient deployment workflow tailored for Azure Synapse Analytics.

Prerequisites:

- An Azure DevOps project.
- An ability to run pipelines on Microsoft-hosted agents. You can either purchase a parallel job or you can request a free tier.
- Basic knowledge of YAML and Azure Pipelines. For more information, see Create your first pipeline.
- Permissions:
To add environments, the Creator role for environments in your project. By default, members of the Build Administrators, Release Administrators, and Project Administrators groups can also create environments.
- The appropriate assigned user roles to create, view, use, or manage a service connection. For more information, see Service connection permissions.

To learn more about setting up Azure DevOps Environments for pipelines and setting up Service Connections, please refer to these documents:
Create and target Azure DevOps environments for pipelines - Azure Pipelines | Microsoft Learn

Service connections - Azure Pipelines | Microsoft Learn

Defining Azure DevOps Environments

An environment represents a logical target where your pipeline deploys software. Common environment names include Dev, Test, QA, Staging, and Production. You can learn more about environments here.

Since our Git branching strategy is based on environment-specific branches, we’ll leverage this Azure DevOps environments feature to monitor and track Synapse code deployments by environment/team. From a security perspective, this also ensures that pipeline execution can be authorized and approved by specific users per environment.

⚠️ Note: Azure DevOps environments are not available in Classic pipelines. For Classic pipelines, Release Stages offer similar functionality.

Let’s begin by creating the necessary Azure DevOps environments for our Synapse CI/CD flow.

In this first step, we’ll create four environments: DEV, UAT, PRD, and EMPTY. Each environment will be associated with its corresponding environment branch. The purpose of the EMPTY environment is to ensure that the deployment job only runs when the branch is recognized as valid (e.g., environments/<team>/dev, environments/<team>/uat, or environments/<team>/prd). Even if someone modifies the trigger or manually runs the pipeline from another branch, the job will be automatically skipped.

To create these environments, follow these steps:

Sign in to your Azure DevOps organization at https://dev.azure.com/{yourorganization} and open your project.
Go to Pipelines > Environments > Create environment.

Figure 1: How to create your pipeline Environments

Once you’ve created all four environments, your environment list should resemble the one shown in the figure below.

Figure 2: All environments for this tutorial created

To add an extra layer of security to each of these environments, we can configure an approval step and specify the user(s) authorized to approve pipeline execution in each environment.

After selecting your environment, go to the Approvals and checks tab, then click the + icon to add a new check.

Figure 3: Adding approvers to your pipeline environments

Select Approvals, and then select Next.

Add users or groups as your designated Approvers, and, if desired, provide instructions for the approvers. Specify if you want to permit or restrict approvers from approving their own runs, and specify your desired Timeout. If approvals aren't completed within the specified Timeout, the stage is marked as skipped.

Figure 4: Adding an approver to your pipeline environment

Creating the Pipeline for Synapse Code Deployment

With the Azure DevOps environments defined, we can now create the pipeline that will drive the CI/CD flow.

From the left Navigation menu, Go to "Pipelines" and select "New pipeline"

Note: The following images correspond to the native Azure DevOps pipeline configuration experience.

Since we are using Azure DevOps, we will select the first option presented.

Figure 5: Selecting your git provider

Select your repository

Figure 6: Selecting your repository

and then select the “Starter pipeline” option

Figure 7: Configuring your pipeline

Now it’s time to define the code that our pipeline will use to deploy Synapse code to the corresponding environments.

Configuring your Pipeline

Figure 8: Reviewing your YAML pipeline

Replace the existing sample code with this code below.

trigger:

- environments/data_eng/dev
- environments/data_eng/uat
- environments/data_eng/prd
- environments/data_sci/dev
- environments/data_sci/uat
- environments/data_sci/prd

variables:
- name: workspaceEnv
  ${{ if endsWith(variables['Build.SourceBranch'], '/uat') }}:
    value: 'UAT'
  ${{ elseif endsWith(variables['Build.SourceBranch'], '/dev') }}:
    value: 'DEV'
  ${{ elseif endsWith(variables['Build.SourceBranch'], '/prd') }}:
    value: 'PRD'
  ${{ else }}:
    value: 'EMPTY'
jobs:
- deployment: deploy_workspace
  displayName: Deploying to ${{ variables.workspaceEnv }}
  environment: $(workspaceEnv)
  condition: and(succeeded(), not(eq(variables['workspaceEnv'], 'EMPTY')))
  strategy:
    runOnce:
      deploy:
        steps:
        - checkout: self
        - template: /adopipeline/deploy_template.yml
          parameters:
            serviceConnection: 'Service Connection name goes here'
            resourceGroup: 'Target workspace resource group name goes here'
            ${{ if endsWith(variables['Build.SourceBranch'], '/dev') }}:
              workspace: 'Development workspace name goes here'
            ${{ elseif endsWith(variables['Build.SourceBranch'], '/uat') }}:
              workspace: ' UAT workspace name goes here '
            ${{ elseif endsWith(variables['Build.SourceBranch'], '/prd') }}:
              workspace: ' Production workspace name goes here '
            ${{ else }}:
              workspace: ''

⚠️Important notes:

In case you don’t have a service connection created yet, you can refer to this document ARM service connection and create one.

Because the Synapse Workspace Deployment task does not support the “Workload Identity Federation” credential type, you must select the “Secret” credential type.

Figure 9: Setting the credential type for your Azure Service Connection

In the YAML pipeline provided above, you should replace the highlighted placeholder, with your service connection name.

Figure 10: Configuring the serviceConnection parameter

The service connection is the resource used to provide the credentials on the task execution allowing it to connect to the workspace for deployment. In our example, the same service connection is allowing access to all of our workspaces. You may need to provide a different service connection depending on the workspace and the pipeline will need to be adjusted for this use case. Same logic should apply to the resourceGroup parameter. If your workspaces belong to different resource groups, you can adapt the if condition in the parameters section, including the resource group parameter on each if clause to assign a different value to the resourceGroup parameter depending on the environment branch that is triggering the YAML pipeline.

Creating a service connection in Azure DevOps, using automatic App registration, will trigger the provisioning of a new service principal in your Microsoft Entra ID.
Before starting the CI/CD flow to promote Synapse code across different workspaces, this service principal must be granted the appropriate Synapse RBAC role — either Synapse Administrator or Synapse Artifact Publisher, depending on whether your Synapse deployment task is configured to deploy Managed Private Endpoints.

How can you identify the service principal associated with the service connection?
In your DevOps project settings, go to Service Connections and select your service connection. On the Overview tab, click the "Manage App registration" link. This will take you to the Azure Portal, specifically to Microsoft Entra ID, where you can copy details such as the display name of the service principal.

Figure 11: Service connection details - selecting the Manage App registration

Then, in the destination Synapse Studio environment, you can assign the appropriate Synapse RBAC role to this service principal.
If you skip this step, the Synapse code deployment will fail with an authorization error (HTTP 403 – Forbidden).

Figure 12: Granting Synapse RBAC to the SPN associated to your DevOps service connection

Once you're done, don’t forget to rename your pipeline and save it in your preferred branch location.

In this example, I’m saving the pipeline.yaml file inside the “adopipeline” folder.

After renaming the file, save your pipeline — but do not run it yet.

Figure 13: Saving your YAML pipeline

Configuring the Synapse Deployment Task

You may have noticed that this pipeline uses another file as a template, named deploy_template.yml. Templates allow us to create steps, jobs, stages and other resources that we can re-use across multiple pipelines for easier management of shared pipeline components.

Let’s go ahead and create that file.

Figure 14: Saving your template files in your branch

We’ll start by adding the following content to our new file:

parameters:
- name: workspace
  type: string
- name: resourceGroup
  type: string
- name: serviceConnection
  type: string

steps:
- task: AzureSynapseWorkspace.synapsecicd-deploy.synapse-deploy.Synapse workspace displayName: 'Synpase deployment task for workspace: ${{ parameters.workspace }}'

  inputs:
    operation: validateDeploy
    ArtifactsFolder: '$(System.DefaultWorkingDirectory)/workspace'
    azureSubscription: '${{ parameters.serviceConnection }}'
    ResourceGroupName: '${{ parameters.resourceGroup }}'
    TargetWorkspaceName: '${{ parameters.workspace }}'
  condition: and(succeeded(), not(eq(length('${{ parameters.workspace }}'), 0)))

This template is responsible for adding the Synapse Workspace Deployment Task, which handles deploying Synapse code to the target environment.

We configure this task using the “Validate and Deploy” operation — a key enabler of our Git branching strategy. It allows Synapse code to be deployed from any user branch, not just the publish branch.

Previously, Synapse users could only deploy code that existed in the publish branch. This meant they had to manually publish their changes in Synapse Studio to ensure those changes were reflected in the ARM templates generated in that branch. With the new “Validate and Deploy” operation, users can now automate this publishing process — as described in [this article].

⚠️ Important note about the ArtifactsFolder input:

The specified path must match the Root Folder defined in the Git repository information associated with your Synapse Workspace.

Figure 15: The Git configuration in your Development Synapse workspace

Once this file is saved, your Azure DevOps setup is complete and ready to support the development and promotion of Synapse code across multiple environments leveraging our Git branching strategy!

In the next and final blog post of this series, we’ll walk through an end-to-end demonstration of the Synapse CI/CD flow using our Git branching strategy.

Conclusion

In this second part of our blog series, we demonstrated how to implement a CI/CD flow for Azure Synapse Analytics that fully leverages our Git branching strategy.

With this CI/CD flow in place, teams are now equipped to develop, test, and promote Synapse artifacts across environments in a streamlined, secure, and automated manner.

In the final post of this series, we’ll walk through a complete end-to-end demonstration of this CI/CD flow in action — showcasing how our Git branching strategy empowers collaborative work in Synapse Studio and turbo-charges your code release cycles.

Enhancing Team Collaboration in Azure Synapse Analytics using a Git Branching Strategy – Part 1 of 3

RuiCunha — Thu, 15 May 2025 16:00:00 GMT

Introduction

Over the past few years of working with numerous Synapse Studio users, many have asked how to make the most of collaborative work in Synapse Studio —especially in complex development scenarios where developers work on different projects in parallel within a single Synapse workspace.

Based on our experience and internal feedback from other Synapse experts, our general recommendation is that each development team or project should have its own Synapse workspace. This approach is particularly effective when the maturity level of the teams—both in Synapse and Git, is still developing. In such cases, having separate workspaces simplifies the CI/CD journey.

However, in scenarios where teams demonstrate greater maturity (especially in Git) and the number or complexity of Synapse projects is relatively low, it is possible for multiple teams and projects to coexist within a single Synapse development workspace. In these cases, evaluating your team’s maturity in both Synapse and Git is crucial. Teams must honestly assess their comfort level with these technologies. For example, expecting success from teams that are just beginning their Synapse journey and have limited Git experience—or planning to develop more than five projects in parallel within a single workspace—would likely lead to challenges. Managing even a single project in Synapse can be complex; doing so for multiple projects without sufficient expertise in both Synapse and Git could be a recipe for disaster.

That said, the main objective of this article is to demonstrate how a simple Git branching strategy can enhance collaborative work in Synapse Studio, enabling different projects to be developed in parallel within a single Synapse workspace. This guide can help teams at the beginning of their Synapse journey assess their current maturity level (in both Synapse and Git) and understand what level they should aim for to adopt this approach confidently. For teams with a reasonable level of maturity, this article can help validate whether this strategy can further improve their collaborative efforts in Synapse.

This is the first of three articles, where we’ll show how to implement a simple branching strategy that allows two development teams working on separate projects to share a single Synapse workspace. The strategy supports isolated code promotion through various environments without interfering with each team’s work. While we use Azure DevOps as our Git provider throghout these articles, the approach is also applicable to GitHub GitHub.

Start elevating your collaborative work in Synapse Studio, by implementing a simple and effictive Git branching strategy

Let’s begin by outlining our scenario: two development teams—Data Engineering and Data Science—are about to start their projects in Synapse. Both teams have substantial experience with Synapse and Git. Together, they’ve agreed on a simple Git branching strategy that will enable them to collaborate effectively in Synapse Studio while supporting a CI/CD flow designed to automate the promotion of their code from the development environment to higher environments.

The Git branching strategy involves creating feature branches and environment branches, organized by team, as illustrated in the following diagram.

Figure 1: A Simple Git Branching Strategy

Important note on governance of the branching strategy:
The first branches that should be created are the environment branches. Once these are in place, any time a developer needs to create a feature branch, it must always be based on the production environment branch of their respective team. In this strategy, the production branch serves as the team’s collaboration branch, ensuring consistency and alignment across development efforts.

Figure 2: Creating a Feature Branch Based on the Production Environment Branch

In the initial phase of implementing this strategy, environment branches can be created using the "Branches" feature in Azure DevOps, or locally in a developer’s repository and then pushed to the remote repository. Alternatively, teams can use the branch selector functionality within Synapse Studio. The team should choose the method they are most comfortable with.

Below is an example of the branch structure that will be developed throughout this article:

Figure 3: Example of Branching Structure Visualization from DevOps

Start at the feature branch level...

With the branching strategy defined, we can now demonstrate how the two teams will carry out their respective developments within a single Synapse development workspace.

Let’s begin with Mary from the Data Engineering team, who will develop a new pipeline. She creates this pipeline in her feature branch: features/data_eng/mary/mktetl.

Figure 4: Creating a Pipeline in a Feature Branch of the Data Engineering Team

Meanwhile, Anna, a developer from the Data Science team, also begins working on a new feature for the Data Science project.

Figure 5: Creating a Notebook in a Feature Branch of the Data Science Team

Both teams are ready to start their unit testing independently, at different times, and with distinct code executions.

This is where the Environment Branches come into play.

…and end at the Environment branch level!

After completing the development of her feature, Anna promotes her changes to the development environment. It’s important to note that the code has only been committed to Git—it has not been published to Live Mode yet.

You might wonder why Anna didn’t simply use the Publish button in Synapse Studio to push her changes live. That would be a valid question—if both teams were sharing a single collaboration branch (as described here). In such a setup, the collaboration branch would contain code from both the Data Engineering and Data Science teams. However, that’s not the goal of our branching strategy.

Our strategy is designed to ensure segregation at both the source control and CI/CD levels for all teams working within a shared Synapse development workspace. Instead of using a single collaboration branch for everyone, each team uses its own production environment branch as its collaboration branch. In this context, using the Publish button in Synapse Studio is not appropriate.

Instead, we leverage a feature of the Synapse public extension—specifically, the the Synapse Workspace Deployment Task in Azure DevOps (or the GitHub Action for Synapse Workspace Artifacts Deployment, if using GitHub). This extension allows us to publish Synapse artifacts to any environment from any user branch—in this case, from the environment branches.

Therefore, when configuring Git for your Synapse development workspace under this strategy, you can set the collaboration branch to any placeholder (e.g., main, master, or develop), as it will be ignored. This approach ensures that each team maintains code isolation throughout the development and deployment lifecycle.

It’s important to understand that the decision not to use the Publish functionality in Synapse Studio is intentional and directly tied to our strategy of supporting multiple teams and multiple projects within a single Synapse workspace.

Figure 6: Data Science Team: Creating a Pull Request from the Feature Branch to an Environment Branch in Synapse Studio

Figure 7: Data Science Team: Configuring the Pull Request in DevOps, Indicating the Source (Feature Branch) and Destination (DEV Environment Branch)

Meanwhile, Mary, our Data Engineer, has also completed the development of her feature and is now ready to publish her pipeline to the development environment.

Figure 8: Data Engineering Team: Creating a Pull Request from the Feature Branch to an Environment Branch in Synapse Studio

Figure 9: Data Engineering Team: Configuring the Pull Request in DevOps, Indicating the Source (Feature Branch) and Destination (DEV Environment Branch)

Conclusion

In conclusion, this article has demonstrated how different development teams can effectively leverage a Git branching strategy to develop their code within a single Synapse development workspace. By creating both feature branches and environment branches, the teams are able to work in parallel without interfering with each other’s development processes. This approach ensures proper isolation and enables smooth code promotion across environments.

As we move forward, the next article in this series will explore how this strategy helps both teams accelerate their development lifecycle and streamline the CI/CD flow in Synapse.

Runtime 1.1, based on Apache Spark 3.3, will be retired and disabled as of March 31, 2025

JeanC750 — Wed, 26 Mar 2025 05:32:44 GMT

Microsoft Fabric Runtime 1.1 will be retired and disabled as of March 2025.  End of support for Microsoft Fabric Runtime 1.1 was announced July 12, 2024. We recommend that you upgrade your Fabric workspace and environments to use Runtime 1.3 (Apache Spark 3.5 and Delta Lake 3.2). This latest Fabric Runtime 1.3 also offers a Native Execution Engine to boost performance and flexibility at no additional cost. Learn more about Native Execution Engine here. For the complete lifecycle and support policies of Apache Spark runtimes in Fabric, refer to Lifecycle of Apache Spark runtimes in Fabric.

We're moving!

ryanmajidi — Mon, 21 Oct 2024 21:23:20 GMT

We’re moving to the Analytics on Azure Tech Community! All new Azure Synapse Analytics content will be published there. In the next few days all existing content will be migrated over.

Thank you, and we look forward to seeing you all at Analytics on Azure!

Upgrade to Azure Synapse runtimes for Apache Spark 3.4 & previous runtimes deprecation

eskot — Thu, 27 Jun 2024 22:38:23 GMT

It is important to stay ahead of the curve and keep services up to date. That's why we encourage all Azure Synapse customers with Apache Spark workloads to migrate to the newest GA version, Azure Synapse Runtime for Apache Spark 3.4. The update brings Apache Spark to version 3.4 and Delta Lake to version 2.4, introduces Mariner as the new operating system, and updates Java from version 8 to 11.

Within a few days/weeks, we are disabling Apache Spark 2.4, 3.1, 3.2 job execution. If you are affected you have already been notified. Using the runtime after EOS date is at one's own risk, and with the agreement and acceptance of the risks that jobs will eventually stop executing. All support tickets will be auto-resolved. Learn more about the Microsoft Lifecycle Policy.

Migrate to the latest GA version of Azure Synapse runtimes for Apache Spark 3.4 before the deprecation and disablement of previous versions.

Please refer to the following article for more information on the lifecycle and supportability of our runtimes: Azure Synapse runtimes.

Go to Synapse runtime for Apache Spark lifecycle and supportability - Azure Synapse Analytics | Microsoft Learn and Migration Guide: Spark Core - Spark 3.4.1 Documentation (apache.org) for more details on how to migrate and how to change Apache Spark-based runtime in Azure Synapse Analytics.

ADF\Synapse Analytics - Replace Columns names using Rule based mapping in Mapping data flows

Subashri_Vasu — Fri, 23 Feb 2024 07:06:25 GMT

In real time, the column names from source might not be uniform, some columns will have a space in it, some other columns will not.

For example,

Sales Channel
Item Type,
Region
Country
Unit Price

It is a good practice to replace all the spaces in a column name before doing any transformation for easy handling. This also helps with auto mapping, when the sink column names do not come with spaces!

Select transformation in data flow makes it simpler to automatically detect spaces in column names and then remove them for the rest of the dataflow.

Consider the below source, with the given column names.

Here as we can see, few columns have spaces, few columns like Region and Country do not have spaces in it.

Using the below configuration in select transformation, we can get rid of the spaces in the column names with a simple expression.

In the Input columns, Click on Add mapping button, and choose Rule-based Mapping.

Then give the below expression:

on Source1's column: true()

on Name as column: replace($$,' ','')

What it does?

It will return true() for all the columns which have ' ' (space) in it and replace it with '' (no space).

Upon data preview, we get to see the below result,

As we are seeing here, all the columns with spaces are coming without spaces in between.

If not for the Rule based mapping, one would have to manually remove space from all the columns. It would be a nightmare if the number of columns are more! Thanks to rule-based mapping!

Interpreting Script activity output json with Azure Data Factory\Synapse analytics

Subashri_Vasu — Thu, 01 Feb 2024 06:20:20 GMT

Script activity in Azure Data Factory\ Synapse analytics is very helpful to run queries against data sources mentioned here in this document.

When we use two or more queries in the script activity, it is important to understand the output json of script activity to write expressions based on the output in the subsequent activities.

Consider the below Pipeline design:

We have two select queries as follows in script activity, and each of which will give a resultSet.

select top 2 * from tbl_adf;

Select * from tbladf

When debugged, it will give output as below.

{ "resultSetCount": 2, "recordsAffected": 0, "resultSets": [ { "rowCount": 2, "rows": [ { "Order Date": null, "Order ID": "271735084", "Ship Date": "2020-01-24T00:00:00Z", "Region": "AUSTRALIA AND OCEANIA", "Unit Cost": "524.96", "Total Revenue": "849829.05" }, { "Order Date": null, "Order ID": "252502572", "Ship Date": "2018-12-29T00:00:00Z", "Region": "MIDDLE EAST AND NORTH AFRICA", "Unit Cost": "152.58", "Total Revenue": "1522290.66" } ] }, { "rowCount": 1, "rows": [ { "lastmodified": "2024-01-12T08:29:48.1508043Z" } ] } ]

So, as per line #2, resultSet count =2. It is because, we have two select queries in the script activity.

In case we want to get the Total Revenue value from Row#1, we have to write below expression.

@activity('Script1_copy1').output.resultSets[0].rows[0]['Total Revenue']

where, resultSets[0]: First select query result

rows[0]: first row in resultSets[0]

Same way, if we want to get the Total Revenue value from Row#2, we have to write below expression.

@activity('Script1_copy1').output.resultSets[0].rows[1]['Total Revenue']

where, resultSets[0]: First select query result

rows[1]: second row in resultSets[0]

And, the below expression gets the rowcount from each resultset.

@activity('Script1_copy1').output.resultSets[0].RowCount

@activity('Script1_copy1').output.resultSets[1].RowCount

So, by understanding the structure of output json, we are able to write expressions to access individual elements of the output of any activity in ADF\Synapse.

Synapse Connectivity Series Part #4 - Advanced network troubleshooting and network trace analysis

FonsecaSergio — Tue, 14 Nov 2023 16:00:00 GMT

Continuing the series of this blog posts I would like to go more advanced on troubleshooting connectivity issues. I would like to thank also Salam Al Hasan (Salamalhasan) that helped me with some real case scenarios from our customers.

This is part 4 of a series related to Synapse Connectivity - check out the other blog articles:

In this post I will speak about how to capture a network trace and how to do some basic troubleshooting using Wireshark to investigate connection and disconnection issues, not limited to samples error messages below:

An existing connection was forcibly closed by the remote host, The specified network name is no longer available, The semaphore timeout period has expired.
Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=5895; handshake=29;
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
A connection was successfully established with the server, but then an error occurred during the login process
Failed to copy to SQL Data Warehouse from blob storage. A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.) An existing connection was forcibly closed by the remote host

Index

0 - Scoping
1 - How to CAPTURE a network trace
- 1.1 - Input
  - 1.1.1 - Capture filter
- 1.2 - Output
  - 1.2.1 - Reproducible error?
  - 1.2.2 - Transient error?
- 1.3 - Options
2 - Capture Alternatives
- 2.1 - NETSH - Native on Windows
- 2.2 - TCPDUMP - Native on Linux / MacOs
3 - Tool configuration - Wireshark
4 - Analysis
- 4.1 - SCENARIO 1 - Success path
  - 4.1.1 - Simple sample on Wireshark
  - 4.1.2 - Direction of the packages
  - 4.1.3 - Redirect
- 4.2 - SCENARIO 2 - The server was not found or was not accessible
- 4.3 - SCENARIO 3 - Redirect port 11xxx range closed
- 4.4 - SCENARIO 4 - Connection timeout (Middle connection) / PreLogin handshake
  - 4.4.1 - SCENARIO 4.1 - TLS Blocked
  - 4.4.2 - SCENARIO 4.2 - Long connection
  - 4.4.3 - SCENARIO 4.3 - Azure firewall
- 4.5 - SCENARIO 5 - Connection Dropped

0 - Scoping

Before we can even start thing about a connection or disconnection issue we need to better understand the scenario and we need to explore the following questions:

1. Which tool is being utilized?
2. Is the client machine situated on an Azure VM or an on-premises machine?
3. What type of firewall is in use: Azure Firewall or a third-party application?
4. What is the operating system of the host machine for the client?
5. Is the connection type private or public (Using Private endpoints)?
6. What type of user is the customer employing (SQL User, AAD User)?
7. Are connection retry policies in place?

- Retry logic for transient errors

- IMPORTANT: Transient failure are a normal occurrence and should be expected from time to time. They can occur for many reasons such as balance and deployments in the region your server is in, network issues. A transient failure can takes some seconds or minutes, when this takes more time we can look to see if there was a larger underlying reason.

8. What is the timeout value set for the connection?
9. Are you using OLEDB, ODBC, JDBC, other? Neglecting to use the latest version can sometimes lead to disconnections.

1 - How to CAPTURE a network trace

First we need to know how to start a capture and what tools to use. The tool selection depend on your personal taste and the OS being used. Remember that "The best tool is always the one you know".

First will explain my preferred, Wireshark. It is a free tool that work on multiple platforms what makes easy to support different kind of clients.

When you open it, you could just start collecting the trace. My suggestion is always start with the "config data capture settings" button.

1.1 - Input

First you need to select the network cards that will be monitored. The selection of network cards is just the blue or white rows that you select with shift and/or control commands, as you can see on image above.

Notice in this interface there are some checkboxes (Promiscuous Mode), ignore them. They are not selection box, you can actually unselect them.
IMPORTANT: If you select default (first card) you may not collect anything useful. Make sure you select the correct interface. Or just select all if not sure

1.1.1 - Capture filter

In the bottom you can also filter the capture, ideally you should not filter, but if need to save resources / space in disk might be useful, so you capture traffic only of specific ports and IP addresses. You can add as sample

SQL port 1433 + 11xxx range needed for redirect + 53 for DNS requests

tcp portrange 11000-12000 or tcp port 1433 or tcp port 53

1.2 - Output

This output area will depend if you can easily repro the issue, or if it is more like a transient issue.

1.2.1 - Reproducible error?

If the error you are facing is repeatable, you can capture one single file, repro the issue and stop data collection, like sample below

1.2.2 - Transient error?

If the error is intermittent, you might need to caputure what we call a CIRCULAR network trace capture. That means that trace will be capturing in a loop until you stop the data collection, it will keep as many files of the specified size you define.

Check some important information below:

Do NOT create files over 2GB, will be a problem to open it, instead, increase number of files. Just need more disk
As soon as error happens, try to stop collection or else you might lose telemetry, you can also increase number of files to keep more data
It is hard to estimate how much time each 2GB file can handle. It will depend on volume of data you are sending

1.3 - Options

The last part you could create some stop condition like stop after some time. Keep default options here

Then just click to start collection

2 - Capture Alternatives

Before we start analyzing it, we need to talk about alternatives

2.1 - NETSH - Native on Windows.
- No need to install new tool on environment
- ETL file output does not open on Wireshark nativelly, you need to convert it. Can use application (https://github.com/microsoft/etl2pcapng)
- NETSH Short capture
  - Only 1 file of 2GB
  - Start CMD (Run as Admin)
  - Start the process:
    - ```
    netsh trace start persistent=yes capture=yes tracefile=%temp%\%computername%_nettrace.etl maxsize=2048
```
- Reproduce the issue.
- Stop the process:
  - ```
  netsh trace stop
```
- NETSH Circular capture
  - Only 1 file of 2GB you can get more data truncating packages (packettruncatebytes=512)
  - Start CMD (Run as Admin)
  - Start the process:
    - ```
    netsh trace start capture=yes packettruncatebytes=512 tracefile=%temp%\%computername%_nettrace.etl maxsize=2048 filemode=circular overwrite=yes report=no
```
- Reproduce the issue.
- Stop the process:
  - ```
  netsh trace stop
```

2.2 - TCPDUMP - Native on Linux / MacOs
- TCPDUMP Short capture
  - ```
  tcpdump -n -w /var/tmp/traffic.pcap
```
- TCPDUMP Circular capture
  - 10 files of 2GB
  - ```
  tcpdump -n -w /var/tmp/trace.pcap -W 10 -C 2000
```
- TCPDUMP permission
  - If needed can also run with sudo
  - ```
  SUDO tcpdump -n -w /var/tmp/traffic.pcap
```
- TCPDUMP Filter interface
  - For linux servers, you can use "netstat -i" to list the available INTERFACE devices as well.
  - ```
  sudo netstat -i
```
- Kernel Interface table
  Iface MTU ........
  eth0 9001 ........
- ```
tcpdump -i eth0 -n -w /var/tmp/traffic.pcap
```
- References

3 - Tool configuration - Wireshark

Now that capture is done, we can start looking into the capture, just need to set something on Wireshark to make sure that the interface is ready to analyze and is showing the information needed to analyse it.

Date time format to UTC
- Make sure to set date time format correct, so this way won't exist confusion with timezone (from server side, client side vs client machine timezone). Use UTC

Source and destination ports
- Source and Destination IP might not be enough. To simplify analysis, add also source and destination ports as columns.
- In the package (below), look for the Transmission Control Protocol (TCP) and get Source and Destination ports. Add them as columns. Like image below

4 - Analysis

With the file captured you will use display filters that will depend on the error that you are looking for. The most common used are below

Get SQL Comunication + DNS resolution

(tcp.port in {1433, 11000..11999} or (dns.qry.name contains "database.windows.net") or (dns.qry.name contains "sql.azuresynapse.net"))

Look for SQL connections that were reseted (Disconnection scenarios)
- ```
(tcp.port in {1433, 11000..11999}) and (tcp.flags.reset == 1)
```

Find below some scenarios that will help you get some ideas:

4.1 - SCENARIO 1 - Success path

Let's firs understand the success path. What is expected on a success connection

Ref to Mermaid script used to create above diagram if you want to reuse this on different context

TCP Handshake: This is the first step when a client (for example, your computer) wants to establish a connection with a server (like Synapse). It involves three steps (three-way handshake)
- The client sends a SYN (synchronize) packet to the server.
- The server acknowledges this by sending back a SYN-ACK (synchronize-acknowledge) packet.
- Finally, the client sends an ACK (acknowledge) packet back to the server.
- Now, the connection is established and ready for data transfer.
Prelogin to Synapse: When connecting to Synapse or Azure SQL DB, there’s a pre-login handshake that happens. This is where the client and server agree on certain settings for the connection. If this handshake fails in the middle, you might see pre-login handshake errors as connection to gateway completed, but not able to complete login process.
Key Exchange: After the pre-login handshake, there’s a key exchange process. This is where the client and server agree on a secret key that they’ll use for encrypting and decrypting the data they send to each other. This involves exchanging random numbers and a special number called the Pre-Master Secret . These numbers are combined with additional data permitting client and server to create their shared secret, called the Master Secret. The Master Secret is used by client and server to generate the session keys used for hashing and encryption. Once the session keys are established, the client sends a “Change cipher spec” notification to server to indicate that it will start using the new session keys for hashing and encrypting messages
Data Transfer: Now, client and server can exchange application data over the secured channel they have established.
Idle connection / Keep Alive: Sometimes, a connection between two devices might be open, but no data is being sent. This could be because the user is not performing any action that requires data to be sent or received like a SQL Update command.
Now, if one device doesn’t hear from the other for a long time, it might think that the connection has been lost. To prevent this from happening, devices send small packets called “Keep-Alive” packets. These packets are like a small nudge or a ping saying “Hey, I’m still here!”. They help in maintaining the connection alive even when no actual data is being transferred. Idle connections that keeps for long time (30 min) can still be disconnected
- Microsoft DOC
  - https://learn.microsoft.com/en-us/troubleshoot/azure/synapse-analytics/dedicated-sql/dsql-conn-dropped-connections#connection-policy-configuration-for-gateway-issues
  - "The Azure SQL Database gateway terminates sessions that are idle for more than 30 minutes. This scenario frequently affects pooled, idle connections. For dedicated SQL pool (formerly SQL DW), you can switch the connection policy for your server from proxy to redirect. The redirect setting bypasses the gateway after it's connected. This eliminates the issue."
- Check also post from hugo_sql
  - https://techcommunity.microsoft.com/t5/azure-database-support-blog/azure-sql-idle-sessions-are-killed-after-about-30-minutes/ba-p/3268601
Finish with success: The FIN flag in a TCP packet stands for FINish. It is used to indicate that the sender has finished sending data and wants to terminate the TCP connection. This process is known as the four-way handshake and is used to gracefully terminate a TCP connection. It ensures that both sides have received all the data before the connection is closed

Here’s a simple explanation of how it works:
- When an application is done sending data, it sends a TCP packet with the FIN flag set.
- The receiving side acknowledges this by sending back a packet with an ACK (acknowledgement) flag.
- The receiver then sends its own FIN packet when it’s done sending data.
- Finally, the original sender acknowledges this with another ACK.
Finish without success: In a network, when a device sends a TCP packet to another device, it expects an acknowledgement in return. However, there might be situations where the receiving device cannot process the packet properly or the connection is not valid anymore. In such cases, the receiving device sends a TCP packet with the Reset (RST) flag set. The RST packet is like a message saying “I can’t process this, let’s terminate this connection”. It’s a way for a device to signal that something has gone wrong in the communication process. Even though a reset coming from Server to client does not mean server is down. It could be dropped because of some security reason like taking long time in the login process. Check more info later on scenarios

Here are some reasons why an RST packet might be sent:
- The receiving device was restarted and forgot about the connection.
- The packet was sent to a closed port.
- The packet was unexpected or not recognized by the receiving device.

Additional ref:

4.1.1 - Simple sample on Wireshark

And here we can see a real communication on Wireshark transfer. The numbers on the right match with explanation above

It was a simple SQLCMD command with a wait of 30 seconds before executing the command so we could see a keep alive package also

PS C:> sqlcmd -S Servername.sql.azuresynapse.net -d master -U ******** -P ********
1> select 1
2> go
-----------
1
(1 rows affected)
1> exit

Or could have ended in a bad way, in this scenario, forcing a RST package by killing powershell terminal, so it could not end connection properly

4.1.2 - Direction of the packages

One important thing to notice here is the direction of the packages. Destination of Synapse or Azure SQL DB will always be 1433 and/or 11000-11999 in case of redirect, and source port will be a big port number, this is called ephemeral ports.

Ephemeral ports are temporary communication points used for internet connections. They’re like temporary phone numbers that your computer uses to talk to other computers on the internet.
When you visit a website or use an app, your computer will automatically pick an available ephemeral port from a specific range of numbers. This port is used for that specific connection only and once the conversation is over, the port is closed and can be reused for another connection.

The range of these ports can vary depending on your operating system. For example, many Linux systems use ports 32768-60999, while Windows systems use ports 49152-65535.

4.1.3 - Redirect

For more information on Proxy vs Redirect check Part 1 - Inbound SQL DW connections on Public Endpoints, here we I just want to show this comunication from network trace point of view. And below we can see

- the Source / Destination ports = 1433 means you are speaking with Synapse Gateway,

- when you see port 11xxx range means you are using redirect, that means that you are comunicating directly with host server (Called Tenant Ring)

To better understand this scenario check doc below

Azure Synapse Analytics connectivity architecture

4.2 - SCENARIO 2 - The server was not found or was not accessible

Let's imagine you are looking for simple error

===================================
Cannot connect to WRONGSERVENAME.sql.azuresynapse.net.
===================================
A network-related or instance-specific error occurred while establishing a connection to SQL Server.
The server was not found or was not accessible. Verify that the instance name is correct and
that SQL Server is configured to allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
(Framework Microsoft SqlClient Data Provider)

For "Server not found" error you should be looking for Part 1 - Inbound SQL DW connections on Public Endpoints and Part 2 - Inbound Synapse Private Endpoints of my blog posts series. You can easily check it with NSLOOKUP command. There is NO need to capture network trace in this kind of error. But anyway to show the network trace troubleshooting, I used the display filters below, That can be used to filter Synapse connection related

(tcp.port in {1433, 11000..11999} or (dns.qry.name contains "database.windows.net") or (dns.qry.name contains "sql.azuresynapse.net"))

TIP: If you are NOT seeing DNS requests may be because you already have them on cache. Try to clear it before capturing

ipconfig /flushdns

In the sample above We can see that I sent requests to my 2 DNS servers (Using google DNS servers) and for both I got same answer back from DNS saying "No such name A XXXXXXXX" that mean that this server does not exists.

If the name was correct, then you could have a DNS problem. This is a common issue when using Private Endpoints. Check Part 2 - Inbound Synapse Private Endpoints of these blog posts series as mentioned above

Another common issue for same error message could be port is closed. Best option is to use a Azure Synapse Connectivity Checker. This script helps us verify various aspects. Follow the instructions on Git Main Page to execute script. Upon executing the script, you can check if you have name resolution working fine and all needed ports open, illustrated in the sample below:

PORTS OPEN (Used CX DNS or Host File entry listed above)
- TESTS FOR ENDPOINT - XXX.sql.azuresynapse.net - CX DNS IP (XXXXX)
- PORT 1433 - RESULT: CLOSED
- PORT 1443 - RESULT: CLOSED
- PORT 443 - RESULT: CONNECTED
- TESTS FOR ENDPOINT - XXX.sql.azuresynapse.net - CX DNS IP (XXXXX)
- PORT 1433 - RESULT: CLOSED
- PORT 1443 - RESULT: CLOSED
- PORT 443 - RESULT: CONNECTED
- TESTS FOR ENDPOINT - XXX.database.windows.net - CX DNS IP (XXXXX)
- PORT 1433 - RESULT: CLOSED
- PORT 1443 - RESULT: CLOSED
- PORT 443 - RESULT: CONNECTED
- TESTS FOR ENDPOINT - XXX.dev.azuresynapse.net - CX DNS IP (XXXXX )
- PORT 443 - RESULT: CONNECTED

After analyzing the information provided above, we observed that port 1433 is closed. This port is essential for establishing connections from Power BI, SSMS other clients.

If your issues is from Synapse Studio make sure to check 443 and 1443 as documented at https://learn.microsoft.com/en-us/azure/synapse-analytics/security/synapse-workspace-ip-firewall#connect-to-azure-synapse-from-your-own-network

4.3 - SCENARIO 3 - Redirect port 11xxx range closed

Another similar error to above, but with different conclusion. The customer had a Synapse workspace with public network access enabled and was attempting to connect to the SQL endpoint using SSMS. However, they were unable to complete the login process due to the error below:

Cannot Connect to Server.sql.azuresynapse.net
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: D - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL server, Error: 10060)

Troubleshooting

If we do the NSLOOKUP and test port command as suggested above we could see that connection to gateway was OK. Looking further on network trace using filter similar as above

(tcp.port in {1433, 11000..11999} or (dns.qry.name contains "database.windows.net") or (dns.qry.name contains "sql.azuresynapse.net"))

In the following example, the traffic indicates that communication TO the redirect port (11008) was unsuccessful. Client is trying to stabilish a TCP connection (SYN / SYN-ACK / ACK) but there is no reply back.

Solution

The customer should enable outbound communication on CX side firewall from the client to ALL Azure SQL IP addresses within the region on ports within the range of 11000 to 11999 when using public endpoint and using redirect mode. Utilizing the Service Tags for SQL can simplify the management of this process.

4.4 - SCENARIO 4 - Connection timeout (Middle connection) / PreLogin handshake

Here are some samples of error messages you might receive:

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=5895; handshake=29;
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
A connection was successfully established with the server, but then an error occurred during the login process
Failed to copy to SQL Data Warehouse from blob storage. A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.) An existing connection was forcibly closed by the remote host

This error means that client WAS able to reach Synapse gateway, or else I would get "Server not found" error as mentioned above, but I still could not complete the connection

This usually indicate some issue on network level. Below you can find some related scenarios for above issue:

4.4.1 - SCENARIO 4.1 - TLS Blocked

Something that we see eventually in a customer is where we see TCP communication is OK, so PORT IS OPEN. If you do simple test, port looks open. But encrypted messages TDS / TLS does not get through. From cliente point of view you have pre-login timeout because you reach server, but the connection was not able to complete the login.

In this scenario you need to check client network team need to review firewall configuration. For real CX scenario, their company firewall was blocking this comunication

Here is a sample

4.4.2 - SCENARIO 4.2 - Long connection

Another possible scenario is when client machine have some issues like as sample high CPU for long time and delaying package receive / send. We have an internal security measure that if a connection is taking long time, we might eventually disconnect you. From client point of view, this is a pre-login handshake failure.

In this scenario is not Server fault, it was just client with CPU so HIGH, that cannot handle network packages fast enough. And after long time Synapse will disconnect this connection because it did not complete in the expected time.

4.4.3 - SCENARIO 4.3 - Azure firewall

In this other scenario the customer had a Synapse workspace with public network access and attempted to connect to the Dedicated SQL pool using SSMS through an Azure VM. However, they were unable to complete the login process due to the error below:

Cannot connect to Server.sql.azuresynapse.net.
A was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft 10054)
An existing connection was forcibly closed by the remote host

Troubleshooting

The error indicates that the connection has been successfully established, but the login process was not completed. Therefore, the ports are open. However, now we need to check the communication traffic by capturing the network trace.

Now, let's have a look at the trace below. We will note the FIN packages (Explanation of FIN packages, check above) coming from port 1433 to the destination port 63417. The communication starts at 10:38.42, and the FIN occurred at the same time as start. Something in the middle is breaking communication.

After some troubleshooting, we noted that there was an Azure Firewall that was part o CX network. Therefore, the flow of communication will be as follows:

VM Machine ---> Azure FW ---> Synapse workspace.

Solution

In this scenario we involved Azure Firewall support team that explained the following:

Azure Firewall supports rules and rule collections. A rule collection is a set of rules that share the same order and priority. Rule collections are executed in order of their priority. Network rule collections are higher priority than application rule collections, and all rules are terminating.

There are three types of rule collections:

Application rules: Configure fully qualified domain names (FQDNs) that can be accessed from a Virtual Network.
Network rules: Configure rules that contain source addresses, protocols, destination ports, and destination addresses.
NAT rules: Configure DNAT rules to allow incoming Internet connections.

Ref: https://learn.microsoft.com/en-us/azure/firewall/firewall-faq#what-are-some-azure-firewall-concepts

Accordingly, If networking rules and application rules have been configured, network rules are applied in priority order before application rules.

In this scenario cx had an Application rule to allow client to reach FQDN (server.sql.azuresynapse.net), this rule would not work for redirect that was scenario here. It was recommended to create a Networking Rule, to allow 1433 port + range of redirect ports (11000-11999) using Service tags. The Service tags are found under Networking Rules, and we need to configure the Firewall Policy to allow these communications.

After allowing this communication, the login process has been completed.

4.5 - SCENARIO 5 - Connection Dropped

Let's suppose that the customer is loading data using ETL tools such as SSMS, ADF, Synapse ADF, Databricks, or any other third-party tool. This loading process has failed due to a disconnection. However, this disconnect does not occur in a predefined manner but happens in a transient way, making it challenging to identify or figure out how to reproduce the issue or determine the exact cause behind this problem.

As long as the data loading process proceeds smoothly, there is no need to conduct connectivity tests since the connection is initially established (that means WE CAN CONNECT) but it's subsequently interrupted. But it is essential to gain insights into how the connection is established and the location of the client machine.

Troubleshooting

Troubleshooting begins with the collection of network traces in a circular manner (As explained above). The network trace collection remains active until the issue occurs, at which point the customer needs to stop the trace, as soon as possible.

Within the network trace, we focus on the communication occurring between the Synapse database and the client machine. When dealing with disconnection issues, it's crucial to examine the RESET packages (RST) in the network trace, as outlined below:

To use reset Filters , please use this

(tcp.port in {1433, 11000..11999}) and (tcp.flags.reset == 1)

Notice that in these scenarios the RESET is coming from SERVER to CLIENT (1433 -> Ephemeral Ports).

Even though it came from server, we might need additional investigation on why SERVER sent a reset. That does not mean a health issue on server. It could be a connection reset because you did a scale up operation and this will cause all existing connections to be dropped.

To better understand what happened before the connection dropped you can follow up connection

As data is encrypted there is not much to see on below screen. You can just close

Now the trace will be filtered with just one single communication thread using filter by tcp.stream.

In this sample we can see that connection completed and eventually you got connection reseted by Server side (1433 -> Ephemeral port)

With this time you can try to investigate for some issue that happened at same time that could explain the disconnection or you can also open a case for further investigation sharing this network trace.

Another sample

I run a command that is not actually moving data back and forth

WAITFOR DELAY '01:00:00'

This could be similar to an update that takes a long time on server side, but during all query duration it will not need to send data to client side… To avoid keeping this connection idle Client or Server need to keep sending Keep-Alive packages, and other side need to send ACK to make sure that the other side will keep connection alive.

This keep-alive packages does not mean any error !!! (Check detailed explanation above)

It just mean that during some time there was no comunication between client and server. If idle for long time you can be disconnected

In this sample below we can see:
• Client and server exchange some data
• Client stop requesting and at network level we can see some keep alive packages
• Some more data exchange
• Client is forced to close (In this case forced killing the application). Connection reset is sent from client to server to notify server that this client is gone

Summary

Hope this guide is useful to increase your knowledge on how connection behind the scene and how can you go deep into troubleshooting network traces

More info at: Troubleshoot connectivity issues on a dedicated SQL pool

Boost your CICD automation for Synapse SQL Serverless by taking advantage of SSDT and SqlPackage CLI

RuiCunha — Thu, 14 Sep 2023 14:25:38 GMT

Introduction

Azure Synapse Analytics Serverless SQL is a query service mostly used over the data in your data lake, for data discovery, transformation, and exploration purposes. It is, therefore, normal to find in a Synapse Serverless SQL pool many objects referencing external locations, using disparate external data sources, authentication mechanisms, file formats, etc. In the context of CICD, where automated processes are responsible for propagating the database code across environments, one can take advantage of database oriented tools like SSDT and SqlPackage CLI , ensuring that this code is conformed with the targeted resources.

In this article I will demonstrate how you can take advantage of thee tools when implementing the CICD for the Azure Synapse Serverless SQL engine. We will leverage SQL projects in SSDT to define our objects and implement deploy-time variables (SQLCMD variables). Through CICD pipelines, we will build the SQL project to a dacpac artifact, which enables us to deploy the database objects one or many times with automation.

Pre-Requisites

Before you run this lab, make sure that you are using the latest version of Visual Studio, since the support for Synapse Serverless was recently introduced in the 17.x version. The one that I've used in this lab was Microsoft Visual Studio Community 2022 (64-bit) Version 17.7.3.

I've used Azure DevOps Git to setup the automated processes to build and deploy the Dacpac. In case you are using your own infrastructure to run these processes, ensure that you have installed the latest SqlPackage version in your agent runner machine. In this lab, I've used a Microsoft-Hosted Agent runner using the latest Windows image (running SqlPackage v162.0.52).

Starting the Lab

Step 1: Adding a new database project to Visual Studio and importing the serverless pool

Create a new project and select the "SQL Server Database Project" template.

Select "Next" to start configuring your new project.

Select "Create" to finish the project configuration.

Navigate to the Solution Explorer blade and double-click "Properties". This will open a new window displaying the database project properties.

In "Project Settings", select the "Azure Synapse Analytics Serverless SQL Pool" target platform as shown in the figure below. If you don't see this option available in the dropdown list, most likely you don't have the latest SSDT/ Visual Studio version installed.

Note: You can refer to these links in case you need to update SSDT and Visual Studio:

Download SQL Server Data Tools (SSDT) - SQL Server Data Tools (SSDT) | Microsoft Learn

SQL Server Data Tools - SQL Server Data Tools (SSDT) | Microsoft Learn

After selecting the target platform, you can start importing the Synapse Serverless pool to your project.

To do this, from the "Solution Explorer" blade, right-click the project name and then select the "Import" --> "Database…" option.

Hit the "Select Connection" button to specify your Synapse Serverless pool and from the "Import Settings" section, make sure to uncheck the "Import application-scoped objects only" in case you need to import any server-scoped objects as well.

Select "Start" to begin importing the Serverless SQL pool objects to your project.

When the import is finished, check the Solution Explorer as it will show the sql files containing your database objects:

Step 2: Taking advantage of SQL CMD Variables in your Visual Studio code

In this lab, I'm using an external table that is pointing to a specific external location in my development environment. This external table, named "userData", is targeting a delimited file, named "eds_mapping.csv", saved in a storage container named "csv".

The data source being used by this external table is named "eds_storagecicd" and it is targeting a storage account named "stgsyncicddev".

I've decided to use the Shared Access Signature authentication method, when accessing my storage account. That's why I'm defining a database scoped credential with this kind of authentication.

IMPORTANT NOTE:

In this screenshot above you are not seeing the full t-sql statement as the SECRET argument is missing from the CREATE DATABASE SCOPED CREDENTIAL statement. However, a SECRET value was provided at creation time. This is a by design behavior (for security reasons) in Visual Studio, when importing your database objects from your database to the database project, sensitive information is not being exposed in the code.

So, make sure you revise any code that is using sensitive information, like database scoped credentials, validating if the object definition is consistent with what you have in the database.

Here's an example of an error message that can result from deploying an external table to a target environment using a missing or invalid credential:

Msg 16562, Level 16, State 1, Line 27

External table 'dbo.userData' is not accessible because location does not exist or it is used by another process.

Usually, when deploying database objects like external tables from a source environment to a target environment, you require these objects to reference different resources. For example, the files that are referenced by external tables might be stored in a different storage location or storage path (and eventually stored with a different filename).

So, how can you ensure that your database objects are referencing the right resources when being deployed to a target environment?

The answer is using SQLCMD Variables. These variables can be used in SQL Server Database Projects, providing dynamic substitution to be used for publishing of Dacpac files, for example. By entering these variables in project properties, they will automatically be offered in publishing and stored in publishing profiles.

In Visual Studio, you can add these variables to your project, from the "Project Properties" window, by selecting the "SQLCMD Variables" menu option:

Important note: by adding these variables to your code in Visual Studio, you are not changing anything at database level, your changes will be reflected in your project files only.

In this example, I'm creating three new variables , setting the values for the storage location, the file path, and the SAS key that will be used by the external table "userData" in my development environment:

In the figures below you can see how the hardcoded values have been replaced by these SQLCMD variables in my database project files:

From the "File" menu, you can save all the changes, and before moving on to the next step, I'd recommend building your solution, from the "Build" menu, ensuring that your code is error free.

And with this last action, we complete the lab's second step.

Step 3: Integrating your Visual Studio solution with a Git Repository

By having your Visual Studio solution integrated with a Git repository, you are leveraging source control in your project and improving your Continuous Integration process, as part of the CICD automation for your Synapse Serverless pool.

As part of this process, the goal is to push the changes from your Visual Studio project to your Git branch, as the Dacpac file will be built on top of these files. This Dacpac file will represent the outcome of this Continuous Integration process.

To integrate your Visual Studio solution with your Git provider, switch from the "Solution Explorer" tab to the "Git Changes" tab (you can access this tab from the "View" menu).

From "Git Changes", select the "Create Git Repository…" option to initialize a local Git repository.

To create a new remote repository to push your changes, select the "Push to a new remote" option, otherwise, in case you prefer to use an existing remote repository, select "Existing remote" and provide your repository URL.

Select "Create and Push" to complete the Git integration. During this integration, your project files will be automatically pushed to your remote repository. You can check the master branch in your remote repository, as it should contain all your project files:

Step 4: Creating a DevOps Pipeline and building the Dacpac file

After integrating your Visual Studio database project with your Git repository, it's time to setup a DevOps Pipeline to build the Dacpac.

From the left navigation menu, select "Pipelines" and then "New pipeline" to create a new pipeline.

Select "Use the classic editor" to create a new pipeline without YAML.

Select "Azure Repos Git" as the source type, and then specify your project name, repository and branch.

Select the .NET desktop template.

To simply your pipeline, you can just keep these tasks below:

Select "Save & Queue" to save your changes and then select "Save and Run" to run your pipeline.

Once the job run is finished, you can validate the list of published artifacts by selecting the link below:

The link will take you to the Dacpac file:

Now that the Dacpac file has been published, it's now time to configure the Continuous Deliver process.

Step 5: Creating a Release Pipeline and deploying the Dacpac

In this step I'm creating a new release pipeline to deploy the Dacpac file to a target Synapse Serverless SQL pool.

From the left navigation menu, select "Pipelines" and then select "Releases".

To start configuring your new release pipeline, select "+New" and then "New release pipeline".

When prompted to select a template, select "Empty Job".

you can name your stage and then close this blade.

Let's start by adding our Dacpac file as a pipeline artifact. Select "+Add" to add a new artifact.

Under "Source Type" select "Build". you must specify your project name and the build pipeline name.

Select "Add" to add this artifact to your pipeline.

Select the "Tasks" tab to start configuring your pipeline. Click the "+" button in the Agent Job bar to add a new task.

You can type "data warehouse" in the search bar , as you're looking to add the "Azure SQL Datawarehouse deployment" task to your release pipeline. This task will allow deploying a Dacpac file to the target environment.

Select "Add" to add this task to your pipeline.

Let's start by configuring the authentication related inputs in this task. Instead of using hardcoded values, I'll take advantage of the user defined Variables in my DevOps pipeline.

In order to define and set the values for your variables, you must select the "Variables" tab. I'm using these variables below, defining values for my target Synapse Serverless server, database and user credentials.

Back to the "Tasks" tab, let's continue configuring our task , in particular the "Deployment Package" section.

When you select the "SQL DACPAC file" deploy type, the deployment task will execute the SqlPackage CLI to deploy (publish) the Dacpac file. The SqlPackage is a command line utility built on top of the Data-Tier Application Framework (DacFx) framework , and it exposes some of the public DacFx APIs like the Extract, Publish and Script. Since we want to deploy a dacpac file, the action that we are interested in is the PUBLISH action.

To specify the "DACPAC file" location, hit the "Browse" button

Specify the Dacpac file location from the linked artifact:

There's a final step that you need to take before saving and running your release pipeline: replacing the SQLCMD variables values with new values pointing to your target environment, as these variables are still referencing the resources in your source environment.

Any valid SQLCMD variable existing in the Dacpac can be overridden by adding the /v: (short form for /Variables:) property to the arguments list.
You can refer to this link to get more details on how to use SQLCMD variables in SqlPackage:

SqlPackage Publish - SQL Server | Microsoft Learn

In this example, because I'm using those three SQLCMD variables in Visual Studio (storage_location , file_path and sas_key) , I'm adding three user defined variables to my pipeline, to override the SQLCMD variables.

My external table "userData" will be pointing to a different storage account (stgsyncicduat instead of stgsyncicddev) and to a different file path (target-csv/eds_mapping.csv instead of csv/eds_mapping.csv). I'll be obviously replacing the storage account SAS key as well.

After defining the pipeline variables, return to the task configuration, as you need to configure the SQLCMD variable replacement. This is done via SqlPackage arguments, when using the variables property. Using variables will instruct SqlPackage to override the SQLCMD variables being used in the Dacpac file with the new values defined in your DevOps variables.

This is how I'm overriding my SQLCMD variables (storage_location, file_path, and sas_key).

After the configuration is complete, hit the "Save" button and then select "Create Release" to run your release pipeline.

You can track the release progress by selecting the release number link or by selecting the "View Releases" button.

Once in the release, you can mouse over the stage name, and select the "Logs" button to get more details about the actions being performed during the job run.

After the execution is completed, the task output should look similar to this:

To validate that the deployment went well, and all the objects are now pointing to the target environment resources, you can use a client tool such SSMS.

Et voila! My Synapse serverless objects were successfully deployed to the target environment and they are now pointing to a different external location 🙂

Conclusion

By completing this lab, you should have learned how to take advantage of database oriented tools (like SSDT or SqlPackage) to boost your CICD automation for Azure Synapse Serverless SQL pools. These tools will facilitate the deployment of database changes across the environments, by providing deploy-time variables (SQLCMD variables) that are particularly helpful in the context of CICD for an Azure Synapse Serverless SQL pool, where you must adapt your database objects to the target environment resources.

Metadata-Based Ingestion in Synapse with Delta Lake

madhuvigupta — Wed, 19 Jul 2023 15:00:00 GMT

Overview

The crucial first step in any ETL (extract, transform, load) process or data engineering program is ingestion, which involves dealing with multiple data sources and entities or datasets. Managing ingestion flow for multiple entities is therefore essential. This article outlines a Metadata-based approach for ingestion that allows for convenient modification of datasets whenever necessary. It also shows how a Delta Lake can be accessed by different forms of compute, such as Spark pools and SQL serverless, in Synapse and how these computes can be utilized in a single Synapse pipeline.

Scenarios:

Metadata-based ingestion is a frequent practice used by various teams. In the past, ASQL was used just for storing Metadata, which resulted in additional resource maintenance only for Metadata. However, with modern architecture and migration to Synapse for analytics, there is an opportunity to move away from other Metadata stores such as ASQL database.

To address this, we explored multiple options to find a new and more effective way to store and read Metadata. After careful consideration, we introduced a new method for Metadata storage that would eliminate the need for ASQL and allow for easier maintenance of Metadata resources.

This novel approach to Metadata storage is based on modern technology and is designed to meet the needs of a Data Engineering team. With this novel approach, the team can better manage Metadata resources as it is easily configurable with Delta tables.

Overall, the introduction of this new Metadata-based ingestion approach represents a significant step forward for every team.

Metadata Store Options

ASQL
Dedicated SQL Pool
Delta Lake tables
Excel/csv files
Azure Table Storage
Serverless SQL Pool

Among the above options, Delta Lake tables are the best fit for storing Metadata, as Delta Lake offers ACID like properties, schema enforcement and data versioning, which makes it more useful.

Pros and cons of Delta Lake tables are as below:

Pros:

Delta Lake tables are used heavily in all reporting solutions.
Delta Lake tables support bulk update SQL commands such as update, merge, etc.
Delta Lake tables support audit and restore previous snapshot of data.
Delta Lake provides several features such as ACID transactions, schema enforcement, upsert and delete operations, unified stream and batch data processing, and time travel (data versioning) that are incredibly useful for analytics on Big Data.

Cons:

Synapse pipelines cannot directly read Delta tables. They can only be read through compute like Spark pool.

How to use Delta tables for Metadata-based ingestion

Using Serverless SQL pool
Using Synapse dataflows
Using Spark notebook

From the above listed options, Serverless SQL pool seems to be the optimal way to read Metadata from Delta tables in Synapse.

SQL serverless pool benefits

SQL serverless pool can now read Delta tables directly from Delta Lake, just like csv files. This can be leveraged in Synapse pipelines to read Metadata stored in Delta tables.
SQL serverless pool is always available and part of every Synapse workspace.
SQL serverless pool does not require any spin up time to become active unlike Spark pools.
The cost of serverless pool is low, using $5 per TB of processed data. In this scenario, there are only a few Metadata rows that are read, which is cost efficient.
Costing Model:
- Cost is $5 per TB data processed by the query.
- Cost depends on the amount of data processed:
  - Data read/write into Delta Lake.
  - Data shuffled in intermediate nodes.
  - Creation of statistics.
  - Minimum data processed will be 10 MB.

Metadata (Delta table) based ingestion using Serverless SQL pool.

Using Serverless SQL pool, we can query Delta tables to read our Metadata and then start our orchestration process using pipelines. For sample Metadata, please refer to the GitHub repository mentioned in appendix.

How to use Serverless SQL pool to read Metadata:

Creation of linked service:

Prerequisite: Create at least one database under serverless pool for Metadata.

Connection String: Integrated Security=False; Encrypt=True; Connection Timeout=30; Data Source=synapseserverlesspoolname-ondemand.sql.azuresynapse.net ; Initial Catalog=DBName

2. Create a new dataset using the linked service created in step 1 and keep the table name empty.

3. As shown in below snapshot, Create a pipeline that uses Look-up activity to read Metadata from Delta Lake. In this Look-up activity we are connecting to dataset (from point 2) to fire user customized query on Delta table. Once the Look-up activity retrieves all rows from the Metadata table, the For-loop activity is used to iterate through each Metadata row from Look-up activity output. And within For-loop activity, a copy activity is used to read data from source to sink using query/table option for given column SourceQuery/SourceEntity from Metadata row, respectively.

Overall, this pipeline efficiently leverages the Look-up and For-loop activities to retrieve Metadata and iterate through it, while the copy activity facilitates data movement from the source to the sink using the appropriate query or table options based on the Metadata information.

Look-up activity to fire user customized query on Delta table as shown below: 

Query:

SELECT * FROM OPENROWSET (BULK 'filepath', FORMAT = 'delta') as rows

NOTE:

Use filepath above in format as given: https://datalakegen2.b lob.core.windows.net/syanpseworkspace/data/common/metadata/entitymetadata/ 

With Metadata-based ingestion, Synapse pipeline is simplified and it contains only a few pipeline items like copy activity, for-loop, and look-up, reducing the need of adding 100 of copy activities if you are processing data for 100 entities each day, which makes pipeline huge and difficult to maintain.  Metadata-based pipeline makes orchestration simple and clean.

Process to merge raw data into gold table

Once we have data copied into raw layer, we call a Synapse notebook which reads Metadata and merge raw parquet files data into gold tables in parallel threads as shown below:

Steps to make notebook in parallel call fashion:

Create methods which will have logic to merge data to existing tables:

2. Read Metadata in a data frame:

Call method created above in parallel fashion (async calls):

Advantages:

As the Synapse session takes 2-3 mins to start, it will save a lot of time by processing multiple entities in parallel executions.
With Metadata driven approach if there is a change in any attribute, like entities addition/removal, Delta Lake path, then you just need to change Metadata and code will take care of the rest.
Minimal cost: cost is $5 per TB data processed by the query.

Conclusion

Delta Lake is a valuable resource for persisting data in storage, offering ACID-like properties that are particularly useful for analytics and reporting. It provides several other features such as schema enforcement, upsert and delete operations, unified stream and batch data processing, and time travel (data versioning) that are incredibly useful for analytics on Big Data.

A streamlined ingestion process is critical for ETL. Metadata-based ingestion provides an effective solution for achieving a smooth ingestion flow, with only a one-time setup required. Pipeline activities can be easily extended, to add new entities or retire existing datasets.

In this article, we have explored how Delta Lake can be accessed by different forms of compute in Synapse such as Spark pools and SQL serverless. And how these compute steps can be orchestrated in a single Synapse pipeline. This, combined with the other benefits of Delta Lake mentioned above, make it an incredibly useful format for data ingestion and storage in an ETL process and in Synapse pipelines.

Appendix

Code Repository:  Metadata-IngestionUsing-ServerlessSQLPool
Other Helpful articles:

Missing Fields Added to Dedicated SQL pool Diagnostic Settings Logs

jacindaeng — Tue, 13 Jun 2023 18:09:49 GMT

Over the past year, customers have informed the team there were a set of key columns missing in the standalone Dedicated SQL pools (formerly SQL DW) and Synapse workspace Dedicated SQL pool Diagnostic Setting logs. After receiving a considerable amount of customer feedback, nine fields have been added to the Dedicated SQL pool logs and are generally available. These values will provide a more effective monitoring experience as customers are now able to identify which session the query belongs to along with other fundamental insights.

The fields added into the Synapse Diagnostic Settings Logs are the following:

Azure Monitor Logs reference - SynapseSqlPoolExecRequests:

ClassifierName
Importance
ResourceAllocationPercent
ResultCacheHit

SessionId
SubmitTime [UTC]
SubscriptionId

Azure Monitor Logs reference - SynapseSqlPoolWaits.

Request_id
Session_id
Lock Type

There will be more fields added later and we’re working on enabling Basic Logs for Synapse logs as well so please stay tuned!

Using Azure DevOps with Synapse Workspaces to create hot fixes in production environments

vysuopys — Wed, 10 May 2023 15:00:00 GMT

Have you ever deployed a release to production only to find out a bug has escaped your testing process and now users are being severely impacted? In this post, I’ll discuss how to deploy a fix from your development Synapse Workspace into a production Synapse Workspace without adversely affecting ongoing development projects.

This example uses Azure DevOps for CICD along with a Synapse extension for Azure DevOps: Synapse Workspace Deployment. In this example, I assume Synapse is already configured for source control with Azure DevOps Git and Build and Release pipelines are already defined in Azure DevOps. Instructions on how to apply this this can be found in the Azure Synapse documentation for continuous integration and delivery.

Let’s start by looking at an example of development activities in a Synapse workspace. During development, several things happen. The Main (aka. collaboration, trunk or master) branch in the Synapse Development environment gets branched off one or more times to support parallel feature development activities. Changes are committed within the feature branches. Periodically, the branches need to refresh their code with the latest changes that have been incorporated into Main. It is typically up to the developer working on a feature branch to merge their code from Main at whatever interval they choose. These activities can all be done through the Synapse user interface by executing a pull request in the proper direction, in this case from Main to Feature 1. It is up to the feature developers to resolve conflicts in their feature branch.

At some point, code in the Main branch is ready for deployment to the Synapse Test environment. After testing, a decision is made to release the code into Production. This is done through an Azure DevOps release pipeline outside the scope of this document. For the sake of simplicity, the following diagrams omit the Synapse Test environment and only show the Synapse Development and Production environments since we are targeting a Production “hot fix” use case. Best practice would be to have at least one intermediate environment between Development and Production.

After a Production release, development continues on with Feature 3…N, and changes are incorporated into the Main branch as shown in the diagram below.

Now to the heart of the discussion... At some point, a critical bug is discovered in Production that needs to be fixed immediately. How do we do this?

Start by getting the Commit ID of the code that was released to production and create a new branch based on the Commit ID. These steps must be done through Azure DevOps.

In Azure DevOps, navigate to Pipelines/Releases, and select the appropriate deployment pipeline for the production release. Then select the appropriate release.

Note: It is important to have the release pipeline have access to both the ARM templates created by the build process, as well as the actual source code. The ARM templates are used for deployment into later stages (Test/QA/Prod/etc). The source code will be used for hot fixes.

Copy the Commit ID (this looks like an 8 character hex string in the UX, but is actually only the first 8 characters of a longer SHA) for the released code as shown in the diagram below. Make sure you select the Commit ID for the source code and not the ARM template artifact.

Navigate to the branches section of your Azure DevOps Repo and create a new branch based on the Commit ID you copied in the last step.

Select the pulldown on “main” to get to the UX screen that allows you to search for a specific Commit:

Validate that the new branch is based on the proper Commit ID:

Now that we have a special code branch for creating the fix, we can go back into the Development Synapse Workspace, select the hot fix branch, and make our corrections.

When satisfied with the changes, commit them to the branch. In order to get changes in the hot fix branch deployed to Production without impacting the Development and Test environments, we need to create a new Azure DevOps release pipeline incorporating the Synapse Workspace Extension task. In our example, the release of the hot fix branch into Production is triggered manually in Azure DevOps.

The hot fix branch should remain in existence for the life of the Production release it is associated with, so that future hotfixes can be incorporated easily.

Let’s create a new release pipeline specifically for hot fixes following the instructions for Synapse continuous integration and delivery. We will make a couple modifications to the pipeline that is created in that document. First, the source artifact for this pipeline should have a default branch pointing to the hot fix branch for this release (ie Release3_Hotfixes in our example). Update the source alias to reflect the proper code branch. Update the stage name to “Production_Hotfix”.

Now click on the “1 job, 0 task” link under the stage name. Add a new agent job. Search for Synapse and select the “Toggle” task. Update the fields as appropriate for your Production environment. For the subscription, use a service connection for the Prod environment. You can see how to create service connections in this document.

Add another agent task. Search for Synapse and add the task called “Synapse Workspace Deployment”. Update the fields as shown in the screenshot below, replacing values as appropriate with names in your Production environment. Make sure the operation type is “Validate and Deploy”.

Add another agent task; search for Synapse and add the task called “Azure Synapse Toggle Triggers”. Update the fields as shown in the screenshot below, replacing values as appropriate with names in your Production environment. Rename the pipeline to something meaningful. Save your pipeline.

The pipeline we just created must be manually run to deploy the hot fix to Production. Go into Azure DevOps and create a new release using this pipeline when you are ready.

We have now completed the hot fix release flow shown below. Validate that your Synapse Development Main code branch and live mode UX are unaffected by the hot fix.

Don’t forget to incorporate the hot fix code changes back into the Main and Feature branches at a convenient time. As new releases are deployed, new hot fix branches based on the Commit ID of the release will need to be created. Hot fix branches associated with releases that are no longer current can be deleted.

That’s all there is! Congratulations on fixing the burning issue without interrupting ongoing development and testing.

Azure Synapse MVP Corner - March 2023

pawelpotasinski — Tue, 09 May 2023 16:00:00 GMT

About this blog series

Microsoft Most Valuable Professionals, or MVPs, are technology experts who passionately share their knowledge with the community. They are always on the "bleeding edge" and have an unstoppable urge to get their hands on new, exciting technologies. They have very deep knowledge of Microsoft products and services, while also being able to bring together diverse platforms, products and solutions, to solve real world problems. To learn about the Microsoft MVP Award and to find MVPs visit the official website: https://mvp.microsoft.com/.

The Azure Synapse Analytics team presents you with a blog series "Azure Synapse MVP Corner" to highlight selected content created by MVPs.

This month's MVP content

	Kevin Chant (Twitter: @kevchant) Blog post: Deploy a dacpac to a serverless SQL pool using GitHub Actions Description: Covers how you can deploy a dacpac to a serverless SQL pool using GitHub Actions. Which is now possible thanks to a SqlPackage update.
	Kevin Chant (Twitter: @kevchant) Video: Streamlining your CI/CD Pipeline with Azure Synapse Link for SQL Server 2022 Description: Covers a complete CI/CD experience for Azure Synapse Link for SQL Server 2022.
	Andy Cutler (Twitter: @MrAndyCutler) Blog post: Understanding ResultSet Caching in Dedicated SQL Pools with Power BI Description: In this first blog in a new series of Power BI + Azure Synapse Analytics Andy is looking into Dedicated SQL Pools ResultSet Caching and how this feature can accelerate load performance into Power BI datasets.
	Andy Cutler (Twitter: @MrAndyCutler) Blog post: Azure Synapse Serverless SQL Pools Cheat Sheet Description: Here is a Synapse Serverless SQL Pools Cheat Sheet to help you easily reference syntax to query CSV, Parquet, and Delta data in Azure Data Lake. It covers various scenarios when querying and includes creating Views, External Tables, and using system metadata to track Data Processed.

Call to action

Read or watch the content listed above.
If you like the content, subscribe to the blogs or YouTube channels of the MVPs.
Follow the MVPs mentioned above on Twitter.
Stay tuned for more "MVP Corner" blog posts!

CI & CD With Azure Synapse Dedicated SQL Pool

pradeeps — Thu, 04 May 2023 15:00:00 GMT

Author(s): Pradeep Srikakolapu is a Program Manager in Azure Synapse Customer Success Engineering (CSE) team.

Automating development practices is hard but we can make it simpler using Version Control, Continuous Integration & Deployment and best practices to manage ALM lifecycle of an Azure Synapse Data Warehouse with this blog article.

Introduction

This article helps developers understand the following:

How to version control Synapse Dedicated SQL Pool (Azure data warehouse) objects?
How to continuously develop and deploy data warehouse objects using SSDT & SQL Package?
How to selectively deploy objects from a dacpac file?

The prerequisites to understand this article are:

Basic understanding of GIT, a repository in Azure DevOps or GitHub organizations.
How SSDT and SQL Package tooling works
Azure DevOps Pipelines

Note: Synapse Serverless SQL Pool is not discussed in this blog. I will blog about version control, CI/CD for Synapse Serverless soon.

Version control in DW is achieved by SQL Server Data Tooling (SSDT) with a database project. You can build a database project using Visual Studio, VS Code, Azure Data Studio. I will be using Visual Studio 2022 in this blog.

Build & publish database code locally from a developer machine

Create a database project – File> New > Project, choose SQL Server Database Project template

Once the database project is created, right click on the project in the solution explorer. You should choose the target platform as “Microsoft Azure SQL Data Warehouse”.

Organize the database project in the same way we organize the objects in SSMS.

In the above screenshot, see that I organized schema files in Security -> Schemas folder. I also organized tables and views for each scheme for better abstraction like SSMS with minor differences. Please make sure to include .sql files as part of your build by configuring the build action property to “Build” and copy to output directory property to “Do not copy”.

You can build the DACPAC file by building the database project from context menu or the entire solution by F5. The context menu of database project has an option build to compile the database project into DACPAC file.

SSDT + SQL Database projects use MS build to compile the source code and extract the DAC package file (DACPAC file). If you unzip/unpack DACPAC file, you will see DacMetadata.xml, Origin.xml, model.xml, and model.sql files. model.sql and model.xml files are the representation of your database model.

Once you build a DACPAC file by building database project, you can deploy it to the target data warehouse using SQL Package – Publish action either via database project or SQL Package.exe from cmd.

You need to configure the target database configuration to deploy the DACPAC file. The other way is to use SQL Package file. An example:

SqlPackage /Action:Publish /SourceFile:"C:\Users\pvenkat\source\repos\AzureSynapseDW\DWSSDT\bin\Debug\DWSSDT.dacpac" /TargetConnectionString:"Server=tcp:pvenkat-test-ws.sql.azuresynapse.net,1433;Initial Catalog=testsqlpool;Persist Security Info=False;User ID=dbttestuser;Password={};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

Continuous integration - Build & deploy database project with Azure pipelines

As a developer, you can build data warehouse code and publish it locally. What if you need to deploy the database code to a development or a test or a production environment? We wouldn’t want to manually deploy source code to any environment. The answer to this problem is to enable continuous integration and deployment on the source in Azure DevOps using Azure pipelines or GitHub using Actions. In this example, I will share the source code repository and the Azure pipelines to enable CI & CD aspects of database code development and deployment.

Add a YAML file to your source code to perform three tasks. I am using multistage Azure Pipelines to show these three tasks.

Build the database project and publish artifacts from this stage. Please note that publishing artifacts is not publish action with SQL Package.
Compare/verify the DACPAC file from build task with target data warehouse. The target data warehouse can be a dev/test/production environment.
Deploy the DACPAC file to target environment data warehouse.

Build Database Project & Publish Artifacts

task: VSBuild@1 uses MS Build to generate the DACPAC file in /{project folder}/{release/debug}/bin/{project name}.dacpac
task: CopyFiles@2 copies the DACPAC file and AgileSqlClub.DeploymentFilterContributor.dll file to artifact staging directory ('$(build.artifactstagingdirectory)').
task: PublishPipelineArtifact@1 copies all files and folders from '$(build.artifactstagingdirectory)' to a folder ‘drop’ and will publish the folder ‘drop’ for next steps.

- stage: Build jobs: - job: Build_And_PublishDatabase_Project steps: - task: NuGetCommand@2 displayName: 'NuGet restore' inputs: vstsFeed: 'ebdcf40d-0db5-427e-b3c5-32b9cb5dcb8d' - task: VSBuild@1 displayName: 'Build solution DWSSDT/DWSSDT.sqlproj' inputs: solution: DWSSDT/DWSSDT.sqlproj - task: CopyFiles@2 displayName: 'Copy binaries to staging directory' inputs: SourceFolder: '$(System.DefaultWorkingDirectory)' Contents: '**\DWSSDT\**\bin\**' TargetFolder: '$(build.artifactstagingdirectory)' - task: CopyFiles@2 displayName: 'Copy dacpac tools to staging directory' inputs: SourceFolder: dacpactools TargetFolder: '$(build.artifactstagingdirectory)\dacpactools\' - task: PublishPipelineArtifact@1 displayName: 'Publish Pipeline Artifact' inputs: targetPath: ' $(build.artifactstagingdirectory)' artifact: drop

Verify DACPAC & compare with target data warehouse

Verify stage is verifying the contents of the dacpac that are about to be deployed.

download: current task downloads the ‘drop’ folder and its content generated from previous stage (Build) for dacpac verification process.
task: SqlAzureDacpacDeployment@1, DeploymentAction: Script task uses SQL Package.exe to perform schema compare on the objects from DACPAC file and target data warehouse. It generates a differential script based on arguments provided so that the content of the differential script can be verified.

- stage: Verify jobs: - job: Verify_DW_Package steps: - download: current artifact: drop - task: SqlAzureDacpacDeployment@1 displayName: 'Verify ' inputs: azureSubscription: 'Pradeep-MSFT Personal Use (f4664abe-17d0-4128-8048-150cd01575b4)' ServerName: 'pvenkat-test-ws.sql.azuresynapse.net' DatabaseName: testsqlpool SqlUsername: dbttestuser SqlPassword: '$(PASSWORD)' DeploymentAction: Script DacpacFile: '$(Pipeline.Workspace)\drop\DWSSDT\bin\Debug\DWSSDT.dacpac'

Deploy DACPAC using SQL Package

Deploy stage publishes the DACPAC file to target data warehouse. Publish action generates the differential script between DACPAC and target data warehouse and then deploys the differential script to the target data warehouse.

download: current task downloads the ‘drop’ folder and its content generated from previous stage (Build) for dacpac deployment/publish process.
task: SqlAzureDacpacDeployment@1, DeploymentAction: Publish task deploys differential script to target data warehouse.

- stage: Deploy jobs: - job: Deploy_DW_Project steps: - download: current artifact: drop - task: SqlAzureDacpacDeployment@1 displayName: 'Azure SQL DacpacTask' inputs: azureSubscription: 'Pradeep-MSFT Personal Use (f4664abe-17d0-4128-8048-150cd01575b4)' ServerName: 'pvenkat-test-ws.sql.azuresynapse.net' DatabaseName: testsqlpool SqlUsername: dbttestuser SqlPassword: '$(PASSWORD)' DacpacFile: '$(Pipeline.Workspace)\drop\DWSSDT\bin\Debug\DWSSDT.dacpac' AdditionalArguments: '/p:AdditionalDeploymentContributors="AgileSqlClub.DeploymentFilterContributor" /p:AdditionalDeploymentContributorPaths="$(Pipeline.Workspace)\drop\dacpactools" /p:AdditionalDeploymentContributorArguments="SqlPackageFilter=IgnoreSchema(sch_1)"'

Selective Deployment – Publish Action

SQL Package, publish action support several additional parameters to ignore objects by type. Parameters such as DoNotDropObjectTypes, DropObjectsNotInSource, ExcludeObjectTypes and other properties in SqlPackage Publish - SQL Server | Microsoft Learn lets you selectively deploy the object type(s) of your choice. However, SQL Package does not selective deployment of specific objects by name, regex expressions. Many customers with a large data warehouse (>2000 objects) are having trouble deploying DACPAC solutions without specifying object name(s).

GitHub - GoEddie/DeploymentContributorFilterer provides an alternative to filter objects by name/regex expressions as part of dacpac deployment. Please provide Additional arguments to Publish action - AdditionalDeploymentContributors, AdditionalDeploymentContributorPaths, AdditionalDeploymentContributorArguments to apply filters before DACPAC deployment.

AdditionalArguments: '/p:AdditionalDeploymentContributors="AgileSqlClub.DeploymentFilterContributor" /p:AdditionalDeploymentContributorPaths="$(Pipeline.Workspace)\drop\dacpactools" /p:AdditionalDeploymentContributorArguments="SqlPackageFilter=IgnoreSchema(sch_1)"'

AdditionalDeploymentContributors takes a namespace of the library. This library is applied as an additional deployment contributor as part of Publish action.
AdditionalDeploymentContributorPaths takes path of the library.dll as an input

AdditionalDeploymentContributorArguments takes filters as an input to ignore/filter/keep the objects as part of DACPAC for publish action.

Our team publishes blog(s) regularly and you can find all these blogs at https://aka.ms/synapsecseblog.

For a deeper level of understanding of Synapse implementation best practices, please refer to our Success by Design (SBD) site: https://aka.ms/Synapse-Success-By-Design

Transforming Your Data Lake: Implementing Slow Change Dimension with Synapse

Liliam_C_Leme — Wed, 03 May 2023 16:00:04 GMT

As organizations continue to collect and store large volumes of data in their data lakes, managing this data effectively becomes increasingly important. One key aspect of this is implementing Slow Change Dimension type 2, which allows organizations to track historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys and/or different version numbers. In this blog post we will address the following scenario: a customer wants to implement Slow Change Dimension type 2 on top of their data lake.

For this example, we will use Serverless SQL Pool to demonstrate how this can be done. Additionally, in the next post, we will explore how the same approach can be used with Spark.

What is Slow Change Dimension Type 2?

Slow Change Dimension type 2: This method tracks historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys and/or different version numbers. Check out this wiki to learn more, https://en.wikipedia.org/wiki/Slowly_changing_dimension

When implementing Slow Change Dimension type 2, there are various approaches that can be used depending on how the data is handled at the source. For example, if the source includes row version information or columns with flags for deleted or updated records, a different approach may be used compared to scenarios where this information is not available. In this particular scenario, we will follow a specific approach to address the challenges that are present.

Considerations for this Scenario:

This scenario is managing UPSERT and file versioning. Please note, updates are not supported on Serverless so I am workaround this with the external tables.
The files are not coming from the source with version, or information if the row was updated or deleted.
Deleted rows are not been handled on this solution, only inserted and updated ones.
Those files have a key value that is used to filter if they already exist or not on the destination. If you do not have a key column to compare. You will need to compare all the columns that the business considers as key values to determine if the information is new or not.
The source will send the information on the file regardless if it is a new row to be inserted or updated. In other words, the process of transformation and cleansing needs to understand if the value on the file refers to an updated row or a new row. So this solution tries to understand that scenario and based on that version it accordingly. If your source can send the rows in an accurate way in which it is clear which row is updated, you can change the steps accordingly.

Solution:

Consider as in the example below the folder sourcefiles_folder with all files in parquet format which were sent from the source to be compared to a destination. My destination is the external table: SCD_DimDepartmentGroup.

The file has the following columns:

Column	Datatype
DepartmentGroupKey	int	Not Null
ParentDepartmentGroupKey	int	NULL
DepartmentGroupName	nvarchar	Not Null

Here are the current values for this table - SCD_DimDepartmentGroup as Fig. 1, shows:

The last surrogate key is of the value of 8:

Fig. 1

Code - First Load and Dimension Creation:

CREATE EXTERNAL TABLE SCD_DimDepartmentGroup WITH ( LOCATION = '/SCD_DimDepartmentGroup', DATA_SOURCE = SCD_serveless_dim, FILE_FORMAT = Parquet_file ) AS SELECT ROW_NUMBER () OVER (ORDER BY DepartmentGroupKey) ID_Surr ,[DepartmentGroupKey] ,[ParentDepartmentGroupKey] ,[DepartmentGroupName] ,1 ID_valid ,0 ID_Deleted ,Curr_date as From_date ,Null as End_date FROM OPENROWSET( BULK 'https://Storage.blob.core.windows.net/Container/SCD/sourcefiles_folder/', FORMAT = 'PARQUET' ) AS [SCD_DimDepartmentGroup]

Slow Change Dimension in 5 steps:

The implementation of the SCD is quite simple is basically filtering, persisting the data filtered, and comparing.

Current values on the table:

ID_Valid column will show if the row was versioned or not. 1 for a new row, 0 for a versioned row.
ID_Delete column will show if the row is still valid or not. 0 for a new not, 1 for an invalid
Curr_date is the date where the row was inserted into the table, a column on the SCD will be FromDate
ID_Surr is a surrogate key created for this table.
EndDate will manage the version per date

1) Step 1 - Create a CETAS for new values

TableA_SCD_DimDepartmentGroup_NEW - In this example, this step will basically insert all the values that exist on the files and do not exist on the destination into an External Table.

The column DepartmentGroupKey defines if the row is new or not. You will need a column or columns to understand if this is considered new information or not in your dimension.

Please note three columns were added to this example:

As Curr_date is the date where the row was inserted into the table. So we are using getdate()
1 ID_Valid which means this is a new valid row
1 ID_Delete which means this row was not deleted Or versioned.

Code - First, let's create the data source that will be used across all the external tables:

CREATE EXTERNAL DATA SOURCE SCD_serveless_dim WITH ( LOCATION = 'https://Storage.blob.core.windows.net/Container/SCD/transformation_folder/', CREDENTIAL = [MSI] )

Now, let's create the external table for Step 1:

Please note that I am using the maximum value of the surrogate key column ID_Surr from the main table SCD_DimDepartmentGroup and adding 1 to it to generate new surrogate values for rows that require them. If some values repeat at this point, it is okay because I will reuse this information with the Department Key to create a new surrogate key in the last step (Step 5), thus ensuring a unique value for each row. I am using the max+1 approach to ensure that existing surrogate keys are not affected.

DECLARE @ID_Surr AS INT SELECT @ID_Surr = MAX (ID_Surr)+1 FROM SCD_DimDepartmentGroup CREATE EXTERNAL TABLE TableA_SCD_DimDepartmentGroup_NEW WITH ( LOCATION = '/TableA_SCD_DimDepartmentGroup_NEW', DATA_SOURCE = SCD_serveless_dim, FILE_FORMAT = Parquet_file ) AS SELECT @ID_Surr as ID_Surr ,[DepartmentGroupKey] ,[ParentDepartmentGroupKey] ,[DepartmentGroupName] ,1 ID_valid ,0 ID_Deleted ,Getdate() as Curr_date FROM OPENROWSET( BULK 'https://Storage.blob.core.windows.net/Container/SCD/sourcefiles_folder/', FORMAT = 'PARQUET' ---DELTA can be used here ) AS [SCD_DimDepartmentGroup_Silver] WHERE NOT EXISTS ( SELECT 1 FROM SCD_DimDepartmentGroup WHERE SCD_DimDepartmentGroup_Silver.[DepartmentGroupKey] = SCD_DimDepartmentGroup.DepartmentGroupKey )

Results in Fig. 2 - Step 1:

Fig. 2 - Step 1.

2) Step 2 - Create a CETAS for values that will be updated/versioned.

TableB_SCD_DimDepartmentGroup_OLD will be the next external table to be created by inserting all values that exist on the file exported from the source and do exist on the destination. Those value also should not exist on the external table created in Step 1 (A).

In this scenario, the data that will be versioned is not a new row (Step 1); it is rows that already exist in the destination, but something has changed. The DepartmentGroupKey is the key that will always remain the same, so if there is a new key, it means a new row, and the comparison will happen for any other column. Additionally, in this scenario, it is possible that the source may send the same information again without any changes. Therefore, comparing the data is necessary to ensure that the row sent is indeed a row to be versioned.

Please note for the rows to be versioned:

Flag ID_Valid was changed to 0
ID_Deleted was invalidated by changing the value to 1.
Curr_date here is the date the row was inserted on the table SCD_DimDepartmentGroup, so the From_date column is re-used.
NULL columns are been handled properly(ISNULL) to find the ones that in fact changed.

Code:

Please note I re-used my surrogate key column ID_Surr of the main table SCD_DimDepartmentGroup for the values that will be versioned.

CREATE EXTERNAL TABLE TableB_SCD_DimDepartmentGroup_OLD WITH ( LOCATION = '/TableB_SCD_DimDepartmentGroup_OLD', DATA_SOURCE = SCD_serveless_dim, FILE_FORMAT = Parquet_file ) AS CREATE EXTERNAL TABLE TableB_SCD_DimDepartmentGroup_OLD WITH ( LOCATION = '/TableB_SCD_DimDepartmentGroup_OLD', DATA_SOURCE = SCD_serveless_dim, FILE_FORMAT = Parquet_file ) AS SELECT ID_Surr ,[DepartmentGroupKey] ,[ParentDepartmentGroupKey] ,[DepartmentGroupName] ,0 ID_valid ,1 ID_Deleted ,From_date FROM [SCD_DimDepartmentGroup] WHERE NOT EXISTS ( SELECT 1 FROM OPENROWSET( BULK 'https://Storage.blob.core.windows.net/Container/SCD/sourcefiles_folder', FORMAT = 'PARQUET' ) AS [SCD_DimDepartmentGroup_Silver] WHERE SCD_DimDepartmentGroup_Silver.[DepartmentGroupKey] = SCD_DimDepartmentGroup.DepartmentGroupKey AND (ISNULL(SCD_DimDepartmentGroup_Silver.[ParentDepartmentGroupKey], 1) = ISNULL(SCD_DimDepartmentGroup.[ParentDepartmentGroupKey], 1) AND SCD_DimDepartmentGroup_Silver.[DepartmentGroupName] = SCD_DimDepartmentGroup.[DepartmentGroupName]) ) AND NOT EXISTS (SELECT 1 FROM TableA_SCD_DimDepartmentGroup_NEW WHERE TableA_SCD_DimDepartmentGroup_NEW.[DepartmentGroupKey] = SCD_DimDepartmentGroup.DepartmentGroupKey)

Results are Fig. 3 - Step 2 :

Fig. 3 - Step 2

3) Step 3 - Only updated values are to be versioned

TableC_SCD_DimDepartmentGroup_GOLD_OLD_INS - The next external table will get the new rows from the source file. The filter will be the rows that we already know something changed and needs to be versioned from Step 2.

Code:

DECLARE @ID_Surr AS INT SELECT @ID_Surr = MAX (ID_Surr)+1 FROM SCD_DimDepartmentGroup CREATE EXTERNAL TABLE TableC_SCD_DimDepartmentGroup_GOLD_OLD_INS WITH ( LOCATION = '/TableC_SCD_DimDepartmentGroup_GOLD_OLD_INS', DATA_SOURCE = SCD_serveless_dim, FILE_FORMAT = Parquet_file ) AS SELECT @ID_Surr as ID_Surr ,[DepartmentGroupKey] ,[ParentDepartmentGroupKey] ,[DepartmentGroupName] ,1 ID_valid ,0 ID_Deleted ,getdate() Curr_date FROM OPENROWSET( BULK 'https://Storage.blob.core.windows.net/Container/SCD/sourcefiles_folder/', FORMAT = 'PARQUET' ) AS [SCD_DimDepartmentGroup_Silver] WHERE EXISTS (SELECT 1 FROM TableB_SCD_DimDepartmentGroup_OLD WHERE SCD_DimDepartmentGroup_Silver.[DepartmentGroupKey] = TableB_SCD_DimDepartmentGroup_OLD.DepartmentGroupKey)

The results are in Fig. 4 - Step 3:

Fig. 4 - Step 3

4) Step 4 - Create another external table by Union

For all those external tables that were created on the last steps.

EndDatate should be null for new rows or new values versioned. If the value was versioned the respective row will contain until when it was valid.
ID_valid will be 1 if the row is not versioned, 0 if this row is no longer valid
ID_Deleted will be 0 if the row is not versioned, 1 if this row is no longer valid

CREATE EXTERNAL TABLE UNION_SCD_DimDepartmentGroup WITH ( LOCATION = '/UNION_SCD_DimDepartmentGroup', DATA_SOURCE = SCD_serveless_dim, FILE_FORMAT = Parquet_file ) AS SELECT ID_Surr ,[DepartmentGroupKey] ,[ParentDepartmentGroupKey] ,[DepartmentGroupName] ,ID_valid ,ID_Deleted ,getdate() as From_date ,Null as End_date FROM TableA_SCD_DimDepartmentGroup_NEW UNION ALL SELECT ID_Surr ,[DepartmentGroupKey] ,[ParentDepartmentGroupKey] ,[DepartmentGroupName] ,ID_valid ,ID_Deleted ,From_date as From_date ,Getdate() as End_date FROM TableB_SCD_DimDepartmentGroup_OLD UNION ALL SELECT ID_Surr ,[DepartmentGroupKey] ,[ParentDepartmentGroupKey] ,[DepartmentGroupName] ,ID_valid ,ID_Deleted ,getdate() as From_date ,Null as End_date FROM TableC_SCD_DimDepartmentGroup_GOLD_OLD_INS

Results are - Fig. 5 - Union :

Fig. 5 - Union

Here we have the new rows, new versioned rows consolidated on the external table.

5) Step 5.

Next, we need to transfer this to our main table, by recreating an external table with the new information and dropping the old one.

1) I need the data from the main table excluding the rows that were versioned on this round

2) I need the data from the external tables Union.

3) I need to keep the historical changes of the old/main table

4) On top of that I will recreate the surrogate key. But for this, we will order per ID_Surr and DepartmentGroupKey. So, the new surrogate key values will start from the max surrogate+1 as we defined in the first Steps.

SELECT ROW_NUMBER () OVER (ORDER BY ID_Surr, DepartmentGroupKey) ID_Surr ,[DepartmentGroupKey] ,[ParentDepartmentGroupKey] ,[DepartmentGroupName] ,ID_valid ,ID_Deleted ,From_date ,End_date FROM ( SELECT ID_Surr ,[DepartmentGroupKey] ,[ParentDepartmentGroupKey] ,[DepartmentGroupName] ,ID_valid ,ID_Deleted ,From_date ,End_date FROM SCD_DimDepartmentGroup WHERE NOT EXISTS ( SELECT 1 FROM TableB_SCD_DimDepartmentGroup_OLD WHERE SCD_DimDepartmentGroup.DepartmentGroupKey = TableB_SCD_DimDepartmentGroup_OLD.DepartmentGroupKey ) UNION SELECT ID_Surr ,[DepartmentGroupKey] ,[ParentDepartmentGroupKey] ,[DepartmentGroupName] , ID_valid ,ID_Deleted ,From_date ,End_date FROM UNION_SCD_DimDepartmentGroup UNION SELECT ID_Surr ,[DepartmentGroupKey] ,[ParentDepartmentGroupKey] ,[DepartmentGroupName] ,ID_valid ,ID_Deleted ,From_date ,End_date FROM SCD_DimDepartmentGroup WHERE SCD_DimDepartmentGroup.ID_valid = 0 )NEW_SCD

Results are in Fig. 6 - New Table. Please note in green there are the rows that are versioned, and in blue are the new rows:

Fig. 6 - New Table

This new table should replace the current SCD_DimDepartmentGroup, by recreating a new external table and dropping this old one.

Summary

Please review the considerations for the scenario to understand what was relevant for this implementation. As I mentioned earlier, there are different approaches that can be used to create a Slow Change Dimension, depending on how the data is handled at the source. In this post, I have provided examples of how to read new data, version the old data that has changed, and recreate new external tables with the updated information for a Slow Change Dimension type 2.

In the next post, I will demonstrate how to achieve the same using Spark. Stay tuned for more!

Liliam, UK.

Azure Synapse Analytics April Update 2023

ryanmajidi — Tue, 02 May 2023 15:00:00 GMT

Azure Synapse Analytics April Update 2023

Welcome to the April 2023 Azure Synapse Analytics Update! This month, we have a new ARM template to deploy Azure Data Explorer DB with Cosmos DB connection, as well as additional updates in Apache Spark for Synapse, Synapse Data Explorer, and Data Integration.

Read on for more details and don’t forget to watch the video!

Apache Spark for Synapse
- Delta Lake – Low Shuffle Merge
Synapse Data Explorer
Data Integration
- Capture changed data from Cosmos DB analytical store (Public Preview)

Apache Spark for Synapse

Delta Lake – Low Shuffle Merge

Low Shuffle Merge optimization for Delta tables is now available in Apache Spark 3.2 and 3.3 pools. You can now update a Delta table with advanced conditions using the Delta Lake MERGE command. It can update data from a source table, view, or DataFrame into a target table. The current algorithm of the MERGE command is not optimized for handling unmodified rows. With Low Shuffle Merge optimization, unmodified rows are excluded from expensive shuffling execution and written separately.

To learn more about this new command, read Low Shuffle Merge optimization on Delta tables.

Synapse Data Explorer

Ingest data from Azure Events Hub to ADX free tier

Are you looking for a powerful and cost-effective (or possibly even free...) way to analyze large volumes of near real-time streaming data? Azure Data Explorer now supports integration with Events Hub in ADX free tier.

Events Hub is a big data streaming platform, which can process millions of events per second in near real-time. Connecting your Event Hub data to Azure Data Explorer is easy and straightforward and can be done in just a few simple steps, using an intuitive "One-Click" ingestion wizard.

To learn more about ingesting data from Azure Events Hub to ADX free tier, read Free Event Hub data analysis with Azure Data Explorer.

New ARM template to deploy Azure Data Explorer DB with Cosmos DB connection

A new ARM template that deploys an Azure Data Explorer DB with a Cosmos DB connection is now available. This vastly simplifies the deployment of an ADX cluster with:

a System Assigned Identity
a database
an Azure Cosmos DB account (NoSql)
an Azure Cosmos DB database
an Azure Cosmos DB container
a data connection between the Cosmos DB container and the Kusto database (using the System Assigned identity)

To learn more about this new ARM template, read Deploy Azure Data Explorer DB with Cosmos DB connection with ARM Template.

New look and feel for Query command bar in ADX Web

The ADX Web Query command bar has undergone a major redesign to provide an improved user experience. The new design is not only visually appealing but also makes it easier and faster for users to access the commands they need.

To learn more about the new command bar and other ADX Web updates, read ADX Web updates – March 2023.

Data Integration

Capture changed data from Cosmos DB analytical store (Public Preview)

When you perform data integration and ETL processes in the cloud, your jobs can perform better and be more effective when you only read changed data from your source. We are excited to share that Azure Cosmos DB analytical store now supports change data capture (CDC) for Azure Cosmos DB API for NoSQL and Azure Cosmos DB API for MongoDB.

Available in Public Preview, this will allow you to efficiently consume continuous and changed (inserted, updated, and deleted) data from the analytical store. Seamlessly integrated with Azure Synapse Analytics and Azure Data Factory, it is a scalable, no-code experience for high data volume and will not consume provisioned RUs or affect the performance of your transactional workloads while providing lower latency and a lower TCO.

You can consume incremental analytical store data from a Cosmos DB container using either Azure Synapse Analytics or Azure Data Factory after you enable the Cosmos DB account for Synapse Link and you have enabled analytical store on a new container or an existing container.

To learn more about capturing Change Data:

Watch Azure Cosmos DB analytical store Change Data Capture

Read Capture Changed Data From your Cosmos DB analytical store (Preview) and Now in preview – Change Data Capture (CDC) with Azure Cosmos DB analytical store.

Thanks for reading! That's all we have for you this month. We look forward to hearing your comments and questions. We'll see you here for the next monthly update!

Extracting relational schema from streaming data containing complex JSON documents

devangshah — Thu, 27 Apr 2023 15:00:00 GMT

Author: devangshah is a Principal Program Manager for Data Explorer in the Synapse Customer Success Engineering (CSE) team.

In the world of IoT devices, industrial historians, infrastructure and application logs, and metrics, machine-generated or software-generated telemetry, there are often scenarios where the upstream data producer produces data in non-standard schemas, formats, and structures that often make it difficult to analyze the data contained in these at scale. Azure Data Explorer provides some useful features to run meaningful, fast, and interactive analytics on such heterogenous data structures and formats.

In this blog, we're taking an example of a complex JSON file as shown in the screenshot below. You can access the JSON file from this GitHub page to try the steps below.

This JSON has keys and values in two different arrays. To convert this JSON document into the relational schema as shown below, we will use the approach of extracting the 'structure' object into one table and the 'kpi_data' object into another table and then join the two tables using the GUID.

Step 1: Use ADX Ingestion Hub (also called One Click Ingestion), to upload sample data and let ADX understand the schema of the JSON document. With multi-level JSON, you can extract multiple objects within the JSON document. However, in the example above, this will not work and hence we will write KQL in step 2.

Step 2: Since there are 2 nested JSON arrays, we will use mv-expand operator to expand these dynamic arrays. We will first use the 'project' operator to select the columns of interest and then apply mv-expand on the column containing nested arrays.

SampleTable1 | project Timestamp = from, Level1_id = structure.id, Level1_Name=structure.name, Level1_kpi_type=structure.kpi_type, kpi_structure=structure.kpi_structure | mv-expand kpi_structure

Upon executing these KQL statements, we see the following output:

Step 3: We will apply the mv-expand operator again to expand the next array. We're also using the 'extend' operator to extract columns from the expanded JSON arrays.

SampleTable1 | project Timestamp = from, Level1_id = structure.id, Level1_Name=structure.name, Level1_kpi_type=structure.kpi_type, kpi_structure=structure.kpi_structure | mv-expand kpi_structure | extend Level2_data_type = kpi_structure.data_type, Level2_Id = kpi_structure.id, Level2_kpi_type = kpi_structure.kpi_type, Level2_name = kpi_structure.name, new_kpi_structure=kpi_structure.kpi_structure | mv-expand new_kpi_structure | extend Level3_data_type = new_kpi_structure.data_type, Level3_Id = tostring(new_kpi_structure.id), Level3_name = new_kpi_structure.name, Level3_Unit = new_kpi_structure.unit | project-away kpi_structure, new_kpi_structure

The output of executing these statements is:

Step 4: Use the mv-expand operator again to expand the array inside the 'kpi_data' JSON object

SampleTable1 | mv-expand kpi_data | project kpi_values = kpi_data.values

The output of executing these statements is:

Step 5: In this JSON document, the value (in this case, 18806s) is referenced using a GUID key. Since GUID key can be different for each value, we will use the bag_keys() function to transform this JSON structure into a column of keys and values.

SampleTable1 | mv-expand kpi_data | project kpi_values = kpi_data.values | extend Level3_Id = tostring(bag_keys(kpi_values)[0]) | extend key_value = kpi_values[Level3_Id]

The output of executing these statements is:

Step 6: We will use the mv-apply operator to execute some of the statements in step 5 on each row that can be present in the 'kpi_data' JSON object.

SampleTable1 | mv-expand kpi_data | project kpi_values = kpi_data.values | mv-apply kpi_values on ( extend Level3_Id = tostring(bag_keys(kpi_values)[0]) | project Level3_Id, key_value = kpi_values[Level3_Id] )

In the case of a single row, these statements generate the same output as Step 5. However, in the case of multiple rows, you will get the desired output for each row as shown below.

Step 7: Join the two tables created in Step 3 (Keys) and Step 6 (Values) to retrieve a complete table containing key-value pairs that can be easily queried. After the join, we divided the value column into 3 columns having distinct data types Integer, Boolean, and String. Doing this will allow the data analysts and scientists to run the calculation, aggregation, and other queries more effectively without having to worry about data type conversion at each stage.

let Keys = SampleTable1 | project Timestamp = from, Level1_id = structure.id, Level1_Name=structure.name, Level1_kpi_type=structure.kpi_type, kpi_structure=structure.kpi_structure | mv-expand kpi_structure | extend Level2_data_type = kpi_structure.data_type, Level2_Id = kpi_structure.id, Level2_kpi_type = kpi_structure.kpi_type, Level2_name = kpi_structure.name, new_kpi_structure=kpi_structure.kpi_structure | mv-expand new_kpi_structure | extend Level3_data_type = new_kpi_structure.data_type, Level3_Id = tostring(new_kpi_structure.id), Level3_name = new_kpi_structure.name, Level3_Unit = new_kpi_structure.unit; let Values = SampleTable1 | mv-expand kpi_data | project kpi_values = kpi_data.values | mv-apply kpi_values on ( extend Level3_Id = tostring(bag_keys(kpi_values)[0]) | project Level3_Id, key_value = kpi_values[Level3_Id] ); Keys | join kind = leftouter Values on $left.Level3_Id==$right.Level3_Id | extend int_val = iff(tostring(Level3_data_type) in ("INTEGER","FILL_LEVEL","COUNT"), key_value.integer_value,0), str_val=iff(tostring(Level3_data_type) == "STRING", key_value.string_value,""), bool_val=iff(tostring(Level3_data_type) == "BOOLEAN", iff(isempty(tobool(key_value.boolean_value)),false,tobool(key_value.boolean_value)),false), duration=iff(tostring(Level3_data_type) == "DURATION", tolong(trim("s",tostring(key_value.duration_value))),0) | project Timestamp, Level1_Name, Level1_kpi_type, Level2_name, Level3_name, Level3_data_type, Level3_Unit, int_val, str_val,bool_val,duration

Possible next steps:

You can store these KQL statements as a User-defined Function that can then be used by others in your organization
Once saved as a User-defined Function, it can also be referenced in an Update Policy that will then execute this function on every incoming JSON document in your source table. Update Policy is a lightweight Extract-Load-Transform capability of Azure Data Explorer that can help you transform raw messages into curated data.

In summary, with Kusto Query Language, you can extract data from a complex JSON document containing nested arrays and objects using only a few lines of code.

Our team publishes blog(s) regularly and you can find all these blogs here: https://aka.ms/synapsecseblog

For a deeper level of understanding of Synapse implementation best practices, please refer to our Success by Design (SBD) site: https://aka.ms/Synapse-Success-By-Design

What’s new in SynapseML v0.11

mhamilton723 — Tue, 25 Apr 2023 17:23:47 GMT

We are pleased to announce SynapseML v0.11, a new version of our open-source distributed machine learning library that simplifies and accelerates the development of scalable AI. In this release, we are excited to introduce many new features from the past year of developments well as many bug fixes and improvements. Though this post will give a high-level overview of the most salient new additions, curious readers can check out the full release notes for all of the new additions.

OpenAI Language Models and Embeddings

A new release wouldn’t be complete without joining the large language model (LLM) hype train and SynapseML v0.11 features a variety of new features that make large-scale LLM usage simple and easy. In particular, SynapseML v0.11 introduces three new APIs for working with foundation models: `OpenAIPrompt`, ` OpenAIEmbedding`, and `OpenAIChatCompletion`. The `OpenAIPrompt` API makes it easy to construct complex LLM prompts from columns of your dataframe. Here’s a quick example of translating a dataframe column called “Description” into emojis.

from synapse.ml.cognitive.openai import OpenAIPrompt emoji_template = """ Translate the following into emojis Word: {Description} Emoji: """ results = (OpenAIPrompt() .setPromptTemplate(emoji_template) .setErrorCol("error") .setOutputCol("Emoji") .transform(inputs))

This code will automatically look for a database column called “Description” and prompt your LLM (ChatGPT, GPT-3, GPT-4) with the created prompts. Our new OpenAI embedding classes make it easy to embed large tables of sentences quickly and easily from your Apache Spark clusters. To learn more, see our docs on using OpenAI embeddings API and the SynapseML KNN model to create an LLM-based vector search engine directly on your spark cluster. Finally, the new OpenAIChatCompletion transformer allows users to submit large quantities of chat-based prompts to ChatGPT, enabling parallel inference of thousands of conversations at a time. We hope you find the new OpenAI integrations useful for building your next intelligent application.

Simple Deep Learning

SynapseML v0.11 introduces a new Simple deep learning package that allows for the training of custom text and deep vision classifiers with only a few lines of code. This package integrates the power of distributed deep network training with PytorchLightning with the simple and easy APIs of SynapseML. The new API allows users to fine-tune visual foundation models from torchvision as well as a variety of state-of-the-art text backbones from HuggingFace.

Here’s a quick example showing how to fine-tune custom vision networks:

from synapse.ml.dl import DeepVisionClassifier train_df = spark.createDataframe([ ("PATH_TO_IMAGE_1.jpg", 1), ("PATH_TO_IMAGE_2.jpg", 2) ], ["image", "label"]) deep_vision_classifier = DeepVisionClassifier( backbone="resnet50", num_classes=2, batch_size=16, epochs=2, ) deep_vision_model = deep_vision_classifier.fit(train_df)

Keep an eye out with upcoming new releases of SynapseML featuring additional simple deep-learning algorithms that will make it easier than ever to train and deploy models at scale.

LightGBM v2

LightGBM is one of the most used features of SynapseML and we heard your feedback on better performance! SynapseML v0.11 introduces a completely refactored integration between LightGBM and Spark, called LightGBM v2. This integration aims for high performance by introducing a variety of new streaming APIs in the core LightGBM library to enable fast and memory-efficient data sharing between spark and LightGBM. In particular, the new “Streaming execution mode” has a >10x lower memory footprint than earlier versions of SynapseML yielding fewer memory issues and faster training. Best of all, you can use the new mode by just passing a single extra flag to your existing LightGBM models in SynapseML.

ONNX Model Hub

SynapseML supports a variety of new deep learning integrations with the ONNX runtime for fast, hardware-accelerated inference in all of the SynapseML languages (Scala, Java, Python, R, and .NET). In version 0.11 we add support for the new ONNX model hub, which is an open collection of state-of-the-art pre-trained ONNX models that can be quickly downloaded and embedded into spark pipelines. This allowed us to completely deprecate and remove our old dependence on the CNTK deep learning library.

To learn more about how you can embed deep networks into Spark pipelines, check out our ONNX episode in the new SynapseML video series:

Causal Learning

SynapseML v0.11 introduces a new package for causal learning that can help businesses and policymakers make more informed decisions. When trying to understand the impact of a “treatment” or intervention on an outcome, traditional approaches like correlation analysis or prediction models fall short as they do not necessarily establish causation. Causal inference aims to overcome these shortcomings by bridging the gap between prediction and decision-making. SynapseML's causal learning package implements a technique called "Double machine learning", which allows us to estimate treatment effects without data from controlled experiments. Unlike regression-based approaches, this approach can model non-linear relationships between confounders, treatment, and outcome. Users can run the DoubleMLEstimator using a simple code snippet like the one below:

from pyspark.ml.classification import LogisticRegression from synapse.ml.causal import DoubleMLEstimator dml = (DoubleMLEstimator() .setTreatmentCol("Treatment") .setTreatmentModel(LogisticRegression()) .setOutcomeCol("Outcome") .setOutcomeModel(LogisticRegression()) .setMaxIter(20)) dmlModel = dml.fit(dataset)

For more information, be sure to check out Dylan Wang's guided tour of the DoubleMLEstimator on the SynapseML video series:

Vowpal Wabbit v2

Finally, SynapseML v0.11 introduces Vowpal Wabbit v2, the second-generation integration between the Vowpal Wabbit (VW) online optimization library and Apache Spark. With this update, users can work with Vowpal wabbit data directly using the new “VowpalWabbitGeneric” model. This makes working with Spark easier for existing VW users. This more direct integration also adds support for new cost functions and use cases including “multi-class” and “cost-sensitive one against all” problems. The update also introduces a new progressive validation strategy and a new Contextual Bandit Offline policy evaluation notebook to demonstrate how to evaluate VW models on large datasets.

Conclusion

In conclusion, we are thrilled to share the new SynapseML library with you with you and hope you will find that it simplifies your distributed machine learning pipelines. This blog only covered the highlights, so be sure to check out the full release notes for all the updates and new features. Whether you are working with large language models, training custom classifiers, or performing causal inference, SynapseML makes it easier and faster to develop and deploy machine learning models at scale.

Learn more

Synapse Database Templates for airlines & travel services plus seven industries are now GA

DataModels — Fri, 21 Apr 2023 21:00:00 GMT

In response to continued enthusiastic adoption of the twenty previously published Synapse Database Templates (SDTs), we’re pleased to announce today that we are releasing two Industry Data Models (IDMs), for Airlines and for Travel Services, that have not previously been published as SDTs, along with enhanced versions of seven previously published SDTs.

The IDM for Airlines is a comprehensive data model that addresses the typical data requirements of organizations operating one or more airlines for passengers and/or cargo. The IDM for Travel Services is a comprehensive data model that addresses the typical data requirements of organizations providing booking services and/or hospitality services for airlines, hotels, car rentals, cruises, and vacation packages.

We have released two new SDTs for Travel Services and Airlines, in addition to updated versions of the previously released SDTs for Automotive Industries, Consumer Packaged Goods, Healthcare Insurance, Healthcare Service Providers, Manufacturing, Retail, and Utilities. All twenty-two SDTs can now be accessed in Azure Synapse, either through the Gallery or by creating a new lake database from the Data tab and selecting '+ Table', and then 'From template'.

We have also continued to expand the scope and content of the previously published seven SDTs by releasing new versions. Additionally, we are working to ensure that our customers and partners who use selected Microsoft solution offerings can fully integrate their application data into relevant subsets of a data lake created using the new versions of SDTs.

The new versions for Healthcare Insurance and Healthcare Providers have been fully mapped from Microsoft’s Industry Cloud for Healthcare to ensure customers can land all their data from our Healthcare Cloud into the relevant subset of a data lake published using the applicable SDT to make it easier for our customers to land data from Microsoft’s Healthcare Cloud, along with data from their organization’s many other applications and data sources, into a comprehensive integrated and harmonized lake database deployed using the SDTs. We have also expanded the IDMs for healthcare to enable customers to provision the specific clinical data content required to support OMOP consumption in the gold layer of their Azure data lake if that is of interest.

We’ve expanded the IDM for Retail to accommodate smart store and shopper journey data generated by solutions from key Microsoft smart store partners such as AiFI in support of current and anticipated future smart store analytics use cases. Similarly, the expanded IDM for Utilities provides full data support for data sourced from Microsoft’s 24x7 Sustainability offering. We’ve also expanded the IDMs for Automotive, Consumer Packaged Goods, and Manufacturing to provide full alignment with the recently announced Microsoft Supply Chain Center solution offering. Each of the SDTs that is part of this latest release also benefits from enhanced and expanded support for readings and data streams acquired from leading edge IoT devices and sensors used in each of those industries.

SDTs contain coverage of many different “business areas”, some very industry-specific and others cross-industry, that together comprise each of these very large IDMs. For example, in addition to comprehensive industry-specific business areas (such as Reservations, Ticketing, and Cargo and Departure Control Services for the Airline industry), most SDTs also include cross-industry business areas such as Accounting & Financial Reporting, Human Resources, Inventory, and an Emissions business area which provides support for data used to report greenhouse gas emissions (including scope 1, scope 2, and scope 3 emissions), combining to provide unparalleled coverage of the data typically found in the integrated data estates of large organizations in specific industries, thereby providing comprehensive best practices based accelerators as our customers continue to move their enterprise data estates to the cloud.

To learn more, check out the following:

Tags: Database templates Industry Lake databases

Azure Synapse Analytics Blog articles

Preview: Azure Synapse Runtime for Apache Spark 3.5

Enhancing Team Collaboration in Azure Synapse Analytics using a Git Branching Strategy – Part 2 of 3

Introduction

Prerequisites:

Defining Azure DevOps Environments

Creating the Pipeline for Synapse Code Deployment

Configuring your Pipeline

Configuring the Synapse Deployment Task

Conclusion

Enhancing Team Collaboration in Azure Synapse Analytics using a Git Branching Strategy – Part 1 of 3

Introduction

Start elevating your collaborative work in Synapse Studio, by implementing a simple and effictive Git branching strategy

Start at the feature branch level...

…and end at the Environment branch level!

Conclusion

Runtime 1.1, based on Apache Spark 3.3, will be retired and disabled as of March 31, 2025

We're moving!

Upgrade to Azure Synapse runtimes for Apache Spark 3.4 & previous runtimes deprecation

ADF\Synapse Analytics - Replace Columns names using Rule based mapping in Mapping data flows

Interpreting Script activity output json with Azure Data Factory\Synapse analytics

Synapse Connectivity Series Part #4 - Advanced network troubleshooting and network trace analysis

Index

0 - Scoping

1 - How to CAPTURE a network trace

1.1 - Input

1.1.1 - Capture filter

1.2 - Output

1.2.1 - Reproducible error?

1.2.2 - Transient error?

1.3 - Options

2 - Capture Alternatives

2.1 - NETSH - Native on Windows.

NETSH Short capture

NETSH Circular capture

2.2 - TCPDUMP - Native on Linux / MacOs

TCPDUMP Short capture

TCPDUMP Filter interface

3 - Tool configuration - Wireshark

Date time format to UTC

Source and destination ports

4 - Analysis

4.1 - SCENARIO 1 - Success path

4.1.1 - Simple sample on Wireshark

4.1.2 - Direction of the packages

4.1.3 - Redirect

4.2 - SCENARIO 2 - The server was not found or was not accessible

4.3 - SCENARIO 3 - Redirect port 11xxx range closed

Troubleshooting

Solution

4.4 - SCENARIO 4 - Connection timeout (Middle connection) / PreLogin handshake

4.4.1 - SCENARIO 4.1 - TLS Blocked

4.4.2 - SCENARIO 4.2 - Long connection

4.4.3 - SCENARIO 4.3 - Azure firewall

Troubleshooting

Solution

4.5 - SCENARIO 5 - Connection Dropped

Troubleshooting

Another sample

Summary

Boost your CICD automation for Synapse SQL Serverless by taking advantage of SSDT and SqlPackage CLI

Introduction

Pre-Requisites

TOC

Step 1: Adding a new database project to Visual Studio and importing the serverless pool

Step 2: Taking advantage of SQL CMD Variables in your Visual Studio code

Step 3: Integrating your Visual Studio solution with a Git Repository

Step 4: Creating a DevOps Pipeline and building the Dacpac file

Step 5: Creating a Release Pipeline and deploying the Dacpac

Starting the Lab

Step 1: Adding a new database project to Visual Studio and importing the serverless pool

Step 2: Taking advantage of SQL CMD Variables in your Visual Studio code

Step 3: Integrating your Visual Studio solution with a Git Repository

Step 4: Creating a DevOps Pipeline and building the Dacpac file

Step 5: Creating a Release Pipeline and deploying the Dacpac

Conclusion

Metadata-Based Ingestion in Synapse with Delta Lake

Missing Fields Added to Dedicated SQL pool Diagnostic Settings Logs

Using Azure DevOps with Synapse Workspaces to create hot fixes in production environments

Azure Synapse MVP Corner - March 2023