To understand Synapse, you first need to understand the problems it solves and the vocabulary of modern data platforms.

Two kinds of database work

Most business applications use databases for transactions: placing an order, updating an address, recording a payment. This is called online transaction processing, or OLTP. It involves many small, fast reads and writes affecting a few rows at a time. Analytics is different. A question such as “What were our total sales by region and product category for each of the last five years?” must read millions or billions of rows and summarize them. This is online analytical processing, or OLAP. The two workloads pull a database in opposite directions, so serious organizations keep them in separate systems.

The data warehouse

A data warehouse is a central database designed for analysis. It collects data from many source systems, cleans it, organizes it into a consistent structure, and keeps history so that trends over time can be studied. Warehouses typically use a star schema: a central fact table that holds measurable events, such as sales lines, surrounded by dimension tables that describe the context, such as customer, product, store, and date. This layout makes summary questions simple to write and fast to answer.

The data lake

A data lake is a large, inexpensive storage area that holds data in its original form: spreadsheets, log files, images, JSON messages, and database exports. It does not require you to decide the structure in advance. In Azure, the lake is usually built on Azure Data Lake Storage Gen2, which adds folders, permissions, and speed on top of ordinary Azure storage. Lakes are flexible and cheap, but without discipline they turn into a swamp where nobody trusts or finds anything.

The lakehouse

A lakehouse combines both ideas. It keeps the data in open files in the lake, adds a transaction layer such as Delta Lake so that updates and deletes are reliable, and lets both SQL and programming engines work on the same files. The aim is one copy of the data, many tools, and warehouse-like trust on lake-like storage.

ETL and ELT

Moving data into analytics is known as ETL, short for extract, transform, load, where data is reshaped before being stored. ELT reverses the last two steps: data is loaded first and transformed inside the powerful analytical engine. Cloud platforms favor ELT because storage is cheap and the engines are strong, which is why you will hear the word ELT often in Synapse discussions.

Synapse brings several analytics engines and data integration tools into one workspace, so teams do not have to stitch separate products together.

The core idea

Azure Synapse Analytics is a managed analytics service that combines enterprise data warehousing, big data processing, data integration, and exploration under a single workspace. Microsoft introduced it as the evolution of Azure SQL Data Warehouse, adding a shared studio, Spark processing, serverless querying, and built-in pipelines. The promise was a single place where a data engineer can ingest data, an analyst can query it with SQL, a scientist can run notebooks, and a report author can connect Power BI, without moving data between tools.

The main engines

The workspace

Everything lives in a Synapse workspace, an Azure resource that holds your pools, pipelines, notebooks, scripts, and connections, together with a default Data Lake Storage Gen2 account. You work through Synapse Studio, a browser-based interface with hubs for data, development, integration, monitoring, and management. Security, networking, and identities are configured at the workspace level, which is why workspace design is one of the first decisions in any project.

A responsible guide must state plainly where the product stands today, because that affects every investment decision you make.

The strategic picture

Microsoft Fabric is Microsoft’s newer, software-as-a-service analytics platform that unifies data engineering, warehousing, real-time analytics, data science, and Power BI around a single storage layer called OneLake. Industry guidance published in mid-2026 describes Azure Synapse Analytics as a supported, production-grade platform that is effectively in maintenance mode, with new investment concentrated on Fabric. As of that guidance, Microsoft had not announced an end-of-life date for Synapse, and existing dedicated SQL pools, serverless SQL pools, and Spark pools continue to receive operational support and security patches.

What is Fabric-only

Several newer capabilities are described as available only in Fabric, not Synapse. Examples include Direct Lake mode for Power BI, which reads lake files directly without importing them, OneLake mirroring of operational databases, built-in Copilot assistance, and certain automation and execution-engine features. Check Microsoft’s current documentation for the latest list, because it changes frequently.

When staying on Synapse is sensible

Why learn Synapse then?

Three reasons. First, large numbers of production systems run on it and will for years, so engineers need to operate and improve them. Second, the concepts you learn here, such as massively parallel processing, distribution design, lakehouse layering, and workload management, carry directly into Fabric and other platforms. Third, any migration plan starts with understanding what you are migrating from. This guide teaches Synapse in full, and later chapters return to the migration question honestly.

