DP-203 Practice Test 39 — 15 Questions

Question 1

Refer to the exhibit. You have an Azure Data Factory pipeline that copies trade data from Azure Blob Storage to Azure SQL Database. The pipeline runs every hour and truncates the destination table before each copy. However, users report that data is missing during the copy window. What is the most likely cause?

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

Accepted Answer

The preCopyScript truncates the table before the copy completes, causing a period with no data. The preCopyScript truncates the destination table before the copy operation begins. During the copy window, if the copy takes time or fails, the table remains empty, causing users to see missing data. Option A is incorrect because the output dataset configuration does not cause this issue. Option B is incorrect because writeBatchSize affects performance but not data loss. Option D is incorrect because recursive setting affects source file selection, not data loss.

Answer

The output dataset is not correctly configured

Answer

The writeBatchSize is too low, causing timeouts

Answer

The source dataset is set to recursive, which includes unwanted files

Question 2

You are optimizing the performance of an Azure Synapse Analytics dedicated SQL pool. Which THREE of the following actions will most likely improve query performance?

Accepted Answer

Create materialized views for frequently used aggregations. Materialized views precompute and store aggregated results, reducing the need to scan large tables on every query. Option C is correct because partitioning large fact tables on a date column enables partition elimination, which reduces the amount of data scanned for queries that filter on the partition key. Option D is correct because result-set caching allows repeated queries to return cached results directly without recomputation. Option B is incorrect: using a smaller distribution column does not necessarily improve data distribution and can cause data skew if the column has low cardinality. Option E is incorrect: converting all tables to heap tables removes indexes, which typically degrades query performance; heaps are primarily used for staging or loading data, not for performance optimization.

Answer

Use a smaller distribution column to improve data distribution

Answer

Convert all tables to heap tables to avoid index maintenance

Question 3

A data engineering team is designing a batch processing solution using Azure Databricks. The data is stored in Azure Data Lake Storage Gen2 (ADLS Gen2) and must be processed daily with minimal cost. The team needs to choose between using a Delta Lake table or a Parquet file format for the processed output. Which TWO factors should the team consider when making this decision?

Accepted Answer

Delta Lake provides time travel capabilities for accessing historical data versions.. Delta Lake's time travel feature allows querying previous versions of data using a timestamp or version number, which is essential for auditing, rollback, and reproducing historical reports. This capability is built on the transaction log that tracks every change, making it a key differentiator from plain Parquet files.

Answer

Parquet is easier to implement for schema evolution than Delta Lake.

Answer

Delta Lake reduces storage costs by automatically compressing data.

Answer

Parquet files are not natively supported by Azure Databricks.

Question 4

Your team is using Azure Data Factory to orchestrate a data pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline runs successfully during testing. However, after moving to production, you notice that the pipeline fails intermittently with connectivity errors. You need to ensure reliable data transfer. What should you implement?

Accepted Answer

Use Self-Hosted Integration Runtime. A Self-Hosted Integration Runtime provides a dedicated gateway for on-premises connectivity, enabling Azure Data Factory to access on-premises data sources reliably. Option A is wrong because Azure Integration Runtime cannot connect to on-premises networks. Option B is wrong because PolyBase is used for loading data into Azure Synapse Analytics, not for resolving connectivity issues. Option C is wrong because Azure VPN Gateway is a general VPN solution and does not directly address Data Factory connectivity to on-premises sources.

Answer

Use Azure Integration Runtime (IR)

Answer

Use PolyBase for data transfer

Answer

Configure Azure VPN Gateway

Question 5

You are designing a data processing solution for a financial services company. They need to process sensitive customer data in Azure Databricks while complying with GDPR. The data must be encrypted at rest and in transit, and access must be audited. You need to recommend a configuration that meets these requirements. Which combination of actions should you take?

Accepted Answer

Enable double encryption using Azure Key Vault with customer-managed keys and enable Azure Databricks audit logs.. Enabling double encryption (via Azure Disk Encryption and Azure Storage Service Encryption with customer-managed keys) ensures encryption at rest and in transit; Azure Key Vault manages the keys, and Azure Databricks audit logs capture access for auditing. Option A (SSE with platform-managed keys) provides encryption at rest but not with customer-managed keys and does not include audit logs. Option B (Azure Policy with GDPR blueprint) enforces compliance but does not directly implement encryption or auditing. Option C (Azure Security Center and Defender for Storage) provides monitoring but does not encrypt data or provide access auditing.

