DP-203 Secure, monitor, and optimize data storage and data processing Practice Test 14 — 15 Questions

Question 1

You have an Azure Synapse Analytics workspace with Apache Spark pools. You need to monitor Spark application performance and identify stages that are taking the longest time. Which tool should you use?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Use the Spark UI available in Synapse Studio.. The Spark UI provides detailed information about stages, tasks, and executors. Option B is wrong because Azure Monitor metrics provide aggregate metrics but not stage-level details. Option C is wrong because Log Analytics queries can analyze logs but not as directly as Spark UI. Option D is wrong because Synapse Studio provides a job view but not as granular as Spark UI.

Answer

Run KQL queries in Log Analytics against Spark logs.

Answer

Query Azure Monitor metrics for the Spark pool.

Answer

Use the Synapse Pipeline monitoring view.

Question 2

You need to ensure that data stored in Azure Data Lake Storage Gen2 is encrypted at rest using customer-managed keys. Which Azure service should you use to manage the keys?

Accepted Answer

Azure Key Vault. Azure Key Vault is used to store customer-managed keys for Azure Storage encryption. Option B is wrong because Microsoft Purview is for data governance. Option C is wrong because Azure Confidential Computing is for compute. Option D is wrong because Microsoft Entra ID is for identity.

Answer

Microsoft Purview

Answer

Azure Confidential Computing

Answer

Microsoft Entra ID

Question 3

You have an Azure Data Factory pipeline that uses a Self-Hosted Integration Runtime (SHIR) to copy data from an on-premises Oracle database to Azure Blob Storage. The pipeline is failing with a connectivity error. You have verified that the SHIR is running and the network firewall allows outbound traffic to Azure. What is the most likely cause of the failure?

Accepted Answer

The SHIR cannot reach the Oracle database due to a network firewall.. The most likely cause is that the SHIR cannot reach the on-premises Oracle database due to a network firewall. While the SHIR can connect to Azure outbound, the connection from the SHIR to the Oracle database must also be allowed through any on-premises firewalls. Option A is incorrect because if the SHIR were not registered, you would see a different error. Option C is incorrect because the SHIR only requires outbound connectivity to Azure, not inbound. Option D is incorrect because Azure Key Vault is not required for basic connectivity; it is used for storing credentials.

Answer

The SHIR is not registered with Azure Data Factory.

Answer

The SHIR requires inbound port 443 from Azure to on-premises.

Answer

The SHIR does not have access to Azure Key Vault.

Question 4

You are designing a data processing solution using Azure Synapse Analytics serverless SQL pool. The solution will query data stored in Parquet files in Azure Data Lake Storage Gen2. You need to ensure that the queries are optimized for performance. Which action should you take?

Accepted Answer

Partition the Parquet files by date and use partition pruning in the query.. Partitioning Parquet files by a commonly filtered column, such as date, allows Azure Synapse serverless SQL pool to perform partition pruning, which eliminates scanning unnecessary partitions and reduces the amount of data read. Option A is incorrect because increasing MAXDOP (maximum degree of parallelism) can lead to resource contention and may not improve query performance in serverless SQL pool. Option B is incorrect because Parquet is a columnar format optimized for analytics and is more efficient than CSV for querying large datasets. Option C is incorrect because materialized views are not supported in serverless SQL pool; they are only available in dedicated SQL pool.

Answer

Increase the MAXDOP setting in the query.

Answer

Convert the Parquet files to CSV format for faster parsing.

Answer

Create materialized views on the external tables.

Question 5

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?

Accepted Answer

Queued queries. 'Queued queries' metric directly shows the number of queries waiting due to concurrency limits. Option B is wrong because DWU percentage shows resource usage, not queuing. Option C is wrong because active queries show running queries. Option D is wrong because memory percentage shows memory pressure.

Answer

DWU percentage

Answer

Active queries

Answer

Memory percentage

Question 6

Which TWO actions should you take to secure data at rest in Azure Data Lake Storage Gen2? (Choose TWO)

Accepted Answer

Use Azure RBAC to grant least-privilege access to the storage account.. Correct answers: A and C. A: Use Azure RBAC to grant least-privilege access to the storage account—this ensures only authorized users can access data. C: Enable Azure Storage Service Encryption (SSE) for data at rest—this encrypts data automatically at the storage level. B is incorrect because dynamic data masking is used for databases, not for Azure Data Lake Storage Gen2. D is incorrect because firewall rules control network access, not data at rest. E is incorrect because audit logging is for monitoring, not securing data at rest.