The big picture of how the pieces fit together makes every later detail easier to place.

The layers

A Synapse deployment has four main layers. At the bottom is storage: the Data Lake Storage Gen2 account for the lake, plus the internal storage that holds dedicated SQL pool tables. Above it are the compute engines: dedicated SQL pools, serverless SQL, and Spark pools. Next comes integration: pipelines, triggers, and linked connections to outside systems. At the top is the experience layer: Synapse Studio, along with connections for Power BI, Azure Machine Learning, and Microsoft Purview.

flowchart TD
  SRC["Sources: databases, files, SaaS apps, streams"] --> PIPE["Pipelines and Data Flows"]
  PIPE --> LAKE["Data Lake Storage Gen2: Raw, Curated, Serving"]
  LAKE --> SPARK["Spark Pools"]
  LAKE --> SRVL["Serverless SQL Pool"]
  LAKE --> DSQL["Dedicated SQL Pool"]
  SPARK --> LAKE
  DSQL --> BI["Power BI and Reports"]
  SRVL --> BI
  SPARK --> ML["Machine Learning"]
  STUDIO["Synapse Studio"] -. manages .-> PIPE
  STUDIO -. manages .-> SPARK
  STUDIO -. manages .-> DSQL
        

Compute separated from storage

A guiding principle of modern analytics is to separate compute from storage. Your data stays in storage while compute engines are created, resized, paused, or deleted as needed. Dedicated SQL pools follow this by keeping data in distributed storage independent of the compute size, so you can scale compute up for the monthly close and pause it overnight without losing the tables. This is what makes cloud warehouses economical compared with old fixed-size appliances.

Control plane and data plane

The control plane handles management actions such as creating a pool or granting access, and runs through Azure Resource Manager and the workspace service. The data plane carries your queries and files. Keeping them separate means that scaling operations and permissions do not interfere with query traffic, and that a busy query workload does not block administration.

The dedicated SQL pool is the heart of Synapse for serious warehousing. Its power comes from dividing work across many machines.

The control node and compute nodes

A dedicated SQL pool has one control node and many compute nodes. The control node is the front door: it receives your queries, parses them, creates an execution plan that spreads the work, and sends instructions to the compute nodes. The compute nodes do the heavy lifting in parallel, each processing its own slice of the data, and the results are combined and returned. As you buy a larger pool, you get more compute nodes and the same query finishes faster because more workers share the job.

Sixty distributions

Behind the scenes, the data of every table is split into 60 pieces called distributions, regardless of pool size. A small pool assigns all sixty to a single compute node. A very large pool gives each of sixty compute nodes exactly one. Scaling the pool changes how many nodes share the sixty pieces, not how the data is cut. This fixed number matters for design: it explains why tables need enough rows to make each distribution worthwhile and why uneven data creates slow queries.

flowchart LR
  Q["Query from Client"] --> CN["Control Node: plan and coordinate"]
  CN --> C1["Compute Node 1"]
  CN --> C2["Compute Node 2"]
  CN --> CN3["Compute Node N"]
  C1 --> D1["Distributions 1 to 20"]
  C2 --> D2["Distributions 21 to 40"]
  CN3 --> D3["Distributions 41 to 60"]
  D1 --> ST["Shared Storage"]
  D2 --> ST
  D3 --> ST
        

Data warehouse units

The size of a dedicated SQL pool is expressed in data warehouse units, abbreviated DWU, a bundled measure of compute, memory, and input and output capacity. The current generation uses sizes written with a lowercase c, starting small and growing to very large. More units mean more compute nodes, more memory, and higher concurrency capacity. You can scale up or down in minutes, though the pool is briefly offline during the change.

The data movement service

Joining two tables is easy when the matching rows already live in the same distribution. When they do not, the pool must reshuffle rows across nodes before it can join them. A component called the data movement service performs these reshuffles. Data movement is the single most expensive operation in a distributed warehouse, so most tuning advice boils down to one goal: arrange the data so that the joins you run most often need little or no movement.

In a dedicated SQL pool, table design decisions made on day one determine performance for years.

