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

Question 1

Which THREE methods can you use to authenticate an Azure Data Factory pipeline to access Azure Data Lake Storage Gen2? (Choose THREE)

Accepted Answer

Use a service principal with a client secret or certificate.. Option A is correct because Azure Data Factory supports authenticating to ADLS Gen2 via a service principal (an Microsoft Entra ID app registration) using either a client secret or a certificate as the credential, which is a standard supported authentication method in linked services. Option B is correct because ADF linked services for ADLS Gen2 can authenticate using a shared access signature (SAS) token, which grants scoped, time-limited access to the storage account's resources. Option C is correct because a managed identity (system-assigned or user-assigned) assigned to the Data Factory can be granted RBAC roles on the storage account, allowing credential-free authentication to ADLS Gen2. Option D is not a supported authentication method for ADF linked services to ADLS Gen2, as user account/password authentication is not offered for this data store. Option E is not among the supported authentication types for ADLS Gen2 linked services in ADF, which rely on service principal, managed identity, or SAS rather than the storage account access key.

Answer

Use a user account with password.

Answer

Use the storage account access key.

Question 2

You have an Azure Data Factory pipeline defined as shown. The pipeline is failing because the preCopyScript truncates the staging table before each run, but the table is empty on the first run. What change would you make to ensure the pipeline works correctly?

Accepted Answer

Change the preCopyScript to: IF OBJECT_ID('dbo.Staging') IS NOT NULL TRUNCATE TABLE dbo.Staging.. The preCopyScript runs on every pipeline execution. On the first run, the staging table does not exist, so TRUNCATE TABLE dbo.Staging would fail. By adding a check with IF OBJECT_ID, the script only truncates if the table exists. This handles the first run gracefully. Option A is wrong because removing the preCopyScript would not truncate the table on subsequent runs, potentially causing duplicate data issues. Option B is wrong because increasing writeBatchSize does not solve the truncation error. Option D is wrong because setting recursive to false affects how the source dataset handles recursive file systems, not the truncation problem.

Answer

Remove the preCopyScript entirely.

Answer

Increase the writeBatchSize to 50000 to speed up the copy.

Answer

Set recursive to false in the source.

Question 3

You are a data engineer for a large e-commerce company. You have an Azure Synapse Analytics dedicated SQL pool that stores transactional data. The pool is currently at DWU1000c. You have a critical dashboard that runs a complex query every 5 minutes. The query scans a large fact table partitioned by date. The query performance is degrading over time as data accumulates. You need to improve performance without increasing DWUs or changing the dashboard query. You also need to minimize data movement overhead. You have the following options:

A. Create a columnstore index on the fact table with a partition alignment.
B. Create a materialized view that aggregates the data at the partition level.
C. Implement result-set caching and set the cache to expire every 5 minutes.
D. Redistribute the fact table using hash distribution on the date column.

Which option should you choose?

Accepted Answer

Implement result-set caching and set the cache to expire every 5 minutes.. Result-set caching stores the exact query results and can serve the dashboard query instantly if the underlying data has not changed. Since the query runs every 5 minutes, setting the cache expiration to 5 minutes ensures fresh data. Option A (columnstore index) is wrong because the table likely already has a columnstore index (the default in Synapse). Option B (materialized view) is wrong because materialized views require maintenance and may not match the exact query. Option D (hash distribution on date) is wrong because it can cause data skew and does not reduce scan overhead as effectively as caching.

Answer

Create a columnstore index on the fact table with a partition alignment.

Answer

Create a materialized view that aggregates the data at the partition level.

Answer

Redistribute the fact table using hash distribution on the date column.

Question 4

Your Azure Synapse Analytics dedicated SQL pool is experiencing performance degradation. Queries that previously completed in seconds now take minutes. You suspect memory pressure and concurrency issues. What should you first review to diagnose the problem?

Accepted Answer

sys.dm_pdw_exec_requests. Sys.dm_pdw_exec_requests provides a list of all currently executing or recent requests in the dedicated SQL pool, including their status, resource class, and elapsed time. This is the best first step to identify queries that may be causing memory pressure or concurrency issues. Option A (sys.dm_pdw_resource_waits) shows only resource waits but does not show the actual requests causing the waits. Option B (sys.dm_pdw_waits) shows wait information but lacks the direct request context needed for diagnosis. Option C (sys.dm_pdw_query_stats_xe) provides query execution statistics but not real-time active requests, so it is less suitable for immediate troubleshooting.

Answer

sys.dm_pdw_resource_waits

Answer

sys.dm_pdw_waits

Answer

sys.dm_pdw_query_stats_xe

Question 5

You are designing a data lake architecture using Azure Data Lake Storage Gen2. Sensitive customer data must be encrypted at rest using customer-managed keys stored in Azure Key Vault. Additionally, access must be audited at the file level. Which combination of features should you implement?

Accepted Answer

Customer-managed keys (CMK) in Azure Key Vault and Azure Storage analytics logs. Customer-managed keys (CMK) in Azure Key Vault provide encryption at rest with customer-controlled keys, and Azure Storage analytics logs (or diagnostic settings) capture file-level audit events such as read and write operations. Option A is incorrect because Microsoft Entra ID authentication controls access but does not provide encryption at rest. Option B is incorrect because Azure Information Protection is a classification and labeling solution, not an encryption mechanism for data at rest. Option D is incorrect because service-managed keys do not give customer control over encryption, and Azure Monitor alerts are for notifications, not audit logging.

Answer

Azure AD authentication and Azure Storage firewall

Answer

Azure Information Protection labels and Azure Policy

