Knowledge + Practice

Microsoft Azure Data Engineer Associate DP-203 (DP-203) — Questions 1–75

509 questions total · 7pages · All types, answers revealed

Take a mock exam Exam hub

Page 1 of 7

1

MCQeasy

You need to store structured data in Azure. The data will be accessed by multiple applications using T-SQL queries, and you require automatic indexing and serverless compute. Which Azure service should you use?

A.Azure Cosmos DB with Core (SQL) API.

B.Azure Data Lake Storage Gen2 with hierarchical namespace.

C.Azure Blob Storage with a flat namespace.

D.Azure SQL Database with serverless compute tier.

AnswerD

Azure SQL Database is a fully managed relational database service that supports T-SQL queries, automatic indexing, and a serverless compute tier that automatically scales compute based on workload. It provides built-in high availability and security. This service directly meets the requirements for structured data, T-SQL access, and serverless compute with automatic indexing.

Why this answer

Azure SQL Database is a relational database service that natively supports T-SQL, offers automatic indexing through features like automatic tuning, and provides a serverless compute tier that scales automatically. It is the appropriate choice for structured data accessed via T-SQL with serverless compute requirements. Other options lack T-SQL support or are not relational databases.

Exam trap

The trap here is assuming that any Azure storage or database service supports T-SQL; only relational services like Azure SQL Database do.

Full explanation →

2

Multi-Selectmedium

You are securing an Azure Data Lake Storage Gen2 account that contains sensitive data. Which TWO of the following should you implement to protect data from unauthorized access?

Select 2 answers

A.Configure ACLs to grant least privilege to users and groups

B.Use private endpoints to restrict access to the storage account

C.Set the default ACL to allow read access for all authenticated users

D.Enable CORS rules to allow only specific origins

E.Enable large file shares on the storage account

AnswersA, B

POSIX-style ACLs on Data Lake Storage Gen2 apply at directory and file level, enforcing least-privilege access for individual users and groups independently of broad role assignments. This granular permission model directly prevents unauthorised reads by limiting each principal to only the paths they require.

Why this answer

Option A is correct because Azure Data Lake Storage Gen2 uses POSIX-style access control lists (ACLs) at both the directory and file level, and configuring ACLs to grant least privilege ensures users and groups only receive the specific read/write/execute permissions they require, directly preventing unauthorized access to sensitive data. Option B is correct because private endpoints assign a private IP address from your virtual network to the storage account, removing exposure to the public internet and restricting access to only clients within the approved virtual network or connected networks. Option C is incorrect because setting a default ACL that allows read access to all authenticated users violates least privilege and would broaden, not restrict, access to sensitive data.

Option D is incorrect because CORS rules only control which web origins can make cross-origin browser requests to the service; they do not authenticate users or prevent unauthorized direct access. Option E is incorrect because enabling large file shares only increases the maximum capacity and file size limits of the file share, and has no effect on access control or authorization.

Exam trap

DP-203 often tests the confusion between network-layer controls (private endpoints, firewall rules) and identity-layer controls (RBAC, ACLs) — candidates who pick CORS or large file shares mistake non-security features for access controls.

Full explanation →

3

MCQhard

Your company runs a streaming job in Azure Stream Analytics that ingests data from Event Hubs and outputs to Azure Synapse Analytics. The job is failing with a 'Watermark delay' alert and the output to Synapse is delayed by over 30 minutes. The input rate is 5,000 events per second. The job uses a 1-minute tumbling window. What is the most likely cause of the delay?

A.The output schema in Synapse does not match the Stream Analytics output.

B.The Event Hubs has a large number of late-arriving events.

C.The tumbling window size is too large.

D.The Stream Analytics job is under-provisioned in terms of Streaming Units (SUs).

AnswerD

Insufficient Streaming Units cap the job's processing throughput, so it cannot keep pace with 5,000 events per second. The backlog grows, inflating watermark delay and delaying Synapse output beyond 30 minutes. Scaling SUs raises parallel processing capacity, clearing the bottleneck and restoring timely windowed output.

Why this answer

A watermark delay alert in Azure Stream Analytics indicates that the job is falling behind in processing incoming data. With an input rate of 5,000 events per second and a 1-minute tumbling window, the job requires sufficient Streaming Units (SUs) to keep up. Under-provisioned SUs cause backpressure, leading to output delays exceeding 30 minutes.

Exam trap

The trap here is that candidates may confuse a watermark delay alert with late-arriving events (Option B), but the alert indicates the job is falling behind overall, not just handling late data, and the 30-minute delay points to insufficient compute resources rather than data timing issues.

How to eliminate wrong answers

Option A is wrong because a schema mismatch between Stream Analytics output and Synapse would cause data write errors or failures, not a watermark delay alert or a 30-minute output delay. Option B is wrong because a large number of late-arriving events would increase the watermark delay but the alert specifically indicates the job is falling behind overall processing, not just handling late data; late events are managed by the late arrival policy and do not inherently cause a 30-minute delay. Option C is wrong because a 1-minute tumbling window is small and appropriate for real-time analytics; a larger window would reduce processing frequency, not cause delay.

Full explanation →

4

MCQeasy

You have an Azure Data Factory pipeline that must copy data from an on-premises Oracle database to Azure Blob Storage every night. The on-premises server cannot accept inbound connections, and no VPN or ExpressRoute is available. You need to enable connectivity with minimal administrative overhead. What should you deploy?

A.An Azure VPN Gateway with a site-to-site connection to the on-premises network.

B.A self-hosted integration runtime installed on a machine in the on-premises network.

C.An Azure ExpressRoute circuit with private peering to the on-premises datacenter.

D.An Azure Integration Runtime with a managed virtual network and private endpoints to the Oracle server.

AnswerB

The self-hosted integration runtime is designed exactly for this case: it runs on an on-premises machine and makes outbound connections to Azure Data Factory over HTTPS, so no inbound firewall rules or VPN are required. It supports Oracle as a source and Blob Storage as a sink, and installation is lightweight, matching the minimal-overhead requirement.

Why this answer

The self-hosted integration runtime is the standard Azure Data Factory mechanism for reaching on-premises data sources when inbound connectivity is not possible. It initiates outbound HTTPS connections to the Data Factory service, so it works without VPN, ExpressRoute, or firewall changes. It also supports the Oracle connector and Blob Storage sink required by this nightly copy pipeline.

Exam trap

The trap here is assuming network-level connectivity such as VPN or ExpressRoute is required for on-premises access, when the self-hosted integration runtime already solves it with outbound-only connections.

Full explanation →

5

MCQeasy

You are implementing a data processing solution in Azure Databricks. The solution must read data from Azure Data Lake Storage Gen2, transform it using PySpark, and write the results back to a different location in the same storage account. You need to authenticate to the storage account securely without storing secrets in the notebook. What should you use?

A.Service Principal with a client secret stored in the notebook

B.Azure Key Vault-backed secret scope

C.Shared access signature (SAS) token

D.Storage account access key

AnswerB

A Key Vault-backed secret scope satisfies the no-secrets-in-notebook constraint by retrieving credentials at runtime from Azure Key Vault rather than embedding them in code. Databricks resolves `dbutils.secrets.get()` calls through the scope, so storage access keys or service principal secrets never appear in the notebook or its revision history.

Why this answer

Azure Key Vault-backed secret scopes allow you to securely reference secrets without storing them in the notebook. Option A is wrong because storing a client secret directly in the notebook is insecure. Option C is wrong because SAS tokens can be exposed and are less secure.

Option D is wrong because storage account access keys are long-lived and should not be used in notebooks.

Full explanation →

6

MCQmedium

Your company uses Azure Data Lake Storage Gen2 and needs to implement a data retention policy that automatically deletes files older than 90 days in a specific container. What should you use?

A.Azure Data Factory pipeline with a Delete activity scheduled to run daily.

B.Azure Policy with a deny effect for files older than 90 days.

C.Azure Storage lifecycle management rule with a filter for the container and a delete action after 90 days.

D.Azure Purview data lifecycle policy.

AnswerC

Lifecycle management rules evaluate blob age and apply actions automatically, so a rule scoped to the container with a delete action after 90 days enforces the retention policy without custom code. This matches Azure Data Lake Storage Gen2's native tiering and expiry capability.

Why this answer

Azure Storage lifecycle management policies are the native, server-side mechanism for automatically transitioning or deleting blobs based on age. A rule can be scoped with a prefix filter matching the specific container and configured with a delete action after 90 days, executing automatically without any external orchestrator. This is the correct, cost-effective, and operationally simple solution for ADLS Gen2 retention.

Exam trap

DP-203 often tests whether candidates reach for custom orchestration (Data Factory) or governance tooling (Azure Policy, Purview) when the native, purpose-built feature — Storage lifecycle management — is the correct and simplest answer.

How to eliminate wrong answers

Option A is wrong because an Azure Data Factory pipeline with a Delete activity is a custom, scheduled workaround that adds compute cost, requires orchestration monitoring, and is unnecessary when native lifecycle rules exist. Option B is wrong because Azure Policy enforces resource-level governance (e.g., allowed SKUs, tags) and cannot evaluate or delete individual blob files based on age — it has no per-object lifecycle capability. Option D is wrong because Azure Purview is a data governance/catalog service; it does not execute storage deletion actions on a schedule.

Full explanation →

7

MCQmedium

You are a data engineer at a financial services company. You have an Azure Data Lake Storage Gen2 account named finlake that stores sensitive transaction data in Parquet files. You need to ensure that data is encrypted at rest using a customer-managed key stored in Azure Key Vault, and that the key is automatically rotated every 90 days. You also need to be able to revoke access to the data immediately if the key is compromised. What should you do?

A.Enable infrastructure encryption (double encryption) on the storage account and store the keys in a hardware security module (HSM) without configuring rotation.

B.Configure Azure Storage encryption with customer-managed keys in Azure Key Vault, set the key rotation policy to 90 days, and grant the storage account access to the key via a managed identity.

C.Enable Azure Storage Service Encryption with Microsoft-managed keys and configure a lifecycle management policy to rotate keys every 90 days.

D.Use Azure Disk Encryption with BitLocker keys stored in Azure Key Vault and apply the encryption to the storage account's underlying disks.

AnswerB

This option uses customer-managed keys stored in Azure Key Vault, which allows you to control rotation and revoke access by disabling or deleting the key. Configuring a rotation policy in Key Vault automates rotation. Granting the storage account a managed identity with access to the key vault enables secure key usage without storing secrets.

Why this answer

To encrypt data at rest in Azure Data Lake Storage Gen2 with customer-managed keys, you must configure the storage account to use a key stored in Azure Key Vault. A managed identity assigned to the storage account grants access to the key vault. You can set a rotation policy in Key Vault to automatically rotate the key every 90 days.

If the key is compromised, disabling or deleting it immediately revokes access to the data.

Exam trap

The trap here is assuming that Microsoft-managed keys can be rotated on a custom schedule or that revoking them is possible; only customer-managed keys provide that level of control.

Full explanation →

8

MCQeasy

You need to secure data at rest for an Azure Data Lake Storage Gen2 account that contains sensitive financial data. Which configuration should you enable to ensure that data is encrypted using a customer-managed key stored in Azure Key Vault, and that access to the key is logged?

A.Enable Azure Storage encryption with Microsoft-managed keys

B.Implement client-side encryption using Azure Key Vault

C.Enable infrastructure encryption for double encryption

D.Configure Azure Storage encryption with customer-managed keys in Azure Key Vault and enable Key Vault logging

AnswerD

Customer-managed keys in Azure Key Vault replace Microsoft-managed keys for storage encryption, and enabling Key Vault logging records every key access. Together these satisfy both the CMK encryption requirement and the key-access auditing constraint for the financial data.

Why this answer

Azure Storage encryption with customer-managed keys in Key Vault provides control and logging. Option A is wrong because Microsoft-managed keys are the default but do not provide customer control. Option B is wrong because client-side encryption requires managing keys on the client side.

Option C is wrong because infrastructure encryption adds a second layer but does not use customer-managed keys.

Full explanation →

9

MCQhard

You have a mission-critical pipeline that processes financial transactions in Azure Synapse Analytics. The pipeline uses Azure Data Factory with a mapping data flow to transform data. You need to ensure high availability and minimal data loss in case of a regional failure. What should you implement?

A.Store the source data in an RA-GRS storage account and use Azure Data Factory to copy from the secondary endpoint.

B.Configure the pipeline to retry on failure and manually restore from backup.

C.Use Azure SQL Database active geo-replication as the source.

D.Use Azure Synapse Link for Cosmos DB to enable near real-time analytics with multi-region writes.

AnswerA

Correct. GRS storage provides geo-redundancy, and the pipeline can be configured to read from the secondary endpoint during a regional failure, ensuring high availability and minimal data loss.

Why this answer

Using an RA-GRS storage account ensures geo-redundancy with a readable secondary endpoint, and Azure Data Factory can copy data from that secondary endpoint in case of a regional failure. This provides high availability and minimal data loss for the source data, while the mapping data flow transformation can proceed after the copy. Option D is incorrect because Azure Synapse Link for Cosmos DB changes the architecture (using Cosmos DB as source) and does not directly provide high availability for the existing ADF pipeline with mapping data flows; it introduces a different technology stack that may not meet the requirement of pipeline resilience.

Exam trap

Candidates may think that adding a new technology like Synapse Link solves HA, but it's about pipeline architecture, not the source system.

Full explanation →

10

MCQhard

You have a streaming pipeline using Azure Stream Analytics that ingests data from Event Hubs and outputs to Azure Synapse Analytics. The job has a high watermark delay and is falling behind. You need to reduce the latency. Which action should you take?

A.Add more partitions to the Event Hubs.

B.Increase the number of Streaming Units (SUs) for the Stream Analytics job.

C.Replace the output with Azure Functions for each event.

D.Change the input to a reference data input.

AnswerB

Streaming Units provision compute and throughput for the job; insufficient SUs cause the watermark delay to grow as the job falls behind. Increasing SUs adds parallel processing capacity, directly reducing latency, whereas partitioning or query changes alone may not resolve resource starvation.

Why this answer

