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

Question 1

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?

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

Accepted Answer

Redistribute the fact table on the join column using hash distribution.. Redistributing the fact table on the join column using hash distribution aligns the distribution key with the join column. This ensures that rows with the same join key value are stored on the same distribution, minimizing data movement during joins. Option A is wrong because round-robin distribution places rows randomly, which actually increases data movement for joins. Option B is wrong because while updating statistics helps the optimizer choose better plans, it does not directly reduce data movement. Option C is wrong because increasing the number of distributions does not change the alignment of the distribution key with the join column, so data movement remains high.

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 2

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.. Spark Structured Streaming in Azure Databricks, combined with Delta Lake, provides exactly-once semantics through checkpointing and transaction logs, and it is designed for low-latency streaming ingestion—ideal for CDC data. Option A is incorrect: Azure Data Factory with a tumbling window is a batch-based approach and cannot achieve exactly-once semantics for streaming data with low latency. Option B is incorrect: PolyBase is used for bulk loading data into Azure Synapse, not for streaming CDC ingestion. Option C is incorrect: Auto Loader and COPY INTO are designed for incremental batch loads of files, not for streaming row-level changes from a database.

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 3

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.. By classifying queries with workload classification and assigning lower importance to background or non-critical queries, those queries consume fewer concurrency slots, freeing slots for higher-priority queries without additional cost. Option A is incorrect because workload groups manage resources but do not directly reduce per-query concurrency slot usage; they allocate resources among groups but total concurrency slots remain fixed. Option C is incorrect because scaling up increases cost without necessarily improving concurrency efficiency. Option D is incorrect because table distribution type (e.g., round-robin) affects query performance and data movement, not concurrency slot consumption.

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 4

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.. Microsoft Purview can automatically detect sensitive data like credit card numbers by configuring a scan rule set that includes built-in classification rules. Option A is incorrect because Azure Policy enforces compliance rules but does not perform data scanning or classification. Option C is incorrect because Data Catalog self-service search is for discovering and searching data assets, not for automatic sensitive data detection. Option D is incorrect because Microsoft Information Protection is primarily for labeling and protection in Microsoft 365, not for scanning data in Purview.

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 5

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.. The 'Transactions' metric in Azure Monitor can be used to count the number of requests to the storage account, and you can set a metric alert with a threshold of 20,000 aggregated over an hour. This is the most efficient method as it directly uses the metric without needing complex queries. Option A is wrong because it requires creating a Log Analytics workspace and writing a KQL query, which is more complex and less efficient than a metric alert. Option B is wrong because Activity Log alerts are for management events like 'List Storage Account Keys', not for data transaction counts. Option D is wrong because Azure Advisor provides recommendations, not custom alerting on specific metric thresholds.

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.

Question 6

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.. Mapping Data Flows in Azure Data Factory provide built-in transformations for data masking, such as the Mask transformation, which can obfuscate PII columns during the data flow. Option A is incorrect because a copy activity with a query can only filter columns but does not support masking; it simply selects a subset of columns without transformation. Option B is incorrect because while a stored procedure activity can execute masking logic on the source, it requires additional setup and does not integrate seamlessly with Data Factory's native transformation capabilities. Option C is incorrect because staging with PolyBase is used to improve bulk copy performance, not for data 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 7

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.. The correct actions are B and E. Transparent Data Encryption (TDE) with service-managed keys (Option E) encrypts the entire dedicated SQL pool at rest by default. Infrastructure-level encryption using double encryption (Option B) adds a second layer of encryption at the infrastructure level, providing extra protection. Option A (Always Encrypted with secure enclaves) is for client-side encryption during queries, not at rest. Option C (column-level encryption with ENCRYPTBYPASSPHRASE) encrypts specific columns, not the entire pool. Option D (creating a customer-managed key in Azure Key Vault) is a prerequisite for TDE with customer-managed keys, but the action to enable encryption itself is TDE; Option E with service-managed keys already achieves at-rest encryption without requiring a customer-managed key.

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 8

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

Accepted Answer

Ingress. To evaluate the performance of an Azure Data Lake Storage Gen2 account, key metrics include Ingress (data written to the account), Success E2E Latency (end-to-end latency of successful requests), and Blob Count (total number of blobs/objects). Option A (Ingress) is correct because it measures the throughput of data entering the storage account. Option C (Success E2E Latency) is correct as it reflects the overall response time for successful operations. Option E (Blob Count) is correct because it helps assess scalability and cost. Options B (CPU Usage) and D (Available Storage Capacity) are not metrics for storage accounts; CPU is for compute resources, and capacity is a property, not a performance metric.

Answer

CPU Usage

Answer

Available Storage Capacity

Question 9

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. The correct answers are D and E. Azure Monitor with Log Analytics collects and analyzes telemetry data, including query performance metrics from Azure Synapse Analytics. Dynamic Management Views (DMVs) provide detailed insights into query execution statistics, such as wait types and resource usage. Option A (SQL Data Sync) is for data synchronization across databases, not monitoring. Option B (Azure Policy) is for governance and compliance, not performance analysis. Option C (Azure Advisor) offers optimization recommendations but not real-time monitoring.

Answer

SQL Data Sync

Answer

Azure Policy

Answer

Azure Advisor

Question 10

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.. The most secure approach is to use Azure RBAC to grant specific storage account permissions to the Azure Databricks managed identity. This follows the principle of least privilege by granting only the required permissions to the identity used by the spark jobs. Option A is less secure because storing keys in Key Vault still requires managing secrets and the keys provide broad access. Option B (shared access keys) also provides broad access and is not recommended. Option D (disabling public network access) controls network-level access but does not prevent access from authorized networks or services that are allowed; it does not specifically restrict spark job access within Databricks. Therefore, option C is the most secure and best practice.

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 11

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.. It moves blobs to the cool tier after 30 days (saving costs for infrequent access) and then to the archive tier after 1 year (maximizing savings for long-term retention), meeting both the access pattern and 7-year retention requirement. Option A is wrong because moving directly to archive after 30 days makes data unavailable for immediate access and incurs high retrieval costs if needed. Option B is wrong because deleting after 30 days violates the 7-year retention policy. Option C is wrong because the premium tier is designed for high performance, not cost savings on cold data.

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 12

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.. Transparent Data Encryption (TDE) encrypts data at rest in the dedicated SQL pool, and enforcing TLS 1.2 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 C 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 13

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.. 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 D 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 14

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.. Azure Policy can enforce a deny effect on data access requests originating from unapproved locations, effectively preventing data exfiltration even if the user has Contributor role. This is a preventive control, not just monitoring. Option A (diagnostic settings) is detective only. Option B (removing Contributor role) might reduce permissions but does not prevent the insider from using their current role. Option C (network firewall rules) can be bypassed if the insider accesses from within the trusted network. Therefore, Azure Policy with location-based deny is the most effective prevention.

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 15

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.. Staging the copy to Azure Blob Storage and then using a stored procedure for bulk insert reduces the direct load on Azure SQL Database, lowering DTU consumption during the copy. Option B is incorrect because PolyBase is designed for Azure Synapse Analytics, not Azure SQL Database. Option C (parallel copy) can increase throughput but also increases DTU usage, not reducing it. Option D (increasing DTU) is a scaling measure, not a reduction technique.

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.