DP-900: understanding and using data warehouses in Azure Synapse Analytics

I will teach the skill: understanding and using data warehouses in Azure Synapse Analytics. This skill is common on the DP-900 because it assesses whether you understand how analytics architectures work in Azure and how to choose and configure a data warehouse for real analytical workloads.

What you need to know

A data warehouse is a solution optimized for analyzing large volumes of historical data and for reporting. Azure Synapse Analytics provides an integrated space for ingestion, storage and analytical processing. Two key concepts you should distinguish:

• Decoupled storage and compute: storage (data in OneLake / Azure Data Lake Storage) is separate from the processing engine. You can scale both independently.
• Processing models: Synapse offers two main modes to query tabular data at scale — Dedicated SQL Pool (formerly SQL Data Warehouse) and serverless SQL pool. Dedicated SQL Pool provides predictable performance with dedicated resources (DWUs), while serverless is on‑demand and charges by data read.

Simple example: you have e‑commerce transaction records in Parquet files in OneLake. For high-frequency reporting with multiple users, you use a Dedicated SQL Pool with distributed tables; for ad‑hoc exploration or integration with Power BI for initial exploration, you can use the serverless SQL to read the files directly.

How it works in practice

Here is the common flow and decisions you will need to make:

1. Ingestion: bring data into OneLake / ADLS Gen2 (Parquet/CSV files) or use pipelines (Synapse Pipelines / Data Factory) to move into the warehouse.
2. Choose query engine:
   • Serverless SQL: good for exploration, low costs when usage is sporadic.
   • Dedicated SQL Pool: good for steady workloads and high parallelism with distributed tables and columnstore indexes.
3. Modeling: create fact/dimension tables (star schema), choose distribution strategy (HASH, ROUND_ROBIN, REPLICATE) for Dedicated SQL Pool.
4. Optimization: use columnstore tables for compression and speed in analytics, update statistics and perform maintenance (rebuilds, reorganize if applicable).

// Exemplo T-SQL mínimo (Dedicated SQL Pool) para criar tabela columnstore e distribuir por CustomerID
CREATE TABLE dbo.FactSales (
  SaleID BIGINT,
  CustomerID BIGINT,
  ProductID BIGINT,
  SaleDate DATE,
  Amount DECIMAL(18,2)
)
WITH (
  DISTRIBUTION = HASH (CustomerID),
  CLUSTERED COLUMNSTORE INDEX
);

Quick notes on distribution: HASH is effective when there are queries that filter or join on the distribution key; REPLICATE copies the small dimension to all nodes; ROUND_ROBIN is simple, useful in initial ETL loads but can cause data movement in joins.

Common mistakes

Three typical pitfalls you often see:

• Thinking serverless replaces a Dedicated SQL Pool: serverless is for ad‑hoc reads; it does not replace a dedicated warehouse for predictable, multi‑user workloads with SLAs.
• Incorrect distribution choice: using ROUND_ROBIN by default can generate excessive data movement in joins, degrading performance.
• Ignoring columnstore compression: rowstore tables in large fact tables consume a lot of space and are slow for aggregations; using columnstore reduces I/O and improves performance.

How to practice

Practice by creating a workspace in Azure Synapse (or use the free/trial Azure). Try these hands‑on activities:

• Upload Parquet files to OneLake / ADLS Gen2 and query them with serverless SQL.
• Create a small Dedicated SQL Pool, load data and experiment with different DISTRIBUTION (HASH/REPLICATE/ROUND_ROBIN).
• Measure query time and observe effects of columnstore vs rowstore.

For DP-900 preparation, always use the official, free Microsoft Practice Assessment and the official free study guide — these are the recommended sources to practice and align with the skills measured. Do not use or rely on "exam dumps"; follow the official resources.

In summary

• Azure Synapse separates storage and compute: choose between serverless (on‑demand) and Dedicated SQL Pool (dedicated resources).
• Analytics modeling requires columnstore tables and a good distribution strategy (HASH/REPLICATE/ROUND_ROBIN).
• Serverless is great for exploration; Dedicated SQL Pool is suitable for steady, multi‑user analytical workloads.
• Practice in the Azure portal: upload data to OneLake, run serverless queries and deploy a Dedicated SQL Pool to compare performance.