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

Question 1

You have an Azure Data Lake Storage Gen2 account that stores sensitive customer data. You need to prevent data exfiltration to unauthorized external IP addresses. Which TWO actions should you take?

Accepted Answer

Use private endpoints for the storage account. Network security controls: enabling firewall and deny access from internet, plus configuring service endpoints or private endpoints. Option A (Azure Firewall) is not a storage setting. Option D (Geo-redundant storage) is for durability, not security. Option E (Shared access signatures) is for fine-grained access but not prevent exfiltration.

Answer

Enable Azure Firewall on the storage account

Answer

Use shared access signatures (SAS) with limited permissions

Answer

Enable geo-redundant storage (GRS)

Question 2

Your Azure Synapse Analytics dedicated SQL pool is experiencing high resource utilization. You need to identify queries that are consuming excessive memory. Which THREE DMVs should you query?

Accepted Answer

sys.dm_pdw_sql_requests. sys.dm_pdw_exec_requests shows requests, sys.dm_pdw_request_steps shows steps, and sys.dm_pdw_sql_requests shows distributed SQL requests. Option D is for resource waits, Option E is for node resource usage.

Answer

sys.dm_pdw_nodes_resource_usage

Answer

sys.dm_pdw_waits

Question 3

You need to secure access to an Azure Storage account that hosts sensitive data. Which TWO methods provide encryption in transit?

Accepted Answer

Enforce HTTPS for REST API calls. HTTPS and SMB over encryption ensure data is encrypted in transit. Option C (SSE) is at rest. Option D (Azure Disk Encryption) is for VMs. Option E (IPSec) is not supported by Azure Storage directly.

Answer

Enable Azure Storage Service Encryption (SSE)

Answer

Enable Azure Disk Encryption on client VMs

Answer

Configure an IPsec policy between clients and storage

Question 4

You are a data engineer for a healthcare company. You have a production Azure Synapse Analytics dedicated SQL pool (DW500c) that hosts patient data. The pool is used for both ETL and reporting. You need to ensure that reporting queries always get resources even during heavy ETL loads. You also need to monitor query performance and set up alerts when certain queries exceed a threshold. You have configured workload management using workload groups and classifiers. However, reporting queries are still waiting for resources when ETL is running. You check the sys.dm_pdw_exec_requests DMV and see that ETL queries are using the largest resource class. You need to modify the configuration to guarantee resources for reporting. What should you do?

Accepted Answer

Create a new workload group for reporting with min_percentage_resource set to 30%. Workload isolation is achieved by creating separate workload groups with minimum resource allocation. By setting minimum resources for the reporting workload group, it ensures a baseline of resources even during heavy ETL. Option A (increase DWU) is costly and may not isolate. Option B (importance) helps but without min resources, high concurrency can still starve. Option D (change classifier) does not guarantee resources.

Answer

Set importance to HIGH for the reporting workload group

Answer

Increase the DWU setting to DW1000c

Answer

Change the classifier for reporting queries to use the same workload group as ETL but with a different resource class

Question 5

Your company uses Azure Data Lake Storage Gen2 for a data lake. You need to implement a security strategy that meets the following requirements: 1) All data must be encrypted at rest using customer-managed keys stored in Azure Key Vault. 2) Access to the storage account must be restricted to specific virtual networks. 3) Users must authenticate using Microsoft Entra ID and be granted read-only access to the 'landing' container. You have configured the storage account with Azure Storage Service Encryption (SSE) using customer-managed keys. You have also configured firewall rules to allow access only from the required virtual network. However, users cannot access the 'landing' container even though they have the Storage Blob Data Reader role. What is the most likely issue?

Accepted Answer

The Key Vault firewall is blocking access from the storage account. When using SSE with customer-managed keys, the storage account must have the 'Allow trusted Microsoft services to access this storage account' setting enabled, or the Key Vault must allow access from the storage account. Option A is wrong because RBAC is correctly assigned. Option B is wrong because firewall is set. Option D is wrong because they have read role.

Answer

The users have not been granted access to the Key Vault

Answer

The users do not have the Storage Blob Data Reader role assigned at the container scope

Answer

The firewall is blocking the users' IP addresses even though they are in the virtual network

Question 6