Answer

Enable Azure Storage Service Encryption (SSE) with platform-managed keys and configure Azure Databricks to use a VNet injection.

Answer

Apply an Azure Policy to require encryption and assign a built-in GDPR blueprint.

Answer

Use Azure Security Center to monitor for data exposure and enable Azure Defender for Storage.

Question 6

A company uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled. They need to restrict a specific application's access to only write files in a particular directory without being able to read or list files. Which type of permission should be assigned?

Accepted Answer

Configure an access control list (ACL) that grants execute and write permissions to the application's service principal.. Azure Data Lake Storage Gen2 uses POSIX-style ACLs that allow granular permissions. To restrict an application to write-only access without read or list, you grant execute (for directory traversal) and write permissions on the target directory via an ACL entry for the application's service principal. Option A is incorrect because firewall rules control network access, not data permissions. Option C is incorrect because a SAS with list permission would allow listing; write-only SAS is not easily achievable at directory level. Option D is incorrect because the Storage Blob Data Contributor role at the account level grants read, write, and delete permissions, not write-only.

Answer

Configure a firewall rule to allow only the application's IP address.

Answer

Generate a shared access signature (SAS) with write and list permissions.

Answer

Assign the Storage Blob Data Contributor role at the storage account level.

Question 7

Your company runs a critical data pipeline using Azure Data Factory (ADF) that ingests data from multiple sources into an Azure Synapse Analytics dedicated SQL pool. Recently, you have observed that the pipeline frequently fails with the error: 'Operation for target table failed: 'Cannot insert duplicate key row in object 'dbo.FactSales' with unique index 'PK_FactSales'. The duplicate key value is (20241001, 12345).'' The pipeline uses a Copy activity with a stored procedure sink that merges data into the fact table. The fact table has a clustered columnstore index and a unique constraint on (DateKey, ProductKey). You need to modify the pipeline to handle duplicates without losing data and without impacting performance significantly. What should you do?

Accepted Answer

Configure the Copy activity sink to use 'upsert' behavior with the unique key columns.. Azure Data Factory's Copy activity supports native upsert behavior when using a stored procedure sink, allowing it to handle duplicate key violations by updating existing rows instead of failing. By specifying the unique key columns (DateKey, ProductKey) in the upsert configuration, the pipeline can merge incoming data into the fact table without requiring manual staging or pre-cleanup, minimizing performance impact by leveraging the existing clustered columnstore index and unique constraint.

Answer

Change the distribution of the fact table to round-robin and remove the unique constraint.

Answer

Use a staging table and then execute a T-SQL MERGE statement to update or insert.

Answer

Add a pre-copy script to delete existing rows that match the incoming data before the copy.

Question 8

A company uses Azure Stream Analytics to process real-time data from IoT devices. They need to ensure that the output to Azure Synapse Analytics is optimized for high throughput and low latency. What should they configure in the Stream Analytics job?

Accepted Answer

Partition the output by a key and use a columnstore index in the target table.. Partitioning the output by a key enables parallel writes to Azure Synapse Analytics, significantly improving throughput, and using a columnstore index optimizes the target table for large data loads, reducing latency. Option A is incorrect because the question specifically requires Azure Synapse Analytics output, not Azure SQL Database. Option C is wrong because a single partition serializes writes, reducing throughput. Option D is incorrect because disabling batching increases the number of write transactions, leading to higher latency and reduced throughput.

Answer

Use Azure SQL Database output instead of Azure Synapse Analytics.

Answer

Use a single partition for the output to simplify processing.

Answer

Disable batching to reduce latency.

Question 9

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

Accepted Answer

Queued queries. To optimize the performance of an Azure Synapse Analytics dedicated SQL pool, monitor Queued queries (B) to detect concurrency throttling, DWU usage (C) to gauge overall resource utilization, and TempDB usage (E) as high usage can degrade performance. Storage space used (A) is a capacity metric, not a performance metric, and Login failures (D) are security-related, not performance-related.

Answer

Storage space used

Answer

Login failures

Question 10

Your organization uses Azure Purview for data governance. You need to ensure that sensitive data is properly classified and that access to it is monitored. Which THREE actions should you take? (Choose three.)

Accepted Answer