Increasing the number of Streaming Units (SUs) for the Stream Analytics job allocates more compute resources, reducing latency. Adding more Event Hubs partitions may improve throughput but not directly reduce latency if the job is already bottlenecked. Switching to reference data input does not help.

Using Azure Functions for output may add overhead.

Full explanation →

11

Multi-Selecthard

You are optimizing the performance of a large-scale batch processing job in Azure Databricks. The job reads data from Azure Data Lake Storage Gen2, performs transformations, and writes results back. You notice that the job is I/O bound. Which THREE strategies can improve performance? (Choose three.)

Select 3 answers

A.Use Delta Lake format and optimize the table with Z-ordering on frequently filtered columns.

B.Increase the number of partitions in the DataFrame to improve parallelism.

C.Cache the DataFrame in memory after reading to avoid re-reading from disk.

D.Reduce the number of shuffle partitions to minimize data movement.

E.Enable autoscaling on the cluster to add more nodes during processing.

AnswersA, B, C

Delta Lake with Z-ordering co-locates related data in the same files using multi-dimensional clustering on frequently filtered columns, enabling data skipping that reads far fewer files. This directly reduces I/O against Azure Data Lake Storage Gen2, satisfying the stem's I/O-bound constraint.

Why this answer

Option A is correct because Delta Lake with Z-ordering co-locates related data in the same set of files, enabling data-skipping so the I/O-bound job reads far fewer bytes when filtering on those columns. Option B is correct because increasing DataFrame partitions spreads the read/transform workload across more concurrent tasks, raising parallelism and better saturating the storage throughput available to the cluster. Option C is correct because caching the DataFrame in memory (or Delta cache) prevents repeated reads from ADLS Gen2, directly cutting the disk I/O that is the bottleneck.

Option D is not correct because reducing shuffle partitions lowers parallelism for wide transformations and typically increases per-task data volume, which does not relieve I/O-bound reads. Option E is not correct because autoscaling adds compute nodes but does not by itself reduce the I/O volume or improve data layout, so it is not a targeted fix for an I/O-bound workload.

Exam trap

A common trap is thinking that reducing shuffle partitions (Option D) or enabling autoscaling (Option E) directly address I/O bottlenecks. However, shuffle partitions affect shuffle performance, not storage I/O, and autoscaling adds compute resources, not storage I/O bandwidth. Candidates may also incorrectly believe that caching is only beneficial for compute-bound jobs, but it also reduces I/O.

Full explanation →

12

MCQmedium

Your organization uses Azure Data Lake Storage Gen2 for a data lake. You need to prevent accidental deletion of data by enabling a soft delete policy. Which configuration is required?

A.Apply an Azure Resource Manager lock.

B.Configure Azure Backup for the storage account.

C.Enable blob versioning.

D.Enable blob soft delete on the storage account.

AnswerD

Blob soft delete retains deleted blobs for a configurable retention period, letting you recover data removed accidentally. Enabling it at the storage account level protects all containers, satisfying the requirement to prevent accidental deletion in the Data Lake Storage Gen2 account.

Why this answer

Azure Data Lake Storage Gen2 supports blob soft delete, which protects against accidental deletion by retaining deleted blobs for a specified retention period. Option A is incorrect because Azure Resource Manager locks prevent the deletion or modification of the storage account itself, not the data within it. Option B is incorrect because Azure Backup is designed for backing up VMs, SQL databases, and other workloads, not for managing soft delete of blobs.

Option C is incorrect because blob versioning preserves previous versions of blobs, but it does not prevent deletion of the current version; soft delete is specifically for recovery from accidental deletion.

Full explanation →

13

MCQeasy

You store sensitive data in Azure Data Lake Storage Gen2. You need to ensure that only members of a specific security group can read the data, while other users in the organization must not have access, even if they have the Storage Blob Data Reader role at the storage account level. What should you use?

A.Azure Private Link with a private endpoint for the storage account.

B.Access control lists (ACLs) on the directory or file, with entries for the security group and a mask.

C.Shared access signatures (SAS) scoped to the container.

D.Azure role-based access control (RBAC) at the storage account level.

AnswerB

ACLs in Azure Data Lake Storage Gen2 allow you to grant permissions to specific Microsoft Entra ID security groups at the directory or file level. By setting an ACL entry for the group and a mask, you can ensure that only members of that group have read access, even if others have broader RBAC roles. ACLs are evaluated together with RBAC to determine effective permissions.

Why this answer

Access control lists in Azure Data Lake Storage Gen2 provide file and directory-level permissions that can be assigned to Microsoft Entra ID security groups. By granting read access to the specific group and using a mask, you ensure that only group members can read the data. RBAC roles at the account level are too broad and cannot restrict access to a subset of users.

Exam trap

The trap here is assuming that account-level RBAC roles or network controls like Private Link can restrict access to a specific group, when only POSIX-style ACLs on directories or files provide that granularity.

Full explanation →

14

MCQmedium

You manage an Azure Data Lake Storage Gen2 account containing a large volume of JSON files. Users report that direct read operations from the data lake are slow, and you observe high egress costs. You need to optimize read performance and reduce cost for analytical queries that frequently filter on a specific timestamp column and select a subset of columns. What should you do?

A.Convert the files to Parquet format and partition the data by the timestamp column.

B.Enable Azure Storage analytics logging and review the logs to identify slow queries.

C.Increase the number of partitions in the Azure Synapse Analytics dedicated SQL pool that reads the data.

D.Move the data to a premium block blob storage account with a higher throughput tier.

AnswerA

Parquet is a columnar format that enables column pruning and predicate pushdown, reducing I/O and cost. Partitioning by the timestamp column further limits the data scanned when queries filter on that column. This directly addresses slow reads and high egress by minimizing the amount of data transferred and processed.

Why this answer

Converting JSON to Parquet reduces storage size and enables columnar reads, while partitioning by the frequently filtered timestamp column allows the query engine to skip irrelevant data. Together, these changes minimize the data scanned and transferred, improving read performance and lowering egress costs. Other options either do not address the root cause or introduce unnecessary expense.

Exam trap

The trap here is assuming that simply moving data to a higher-performance storage tier or increasing compute resources will solve read performance and cost issues, without addressing the inefficient data format and lack of partitioning.

Full explanation →

15

MCQeasy

You are tasked with transforming data in an Azure Synapse Analytics pipeline using a mapping data flow. The source data contains a column 'FullName' in the format 'LastName, FirstName'. You need to split this into two separate columns: 'LastName' and 'FirstName'. Which transformation should you use?

A.Pivot transformation

B.Aggregate transformation

C.Lookup transformation

D.Derived Column transformation

AnswerD

Derived column can use expressions to split strings.

Why this answer

The Derived Column transformation is correct because it allows you to create new columns by applying expressions to existing data. In this case, you can use string functions like `split()` or `substring()` and `locate()` to parse 'FullName' into 'LastName' and 'FirstName' based on the comma delimiter. This transformation operates row-by-row, making it ideal for simple column splits.

Exam trap

The trap here is that candidates often confuse the Derived Column transformation with the Split transformation (which does not exist in mapping data flows) or mistakenly think the Pivot transformation can re-arrange column data, when in fact Derived Column is the correct choice for column-level string operations.

How to eliminate wrong answers

Option A is wrong because the Pivot transformation is used to rotate data from rows into columns by aggregating values, not for splitting a single column into multiple columns. Option B is wrong because the Aggregate transformation is designed to perform calculations like sum, count, or average over groups of rows, not for row-level string manipulation. Option C is wrong because the Lookup transformation is used to join data from a reference dataset based on a key, not to parse or split column values.

Full explanation →

16

MCQhard

Your team uses Azure Databricks for data processing. You need to implement a cost-control strategy that automatically terminates idle clusters after 30 minutes of inactivity, but allows users to override this policy for specific workloads that require long-running clusters. What is the most efficient approach?

A.Instruct all users to set auto-termination to 30 minutes on each cluster they create.

B.Configure a global auto-termination setting in the Azure Databricks workspace that terminates all clusters after 30 minutes of inactivity.

C.Use Azure Policy to enforce a tag that triggers a function to terminate idle clusters.

D.Create a cluster policy that enforces auto-termination with a default of 30 minutes, but allows users to override the value for specific clusters.

AnswerD

A cluster policy sets auto-termination at 30 minutes by default while permitting overrides, so idle clusters terminate automatically yet long-running workloads can extend the timeout. This satisfies both the cost-control and override requirements without manual intervention.

Why this answer

Azure Databricks cluster policies let administrators define constraints and defaults for cluster configuration, including auto-termination. A cluster policy can set a default auto-termination of 30 minutes while allowing users to override the value (within policy-permitted bounds) for specific long-running workloads. This centralizes governance without blocking legitimate exceptions.

Exam trap

DP-203 often tests the difference between a hard global limit (which removes flexibility) and a policy-based default with override (which balances governance and flexibility) — candidates who pick the global setting miss the override requirement.

How to eliminate wrong answers

Option A is wrong because relying on users to manually set auto-termination is not enforced and will inevitably be missed, defeating the cost-control objective. Option B is wrong because a global workspace setting that terminates all clusters after 30 minutes removes the ability for users to override for long-running workloads, violating the requirement. Option C is wrong because Azure Policy operates on Azure resource metadata and cannot directly terminate idle Databricks clusters; it is the wrong control plane and would require custom automation that is far less efficient than a native cluster policy.

Full explanation →

17

MCQhard

You are migrating a large on-premises SQL Server database to Azure Synapse Analytics. The database includes tables with up to 500 million rows and frequent updates. You need to minimize data movement during the migration while ensuring optimal query performance in the dedicated SQL pool. Which table design strategy should you use?

A.Use hash-distributed tables for all tables and clustered columnstore indexes.

B.Use replicated tables for all fact tables and hash-distributed tables for dimension tables.

C.Use round-robin tables for all tables to simplify the migration.

D.Use round-robin tables for staging tables and hash-distributed tables for large fact tables on a key column.

AnswerD

Round-robin distribution suits staging tables by balancing loads evenly without a join key, while hash distribution on a high-cardinality fact column co-locates related rows, reducing data movement during joins and aggregations in the dedicated SQL pool.

Why this answer

It uses round-robin tables for staging to minimize data movement during the initial load, then hash-distributes large fact tables on a key column to optimize query performance by collocating rows with the same distribution key on the same compute node. This balances the need for fast ingestion with efficient parallel query execution in Azure Synapse Analytics dedicated SQL pools.

Exam trap

The trap here is that candidates often assume hash-distributed tables are always the best choice for all tables, overlooking the fact that round-robin tables reduce data movement during migration and that hash distribution should be reserved for large fact tables to avoid skew and unnecessary shuffling.

How to eliminate wrong answers

Option A is wrong because using hash-distributed tables for all tables, including small dimension tables, can cause unnecessary data shuffling and skew, and clustered columnstore indexes are not optimal for tables with frequent updates due to high overhead in maintaining columnstore segments. Option B is wrong because replicated tables are designed for small dimension tables (typically < 2 GB), not for large fact tables with up to 500 million rows, as replicating such large tables would cause excessive storage and data movement. Option C is wrong because round-robin tables distribute data randomly across distributions, leading to poor query performance due to data movement during joins and aggregations, and are not suitable for production fact tables in a dedicated SQL pool.

Full explanation →

18

MCQmedium

You have an Azure Databricks workspace that processes sensitive data. The security team requires that all access to the workspace be authenticated using Microsoft Entra ID and that all API calls be audited. Which configuration should you implement?

A.Configure workspace to use Microsoft Entra ID authentication and enable diagnostic settings for audit logs.

B.Enable VNet injection and configure network security groups.

C.Deploy Azure Private Link and disable public access.

D.Configure personal access tokens for API access and enable cluster logs.

AnswerA

Configuring the workspace for Microsoft Entra ID authentication enforces identity-based access, while diagnostic settings stream audit logs to a Log Analytics workspace or storage account. Together these satisfy both constraints: Entra ID authentication for all access and auditable API calls.

Why this answer

To meet both requirements—Microsoft Entra ID authentication and audited API calls—you must configure the workspace to use Entra ID authentication and enable diagnostic settings to send audit logs to a destination like Log Analytics. This combination ensures identity-based access and a record of API activity.

Exam trap

The trap is focusing on network security (Private Link, VNet injection) when the question explicitly asks for authentication and auditing; candidates may overlook the need for diagnostic settings to capture API calls.

How to eliminate wrong answers

Option B is wrong because VNet injection and NSGs address network isolation, not authentication or API auditing. Option C is wrong because Private Link and disabling public access improve network security but do not enforce Entra ID authentication or provide API audit logs. Option D is wrong because personal access tokens are a separate authentication mechanism that does not use Entra ID, and cluster logs do not capture all API calls for auditing.

Full explanation →

19

MCQeasy

Your organization uses Azure Data Factory to orchestrate data pipelines. You need to ensure that sensitive data is not exposed in pipeline logs. What should you configure?

A.Store connection strings in Azure Key Vault.

B.Enable 'Secure output' on pipeline activities.

C.Set a retention policy for pipeline logs.

D.Use data flow debug logs with session logs.

AnswerB

Enabling 'Secure output' prevents activity output from being written to Data Factory monitoring logs, directly satisfying the requirement that sensitive data must not appear in pipeline logs. Unlike 'Secure input', which masks only input values, this setting suppresses the logged output itself, ensuring credentials or personal data captured during execution remain hidden.

Why this answer

Enabling 'Secure output' on pipeline activities prevents sensitive data from being written to Azure Data Factory logs. Option A is incorrect because while storing connection strings in Azure Key Vault is a security best practice, it does not prevent sensitive data from appearing in pipeline logs. Option C is incorrect because setting a retention policy for logs controls how long logs are kept, but does not prevent sensitive data exposure.

Option D is incorrect because data flow debug logs are for debugging and do not mask sensitive data in pipeline logs.

Full explanation →

20

MCQmedium

You are designing a data processing solution in Azure Synapse Analytics. The solution must process streaming data from Azure Event Hubs and store the results in a dedicated SQL pool. You need to choose the most appropriate service for near real-time ingestion with minimal latency. What should you use?

A.Azure Databricks with Structured Streaming

B.Azure Stream Analytics

C.Azure Data Factory

