DP-203 Practice Test 25 — 15 Questions

Question 1

You are designing a data processing solution for a real-time dashboard. The source data is streaming from IoT devices at a rate of 10,000 events per second. Each event is under 1 KB. You need to store the raw data in a staging area before processing, and you must minimize storage costs. Which storage solution should you use?

Accepted Answer

Azure Blob Storage. Azure Blob Storage is cost-effective for large volumes of streaming data that will be processed later. Azure Data Lake Storage Gen2 is built on Blob Storage but adds hierarchical namespace, which is not required for simple staging. Azure SQL Database and Azure Cosmos DB are more expensive and suited for transactional or real-time query workloads.

Answer

Azure Data Lake Storage Gen2

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Question 2

You are a data engineer working for a logistics company. You have an existing Azure Data Factory pipeline that ingests data from a REST API to Azure Data Lake Storage Gen2. The API has rate limiting that can cause failures. You need to implement a solution that can handle rate limiting by retrying with exponential backoff. The pipeline should also log the number of retries for each API call. What should you do?

Accepted Answer

Configure the Copy activity with retry policy using exponential backoff by setting the retry count and retry interval. Enable diagnostic logs to capture retry details.. Option B is correct because Azure Data Factory's Copy activity has built-in support for retry with exponential backoff when you set the retry count and retry interval. You can also log retry attempts using diagnostic logs. Option A is wrong because Azure Logic Apps would be a separate service and add complexity. Option C is wrong because Azure Functions would require custom development. Option D is wrong because the Web activity does not have built-in retry with exponential backoff.

Answer

Use a Web activity with a Until loop to implement custom retry logic.

Answer

Use an Azure Function as a custom activity in Azure Data Factory to implement retry logic with exponential backoff.

Answer

Use Azure Logic Apps to call the API and then copy the response to Azure Data Lake Storage Gen2.

Question 3

Refer to the exhibit. You have a Mapping Data Flow in Azure Data Factory that reads JSON files from a folder partitioned by year/month/day. The source setting includes a row limit of 10,000. The sink writes Parquet files with a file pattern and partition columns. You notice that the job processes only the first 10,000 rows from the entire dataset instead of 10,000 rows per partition. How should you modify the data flow to achieve row limit per partition?

Accepted Answer

Remove the rowLimit from source settings. In the Optimize tab of the source, set 'Partition option' to 'Set partitioning' with 'Value' as 'year,month'. Then add a 'Top N' transformation after the source to limit rows per partition.. The row limit in the source settings applies to the entire dataset, not per partition. To achieve row limit per partition, you need to set the row limit in a derived column or filter transformation after partitioning. However, the correct approach is to remove the row limit from source and instead use a 'Top N' transformation after a 'Partition' transformation, but Data Flow does not have a Top N per partition. The practical solution is to use a 'Window' transformation to rank rows within each partition and then filter. Alternatively, you can use a 'SurrogateKey' to assign row numbers per partition. The correct answer here is to enable 'Sampling' within each partition using the 'Optimize' tab, but the closest option is to apply the row limit in a filter transformation after partitioning. Option A correctly suggests using the 'Optimize' tab to set 'Partition' and then apply a 'Top N' filter. Option B is wrong because row limit cannot be set per partition in source settings. Option C is wrong because Data Flows do not have a 'RowNumber' transformation. Option D is wrong because 'Distinct' does not limit rows.

Answer

Add a 'RowNumber' transformation after the source, then filter rows where row number <= 10000 per partition.

Answer

In the source settings, change rowLimit to a dynamic expression that uses partition variables to limit per partition.

Answer

Use a 'Distinct' transformation with 'All columns' to remove duplicate rows, which inherently limits rows.

Question 4

You have a streaming pipeline using Azure Stream Analytics that ingests data from Event Hubs and outputs to Azure Synapse Analytics. The job has a high watermark delay and is falling behind. You need to reduce the latency. Which action should you take?