Answer

Apply dynamic data masking to sensitive columns.

Answer

Configure firewall rules to restrict network access.

Answer

Enable audit logging for the storage account.

Question 7

Which THREE components are valid parts of the Microsoft Purview Data Map? (Choose THREE)

Accepted Answer

Scan rule sets. Correct answers: A, D, E. The Microsoft Purview Data Map consists of components that define how data sources are registered and scanned. Scan rule sets (A) are used to configure scanning rules for data sources. Data sources (D) are the actual data locations that are registered and scanned. Classifications (E) are the rules or patterns that identify data types (e.g., PII) during scanning; they are a core component of the Data Map. B (Sensitivity labels) is incorrect because sensitivity labels are part of Microsoft Information Protection, not a component of the Data Map itself, though they can be applied to assets in Purview. C (Data flows) is incorrect because data flows are part of Azure Data Factory, not the Data Map.

Answer

Sensitivity labels

Answer

Data flows

Question 8

Which THREE methods can you use to authenticate an Azure Data Factory pipeline to access Azure Data Lake Storage Gen2? (Choose THREE)

Accepted Answer

Use a service principal with a client secret or certificate.. The correct answers are A, B, and C. Azure Data Factory supports three authentication methods for ADLS Gen2: service principal with client secret or certificate (A), SAS token (B), and managed identity (C). Service principal uses an Azure AD application. SAS tokens provide scoped access. Managed identity allows Data Factory to authenticate without storing credentials. User account (D) and storage account access key (E) are not valid for ADLS Gen2 authentication.

Answer

Use a user account with password.

Answer

Use the storage account access key.

Question 9

You are reviewing the ARM template snippet for an Azure Data Lake Storage Gen2 account. The template fails to deploy with an error that the encryption key cannot be accessed. What is the most likely cause?

Accepted Answer

The Data Lake Storage account does not have Get and Wrap Key permissions on the key vault.. The error occurs because the Data Lake Storage Gen2 account uses a customer-managed key from a key vault for encryption. The account's managed identity must have the 'Get' and 'Wrap Key' permissions on the key vault to access the key. Without these permissions, the deployment fails. Option B correctly identifies this. Option A (soft-delete) is not required for initial access but is recommended for protection. Option C (key name/version) would cause a different error (key not found). Option D (URI format) would also cause a different error.

Answer

The key vault does not have soft-delete enabled.

Answer

The key name or version is incorrect.

Answer

The key vault URI is incorrectly formatted.

Question 10

You have an Azure Data Factory pipeline defined as shown. The pipeline is failing because the preCopyScript truncates the staging table before each run, but the table is empty on the first run. What change would you make to ensure the pipeline works correctly?

Accepted Answer

Change the preCopyScript to: IF OBJECT_ID('dbo.Staging') IS NOT NULL TRUNCATE TABLE dbo.Staging.. The preCopyScript runs on every pipeline execution. On the first run, the staging table does not exist, so TRUNCATE TABLE dbo.Staging would fail. By adding a check with IF OBJECT_ID, the script only truncates if the table exists. This handles the first run gracefully. Option A is wrong because removing the preCopyScript would not truncate the table on subsequent runs, potentially causing duplicate data issues. Option B is wrong because increasing writeBatchSize does not solve the truncation error. Option D is wrong because setting recursive to false affects how the source dataset handles recursive file systems, not the truncation problem.

Answer

Remove the preCopyScript entirely.

Answer

Increase the writeBatchSize to 50000 to speed up the copy.

Answer

Set recursive to false in the source.

Question 11

You are a data engineer for a large e-commerce company. You have an Azure Synapse Analytics dedicated SQL pool that stores transactional data. The pool is currently at DWU1000c. You have a critical dashboard that runs a complex query every 5 minutes. The query scans a large fact table partitioned by date. The query performance is degrading over time as data accumulates. You need to improve performance without increasing DWUs or changing the dashboard query. You also need to minimize data movement overhead. You have the following options:

A. Create a columnstore index on the fact table with a partition alignment.
B. Create a materialized view that aggregates the data at the partition level.
C. Implement result-set caching and set the cache to expire every 5 minutes.
D. Redistribute the fact table using hash distribution on the date column.

Which option should you choose?

Accepted Answer