Three distribution methods

When you create a table, you choose how its rows are spread across the sixty distributions. Each method suits a different kind of table.

Choosing a good hash key

A good hash column has many distinct values, is spread evenly, appears often in joins and grouping, and is not used in filters that pick a single value. A customer identifier is usually better than a date column. A date column is risky because all of today’s rows hash to one distribution while the other fifty-nine sit idle. If the largest tables in your joins share a distribution key, matching rows are already together and the join needs no movement.

Columnstore: the default for large tables

Dedicated SQL pools store large tables by default as clustered columnstore. Instead of saving each row together, columnstore saves each column separately and compresses it heavily. Analytical queries usually need only a few columns out of many, so the engine reads far less data. Columnstore works best when row groups contain about a million rows. Because each of the sixty distributions builds its own row groups, a table needs on the order of sixty million rows before columnstore reaches its full efficiency, and smaller tables often do better with other storage choices.

Heap and rowstore indexes

For small tables and temporary staging, a heap with no index loads quickest. A clustered or nonclustered rowstore index helps queries that fetch a handful of rows by key from a small table. Use them sparingly, since every index adds load time and storage.

Partitioning

Partitioning slices a table into sections, commonly by date, so that maintenance and queries can focus on a part. A well-chosen partition scheme lets you remove or switch out old data instantly, and lets queries that filter on date skip irrelevant sections. Remember that partitioning multiplies by the sixty distributions: a table with a hundred partitions has six thousand physical pieces, which can starve each piece of rows and damage columnstore quality. Use fewer, larger partitions than you would on a single-server database.

Statistics

The planner relies on statistics, summaries of data distribution, to choose good plans. In dedicated pools, statistics need care: they are best created on columns used in joins, filters, and grouping, and refreshed after large loads. Out-of-date statistics lead to poor plans and extra data movement.

A warehouse is only as good as its data. Loading well means loading fast, loading safely, and loading in a way that keeps queries healthy.

Why row-by-row loading is slow

Inserting single rows through the control node is a bottleneck because every row passes through one place. Dedicated SQL pools are built so that the compute nodes read files directly from storage in parallel. Bulk-loading mechanisms, namely the COPY statement and PolyBase external tables, let every node read its own slice of the files at once, which can load billions of rows in minutes instead of hours.

The staging pattern

Loading guidelines

When many people and jobs share a pool, someone must decide who gets resources and when. Workload management is that referee.

Concurrency and slots

A dedicated SQL pool can run a limited number of queries at once, and each running query reserves a portion of the pool’s memory and compute. Queries beyond the limit wait in a queue. Larger pools allow more concurrency and larger memory grants. If one user submits an enormous query that reserves much of the memory, small queries may wait behind it, which feels to everyone like the system is frozen.

Resource classes

Resource classes are the older, simpler mechanism. A user is assigned to a class, such as small, medium, large, or extra large, which determines how much memory each of that user’s queries receives. Bigger classes make heavy transformations faster but reduce how many queries can run together. Static and dynamic variants differ in whether the grant stays fixed or scales with pool size.

Workload groups and classifiers

Workload groups are the modern and more flexible approach. A group reserves a minimum percentage of pool resources for a purpose, caps the maximum it may take, and sets how much each request receives. Classifiers then route incoming requests into groups using the user, role, label, or time of day. A group can also carry an importance level so that executive dashboards run before overnight experiments when both are waiting.

Speed-up features

Not every question deserves a provisioned warehouse. Serverless lets you ask first and pay only for the data scanned.

How it works

Every Synapse workspace has a serverless SQL endpoint ready to use. You write ordinary SQL that points to files in the lake, such as Parquet, CSV, JSON, or Delta files, and the service reads them on demand using automatically managed compute. You never create, size, or pause anything. Billing is based on the amount of data processed by each query, so a query that scans little data costs little, and an idle workspace costs nothing for this engine.

Best uses

Making serverless efficient

Because cost follows data scanned, file layout matters. Columnar formats such as Parquet and Delta let the engine read only the needed columns. Partitioned folder structures, for example by year and month, let queries skip irrelevant folders. Selecting only the columns you need rather than all of them lowers cost. Many small files add overhead, so compact them periodically. You can also set daily, weekly, or monthly cost limits for the serverless pool in the workspace to avoid surprises.

