DP-203 Develop data processing Practice Test 17 — 15 Questions

Question 1

You are tuning a Spark job in Azure Synapse Analytics that processes large Parquet files. The job currently takes too long due to data skew. Which three actions can improve performance? (Choose three.)

Accepted Answer

Use bucketing on the join key when writing intermediate data.. Options A, B, and E are correct. Repartitioning redistributes data. Salting adds a random key to break skew. Bucketing pre-partitions data. Option C is wrong because coalesce reduces partitions but does not address skew. Option D is wrong because increasing executor memory may help but does not directly solve skew.

Answer

Use coalesce to reduce the number of partitions.

Answer

Increase executor memory to handle larger partitions.

Question 2

You are designing a data processing solution in Azure Data Factory that uses mapping data flows. You need to perform type conversions on incoming data. Which three transformations can be used to change data types? (Choose three.)

Accepted Answer

Derived Column. Options A, B, and C are correct. Derived Column can change types via expressions. Select can cast columns during projection. Assert can check and convert types. Option D is wrong because Sort only orders data. Option E is wrong because Conditional Split routes rows based on conditions, not type conversion.

Answer

Conditional Split

Answer

Sort

Question 3

You are a data engineer for a global e-commerce company. You need to design a data processing solution using Azure Databricks that processes real-time clickstream data from Azure Event Hubs. The solution must join the streaming data with a slowly changing dimension (SCD) table that stores product details. The SCD table is stored in Azure Data Lake Storage Gen2 as Delta format and is updated every few hours. The joined results must be written to a Delta table for near-real-time dashboards. The key requirement is to ensure that the join always uses the latest version of the SCD data without reprocessing the entire stream. The solution must minimize latency and cost. Which approach should you recommend?

Accepted Answer

Use Spark Structured Streaming with a static DataFrame for the SCD table and refresh it periodically using a trigger that reads the latest snapshot.. Option B is correct because it uses a static DataFrame for the SCD table and refreshes it periodically using a trigger (e.g., a time-based or file-based trigger). This approach ensures that the join always uses the latest version of the SCD data without reprocessing the entire stream, as the static DataFrame is re-read only when the SCD is updated. It minimizes latency and cost by avoiding the overhead of reading the SCD in every micro-batch (as in Option A) or using a streaming join (as in Option D), which is not suitable for batch-updated SCD tables.

Answer

Use Spark Structured Streaming with foreachBatch and read the SCD table inside the foreachBatch function.

Answer

Use Spark Structured Streaming with a batch read of the SCD table in each micro-batch using spark.read.

Answer

Use Spark Structured Streaming with a streaming join on the SCD table by converting it to a stream using readStream.

Question 4

Your company uses Azure Synapse Analytics to run a data warehouse. You have a dedicated SQL pool with a hash-distributed fact table named Sales. The distribution column is ProductID. You notice that queries against the Sales table are slow due to data skew. After analysis, you find that a few products (e.g., ProductID 100, 200) account for 80% of the rows. You need to optimize query performance without redesigning the entire table. You also need to minimize data movement during queries. Which action should you take?

Accepted Answer

Change the distribution to round-robin.. Option B is correct. Round-robin distribution distributes data evenly across distributions, eliminating skew. However, it may increase data movement for joins. Given the severe skew, round-robin is a reasonable trade-off. Option A is wrong because adding more distributions does not fix skew. Option C is wrong because changing to replicate distribution is not suitable for large fact tables. Option D is wrong because creating non-clustered indexes does not address distribution skew.

Answer

Increase the number of distributions to 120.

Answer

Change the distribution to replicate for the Sales table.

Answer

Create non-clustered indexes on the ProductID column.

Question 5

You are designing a data processing pipeline using Azure Data Factory. The pipeline must ingest data from an HTTP endpoint that returns a JSON array. The data must be transformed by flattening nested arrays and then loaded into an Azure SQL Database table. The pipeline should be triggered daily. You need to choose the appropriate activities and transformations. The solution must be cost-effective and easy to maintain. Which combination of activities should you use?

Accepted Answer

Use a Copy activity to ingest data from the HTTP source into Azure Blob Storage, then a Data Flow activity with a Flatten transformation to flatten the JSON, and finally a Copy activity to load into SQL Database.. Option A is correct. A Copy activity can ingest the JSON from HTTP, and a Data Flow with Flatten transformation can flatten the nested arrays. Option B is wrong because Execute Pipeline is unnecessary. Option C is wrong because a Lookup activity is for reading a single row, not for data ingestion. Option D is wrong because multiple Copy activities are not needed.

