What is Azure Synapse ?

Azure Synapse is a limitless analytics service that brings together enterprise data warehousing and Big Data analytics. It gives you the freedom to query data on your terms, using either server-less or provisioned resources at scale. Azure Synapse brings these two worlds together with a unified experience to ingest, prepare, manage, and serve data for immediate BI and machine learning needs.

Architecture and Design of a data warehouse depends on many factors. A dynamic and well performing DW should guaranty the data validity, concurrency, low latency and capability to integrate with other systems.

Azure Synapse provides you the platform to build and mange a modern DW with limitless analytics service that brings together enterprise data warehousing and Big Data analytics. It gives you the freedom to query data on your terms, using either server less on-demand or provisioned resources at scale.

Recently I had to work on a Synapse project and I collected the following references and now I’m sharing it with you.

Cheatsheet

• https://docs.microsoft.com/en-us/azure/sql-data-warehouse/cheat-sheet

Best Practices

• https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-best-practices
• https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-sql-pool
• https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-sql-on-demand
• https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-best-practices

Best practice and guide on Loading Data

• https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/guidance-for-loading-data
• https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/data-loading-best-practices

Designing Tables

• https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-overview

Indexing Tables

• https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index

Heaps

• https://docs.microsoft.com/en-us/sql/relational-databases/indexes/heaps-tables-without-clustered-indexes?view=sql-server-ver15

Clustered Columnstore Index

• https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15
• https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15#can-i-combine-rowstore-and-columnstore-on-the-same-table

Designing Distributed Tables — Round Robin vs Hash vs Replicated

• https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-distribute
• https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-overview#common-distribution-methods-for-tables

Hash-Distribution — Use this distribution methodology for large fact tables with clustered columnstore index.

Replicated — Use this distribution strategy for smaller tables (<2GB), which can be replicated to each compute node, typically used for Dimension Tables.

Round-Robin — Use this strategy for staging tables or for loading data or when there is no clear choice for distribution.

Load data from external tables using polybase

• https://docs.microsoft.com/en-us/azure/sql-data-warehouse/load-data-from-azure-blob-storage-using-polybase#load-the-data-into-your-data-warehouse

Azure Synapse SQL Extension

The Azure Synapse SQL Extension contain a collection of User Defined Functions (UDFs) and Views that extend the capabilities of Azure Synapse SQL. This is especially useful when you’re migrating from legacy on premise systems such as Teradata and need to emulate functionality in Synapse.

Azure-Samples/Synapse

Most often the question on how to size a proper cluster is asked. The general guidelines are:

1. Number of concurrent Queries — https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/memory-concurrency-limits. Result set caching can reduce the number of concurrent queries.
2. Size of the Active data set being queried Daily. — 1.5 TB per compute node
3. Size of the Memory need to perform Calculations in Memory. — 300GB per compute node. This often matches or exceeds existing source systems.
4. Necessary size of Temp DB the ETL and Queries need (difficult to get from source systems). This is rare, but, I have seen companies need to increase DWU to get additional TempDB space. This typically happens with very large Datasets but have few concurrent queries.

There is also bench marking practices that are published here:

https://techcommunity.microsoft.com/t5/azure-synapse-analytics/performance-benchmark-azure-synapse-analytics-data-warehouse/ba-p/1381302

If you are interested to check the GitHub Azure Samples for Synapse see the link

Azure-Samples/Synapse

Attention to Resource Classes will help you !

Resource classes for workload management - Azure Synapse Analytics

Materialized Views

Materialized views in Synapse SQL pool provide a low maintenance method for complex analytical queries to get fast performance without any query change. This article discusses the general guidance on using materialized views.

Performance tune with materialized views — Azure Synapse Analytics | Microsoft Docs

CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL) — SQL Server | Microsoft Docs

DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD (Transact-SQL) — SQL Server | Microsoft Docs