Limits to remember

Serverless is powerful but is not a replacement for a tuned warehouse. It has no local storage for tables, offers less control over concurrency, can show variable performance because resources are shared, and supports a subset of SQL features. Highly concurrent dashboards on large datasets, or complex joins that repeat hundreds of times a day, often run better and cheaper on a dedicated pool or on cached data.

When data is messy, huge, or destined for machine learning, Spark gives you the flexibility of full programming languages on a cluster that you do not have to build.

What Spark is

Apache Spark is an open-source engine that divides large datasets into pieces, spreads them across many machines, and processes the pieces in parallel, keeping intermediate results in memory for speed. In Synapse, a Spark pool is a definition of the cluster you want, such as node size and the minimum and maximum number of nodes. The cluster is created only when someone starts a session or runs a job, and it shuts down after a configurable idle period, so you pay for compute only while it works.

Notebooks and languages

Most people use Spark through notebooks, documents that mix code, results, charts, and explanations. Synapse notebooks support Python through PySpark, Scala, and Spark SQL, plus other options. They suit exploration and teaching, and can also be run as steps in pipelines for production. Spark job definitions provide a way to submit packaged applications for unattended runs.

Autoscale and sizing

Autoscale lets a pool grow when a job needs more executors and shrink when work finishes, within limits that you define. Choose node sizes by the shape of the work: memory-heavy joins need larger memory per node, while wide parallel scans benefit from more nodes. Start modestly, observe real usage in monitoring, and adjust, because over-provisioned Spark pools are one of the commonest causes of surprise bills.

Delta Lake and shared metadata

Delta Lake is the preferred table format for Spark in the lake. It adds ACID transactions, so jobs do not leave half-written data, supports updates and deletes, allows reading older versions of a table, and enforces column rules. Synapse also lets Spark and serverless SQL share table definitions through lake databases, so a table created in a notebook can be queried with SQL without redefining it.

Runtimes and lifecycle

Each Spark pool uses a Synapse runtime, a versioned bundle of Spark, Delta Lake, Python, and libraries. Runtimes follow a lifecycle: they are announced, become generally available, then reach deprecation and end of support, after which they receive no fixes or security updates. Microsoft documents that the Spark 3.4 runtime in Synapse reached end of support on 31 March 2026 and advises moving to the newer Synapse runtime for Spark 3.5 or to a current Fabric runtime. Review the runtime used by every pool on a calendar, since jobs on unsupported runtimes can be disabled in phases.

Data rarely starts where you need it. Pipelines carry it there, on time, and in a repeatable way.

Pipeline concepts

A pipeline is a group of activities arranged in a workflow. An activity is a single step, such as copying data, running a notebook, executing a stored procedure, calling a web service, or waiting. Activities can depend on each other, run in parallel, loop over lists, and branch on conditions. Triggers start pipelines: on a schedule, in a tumbling time window that processes each interval exactly once, or in response to an event such as a new file arriving in storage.

The copy activity

The copy activity is the workhorse. It reads from a source and writes to a destination using built-in connectors for databases, files, cloud storage, software-as-a-service applications, and more. It can scale out by using several parallel streams, convert formats, and map columns. For loading dedicated SQL pools it can use fast bulk paths, which is the recommended way to load large volumes.

Mapping data flows

Mapping data flows let you design transformations visually: joins, aggregations, derived columns, lookups, and conditional splits, drawn as boxes and arrows. Behind the scenes they run on managed Spark clusters, so you get scale without writing Spark code. They are productive for teams that prefer low-code tools, though complex logic may be clearer, cheaper, and easier to test in notebooks or SQL.

Self-hosted integration runtime

Sources inside company networks, such as on-premises databases, are not reachable from the cloud by default. A self-hosted integration runtime is software that you install on a machine inside the private network. It makes outbound connections to Synapse, reads the source locally, and sends the data on. Run it on more than one machine for resilience and watch its capacity, because it can become the bottleneck of the entire ingestion process.

