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

Question 1

Your organization uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled. You need to implement a security strategy that allows users to read only specific folders within a container. Which authorization method should you use?

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

Accepted Answer

Access control lists (ACLs) on the folder. ACLs (Access Control Lists) in Azure Data Lake Storage Gen2 can be applied to individual folders, enabling granular read permissions. Option A is incorrect because a storage account shared key grants full access to the entire account. Option B is incorrect because Azure RBAC roles like Storage Blob Data Contributor apply at the container level, affecting all folders within. Option C is incorrect because shared access signatures (SAS) can be scoped to a container or a file, but not to a specific folder within a container.

Answer

Storage account shared key

Answer

Azure RBAC roles (e.g., Storage Blob Data Contributor) at the container level

Answer

Shared access signatures (SAS) with folder-level permissions

Question 2

You are monitoring Azure Stream Analytics job performance. The job is falling behind in processing real-time data. You notice that the SU (Streaming Unit) utilization is consistently at 90% or higher. What is the most appropriate action to improve throughput?

Accepted Answer

Increase the number of Streaming Units (SUs). When SU utilization is consistently at 90% or higher, the job is resource-constrained, which leads to backpressure and falling behind. The most direct way to improve throughput is to increase the number of Streaming Units (SUs), as this adds more compute capacity. Option A (changing output to use a partition scheme) can improve performance in some cases but is not the immediate step and may not resolve high SU utilization. Option B (reducing window duration) can cause data loss or inaccurate results, and does not address the resource bottleneck. Option D (decreasing event ordering tolerance) also risks data loss and is not a throughput improvement measure.

Answer

Change the output to use a partition scheme

Answer

Reduce the window duration in the query

Answer

Decrease the event ordering tolerance

Question 3

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 4

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 5

You are troubleshooting an Azure Databricks job that writes data to Azure Data Lake Storage Gen2. The job fails with '403 Forbidden' error. The Databricks workspace uses a managed identity (system-assigned) for authentication. What should you verify?

Accepted Answer

The managed identity has 'Storage Blob Data Contributor' role assigned to the storage account. The 403 Forbidden error indicates that authentication succeeded but authorization failed. For a managed identity to write data to Azure Data Lake Storage Gen2, it must be assigned the 'Storage Blob Data Contributor' RBAC role on the storage account. This role grants read, write, and delete permissions for blobs and directories. Without this role assignment, the managed identity cannot write data, resulting in a 403 error. Even if the storage account name is correct (A), the firewall is configured (B), or a private endpoint exists (C), the missing RBAC role assignment will cause the failure.

Answer

The storage account name is correct

Answer

The storage account firewall is configured to allow Azure services

Answer

A private endpoint is configured between Databricks and the storage account

Question 6

Which TWO actions should you take to secure sensitive data in Azure Data Lake Storage Gen2? (Choose two.)

Accepted Answer

Use access control lists (ACLs) to restrict access to specific directories. The correct answers are B and E. Option B: Using access control lists (ACLs) in Azure Data Lake Storage Gen2 allows granular permissions at the directory and file level, enabling you to restrict access to sensitive data. Option E: Enabling encryption at rest with customer-managed keys in Azure Key Vault provides an additional layer of security by allowing you to manage your own encryption keys. Option A is incorrect because public network access from all networks exposes data to potential threats; it should be restricted. Option C is incorrect as anonymous access should always be disabled for sensitive data. Option D is incorrect because soft delete should be enabled to protect against accidental data deletion or corruption.

Answer

Enable public network access from all networks for ease of use

Answer

Allow anonymous access to enable sharing

Answer

Disable soft delete to prevent accidental retention of deleted data

Question 7

Which THREE metrics should you monitor to optimize the performance of an Azure Synapse Analytics dedicated SQL pool? (Choose three.)

Accepted Answer

DWU (Data Warehouse Unit) usage percentage. Options B, C, and E are correct. Option B: DWU usage percentage indicates whether the pool is under- or over-provisioned, helping optimize performance. Option C: Active queries count monitors concurrency and workload. Option E: Memory grant waiters count shows queries waiting for memory, which can indicate memory pressure and affect query performance. Option A: Storage used percentage is about capacity, not performance. Option D: Buffer cache hit ratio is more relevant for SQL Server, not directly for Synapse dedicated SQL pool performance optimization.

Answer

Storage used percentage

Answer

Buffer cache hit ratio

Question 8

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 9

You are a data engineer at a financial services company. Your Azure Synapse Analytics dedicated SQL pool contains a fact table named 'Transactions' with 10 billion rows. The table is hash-distributed on 'AccountID' and partitioned by month. You notice that queries filtering on 'TransactionDate' (a date column) are performing slowly despite partition elimination. You also observe that the 'Transactions' table is frequently joined with a 'DimAccount' dimension table on 'AccountID'. You need to optimize query performance for the most common workload: monthly reports that aggregate transaction amounts by account for the last 12 months. Additionally, you need to ensure that the solution minimizes maintenance overhead. What should you do?

Accepted Answer

Create a clustered columnstore index on the table. Creating a clustered columnstore index on the 'Transactions' fact table provides column-level compression and significantly improves scan performance for aggregation queries, which is ideal for monthly reports aggregating transaction amounts. Option B is wrong because redistributing on TransactionDate using hash distribution would not improve join performance with DimAccount (which joins on AccountID) and may cause data skew if many rows share the same date. Option C is wrong because changing distribution to round-robin would eliminate collocation benefits for joins on AccountID, hurting query performance. Option D is wrong because table replication is designed for small dimension tables, not large fact tables like 'Transactions' with billions of rows.

