DP-203 Practice Test 25 — 15 Questions

Question 1

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?

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

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 2

Which TWO methods can you use to optimize the cost of storing data in Azure Data Lake Storage Gen2?

Accepted Answer

Configure lifecycle management policies to move older data to the cool or archive tier.. Options B and D are correct. Option B: Configure lifecycle management policies to automatically move data to cooler tiers (cool or archive) based on age, reducing storage costs. Option D: Azure Blob Storage access tiers (hot, cool, archive) allow you to choose the appropriate tier for data based on access frequency, optimizing cost. Option A is incorrect because using customer-managed keys for encryption does not affect storage costs; encryption is free. Option C is incorrect: enabling soft delete adds storage overhead for deleted versions, increasing cost. Option E is incorrect: geo-redundant storage (GRS) replicates data to another region, increasing storage cost.

Answer

Use customer-managed keys for encryption.

Answer

Enable soft delete for blobs.

Answer

Enable geo-redundant storage (GRS) for disaster recovery.

Question 3

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.. To reduce shuffle writes in a Databricks batch job using Delta Lake, you should partition the Delta table by the most frequently used filter column (B) and use the OPTIMIZE command with Z-Ordering on join keys (E). Partitioning limits data shuffling by filtering out irrelevant partitions, and Z-Ordering colocates related data, reducing shuffle size during joins. Option A (auto-optimize) helps compaction but does not directly reduce shuffle writes. Option C (broadcast hash join) only helps if one table is small, not for large tables. Option D (increasing shuffle partitions) often increases shuffle writes due to more tasks.

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 4

You are monitoring an Azure Synapse Analytics dedicated SQL pool using dynamic management views (DMVs). You want to identify queries that are experiencing excessive memory grants causing concurrency issues. Which DMV should you query?

Accepted Answer

sys.dm_pdw_exec_requests. (sys.dm_pdw_exec_requests) is correct because it contains the total_request_memory_granted column, which can be used to identify queries with excessive memory grants contributing to concurrency issues. Option A (sys.dm_pdw_waits) only shows wait types, not memory grants. Option C (sys.dm_pdw_resource_waits) shows resource wait statistics but not actual grant amounts. Option D (sys.dm_pdw_workload_management_workload_groups_details) provides workload group configuration and metrics, not per-query memory grant details.

Answer

sys.dm_pdw_waits

Answer

sys.dm_pdw_resource_waits

Answer

sys.dm_pdw_workload_management_workload_groups_details

Question 5

Which THREE of the following are required to implement column-level security in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

A GRANT statement on specific columns to users or roles. Column-level security in Azure Synapse Analytics dedicated SQL pool is implemented using GRANT statements on specific columns. By granting SELECT on only certain columns to a user or role, you restrict access to sensitive data at the column level. While a VIEW can help simplify permission management, it is not mandatory. A database user does not require a default schema specifically for column-level security; it is a general database requirement. Therefore, only Option A is strictly required.

Answer

A VIEW that selects only the allowed columns

Answer

A DENY statement on specific columns to users or roles

Answer

A row-level security policy must be in place

Answer

The database user must have a default schema

Question 6

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 7

You have an Azure Synapse Analytics dedicated SQL pool. You notice that some queries are taking longer than expected due to excessive data movement operations. You need to minimize data movement without changing the distribution columns. Which table design approach should you recommend?

Accepted Answer

Use replicated tables for small dimension tables. Replicated tables are recommended for small dimension tables because they are copied to all compute nodes, avoiding data movement during joins. This reduces excessive data movement without changing distribution columns. Option B is incorrect because round-robin distribution distributes data evenly but does not reduce data movement for joins; it is typically used for staging tables. Option C is incorrect because using hash distribution for all tables can lead to data movement when joining on different columns, and it is not a one-size-fits-all solution. Option D is incorrect because partitioning alone does not reduce data movement; it is used for data management and pruning, not for minimizing shuffle operations.

Answer

Use round-robin distribution for dimension tables

Answer

Use hash distribution for all tables

Answer

Use partitioning on join columns

Question 8

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 9

Your Azure Data Factory pipeline uses a Self-Hosted Integration Runtime (SHIR) to copy data from an on-premises SQL Server to Azure Blob Storage. The copy activity is failing with a timeout error after 30 minutes. The data volume is 50 GB. You need to optimize the data transfer performance. Which configuration change should you make first?

Accepted Answer

