Azure Synapse Analytics - Pools

Data partitioning and distribution

Dedicated SQL Pool:

Distribution: Refers to how data is physically distributed across multiple nodes in the SQL pool.
When you create a table, you can choose a distribution method (Round Robin, Hash, or Replicated) that determines how data is distributed across the compute nodes.
This physical distribution is key to parallel processing and performance optimization.
The goal is to ensure balanced data distribution across compute nodes to enable efficient parallel processing.
By distributing data across nodes, each node can perform computations on a subset of data in parallel.

Partitioning: In a Dedicated SQL Pool, partitioning is a logical division of data within a distributed table.
It's used to improve query performance by allowing the system to process only the relevant partitions of data for a given query.
Partitioning in this context is similar to traditional SQL Server table partitioning.
This is about dividing a large table into smaller, logical segments based on a specific column, such as date or ID.
Partitioning aims to improve query performance by allowing the engine to process only the relevant partitions for a query, thus reducing the amount of data scanned. 

Serverless SQL Pool:

Serverless SQL Pool does not expose direct control over data distribution or partitioning in the same way as a Dedicated SQL Pool.
Since it operates on a serverless model, the focus is on querying data across various data sources without managing physical storage or distribution details.

Queries can still benefit from the underlying organization of data in the source system, such as files partitioned in a data lake, but this is managed in the underlying storage. 

Apache Spark Pool:

In Apache Spark, the term "partitioning" is used to describe how data is divided into chunks that can be processed in parallel across the nodes in the Spark cluster.
While it's similar to the dedicated SQL pool's concept of logical partitioning, Spark's partitioning also inherently considers the physical aspect because Spark must manage the data across the nodes for its in-memory processing.

However, Spark partitioning doesn’t directly control the physical storage of data like specifying file locations on disk; that aspect is managed by the underlying storage system, such as HDFS or a cloud storage service.

Spark's partitioning does effectively combine aspects of both logical organization (how data is grouped for processing tasks) and physical distribution (how tasks are distributed across the cluster for execution). It's a logical division from a data processing perspective, but it also has a physical component in that each partition is processed by different executors potentially on different nodes.

Each of these pools serves different use cases: 

Dedicated SQL Pools are best for consistent, heavy workloads that require predictable performance.
Serverless SQL Pools are suitable for querying data on-demand with flexible, cost-effective billing.
Apache Spark Pools are tailored for complex analytics and machine learning projects that benefit from in-memory processing.

Distribution methods in Dedicated SQL Pool

Round Robin Distribution

This method evenly distributes data across all nodes in a round-robin fashion - each new piece of data or task is assigned to the next node in line, and once the end of the line is reached, the process loops back to the first node and continues in this cycle. This ensures even data load for every node but without considering the data's content.
Use Round Robin distribution during initial data loading or when avoiding data skew in temporary, staging tables without a clear distribution key.

Hash Distribution 

Data is distributed based on the hash value of a specified column, typically a key in the data. This method is beneficial for queries involving joins or aggregations on the hash column, as related data is located on the same node, reducing data movement across nodes and improving query performance.

Replicated Distribution

The entire dataset is copied to each node, ensuring that all data is locally available for queries on every node. This approach eliminates the need for data movement during queries that join replicated tables with other tables, ideal for small lookup tables used across many queries, but it increases storage requirements.

General guidelines for choosing HASH distribution column in Dedicated SQL Pool

Even Distribution

Ensure that data is distributed as evenly as possible across all nodes/distributions to avoid any particular node being a bottleneck. This is referred to as avoiding data skew.

Joins 

When two large tables are joined, and both are hash-distributed on the join key, the related data is already collocated on the same distribution. This means the join can be performed locally on each distribution without requiring data movement between distributions, resulting in faster join operations.

Grouping and Aggregation

Distributing on columns that are frequently used in GROUP BY clauses can help optimize aggregation queries. Aggregate functions (like SUM, AVG, etc.) can benefit from hash-distributed tables when the aggregation is done on the distribution column, as each distribution can perform the operation on its local data.

Avoiding NULLs

NULL values can lead to uneven distribution and should be minimized in the distribution column.

Not a Date Column

Date columns can lead to clustering of data if most of the query operations are focused on specific date ranges. Moreover, using dates as distribution columns can cause skewed processing during incremental data loads.

Filters

If a column frequently appears in WHERE clauses, it may not be the best choice for a distribution column. This is because filtering on the distribution column can lead to skewed processing, with only a few nodes handling the majority of the work.
Filtering performance can be influenced by partitioning rather than distribution. If a table is partitioned on a date column and most queries filter on that date column, then those queries can scan only the relevant partitions, improving performance.

Source:
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/on-demand-workspace-overview
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-overview-what-is?context=%2Fazure%2Fsynapse-analytics%2Fcontext%2Fcontext
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/overview-architecture
https://learn.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-overview
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-partition