Operational habits for pipelines

A lake without structure becomes a dumping ground. A few simple layers keep it understandable, trustworthy, and fast.

The three layers

The most popular organizing idea is the medallion architecture, with three zones often named bronze, silver, and gold. Bronze holds raw data exactly as received, never modified, so you can always replay history. Silver holds cleaned, validated, de-duplicated, and conformed data with consistent types and keys. Gold holds business-ready datasets shaped for specific uses, such as star schemas, aggregates, and feature tables for machine learning.

flowchart LR
  S["Source Systems"] --> B["Bronze: raw and unchanged"]
  B -->|clean, validate, deduplicate| SI["Silver: conformed and trusted"]
  SI -->|model and aggregate| G["Gold: business-ready"]
  G --> DW["Dedicated SQL Pool"]
  G --> BI["Power BI"]
  G --> ML["Machine Learning"]
  B -. replay history .-> SI
        

Folder structure and naming

Organize folders by zone, then by source system or subject area, then by time, for example year, month, and day. Consistent naming allows tools to find data and allows queries to skip folders they do not need. Avoid putting meaning in file names alone, and document the layout in a shared place.

File formats

Access control in the lake

Data Lake Storage Gen2 supports role-based access at the account level and access control lists at the folder and file level. A common pattern gives engineers broad access to bronze and silver, grants analysts access to gold only, and uses groups rather than individual users in all permissions so that changes are easy to manage. Make sure the workspace’s managed identity has the access it needs, and no more.

Handling small files and schema change

Streaming and frequent loads create many small files, which slows every engine. Schedule compaction jobs to merge them into healthy sizes. Source systems change over time, adding or renaming columns. Decide on a policy, such as allowing new columns to flow through silver with alerts while blocking breaking changes, so that surprises are caught early.

Traditionally, analysing live application data meant slow overnight exports. Synapse Link reduces that delay to a near real-time flow.

The problem it solves

Running big analytical queries directly on a busy operational database harms the application. Building extraction jobs is slow, complex, and always somewhat out of date. Hybrid transactional and analytical processing, or HTAP, aims to let analytics run on fresh operational data without disturbing the application. Synapse Link is Microsoft’s approach to this for several sources.

How it works for different sources

Trade-offs

Some questions cannot wait for tonight’s batch. Streaming handles events as they arrive.

Batch versus streaming

Batch processing collects data over a period and processes it all together. Streaming processes each event or small group of events within seconds. Streaming is valuable for fraud detection, sensor monitoring, clickstream analysis, and live dashboards. It is also more complex to build and operate, so use it only where the business really needs low latency.

Building blocks around Synapse

The lambda and kappa ideas

Two classic designs combine batch and streaming. In lambda, a fast path serves approximate recent results while a batch path later recomputes exact ones. In kappa, a single streaming path handles everything and history is replayed through it when logic changes. With Delta tables, a single table can be fed by both batch and streaming jobs, which often simplifies the design.

Real-time engines and lifecycle

Synapse once offered Data Explorer pools for fast exploration of logs and telemetry. Microsoft announced their retirement and points real-time workloads toward the real-time capabilities of Fabric and toward the standalone Azure Data Explorer service. If you encounter Data Explorer pools in an existing workspace, check the current lifecycle announcement and plan their replacement.

Analytics platforms hold a company’s most valuable and sensitive data in one place, so security must be designed in layers from the start.

Network isolation

By default, a workspace has public endpoints protected by firewall rules. For stronger isolation you can create the workspace with a managed virtual network. Compute for pipelines and Spark then runs inside a Microsoft-managed network isolated from other customers. Connections to storage, databases, and other services go through managed private endpoints, private addresses that keep traffic off the public internet. You can also use private endpoints for connecting your own network to the workspace’s SQL, development, and Studio endpoints, and disable public network access altogether.

Data exfiltration protection

Exfiltration means data leaving where it should stay. When data exfiltration protection is enabled on a workspace with a managed virtual network, outbound connections are limited to approved targets, such as specified Microsoft Entra tenants, through managed private endpoints. This prevents a compromised notebook or a careless user from sending data to an unauthorized destination.

Identity and access