Your company runs an Azure Data Factory pipeline that copies data from an FTP server to Azure Blob Storage daily. Recently, the pipeline has been failing with the error: 'Failure happened on 'Source' side. ErrorCode=UserErrorFailedFileOperation, Error details: The remote server returned an error: (550) File unavailable (e.g., file not found, no access).' The FTP server administrator confirms that the file exists and the credentials are correct. You need to resolve the issue with minimal administrative effort. What should you do?

Accepted Answer

Check the file path and correct the case sensitivity in the dataset. The error indicates the file is not found or access denied. The most common cause is that the file path is case-sensitive on the FTP server. Verify the correct path with case sensitivity. Option A is wrong because credentials are confirmed correct. Option B is wrong because the file exists. Option D is wrong because changing the connector may not help if path is wrong.

Answer

Use an SFTP connector instead of FTP

Answer

Reset the FTP server credentials in the linked service

Answer

Ask the FTP administrator to re-upload the file

Question 7

Your organization has an Azure Synapse Analytics dedicated SQL pool that stores sensitive customer data. You need to ensure that only authorized users can access the data, and auditing must be enabled to track all access attempts. What should you do first?

Accepted Answer

Configure Microsoft Entra ID authentication and use RBAC to grant only necessary permissions.. Option B is correct because enabling Microsoft Entra ID authentication and assigning roles via RBAC is the first step to control access. Option A is wrong because enabling auditing does not restrict access. Option C is wrong because column-level security only restricts specific columns, not overall access. Option D is wrong because dynamic data masking obfuscates data but does not prevent access.

Answer

Implement column-level security to restrict sensitive columns.

Answer

Enable auditing on the SQL pool and configure a storage account for audit logs.

Answer

Apply dynamic data masking to the sensitive columns.

Question 8

Refer to the exhibit. You are reviewing an Azure Policy definition that was created to audit diagnostic settings for Azure Data Lake Storage Gen2. The policy is assigned to a management group containing several storage accounts. You notice that the policy is not flagging storage accounts that lack diagnostic settings. What is the most likely cause?

Accepted Answer

The existence condition incorrectly checks for a specific log category 'StorageRead' instead of any diagnostic setting.. Option C is correct because the existence condition checks if a diagnostic setting of category 'StorageRead' exists, but the policy should check for any diagnostic setting. Option A is wrong because the policy type is custom, but that doesn't cause the issue. Option B is wrong because the effect is parameterized and default is AuditIfNotExists, which should work. Option D is wrong because the field check for type is correct.

Answer

The 'field' property in the 'if' condition should be 'type' equals 'Microsoft.Storage/storageAccounts/blobServices' for Data Lake.

Answer

The effect parameter is not assigned a value; the default 'AuditIfNotExists' is not applied.

Answer

The policy type is 'Custom', which requires special permissions to evaluate.

Question 9

You are designing a data pipeline in Azure Data Factory that processes streaming data from Azure Event Hubs and stores it in Azure Data Lake Storage Gen2. The data must be encrypted at rest and in transit. Which configuration ensures encryption in transit?

Accepted Answer

Use HTTPS endpoint for Azure Data Lake Storage Gen2.. Option C is correct because HTTPS ensures encryption in transit. Option A is wrong because encryption at rest does not cover transit. Option B is wrong because TLS is used for SQL connections, not for Data Lake. Option D is wrong because Azure Firewall does not provide encryption.

Answer

Enable encryption at rest using Azure Storage Service Encryption.

Answer

Configure the Azure Data Factory integration runtime to use TLS 1.2.

Answer

Deploy Azure Firewall to inspect traffic between Event Hubs and Data Lake.

Question 10

Your company uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2. You need to monitor query performance and identify queries that are consuming excessive resources. Which Azure tool should you use?

Accepted Answer

Synapse Studio monitoring hub.. Option A is correct because Synapse Studio provides monitoring dashboards for serverless SQL pool. Option B is wrong because Azure Monitor Metrics can show some metrics but not query-level details. Option C is wrong because Log Analytics can be used but requires diagnostic settings. Option D is wrong because Query Performance Insight is for dedicated SQL pool.

Answer

Azure Monitor Metrics for the serverless SQL pool.

Answer

Azure Log Analytics queries against diagnostic logs.