Accepted Answer

Increase the number of Streaming Units (SUs) for the Stream Analytics job.. Increasing the number of Streaming Units (SUs) for the Stream Analytics job allocates more compute resources, reducing latency. Adding more Event Hubs partitions may improve throughput but not directly reduce latency if the job is already bottlenecked. Switching to reference data input does not help. Using Azure Functions for output may add overhead.

Answer

Add more partitions to the Event Hubs.

Answer

Replace the output with Azure Functions for each event.

Answer

Change the input to a reference data input.

Question 5

You have a Data Factory pipeline that runs a U-SQL script in Azure Data Lake Analytics. The script processes terabytes of data and outputs to a CSV file. The pipeline is failing with the error: 'The job failed with UserError: Script execution failed.' You need to troubleshoot the issue. Which approach should you take first?

Accepted Answer

Review the job logs in Azure Data Lake Analytics to identify the specific script error.. The most effective first step is to examine the detailed job logs in Data Lake Analytics, which contain the actual script error. Increasing parallelism or changing output format may not address the underlying script error. Moving to Azure Synapse is a larger architectural change.

Answer

Change the output format to Parquet to reduce file size.

Answer

Migrate the U-SQL script to Azure Synapse Spark pool.

Answer

Increase the degree of parallelism for the U-SQL job.

Question 6

You are designing a data processing solution for a large e-commerce company. The data includes sensitive customer information that must be encrypted at rest and in transit. The solution uses Azure Data Lake Storage Gen2 and Azure Databricks. Compliance requires that all access to the data is logged and monitored. Which combination of security features should you implement?

Accepted Answer

Enable Azure Storage encryption at rest, enforce HTTPS, and configure diagnostic settings to log all read/write operations.. Option B is correct because it covers encryption at rest (Azure Storage encryption), in transit (HTTPS), and logging (diagnostic settings). Option A is incorrect because RBAC alone does not log access details. Option C is incorrect because Azure Firewall is not for storage logging. Option D is incorrect because TDE is for SQL databases, not Data Lake.

Answer

Enable Azure RBAC for storage and use Azure AD authentication.

Answer

Use Azure Firewall to restrict access and enable Azure AD Authentication for Databricks.

Answer

Enable transparent data encryption (TDE) on the storage account and use service endpoints.

Question 7

You are developing a data processing pipeline for a gaming company that uses Azure Databricks. The pipeline processes game event data from Azure Event Hubs. You need to detect cheating patterns by analyzing events in real time. The solution must be able to handle high throughput and low latency. The output should be written to Azure Cosmos DB for real-time dashboards. Which approach should you use?

Accepted Answer

Use Azure Databricks Structured Streaming to read from Event Hubs, use Spark SQL and machine learning to detect cheating patterns, and write to Cosmos DB using the Azure Cosmos DB Spark connector.. Option B is correct because Azure Databricks Structured Streaming can handle high throughput and low latency, and it can write to Cosmos DB using the Azure Cosmos DB Spark connector. Option A is wrong because Azure Stream Analytics outputs to Cosmos DB but may not provide the same flexibility for complex pattern detection. Option C is wrong because Azure Data Factory is not for real-time processing. Option D is wrong because Azure Functions would be too complex for high throughput.

Answer

Use Azure Functions with Event Hubs trigger to process each event and write to Cosmos DB.

Answer

Use Azure Data Factory with continuous copy to load data into Cosmos DB and then use Azure Synapse Analytics to detect patterns.

Answer

Use Azure Stream Analytics to query the stream for cheating patterns and output to Cosmos DB.

Question 8

You are monitoring an Azure Data Factory pipeline that copies data from Azure Blob Storage to Azure SQL Database. The pipeline fails intermittently with the error: 'Operation on target SQL table failed: String or binary data would be truncated.' Which action should you take to resolve this issue?

Accepted Answer

