Azure synapse Analytics Tutorial

by

A retail company I worked with had sales data spread across three places: an on-premises SQL Server, a folder of CSV exports, and a reporting database that timed out every Monday morning. The analysts pulled files by hand and built spreadsheets. Nobody trusted the numbers, and the finance team had no idea what the reporting servers cost.

We fixed it by moving the data into a single platform. That platform was Azure Synapse Analytics, Microsoft’s unified analytics service. It combines SQL querying, Apache Spark, data pipelines, and data lake access in one workspace. This Azure Synapse Analytics tutorial follows the same path we used, with the security and cost decisions we learned the hard way.

By the end, you will know how to plan, deploy, secure, query, monitor, and cost-control a Synapse workspace that a real team can use.

Azure Synapse Analytics Tutorial: What You Are Building

Here is the target design. It is a small but production-minded analytics platform for a retail sales team.

  • A resource group that holds every related resource.
  • An Azure Data Lake Storage Gen2 account for raw and curated files.
  • A Synapse workspace with a managed virtual network.
  • A serverless SQL pool for ad hoc queries over files in the lake.
  • A dedicated SQL pool for heavy, repeated reporting workloads.
  • Microsoft Entra ID authentication with role-based access control (RBAC).
  • Azure Key Vault for any secrets that must exist.
  • Azure Monitor alerts and a budget alert.

Synapse has several compute engines, and each one bills differently. A serverless SQL pool charges by the amount of data your queries process. A dedicated SQL pool is a provisioned data warehouse that bills by the hour while it runs, measured in data warehouse units (DWUs). Apache Spark pools bill for the time the cluster is up.

That difference drives most of your cost decisions. Serverless suits exploration and light reporting. Dedicated pools suit predictable, high-concurrency workloads where you need consistent speed.

If you are also weighing Synapse against other tools, read this comparison of Azure Synapse Analytics vs. Azure Data Factory. For a Spark-focused alternative, see Azure Synapse Analytics vs. Databricks. If you are new to the platform overall, start with this Microsoft Azure tutorial for beginners.

Pro Tip: In my experience, teams pick a dedicated pool on day one because it sounds more “enterprise.” Start with serverless, measure real query patterns for a few weeks, and add a dedicated pool only when the numbers justify it.

Plan the Azure Architecture First

Before you click anything, make four decisions. Changing them later is painful.

Region. Choose one region for your workspace, storage account, and dependent services. Cross-region data movement adds latency and egress charges. Also check that your region supports the features you need, such as availability zones for your storage redundancy option.

Naming and tagging. Use a predictable pattern like rg-sales-analytics-prod and synw-sales-prod-eus. Tag every resource with an owner, cost center, and environment. If you need a standard, this guide to Microsoft Azure naming conventions is a good reference, and this one explains what Azure tags are and how to apply them.

Environment separation. Keep dev, test, and production in separate resource groups, and ideally separate subscriptions. A subscription is a billing and governance boundary. Separate subscriptions let you apply different budgets and policies to each environment. If you are designing at that level, this overview of the Azure landing zone shows how larger organizations structure it.

Network exposure. Decide now whether the workspace will be reachable from the public internet. For anything with customer or financial data, I choose a managed virtual network and private endpoints, and I disable public access after setup is verified.

Pro Tip: I always write these four decisions in a one-page design note and attach it to the resource group as a tag or wiki link. Six months later, nobody remembers why the workspace is in Central US, and that page saves an argument.

Create the Resource Group and Data Lake

A resource group is a logical container for related Azure resources. Keeping the workspace and its storage together makes access control, cost tracking, and cleanup much easier. If you need a refresher, follow this walkthrough on how to create a resource group in Azure, and review these resource group best practices.

Use Azure CLI to create the group:

bashaz group create \
  --name rg-sales-analytics-prod \
  --location eastus \
  --tags owner=data-team costcenter=finance environment=prod

This command creates a resource group named rg-sales-analytics-prod in East US. The --tags parameter attaches ownership and cost-center metadata so cost analysis can break spending down by team.

Synapse needs a default storage account. It must be an Azure Data Lake Storage Gen2 account, which is a Storage account with the hierarchical namespace turned on. That feature gives you real folders and fine-grained access control lists, and Synapse relies on it. For the differences, see Azure Storage account vs. Data Lake Gen2. For a full portal walkthrough, see how to create Azure Data Lake Storage Gen2.

az storage account create \
  --name <storage-account-name> \
  --resource-group rg-sales-analytics-prod \
  --location eastus \
  --sku Standard_ZRS \
  --kind StorageV2 \
  --hns true \
  --allow-blob-public-access false \
  --min-tls-version TLS1_2

Here is what the important parameters do:

  • --hns true enables the hierarchical namespace, which makes the account a Data Lake Storage Gen2 account. You cannot skip this for Synapse.
  • --sku Standard_ZRS uses zone-redundant storage. Your data is copied across availability zones in the region. That protects against a single datacenter failure, but it costs more than locally redundant storage.
  • --allow-blob-public-access false blocks anonymous access to containers. This prevents the classic “we exposed a container by mistake” incident.
  • --min-tls-version TLS1_2 rejects older, weaker connections.

Next, create a file system (container) for the workspace:

az storage fs create \
  --name synapse-fs \
  --account-name <storage-account-name> \
  --auth-mode login

The --auth-mode login option uses your Microsoft Entra ID identity instead of an account key. Get in the habit now. Storage account keys grant full access and are easy to leak.

Pro Tip: I create separate containers for raw, curated, and sandbox zones from the start. Analysts get write access only to sandbox, which stops accidental overwrites of source data.

