DP-203 Practice Test 40 — 15 Questions

Question 1

You are optimizing an Azure Synapse Analytics dedicated SQL pool. The workload includes large fact tables and dimension tables. You need to improve query performance for star join queries. Which TWO actions should you take?

Accepted Answer

Use replicated distribution on dimension tables.. Options B and D are correct. Using hash-distributed fact tables on the join key and replicated dimension tables are best practices for star joins. Option A is wrong because round-robin is for staging tables, not for fact tables. Option C is wrong because clustered columnstore index is default and recommended, not heap. Option E is wrong because distribution on dimension tables should be replicated, not hash.

Answer

Use round-robin distribution on fact tables.

Answer

Use hash distribution on dimension tables.

Answer

Use heap table structure for fact tables.

Question 2

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.. Option D is correct because 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 C 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 3

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

Accepted Answer

Use a service principal with a client secret or certificate.. Correct answers: A and C. A: Managed identity is a recommended method for Azure services. C: Service principal is another common method. B is wrong because storage account key is not recommended for production due to security risks. D is wrong because SAS tokens are less secure and have expiration. E is wrong because user password is not supported.

Answer

Use a shared access signature (SAS) token.

Answer

Use a user account with password.

Answer

Use the storage account access key.

Question 4

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. Option C is correct because you create a database user mapped to the Microsoft Entra group, then grant SELECT on the schema to that user. Option A is wrong because RBAC at workspace level is too broad. Option B is wrong because it grants permissions to all users. Option D is wrong because user mapping alone does not grant permissions.

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 5

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 indicate that queries are waiting for resources. This is typically due to concurrency throttling when the number of concurrent queries exceeds the capacity of the current Service Level Objective (SLO). Option A is wrong because data skew would cause uneven distribution, not necessarily queue waits. Option C is wrong because a single large query would show high execution time, not queue wait. Option D is wrong because stats being out of date would affect execution plans, not queue waits.

Answer

Outdated statistics

Answer

A single long-running query blocking others

Answer

Data skew in distribution

Question 6

You need to secure access to an Azure Storage account that hosts sensitive data. Which TWO methods provide encryption in transit?

Accepted Answer

Enforce HTTPS for REST API calls. HTTPS and SMB over encryption ensure data is encrypted in transit. Option C (SSE) is at rest. Option D (Azure Disk Encryption) is for VMs. Option E (IPSec) is not supported by Azure Storage directly.

Answer

Enable Azure Storage Service Encryption (SSE)

Answer

Enable Azure Disk Encryption on client VMs

Answer

Configure an IPsec policy between clients and storage

Question 7

You are monitoring an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. You notice frequent failures due to transient network errors. Which TWO actions should you take to improve reliability?

Accepted Answer

Enable fault tolerance in the copy activity to skip incompatible rows.. Options B and C are correct. Retry policy and fault tolerance handle transient errors. Option A is wrong because staging is for bulk copies, not for transient errors. Option D is wrong because self-hosted IR is for connectivity, not for retry. Option E is wrong because ParallelCopy increases throughput but not reliability.

Answer

Deploy a self-hosted integration runtime on a VM in Azure.

Answer

Use staged copy with Azure Data Lake as intermediate storage.

Answer

Increase the degree of copy parallelism.

Question 8

Which THREE measures should you implement to monitor and optimize the performance of Azure Data Lake Storage Gen2?

Accepted Answer

Enable Storage Insights to monitor capacity and transactions.. Option A is correct: Metrics like 'Ingress' help identify bottlenecks. Option C is correct: Diagnostic logs provide request details for troubleshooting. Option D is correct: Azure Storage Analytics offers latency and throughput data. Option B is incorrect: Tiering is for cost optimization, not performance monitoring. Option E is incorrect: NSG flow logs are for network security, not storage performance.

Answer

Enable Network Security Group flow logs for the storage account subnet.

Answer

Configure lifecycle management policies to move cold data to archive tier.

