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

Question 1

You are optimizing a batch processing job in Azure Databricks that reads data from Azure Data Lake Storage Gen2 and writes aggregated results back. The job currently runs slowly due to high shuffle writes. You plan to use Delta Lake and optimize the table layout. Which two actions should you take to reduce shuffle writes? (Select two.)

Accepted Answer

Partition the Delta table by the most frequently used filter column.. Options A and D are correct. Optimizing the data layout with Z-Ordering and compaction reduces shuffle size. Option B is wrong because increasing the number of shuffle partitions can increase shuffle writes. Option C is wrong because enabling auto-optimize helps but does not directly reduce shuffle writes. Option E is wrong because using broadcast hash join reduces shuffles if one table is small, but not generally for large tables.

Answer

Enable Delta Lake auto-optimize to coalesce small files.

Answer

Use a broadcast hash join hint for all joins.

Answer

Increase the number of shuffle partitions to 400.

Question 2

Your team uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2. You notice that queries are running slower than expected. You need to improve query performance by reducing the amount of data scanned. Which two features should you implement? (Select two.)

Accepted Answer

Partition the data in the data lake and use partition elimination in queries.. Options A and C are correct. File pruning reduces data scanned by skipping irrelevant files. Partition elimination reduces data scanned by skipping irrelevant partitions. Option B is wrong because materialized views store pre-computed data but do not reduce scan on source. Option D is wrong because result-set caching caches query results but does not reduce data scanned for new queries. Option E is wrong because enabling auto-optimize is for Delta Lake, not serverless SQL.

Answer

Enable result-set caching for the serverless SQL pool.

Answer

Enable auto-optimize on the Delta Lake tables.

Answer

Create materialized views on the serverless SQL pool.

Question 3

You have an Azure Data Lake Storage Gen2 account that stores log files. You need to implement a data retention policy so that logs older than 90 days are automatically deleted. What should you use?

Accepted Answer

Lifecycle management policy. Option B is correct because a lifecycle management policy can automatically delete blobs based on age. Option A is wrong because Azure Blob Storage inventory provides reports but does not delete. Option C is wrong because Azure Policy enforces compliance but does not delete data. Option D is wrong because Azure Purview scopes metadata and data discovery, not lifecycle management.

Answer

Azure Policy

Answer

Azure Blob Storage inventory

Answer

Microsoft Purview

Question 4

You are monitoring an Azure Synapse Analytics dedicated SQL pool and notice that some queries are experiencing high wait times due to concurrency slots being exhausted. You need to optimize the workload to reduce contention. Which three actions should you take? (Select three.)

Accepted Answer

Create workload groups with different importance levels.. Options A, C, and D are correct. Classifying queries into workload groups and assigning importance helps prioritize critical queries. Using workload isolation ensures that resource-intensive queries do not block others. Setting a minimum percentage of resources for small queries ensures they are not starved. Option B is wrong because increasing service level (e.g., DWU) increases concurrency slots but may increase cost. Option E is wrong because result-set caching does not affect concurrency slot usage.

Answer

Increase the data warehouse service level (DWU).

Answer

Enable result-set caching for frequently executed queries.

Question 5

Refer to the exhibit. You deploy the ARM template to create a storage account. After deployment, a developer reports that they cannot access the storage account from an Azure Databricks notebook running in the same virtual network as specified in the subnet rule. What is the most likely reason?

Accepted Answer

The Databricks cluster is not using a managed virtual network with a service endpoint to the storage account.. Option D is correct because the default action is Deny, and only traffic from the specified subnet is allowed. The developer's Databricks notebook might be using a cluster that is not using a managed virtual network or is not in that subnet. Option A is wrong because HNS enabled does not block access. Option B is wrong because firewall is correctly set to Deny with a rule for the subnet. Option C is wrong because the key vault encryption doesn't block access.

Answer

The storage account uses customer-managed keys from Key Vault, which requires additional permissions.

Answer

The firewall default action is Deny, and there are no IP rules allowing the Databricks cluster's public IP.

