DP-203 Azure Data Engineer Associate Azure Synapse Analytics — 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.
Question 8: What is the primary purpose of Azure Synapse Link for Azure Cosmos DB?
- Migrating Cosmos DB data permanently to Azure SQL Database
- Enabling near real-time analytics on operational Cosmos DB data without ETL pipelines (Correct answer)
- Backing up Cosmos DB containers to Azure Blob Storage
- Replicating Cosmos DB data across Azure regions for disaster recovery
Correct answer: Enabling near real-time analytics on operational Cosmos DB data without ETL pipelines
Azure Synapse Link creates a tight integration between Cosmos DB's analytical store and Synapse Analytics, enabling zero-ETL near real-time analytics without impacting transactional performance.
Question 9: Which file format is NOT natively supported by Azure Synapse Analytics serverless SQL pools for querying data in Azure Data Lake Storage?
- Parquet
- CSV
- Avro (Correct answer)
- JSON
Correct answer: Avro
Serverless SQL pools natively support Parquet, CSV, JSON, and Delta Lake formats; Avro is not natively supported and requires additional workarounds.
Question 10: What is the primary use case for Apache Spark pools in Azure Synapse Analytics?
- Running T-SQL queries against dedicated SQL pool tables
- Providing serverless on-demand SQL queries for data exploration
- Running distributed data processing, transformation, and machine learning workloads (Correct answer)
- Managing and scheduling Synapse pipeline activities
Correct answer: Running distributed data processing, transformation, and machine learning workloads
Apache Spark pools provide a managed distributed computing environment supporting Python, Scala, R, and .NET for large-scale data processing and machine learning.
Question 11: What does the Delta Lake format primarily provide when used in Azure Synapse Analytics?
- Compressing Parquet files to reduce storage costs
- ACID transactions, schema enforcement, and time travel on data lake storage (Correct answer)
- Real-time streaming ingestion from Azure Event Hubs
- Metadata storage for external table definitions
Correct answer: ACID transactions, schema enforcement, and time travel on data lake storage
Delta Lake adds ACID transaction guarantees, schema enforcement, and time travel (data versioning) capabilities on top of Parquet files in Azure Data Lake Storage.
Question 12: What connector allows Apache Spark pools in Azure Synapse Analytics to efficiently read from and write to dedicated SQL pools?
- Azure Data Factory mapping dataflow
- Synapse Spark connector (azure-synapse-spark-connector) (Correct answer)
- PolyBase over JDBC
- Azure Synapse Link
Correct answer: Synapse Spark connector (azure-synapse-spark-connector)
The Synapse Spark connector uses a high-performance bulk load mechanism to efficiently move data between Spark pools and dedicated SQL pools within the same workspace.
Question 13: Which approach best optimizes query performance in Azure Synapse Analytics serverless SQL pools?
- Creating clustered columnstore indexes on external tables
- Using Parquet format with folder partitioning and creating table statistics (Correct answer)
- Scaling up DWUs to increase compute nodes
- Enabling result-set caching on the serverless endpoint
Correct answer: Using Parquet format with folder partitioning and creating table statistics
Parquet enables predicate and projection pushdown, folder-based partitioning eliminates unnecessary file reads via partition pruning, and statistics help the query optimizer choose efficient plans.
Question 14: Which Azure Synapse dedicated SQL pool feature stores query results so that subsequent identical queries return instantly without re-execution?
- Materialized views
- Result-set caching (Correct answer)
- Workload management queuing
- Columnstore segment elimination
Correct answer: Result-set caching
Result-set caching stores the complete output of a query and serves it from cache for identical subsequent queries, dramatically reducing latency for repeated analytical queries.
Question 15: What is the purpose of workload management in Azure Synapse Analytics dedicated SQL pools?
- Scheduling Synapse pipeline trigger runs by time
- Allocating and isolating compute resources for different user groups to enforce query SLAs (Correct answer)
- Managing autoscaling of Apache Spark cluster nodes
- Controlling data-level access permissions for external tables
Correct answer: Allocating and isolating compute resources for different user groups to enforce query SLAs
Workload management lets administrators define workload groups with specific resource minimums and caps, ensuring critical workloads receive guaranteed compute resources and concurrency slots.
Question 16: In Azure Synapse Analytics dedicated SQL pools, which T-SQL object must be created first to define the location and authentication for an external data source like Azure Data Lake Storage Gen2?
- DATABASE SCOPED CREDENTIAL
- EXTERNAL DATA SOURCE (Correct answer)
- EXTERNAL FILE FORMAT
- EXTERNAL TABLE
Correct answer: EXTERNAL DATA SOURCE
CREATE EXTERNAL DATA SOURCE defines the URI location and credential reference for the external storage, which is then referenced by EXTERNAL TABLE definitions using PolyBase.
Question 17: What feature of Azure Synapse Analytics dedicated SQL pools allows organizations to stop compute charges during periods of inactivity without losing data?
- Autoscale down to DW100c
- Serverless fallback mode
- Pause and resume (Correct answer)
- Elastic pool consolidation
Correct answer: Pause and resume
Pausing a dedicated SQL pool stops the compute layer and associated DWU charges while all data stored in Azure Storage is preserved and instantly available upon resume.
Question 18: What is Azure Synapse Studio?
- A command-line tool for deploying ARM templates to Synapse workspaces
- A web-based unified IDE for SQL development, Spark notebooks, pipeline authoring, and monitoring (Correct answer)
- A desktop application for managing Azure subscription costs
- A dedicated tool for configuring Synapse network security and private endpoints
Correct answer: A web-based unified IDE for SQL development, Spark notebooks, pipeline authoring, and monitoring
Synapse Studio is a web-based development environment embedded in the Azure portal that consolidates SQL scripting, Spark notebook authoring, pipeline design, data exploration, and monitoring.
Question 19: Which Azure Synapse Analytics component enables federated queries that join data from Azure Data Lake Storage, Cosmos DB, and other sources without physically moving the data?
- Dedicated SQL pool with external tables
- Serverless SQL pool with OPENROWSET or external tables (Correct answer)
- Apache Spark pool with cross-source dataframes
- Synapse Link multi-source federation
Correct answer: Serverless SQL pool with OPENROWSET or external tables
Serverless SQL pool uses OPENROWSET or external table syntax to query multiple external data sources in a single query, federating results across sources without data movement.
Question 20: What happens to data stored in an Azure Synapse Analytics dedicated SQL pool when the pool is paused?
- Data is archived to Azure Blob cold storage tier
- Data is temporarily deleted and restored on resume
- Data remains intact in Azure Storage and is fully preserved (Correct answer)
- Data is compressed into a backup snapshot and held offline
Correct answer: Data remains intact in Azure Storage and is fully preserved
Dedicated SQL pools separate compute and storage; pausing only stops the compute nodes while data in Azure Storage remains untouched and is immediately available when the pool is resumed.
Question 21: Which distribution strategy should you choose for a small lookup table under 2 GB that is frequently joined with large distributed fact tables in Azure Synapse dedicated SQL pools?
- HASH distribution on the join key column
- ROUND_ROBIN distribution for uniform load
- REPLICATE distribution to cache the table on every compute node (Correct answer)
- PARTITION BY date to align with the fact table partition
Correct answer: REPLICATE distribution to cache the table on every compute node
REPLICATE distribution caches the entire small table on each compute node, so joins with large hash-distributed fact tables require no cross-node data movement.
DP-203 Azure Data Engineer Associate Azure Synapse Analytics
How many distributions does an Azure Synapse Analytics dedicated SQL pool use to spread data across compute nodes?
What Practice Test Geeks adds beyond an AI chat for DP-203 Azure Data Engineer Associate
An AI chatbot can quiz you, but it can't reproduce the exam conditions or keep your record. Here you get:
- A fixed DP-203 Azure Data Engineer Associate question bank: the same test every attempt, so your score is comparable from one try to the next instead of changing with every regenerated quiz.
- Progress saved on your device: completed tests, scores and missed questions stay in My Progress, so you can retake only what you got wrong.
- A printable DP-203 Azure Data Engineer Associate cheat sheet you can print or save as a PDF.
- DP-203 Azure Data Engineer Associate flashcards built from the same question bank.
These practice questions were written with AI tools. They are not real exam questions. Exam facts are checked against the official sources listed below. Not reviewed by a subject-matter expert. Editorial policy
Sources & references
Exam facts on this page (fees, format, dates, passing scores) are checked against these official sources; the date is our last check. Editorial policy
- learn.microsoft.com — Eligibility, renewal, time limit, score release, score validity, passing score · verified Nov 2026
- pearsonvue.com — Test delivery · verified Nov 2026