DP-203 Practice Test 41 — 15 Questions

Question 1

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.. Option C is correct because HTTPS ensures encryption in transit. Option A is wrong because encryption at rest does not cover transit. Option B is wrong because TLS is used for SQL connections, not for Data Lake. Option D is wrong because Azure Firewall does not provide encryption.

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 2

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. Options A and C are correct. Option A: Enabling encryption at rest using customer-managed keys ensures data is encrypted. Option C: Using ACLs provides granular access control. Option B is wrong because disabling soft delete reduces security. Option D is wrong because public network access should be disabled for security. Option E is wrong because anonymous access is a security risk.

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 3

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. Serverless SQL pools rely on file pruning. Partition elimination is achieved by filtering on partitioned columns in the query. Option A is wrong because materialized views are for dedicated pools. Option C is wrong because increasing MAXDOP may not help pruning. Option D is wrong because partitioning the file set helps but requires reorganizing data.

Answer

Increase the MAXDOP setting

Answer

Create materialized views on the external tables

Answer

Partition the underlying data by a frequently filtered 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. Option C is correct because sys.dm_pdw_exec_requests shows currently running queries and their resource consumption, helping identify memory pressure. Option A is wrong because it shows only resource waits, not the active queries. Option B is wrong because it shows only top resource consumers over time, not current state. Option D is wrong because it shows query execution details but not real-time resource waits.

Answer

sys.dm_pdw_resource_waits

Answer

sys.dm_pdw_waits

Answer

sys.dm_pdw_query_stats_xe

Question 5

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 A, C, and D are correct. Option A: DWU usage indicates if the pool is under- or over-provisioned. Option C: Memory grant waiters shows queries waiting for memory. Option D: Active queries help concurrency. Option B is wrong because cache hit ratio is for SQL Server. Option E is wrong because storage usage is about capacity, not performance.

Answer

Storage used percentage

Answer

Buffer cache hit ratio

Question 6

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 is for resource usage, Option B is for database sizes, Option D is for distribution states.

Answer

sys.dm_pdw_nodes_db_size

Answer

sys.dm_pdw_os_performance_counters

Answer

sys.dm_pdw_nodes_resource_usage

Question 7

Your organization has an Azure Synapse Analytics dedicated SQL pool that stores sensitive customer data. You need to ensure that only authorized users can access the data, and auditing must be enabled to track all access attempts. What should you do first?

Accepted Answer

Configure Microsoft Entra ID authentication and use RBAC to grant only necessary permissions.. Option B is correct because enabling Microsoft Entra ID authentication and assigning roles via RBAC is the first step to control access. Option A is wrong because enabling auditing does not restrict access. Option C is wrong because column-level security only restricts specific columns, not overall access. Option D is wrong because dynamic data masking obfuscates data but does not prevent access.

Answer

Implement column-level security to restrict sensitive columns.

Answer

Enable auditing on the SQL pool and configure a storage account for audit logs.

Answer

Apply dynamic data masking to the sensitive columns.

Question 8

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. Option B is correct because customer-managed keys (CMK) in Azure Key Vault provide encryption at rest with customer control, and Azure Storage analytics logs (or diagnostic settings) provide file-level audit logs. Option A is wrong because Azure AD authentication does not provide encryption. Option C is wrong because Azure Information Protection is a labeling solution, not encryption at rest. Option D is wrong because firewall rules do not encrypt data.

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 9

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 D are correct. Option A: Storage analytics logs capture successful and failed requests. Option D: Azure Monitor diagnostic settings can send logs to Log Analytics for auditing. Option B is wrong because Azure Policy enforces compliance, not auditing. Option C is wrong because Microsoft Defender for Cloud provides security alerts, not detailed access logs. Option E is wrong because Azure RBAC is for access control, not auditing.

Answer

Azure RBAC role assignments

Answer

Azure Policy

Answer

Microsoft Defender for Cloud

Question 10

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 classify data. If the file type is not included in the default scan rule set, it will be skipped. Option A is wrong because Purview can scan Parquet/CSV. Option B is wrong because registration is required. Option D is wrong because classification rules are defined in rule sets.

Answer

The storage account is not registered in Purview

Answer

The files are in Parquet format

Answer

The classification rules are disabled

Question 11