Answer

The storage account has hierarchical namespace enabled, which blocks non-ADLS Gen2 tools.

Question 6

You need to monitor an Azure Data Factory pipeline for failures and send an email notification when a pipeline run fails. Which Azure service should you use to create an alert based on the pipeline run metrics?

Accepted Answer

Azure Monitor. Option A is correct because Azure Monitor can create alerts based on ADF metrics like 'Failed pipeline runs'. Option B is wrong because Azure Service Health monitors Azure service health, not pipeline runs. Option C is wrong because Azure Log Analytics is for log queries, not alerting. Option D is wrong because Microsoft Sentinel is for security.

Answer

Microsoft Sentinel

Answer

Azure Service Health

Answer

Azure Log Analytics

Question 7

You are designing a data processing solution using Azure Stream Analytics. You need to ensure that the output to Azure SQL Database is optimized to minimize the number of write operations. Which output configuration should you use?

Accepted Answer

Use a windowed aggregation to batch writes.. Option D is correct because using a windowed aggregation (e.g., TumblingWindow) reduces the number of writes by batching results. Option A is wrong because increasing batch size alone is not a Stream Analytics configuration. Option B is wrong because partitioning increases parallelism but not necessarily reduces writes. Option C is wrong because the 'All' compatibility level does not optimize write operations.

Answer

Use a partitioned output to distribute writes across multiple tables.

Answer

Set the compatibility level to 1.2.

Answer

Increase the event serialization format batch size.

Question 8

You have an Azure Synapse Analytics dedicated SQL pool that is used for reporting. You notice that the tempdb database is growing rapidly and causing queries to fail. Which two actions should you take to mitigate the issue? (Select two.)

Accepted Answer

Reduce the degree of parallelism (MAXDOP) for the workload.. Options B and D are correct. Reducing the degree of parallelism (DOP) limits the number of concurrent operations, reducing tempdb usage. Optimizing query performance can reduce large sorts and hash joins that use tempdb. Option A is wrong because moving tempdb is not supported in Azure Synapse. Option C is wrong because increasing DWU may provide more tempdb space but does not address the root cause. Option E is wrong because result-set caching does not affect tempdb usage.

Answer

Enable result-set caching to reduce query reruns.

Answer

Increase the service level (DWU) of the dedicated SQL pool.

Answer

Move tempdb to a separate storage account.

Question 9

Your organization uses Azure Purview for data governance. You need to automatically scan an Azure Data Lake Storage Gen2 account and classify sensitive data such as credit card numbers and social security numbers. What should you configure?

Accepted Answer

A new scan rule set in Purview with classification rules for sensitive data types. Option B is correct because Purview's classification rules can detect sensitive data patterns. Option A is wrong because Azure Policy is for resource compliance, not data classification. Option C is wrong because Azure Information Protection (now part of Microsoft Purview Information Protection) labels files but requires integration. Option D is wrong because Microsoft Defender for Cloud is for security posture.

Answer

Azure Information Protection (AIP) scanner

Answer

Microsoft Defender for Cloud

Answer

Azure Policy with built-in guest configuration

Question 10

Your team is troubleshooting slow query performance on a dedicated SQL pool in Azure Synapse Analytics. The query uses a hash-distributed fact table with 60 distributions. After reviewing the execution plan, you notice a high number of data moves. Which action would most likely reduce data movement?

Accepted Answer

Redistribute the fact table on the join column using hash distribution.. Option C is correct because aligning the distribution key of the fact table with the join column in a hash-distributed table keeps matching rows on the same distribution, minimizing data movement. Option A is wrong because changing to round-robin can increase data movement for joins. Option B is wrong because increasing distributions requires rebuilding the table and does not guarantee reduced movement. Option D is wrong because statistics help the optimizer but do not directly reduce movement.

Answer

Change the distribution type to round-robin.

Answer

Update statistics on all columns used in joins.

Answer

Increase the number of distributions to 120.

Question 11