Answer

Use a Lookup activity to read the JSON, then a ForEach activity to iterate and insert rows into SQL Database.

Answer

Use a Data Flow activity directly from HTTP source with a Flatten transformation and sink to SQL Database.

Answer

Use two Copy activities: one to copy JSON to Blob Storage, and another to copy from Blob Storage to SQL Database without transformation.

Question 6

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 7

You are running a batch processing job using Azure Data Factory. The job reads from Azure Blob Storage, transforms data with a Data Flow, and writes to Azure Synapse Analytics. The job fails intermittently with the error: 'Operation on target WriteToSynapse failed: Cannot bulk load because the file could not be opened.' You need to resolve the issue with minimal downtime. What should you do?

Accepted Answer

Enable staging blob file deletion in the copy activity settings.. The error indicates a transient lock on the staging blob file. Enabling staging blob file deletion in the copy activity settings allows the pipeline to clean up and retry. Increasing the degree of copy parallelism does not address file locking. Using PolyBase with external tables requires schema changes. Switching to staging via SQL authentication does not solve the file lock issue.

Answer

Increase the degree of copy parallelism in the Data Flow.

Answer

Use PolyBase to load data directly from Blob Storage without staging.

Answer

Switch to staging via SQL authentication instead of Managed Identity.

Question 8

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 9

You are developing a data processing solution that requires aggregating sales data from multiple CSV files stored in Azure Data Lake Storage Gen2. The data should be cleansed and transformed before loading into Azure Synapse Analytics. Which Azure service should you use to implement a code-free transformation pipeline?

Accepted Answer

Azure Data Factory with Mapping Data Flows. Azure Data Factory with Mapping Data Flows allows code-free visual transformations. Azure Databricks and HDInsight require code. Azure Analysis Services is for tabular modeling, not data processing.

Answer

Azure HDInsight with Hive

Answer

Azure Analysis Services

Answer

Azure Databricks with PySpark

Question 10

You are using Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. You notice that queries are slower than expected. The files are large (500 MB each) and not partitioned. You need to improve query performance without moving data. What should you do?

Accepted Answer

Create external tables with explicit file format and partition elimination hints.. Creating external tables with file format options allows the serverless SQL pool to use metadata for optimization. Changing the file format to CSV or using OPENROWSET without external tables does not improve performance as much. Converting to Delta format requires data movement. Partitioning the files would be best, but that requires reorganizing files.

Answer

Convert the Parquet files to Delta format using Azure Databricks.

Answer

Change the files to CSV format with a header row.

Answer

Use OPENROWSET with BULK and specify ROWSET_OPTIONS for better performance.

Question 11

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 12

You need to process a large dataset that contains personally identifiable information (PII). The data must be anonymized before being used for analytics. Which Azure service should you use to apply column-level masking dynamically?

Accepted Answer

Azure Synapse Analytics dynamic data masking. Azure Synapse Analytics provides dynamic data masking at the column level. Azure Purview is for data governance. Azure API Management is for APIs. Azure Data Lake Storage does not provide masking.

Answer

Azure API Management policies

Answer

Azure Data Lake Storage access control lists (ACLs)

Answer

Azure Purview classification and labeling

Question 13

You are designing a data processing solution that requires running custom Python scripts for data transformation. The scripts have dependencies on specific libraries that are not pre-installed in the environment. You need a fully managed, serverless compute option that allows you to install custom libraries. Which service should you use?

Accepted Answer

Azure Databricks with cluster-scoped libraries. Azure Databricks with cluster configuration allows installation of custom libraries. Azure Synapse Spark pools also support libraries but require cluster management. Azure Functions may have limitations. Azure Container Instances require container images.

Answer

Azure Functions with custom handlers

Answer

Azure Container Instances with a custom Docker image

Answer

Azure Synapse Analytics serverless SQL pool

Question 14

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 15

Which TWO options are correct for configuring a copy activity in Azure Data Factory to load data from Azure Blob Storage to Azure SQL Database?

Accepted Answer

Use a staging table in Azure SQL Database before inserting into the final table.. Correct options: A (staging via Blob Storage for large loads) and D (using a staging table and then inserting into final). B is wrong because staging cannot be skipped for large data. C is wrong because Blob Storage is not an intermediate for Azure SQL. E is wrong because PolyBase is not supported for Azure SQL Database.

Answer

Use Azure Data Lake Storage as the staging location for better throughput.

Answer

Use PolyBase to load directly from Blob Storage to Azure SQL Database.

Answer

Always skip staging to reduce latency.