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

Question 1

You need to ensure that an Azure Synapse Analytics serverless SQL pool automatically pauses after 2 hours of inactivity to save costs. Which feature should you configure?

Accepted Answer

Auto-pause feature. The Auto-pause feature in Azure Synapse Analytics serverless SQL pools automatically suspends the pool after a period of inactivity (default 1 hour, configurable up to 8 hours), reducing costs when not in use. Option C is correct because it directly addresses the requirement to pause after 2 hours of inactivity.

Answer

Maintenance window

Answer

Data Exfiltration Prevention

Answer

Workload Management

Question 2

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 the storage firewall to allow only specific virtual networks (Option D) restricts access to traffic originating from those VNets, blocking external IPs. Using private endpoints (Option A) ensures that all traffic to the storage account stays within the Microsoft backbone network, preventing exposure to the public internet. Option B is incorrect because Azure Firewall is a separate service, not a storage account setting. Option C (SAS) provides fine-grained access control but does not prevent exfiltration from external IPs. Option E (GRS) is for data durability, not security.

Answer

Enable Azure Firewall on the storage account

Answer

Use shared access signatures (SAS) with limited permissions

Answer

Enable geo-redundant storage (GRS)

Question 3

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. To identify queries consuming excessive memory in Azure Synapse Dedicated SQL pool, you can query sys.dm_pdw_exec_requests to view all requests, sys.dm_pdw_request_steps to view the steps of requests, and sys.dm_pdw_sql_requests to view the distributed SQL requests. Option B (sys.dm_pdw_nodes_resource_usage) shows node-level resource usage, and Option E (sys.dm_pdw_waits) shows waiting queries, but neither directly identifies memory-intensive queries.

Answer

sys.dm_pdw_nodes_resource_usage

Answer