You are designing a data ingestion pipeline for Azure Data Lake Storage Gen2 using Azure Databricks. The source is an on-premises SQL Server database with incremental changes captured via change data capture (CDC). The requirement is to ensure exactly-once semantics for each row while minimizing latency. Which approach should you recommend?

Accepted Answer

Use Spark Structured Streaming in Azure Databricks to read CDC changes and write to Delta Lake.. Option B is correct because Spark Structured Streaming with Delta Lake provides exactly-once semantics via transaction logs and checkpoints, and is designed for low-latency streaming. Option A is wrong because Azure Data Factory triggers are batch-oriented and not suitable for streaming low-latency. Option C is wrong because COPY INTO is for batch loads, not streaming. Option D is wrong because PolyBase is for bulk load, not streaming.

Answer

Use Azure Data Factory with a tumbling window trigger to copy data every 5 minutes.

Answer

Use PolyBase to create external tables and run T-SQL MERGE statements.

Answer

Use Azure Databricks Auto Loader with COPY INTO command.

Question 12

Your company uses Azure SQL Database and needs to audit all data modifications, including SELECT operations on sensitive columns, for compliance. Which feature should you enable?

Accepted Answer

Enable SQL auditing and configure to log SELECT operations.. Option A is correct because Azure SQL Database auditing captures database events including SELECT statements. Option B is wrong because vulnerability assessment scans for security issues but does not audit. Option C is wrong because TDE encrypts data at rest. Option D is wrong because Azure Defender for SQL provides security alerts but not detailed audit logs.

Answer

Enable Transparent Data Encryption (TDE).

Answer

Enable Azure Defender for SQL.

Answer

Enable SQL vulnerability assessment.

Question 13

You are monitoring an Azure Synapse Analytics dedicated SQL pool and notice that queries are experiencing excessive wait time due to concurrency slots being exhausted. What is the recommended approach to improve concurrency without increasing cost?

Accepted Answer

Classify queries using workload classification and assign lower importance to reduce concurrency slot usage.. Option C is correct because using workload classification and assigning a lower importance to background queries can allow critical queries to run while reducing slot consumption for non-critical ones. Option A is wrong because scaling up increases cost and may not be necessary. Option B is wrong because workload groups can limit resources but do not directly increase concurrency slots. Option D is wrong because changing distribution does not affect concurrency slots.

Answer

Create additional workload groups and assign queries to them.

Answer

Scale up the dedicated SQL pool to a higher service level.

Answer

Change the distribution type of tables to round-robin.

Question 14

Your organization uses Microsoft Purview to catalog data assets. You need to ensure that sensitive data such as credit card numbers are automatically detected and labeled. Which Purview feature should you configure?

Accepted Answer

Configure a scan rule set with built-in classification rules for sensitive data types.. Option A is correct because Microsoft Purview Data Map includes automated scanning and classification of sensitive data types. Option B is wrong because Data Catalog is for searching and governance, not scanning. Option C is wrong because Information Protection is for labeling in Microsoft 365, not data catalog. Option D is wrong because Azure Policy is for compliance rules, not scanning.

Answer

Create an Azure Policy to enforce tagging.

Answer

Enable the Data Catalog self-service search.

Answer

Enable Microsoft Information Protection for the data sources.

Question 15

You are monitoring an Azure Data Lake Storage Gen2 account using Azure Monitor. You need to be alerted when the number of storage account requests exceeds 20,000 per hour. What is the most efficient way to set up this alert?

Accepted Answer

Create a metric alert on the 'Transactions' metric with a threshold of 20,000 and aggregation granularity of 1 hour.. Option B is correct because the metric 'Transactions' is available at the storage account level and can be aggregated per hour. Option A is wrong because a Log Analytics query would be more complex and less efficient. Option C is wrong because Activity Log does not contain transaction counts. Option D is wrong because Azure Advisor does not provide custom alerting on metrics.

Answer

Create a Log Analytics workspace and write a KQL query to count requests.

Answer

Create an Activity Log alert for 'List Storage Account Keys' events.

Answer

Use Azure Advisor to recommend scaling.