D.Azure Functions with Event Hub trigger

AnswerB

Azure Stream Analytics is a fully managed, serverless stream-processing engine that ingests directly from Event Hubs and writes to a dedicated SQL pool, delivering sub-second near real-time latency without provisioning clusters. This satisfies the stem's minimal-latency ingestion constraint, unlike batch-oriented alternatives such as Synapse pipelines or Spark structured streaming jobs.

Why this answer

Azure Stream Analytics is the correct choice because it is purpose-built for near real-time stream processing with sub-second latency, directly integrates with Azure Event Hubs as an input source and dedicated SQL pool as an output sink, and provides a SQL-like query language for defining transformations. This minimizes architectural complexity and latency compared to other services.

Exam trap

The trap here is that candidates often confuse 'near real-time' with 'batch processing' and choose Azure Data Factory (option C) because it is a familiar data integration tool, overlooking that it lacks native streaming capabilities and introduces latency from scheduled pipeline runs.

How to eliminate wrong answers

Option A is wrong because Azure Databricks with Structured Streaming introduces additional overhead from cluster startup time and micro-batch processing, which typically results in higher latency (seconds to minutes) compared to Stream Analytics' continuous processing model. Option C is wrong because Azure Data Factory is a batch-oriented ETL/ELT orchestration service that does not support native streaming ingestion; it polls sources on a schedule, introducing minutes of latency. Option D is wrong because Azure Functions with Event Hub trigger processes events one at a time in a serverless compute model, which can lead to cold-start delays and lacks built-in windowing, aggregation, and exactly-once semantics for streaming workloads.

Full explanation →

21

MCQmedium

Your company uses Azure Purview for data governance. You need to ensure that sensitive data in Azure Data Lake Storage Gen2 is automatically detected and classified. What should you configure in Purview?

A.Apply sensitivity labels to the storage account using Microsoft Purview Information Protection.

B.Enable Microsoft Defender for Cloud's data sensitivity discovery.

C.Use Azure Policy to enforce tagging of resources containing sensitive data.

D.Create a scan rule set that includes built-in classification rules for sensitive data types.

AnswerD

A scan rule set containing built-in classification rules tells Purview which sensitive data types to detect during scans of Data Lake Storage Gen2. Classification then applies automatically, satisfying the requirement for automatic detection and classification of sensitive data.

Why this answer

In Microsoft Purview, you can create scan rule sets that include built-in classification rules to automatically detect sensitive data types during scanning. Option A is incorrect because sensitivity labels are applied after classification, not for detection. Option B is incorrect because Microsoft Defender for Cloud's data sensitivity discovery is a different feature; Purview itself handles classification.

Option C is incorrect because Azure Policy enforces compliance rules, not data classification at the file level.

Full explanation →

22

MCQhard

You are monitoring an Azure Synapse Analytics dedicated SQL pool and notice that queries against a large fact table are slow. The table is distributed using hash distribution on a column that has a high number of nulls. You need to improve query performance. What should you do?

A.Create a clustered columnstore index on the table and rebuild it.

B.Partition the table on the column with many nulls to isolate the null values.

C.Change the distribution to round-robin to evenly distribute the data.

D.Change the distribution column to a column with high cardinality and even distribution, such as a surrogate key.

AnswerD

Hash distribution on a column with many nulls causes skew because all nulls are placed in the same distribution. Choosing a column with high cardinality and even distribution (e.g., a surrogate key) ensures data is evenly spread across distributions, improving query parallelism and reducing data movement for joins on that column.

Why this answer

Hash distribution on a column with many nulls leads to data skew because all nulls hash to the same distribution. This causes one distribution to have more data and processing load, slowing down queries. Choosing a distribution column with high cardinality and even distribution, such as a surrogate key, ensures balanced data across distributions and improves query performance.

Exam trap

The trap here is focusing on indexing or partitioning when the root cause is distribution skew due to a poor choice of hash distribution column.

Full explanation →

23

MCQeasy

You need to grant a data analyst read access to a specific folder in an Azure Data Lake Storage Gen2 account. The analyst must not be able to read other folders in the same container. You want to follow the principle of least privilege. What should you use?

A.The Storage Blob Data Contributor role assigned at the storage account scope.

B.A POSIX access control list (ACL) on the target folder, with the analyst's Azure AD identity granted read and execute.

C.A storage account shared access signature scoped to the container.

D.A stored access policy on the container that allows read operations.

AnswerB

Data Lake Storage Gen2 supports POSIX-style ACLs at the directory and file level. Granting the analyst's Azure AD identity read and execute on the specific folder, without granting permissions on the parent or sibling folders, enforces least privilege. Execute permission on the parent directories is needed for traversal, but it does not grant read access to their contents.

Why this answer

POSIX ACLs in Data Lake Storage Gen2 allow permission entries at the directory and file level, so you can grant an identity read and execute on one folder while leaving sibling folders inaccessible. This is the native mechanism for fine-grained, least-privilege access. Broad role assignments and container-scoped SAS or policies grant access to the whole container and are therefore unsuitable here.

Exam trap

The trap here is confusing role-based access control, which is typically scoped at the account or container level, with directory-level POSIX ACLs that can isolate a single folder.

Full explanation →

24

Multi-Selectmedium

You are designing an ETL process in Azure Data Factory. You need to transform data using Mapping Data Flows. Which THREE of the following transformations are available in Mapping Data Flows?

Select 3 answers

A.Pivot

B.Derived Column

C.Union All

D.Aggregate

E.Merge Join

AnswersA, B, D

Pivot is a native Mapping Data Flows transformation, reshaping rows into columns through an aggregate-style pivot operation on selected group-by and pivot keys. It satisfies the stem's requirement for transformations available within Mapping Data Flows, unlike pipeline activities or external compute, running on the Spark execution engine.

Why this answer

In Azure Data Factory Mapping Data Flows, the Pivot transformation (A) is a built-in transformation that reshapes data by turning unique row values into columns, which is why it is correct. The Derived Column transformation (B) is also available and is used to create new columns or modify existing ones using expression builder logic, making it correct. The Aggregate transformation (D) is likewise a native Mapping Data Flows transformation that performs group-by operations and aggregations such as SUM, AVG, COUNT, and MIN/MAX, so it is correct.

Union All (C) is not a Mapping Data Flows transformation; combining multiple streams is done with the Union transformation, which behaves like a union (not specifically 'Union All' as a named transformation). Merge Join (E) is not a Mapping Data Flows transformation either; joins are performed with the Join transformation, and the term 'Merge Join' refers to a SQL Server/SSIS-style operator rather than an ADF Mapping Data Flows transformation.

Exam trap

The trap here is that candidates confuse the 'Union All' and 'Merge Join' names from other tools (like SSIS or T-SQL) with the actual transformation names in Azure Data Factory Mapping Data Flows, leading them to select options that sound familiar but are not available.

Full explanation →

25

MCQmedium

You need to design a near-real-time data processing solution that ingests IoT telemetry data from millions of devices. The data must be aggregated per minute and stored in Azure Cosmos DB for low-latency queries. Which Azure service combination should you use?

A.Azure Event Hubs -> Azure HDInsight (Kafka) -> Azure Cosmos DB

B.Azure Event Hubs -> Azure Stream Analytics -> Azure Cosmos DB

C.Azure IoT Hub -> Azure Databricks (Structured Streaming) -> Azure Cosmos DB

D.Azure Event Hubs -> Azure Data Factory -> Azure Cosmos DB

AnswerB

Event Hubs ingests millions of device telemetry streams at scale, Stream Analytics performs the per-minute tumbling-window aggregation, and Cosmos DB stores results for low-latency queries. This combination satisfies both the near-real-time ingestion requirement and the minute-level aggregation constraint.

Why this answer

Azure Stream Analytics provides native, low-latency windowed aggregation (e.g., TumblingWindow for per-minute aggregates) directly on data ingested from Event Hubs, and it has a built-in output sink to Azure Cosmos DB. This combination meets the near-real-time requirement without needing an intermediate compute or storage layer, minimizing end-to-end latency.

Exam trap

The trap here is that candidates often over-engineer the solution by adding a big-data processing layer (like HDInsight or Databricks) when a simpler, fully managed stream analytics service (Azure Stream Analytics) is the correct choice for fixed-window aggregation and direct Cosmos DB output.

How to eliminate wrong answers

Option A is wrong because Azure HDInsight (Kafka) introduces unnecessary complexity and latency for simple per-minute aggregation; it requires manual stream processing setup and is not optimized for direct, low-latency output to Cosmos DB. Option C is wrong because Azure Databricks Structured Streaming, while capable, adds startup and cluster management overhead that is not ideal for sub-minute latency, and IoT Hub is typically used for device management and bi-directional communication, not purely for high-throughput telemetry ingestion. Option D is wrong because Azure Data Factory is a batch-oriented orchestration service, not designed for near-real-time stream processing or windowed aggregation.

Full explanation →

26

MCQhard

You are designing a data processing solution using Azure Databricks. The solution must use Delta Lake for ACID transactions and must optimize storage costs by automatically compacting small files. Which feature should you enable?

A.Run OPTIMIZE command in a scheduled job.

B.Set retention duration for vacuum to 7 days.

C.Enable Z-order indexing on the Delta table.

D.Enable auto-optimize on the Delta table.

AnswerD

Auto-optimize automatically compacts small files during writes, removing the need for manual OPTIMIZE jobs while preserving Delta Lake ACID guarantees. This directly satisfies the storage-cost constraint by reducing many small files into larger ones without extra orchestration.

Why this answer

Auto-optimize (also called Optimized Writes / auto compaction) is a Delta Lake table property that automatically compacts small files during writes, eliminating the need for manual OPTIMIZE jobs. It combines with optimized writes to reduce the small-file problem at ingestion time, directly lowering storage and query costs. This is the native, built-in feature designed for the stated requirement.

Exam trap

DP-203 often tests the distinction between automatic table maintenance features (auto-optimize, optimized writes) and manual commands (OPTIMIZE, VACUUM, ZORDER), so candidates who see 'OPTIMIZE' and assume it means automatic compaction pick the wrong answer.

How to eliminate wrong answers

Option A is wrong because running OPTIMIZE in a scheduled job is a manual workaround that requires orchestration and only compacts periodically, not automatically — it does not satisfy 'automatically compacting.' Option B is wrong because VACUUM retention controls how long old file versions are kept before deletion; it has nothing to do with compaction of small files. Option C is wrong because Z-order indexing is a data-clustering technique applied via OPTIMIZE ZORDER BY to improve data-skipping on frequently filtered columns — it does not automatically compact small files.

Full explanation →

27

MCQeasy

You need to monitor the performance of your Azure Synapse Analytics dedicated SQL pool. Which metric should you use to identify queued queries due to concurrency limits?

A.Queued queries

B.DWU percentage

C.Active queries

D.Memory percentage

AnswerA

The queued queries metric counts requests waiting for a concurrency slot in the dedicated SQL pool. Rising values indicate that active queries have exhausted available slots, directly identifying concurrency-limit queuing rather than resource pressure such as DWU saturation or tempdb usage.

Why this answer

The 'Queued queries' metric in Azure Synapse Analytics dedicated SQL pool specifically counts queries waiting for resources due to concurrency limits, making it the direct indicator of queuing caused by insufficient concurrency slots. Monitoring this metric helps identify when the workload exceeds available concurrency and queries are being held in the queue.

Exam trap

DP-203 often tests the distinction between metrics that indicate resource saturation (DWU percentage, memory percentage) and those that specifically indicate concurrency queuing, so candidates must know that 'Queued queries' is the direct signal.

How to eliminate wrong answers

Option B is wrong because DWU percentage measures the utilization of Data Warehouse Units (compute resources) and does not directly indicate queued queries. Option C is wrong because Active queries shows queries currently executing, not those waiting in the queue. Option D is wrong because Memory percentage reflects memory consumption and is not the primary metric for concurrency-related queuing.

Full explanation →

28

MCQmedium

You are troubleshooting a slow-running pipeline in Azure Data Factory that uses a Copy activity to transfer data from Azure Blob Storage to Azure Synapse Analytics. The pipeline processes about 100 GB of CSV files. The copy performance is poor even though the source and sink are in the same region. What is the most likely cause?

A.The copy activity is not using staging and PolyBase

B.The source and sink are in different Azure regions

C.The Data Integration Unit (DIU) setting is too low

D.The source files are compressed

AnswerA

PolyBase dramatically improves load performance.

Why this answer

The Copy activity in Azure Data Factory uses PolyBase or COPY statement (staging) to bulk load data into Azure Synapse Analytics. Without staging and PolyBase, the default insert method is row-by-row, which is extremely slow for large datasets like 100 GB of CSV files. Enabling staging with PolyBase allows parallel, high-throughput loading, which is essential for performance at this scale.

Exam trap

The trap here is that candidates often assume DIU settings are the primary performance lever, but for Synapse sinks, the staging/PolyBase mechanism is the critical factor that can improve performance by orders of magnitude.

How to eliminate wrong answers

Option B is wrong because the question explicitly states that the source and sink are in the same region, so cross-region latency is not the issue. Option C is wrong because Data Integration Units (DIUs) control parallelism within the Copy activity, but even with maximum DIUs, the row-by-row insert into Synapse is the bottleneck, not the DIU setting. Option D is wrong because compressed source files can actually improve performance by reducing network transfer time, and ADF can decompress them efficiently; compression is not inherently a cause of poor copy performance.

Full explanation →

29

MCQmedium

A data engineering team is designing a storage solution for a retail company that receives point-of-sale (POS) transaction data from thousands of stores. The data arrives as JSON files in Azure Data Lake Storage Gen2. The team needs to query the data using Azure Synapse Analytics serverless SQL pool and optimize for performance and cost. The data is partitioned by year, month, and day. They want to minimize the amount of data scanned per query. What should they do?

A.Use Azure Data Explorer to ingest the JSON data and query it with KQL.

B.Convert the JSON files to Parquet format and use OPENROWSET with explicit file paths for each partition.

C.Keep the JSON files and use OPENROWSET with wildcards to read all files, then filter by date in the query.

D.Convert the JSON files to Parquet format and create an external table with partition columns (year, month, day) and use partition elimination in queries.