Question 9

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.. Option A is correct because diagnostic settings log activity to a Log Analytics workspace, which can be used for monitoring and alerting on suspicious data access, but the question asks to prevent exfiltration. Actually, the best prevention is to use Azure Policy to restrict data access. Wait, re-reading: The question is about preventing exfiltration. The correct answer is to use Azure Policy with deny effect to restrict access to certain data. But among options, Option A (diagnostic settings) is monitoring, not prevention. Option B (Azure Policy with deny effect) is prevention. Option C (RBAC with read-only) could help but insider has Contributor. Option D (firewall rules) can restrict network access. The best is to use Azure Policy to enforce data access rules. Given the options, Option B is most relevant. However, I need to ensure correctness. Let me re-evaluate: The insider has Contributor role, which allows write/read. To prevent exfiltration, you can use Azure Policy to deny access to specific data or use service endpoints. Option B is correct because Azure Policy can enforce that data cannot be accessed from outside the corporate network. Option A is wrong because it only monitors. Option C is wrong because the user already has Contributor. Option D is wrong because firewall rules can be bypassed by the insider if they are inside the network. So correct is B.

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 10

You are reviewing the ARM template snippet for an Azure Data Lake Storage Gen2 account. The template fails to deploy with an error that the encryption key cannot be accessed. What is the most likely cause?

Accepted Answer

The Data Lake Storage account does not have Get and Wrap Key permissions on the key vault.. Option C is correct because the Data Lake Storage account's managed identity (or the user deploying) needs access to the Key Vault to retrieve the key. If the identity does not have 'Get' and 'Wrap Key' permissions, it will fail. Option A is wrong because the URI is valid. Option B is wrong because the key version exists. Option D is wrong because soft-delete is not required for access, though it is recommended.

Answer

The key vault does not have soft-delete enabled.

Answer

The key name or version is incorrect.

Answer

The key vault URI is incorrectly formatted.

Question 11

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'. Option D is correct because enabling parallel copy (degree of copy parallelism) allows multiple threads to read and write data concurrently, improving throughput for large datasets. Option A is wrong because it reduces resource usage. Option B is wrong because staging is for other scenarios. Option C is wrong because it affects scheduling, not performance.

Answer

Enable staging copy via Azure Blob Storage

Answer

Increase the 'Activity retry' count

Answer

Reduce the 'Data Integration Unit' (DIU) setting

Question 12

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 wrong because Azure IR cannot access on-premises networks. Option B is wrong because Sharded staging doesn't address connectivity. Option D is wrong because VPN Gateway alone doesn't improve the IR reliability.

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 13

You are designing a data processing solution using Azure Synapse Analytics serverless SQL pool. The solution will query data stored in Parquet files in Azure Data Lake Storage Gen2. You need to ensure that the queries are optimized for performance. Which action should you take?

Accepted Answer

Partition the Parquet files by date and use partition pruning in the query.. Option B is correct because partitioning the data by a commonly filtered column, such as date, allows the serverless SQL pool to use partition elimination, reducing the amount of data scanned. Option A is wrong because increasing MAXDOP may not help and can cause resource contention. Option C is wrong because usingOPENROWSET with CSV is less efficient than Parquet. Option D is wrong because materialized views are not supported in serverless SQL pool.

Answer

Increase the MAXDOP setting in the query.

Answer

Convert the Parquet files to CSV format for faster parsing.

Answer

Create materialized views on the external tables.

Question 14

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. Option B is correct because a 403 error typically indicates that the managed identity used by Data Factory does not have the correct RBAC role (e.g., Storage Blob Data Contributor) on the storage account. Option A is wrong because the error is 403, not 400. Option C is wrong because the SQL Database firewall is open. Option D is wrong because there is no indication of sink throttling.

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 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. Option B (self-hosted IR) is for on-premises. Option C (polybase) is for loading into SQL DW but still uses IR.

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