01
Hash
Example: A large fact table joined to customers on customer_id
Approach: Hash on a column with many unique values, few nulls and a role in joins. Never hash on the date most queries filter by, or one distribution does all the work.
Azure Synapse Analytics
Azure Synapse Analytics consulting and migration
One Synapse workspace holds several engines, and using the wrong one for a job is where slow queries and surprise bills begin. Agnotic Technologies designs dedicated SQL pool tables, serverless queries over your lake, Spark pools and pipelines, and plans a move to Microsoft Fabric when you choose one.
Dedicated and serverless SQLDistribution designWorkload managementFabric migration planning
Trusted by global innovators
Synapse consulting
What does Azure Synapse Analytics consulting cover?
Azure Synapse Analytics consulting is designing, tuning and moving the analytics workloads in a Synapse workspace. Agnotic Technologies chooses between dedicated and serverless SQL pools, designs table distributions and columnstore indexes, builds Spark pools and pipelines, sets workload management and pause schedules, and plans a move to Microsoft Fabric when that is your direction.
We work on new workspaces, on warehouses moving into Synapse from on-premises SQL Server, and on existing pools that slowed down or cost more each month.
Moving applications and their operational SQL Server databases to Azure is a separate job, covered by our Azure migration services. Semantic models and report migration sit with our business intelligence work.
Component map
Which Synapse component fits each job?
The map we use in assessments: what each part of a workspace is for, the design mistake we see most often, and where it lands if you later move to Microsoft Fabric.
| Component | Use it for | Common design mistake | Fabric counterpart |
|---|---|---|---|
| Dedicated SQL pool | Heavy, repeated warehouse queries on modeled star schemas | Large fact tables left round-robin and joined on a key, so every join moves rows | Fabric Data Warehouse |
| Serverless SQL pool | Ad hoc T-SQL and views over Parquet, Delta Lake and CSV files in the lake | Querying raw CSV or thousands of small files, which raises the data processed per query | SQL analytics endpoint of a Fabric lakehouse |
| Apache Spark pool | Data preparation, notebooks and machine learning on lake data | Clusters sized for the biggest job and left running between runs | Fabric Data Engineering with Spark notebooks |
| Synapse pipelines | Orchestration and copy activities, on the same model as Azure Data Factory | Copying whole tables every night instead of loading only changed rows | Data Factory in Fabric |
| Synapse Link | Near real-time copies of operational data for analytics without hand-built ETL | Starting new Synapse Link for Cosmos DB work, which Microsoft no longer supports for new projects | Mirroring in Fabric |
The Fabric column is a planning guide, not a feature match. Microsoft's migration documentation lists the data types and T-SQL that change, and we check each one against your code.
Table design
How do we choose a distribution for each table?
Every table in a dedicated SQL pool is spread across distributions, so this choice decides how much data moves on each query.
02
Round-robin
Example: A staging table loaded and then transformed
Approach: Round-robin for fast loads when no key is obvious. Avoid it for large tables that are joined later, because each join moves rows.
03
Replicated
Example: A small product or region dimension used in many joins
Approach: A full copy on each compute node, so joins need no data movement. Keep it for small tables that change rarely.
04
Clustered columnstore
Example: Large fact tables read a few columns at a time
Approach: The usual index for large tables. Load in large batches so rowgroups compress well, and rebuild after heavy trickle loads.
05
Heap
Example: A transient load table emptied after each run
Approach: No index for the fastest landing, then one insert into the columnstore target table.
Failure points
Why do Synapse workloads slow down or cost more?
Challenge
Joins shuffle large amounts of data between distributions on every run.
Agnotic approach
Align hash keys on the biggest join, and look for shuffle and broadcast move steps in the query's request steps.
Challenge
One distribution holds far more rows than the others, so one node does most of the work.
Agnotic approach
Check skew with DBCC PDW_SHOWSPACEUSED and move to a distribution column with more distinct values.
Challenge
Dashboards queue behind nightly loads.
Agnotic approach
Workload groups and classifiers give report queries reserved resources and higher importance, and cap what loads can take.
Challenge
Serverless SQL costs grow as more people query raw CSV files.
Agnotic approach
Convert busy data to Parquet or Delta Lake, partition folders by date and filter on the path, so each query reads fewer files.
Challenge
A dedicated pool runs all night and all weekend with no queries.
Agnotic approach
Pause it on a schedule and resume before the first load, after checking which jobs and users depend on it.
Cost controls
How do we keep Synapse spend predictable?
- Pause and resume schedules for dedicated pools used only in working hours
- Dedicated pool size set for the normal load, scaled up only for heavy backfills
- Daily, weekly or monthly limits on data processed by serverless SQL
- Result set caching for repeated report queries on dedicated pools
- Spark pools with autoscale and auto-pause so idle clusters stop
- Spend figures taken from Azure Cost Management for your subscription, never from estimates
Synapse to Fabric
How does a move from Synapse to Fabric run?
We follow the stages in Microsoft's migration guidance and keep Synapse running until the new warehouse has matched it.
Assess
List tables, views, stored procedures, pipelines, security roles and every report or tool that connects. Decide what moves as it is and what gets redesigned.
Convert schema and code
Map data types, for example splitting datetimeoffset values into a datetime2 column plus an offset column, and rewrite T-SQL the target does not support.
Run in parallel
Load both warehouses, compare row counts and totals, and test reports and query tools against the new one before anyone switches.
Switch and retire
Move reports and other consumers, scale the dedicated pool down, then pause or remove it once nothing reads from it.
Which teams bring us Synapse work?
Teams whose dedicated pool slowed down
Data and users grew faster than the table design. We redistribute, re-index and separate workloads.
Companies leaving an on-premises SQL Server warehouse
Schema and loads moved into a dedicated pool or straight to Fabric, with a parallel run before reports switch.
Lake-first data teams
Serverless SQL views over Parquet and Delta Lake, so analysts query the lake without a loaded warehouse.
Startups on Azure
Azure Synapse Analytics for startups often means serverless SQL first, paying for data processed, and a dedicated pool only once queries are steady.
Teams weighing Microsoft Fabric
An assessment of what a move changes in their code, data types and pipelines before they commit.
References
Documentation we design Synapse workloads against
Dedicated SQL poolServerless SQL poolWorkload managementMicrosoft FabricAzure Well-Architected Framework
Facts and sources
Facts from Microsoft's Synapse documentation
Behaviors and guidance from Microsoft Learn that shape our Synapse designs and migration plans.
A distributed table in a Synapse dedicated SQL pool appears as one table, but its rows are stored across 60 distributions using a hash or round-robin algorithm; small tables can instead be replicated to every compute node.
Source: Microsoft Learn: designing distributed tables in dedicated SQL poolServerless SQL pool in Azure Synapse queries Parquet, Delta Lake and delimited text files in the data lake with T-SQL, and charges only for the data processed by the queries you run.
Source: Microsoft Learn: serverless SQL pool in Azure Synapse AnalyticsA paused dedicated SQL pool is not charged for compute resources, but storage charges continue while it is paused.
Source: Microsoft Learn: pause and resume compute in dedicated SQL poolMicrosoft's dedicated SQL pool documentation advises teams new to data warehousing to start with Fabric Data Warehouse, and says existing dedicated SQL pool workloads can upgrade to Fabric.
Source: Microsoft Learn: designing distributed tables in dedicated SQL poolFabric Data Warehouse does not currently support the datetimeoffset data type, so a migration from a Synapse dedicated SQL pool must move the time zone offset into a separate column.
Source: Microsoft Learn: migration planning from Synapse dedicated SQL pools to Fabric Data Warehouse
Scope
What drives the scope of a Synapse project?
We do not quote Synapse or Fabric capacity prices. Scope depends on these factors.
Tables and stored procedures
Large T-SQL code bases take most of the effort in redesigns and migrations.
Pipelines and sources
Each source and pipeline needs retesting after a redistribution or a move.
Data volume
The largest tables decide how long redistribution, reloads and parallel runs take.
Consumers
Every report, notebook and application that connects needs testing and repointing.
Security model
Row-level and column-level security, Microsoft Entra ID groups and private endpoints carried across.
Target
Tuning in place, a move into Synapse, or a move from Synapse to Fabric.
Frequently Asked Questions
It depends on how much T-SQL and how many pipelines are involved, and whether data has to move. A tuning review is shorter than a migration with a parallel run. We set the plan after an assessment of your workspace.
Not automatically. Microsoft points new warehouse projects to Fabric and says existing dedicated pools can upgrade. If your Synapse workload is stable, we first assess what a move would change, then you decide when it is worth doing.
Serverless suits exploration and occasional queries over files in the lake, billed by data processed. A dedicated pool suits heavy, repeated warehouse queries on modeled tables, billed for compute while it runs. Many workspaces use both.
We do not quote prices. Cost comes from dedicated pool compute while it runs, storage, data processed by serverless queries and Spark pool hours, at Microsoft's published rates. We estimate it from your own usage.
Existing Synapse Link setups can. For new projects, Microsoft says Synapse Link for Cosmos DB is no longer supported and points to Azure Cosmos DB mirroring in Microsoft Fabric, so that is what we build.
Operational SQL Server databases that serve applications move through our Azure migration work, usually to Azure SQL. This page covers the analytics warehouse that reports read from.
Yes. We tune the tables and views Power BI reads and set workload groups so report queries do not wait behind loads. Semantic model and report design are part of our business intelligence work.