AnswerD

Converting to Parquet reduces storage size and improves query performance due to columnar storage and compression. Creating an external table with partition columns allows the serverless SQL pool to perform partition elimination, scanning only the relevant partitions based on query filters. This minimizes data scanned and reduces cost, meeting the requirements.

Why this answer

Converting JSON to Parquet and creating an external table with partition columns enables partition elimination in Synapse serverless SQL pool. This means queries that filter on year, month, or day will only scan the relevant partitions, significantly reducing the amount of data processed and lowering costs. Parquet's columnar format also improves performance by allowing column pruning.

Exam trap

The trap here is assuming that simply storing data in partitions is enough, but without using an external table with partition columns, the serverless SQL pool cannot eliminate partitions and will scan all data.

Full explanation →

30

MCQeasy

You are running a Python script in Azure Databricks that reads a CSV file from DBFS. The script runs successfully in an interactive notebook but fails when executed as a job with the error: 'Path does not exist: dbfs:/tmp/data.csv'. What is the most likely cause?

A.The job is using a different runtime that does not support Python.

B.The file is too large for DBFS.

C.The job cluster does not have permission to access DBFS.

D.The file was uploaded to the workspace filesystem, not to DBFS.

AnswerD

Workspace files are not automatically in DBFS.

Why this answer

The most likely cause is that the file was uploaded to the workspace filesystem (Workspace), not to DBFS. In interactive notebooks, the workspace filesystem is accessible via a symlink that makes it appear under `dbfs:/`, but when running as a job, the path `dbfs:/tmp/data.csv` does not resolve to workspace files. The file must be in the DBFS root or mounted location to be accessible via that path.

Option D is correct. Option A is incorrect because both interactive and job clusters support Python. Option B is incorrect because file size is not indicated as an issue.

Option C is incorrect because the job cluster automatically has permission to access DBFS.

Full explanation →

31

MCQeasy

A financial services company needs to store transactional data in Azure Cosmos DB. The data is accessed by multiple applications using different partition keys. The company requires strong consistency for financial transactions and wants to minimize latency for reads and writes. Which consistency level should they choose?

A.Bounded staleness

B.Strong

C.Eventual

D.Session

AnswerB

Strong consistency guarantees linearizability, ensuring reads always return the most recent committed write. This is critical for financial transactions where data accuracy is paramount. While it may increase latency compared to weaker levels, it meets the requirement for strong consistency and is suitable for scenarios where correctness outweighs latency.

Why this answer

Strong consistency in Azure Cosmos DB ensures that all reads see the most recent committed write, which is essential for financial transactions. Although it may introduce higher latency compared to weaker consistency levels, it provides the required correctness. The scenario prioritizes strong consistency over minimal latency, making Strong the appropriate choice.

Exam trap

The trap here is assuming that lower latency always outweighs consistency, leading to the selection of a weaker consistency level that fails the financial accuracy requirement.

Full explanation →

32

MCQhard

You have an Azure Databricks notebook that processes a large Delta table. The notebook uses a structured streaming query to read from the Delta table and write to another Delta table. The source table receives frequent updates and deletes. You need the streaming query to process both new data and changes (updates and deletes) from the source table. What should you do?

A.Convert the source table to an external table and use `spark.readStream.format("delta").option("mergeSchema", "true")` to read all changes.

B.Use `readStream` with the `startingVersion` option set to the latest version and `maxFilesPerTrigger` set to 1.

C.Configure the streaming query to use `readStream` with the `ignoreDeletes` option set to true.

D.Enable Change Data Feed on the source Delta table and configure the streaming query to read the change data feed.

AnswerD

Delta Lake Change Data Feed (CDF) records row-level changes (inserts, updates, deletes) in the source table. By enabling CDF and reading the change data feed in a streaming query, you can process all changes. This is the designed mechanism for capturing changes from a Delta table in a streaming fashion, and it supports both updates and deletes.

Why this answer

Delta Lake Change Data Feed (CDF) is the feature designed to capture row-level changes from a Delta table. When enabled, it records inserts, updates, and deletes, and a streaming query can read these changes as a stream. This allows downstream processing to react to all modifications, not just appends.

Enabling CDF and reading the change data feed is the correct approach to meet the requirement.

Exam trap

The trap here is assuming that a standard Delta streaming read can capture updates and deletes, when it only processes new files unless Change Data Feed is enabled.

Full explanation →

33

Multi-Selectmedium

Which TWO Azure services can be used to monitor and analyze query performance in Azure Synapse Analytics dedicated SQL pool?

Select 2 answers

A.SQL Data Sync

B.Azure Policy

C.Azure Advisor

D.Azure Monitor with Log Analytics

E.Dynamic Management Views (DMVs)

AnswersD, E

Dedicated SQL pool emits query execution metrics to Azure Monitor, and Log Analytics stores and queries them via KQL. This satisfies the monitoring requirement by correlating DMV-level query performance data across the pool, which neither storage nor networking services provide.

Why this answer

Azure Monitor with Log Analytics (D) is correct because dedicated SQL pool emits diagnostic logs and metrics (e.g., DmvLogs, QueryStoreRuntimeStatistics, ExecRequests) that can be routed to a Log Analytics workspace, where KQL queries and workbooks are used to monitor and analyze query performance over time. Dynamic Management Views (E) are correct because T-SQL DMVs such as sys.dm_pdw_exec_requests, sys.dm_pdw_exec_sessions, sys.dm_pdw_request_steps, and sys.dm_pdw_sql_requests expose real-time execution details, wait statistics, and step-level timings for queries running in the dedicated SQL pool. SQL Data Sync (A) is incorrect because it is a data synchronization service for Azure SQL Database and SQL Managed Instance, not a query performance monitoring tool for Synapse dedicated SQL pools.

Azure Policy (B) is incorrect because it enforces governance and compliance rules on resources, not query performance analysis. Azure Advisor (C) is incorrect because it provides general best-practice recommendations (cost, reliability, performance) at the resource level, not detailed query-level monitoring or analysis for dedicated SQL pools.

Exam trap

DP-203 often tests the distinction between real-time diagnostic tools (DMVs) and historical monitoring services (Azure Monitor), causing candidates to overlook DMVs as a monitoring option or confuse Azure Advisor's recommendations with actual performance monitoring.

Full explanation →

34

MCQmedium

You are designing a data lake architecture using Azure Data Lake Storage Gen2. You need to implement a least-privilege security model. Which authorization mechanism should you use for granular control?

A.Use storage account keys for access.

B.Use Azure RBAC roles at the storage account level.

C.Use POSIX-like access control lists (ACLs).

D.Use shared access signatures (SAS) with stored access policies.

AnswerC

POSIX-like ACLs grant per-file and per-directory permissions to individual security principals, exceeding what coarse role assignments allow. This satisfies the least-privilege requirement by scoping read, write and execute rights at the folder or file level within Azure Data Lake Storage Gen2.

Why this answer

POSIX-like access control lists (ACLs) in Azure Data Lake Storage Gen2 provide file- and directory-level granular permissions, enabling least-privilege access down to individual users or groups. They are the recommended mechanism when you need fine-grained control beyond what Azure RBAC roles at the container or storage account level can provide. ACLs support both access ACLs (for current access) and default ACLs (inherited by new child items).

Exam trap

DP-203 often tests the confusion between RBAC (coarse, management-plane, container-level) and ACLs (fine-grained, data-plane, file/directory-level) — candidates pick RBAC because it is the more familiar Azure authorization model, but it cannot deliver least-privilege at the file level.

How to eliminate wrong answers

Option A is wrong because storage account keys grant full administrative access to the entire storage account — the opposite of least privilege — and cannot be scoped to individual files or directories. Option B is wrong because Azure RBAC roles at the storage account level operate at the management plane and are too coarse for file-level granularity; they grant broad permissions like 'Storage Blob Data Reader' across the whole account or container. Option D is wrong because shared access signatures (SAS) with stored access policies are time-bound, delegated access tokens for external sharing or temporary access, not a mechanism for persistent, granular, identity-based least-privilege control within the data lake.

Full explanation →

35

MCQhard

You are designing a data processing solution for a retail company that uses Azure Synapse Analytics. The solution must process point-of-sale (POS) data from multiple stores. The data arrives in CSV files in Azure Data Lake Storage Gen2. Each store sends a file every hour. You need to process the files as they arrive and load the data into a dedicated SQL pool. The solution must handle late-arriving files (files that arrive after the scheduled processing time) and ensure that the data is consistent. Which approach should you use?

A.Use Azure Data Factory with a Copy activity to load data into a staging table, then use a Data Flow activity to perform upserts.

B.Use Azure Databricks to read the CSV files, perform upserts, and write to the dedicated SQL pool using JDBC.

C.Use PolyBase to create external tables on the CSV files and then use CREATE TABLE AS SELECT to load into the dedicated SQL pool.

D.Use Azure Data Factory with a Copy activity to load data into a staging table in the dedicated SQL pool, then use a Stored Procedure activity to merge the data into the final table.

AnswerD

Staging then merging via a Stored Procedure activity makes the load idempotent: late-arriving files are upserted on business keys rather than duplicated. This satisfies both the hourly arrival pattern and the consistency requirement in the dedicated SQL pool.

Why this answer

The recommended approach for handling late-arriving files and ensuring data consistency when loading into a dedicated SQL pool is to use Azure Data Factory with a Copy activity to load data into a staging table, then use a Stored Procedure activity to merge the data into the final table. This pattern allows for upserts and handles late-arriving data by merging based on business keys.

Exam trap

DP-203 often tests the choice between different data loading patterns, where candidates might overlook the need for upserts and late-arriving data handling, opting for simpler append-only methods like PolyBase CTAS, which do not ensure consistency.

How to eliminate wrong answers

Option A is wrong because while Data Flow can perform upserts, it may not be as efficient or straightforward for handling late-arriving files and ensuring consistency in a dedicated SQL pool; Data Flows are more suited for complex transformations but can be slower and more expensive for simple upserts. Option B is wrong because using Azure Databricks with JDBC to write to a dedicated SQL pool can work, but it introduces additional complexity and cost, and may not be the most streamlined approach for this scenario. Option C is wrong because PolyBase with CREATE TABLE AS SELECT (CTAS) is for bulk loading and does not handle upserts or late-arriving data; it would append data, leading to duplicates.

Full explanation →

36

MCQhard

You are designing a storage layer for a fraud detection system. The system writes millions of small JSON records per hour to Azure Data Lake Storage Gen2 and must support both batch analytics and interactive queries from Azure Databricks. You need to choose a storage format and layout that minimizes query latency for selective filters on customer ID while keeping storage costs predictable. What should you do?

A.Store the data as Delta Lake tables with Z-ORDER clustering on customer ID and optimize file sizes.

B.Store the data as Parquet files partitioned by customer ID and use partition pruning in Databricks.

C.Store the data as Avro files and use schema evolution to handle new fields.

D.Store the data as uncompressed JSON files and rely on Databricks caching to improve performance.

AnswerA

Delta Lake provides ACID transactions and metadata that Databricks can use for data skipping. Z-ORDER clustering colocates related customer ID values within files, so selective filters skip irrelevant data efficiently. Combined with OPTIMIZE to compact small files, this reduces latency and keeps storage costs predictable through compaction and retention policies, matching the scenario's needs.

Why this answer

Delta Lake with Z-ORDER clustering on customer ID enables data skipping by storing min/max statistics per file and colocating similar values. This directly reduces the data read for selective filters. OPTIMIZE compacts small files, which is critical when millions of small records arrive hourly, improving both batch and interactive query performance while keeping storage costs controlled.

Exam trap

The trap here is choosing Parquet with a high-cardinality partition column, which creates too many small partitions instead of using clustering within files.

Full explanation →

37

MCQhard

Refer to the exhibit. A Stream Analytics job shows increasing watermark delay and input deserialization errors. Which action should be taken first to troubleshoot?

A.Check the input data schema and ensure it matches the query

B.Change the output to a different sink

C.Increase the number of Streaming Units (SUs)

D.Set the watermark delay threshold higher

AnswerA

Deserialization errors indicate the incoming event format cannot be parsed against the declared input schema, which directly inflates watermark delay as unreadable events stall progress. Verifying that the input data schema matches the query's field definitions and serialisation format (JSON, Avro, CSV) addresses the root cause before scaling or partition tuning.

Why this answer

Input deserialization errors indicate that the incoming data format does not match the schema defined in the Stream Analytics query. Increasing watermark delay is a symptom of this mismatch, as the job cannot parse events correctly and falls behind. Checking and aligning the input schema with the query is the first and most direct troubleshooting step.

Exam trap

The trap here is that candidates often assume increasing resources (SUs) or adjusting thresholds will fix performance issues, when the real cause is a data format mismatch that prevents any processing from succeeding.

How to eliminate wrong answers

Option B is wrong because changing the output sink does not address the root cause of deserialization errors or watermark delay; the issue is with input parsing, not output destination. Option C is wrong because increasing Streaming Units (SUs) can improve throughput but will not fix schema mismatches that cause deserialization errors; it may even mask the underlying problem. Option D is wrong because raising the watermark delay threshold only hides the symptom by allowing more lateness, but does not resolve the input parsing failures that are generating the errors.

Full explanation →

38

MCQhard

Your organization uses Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. You notice that queries are slow when filtering on a date column. You need to improve query performance without increasing costs. What should you do?

A.Increase the maximum query concurrency limit

B.Provision a dedicated SQL pool with more DTUs

C.Create a clustered columnstore index on the date column

D.Partition the data by date in the data lake (e.g., folder structure: /year=*/month=*/day=*)

AnswerD

Partitioning Parquet files into year/month/day folders lets the serverless SQL pool prune irrelevant files, reading only matching partitions instead of scanning the whole dataset. This reduces data scanned per query, improving performance without raising cost, since serverless billing is per terabyte processed.

Why this answer

Partitioning the data by date in the data lake (e.g., /year=*/month=*/day=*) allows the serverless SQL pool to leverage partition elimination. When querying with a filter on the date column, the pool can read only the relevant partitions (folders) instead of scanning all Parquet files, drastically reducing I/O and improving query performance at no additional cost.