sys.dm_pdw_waits

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%. Creating a separate workload group for reporting with a minimum percentage of resources (min_percentage_resource) guarantees a baseline amount of resources for reporting queries, isolating them from ETL even when ETL is using a large resource class. This is the correct solution. Option B (importance) can help order queries within the same group but does not guarantee resource availability if the group has no minimum. Option C (increasing DWU) adds more overall resources but does not isolate; reporting may still be starved if ETL uses them. Option D (changing classifier) does not guarantee resources because both queries would compete in the same group.

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. The most likely issue is that the Key Vault firewall is blocking access from the storage account. When using Azure Storage Service Encryption (SSE) with customer-managed keys, the storage account must be able to access the Key Vault to wrap and unwrap the encryption key. Even though the users have the Storage Blob Data Reader role and the storage account firewall allows the virtual network, if the Key Vault firewall does not permit access from the storage account (e.g., via 'Allow trusted Microsoft services' or a specific network rule), the storage service cannot decrypt the data, resulting in access failures. Option D correctly identifies this. Options A and B are incorrect because the users have the correct RBAC role and the role is effective at the storage account level; the issue is not with user permissions but with the storage account's ability to reach the Key Vault. Option C is incorrect because the firewall rules are correctly configured for the virtual network.

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 code 550 indicates that the file is not found or access is denied on the FTP server. Since the file exists and credentials are correct, the most likely cause is a case-sensitive file path mismatch. Option C is correct because checking and correcting the case sensitivity in the dataset resolves this. Option A is incorrect because switching to SFTP does not address the case sensitivity issue. Option B is incorrect because resetting credentials is unnecessary when they are already correct. Option D is incorrect because re-uploading the file does not fix the path reference problem.

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.. The first step to secure access to the Azure Synapse Analytics dedicated SQL pool is to configure authentication and authorization using Microsoft Entra ID and RBAC. This establishes who can access the pool and what they can do. Once that is in place, you can then implement additional security measures like auditing (option B), column-level security (option A), or dynamic data masking (option D), but these are secondary steps. Option B is not the first step because auditing tracks access but does not control it. Option A is too granular for initial access control. Option D obfuscates data but does not prevent unauthorized 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.. The existence condition checks for a specific log category 'StorageRead' rather than checking for any diagnostic setting. Azure Policy for auditing diagnostic settings should verify that at least one diagnostic setting exists, not that a particular category is configured. This causes storage accounts with diagnostic settings that do not include 'StorageRead' (or with no settings at all) to not be flagged. Option B is incorrect because the 'field' property 'type' with value 'Microsoft.Storage/storageAccounts' is appropriate for auditing diagnostic settings on the storage account level, not blobServices. Option C is incorrect because the effect parameter defaults to 'AuditIfNotExists' even without explicit assignment, so that is not the issue. Option D is incorrect because custom policy type does not require special permissions beyond those needed to assign policies; evaluation occurs normally.

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.. HTTPS ensures encryption in transit for Azure Data Lake Storage Gen2. Option A is wrong because encryption at rest does not cover data in transit. Option C is wrong because while TLS 1.2 is important, the integration runtime configuration does not directly apply to the storage endpoint; the storage account itself must use HTTPS. Option D is wrong because Azure Firewall does not provide encryption; it filters traffic.

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.. Synapse Studio monitoring hub provides built-in dashboards for serverless SQL pool queries, showing execution details, resource consumption, and query performance. This is the most direct and integrated tool for monitoring serverless SQL pool queries in Azure Synapse Analytics. Azure Monitor Metrics provides pool-level metrics but not query-level details. Azure Log Analytics can be used with diagnostic settings to analyze logs, but it requires additional configuration and is not the primary tool for ad-hoc query monitoring. SQL Server Management Studio Query Performance Insight is designed for dedicated SQL pools, not serverless.

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.. The correct actions are D and E. For star join queries in Azure Synapse Analytics dedicated SQL pool, best practices are to use hash distribution on fact tables using the join key (option E) to distribute data across distributions efficiently, and use replicated distribution on dimension tables (option D) to avoid data movement. Option A is wrong because round-robin distribution is suitable for staging or temporary tables, not for large fact tables in star schemas. Option B is wrong because dimension tables should be replicated, not hash-distributed, to minimize shuffling. Option C is wrong because heap tables are not optimized for analytical queries; clustered columnstore index is the recommended table structure for fact tables.

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 C and D are correct. Enabling fault tolerance allows the copy activity to skip incompatible rows and continue, while configuring a retry policy automatically retries the activity on failure due to transient errors. Option A is incorrect because deploying a self-hosted IR in Azure does not address transient network errors; it is used for connectivity to on-prem data stores. Option B is incorrect because staged copy is for copying large datasets efficiently, not for handling transient errors. Option E is incorrect because increasing parallelism improves throughput but does not improve reliability against transient failures.

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.. To change the distribution of the Customer table to replicated. In Azure Synapse Analytics dedicated SQL pool, replicated tables are small enough to be cached on each compute node, eliminating the need to move data during joins. Since Customer is a dimension table, it is likely small enough to benefit from replication. This directly addresses the high data movement during joins. Option B (increasing DWU) would allocate more resources but does not fix the root cause of data movement and may only mask the issue. Option C (changing Sales to round-robin) is not recommended because fact tables in star schemas should be hash-distributed on a join key to minimize data movement. Option D (changing Sales distribution key to ProductID) would not improve the join with Customer unless the join is on ProductID, but the problem is with the Sales-Customer join; changing the distribution key to ProductID would affect the join with Product, not Customer, and could worsen the skew issue.

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.. Scaling out the self-hosted IR by adding more nodes distributes the load and reduces timeout issues. Option A is wrong because Azure ExpressRoute provides dedicated bandwidth but is costly and overkill for this scenario. Option B is wrong because increasing the polling interval does not fix network timeouts; it only changes how often the activity checks for data. Option D is wrong because migrating to Azure-SSIS IR is expensive and unnecessary for this pipeline.

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.