Answer

Service-managed keys and Azure Monitor alerts

Question 6

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 from the source (Azure Blob Storage) almost always indicates an authorization failure rather than a network block. Since the SQL firewall is already verified, the most likely cause is that the Data Factory managed identity has not been granted the 'Storage Blob Data Contributor' role on the storage account, so ADF cannot read the blobs. Granting that RBAC role resolves the 403.

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 7

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 8

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. In a serverless SQL pool, access is controlled through T-SQL security principals inside the database, not through workspace-level roles. Creating a database user mapped to the Microsoft Entra group and granting SELECT ON SCHEMA::sales to that group enforces least-privilege read access scoped precisely to the sales schema. This is the standard Azure Synapse pattern for Entra-based schema-level authorization.

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 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'. The first optimization for a large data transfer (50 GB) over a Self-Hosted Integration Runtime is to increase the 'Degree of copy parallelism' in the copy activity. This setting controls how many concurrent threads the SHIR uses to read from the source and write to the sink, directly improving throughput for large datasets. The default is 1, which severely limits performance for high-volume transfers.

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 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. Option B (DWU usage percentage) is correct because dedicated SQL pools are provisioned in Data Warehouse Units, and monitoring DWU usage percentage reveals whether the pool is under- or over-provisioned, directly guiding scaling decisions for performance optimization. Option C (Active queries count) is correct because tracking the number of concurrently running queries shows workload concurrency and contention, which is essential for diagnosing queuing, throttling, and resource saturation in a dedicated SQL pool. Option E (Memory grant waiters count) is correct because it indicates queries waiting for memory grants, a key signal of memory pressure and a common bottleneck that degrades dedicated SQL pool performance. Option A (Storage used percentage) is not among the correct answers because it reflects capacity consumption rather than query performance tuning. Option D (Buffer cache hit ratio) is not among the correct answers because dedicated SQL pool performance monitoring focuses on DWU, concurrency, and memory-grant metrics rather than this cache ratio.

Answer

Storage used percentage

Answer

Buffer cache hit ratio

Question 11

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

Accepted Answer

Azure Monitor diagnostic settings. Azure Monitor diagnostic settings (A) are correct because they can route storage account logs, including the StorageRead/StorageWrite/StorageDelete blob and file access logs, to a Log Analytics workspace, Event Hubs, or a storage account for auditing who accessed data. Azure Storage analytics logs (B) are also correct because they record successful and failed requests against Blob, Queue, Table, and File services with fields such as requestor IP, authentication type, and operation, providing a native auditing trail. Azure RBAC role assignments (C) only define permissions granted to principals and do not record actual data access events. Azure Policy (D) evaluates and enforces resource configuration compliance, not individual data-plane access. Microsoft Defender for Cloud (E) provides security posture and threat protection alerts but is not the storage access auditing feature itself.

Answer

Azure RBAC role assignments

Answer

Azure Policy

Answer

Microsoft Defender for Cloud

Question 12

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 13

You are designing access control for Azure Data Lake Storage Gen2. You need to allow a group of data scientists to read and write files in the 'processed' directory but prevent them from deleting files. Which authorization method should you use?

Accepted Answer

Assign the Storage Blob Data Contributor role at the container level and use ACLs to deny delete on the directory. Azure RBAC roles like Storage Blob Data Contributor grant read/write/delete permissions. To differentiate write and delete, you need ACLs that allow read/execute on the container and read/write on the directory, but not delete. Option A (Storage Blob Data Contributor) includes delete. Option B (Storage Blob Delegator) is not a built-in role for data operations. Option D (Storage Blob Data Reader) is read-only, and adding ACLs for write would be complex and not best practice. Option C correctly combines Storage Blob Data Contributor at the container level (which includes delete) but then uses ACLs to explicitly deny delete on the target directory, effectively preventing deletion while allowing read and write.

Answer

Assign the Storage Blob Data Contributor role at the container level

Answer

Assign the Storage Blob Delegator role at the container level

Answer

Assign the Storage Blob Data Reader role and use ACLs to grant write

Question 14

You need to monitor resource usage for an Azure Synapse Analytics dedicated SQL pool. Which dynamic management view (DMV) should you query to see current query execution status?

Accepted Answer

sys.dm_pdw_exec_requests. sys.dm_pdw_exec_requests provides information about all requests currently executing or recently executed in the dedicated SQL pool. Option A (sys.dm_pdw_nodes_db_size) is for database sizes, Option B (sys.dm_pdw_os_performance_counters) is for OS performance counters, Option D (sys.dm_pdw_nodes_resource_usage) is for resource usage.

Answer

sys.dm_pdw_nodes_db_size

Answer

sys.dm_pdw_os_performance_counters

Answer

sys.dm_pdw_nodes_resource_usage

Question 15

Your Azure Data Factory pipeline uses Copy Activity to ingest data from Azure Blob Storage to Azure Synapse Analytics. You need to minimize network latency and data transfer costs. Which data integration approach should you choose?

Accepted Answer

Use an Azure integration runtime in the same region as the data stores. Using Azure IR in the same region as the source and sink ensures data movement stays within Azure backbone, minimizing egress costs. Option A (staging) adds cost due to an extra hop. Option B (PolyBase) loads directly but still requires the Azure IR for orchestration. Option C (self-hosted IR) is designed for on-premises or private network scenarios, not optimized for cloud-to-cloud transfers in the same region.

Answer

Use a staging copy with Azure Blob as intermediate

Answer

Use PolyBase to load data directly from Blob Storage

Answer

Use a self-hosted integration runtime in the same region