Exam trap

The trap here is that candidates often confuse serverless SQL pool with dedicated SQL pool and incorrectly choose to create indexes or scale resources, not realizing that serverless SQL pool relies on external data partitioning and file-skipping techniques rather than internal indexing or provisioning.

How to eliminate wrong answers

Option A is wrong because increasing the maximum query concurrency limit does not improve the performance of a single query; it only allows more concurrent queries to run, which could even degrade individual query performance due to resource contention. Option B is wrong because provisioning a dedicated SQL pool with more DTUs increases costs and is not a serverless SQL pool feature; serverless SQL pool scales automatically and does not use DTUs, so this would be an expensive and incorrect solution. Option C is wrong because clustered columnstore indexes are not supported in serverless SQL pool; they are a feature of dedicated SQL pools, and creating one on a date column in a serverless context is not possible.

Full explanation →

39

MCQmedium

You are developing an Azure Databricks notebook that processes streaming data from Azure Event Hubs using Structured Streaming. The stream writes to a Delta Lake table. You need to ensure that the stream can recover from failures and continue processing from where it left off without reprocessing all data. You also need to minimize the impact on the source. What should you configure?

A.Use the foreachBatch sink and manually store the offset in an Azure SQL Database.

B.Set the checkpoint location to a path in Azure Data Lake Storage Gen2 and use the Delta table as the sink.

C.Enable auto-compaction on the Delta table and set the trigger to continuous.

D.Set the startingOffsets option to earliest and enable watermarking.

AnswerB

Structured Streaming uses checkpointing to track progress and recover from failures. Setting a checkpoint location in durable storage like Azure Data Lake Storage Gen2 allows the stream to resume from the last committed offset after a restart. Writing to a Delta table provides exactly-once semantics when combined with checkpointing, ensuring no data loss or duplication. This configuration meets both recovery and minimal source impact requirements.

Why this answer

Checkpointing is the mechanism that enables Structured Streaming to recover from failures by storing progress information in durable storage. Combining a checkpoint location in Azure Data Lake Storage Gen2 with a Delta Lake sink ensures exactly-once processing and allows the stream to resume from the last offset. Other options either lack checkpointing or introduce unnecessary complexity and source load.

Exam trap

The trap here is thinking that Delta Lake alone provides fault tolerance, when in fact checkpointing must be configured separately to track streaming progress and enable recovery.

Full explanation →

40

MCQmedium

Your team is migrating an on-premises SQL Server data warehouse to Azure Synapse Analytics. The source has a fact table with 500 million rows and several dimension tables. You need to choose the best distribution strategy for the fact table to minimize data movement during joins. Which distribution type should you use?

A.Hash distribution on the foreign key column used in joins

B.No distribution (single distribution)

C.Replicated distribution

D.Round-robin distribution

AnswerA

Hash distribution on the join key colocates matching rows on the same compute node, so joins between the fact table and dimensions occur locally rather than shuffling 500 million rows across nodes. This directly satisfies the requirement to minimise data movement during joins.

Why this answer

Hash distribution on the foreign key column used in joins ensures that rows with the same join key are co-located on the same distribution node. This minimizes data movement because the join can be performed locally on each node without shuffling data across the compute nodes, which is critical for a 500-million-row fact table.

Exam trap

The trap here is that candidates often confuse replicated distribution as a general performance booster, but they fail to recognize that replicating a large fact table is impractical and that hash distribution on the join key is the correct strategy to minimize data movement for large fact tables.

How to eliminate wrong answers

Option B is wrong because single distribution (no distribution) places all data on one node, causing a bottleneck and eliminating the parallelism benefits of Azure Synapse Analytics, leading to poor performance for large fact tables. Option C is wrong because replicated distribution copies the entire table to each node, which is impractical for a 500-million-row fact table due to excessive storage and maintenance overhead; it is suitable only for smaller dimension tables. Option D is wrong because round-robin distribution distributes rows evenly without considering join keys, so joins require data to be shuffled across nodes, causing significant data movement and slower query performance.

Full explanation →

41

MCQhard

Refer to the exhibit. You are deploying an Azure Synapse Analytics dedicated SQL pool using the provided ARM template snippet. After deployment, you need to adjust the performance level to DW200c to handle increased workload. Which parameter should you modify?

A.storageAccountType

B.maxSizeBytes

C.collation

D.sku.name

AnswerD

The performance level of a dedicated SQL pool is defined by the sku.name property, which carries values such as DW200c. Modifying sku.name in the ARM template sets the pool to the required DW200c performance level.

Why this answer

In an Azure Synapse Analytics dedicated SQL pool ARM template, the performance level (e.g., DW100c, DW200c, DW300c) is specified in the sku.name property of the Microsoft.Synapse/workspaces/sqlPools resource. To change the performance level to DW200c, you modify sku.name. The other parameters control storage redundancy, maximum size, and collation, none of which affect the compute performance level.

Exam trap

The trap is confusing the performance level (sku.name) with capacity limits (maxSizeBytes) — candidates who see 'performance' and 'size' in the same resource often pick maxSizeBytes, but performance tier is always the SKU.

How to eliminate wrong answers

Option A is wrong because storageAccountType controls the storage redundancy/type (e.g., LRS, GRS) for the SQL pool's backing storage, not the compute performance level. Option B is wrong because maxSizeBytes sets the maximum data size for the pool (in bytes), which is a capacity limit, not a performance tier. Option C is wrong because collation defines the database collation (sorting and comparison rules) and has no bearing on the DWU/performance level.

Full explanation →

42

Multi-Selecthard

Which THREE of the following are best practices for designing tables in a dedicated SQL pool in Azure Synapse Analytics?

Select 3 answers

A.Avoid using clustered columnstore indexes on large tables.

B.Avoid data skew by choosing a good distribution key.

C.Use round-robin distribution for all large fact tables.

D.Use replicated tables for small dimension tables (less than 1 GB).

E.Use hash distribution on a column with high cardinality for large fact tables.

AnswersB, D, E

A well-chosen hash distribution key spreads rows evenly across the sixty distributions, preventing one distribution from bearing disproportionate rows. Skew forces serialised processing, so even distribution directly supports the parallel-query design of dedicated SQL pools.

Why this answer

Option B is correct because choosing a good distribution key prevents data skew, which otherwise causes some distributions to hold disproportionate rows and forces the query engine to move data across nodes, degrading performance in a dedicated SQL pool. Option D is correct because replicated tables copy the full table to every compute node, eliminating data movement for joins; this is recommended for small dimension tables under roughly 1 GB (or up to 2 GB compressed). Option E is correct because hash distribution on a high-cardinality column spreads rows evenly across the 60 distributions, which is ideal for large fact tables and minimizes skew.

Option A is wrong because clustered columnstore indexes are the default and preferred storage for large tables in dedicated SQL pools, delivering high compression and fast analytical scans. Option C is wrong because round-robin distribution is a fallback for staging or tables with no clear join key, not a blanket best practice for all large fact tables, since it can cause costly data movement during joins.

Exam trap

The trap here is that candidates often assume clustered columnstore indexes are unsuitable for large tables due to memory constraints, but they are actually the default and recommended index type for fact tables in Synapse dedicated SQL pools.

Full explanation →

43

Multi-Selecteasy

Which THREE best practices should be followed when designing a data lake in Azure Data Lake Storage Gen2 for optimal performance?

Select 3 answers

A.Disable hierarchical namespace to improve performance.

B.Use a deep directory structure with many subfolders.

C.Use Parquet file format for analytics workloads.

D.Use a naming convention that avoids special characters and high cardinality.

E.Partition data by date to enable partition elimination.

AnswersC, D, E

Parquet's columnar, compressed layout lets analytical engines read only the referenced columns and skip irrelevant row groups, sharply cutting I/O against the stem's optimal-performance requirement. Row-based formats force full-row deserialisation regardless of projection, so Parquet directly satisfies the constraint that analytics workloads over Data Lake Storage Gen2 must minimise scanned data.

Why this answer

Option C is correct because columnar formats like Parquet compress data efficiently and let analytics engines such as Spark, Synapse, and Databricks read only the needed columns, dramatically reducing I/O and improving query performance. Option D is correct because ADLS Gen2 (and the underlying Blob REST/ABFS APIs) handles simple, predictable names more efficiently; special characters can break path parsing and high-cardinality names (e.g., GUIDs or timestamps in every filename) defeat caching, listing, and partition pruning. Option E is correct because partitioning data by date (e.g., year=/month=/day=) aligns with how engines like Spark, Hive, and Synapse perform partition elimination, so queries scanning a single day skip all other partitions instead of reading the whole dataset.

Option A is wrong because enabling the hierarchical namespace is what makes ADLS Gen2 a true data lake with directory-level operations, atomic renames, and better performance for analytics; disabling it reverts to flat Blob storage semantics. Option B is wrong because deep, heavily nested directory trees increase metadata and listing overhead and slow down operations; a shallow, well-partitioned structure is preferred.

Exam trap

The trap is that candidates assume disabling the hierarchical namespace improves performance (it actually removes key optimizations) and that deeper folder hierarchies are better, when flat, date-partitioned layouts are the recommended design.

Full explanation →

44

Multi-Selectmedium

A data engineering team is designing a batch processing solution using Azure Databricks. The data is stored in Azure Data Lake Storage Gen2 (ADLS Gen2) and must be processed daily with minimal cost. The team needs to choose between using a Delta Lake table or a Parquet file format for the processed output. Which TWO factors should the team consider when making this decision?

Select 2 answers

A.Delta Lake provides time travel capabilities for accessing historical data versions.

B.Parquet is easier to implement for schema evolution than Delta Lake.

C.Delta Lake reduces storage costs by automatically compressing data.

D.Delta Lake supports ACID transactions, ensuring data consistency during concurrent writes.

E.Parquet files are not natively supported by Azure Databricks.

AnswersA, D

Delta Lake's transaction log stores metadata for every write, enabling time travel to query or restore earlier table versions without duplicating the underlying Parquet files. This satisfies the daily batch scenario's need for historical data access and rollback, while retaining Parquet's columnar storage benefits for cost-effective processing.

Why this answer

Option A is correct because Delta Lake stores a transaction log (_delta_log) that records every commit, enabling time travel queries such as SELECT * FROM table VERSION AS OF n or TIMESTAMP AS OF, which is valuable for auditing, reproducing past results, and recovering from bad writes in a daily batch pipeline. Option D is correct because Delta Lake provides ACID transactions on top of ADLS Gen2, so concurrent writers and readers see consistent snapshots and partial or failed writes are not exposed, which matters when multiple jobs or retries touch the same output. Option B is wrong because schema evolution is actually a Delta Lake strength (mergeSchema and automatic schema evolution in MERGE/append), while Parquet requires manual rewrites or workarounds.

Option C is wrong because Delta Lake does not automatically compress data to reduce storage cost; it relies on Parquet compression plus OPTIMIZE/Z-ORDER and VACUUM for layout and cleanup, and it adds transaction-log overhead. Option E is wrong because Azure Databricks natively reads and writes Parquet files, including via Spark and external tables.

Exam trap

The trap here is that candidates assume Parquet is not natively supported in Databricks, but in reality, Parquet is the default storage format for Delta Lake and is fully supported; the key differentiators are ACID transactions and time travel, not format compatibility.

Full explanation →

45

MCQeasy

You are configuring security for an Azure Data Lake Storage Gen2 account. You need to ensure that users can only access files and folders for which they have explicit permissions, and that permissions are enforced at the file and folder level. What should you enable?

A.Access control lists (ACLs) only

B.Shared access signatures (SAS) only

C.Azure RBAC and access control lists (ACLs)

D.Azure role-based access control (Azure RBAC) only

AnswerC

To enforce file and folder level permissions in Azure Data Lake Storage Gen2, you must use both Azure RBAC and ACLs. RBAC grants the user or service principal access to the storage account or container, while ACLs provide fine-grained permissions on individual files and folders. This combination ensures that users can only access the data for which they have explicit permissions, meeting the requirement.

Why this answer

Azure Data Lake Storage Gen2 uses a combination of Azure RBAC and POSIX-like ACLs to secure data. RBAC controls access at the management and container level, while ACLs provide fine-grained permissions at the file and folder level. To ensure users can only access files and folders for which they have explicit permissions, both must be configured.

RBAC alone is too coarse, ACLs alone lack the authentication context, and SAS tokens are not identity-based for per-user permissions.

Exam trap

The trap here is thinking that ACLs alone or RBAC alone can provide complete file-level security, when in fact they are complementary.

Full explanation →

46

MCQeasy

You are running an Azure Stream Analytics job that reads from an Event Hub and writes to a Power BI dataset. The job is falling behind and processing latency is increasing. What should you do to improve performance?

A.Increase the number of Streaming Units (SUs) allocated to the job.

B.Use a reference data input to filter events.

C.Change the output to Azure Blob Storage instead of Power BI.

D.Decrease the size of events sent to the Event Hub.

AnswerA

Streaming Units govern the compute and parallelism available to the job; when throughput exceeds current capacity, partitions queue and latency climbs. Scaling SUs raises processing bandwidth, directly addressing the backlog constraint without altering the query or Event Hub configuration.

Why this answer

Azure Stream Analytics scales throughput by allocating Streaming Units (SUs), which represent a bundle of CPU, memory, and network resources. When a job falls behind, increasing SUs provides more parallel processing capacity, directly reducing latency. This is the standard vertical scaling mechanism for Stream Analytics and is the correct first response to performance degradation.

Exam trap

DP-203 often tests the misconception that changing the output or input configuration will improve performance, when the primary scaling lever for Stream Analytics is increasing Streaming Units (SUs).

How to eliminate wrong answers

Option B is wrong because reference data is used for enriching or filtering events based on static or slowly changing data, not for improving throughput; it adds a join operation that can actually increase latency. Option C is wrong because changing the output to Blob Storage does not address the root cause of the job falling behind; it merely changes the sink and may even reduce performance if the job is output-bound, but the question asks about improving performance, not changing output. Option D is wrong because decreasing event size might reduce network and processing overhead per event, but it does not increase the job's processing capacity and is not a direct scaling action; moreover, it may not be feasible if the event schema is fixed.