Deploy the Synapse Workspace

The Synapse workspace is the top-level resource. It gives you Synapse Studio (the web interface), the SQL engines, Spark, and pipelines. For a portal-based walkthrough, see this guide on how to create Azure Synapse Analytics.

The workspace requires a SQL administrator login. Do not type a password into a script and commit it. Read it from your terminal or pull it from a vault:

read -s -p "SQL admin password: " SQL_ADMIN_PASSWORD

az synapse workspace create \
  --name synw-sales-prod-eus \
  --resource-group rg-sales-analytics-prod \
  --storage-account <storage-account-name> \
  --file-system synapse-fs \
  --sql-admin-login-user sqladminuser \
  --sql-admin-login-password "$SQL_ADMIN_PASSWORD" \
  --location eastus \
  --enable-managed-virtual-network true \
  --prevent-data-exfiltration true

Here is what this does:

  • read -s prompts for the password without echoing it, so it never lands in your shell history or a script file.
  • --storage-account and --file-system link the workspace to your data lake.
  • --enable-managed-virtual-network true places Synapse compute inside a Microsoft-managed network. This is the foundation for private connectivity.
  • --prevent-data-exfiltration true limits outbound connections to approved targets only. Even if an account is compromised, data cannot easily be sent to an unapproved destination. This setting must be chosen at creation time, so decide early.

After deployment, I switch the workspace to Microsoft Entra-only authentication so the SQL password cannot be used at all:

az synapse ad-only-auth enable \
  --workspace-name synw-sales-prod-eus \
  --resource-group rg-sales-analytics-prod

This command disables SQL authentication on the workspace’s SQL pools. Every login must then come through Microsoft Entra ID, which supports multifactor authentication and conditional access. Test that your pipelines and tools work with Entra sign-in before you enable this in production.

Pro Tip: I once watched a team lose a full day because they enabled Entra-only authentication before checking a legacy reporting tool that used SQL logins. Inventory every connecting application first, then flip the switch.

Repeatable Deployment with Bicep

Clicking through the portal works once. It fails when you need identical dev and prod environments. Infrastructure as code fixes that, because the same template produces the same result every time, which removes manual configuration drift.

textparam workspaceName string
param location string = resourceGroup().location
param storageAccountName string
param fileSystemName string = 'synapse-fs'
param sqlAdminUser string
@secure()
param sqlAdminPassword string

resource workspace 'Microsoft.Synapse/workspaces@2021-06-01' = {
  name: workspaceName
  location: location
  identity: {
    type: 'SystemAssigned'
  }
  properties: {
    defaultDataLakeStorage: {
      accountUrl: 'https://${storageAccountName}.dfs.${environment().suffixes.storage}'
      filesystem: fileSystemName
    }
    sqlAdministratorLogin: sqlAdminUser
    sqlAdministratorLoginPassword: sqlAdminPassword
    managedVirtualNetwork: 'default'
    managedVirtualNetworkSettings: {
      preventDataExfiltration: true
    }
    publicNetworkAccess: 'Enabled'
  }
}

This Bicep template declares a Synapse workspace with a system-assigned managed identity, a link to the data lake, and a managed virtual network with exfiltration protection. The @secure() decorator keeps the password out of deployment logs. In a pipeline, pass it from Key Vault instead of a variable file. Keep publicNetworkAccess as Enabled until private endpoints are tested, then change it to Disabled.

For automating this in a release process, see this guide on an Azure DevOps CI/CD pipeline step by step and how to create a pipeline in Azure DevOps.

Pro Tip: I keep one parameter file per environment and let only the pipeline service connection deploy to production. Nobody, including me, deploys prod by hand.

Secure Identity, RBAC, and Networking

Most Synapse incidents I have seen come from access problems, not from Azure itself. Fix identity first.

Grant the Workspace Access to Storage

The workspace has a managed identity, an identity Azure creates and manages so services can authenticate without stored passwords. Read this explanation of what a managed identity in Azure is if the term is new.

Give that identity the Storage Blob Data Contributor role on the storage account. Without it, Synapse queries fail with permission errors even when you are an owner.

az role assignment create \
  --assignee-object-id <workspace-managed-identity-object-id> \
  --assignee-principal-type ServicePrincipal \
  --role "Storage Blob Data Contributor" \
  --scope /subscriptions/<subscription-id>/resourceGroups/rg-sales-analytics-prod/providers/Microsoft.Storage/storageAccounts/<storage-account-name>

This assigns a data-plane role to the workspace identity, scoped to one storage account instead of the whole subscription. Narrow scope is the heart of least privilege.

Assign Synapse Roles to People

Synapse has its own role system inside Synapse Studio, separate from Azure RBAC. It is worth understanding both. Start with the basics of Azure RBAC and the difference between Azure roles and Entra roles.

az synapse role assignment create \
  --workspace-name synw-sales-prod-eus \
  --role "Synapse SQL Administrator" \
  --assignee <entra-group-object-id>

This gives an Entra group the Synapse SQL Administrator role. I always assign roles to groups, not individual users. When someone leaves the team, you remove them from the group and access disappears everywhere.

Do not hand out Synapse Administrator or subscription Owner casually. Analysts usually need only Synapse User plus read access to specific data-lake folders. Test with a non-administrator account before you call the setup finished.

Lock Down the Network

Public workspace endpoints are convenient and risky. Use private endpoints, which give you a private IP address inside your virtual network for a supported Azure service. Learn the basics in what an Azure virtual network is, then follow how to