Protecting data inside the SQL pool

Security says who may touch the data. Governance says what the data is, where it came from, and whether it can be trusted.

Why governance matters

As lakes and warehouses grow, people lose track of what exists. Duplicate datasets appear, definitions drift apart, and nobody knows whether a number can be used in a board report or where a sensitive column went. Governance answers these questions through catalogs, ownership, definitions, quality rules, and lineage.

Microsoft Purview

Microsoft Purview integrates with Synapse to scan data sources, build a searchable catalog, classify sensitive information automatically, and capture lineage, the map showing how data flows from source through pipelines and notebooks into tables and reports. When a report number looks wrong, lineage lets you trace it back to the exact step that produced it. When a source system changes, lineage shows what will be affected downstream.

Governance building blocks

A platform that moves critical data every night must show you what is happening, before users complain.

Synapse Studio monitoring

The monitor hub in Synapse Studio shows pipeline runs, trigger runs, SQL requests, Spark applications, and integration runtime status. You can see which runs failed, how long they took, and drill into error messages. It is the first place to look during an incident.

Azure Monitor and Log Analytics

For history, alerting, and cross-service analysis, send diagnostic logs and metrics to Azure Monitor and a Log Analytics workspace. This lets you build dashboards, set alerts, correlate pipeline failures with infrastructure events, and keep logs longer than the Studio view holds them.

What to watch

Dynamic management views

Dedicated SQL pools expose system views that list current sessions, running and queued requests, the steps of each query plan, and the amount of data moved. They are the precise tools for answering which query is running, why it is waiting, and where the time is spent. Learn the handful of views that show active requests and request steps, and build saved queries for them.

Alerting and runbooks

Alert on events that need action: a pipeline failure, a pool paused unexpectedly, queue times exceeding a limit, storage nearing capacity, or a cost threshold crossed. For each alert, write a short runbook describing what it means, how to check it, and who to call. Review alerts quarterly and remove noise.

Tuning a distributed warehouse is mostly about moving less data, spreading work evenly, and giving the planner good information.

The tuning loop

Data skew

Skew means some distributions hold far more rows than others. Since a query finishes only when the slowest distribution does, one overloaded distribution makes the whole query slow even if fifty-nine are idle. Skew comes from poorly chosen hash keys, such as columns with many nulls or a few dominant values. Check row counts per distribution regularly for large tables and redistribute on a better key when skew is found.

Reducing data movement

Align distribution keys of large tables that join often. Replicate small dimensions. Join on the distribution key whenever possible. Pre-aggregate before joining when the question allows it. Avoid wide intermediate results by selecting only needed columns early in the query.

Columnstore health

Columnstore quality depends on full, well-compressed row groups. After heavy updates, deletes, or many small loads, the pool may hold many small or partially deleted row groups. Rebuilding indexes, ideally with enough memory granted through a suitable workload group, restores quality. Monitor row group quality as part of routine maintenance.

Statistics, caching, and views

Keep statistics fresh on join and filter columns. Enable result set caching for read-heavy reporting pools. Use materialized views for expensive aggregations that many reports repeat. Consider ordered columnstore for tables that are filtered on a common range column.

Lake-side tuning

For Spark and serverless work, use columnar formats, partition by commonly filtered columns, avoid millions of tiny files, compact regularly, and prune folders in queries. Cache frequently used datasets in Spark during a session where repeated reads occur.

Each engine in Synapse is billed differently. Understanding the meters is the first step to controlling the bill.

The billing models

Practical savings steps

Analytics code deserves the same discipline as application code: version control, review, testing, and automated deployment.

Source control in the workspace

Synapse Studio can connect a workspace to a Git repository. Developers work in branches, review changes through pull requests, and merge into a main branch. A separate publish branch holds the generated templates that describe the workspace content, which are used for deployment. This gives you history, collaboration, and the ability to roll back mistakes.

Multiple environments

Use at least separate development, test, and production workspaces. Developers experiment freely in development, testers validate changes with realistic data in test, and production runs only approved releases. Each environment has its own storage, pools, and security settings, and connection details differ between them, so parameters and variable files are essential.

Database changes