Create custom sensitivity labels in Microsoft Purview Information Protection and apply them to data sources.. The correct answers are C, D, and E. Creating custom sensitivity labels in Microsoft Purview Information Protection (option C) allows data to be tagged with sensitivity levels. Integrating Azure Purview with Microsoft Defender for Cloud Apps (option D) provides monitoring of access to sensitive data. Automated scanning in Azure Purview (option E) discovers and classifies data automatically. Option A is incorrect because Azure Policy is used for governance and compliance enforcement, not for data classification or monitoring. Option B is incorrect because Azure Sentinel is a security information and event management (SIEM) solution, not a data classification tool.

Answer

Define Azure Policy initiatives to enforce classification on all storage accounts.

Answer

Use Azure Sentinel to classify data as it is ingested.

Question 11

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. Managed Identity is the most secure authentication method because it uses Azure Active Directory (Azure AD) to authenticate to Azure Synapse Analytics without storing any credentials in the pipeline. This eliminates the risk of secret exposure and provides automatic credential rotation. SQL authentication (Option D) requires embedded credentials, service principal (Option C) requires secret management, and storage account key (Option B) is a shared secret that cannot be used to authenticate to Synapse as a sink.

Answer

Storage account key

Answer

Service principal

Answer

SQL authentication

Question 12

You are reviewing an Azure Data Factory pipeline JSON. Based on the exhibit, what will be the behavior of the Copy activity when copying files from a source folder that contains subfolders?

Accepted Answer

All files from all subfolders will be copied into a single folder in the sink.. The Copy activity is configured with recursive: true, which copies all files from all subfolders, and the sink is set to FlattenHierarchy, which places all files into a single folder without preserving the source folder structure. Option A is incorrect because staging is disabled. Option B is incorrect because recursive: true causes files from subfolders to be copied. Option C is incorrect because FlattenHierarchy does not preserve folder structure.

Answer

The copy will use staging to improve performance.

Answer

Only files in the root folder will be copied.

Answer

Files will be copied preserving the source folder structure.

Question 13

You need to monitor the performance of an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline runs on a self-hosted integration runtime. Which metric is most important to monitor to ensure the self-hosted IR is not a bottleneck?

Accepted Answer

Queue depth for the self-hosted IR. Queue depth for the self-hosted IR is the most important metric to monitor because it indicates how many activities are queued waiting for the IR to process. A high queue depth suggests the IR is overloaded and becoming a bottleneck. In contrast, pipeline duration (A) reflects total time but does not isolate IR performance. Active connections (C) measure concurrent loads but not queuing. Data read/written (D) track throughput, but a bottleneck may appear even with high throughput if the IR cannot keep up.

Answer

Pipeline duration metric

Answer

Number of active connections to the IR

Answer

Data read and data written metrics for the pipeline

Question 14

Which THREE considerations are important when designing a table distribution strategy for an Azure Synapse Analytics dedicated SQL pool? (Choose three.)

Accepted Answer

Align distribution keys on tables that are frequently joined together. Aligning distribution keys on tables that are frequently joined together ensures that the join columns are hash-distributed on the same key, enabling collocated joins. This avoids data movement across distributions during query execution, which significantly improves performance in Azure Synapse Analytics dedicated SQL pools.

Answer

Use round-robin distribution for large fact tables to distribute data evenly

Answer

Use replicated tables for large tables to avoid data movement

Question 15

You are designing a data pipeline using Azure Synapse Pipelines. The pipeline ingests data from multiple sources, performs transformations using a notebook, and loads the results into a dedicated SQL pool. You need to ensure that if the notebook fails, the entire pipeline stops and sends an alert. What is the most efficient way to configure this?

Accepted Answer

Add a 'Fail' activity after the notebook activity and connect the notebook's failure output to it. Configure the Fail activity to send an alert.. The most efficient: In Azure Synapse Pipelines (and Azure Data Factory), you can connect the failure output of the notebook activity to a 'Fail' activity. The Fail activity can be configured to send an alert (e.g., via webhook) and it stops the pipeline run immediately. Option A is incorrect because setting a wildcard error path would allow other activities to run; instead, you should explicitly route the failure to a Fail activity. Option B is incorrect because setting retry to 0 only prevents retries, but does not automatically stop the pipeline or send an alert. Option D is incorrect because the default behavior of a failed activity is to continue pipeline execution along the default error path, not to stop entirely; you must define an explicit failure path.

Answer

Set the notebook activity's error path to a webhook activity that sends an alert, and then set a wildcard error path for the pipeline.

Answer

Set the notebook activity's retry count to 0, and configure an alert on the pipeline run failure.

Answer

No configuration needed; by default, a failed activity stops the entire pipeline.