DP-203 Practice Test 28 — 15 Questions

Question 1

You are monitoring the performance of an Azure Data Factory pipeline that uses a Copy activity to load data into Azure Synapse Analytics. Which THREE metrics should you monitor to identify potential performance bottlenecks?

Accepted Answer

Throughput (data read/written per second).. Options A, C, and D are correct. Data read and written, copy activity duration, and throughput are direct indicators. Option B is wrong because pipeline run duration includes orchestration overhead. Option E is wrong because integration runtime CPU is not a standard metric in Azure Data Factory.

Answer

Integration runtime CPU utilization.

Answer

Pipeline run duration.

Question 2

You are designing a solution to monitor the performance of Azure Synapse Analytics dedicated SQL pools. You need to identify queries that are consuming more than 100 GB of memory and have been running for more than 30 minutes. Which DMV should you query?

Accepted Answer

sys.dm_pdw_exec_requests. Option B is correct because sys.dm_pdw_exec_requests provides query execution details including memory and duration. Option A is wrong because sys.dm_pdw_nodes is for node-level info. Option C is wrong because sys.dm_pdw_resource_waits shows waits, not memory usage. Option D is wrong because sys.dm_pdw_waits shows wait types, not memory consumption.

Answer

sys.dm_pdw_resource_waits

Answer

sys.dm_pdw_nodes

Answer

sys.dm_pdw_waits

Question 3

Which TWO actions can you take to optimize the performance of an Azure Synapse Analytics dedicated SQL pool? (Choose two.)

Accepted Answer

Replicate small dimension tables.. Options A and E are correct. A improves query performance by distributing data for parallelism. E reduces data movement by colocating joins on the same distribution. B is wrong because round-robin distributes data evenly but does not reduce data movement. C is wrong because heaps are not optimal for data warehousing. D is wrong because increasing DWU may help but is not always the best optimization.

Answer

Scale up the SQL pool to a higher DWU.

Answer

Use heap indexes for fact tables.

Answer

Use round-robin distribution for all large fact tables.

Question 4

You are using Azure Stream Analytics to process real-time data from an event hub and output to Azure Synapse Analytics. You need to ensure exactly-once delivery semantics to the output. What should you configure?

Accepted Answer

Set the output to 'Exactly Once' delivery mode.. Option A is correct because Azure Stream Analytics supports exactly-once delivery to Azure Synapse Analytics by enabling 'Exactly Once' output mode. Option B is wrong because checkpointing is for state management. Option C is wrong because event ordering does not guarantee exactly-once. Option D is wrong because the event hub is the input, not output.

Answer

Set the input to 'Exactly Once' consumption mode.

Answer

Configure event ordering and late arrival policies.

Answer

Enable checkpointing in the query.

Question 5

A company uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2. They notice that queries are slow and want to improve performance by reducing the amount of data read. What is the most effective strategy?

Accepted Answer

Partition the data by a frequently filtered column and use file elimination in queries.. Option A is correct because partitioning the data in the lake and using partition elimination reduces data read. Option B is wrong because OPENROWSET with CSV reads all files; it does not reduce data read. Option C is wrong because CETAS is for creating external tables, not for improving query performance directly. Option D is wrong because increasing the number of compute nodes is not possible in serverless; it's auto-scaling.

Answer

Increase the number of compute nodes in the serverless pool.

Answer

Use OPENROWSET with CSV format instead of Parquet.

Answer

Create external tables using CETAS and query them.

Question 6

You have an Azure Synapse Analytics dedicated SQL pool that stores sensitive financial data. You need to ensure that all queries accessing the data are audited and that any use of unapproved client tools is blocked. What should you implement?

Accepted Answer

Enable auditing and set an IP firewall rule that allows only approved IP ranges.. Option C is correct because Azure Synapse Analytics supports IP firewall rules and auditing. By enabling auditing and creating a firewall rule that only allows approved client IP ranges, you can both audit queries and block unapproved tools. Option A is wrong because Azure Private Link only provides network-level isolation, not auditing or blocking. Option B is wrong because Microsoft Defender for Cloud provides threat detection but not granular control over client tools. Option D is wrong because Azure role-based access control (RBAC) alone does not enforce client tool restrictions.

Answer

Enable Microsoft Defender for Cloud on the server.

Answer

Configure Azure Private Link for the dedicated SQL pool.

Answer

Assign Azure RBAC roles to users for the SQL pool.

Question 7

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

Accepted Answer

Queued queries. Options A, C, and D are correct. A: DWU usage indicates overall resource utilization. C: Queued queries indicate concurrency throttling. D: TempDB usage can cause performance degradation if high. Option B is wrong because storage space is not a performance metric (though important for capacity). Option E is wrong because login failures are security-related, not performance.

Answer

Storage space used

Answer

Login failures

Question 8

You need to secure data at rest for an Azure Data Lake Storage Gen2 account that contains sensitive financial data. Which configuration should you enable to ensure that data is encrypted using a customer-managed key stored in Azure Key Vault, and that access to the key is logged?

Accepted Answer

Configure Azure Storage encryption with customer-managed keys in Azure Key Vault and enable Key Vault logging. Option D is correct because Azure Storage encryption with customer-managed keys in Key Vault provides control and logging. Option A is wrong because Microsoft-managed keys are the default but do not provide customer control. Option B is wrong because client-side encryption requires managing keys on the client side. Option C is wrong because infrastructure encryption adds a second layer but does not use customer-managed keys.