Increase the 'Degree of copy parallelism'. Increasing the 'Degree of copy parallelism' allows multiple threads to read from the source and write to the sink concurrently, significantly improving throughput for large datasets like 50 GB. Option B is incorrect because staging copy is used for scenarios like cross-cloud transfers or to leverage intermediate storage for performance, but it adds overhead and is not the first optimization for direct on-premises to Azure Blob copy. Option C is incorrect because increasing activity retry count only handles transient failures, not performance issues. Option D is incorrect because reducing Data Integration Units would decrease the resources available for the copy activity, worsening performance.

Answer

Enable staging copy via Azure Blob Storage

Answer

Increase the 'Activity retry' count

Answer

Reduce the 'Data Integration Unit' (DIU) setting

Question 10

Which TWO features can be used to audit access to data in Azure Storage? (Choose two.)

Accepted Answer

Azure Monitor diagnostic settings. Options A and B are correct. Option A: Azure Monitor diagnostic settings can be configured to send resource logs (including storage audit logs) to Log Analytics, Storage, or Event Hubs for auditing. Option B: Storage Analytics logs provide detailed information about successful and failed requests to a storage account, which can be used for auditing. Option C is incorrect because Azure RBAC role assignments are for access control, not auditing. Option D is incorrect because Azure Policy enforces compliance rules, not auditing. Option E is incorrect because Microsoft Defender for Cloud provides security alerts and threat protection, but not detailed access auditing.

Answer

Azure RBAC role assignments

Answer

Azure Policy

Answer

Microsoft Defender for Cloud

Question 11

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 12

You are configuring security for an Azure Synapse Analytics workspace. You need to ensure that only users in the 'DataScientists' Microsoft Entra group can read data from the 'sales' schema in the serverless SQL pool. What should you configure?

Accepted Answer

Create a database user mapped to the Microsoft Entra group and grant SELECT ON SCHEMA::sales to the group. You create a database user mapped to the Microsoft Entra group in the serverless SQL pool database, then grant SELECT ON SCHEMA::sales to that user. This ensures only members of the 'DataScientists' group have permission to read data from the 'sales' schema. Option A is wrong because a server-level login for the group does not grant specific schema-level permissions; the 'public' role provides broad access. Option C is wrong because the 'Synapse SQL Administrator' role grants too many permissions (admin-level access) and is not schema-specific. Option D is wrong because a contained database user with password does not leverage Microsoft Entra group membership, and the 'db_datareader' role grants read access to all tables, not just the 'sales' schema.

Answer

Create a server-level login for the group and assign it to the 'public' role

Answer

Assign the 'Synapse SQL Administrator' role to the group at workspace level

Answer

Create a contained database user with password and assign it to the 'db_datareader' role

Question 13

Your Azure Data Factory pipeline is failing with the error: 'Operation on target Copy data1 failed: The remote server returned an error: (403) Forbidden.' The source is Azure Blob Storage and the sink is Azure SQL Database. You have verified the SQL Database firewall rules allow Azure services. What is the most likely cause?

Accepted Answer

The Data Factory managed identity lacks Storage Blob Data Contributor role on the storage account. A 403 Forbidden error when reading from Azure Blob Storage typically indicates that the Data Factory does not have permission to access the storage. The managed identity used by Data Factory must have the Storage Blob Data Contributor role (or equivalent) on the storage account. Option A is incorrect because the error is 403, not a connectivity issue. Option B is incorrect because a private endpoint alone does not cause a 403; it would affect network connectivity. Option D is incorrect because throttling from Azure SQL Database would produce a different error (e.g., 429 Too Many Requests).

Answer

The SQL Database firewall is blocking the Data Factory IP

Answer

The storage account is behind a private endpoint

Answer

The SQL Database is throttling the write operations

Question 14

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 15

Your Azure Synapse Analytics dedicated SQL pool is experiencing performance degradation. Queries that previously completed in seconds now take minutes. You notice high queue wait times in sys.dm_pdw_exec_requests. What is the most likely cause?

Accepted Answer

Concurrency throttling due to insufficient resources. High queue wait times in sys.dm_pdw_exec_requests indicate that queries are waiting for resources, typically due to concurrency throttling when the number of concurrent queries exceeds the capacity of the current Service Level Objective (SLO). Option A (outdated statistics) can cause suboptimal execution plans but not necessarily queue waits. Option B (a single long-running query) would show high execution time for that query, not queue waits for others. Option D (data skew) leads to uneven distribution and slow data movement, not directly to queue waits.

Answer

Outdated statistics

Answer

A single long-running query blocking others

Answer

Data skew in distribution