Table definitions, views, and procedures in dedicated SQL pools should be managed as code too, using database projects or migration scripts that are versioned and deployed through the same release pipeline. Never change production schemas by hand. Test schema changes against a copy because loads, distributions, and partitions can make a change much heavier than it looks.

Testing data work

Test transformations with small fixed datasets whose expected results are known, validate row counts and totals between layers, and check that rerunning a job does not duplicate data. Automated tests catch the errors that are most embarrassing in front of business users.

Analytical data feeds executive decisions and regulatory reports, so you must know how to get it back.

Restore points in dedicated SQL pools

A dedicated SQL pool automatically takes snapshots of its data several times a day and keeps them for a limited period, typically seven days. These automatic restore points allow you to restore the pool to an earlier state by creating a new pool from a snapshot. You can also create your own user-defined restore points before risky operations such as a large schema change or load, and keep them for a chosen time. Restoring creates a new pool, so the original is untouched until you decide to switch.

Geo-backup and regional disasters

Dedicated pools also keep a daily geo-redundant backup in the paired region, which allows a restore into another region if the primary region is unavailable. The recovery point can be up to a day old, and the restore takes time proportional to the data size. For stricter objectives, organizations run a second workspace in another region and reload or replicate data, accepting higher cost for faster recovery.

The lake and other components

Lake data is protected by the storage account’s redundancy options, which can replicate data across zones or regions. Enable soft delete and versioning so that accidental deletions can be undone. Workspace content such as pipelines and notebooks is protected by Git. Make sure that every part of the platform has a recovery story, not just the warehouse.

Most teams are either bringing an old warehouse to the cloud or deciding what to do with Synapse as Fabric matures.

Migrating a legacy warehouse into Synapse

Moving from an on-premises warehouse involves much more than copying tables. The steps are to assess what exists, redesign tables for distribution and columnstore instead of copying the old design blindly, convert code and procedures for SQL differences, move data through the lake in bulk, validate results, and run both systems in parallel before cutting over. The largest effort is usually rewriting and testing logic, not moving bytes.

Deciding whether to move from Synapse to Fabric

Fabric uses OneLake as a single logical lake, Delta as its standard table format, and capacity-based pricing shared across engines. The migration question depends on your situation. Industry guidance describes a phased approach, which is summarized below, and Microsoft has also introduced tooling such as a migration assistant for Fabric Data Warehouse, which was in preview at the time of writing. Confirm current availability before depending on any tool.

flowchart LR
  A["Assess estate and dependencies"] --> B["Size target capacity"]
  B --> C["Connect lake data in place"]
  C --> D["Refactor notebooks, pipelines, SQL"]
  D --> E["Run both platforms in parallel"]
  E --> F["Cut over consumers"]
  F --> G["Retire old resources"]
  E -. issues found .-> D
        

Decisions to make early

Patterns are proven solutions. Anti-patterns are mistakes that appear again and again in real projects.

Helpful patterns

flowchart TD
  SRC["Operational Systems and Files"] --> ING["Metadata-driven Pipelines"]
  ING --> BR["Bronze in Lake"]
  BR --> SP["Spark: clean and conform"]
  SP --> SV["Silver in Lake"]
  SV --> SP2["Spark or SQL: model"]
  SP2 --> GD["Gold in Lake"]
  GD --> SQLP["Dedicated Pool: high-concurrency serving"]
  GD --> SRV["Serverless Views: self-service"]
  SQLP --> PBI["Power BI"]
  SRV --> PBI
  PUR["Purview: catalog and lineage"] -. governs .-> GD
        

Frequent anti-patterns

These illustrative composites show how the building blocks come together. They describe typical situations, not specific named customers.

These habits separate smooth analytics platforms from expensive, fragile ones.

When a report is late or a query crawls, an ordered approach finds the cause faster than guessing.

Start by locating the layer

Every problem sits in one of a few layers: the source system, the ingestion pipeline, the lake, the compute engine, the model and query, or the reporting tool. Ask which layer first. Check freshness of source data, status of the latest pipeline run, health of the pool, and then the specific query. Narrowing down saves hours.