Full explanation →

47

MCQmedium

A data engineer needs to store JSON documents that are frequently updated by multiple users concurrently. The solution must support optimistic concurrency control and have built-in indexing on all fields. Which Azure data store should be used?

A.Azure Cosmos DB (SQL API)

B.Azure Blob Storage

C.Azure SQL Database

D.Azure Table Storage

AnswerA

Azure Cosmos DB (SQL API) natively supports optimistic concurrency control through ETag-based conditional writes, satisfying the concurrent multi-user update requirement. Its automatic indexing policy indexes every property by default, meeting the built-in indexing constraint without manual configuration. JSON documents are stored natively, so no schema translation is needed.

Why this answer

Azure Cosmos DB (SQL API) is the correct choice because it natively supports optimistic concurrency control via ETags (HTTP entity tags) and provides automatic indexing of all fields without requiring manual index management. This makes it ideal for storing JSON documents that are frequently updated by multiple concurrent users, as it ensures conflict detection and resolution while maintaining high performance.

Exam trap

The trap here is that candidates often choose Azure SQL Database because they associate concurrency control with relational databases, overlooking that Cosmos DB is purpose-built for JSON documents with automatic indexing and native optimistic concurrency via ETags, which is more aligned with the requirements than a relational store.

How to eliminate wrong answers

Option B (Azure Blob Storage) is wrong because it does not support optimistic concurrency control; it uses lease-based locking for blobs, which is not designed for fine-grained concurrent updates on JSON documents and lacks built-in indexing on all fields. Option C (Azure SQL Database) is wrong because while it supports optimistic concurrency via snapshot isolation or row versioning, it requires manual index creation and is not optimized for storing and querying JSON documents natively; it is a relational store, not a document store. Option D (Azure Table Storage) is wrong because it does not support optimistic concurrency control (it uses ETags but only for individual entities, not for complex JSON documents) and its indexing is limited to partition and row keys, not all fields.

Full explanation →

48

MCQeasy

You need to ensure that data in an Azure Data Lake Storage Gen2 account is encrypted at rest using a customer-managed key. Which feature should you configure?

A.Azure Key Vault integration with Storage Service Encryption

B.Azure Information Protection

C.Azure Storage Service Encryption with Microsoft-managed keys

D.Azure Disk Encryption

AnswerA

Configuring Azure Key Vault integration lets Storage Service Encryption use a customer-managed key stored in Key Vault rather than a Microsoft-managed key, giving the organisation control over the key lifecycle. This satisfies the requirement for encryption at rest with a customer-managed key.

Why this answer

Customer-managed keys (CMK) for Azure Storage are implemented by integrating the storage account with Azure Key Vault (or Managed HSM) and configuring the account's encryption key to reference a key stored there. Storage Service Encryption (SSE) then uses that key to wrap the account's data encryption key, giving the customer control over key lifecycle and revocation.

Exam trap

The trap is confusing data-classification services (Azure Information Protection) or disk-level encryption (ADE) with storage-account encryption; only Key Vault integration with SSE provides customer-managed keys for ADLS Gen2.

How to eliminate wrong answers

Option B is wrong because Azure Information Protection is a data-classification and labeling service for documents and emails, not a storage-at-rest encryption mechanism for Data Lake Storage Gen2. Option C is wrong because Microsoft-managed keys are the default and do not satisfy the requirement for customer-managed keys — the customer has no control over rotation or revocation. Option D is wrong because Azure Disk Encryption applies BitLocker/DM-Crypt to IaaS VM OS and data disks, not to PaaS storage accounts such as ADLS Gen2.

Full explanation →

49

MCQeasy

Your organization uses Azure Data Lake Storage Gen2 and needs to prevent accidental deletion of data by enabling soft delete. You also need to ensure that deleted blobs are recoverable for 30 days. What should you configure?

A.Enable blob snapshots and set them to expire after 30 days.

B.Use Azure Backup to create daily backups of the storage account.

C.Enable container soft delete with a retention period of 30 days.

D.Enable blob soft delete and set retention period to 30 days.

AnswerD

Blob soft delete retains deleted blobs for a configurable retention period, satisfying the 30-day recoverability constraint. Setting the retention period to 30 days ensures accidental deletions remain restorable within that window, directly meeting the stated requirement.

Why this answer

Blob soft delete enables recovery of deleted blobs within a specified retention period (30 days in this case). Option A is incorrect because blob snapshots are point-in-time copies that require manual management and do not provide automatic recovery of deleted blobs. Option B is incorrect because Azure Backup is designed for virtual machines and other Azure resources, not for blob-level recovery in Data Lake Storage Gen2.

Option C is incorrect because container soft delete deletes entire containers, not individual blobs.

Exam trap

A common trap is confusing blob soft delete with container soft delete. Container soft delete protects entire containers, while blob soft delete protects individual blobs. For this question, blob soft delete is required.

Full explanation →

50

MCQhard

You are monitoring an Azure Stream Analytics job that processes data from an IoT hub. The job's output to Azure Synapse Analytics is experiencing high latency. The job's SU% utilization is at 90%. Which action will most likely reduce the latency?

A.Increase the number of Streaming Units (SUs) allocated to the job.

B.Decrease the watermark delay interval.

C.Increase the late arrival tolerance window.

D.Increase the number of partitions in the output table.

AnswerA

SU% utilisation at 90% means the job is near its allocated streaming capacity, so processing falls behind the IoT Hub input rate and output latency grows. Adding Streaming Units provides more compute parallelism, allowing the backlog to drain faster.

Why this answer

The job's SU% utilization is at 90%, indicating that the current Streaming Units (SUs) are nearly saturated, causing a processing bottleneck. Increasing the number of SUs allocates more compute resources (CPU and memory) to the Stream Analytics job, allowing it to process incoming IoT data faster and reduce the latency to Azure Synapse Analytics. This directly addresses the high utilization issue, which is the most likely root cause of the latency.

Exam trap

The trap here is that candidates often confuse output-side tuning (like partitioning or sink configuration) with the actual processing bottleneck, overlooking that high SU% utilization directly indicates the Stream Analytics job itself is the limiting factor.

How to eliminate wrong answers

Option B is wrong because decreasing the watermark delay interval would make the job emit results more frequently, but it does not increase processing capacity; with SU utilization already at 90%, this could worsen backpressure and increase latency. Option C is wrong because increasing the late arrival tolerance window only allows the job to handle out-of-order events for a longer period; it does not improve throughput or reduce latency caused by resource saturation. Option D is wrong because increasing the number of partitions in the output table improves write parallelism on the Synapse side, but the bottleneck is the Stream Analytics job's processing capacity (90% SU utilization), not the output sink's partitioning.

Full explanation →

51

Drag & Dropmedium

Drag and drop the steps to convert data from CSV to Parquet format using Azure Data Factory into the correct order.

Drag or tap steps into the slots.

Steps

Order

1Step 1

2Step 2

3Step 3

4Step 4

Why this order

Define source (CSV) and sink (Parquet) datasets, then a copy activity with mapping, run, and monitor.

Full explanation →

52

MCQmedium

You are building a data pipeline that uses Azure Data Factory to copy data from a REST API to Azure Blob Storage. The REST API returns JSON data in pages of 1000 records each. The total number of records is 50,000. Which activity or feature should you use to loop through the pages?

A.Use a ForEach activity to iterate over a fixed number of pages.

B.Use a Lookup activity to retrieve the total number of pages and then use a ForEach.

C.Use an Until activity to loop until the API returns no more pages.

D.Use a Copy activity with pagination rules enabled in the source.

AnswerD

Copy activity pagination rules let the source iterate REST API pages automatically using properties such as NextPageUrl or absolute URLs, retrieving all 50,000 records without a ForEach loop. This satisfies the stem's requirement to traverse 1,000-record pages efficiently.

Why this answer

Azure Data Factory's Copy activity supports pagination rules that automatically handle paging through REST API responses. By configuring pagination rules in the source dataset, the Copy activity can iterate through pages until all data is copied, without needing explicit looping activities. This is the most efficient and recommended approach.

Exam trap

DP-203 often tests the preference for built-in pagination in Copy activity over manual looping, where candidates may overcomplicate with ForEach or Until activities.

How to eliminate wrong answers

Option A is wrong because a ForEach activity with a fixed number of pages is inflexible and requires knowing the exact page count in advance. Option B is wrong because using a Lookup to get total pages then ForEach is more complex and unnecessary when pagination rules exist. Option C is wrong because an Until activity would require custom logic to detect the end of pages, which is more complex than built-in pagination.

Option D is correct because Copy activity with pagination rules is designed for this scenario.

Full explanation →

53

MCQeasy

You are designing a data storage solution for a marketing analytics platform. The platform collects clickstream data from websites and needs to store it for both real-time dashboards and historical analysis. The data is semi-structured (JSON) and arrives at a rate of 10,000 events per second. You need to choose an Azure storage solution that can handle the ingestion rate, support schema-on-read, and integrate with Azure Databricks for advanced analytics. The solution must also be cost-effective for long-term storage. What should you use?

A.Azure Table Storage

B.Azure Data Lake Storage Gen2

C.Azure Cosmos DB

D.Azure SQL Database

AnswerB

Data Lake Storage Gen2 ingests high-volume JSON streams, applies schema-on-read, integrates natively with Azure Databricks, and its hierarchical namespace plus lifecycle tiering keep long-term storage cost-effective, meeting the 10,000 events-per-second rate and historical analysis needs.

Why this answer

Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical namespace with Azure Blob Storage, providing high-throughput ingestion (up to 60 GB/s per account) to handle 10,000 events per second of semi-structured JSON data. It supports schema-on-read natively, allowing Azure Databricks to query the data directly using Spark without prior schema definition, and its tiered storage (hot, cool, archive) makes it cost-effective for long-term historical analysis.

Exam trap

The trap here is that candidates often choose Azure Cosmos DB for its real-time capabilities, overlooking that the question emphasizes cost-effective long-term storage and schema-on-read for historical analysis, which ADLS Gen2 handles far more efficiently and cheaply than Cosmos DB's per-request-unit pricing model.

How to eliminate wrong answers

Option A is wrong because Azure Table Storage is a NoSQL key-value store designed for structured data with a fixed schema, not for semi-structured JSON clickstream data, and it lacks the hierarchical namespace and high-throughput ingestion needed for 10,000 events per second. Option C is wrong because Azure Cosmos DB is optimized for low-latency real-time access with its multi-model API, but it is significantly more expensive for long-term storage of high-volume historical data and does not natively integrate with Azure Databricks for schema-on-read analytics as efficiently as ADLS Gen2. Option D is wrong because Azure SQL Database is a relational database requiring a predefined schema (schema-on-write), which conflicts with the schema-on-read requirement, and its ingestion rate and cost model are not designed for high-velocity semi-structured data at 10,000 events per second.

Full explanation →

54

MCQmedium

Refer to the exhibit. You are deploying an Azure Synapse Analytics workspace using an ARM template. The template defines a managed virtual network integration runtime. You need to ensure that the integration runtime can run mapping data flows with a time-to-live (TTL) of 10 minutes. What is the purpose of the 'timeToLive' property in this configuration?

A.It defines how long the cluster will be kept alive after a data flow completes, allowing subsequent data flows to reuse the cluster.

B.It sets the timeout for the integration runtime to connect to the data sources.

C.It specifies the maximum duration a data flow activity can run before timing out.

D.It determines the maximum number of concurrent data flows that can run on the cluster.

AnswerA

The timeToLive property sets the idle period before the data flow cluster shuts down. With 10 minutes configured, the cluster persists after a flow completes, letting subsequent mapping data flows reuse the warm cluster and avoid cold-start provisioning delays.

Why this answer

The 'timeToLive' property in an Azure Synapse Analytics managed virtual network integration runtime controls how long the cluster remains alive after a mapping data flow completes. By setting a TTL of 10 minutes, subsequent data flows can reuse the same warm cluster, avoiding the 5–10 minute cold start time for new clusters. This optimizes performance and reduces latency for consecutive data flow executions.

Exam trap

The trap here is that candidates confuse 'timeToLive' with activity timeout or concurrency limits, because all three involve time or capacity constraints, but TTL specifically governs cluster reuse after a data flow completes, not execution duration or parallelism.

How to eliminate wrong answers

Option B is wrong because the connection timeout to data sources is configured separately via linked service properties or the 'connectVia' runtime settings, not through the 'timeToLive' property. Option C is wrong because the maximum duration a data flow activity can run is set by the activity's 'timeout' property in the pipeline, not by the integration runtime's TTL. Option D is wrong because the maximum number of concurrent data flows is controlled by the 'concurrency' property on the integration runtime, not by 'timeToLive'.

Full explanation →

55

Multi-Selecthard

Which THREE security features are available in Azure Data Lake Storage Gen2 to protect data at rest and in transit? (Choose three.)

Select 3 answers

A.Azure Storage firewalls and virtual network rules

B.Azure Information Protection

C.Encryption at rest using Storage Service Encryption (SSE)

D.Azure ADLS Gen2 supports HTTPS for data in transit.

E.Azure Policy

AnswersA, C, D

Storage firewalls and virtual network rules restrict network access to the storage account, blocking public internet traffic and permitting only approved subnets or IP ranges. This protects data in transit by enforcing network-level perimeter controls on Azure Data Lake Storage Gen2.

Why this answer

Option A (Azure Storage firewalls and virtual network rules) is correct because ADLS Gen2, built on Azure Storage, lets you restrict network access to the storage account by configuring firewall rules and allowing specific virtual networks and subnets, thereby protecting data at rest from unauthorized network access. Option C (Encryption at rest using Storage Service Encryption, SSE) is correct because Azure Storage automatically encrypts data at rest using 256-bit AES encryption, and this applies to ADLS Gen2 accounts, protecting data at rest. Option D (HTTPS for data in transit) is correct because ADLS Gen2 supports secure transfer over HTTPS/TLS, ensuring data is encrypted in transit between clients and the storage service.

Option B (Azure Information Protection) is not a native ADLS Gen2 data-at-rest or in-transit protection feature; it is a separate classification and labeling service for documents and emails. Option E (Azure Policy) is a governance and compliance tool for enforcing organizational rules, not a direct data-at-rest or in-transit encryption/network protection feature of ADLS Gen2.

