DP-203 Azure Data Engineer Associate Azure Synapse Analytics 1 — Questions and Answers
Question 1: How many distributions does an Azure Synapse Analytics dedicated SQL pool use to spread data across compute nodes?
- 30
- 60 (Correct answer)
- 90
- 120
Correct answer: 60
Azure Synapse Analytics dedicated SQL pools always use exactly 60 distributions to enable massively parallel processing (MPP) across compute nodes.
Question 2: Which table distribution type in an Azure Synapse dedicated SQL pool is best for large fact tables that are frequently joined on a specific column?
- ROUND_ROBIN
- REPLICATE
- HASH (Correct answer)
- PARTITION
Correct answer: HASH
HASH distribution places rows sharing the same hash value on the same distribution, reducing data movement during joins when the join column matches the hash key.
Question 3: Which index type in Azure Synapse dedicated SQL pools provides the best compression and performance for large-scale analytical queries?
- Rowstore clustered index
- Clustered columnstore index (Correct answer)
- Heap
- Nonclustered rowstore index
Correct answer: Clustered columnstore index
Clustered columnstore indexes store data column-by-column enabling high compression ratios and efficient columnar scans that dramatically improve analytical query performance.
Question 4: What is the primary purpose of an Azure Synapse workspace?
- Storing raw data in Azure Data Lake Storage only
- Providing a unified collaboration environment integrating SQL, Spark, pipelines, and analytics (Correct answer)
- Running Apache Spark workloads exclusively
- Managing Azure Data Factory pipelines only
Correct answer: Providing a unified collaboration environment integrating SQL, Spark, pipelines, and analytics
A Synapse workspace is a unified analytics platform that integrates dedicated SQL pools, serverless SQL, Apache Spark, Data Explorer, and pipeline authoring in a single environment.
Question 5: What is the main advantage of using REPLICATE table distribution in Azure Synapse dedicated SQL pools?
- Distributes rows evenly across all 60 distributions for balanced loading
- Copies the full table to each compute node, eliminating data movement during joins (Correct answer)
- Partitions data by date for faster time-range queries
- Enables row-level security for sensitive dimension tables
Correct answer: Copies the full table to each compute node, eliminating data movement during joins
REPLICATE caches a full copy of the table on each compute node so that joins with large distributed tables require no data movement across nodes.
Question 6: In Azure Synapse Analytics dedicated SQL pools, what does the service objective (e.g., DW1000c) control?
- The number of storage accounts attached to the pool
- The version of SQL Server engine used internally
- The number of Data Warehouse Units (DWUs) and compute power allocated (Correct answer)
- The maximum number of concurrent users allowed
Correct answer: The number of Data Warehouse Units (DWUs) and compute power allocated
The service objective specifies the DWU (Data Warehouse Unit) allocation, which determines the compute power, number of compute nodes, and query concurrency available.
Question 7: Which feature in Azure Synapse Analytics allows SQL queries to access data stored in Azure Data Lake Storage Gen2 without loading it into a dedicated SQL pool?
- External tables with PolyBase (Correct answer)
- Azure Synapse Link
- Azure Data Factory Copy activity
- Spark dataframe connector
Correct answer: External tables with PolyBase
PolyBase external tables enable Synapse SQL to query data directly in Azure Data Lake Storage Gen2 without physically importing the data into the dedicated SQL pool.
DP-203 Azure Data Engineer Associate Azure Synapse Analytics 1
How many distributions does an Azure Synapse Analytics dedicated SQL pool use to spread data across compute nodes?