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

Question 1

You are designing a data pipeline that uses Azure Data Factory to copy data from an Azure SQL database to Azure Data Lake Storage Gen2. The data contains personally identifiable information (PII) that must be masked. Which Data Factory feature should you use?

Accepted Answer

Use a mapping data flow to apply a mask transformation on PII columns.. Option B is correct because Data Factory data flows allow column-level transformations including data masking. Option A is wrong because copy activity only copies data without transformation. Option C is wrong because stored procedures run on the source, not during copy. Option D is wrong because staging is for bulk copy performance, not masking.

Answer

Use a copy activity with a query to select only non-PII columns.

Answer

Use a stored procedure activity to mask data in the source before copy.

Answer

Enable staging on the copy activity to use PolyBase.

Question 2

Which TWO actions should you take to ensure that data at rest is encrypted in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Enable infrastructure-level encryption using double encryption.. Options A and D are correct. TDE encrypts data at rest for dedicated SQL pool, and enabling double encryption provides additional protection. Option B is wrong because Always Encrypted is for client-side encryption, not at rest. Option C is wrong because column-level encryption is for specific columns, not the whole pool. Option E is wrong because creating a customer-managed key is only part of TDE configuration, not a separate action.

Answer

Enable Always Encrypted with secure enclaves.

Answer

Apply column-level encryption using ENCRYPTBYPASSPHRASE.

Answer

Create a customer-managed key in Azure Key Vault.

Question 3

Which THREE metrics from Azure Monitor should you use to evaluate the performance of an Azure Data Lake Storage Gen2 account?

Accepted Answer

Ingress. Options B, C, and D are correct. Success E2E Latency measures end-to-end latency, Blob Count tracks object count, and Ingress measures throughput. Option A is wrong because Available Storage Capacity is not a metric for storage accounts. Option E is wrong because CPU usage is for compute, not storage.

Answer

CPU Usage

Answer

Available Storage Capacity

Question 4

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

Accepted Answer

Azure Monitor with Log Analytics. Options B and D are correct. DMVs provide detailed query execution statistics, and Azure Monitor with Log Analytics can store and analyze performance data. Option A is wrong because Azure Advisor provides recommendations but not real-time monitoring. Option C is wrong because SQL Data Sync is for synchronization. Option E is wrong because Azure Policy is for governance.

Answer

SQL Data Sync

Answer

Azure Policy

Answer

Azure Advisor

Question 5

Your company uses Azure Databricks for data processing. You need to ensure that spark jobs cannot access certain storage accounts. What is the most secure approach?

Accepted Answer

Use Azure RBAC to grant specific storage account permissions to the Azure Databricks managed identity.. Option D is correct because Azure RBAC on storage accounts using Microsoft Entra ID (formerly Azure AD) is the recommended way to control access. Option A is wrong because shared access keys provide broad access. Option B is wrong because secrets are not recommended for production. Option C is wrong because disabling firewall doesn't help.

Answer

Store storage account keys in Azure Key Vault and retrieve them in notebooks.

Answer

Use shared access keys and restrict their usage.

Answer

Disable public network access on storage accounts.

Question 6

You are optimizing cost for an Azure Data Lake Storage Gen2 account that stores historical data. The data is accessed infrequently after 30 days and must be retained for 7 years. Which lifecycle management rule should you apply?

Accepted Answer

Move blobs to cool tier after 30 days, then to archive tier after 1 year.. Option B is correct because moving blobs to cool tier after 30 days and then to archive after a longer period is cost-effective. Option A is wrong because deleting after 30 days loses data. Option C is wrong because premium tier is expensive. Option D is wrong because archive after 30 days would incur high retrieval costs.

Answer

Move blobs to archive tier immediately after 30 days.

Answer

Delete blobs after 30 days.

Answer

Move blobs to premium tier after 30 days.

Question 7

You are designing a data ingestion pipeline for Azure Synapse Analytics. The pipeline will load sensitive financial data from an on-premises SQL Server to a dedicated SQL pool. The data must be encrypted at rest and in transit. Which combination of features should you use?

Accepted Answer

Use Azure Synapse Transparent Data Encryption (TDE) and enforce TLS 1.2 for connections.. Option C is correct because Transparent Data Encryption (TDE) encrypts data at rest in the dedicated SQL pool, and TLS ensures encryption in transit. Option A is wrong because Always Encrypted is for column-level encryption, not for the entire pipeline. Option B is wrong because Azure Disk Encryption is for IaaS VMs, not for Azure Synapse. Option D is wrong because client-side encryption is not built-in for Synapse ingestion.

Answer

Use Always Encrypted in SQL Server and Azure Key Vault for column encryption.

Answer

Enable Azure Disk Encryption on the source server and use a VPN gateway.

Answer

Implement client-side encryption using Azure Storage client library and store keys in Microsoft Purview.