Exam trap

DP-203 often tests the confusion between governance services (Azure Policy, Azure Information Protection) and native storage security controls, tricking candidates into selecting services that 'sound' security-related but do not directly protect ADLS Gen2 data.

Full explanation →

56

MCQhard

You are developing an Azure Synapse Analytics serverless SQL pool solution that queries Parquet files in Azure Data Lake Storage Gen2. Analysts run ad-hoc queries with predicates on a high-cardinality column named TransactionId, and each query scans the entire folder, causing high cost. You need to reduce the amount of data scanned per query without changing the file format. What should you do?

A.Create an external table over the folder and enable statistics on the TransactionId column.

B.Create a partitioned table in a dedicated SQL pool and load the Parquet data into it.

C.Use OPENROWSET with a filepath() predicate to restrict the query to specific files.

D.Create a partitioned external table where the folder structure is organized by a low-cardinality key and query only the relevant partitions.

AnswerD

Partitioning the external table on a low-cardinality column such as date or region lets the serverless engine prune entire folders that cannot match the predicate. Queries that filter on that partition column then scan only the relevant directories, sharply reducing bytes read. This preserves the Parquet format and works with the existing file layout once the folder hierarchy reflects the partition key.

Why this answer

Serverless SQL pool reduces cost by reading fewer bytes, and partition elimination is the primary lever for that when the underlying layout supports it. Organizing folders by a low-cardinality key and exposing it through a partitioned external table allows the engine to skip irrelevant directories. Predicates on a high-cardinality column cannot prune files because their values are scattered across every file.

Exam trap

The trap here is expecting statistics or filepath filtering to skip data for a high-cardinality predicate, when only partition elimination on a low-cardinality key can prune files.

Full explanation →

57

Multi-Selectmedium

A company uses Azure Synapse Analytics dedicated SQL pool for a data warehouse. They notice that some queries are using more memory than expected, causing resource contention. Which TWO actions should they take to diagnose and optimize memory usage?

Select 2 answers

A.Enable result-set caching.

B.Increase the resource class for the users running the heavy queries.

C.Scale up the DWU setting.

D.Query the sys.dm_pdw_exec_requests DMV to identify queries with high memory grants.

E.Rebuild clustered columnstore indexes.

AnswersB, D

Increasing the resource class allocates more memory per query, directly relieving contention caused by heavy queries exceeding their default allocation. This satisfies the memory-pressure constraint by giving those queries greater dedicated memory within the dedicated SQL pool.

Why this answer

Option B is correct because in a dedicated SQL pool, the resource class assigned to a user directly controls the memory grant (and concurrency slots) available to their queries; raising the resource class for users running memory-heavy queries gives those queries a larger memory allocation, reducing memory pressure and contention. Option D is correct because sys.dm_pdw_exec_requests is the DMV that exposes per-request execution details, including resource allocation and memory grant information, so querying it lets administrators identify exactly which queries are consuming excessive memory before tuning them. Option A is not appropriate because result-set caching is a feature of serverless SQL pools and does not address memory grants in a dedicated SQL pool.

Option C is not the right first step because scaling DWU changes overall compute capacity and cost rather than diagnosing or right-sizing per-query memory grants. Option E is not relevant because rebuilding clustered columnstore indexes addresses data compression and segment quality, not query memory grant consumption.

Exam trap

The trap here is that candidates often confuse scaling up the DWU (Option C) as a diagnostic action, but it is a reactive scaling measure that does not help identify which queries are causing the memory issue, whereas querying the DMV and adjusting resource classes are targeted diagnostic and optimization steps.

Full explanation →

58

Multi-Selectmedium

Which TWO actions can you take to optimize the performance of a dedicated SQL pool in Azure Synapse Analytics when loading large volumes of data?

Select 2 answers

A.Create nonclustered indexes on all columns of the target table

B.Use ROUND_ROBIN distribution for the staging table

C.Set the row group size to 100,000 rows for optimal compression

D.Enable change tracking on the target table

E.Use CREATE TABLE AS SELECT (CTAS) with partition switching

AnswersB, E

ROUND_ROBIN distributes staging rows evenly across all distributions without requiring a distribution key, avoiding skew and data-movement overhead during the load. This maximises parallel ingestion throughput into the staging table before the final CTAS into the production table.

Why this answer

Option B is correct because a ROUND_ROBIN distributed staging table spreads incoming rows evenly across all distributions without requiring a distribution key, which maximizes parallel ingestion throughput and avoids data movement during the load before the data is redistributed into the final table. Option E is correct because CTAS performs a parallel, minimally logged bulk operation that creates a new table with the desired distribution and indexing in one step, and combining it with partition switching lets you swap the fully loaded table into the target quickly and efficiently. Option A is not appropriate because nonclustered indexes on every column slow down bulk loads and consume extra storage; dedicated SQL pools rely primarily on clustered columnstore indexes.

Option C is wrong because the optimal row group size for columnstore compression is around 1,048,576 rows (1 million), not 100,000, which yields smaller, less efficient row groups. Option D is incorrect because change tracking is used for incremental data synchronization scenarios and does not improve bulk load performance.

Exam trap

The trap here is that candidates often confuse the purpose of indexes and distribution types, mistakenly thinking that adding indexes on all columns will speed up loading, when in fact it degrades performance, and they overlook that ROUND_ROBIN is specifically designed for fast staging loads, not for query performance.

Full explanation →

59

MCQeasy

Refer to the exhibit. You have a mapping data flow in Azure Data Factory that aggregates sales data. The data flow runs successfully but the sink table contains only the total sum per run instead of per product. What is missing?

A.The source dataset is not filtering by date

B.The aggregate transformation does not have a groupBy column

C.The data flow is missing a filter transformation

D.The sink dataset is not configured to append

AnswerB

An aggregate transformation without a groupBy column collapses all rows into a single group, producing one total sum per run. Adding ProductID as the groupBy column makes the aggregation produce one row per product, matching the expected sink output.

Why this answer

In an ADF mapping data flow, the Aggregate transformation computes aggregates based on the columns listed in the Group By tab. If no groupBy column is specified, the transformation treats the entire dataset as a single group, producing one row with the total sum. Adding the product column to groupBy makes the aggregation produce one row per product.

Exam trap

The trap here is confusing row-level filtering or sink configuration with aggregation granularity — candidates overlook that an empty Group By in the Aggregate transformation collapses all rows into a single global total.

How to eliminate wrong answers

Option A is wrong because date filtering affects which rows are included, not the granularity of the aggregation — the result would still be a single total. Option C is wrong because a filter transformation only removes rows; it does not change the grouping behavior of the aggregate. Option D is wrong because append vs. overwrite on the sink affects how results are written, not whether the aggregation is per-product or global.

Full explanation →

60

MCQmedium

A company uses Azure Synapse Analytics dedicated SQL pool. They notice that some queries are slow due to high data movement. What should you do to minimize data movement for queries that join large fact tables?

A.Use round-robin distribution for all tables.

B.Partition both tables on the join keys.

C.Hash-distribute the fact tables on the join keys.

D.Use replicated tables for all large fact tables.

AnswerC

Hash-distributing both fact tables on their join keys colocates matching rows on the same distribution, so the join executes locally instead of shuffling large datasets between nodes. This directly reduces the high data movement causing slow queries in the dedicated SQL pool.

Why this answer

Hash-distributing the fact tables on the join keys ensures that rows with the same join key value are placed on the same distribution node. This eliminates the need to shuffle data across nodes during the join, minimizing data movement and improving query performance in Azure Synapse dedicated SQL pool.

Exam trap

The trap here is that candidates confuse partitioning with distribution, thinking that partitioning on join keys reduces data movement, when in fact only hash distribution on the join key ensures collocation across nodes.

How to eliminate wrong answers

Option A is wrong because round-robin distribution distributes rows evenly without considering join keys, which does not reduce data movement for joins and can actually increase it. Option B is wrong because partitioning on join keys organizes data within a distribution but does not control data placement across distributions; data movement still occurs when joining across partitions. Option D is wrong because replicated tables are suitable for small dimension tables, not large fact tables, as replicating large tables would consume excessive storage and negate the benefits of scale-out.

Full explanation →

61

Multi-Selecthard

Which THREE metrics should you monitor for an Azure Synapse Analytics dedicated SQL pool to ensure optimal performance?

Select 3 answers

A.tempdb usage

B.DWU usage

C.Queued queries

D.Login failures

E.Total storage size

AnswersA, B, C

tempdb usage in a dedicated SQL pool indicates spill-to-disk from insufficient memory grants during large sorts, joins and aggregations. Monitoring it reveals queries exceeding granted memory, which degrade performance and signal the need for statistics updates or query tuning.

Why this answer

Monitoring tempdb usage (A) is essential because dedicated SQL pool queries spill to tempdb for sorts, hash joins, and other operations, and high tempdb utilization can indicate insufficient memory or poorly designed queries that degrade performance. DWU usage (B) is a key metric because it reflects how much of the provisioned Data Warehouse Unit capacity is being consumed; consistently high DWU usage signals that the pool may need scaling or workload tuning. Queued queries (C) should be monitored because a growing queue indicates that concurrency limits or resource contention are delaying query execution, directly affecting performance.

The unmarked options do not belong: login failures (D) is a security/connectivity metric rather than a performance indicator, and total storage size (E) relates to capacity planning and cost, not to query performance optimization.

Exam trap

The trap is confusing capacity metrics (like storage size) with performance metrics. Candidates might think login failures are a performance issue, but they are security-related. Also, DWU usage is sometimes overlooked as it directly measures compute utilization.

Full explanation →

62

MCQmedium

You are designing a storage solution for a healthcare analytics platform. The platform ingests large volumes of structured patient records stored as Parquet files in Azure Data Lake Storage Gen2. Analysts query this data using Azure Synapse Analytics serverless SQL pools. To minimize query cost and improve performance, you need to choose an appropriate file organization and table type. What should you do?

A.Store data as CSV files partitioned by date, and query them using an external table in a serverless SQL pool.

B.Store data as JSON files and query them using OPENROWSET in a serverless SQL pool.

C.Store data as Parquet files in a dedicated SQL pool table with hash distribution on patient ID.

D.Store data as Parquet files partitioned by date, and query them using an external table in a serverless SQL pool.

AnswerD

Parquet is a columnar format that reduces I/O and cost in serverless SQL pools. Partitioning by date enables partition elimination, further reducing data scanned. External tables allow querying data in place without loading, which is ideal for ad-hoc analytics and cost control. This combination directly addresses the requirement to minimize cost and improve performance.

Why this answer

Storing data in Parquet format with date-based partitioning and querying via external tables in a serverless SQL pool minimizes data scanned and cost, while improving query performance. Parquet's columnar nature and compression reduce I/O, and partitioning enables partition elimination. This aligns with best practices for cost-effective analytics on large datasets in Azure Synapse Analytics.

Exam trap

The trap here is assuming that any file format works equally well with serverless SQL pools, ignoring the cost and performance benefits of columnar storage and partitioning.

Full explanation →

63

Multi-Selectmedium

You are designing a data storage solution for a media company that ingests video files from various sources into Azure Data Lake Storage Gen2. The files are uploaded continuously and must be processed by Azure Databricks. You need to ensure that the data is organized efficiently for query performance and that access is secure. Which two actions should you include in your design? (Choose two.)

Select 2 answers

A.Configure Azure Private Endpoints for the storage account to restrict access to the virtual network.

B.Use Azure Blob Storage with a flat namespace to simplify file management.

C.Store all video files in a single container without subfolders to reduce complexity.

D.Enable Azure Defender for Storage to protect against malicious file uploads.

E.Partition the data by upload date in a hierarchical folder structure (e.g., /year/month/day).

AnswersA, E

Private Endpoints ensure that access to the storage account is restricted to the virtual network, enhancing security by preventing public internet access. This meets the requirement for secure access and is a recommended practice when processing sensitive or large-scale data with services like Azure Databricks.

Why this answer

Partitioning data by upload date in a hierarchical folder structure enables efficient querying through partition pruning, which is essential for large-scale video processing in Databricks. Configuring Azure Private Endpoints ensures that access to the storage account is secure and restricted to the virtual network, meeting the security requirement. Together, these actions optimize performance and security.

Exam trap

The trap here is focusing on security features like Azure Defender that do not address data organization for query performance, while overlooking the need for hierarchical partitioning and private network access.

Full explanation →

64

MCQhard

Your organization uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled. You need to implement a security strategy that allows users to read only specific folders within a container. Which authorization method should you use?

A.Storage account shared key

B.Azure RBAC roles (e.g., Storage Blob Data Contributor) at the container level

C.Shared access signatures (SAS) with folder-level permissions

D.Access control lists (ACLs) on the folder

AnswerD

ACLs provide POSIX-style permissions at file and directory level, so individual folders within a container can grant read access independently. This satisfies the requirement for folder-scoped authorisation, which container-level RBAC roles cannot achieve because they apply to the whole container.

Why this answer

ACLs (Access Control Lists) in Azure Data Lake Storage Gen2 can be applied to individual folders, enabling granular read permissions. Option A is incorrect because a storage account shared key grants full access to the entire account. Option B is incorrect because Azure RBAC roles like Storage Blob Data Contributor apply at the container level, affecting all folders within.

Option C is incorrect because shared access signatures (SAS) can be scoped to a container or a file, but not to a specific folder within a container.

Full explanation →

65

MCQmedium

You have an Azure Synapse Analytics dedicated SQL pool that contains a large fact table named FactSales. The table is partitioned by date and has a clustered columnstore index. You notice that queries filtering on a specific date range are slow. You need to improve query performance for these queries. What should you do?

A.Rebuild the clustered columnstore index on the FactSales table.

B.Create a nonclustered index on the date column.

C.Ensure that the queries use a predicate on the partitioning column so that partition elimination occurs.

D.Switch the table distribution to round-robin.

AnswerC

Partition elimination allows the query optimizer to skip partitions that do not contain relevant data. If queries filter on the partitioning column (e.g., date), and the predicate is sargable, the engine can scan only the necessary partitions. This directly reduces I/O and improves performance for date-range queries.