Increase the length of the destination columns in the SQL table to accommodate the source data.. Option A is correct because the error indicates that source data length exceeds destination column length. Increasing column size resolves it. Option B is incorrect because the table already exists. Option C is incorrect because the error is not about connection. Option D is incorrect because the error is not about identity insert.

Answer

Set 'enable identity insert' to true.

Answer

Use auto-create table option in the copy activity.

Answer

Enable staging copy to use PolyBase.

Question 9

You are designing a data pipeline in Azure Data Factory to load incremental changes from an on-premises SQL Server database to Azure Synapse Analytics. The source table has over 1 billion rows and a datetime column 'LastUpdated' that is indexed but not always increasing. The requirement is to capture all changes with minimal latency and no missed rows. Which approach should you recommend?

Accepted Answer

Use SQL Server change tracking with a watermark table.. Option D is correct because change tracking reliably captures all changes including deletes and out-of-order updates, and the watermark mechanism ensures no rows are missed. Option A is incorrect because it can miss rows when timestamps are out of order. Option B is incorrect because it requires schema changes (triggers) and may impact source performance. Option C is incorrect because it requires costly schema changes.

Answer

Enable Change Data Capture (CDC) on the source table.

Answer

Use a custom staging table with triggers to capture changes.

Answer

Use a watermark column (LastUpdated) and query rows where LastUpdated > last run time.

Question 10

A data engineer is tasked with optimizing a Spark job in Azure Synapse Analytics that processes 10 TB of data daily. The job currently uses 50 executors with 4 cores each. The performance is bottlenecked by shuffle operations. The engineer wants to reduce shuffle data size. Which technique should be applied?

Accepted Answer

Use column pruning to select only required columns before shuffle.. Option B is correct because column pruning reduces the amount of data shuffled by eliminating unnecessary columns. Option A is incorrect because increasing parallelism may increase shuffle overhead. Option C is incorrect because coalescing reduces partitions but does not reduce shuffle data. Option D is incorrect because broadcasting is for small tables, not 10 TB.

Answer

Coalesce the number of partitions before the shuffle.

Answer

Increase the number of shuffle partitions.

Answer

Broadcast all tables to avoid shuffles.

Question 11

You are running a Python script in Azure Databricks that reads a CSV file from DBFS. The script runs successfully in an interactive notebook but fails when executed as a job with the error: 'Path does not exist: dbfs:/tmp/data.csv'. What is the most likely cause?

Accepted Answer

The file was uploaded to the workspace filesystem, not to DBFS.. Option C is correct because job clusters are ephemeral and may not have the file in DBFS if it was uploaded to the workspace files or requires mounting. Option A is incorrect because the cluster has access. Option B is incorrect because Python is supported. Option D is incorrect because the file exists in interactive but not in job context.

Answer

The job is using a different runtime that does not support Python.

Answer

The file is too large for DBFS.

Answer

The job cluster does not have permission to access DBFS.

Question 12

You are implementing a data pipeline in Azure Synapse Analytics that uses PolyBase to load data from Azure Data Lake Storage Gen2 into a dedicated SQL pool. The pipeline runs nightly and processes approximately 500 GB of data. You notice that the load operation is slow and frequently times out. What should you do to improve performance?

Accepted Answer

Increase the resource class for the loading user.. Option C is correct because increasing the resource class in dedicated SQL pool provides more resources (CPU, memory) to the PolyBase load operation, which directly addresses the timeout issue. Option A is wrong because columnstore indexes are relevant for query performance, not load speed. Option B is wrong because round-robin distribution can improve load speed, but the primary issue is resource allocation. Option D is wrong because the data type of the clustered index does not impact PolyBase load performance.

Answer

Create a columnstore index on the target table.

Answer

Use a clustered index on the target table.

Answer

Change the distribution of the target table to round-robin.

Question 13