Implement result-set caching and set the cache to expire every 5 minutes.. Result-set caching stores the exact query results and can serve the dashboard query instantly if the underlying data has not changed. Since the query runs every 5 minutes, setting the cache expiration to 5 minutes ensures fresh data. Option A (columnstore index) is wrong because the table likely already has a columnstore index (the default in Synapse). Option B (materialized view) is wrong because materialized views require maintenance and may not match the exact query. Option D (hash distribution on date) is wrong because it can cause data skew and does not reduce scan overhead as effectively as caching.

Answer

Create a columnstore index on the fact table with a partition alignment.

Answer

Create a materialized view that aggregates the data at the partition level.

Answer

Redistribute the fact table using hash distribution on the date column.

Question 12

Your Azure Synapse Analytics dedicated SQL pool is experiencing performance degradation. Queries that previously completed in seconds now take minutes. You suspect memory pressure and concurrency issues. What should you first review to diagnose the problem?

Accepted Answer

sys.dm_pdw_exec_requests. Sys.dm_pdw_exec_requests provides a list of all currently executing or recent requests in the dedicated SQL pool, including their status, resource class, and elapsed time. This is the best first step to identify queries that may be causing memory pressure or concurrency issues. Option A (sys.dm_pdw_resource_waits) shows only resource waits but does not show the actual requests causing the waits. Option B (sys.dm_pdw_waits) shows wait information but lacks the direct request context needed for diagnosis. Option C (sys.dm_pdw_query_stats_xe) provides query execution statistics but not real-time active requests, so it is less suitable for immediate troubleshooting.

Answer

sys.dm_pdw_resource_waits

Answer

sys.dm_pdw_waits

Answer

sys.dm_pdw_query_stats_xe

Question 13

You are designing a data lake architecture using Azure Data Lake Storage Gen2. Sensitive customer data must be encrypted at rest using customer-managed keys stored in Azure Key Vault. Additionally, access must be audited at the file level. Which combination of features should you implement?

Accepted Answer

Customer-managed keys (CMK) in Azure Key Vault and Azure Storage analytics logs. Customer-managed keys (CMK) in Azure Key Vault provide encryption at rest with customer-controlled keys, and Azure Storage analytics logs (or diagnostic settings) capture file-level audit events such as read and write operations. Option A is incorrect because Azure AD authentication controls access but does not provide encryption at rest. Option B is incorrect because Azure Information Protection is a classification and labeling solution, not an encryption mechanism for data at rest. Option D is incorrect because service-managed keys do not give customer control over encryption, and Azure Monitor alerts are for notifications, not audit logging.

Answer

Azure AD authentication and Azure Storage firewall

Answer

Azure Information Protection labels and Azure Policy

Answer

Service-managed keys and Azure Monitor alerts

Question 14

Your Azure Data Factory pipeline is failing with the error: 'Operation on target Copy data1 failed: The remote server returned an error: (403) Forbidden.' The source is Azure Blob Storage and the sink is Azure SQL Database. You have verified the SQL Database firewall rules allow Azure services. What is the most likely cause?

Accepted Answer

The Data Factory managed identity lacks Storage Blob Data Contributor role on the storage account. A 403 Forbidden error when reading from Azure Blob Storage typically indicates that the Data Factory does not have permission to access the storage. The managed identity used by Data Factory must have the Storage Blob Data Contributor role (or equivalent) on the storage account. Option A is incorrect because the error is 403, not a connectivity issue. Option B is incorrect because a private endpoint alone does not cause a 403; it would affect network connectivity. Option D is incorrect because throttling from Azure SQL Database would produce a different error (e.g., 429 Too Many Requests).

Answer

The SQL Database firewall is blocking the Data Factory IP

Answer

The storage account is behind a private endpoint

Answer

The SQL Database is throttling the write operations

Question 15

You are monitoring an Azure Synapse Analytics dedicated SQL pool using dynamic management views (DMVs). You want to identify queries that are experiencing excessive memory grants causing concurrency issues. Which DMV should you query?

Accepted Answer

sys.dm_pdw_exec_requests. (sys.dm_pdw_exec_requests) is correct because it contains the total_request_memory_granted column, which can be used to identify queries with excessive memory grants contributing to concurrency issues. Option A (sys.dm_pdw_waits) only shows wait types, not memory grants. Option C (sys.dm_pdw_resource_waits) shows resource wait statistics but not actual grant amounts. Option D (sys.dm_pdw_workload_management_workload_groups_details) provides workload group configuration and metrics, not per-query memory grant details.

Answer

sys.dm_pdw_waits

Answer

sys.dm_pdw_resource_waits

Answer

sys.dm_pdw_workload_management_workload_groups_details