Why this answer

Partition elimination is a key performance feature in dedicated SQL pools. When queries filter on the partitioning column with a sargable predicate, the engine can prune partitions and read only the relevant data. This reduces the amount of data scanned and speeds up queries.

Ensuring that queries are written to take advantage of partition elimination is the most direct solution for slow date-range queries.

Exam trap

The trap here is assuming that index maintenance or distribution changes will fix slow date-range queries, when the actual issue is often lack of partition elimination.

Full explanation →

66

Drag & Dropmedium

Drag and drop the steps to configure Azure Stream Analytics job with event input and Power BI output into the correct order.

Drag or tap steps into the slots.

Steps

Order

1Step 1

2Step 2

3Step 3

4Step 4

Why this order

First, set up the event hub as the data source. Then create the Stream Analytics job, configure input and output, write the query, and start it.

Full explanation →

67

MCQhard

You are building a batch processing solution in Azure Synapse Analytics that reads data from a dedicated SQL pool, applies complex transformations using Synapse Spark, and writes the results back to the dedicated SQL pool. The pipeline must run on a schedule and handle transient failures with retries. Which approach should you use?

A.Use Azure Batch with a custom application to run Spark jobs

B.Use Azure Synapse Pipelines with a Notebook activity that runs Spark code

C.Use Azure Functions to trigger Spark jobs on demand

D.Use Azure Databricks with Auto Loader and Delta Live Tables

AnswerB

A Notebook activity inside Azure Synapse Pipelines runs the Spark transformation code while the surrounding pipeline supplies scheduling and built-in retry policies for transient failures. This satisfies both the batch orchestration and resilience requirements without external tooling.

Why this answer

Azure Synapse Pipelines with a Notebook activity is the correct approach because it natively integrates Synapse Spark for complex transformations and supports scheduling and retry policies for transient failures. This allows you to read from a dedicated SQL pool, process data in Spark, and write back to the pool without external orchestration, leveraging the built-in pipeline reliability features.

Exam trap

The trap here is that candidates may confuse Azure Batch or Azure Functions as viable alternatives for Spark job orchestration, overlooking the native integration and retry capabilities of Synapse Pipelines within the same service.

How to eliminate wrong answers

Option A is wrong because Azure Batch is a general-purpose batch computing service that does not natively integrate with Synapse Spark or dedicated SQL pools, requiring custom application development and lacking the seamless scheduling and retry capabilities of Synapse Pipelines. Option C is wrong because Azure Functions are event-driven and stateless, not designed for long-running Spark jobs or built-in retry logic for transient failures in a scheduled batch pipeline. Option D is wrong because Azure Databricks with Auto Loader and Delta Live Tables is a separate platform that does not natively integrate with Azure Synapse dedicated SQL pools, requiring additional connectivity setup and missing the unified orchestration provided by Synapse Pipelines.

Full explanation →

68

MCQmedium

You are partitioning a large fact table in Azure Synapse Dedicated SQL Pool by date. The table is used for queries that filter on CustomerID and Date. You want to minimize data movement. Which distribution strategy should you use?

A.Round-robin distribution

B.Hash distribution on CustomerID

C.Replicate distribution

D.Hash distribution on Date

AnswerB

Hash distribution on CustomerID co-locates rows sharing that value on the same compute node, so joins and aggregations filtering by CustomerID avoid reshuffling data across nodes. Date partitioning prunes partitions independently of distribution, satisfying both predicates while minimising data movement.

Why this answer

Hash distribution on CustomerID is correct because queries filtering on CustomerID and Date will benefit from collocated joins and aggregations when CustomerID is the distribution key. Since the table is large and partitioned by Date, hash distribution on CustomerID minimizes data movement by ensuring that rows with the same CustomerID reside on the same distribution node, allowing filters on Date to be applied locally within each partition.

Exam trap

The trap here is that candidates often assume partitioning and distribution should be on the same column (Date) to optimize date-range queries, but this ignores that distribution on the join key (CustomerID) is what minimizes data movement for the most common query pattern involving both CustomerID and Date filters.

How to eliminate wrong answers

Option A is wrong because round-robin distribution distributes rows evenly without any logical grouping, causing every query that filters on CustomerID to require full data movement across all distributions. Option C is wrong because replicate distribution copies the entire table to each distribution node, which is impractical for a large fact table due to storage overhead and write performance degradation. Option D is wrong because hash distribution on Date would cause data movement for queries filtering on CustomerID, as the distribution key does not align with the join or filter column, and partitioning on Date already provides local pruning without needing distribution on the same column.

Full explanation →

69

MCQmedium

You have an Azure Synapse Analytics workspace with a dedicated SQL pool. You need to create an external table that references Parquet files stored in Azure Data Lake Storage Gen2. The external table will be used for ad-hoc queries. Which statement correctly describes the required components?

A.You only need to create an external data source and an external file format.

B.You must create a PolyBase external table using the CREATE EXTERNAL TABLE AS SELECT (CETAS) statement.

C.You must use the OPENROWSET function instead of an external table.

D.You must create a database scoped credential, an external data source, and an external file format.

AnswerD

To create an external table in a dedicated SQL pool, you need a database scoped credential for authentication, an external data source pointing to the storage location, and an external file format defining the file type (Parquet). These components enable the pool to access and interpret the external data.

Why this answer

Creating an external table in a dedicated SQL pool requires three components: a database scoped credential for authentication, an external data source for the storage location, and an external file format for Parquet. These are essential for the pool to read the external data. Other options are either incomplete or describe different features.

Exam trap

The trap here is assuming that only a data source and file format are needed, forgetting the database scoped credential for authentication.

Full explanation →

70

MCQeasy

You need to transform JSON data containing nested arrays into a tabular format for analysis in Azure Synapse Analytics. Which transformation in Azure Data Factory or Synapse Pipelines should you use?

A.Join transformation

B.Derived Column transformation

C.Aggregate transformation

D.Flatten transformation

AnswerD

The Flatten transformation unnests nested arrays and structures, expanding each element into separate rows while preserving parent columns. This satisfies the requirement to convert hierarchical JSON into a tabular shape suitable for downstream analysis in Azure Synapse Analytics.

Why this answer

The Flatten transformation is specifically designed to denormalize nested JSON arrays into a tabular format by expanding array elements into multiple rows while preserving parent attributes. In Azure Data Factory and Synapse Pipelines, this transformation handles complex hierarchical structures like arrays of objects, making it the correct choice for converting JSON with nested arrays into a row-based dataset suitable for analysis in Azure Synapse Analytics.

Exam trap

The trap here is that candidates often confuse the Flatten transformation with the Unpivot transformation, which pivots columns into rows but does not handle nested JSON arrays, or they mistakenly think the Derived Column transformation can handle array expansion through expressions.

How to eliminate wrong answers

Option A is wrong because the Join transformation combines two streams based on matching keys, not for flattening nested arrays within a single JSON document. Option B is wrong because the Derived Column transformation creates or modifies columns using expressions but cannot expand nested arrays into multiple rows. Option C is wrong because the Aggregate transformation performs grouping and summarization operations (e.g., SUM, COUNT) and does not handle the denormalization of nested array structures.

Full explanation →

71

MCQeasy

You need to monitor the performance of an Azure Stream Analytics job in real time. Which Azure service should you use to track the job's resource utilization (e.g., SU % utilization) and set up alerts when the job is approaching its capacity?

A.Azure Monitor

B.Azure Advisor

C.Microsoft Sentinel

D.Azure Log Analytics

AnswerA

Azure Monitor collects Stream Analytics job metrics including SU % utilisation and supports metric alerts on thresholds. It satisfies the stem's requirement to track resource utilisation in real time and notify when the job approaches capacity, unlike log-only or billing-focused services.

Why this answer

Azure Monitor is the correct service for real-time monitoring of Azure Stream Analytics jobs, providing metrics such as SU % utilization, watermark delay, and input/output events. It allows you to create alert rules based on these metrics to notify when the job approaches capacity limits.

Exam trap

DP-203 often tests the distinction between Azure Monitor (metrics and alerts) and Log Analytics (log queries), causing candidates to choose Log Analytics for real-time metric monitoring.

How to eliminate wrong answers

Option B is wrong because Azure Advisor provides best-practice recommendations, not real-time performance monitoring or alerting. Option C is wrong because Microsoft Sentinel is a SIEM/SOAR solution for security analytics, not job performance monitoring. Option D is wrong because Azure Log Analytics is a log query and analysis service; while it can store logs, it does not natively provide the real-time metric tracking and alerting for SU utilization that Azure Monitor does.

Full explanation →

72

MCQhard

You have an Azure Synapse Analytics dedicated SQL pool with a table that uses hash distribution on CustomerID. You notice that queries joining this table with another table on OrderDate are slow. What is the most likely cause?

A.The table is not partitioned by OrderDate

B.Statistics on the join columns are outdated

C.The table should use round-robin distribution instead

D.The join columns are not aligned; data must be shuffled across distributions

AnswerD

Hash distribution places rows by CustomerID, so joining on OrderDate requires moving rows between distributions before the join completes. This shuffle, caused by misaligned join columns, is the most likely source of the slow query performance.

Why this answer

D is correct because in Azure Synapse Analytics dedicated SQL pools, hash distribution distributes rows across distributions based on a hash of the distribution key (CustomerID). When joining on OrderDate, which is not the distribution key, the join columns are not aligned across distributions. This forces data movement (shuffling) where rows from one or both tables must be redistributed to match the join key, causing significant performance degradation.

Exam trap

The trap here is that candidates often confuse partitioning with distribution, thinking that partitioning on the join column solves the data movement issue, when in fact distribution alignment is the critical factor for collocated joins in a distributed MPP system.

How to eliminate wrong answers

Option A is wrong because partitioning by OrderDate would help with partition elimination for scans or maintenance, but it does not address the fundamental issue of data movement required when join columns are not aligned with the distribution key. Option B is wrong because outdated statistics can cause suboptimal query plans, but the primary performance bottleneck here is the physical data movement across distributions, not statistics. Option C is wrong because round-robin distribution distributes rows evenly without any key, which would still require full data movement for any join, making performance even worse than hash distribution on a non-join column.

Full explanation →

73

Multi-Selectmedium

Which TWO Azure services can be used to monitor Azure Data Factory pipeline runs and set up alerts?

Select 2 answers

A.Log Analytics

B.Microsoft Sentinel

C.Azure Policy

D.Azure Monitor

E.Azure Advisor

AnswersA, D

Log Analytics stores Azure Data Factory diagnostic logs, and alert rules defined against that workspace trigger notifications on pipeline run failures or duration thresholds. This satisfies the monitoring and alerting requirement for Data Factory pipeline runs.

Why this answer

Azure Monitor (option D) is the native platform for collecting metrics and activity/diagnostic logs from Azure Data Factory and for creating alert rules (metric alerts, log search alerts, activity log alerts) that trigger on pipeline run failures or durations, so it directly satisfies the monitoring-and-alerting requirement. Log Analytics (option A) is the service where ADF diagnostic logs are stored and queried with KQL, and it powers log search alerts (via Azure Monitor) on pipeline run records such as PipelineRun and ActivityRun, making it a valid way to monitor runs and set alerts. Microsoft Sentinel (B) is a SIEM/SOAR for security analytics, not pipeline operational monitoring; Azure Policy (C) enforces governance/compliance rules rather than monitoring runs; and Azure Advisor (E) only provides best-practice recommendations, not run monitoring or alerting.

Exam trap

DP-203 often tests the distinction between monitoring/alerting services (Azure Monitor, Log Analytics) and governance/security services (Azure Policy, Microsoft Sentinel, Azure Advisor), so candidates may incorrectly select Azure Policy for alerting or Microsoft Sentinel for pipeline monitoring.

Full explanation →

74

Drag & Dropmedium

Drag and drop the steps to set up Azure Data Factory pipeline with parameterization and dynamic expressions into the correct order.

Drag or tap steps into the slots.

Steps

Order

1Step 1

2Step 2

3Step 3

4Step 4

Why this order

Create the pipeline, define parameters, use them in activities and linked services, then trigger with values.

Full explanation →

75

MCQeasy

Refer to the exhibit. You are reviewing an Azure Stream Analytics job query. The job has a stream input and a reference data input. The job is failing with the error 'Reference data input must be of type Reference, not Stream'. What is the cause of the error?

A.The input alias is incorrect.

B.The JOIN syntax is incorrect for reference data.

C.The input type is Stream; it should be Reference.

D.The output type is set to ReferenceData; it should be a different type.

AnswerC

Stream Analytics classifies each input as Stream or Reference at creation. The query joins reference data, but the input was configured as Stream, so the engine rejects it; changing the input type to Reference resolves the error.

Why this answer

The error 'Reference data input must be of type Reference, not Stream' explicitly indicates that the input configured as Stream should be set to Reference type. In Azure Stream Analytics, reference data inputs are used for static lookup data, and they must be declared as Reference input type. Setting the input type to Stream when it should be Reference causes this error.

Therefore, option C is correct. Option D is incorrect because the error message does not relate to output configuration; it is strictly about input type mismatch.

Exam trap

Candidates may be misled by the wording 'Reference data input' and think it's about output, but the error is straightforward: the input type is wrong. The trap is overthinking or assuming a complex output issue when the error message directly states the input type must be Reference.

How to eliminate wrong answers

Option A is wrong because the input alias being incorrect would cause a different error, such as 'Input alias not found' or 'Invalid input alias', not a type mismatch error. Option B is wrong because the JOIN syntax for reference data (e.g., using temporal join with ON clause) is correct; the error is about the input type, not the syntax. Option C is wrong because it states the input type is Stream when it should be Reference, which is actually the correct diagnosis of the problem, but the question asks for the cause of the error, and the answer option D correctly identifies that the output type is not the issue; the error is caused by the input being Stream instead of Reference, so C is a distractor that describes the problem rather than the cause as framed in the options.

Full explanation →

Page 1 of 7

All pages

Practice DP-203 by domain

Target a specific domain to shore up weak areas.

Secure, monitor, and optimize data storage and data processing Develop data processing Design and implement data storage

See all domains with question counts →