Answer

Enable Azure Storage encryption with Microsoft-managed keys

Answer

Implement client-side encryption using Azure Key Vault

Answer

Enable infrastructure encryption for double encryption

Question 9

Your organization uses Azure Synapse Analytics serverless SQL pools to query data in Azure Data Lake Storage Gen2. You need to ensure that only authorized users can access the data via the serverless SQL endpoint, while minimizing administrative overhead. What should you use?

Accepted Answer

Enable Microsoft Entra ID authentication and grant users permissions via Azure RBAC on the storage account.. Option D is correct because Microsoft Entra ID (formerly Azure AD) pass-through authentication allows users to authenticate using their existing Azure AD identities without managing separate SQL logins, reducing overhead. Option A is wrong because storage account keys provide broad access and are not tied to user identities. Option B is wrong because SAS tokens are time-limited and require token management. Option C is wrong because managed identities are for service-to-service authentication, not individual user access.

Answer

Use managed identities for the serverless SQL pool.

Answer

Use storage account access keys for authentication.

Answer

Use shared access signatures (SAS) tokens generated for each user.

Question 10

Refer to the exhibit. You are reviewing the workload classifier configuration for an Azure Synapse Analytics dedicated SQL pool. You notice that the 'HeavyLoader' classifier has a queryExecutionTimeoutSeconds of 0. What is the implication of this setting?

Accepted Answer

Queries classified as 'HeavyLoader' will not have a timeout.. Option B is correct because a timeout of 0 means no timeout is applied. Option A is wrong because 0 does not mean immediate timeout. Option C is wrong because 0 does not mean infinite wait; it means no timeout. Option D is wrong because 0 is not invalid.

Answer

Queries classified as 'HeavyLoader' will wait indefinitely for resources.

Answer

The configuration is invalid; queryExecutionTimeoutSeconds must be greater than 0.

Answer

Queries classified as 'HeavyLoader' will timeout immediately.

Question 11

Which THREE security features are available in Azure Data Lake Storage Gen2 to protect data at rest and in transit? (Choose three.)

Accepted Answer

Azure Storage firewalls and virtual network rules. Options A, B, and D are correct. A ensures data is encrypted at rest using AES-256. B provides data integrity for data in transit. D provides network-level security. C is wrong because Azure Information Protection is for classification, not a built-in storage security feature. E is wrong because Azure Policy is for governance, not data protection.

Answer

Azure Information Protection

Answer

Azure Policy

Question 12

You are designing a data processing solution using Azure Databricks. You need to optimize costs while maintaining performance. Which TWO strategies should you implement? (Choose two.)

Accepted Answer

Use spot instances for non-critical, fault-tolerant workloads.. Options B and D are correct. B: Using spot instances reduces costs for fault-tolerant workloads. D: Autoscaling adjusts resources based on workload. Option A is wrong because all-purpose clusters are more expensive and not recommended for production jobs. Option C is wrong because using larger VMs may lead to underutilization and higher costs. Option E is wrong because Azure Databricks is not serverless in the traditional sense; you pay for running VMs.

Answer

Always choose the largest VM sizes to minimize cluster count.

Answer

Use all-purpose clusters for all workloads to avoid startup delays.

Answer

Use the serverless pricing tier to avoid paying for idle resources.

Question 13

Which TWO security features should be implemented to protect data in Azure Data Lake Storage Gen2?

Accepted Answer

Role-based access control (RBAC). Option A and D are correct: RBAC provides coarse-grained access control, and firewall rules restrict network access. Option B is wrong because Azure AD (Microsoft Entra ID) is used for authentication, but it's not a security feature specific to data protection. Option C is wrong because Azure Policy is for governance, not direct data protection. Option E is wrong because Azure Monitor is for monitoring, not security.

Answer

Azure Policy

Answer

Azure Active Directory (Microsoft Entra ID) integration

Answer

Azure Monitor alerts

Question 14

You are designing a data pipeline in Azure Data Factory that processes sensitive customer data. The pipeline must use a copy activity to move data from Azure Blob Storage to Azure Synapse Analytics. You need to ensure that data is encrypted in transit and at rest, and that the pipeline uses the most secure authentication method. Which authentication method should you use for the sink dataset?

Accepted Answer

Managed Identity. Option C is correct because Managed Identity provides the most secure authentication with no secrets stored, and it supports Azure Synapse Analytics. Option A is wrong because SQL authentication passes credentials in connection strings. Option B is wrong because service principal requires secret management. Option D is wrong because storage account key is a shared secret.

Answer

Storage account key

Answer

Service principal

Answer

SQL authentication

Question 15

Which TWO Azure features can be used to encrypt data at rest in Azure Blob Storage? (Choose two.)

Accepted Answer

Customer-managed keys in Azure Key Vault. Options A and C are correct. A: Storage Service Encryption (SSE) encrypts data at rest by default. C: Customer-managed keys in Azure Key Vault provide additional control over encryption keys. Option B is wrong because Azure Disk Encryption is for VMs. Option D is wrong because TLS is for data in transit. Option E is wrong because Azure Information Protection is for classification, not encryption at rest.

Answer

Azure Disk Encryption

Answer

Azure Information Protection

Answer

Transport Layer Security (TLS)