A data engineer is building a pipeline in Azure Data Factory to load data from Azure Blob Storage to Azure Synapse Analytics using PolyBase. The pipeline must handle large files efficiently and minimize load time. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Split large files into multiple smaller files (e.g., 256 MB each).. Options B and D are correct. Splitting large files into multiple smaller files enables parallel loading with PolyBase. Using the 'RejectValue' option handles errors without failing the entire load. Option A is incorrect because Parquet is not supported by PolyBase to Synapse dedicated SQL pool. Option C is incorrect because PolyBase does not use staging tables. Option E is incorrect because copy activity with staging uses PolyBase but the question asks for PolyBase-specific optimizations.

Answer

Use a staging table to stage the data before loading.

Answer

Convert files to Parquet format for better compression.

Answer

Use the copy activity with staging enabled.

Question 14

Which TWO techniques should you use to secure sensitive data in Azure Synapse Analytics dedicated SQL pools when implementing column-level security?

Accepted Answer

Define dynamic data masking rules to obfuscate sensitive columns for unauthorized users.. Option B is correct because column-level security uses GRANT on specific columns. Option C is correct because dynamic data masking obfuscates data at query time. Option A is wrong because row-level security filters rows, not columns. Option D is wrong because Always Encrypted is for client-side encryption, not column-level security in Synapse. Option E is wrong because Transparent Data Encryption encrypts data at rest, not at the column level.

Answer

Enable Transparent Data Encryption (TDE) on the database.

Answer

Create a security policy with filter predicates to restrict access to specific columns.

Answer

Implement Always Encrypted to encrypt columns at the application level.

Question 15

Refer to the exhibit. A data engineer is reviewing a Mapping Data Flow in Azure Data Factory. The source reads 1000 rows from a CSV file. The sink writes to a Delta dataset with optimizeWrite enabled. The data flow currently has no transformations. The engineer runs the data flow and notices that the sink writes only 500 rows. What is the most likely cause?

Accepted Answer

The sink's partitionBy('hash', 1) setting causes some rows to be dropped due to partitioning logic.. Option A is correct because the sink script has 'partitionBy('hash', 1)' which forces the data to be repartitioned into a single partition. Since the source reads 1000 rows and the repartition may cause some rows to be dropped due to hash distribution logic in the sink. However, the key point is that the sink's partitioning overrides the source partitioning and may lead to data loss if there is a mismatch. Actually, the correct cause is that the sink is using hash partitioning on a column that may have many nulls or the partition column is not specified, causing skewed distribution. But the most direct answer is that the sink's partitionBy('hash', 1) creates a single partition and the hash function might drop rows that hash to a non-existent partition? No, hash with 1 partition should include all rows. Another possibility: the source has rowLimit 1000, but sink's partitionBy may cause duplicates? Let's think. The exhibit shows partitionBy('hash', 1). In ADF, when you use hash partitioning with 1 partition, all rows go to that partition, so no rows should be lost. However, the sink has allowSchemaDrift true, but no transformations. The discrepancy might be due to the source rowLimit being applied after reading? Actually rowLimit is applied at source. The most plausible cause is that the sink uses a Delta dataset and the Delta write operation may skip rows with nulls in partition columns? Option A mentions the sink's partitioning causes some rows to be dropped. Option B mentions allowSchemaDrift causing schema mismatch; but schema is matched. Option C mentions source rowLimit being applied before transformations; there are no transformations. Option D mentions missing transformation; not needed. The correct answer is A because in some cases, if the partition column is not in the source, the hash function might fail. But the script shows no partition column specified. In ADF, if you use partitionBy('hash', 1) without specifying a column, it uses a random hash? Actually it requires a column. The script is incomplete. Given the context, Option A is the best answer.

Answer

The source's rowLimit setting limits the number of rows read, but the sink writes only unique rows.

Answer

The sink's allowSchemaDrift setting prevents writing rows with additional columns.

Answer

The data flow has no transformations, so the sink cannot write data.