flowchart TD
  P["Report is wrong or late"] --> Q1{"Did the latest pipeline run succeed?"}
  Q1 -->|No| F1["Read the failed activity error, check source access, runtime, and permissions"]
  Q1 -->|Yes| Q2{"Is the data fresh in the lake and tables?"}
  Q2 -->|No| F2["Check trigger timing, file arrival, and incremental watermarks"]
  Q2 -->|Yes| Q3{"Is the pool or engine healthy and not queuing?"}
  Q3 -->|No| F3["Check paused state, workload groups, concurrency, and resource limits"]
  Q3 -->|Yes| Q4{"Is one query slow?"}
  Q4 -->|Yes| F4["Inspect plan steps for data movement, skew, stale statistics, and missing caches"]
  Q4 -->|No| F5["Check the reporting layer, semantic model, and refresh settings"]
        

Common symptoms and likely causes

Skew and movement detective work

If a query is slow on a dedicated pool, first look at the steps of its plan. A step that moves a very large number of rows between nodes is a red flag. Then check whether the tables in the join share a distribution column. Finally, compare row counts across distributions for the largest table. Uneven counts indicate skew. The fix is usually to change the distribution column, replicate a small table, or restructure the query, and then to confirm improvement with the same measurements.

Industry guidance from mid-2026 says that Microsoft had not announced an end-of-life date for Synapse and that existing pools remain supported, while new investment is concentrated on Fabric. Individual components, such as older Spark runtimes and Data Explorer pools, have their own lifecycle dates, so check the official lifecycle pages before relying on them.

A dedicated pool is a provisioned, always-sized warehouse with its own distributed storage and tuned tables, billed by hour. Serverless has no provisioned resources, queries files in the lake, and bills by data processed.

It is a fixed design decision that lets pool size change without re-cutting the data. Scaling changes how many compute nodes share the sixty pieces, not the number of pieces.

Use hash on a well-chosen key for large fact tables, replicated for small dimensions, and round robin for staging tables. If unsure for a new table, start with round robin for loading, then refine as you learn the join patterns.

No. Pausing stops compute charges and keeps your data and snapshots in storage, which continues to be billed. Resuming brings the pool back with the same tables.

Use Spark for large-scale cleaning, complex transformations, semi-structured data, streaming, and machine learning. Use SQL pools for structured, repeatable analytics and for serving many concurrent report users.

Use Parquet or Delta, partition folders, select only needed columns, and configure the daily, weekly, and monthly data-processed limits on the serverless pool.

Yes. Create the workspace with a managed virtual network, use managed private endpoints for outbound connections, add private endpoints for access to the workspace, enable data exfiltration protection, and disable public network access.

Inventory the pools and jobs that still use it, test your notebooks and libraries on the newer Synapse runtime or on a Fabric runtime, and migrate before jobs are disabled. Microsoft’s documentation lists the end-of-support date and replacement options.

For most new greenfield projects, evaluate Fabric first, since it receives the newer features. Choose Synapse for new work only when you have clear reasons such as regional availability, existing investments, or specific features, and keep the design portable using open formats.

Restore from a user-defined restore point taken before the load, switch partitions back, or reload from the immutable bronze files in the lake. Taking restore points before risky operations is a cheap and powerful habit.

Not always. A lakehouse with serverless SQL or Spark may be enough for modest concurrency. A dedicated warehouse remains attractive when many users need fast, consistent responses on structured data, or when workload isolation and predictable performance matter.

Azure Synapse Analytics brings together a massively parallel data warehouse, serverless querying, Apache Spark, data pipelines, and operational links in one workspace built on a shared data lake. Its strengths come from using the right engine for each job, designing tables so that work spreads evenly and data movement stays low, loading in bulk through staging, isolating workloads, and layering the lake so that trust grows from raw data to business-ready data. Security is built in layers around private networking, identities, and data protection features, governance gives the data meaning and traceability, and disciplined operations keep costs and reliability under control. At the same time, Microsoft’s investment has shifted toward Fabric, and specific components have reached or are approaching end of support, so wise teams keep their designs portable, watch lifecycle dates, and plan their direction deliberately. Treat official Microsoft documentation as the final word and this guide as your map.