Question 8

You are monitoring an Azure Synapse Analytics dedicated SQL pool that is experiencing performance degradation during peak hours. You notice that some queries are being queued due to resource contention. You need to optimize query performance without scaling the Data Warehouse Units (DWUs). Which action should you take?

Accepted Answer

Configure result-set caching for the dedicated SQL pool.. Option D is correct because result-set caching can significantly reduce query time for repeated queries by storing results in SSD, reducing resource contention. Option A is wrong because increasing DWUs changes the scale, which is not allowed per the requirement. Option B is wrong because materialized views help but do not directly address contention from repeated queries. Option C is wrong because workload classification manages concurrency but does not reduce resource usage for repeated queries.

Answer

Increase the DWU setting to allocate more resources.

Answer

Create materialized views for frequently joined tables.

Answer

Implement workload classification and assign the queries to a higher importance level.

Question 9

You have an Azure Data Lake Storage Gen2 account that stores sensitive customer data. You need to implement security controls to prevent data exfiltration by a malicious insider who has Contributor role access. Which Azure feature should you use?

Accepted Answer

Apply an Azure Policy that denies data access from unapproved locations.. Option A is correct because diagnostic settings log activity to a Log Analytics workspace, which can be used for monitoring and alerting on suspicious data access, but the question asks to prevent exfiltration. Actually, the best prevention is to use Azure Policy to restrict data access. Wait, re-reading: The question is about preventing exfiltration. The correct answer is to use Azure Policy with deny effect to restrict access to certain data. But among options, Option A (diagnostic settings) is monitoring, not prevention. Option B (Azure Policy with deny effect) is prevention. Option C (RBAC with read-only) could help but insider has Contributor. Option D (firewall rules) can restrict network access. The best is to use Azure Policy to enforce data access rules. Given the options, Option B is most relevant. However, I need to ensure correctness. Let me re-evaluate: The insider has Contributor role, which allows write/read. To prevent exfiltration, you can use Azure Policy to deny access to specific data or use service endpoints. Option B is correct because Azure Policy can enforce that data cannot be accessed from outside the corporate network. Option A is wrong because it only monitors. Option C is wrong because the user already has Contributor. Option D is wrong because firewall rules can be bypassed by the insider if they are inside the network. So correct is B.

Answer

Enable diagnostic settings to log all access to the storage account.

Answer

Remove the Contributor role and assign a custom role with read-only permissions.

Answer

Configure network firewall rules to allow only trusted IP addresses.

Question 10

You are optimizing an Azure Data Factory pipeline that moves data from Azure Blob Storage to Azure SQL Database. The pipeline currently uses a Copy activity with a staging setting enabled. You notice high DTU consumption on the Azure SQL Database during the copy operation. How can you reduce the impact on the source database?

Accepted Answer

Configure the Copy activity to use staging with Azure Blob Storage and then use a stored procedure to bulk insert.. Option B is correct because using PolyBase in Azure Synapse Analytics (not SQL Database) is for bulk loads, but the target is Azure SQL Database. Actually, the correct approach is to use staging copy with Azure Blob Storage as staging and then use PolyBase? Wait, the target is Azure SQL Database, not Synapse. For SQL Database, using a staging blob and then bulk insert can reduce DTU consumption. But among options, Option A (increase DTU) is scaling, not reduction. Option B (use PolyBase) is for Synapse. Option C (use staging copy with Azure Blob Storage and then use bulk insert) is correct. Option D (use parallel copy) increases throughput but also DTU. So correct is C.

Answer

Use PolyBase to load data directly from Blob Storage to Azure SQL Database.

Answer

Enable parallel copy in the Copy activity settings.

Answer

Increase the DTU of the Azure SQL Database to handle the load.

Question 11

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?

Accepted Answer

Use the Spark UI available in Synapse Studio.. Option A is correct because 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 12

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. Option A is correct because 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 13

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.. Option D is correct because the SHIR needs network access to the on-premises database; if the database is not reachable due to firewall or network configuration, it will fail. Option A is wrong because SHIR does not require inbound ports. Option B is wrong because SHIR uses outbound to Azure, but the error is connectivity to on-premises. Option C is wrong because SHIR does not require Azure Key Vault for basic connectivity.

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 14

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.. Option B is correct because partitioning the data by a commonly filtered column, such as date, allows the serverless SQL pool to use partition elimination, reducing the amount of data scanned. Option A is wrong because increasing MAXDOP may not help and can cause resource contention. Option C is wrong because usingOPENROWSET with CSV is less efficient than Parquet. Option D is wrong because materialized views are not supported in serverless 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 15

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. Option C is correct because 'Queued queries' metric directly shows the number of queries waiting due to concurrency limits. Option A is wrong because DWU percentage shows resource usage, not queuing. Option B 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