Refer to the exhibit. You are reviewing an Azure Policy definition that was created to audit diagnostic settings for Azure Data Lake Storage Gen2. The policy is assigned to a management group containing several storage accounts. You notice that the policy is not flagging storage accounts that lack diagnostic settings. What is the most likely cause?

Accepted Answer

The existence condition incorrectly checks for a specific log category 'StorageRead' instead of any diagnostic setting.. Option C is correct because the existence condition checks if a diagnostic setting of category 'StorageRead' exists, but the policy should check for any diagnostic setting. Option A is wrong because the policy type is custom, but that doesn't cause the issue. Option B is wrong because the effect is parameterized and default is AuditIfNotExists, which should work. Option D is wrong because the field check for type is correct.

Answer

The 'field' property in the 'if' condition should be 'type' equals 'Microsoft.Storage/storageAccounts/blobServices' for Data Lake.

Answer

The effect parameter is not assigned a value; the default 'AuditIfNotExists' is not applied.

Answer

The policy type is 'Custom', which requires special permissions to evaluate.

Question 12

You have a pipeline in Azure Data Factory that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline fails with a 'Connection closed' error after 1 hour. The data volume is 500 GB over a slow network. What is the most likely cause and solution?

Accepted Answer

The source SQL Server query command timeout is set to 1 hour; increase the command timeout or use staging copy.. The 'Connection closed' error after exactly 1 hour indicates the SQL Server source query command timeout (default 60 minutes) is being hit. When copying 500 GB over a slow network, the query execution exceeds this timeout, causing the connection to drop. Increasing the command timeout or using staging copy (which breaks the query into chunks) resolves this by allowing the query to run longer or reducing the per-query duration.

Answer

The network bandwidth is insufficient; use a faster network connection.

Answer

The copy activity is using a single parallel copy; increase the degree of copy parallelism.

Answer

The Self-Hosted Integration Runtime (SHIR) has a time-to-live (TTL) setting that is too short; extend the TTL.

Question 13

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline uses a self-hosted integration runtime and runs successfully during business hours. However, after a recent network security update, the pipeline fails with a connection error to the on-premises SQL Server. What is the most likely cause?

Accepted Answer

A firewall rule on the on-premises SQL Server is blocking the self-hosted integration runtime.. The self-hosted integration runtime (SHIR) connects to on-premises SQL Server via TCP port 1433 by default. A recent network security update likely added a firewall rule on the SQL Server or the on-premises network that blocks outbound or inbound traffic on this port, preventing the SHIR from establishing a connection. Since the pipeline ran successfully before the update, the most probable cause is a new firewall restriction targeting the SHIR's IP address or subnet.

Answer

The Azure Blob Storage account has been moved to a different subscription.

Answer

The self-hosted integration runtime node has been de-registered from Azure Data Factory.

Answer

The blob container has reached its maximum capacity.

Question 14

You are designing a data storage solution for a global IoT application that ingests millions of events per second. The data is write-heavy with occasional reads for real-time dashboards. Which Azure storage option and configuration would provide the lowest latency writes with high throughput?

Accepted Answer

Azure Cosmos DB with multi-region writes and eventual consistency. Azure Cosmos DB with multi-region writes and eventual consistency provides the lowest latency writes for a global IoT application because it allows each region to accept writes independently without cross-region coordination, and eventual consistency removes the need for quorum confirmations, reducing write latency. This configuration also offers high throughput by distributing write load across multiple regions, making it ideal for write-heavy, high-volume IoT scenarios where occasional reads for dashboards can tolerate stale data.

Answer

Azure Cosmos DB with single-region writes and strong consistency

Answer

Azure Data Lake Storage Gen2 with hierarchical namespace

Answer

Azure Blob Storage with hot tier and append blobs

Question 15

Which THREE of the following are valid methods to load data into Azure Synapse Analytics?

Accepted Answer

Azure Data Factory pipeline. Azure Data Factory (ADF) is a fully managed cloud-based ETL service that provides native connectors to Azure Synapse Analytics. It supports high-throughput data movement using the PolyBase engine or staged copy, making it a valid and recommended method for loading data at scale into Synapse dedicated SQL pools.

Answer

BULK INSERT with a data source from Azure Blob Storage

Answer

SQL Server Integration Services (SSIS) package