Master enterprise data warehousing in Azure: Synapse Dedicated SQL Pool Massively Parallel Processing (MPP), 60 distributions, Hash vs Round Robin vs Replicated tables, Clustered Columnstore Indexes, and Serverless SQL querying over Lakehouse Delta Parquet.
[!NOTE] Azure Synapse Analytics provides two distinct query engines under a unified governance plane:
- Dedicated SQL Pools (formerly Azure SQL DW): Enterprise MPP data warehouse with dedicated compute (DWUs) that distributes data across 60 static physical storage distributions.
- Serverless SQL Pools: Pay-per-query pay-as-you-go engine ($5 per TB scanned) designed for instant querying over ADLS Gen2 Delta, Parquet, and CSV files using
OPENROWSET.
Synapse Dedicated SQL Pools decouple compute from storage. Regardless of DWU size (from DW100c to DW30000c), Synapse always divides table data across exactly 60 physical distributions. Compute nodes divide the workload of managing these 60 distributions.
[ Control Node ] (Parses T-SQL, Generates Distributed Plan)
│
┌────────────────┼────────────────┐
▼ ▼ ▼
[ Compute Node 1 ] [ Compute Node 2 ] [ Compute Node 3 ] ... [ Compute Node N ]
(D1 ... D15) (D16 ... D30) (D31 ... D45) (D46 ... D60)
│ │ │ │
└────────────────┴────────┬───────┴──────────────────────┘
▼
[ Azure Premium Storage: 60 Physical Slices ]
customer_id, transaction_id).JOIN or GROUP BY predicates to avoid Data Movement Service (DMS) shuffles.Serverless SQL does not provision clusters or incur idle costs. It queries files directly in ADLS Gen2:
OPENROWSET(BULK ...)filepath(1), filepath(2))HASH(join_key) for multi-billion-row fact tables (minimizes ShuffleMoveOperation), REPLICATE for small dimension tables under 2 GB (broadcasts cleanly without runtime shuffles), and ROUND_ROBIN only for transient staging tables before CTAS modeling.OPENROWSET(BULK ..., FORMAT="DELTA")) to serve ad-hoc BI queries directly over Bronze/Silver ADLS Gen2 files—paying $5 / TB scanned instead of keeping an idle Dedicated DW1000c cluster running 24/7.COPY INTO vs Row Inserts): Never stream row-by-row INSERTs into Dedicated SQL Pool—each transaction writes micro-rowgroups that tank columnstore compression (<102,400 rows per rowgroup stays open in Delta Store). Use COPY INTO or PolyBase from ADLS Parquet.Review the production code, company interview Q&As, incident traces, and comprehension quiz inside this lesson.
-- Dedicated SQL Pool: Enterprise Fact Table Definition
CREATE TABLE dbo.fact_financial_transactions (
transaction_id BIGINT NOT NULL,
account_id BIGINT NOT NULL,
transaction_date_id INT NOT NULL,
currency_code CHAR(3) NOT NULL,
amount DECIMAL(18, 4) NOT NULL,
status VARCHAR(20) NOT NULL,
created_at DATETIME2(3) NOT NULL
)
WITH (
-- Distribute on account_id to co-locate with dim_account joins
DISTRIBUTION = HASH(account_id),
-- Clustered Columnstore provides 10x compression and segment elimination
CLUSTERED COLUMNSTORE INDEX,
-- Partition on transaction date for O(1) partition switching
PARTITION (transaction_date_id RANGE RIGHT FOR VALUES (
20260101, 20260201, 20260301, 20260401
))
);-- Serverless SQL Pool: Zero-infrastructure Delta Lake querying
SELECT
r.filepath(1) AS transaction_year,
r.filepath(2) AS transaction_month,
account_type,
COUNT(*) AS total_txns,
SUM(amount) AS total_volume_usd
FROM OPENROWSET(
BULK 'https://lakehouseprodstore.dfs.core.windows.net/lakehouse/silver/transactions/year=*/month=*/*.parquet',
FORMAT = 'PARQUET'
) AS r
WHERE r.filepath(1) = '2026' AND r.filepath(2) = '03'
GROUP BY r.filepath(1), r.filepath(2), account_type;Msg 110802, Level 16, State 1. The temporary database is full. An error occurred while executing the query on the compute nodes.