Answer

Redistribute the table on TransactionDate using hash distribution

Answer

Change distribution to round-robin to evenly distribute data

Answer

Use table replication for the Transactions table

Question 10

You are monitoring an Azure Data Factory pipeline that runs hourly. The pipeline executes a stored procedure in an Azure SQL Database. Recently, you have observed that the pipeline occasionally fails with a 'Deadlock' error when the stored procedure runs. The Azure SQL Database is configured with the 'Read Committed Snapshot' isolation level enabled. You need to resolve the deadlock issue with minimal impact on performance. The stored procedure updates multiple tables in a single transaction and is critical for reporting. What should you do?

Accepted Answer

Add retry logic in the Data Factory pipeline for the stored procedure activity. Adding retry logic in the Data Factory pipeline allows the pipeline to automatically retry the stored procedure activity when a deadlock error occurs. Deadlocks are transient and often resolve on retry, minimizing impact on performance without changing the database isolation level or transaction integrity. Option A is wrong because using NOLOCK hints can lead to dirty reads and data inconsistency, which is unacceptable for a critical reporting procedure. Option B is wrong because removing the transaction would break the atomicity of the multiple table updates, potentially leaving data in an inconsistent state. Option D is wrong because disabling Read Committed Snapshot (RCSI) would likely increase blocking and contention, potentially making deadlocks worse or causing other performance issues.

Answer

Change the stored procedure to use NOLOCK hints

Answer

Remove the transaction from the stored procedure

Answer

Disable the 'Read Committed Snapshot' isolation level

Question 11

You are responsible for managing an Azure Data Lake Storage Gen2 account that stores parquet files for analytics. You need to implement a data retention policy that automatically deletes files older than 90 days in the 'logs' container. Additionally, you need to ensure that no data is lost due to accidental deletion; you want to be able to recover deleted files within 30 days. You also need to monitor the storage account for unusual access patterns. The solution must minimize administrative effort. What should you do?

Accepted Answer

Enable soft delete with a retention period of 30 days and configure a lifecycle management rule to delete blobs older than 90 days. Enabling soft delete with a 30-day retention period allows recovery of accidentally deleted files within that window. Configuring a lifecycle management rule to delete blobs older than 90 days automatically enforces the retention policy. This combination minimizes administrative effort. Option B is incorrect because Azure Policy cannot enforce retention or recovery at the blob level. Option C is incorrect because versioning is not equivalent to soft delete for ADLS Gen2 and Azure Policy does not manage retention. Option D is incorrect because manual deletion does not provide automatic enforcement and Azure Backup is not designed for storage-level lifecycle management.

Answer

Create an Azure Policy to enforce tag-based retention and use Azure Monitor to alert on access

Answer

Enable versioning and configure a retention policy in Azure Policy

Answer

Use Azure Backup for the storage account and manually delete old files

Question 12

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

Question 13

You have an Azure Data Factory pipeline that loads data from an on-premises SQL Server to Azure Synapse Analytics. The pipeline fails intermittently with network connectivity errors. You need to ensure reliable data transfer with minimal latency. Which solution should you recommend?

Accepted Answer

Deploy a self-hosted IR with high availability on two nodes. A self-hosted integration runtime (SHIR) is required for on-premises data sources. To improve reliability, a high-availability SHIR with two or more nodes provides redundancy and load balancing. Option A is incorrect because a site-to-site VPN provides network connectivity but does not directly improve the reliability of the data transfer pipeline; the IR component is still needed. Option C is incorrect because staging data in Blob Storage with sharded files is a performance optimization for parallel loading, but it does not address intermittent network connectivity issues. Option D is incorrect because the Azure Integration Runtime cannot access on-premises networks directly.

Answer

Set up a site-to-site VPN gateway

Answer

Stage data in Azure Blob Storage using sharded files

Answer

Use an Azure Integration Runtime instead

Question 14

Your Azure Synapse Analytics workspace uses serverless SQL pools for ad-hoc querying. Users report that queries are slow. You examine the execution plan and see that the query scans multiple partitions in the openrowset. What is the best way to improve performance?

Accepted Answer

Add a WHERE clause on the partition column. In serverless SQL pools, performance is improved by file pruning, which reduces the amount of data scanned. Adding a WHERE clause on the partition column allows the query engine to skip irrelevant partitions, thus reducing scan size. Option A is incorrect because MAXDOP controls parallelism, not data pruning. Option B is incorrect because materialized views are not supported in serverless SQL pools. Option C is incorrect because partitioning the underlying data helps, but the question asks for the best way to improve performance given the current query behavior; adding a WHERE clause on the partition column is the most direct and effective solution.

Answer

Increase the MAXDOP setting

Answer

Create materialized views on the external tables

Answer

Partition the underlying data by a frequently filtered column

Question 15

You are using Azure Purview to scan an Azure Data Lake Storage Gen2 account. After scanning, you notice that some files are not classified. What is the most likely reason?

Accepted Answer

The file types are not included in the scan rule set. Purview uses scan rule sets to determine which file types to scan and apply classifications. If the file type is not included in the scan rule set, those files will be skipped during scanning, leading to no classification. Option A is incorrect because if the storage account were not registered, no files would be scanned at all. Option B is incorrect because Parquet files are supported and can be classified. Option C is incorrect because if classification rules were disabled, no files would be classified, not just some.

Answer

The storage account is not registered in Purview

Answer

The files are in Parquet format

Answer

The classification rules are disabled