Answer

SQL Server Management Studio Query Performance Insight.

Question 11

You are optimizing an Azure Synapse Analytics dedicated SQL pool. The workload includes large fact tables and dimension tables. You need to improve query performance for star join queries. Which TWO actions should you take?

Accepted Answer

Use replicated distribution on dimension tables.. Options B and D are correct. Using hash-distributed fact tables on the join key and replicated dimension tables are best practices for star joins. Option A is wrong because round-robin is for staging tables, not for fact tables. Option C is wrong because clustered columnstore index is default and recommended, not heap. Option E is wrong because distribution on dimension tables should be replicated, not hash.

Answer

Use round-robin distribution on fact tables.

Answer

Use hash distribution on dimension tables.

Answer

Use heap table structure for fact tables.

Question 12

Your organization uses Azure Data Lake Storage Gen2 to store parquet files. You need to secure the data at rest and control access. Which THREE methods should you implement?

Accepted Answer

Set POSIX-like ACLs on directories and files.. Options A, B, and D are correct. Encryption at rest is done by Azure Storage Service Encryption. Access control is via RBAC and ACLs. Option C is wrong because firewall restricts network access, not data at rest. Option E is wrong because soft delete is for data recovery, not security.

Answer

Configure Azure Storage Firewall to allow only trusted IPs.

Answer

Enable soft delete for blobs.

Question 13

You are monitoring an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. You notice frequent failures due to transient network errors. Which TWO actions should you take to improve reliability?

Accepted Answer

Enable fault tolerance in the copy activity to skip incompatible rows.. Options B and C are correct. Retry policy and fault tolerance handle transient errors. Option A is wrong because staging is for bulk copies, not for transient errors. Option D is wrong because self-hosted IR is for connectivity, not for retry. Option E is wrong because ParallelCopy increases throughput but not reliability.

Answer

Deploy a self-hosted integration runtime on a VM in Azure.

Answer

Use staged copy with Azure Data Lake as intermediate storage.

Answer

Increase the degree of copy parallelism.

Question 14

You are a data engineer for a global e-commerce company. The company uses Azure Synapse Analytics dedicated SQL pool for its data warehouse. The environment includes a large fact table 'Sales' distributed by hash on 'CustomerID', and dimension tables 'Customer' (hash-distributed on 'CustomerID') and 'Product' (replicated). Recently, queries that join Sales and Customer are performing poorly. You run a query to check data skew on the Sales table and find that one distribution has 40% more rows than the average. Additionally, the Customer table has high data movement during joins. You need to optimize the performance of these joins. What should you do?

Accepted Answer

Change the distribution of the Customer table to replicated.. Option B is correct because changing the Customer table to replicated distribution eliminates data movement during joins. Option A is wrong because changing the distribution key of Sales to a different column may not solve skew if the key is not the join column. Option C is wrong because round-robin is not suitable for fact tables in star joins. Option D is wrong because increasing DWU may alleviate symptoms but does not fix the root cause of data movement.

Answer

Increase the data warehouse performance level (DWU) to allocate more resources.

Answer

Change the distribution of the Sales table to round-robin.

Answer

Change the distribution key of the Sales table to 'ProductID' to align with the Product table.

Question 15

Your company has an Azure Data Factory pipeline that ingests data from multiple sources into Azure Data Lake Storage Gen2. The pipeline uses a self-hosted integration runtime (IR) running on an on-premises Windows server. Recently, the pipeline started failing with 'Connection timed out' errors during peak hours. You suspect network congestion. You need to resolve this issue with minimal cost and without modifying the pipeline activities. What should you do?

Accepted Answer

Scale out the self-hosted IR by adding more nodes to the cluster.. Option C is correct because scaling out the self-hosted IR by adding more nodes distributes the load and reduces timeout issues. Option A is wrong because moving to Azure-SSIS IR is expensive and unnecessary. Option B is wrong because increasing polling interval does not fix timeouts. Option D is wrong because Azure ExpressRoute is a costly network upgrade.

Answer

Implement Azure ExpressRoute to provide dedicated bandwidth.

Answer

Increase the 'Polling Interval' setting in the copy activity.

Answer

Migrate the self-hosted IR to Azure-SSIS IR.