I85PREP
⌘ K
DP-500 study materials
DP-500 study resources

DP-500: Design and implement enterprise-scale data analytics solutions

DP-500 · Work through your exam, one topic at a time.

All guide sections

Design and implement enterprise-scale data analytics solutions

Domain 1 - Design and implement enterprise-scale data analytics solutions

This domain tests your ability to select the right Azure analytics components and design storage, distribution, and orchestration for large workloads. Expect questions on Synapse workspace architecture, dedicated versus serverless SQL pools, table distribution and partitioning, and pipeline-based incremental loading.

Choose the compute engine first

  • Dedicated SQL pool: provisioned DWUs, reserved compute, best for high-concurrency, repeatable reporting over structured tables. Data lives in distributed tables with a clustered columnstore index by default.
  • Serverless SQL pool: pay-per-query over files in ADLS Gen2. Use OPENROWSET, external tables, or CREATE EXTERNAL TABLE AS SELECT (CETAS) to shape and export data. No ingestion, no persistent compute.
  • Spark pool: notebook-driven transformations over Delta/Parquet when you need big-data processing or machine learning.
  • Pipelines (Synapse or Azure Data Factory): orchestration, with Azure, self-hosted, and SSIS integration runtimes.

Distribution and partitioning rules

  • Hash-distribute a large fact table on the column most used in joins, ideally high cardinality and evenly distributed. Round-robin is fine for staging. Replicate small dimension tables (roughly under 2 GB compressed).
  • Partition large tables on a date column only when queries filter on that column; choose distribution and partition columns together to avoid data movement.
  • COPY INTO is the preferred bulk load path over PolyBase for Parquet/ORC/CSV and supports more flexible authentication.

Worked example

A 2 TB FactSales table is joined to DimCustomer on CustomerKey and filtered by OrderDate. Hash-distribute on CustomerKey (join column, high cardinality), partition monthly on OrderDate, and keep the columnstore index. Replicate DimCustomer if it is small. Reasoning: the hash key eliminates shuffle on the dominant join, and partitioning enables partition elimination for date-filtered reports. Round-robin here would force a shuffle on every customer join.

Traps

  • Hash-distributing on a low-cardinality flag column creates skew; a few distributions carry most rows.
  • Replicated tables have size limits and restrict ALTER operations; do not replicate a growing dimension.
  • Serverless is cheap for ad-hoc exploration but expensive when the same heavy query runs repeatedly at high concurrency.
  • External tables need statistics created manually for good query plans.