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

Question 1

Which THREE Azure services can you use together to build a serverless, event-driven data processing pipeline that ingests data from Azure Blob Storage, transforms it using custom code, and loads it into Azure Cosmos DB?

Accepted Answer

Azure Functions. Options A, C, and E are correct. Azure Functions can be triggered by Blob Storage events to run custom code, and the output can be written to Cosmos DB via the Cosmos DB output binding. Option B is wrong because Azure Batch is for large-scale parallel computing, not event-driven triggers. Option D is wrong because Azure Logic Apps is more for orchestration, not custom code execution.

Answer

Azure Batch

Answer

Azure Logic Apps

Question 2

Which TWO techniques can you use to handle schema drift in Azure Data Factory mapping data flows?

Accepted Answer

Enable 'Allow schema drift' in the source transformation. Options A and D are correct. 'Allow schema drift' allows flexible column mapping based on incoming schema. Column pattern matching can map dynamic columns to a target. Option B is wrong because using a fixed schema mapping is the opposite of handling drift. Option C is wrong because deriving columns is for static transformations. Option E is wrong because assertion rules are for validation, not adaptation.

Answer

Use derived column transformation to handle each new column manually

Answer

Use assertion rules to reject rows with unknown columns

Answer

Use a fixed schema mapping to ignore unknown columns

Question 3

You are debugging an Azure Data Factory pipeline that copies CSV files from Azure Blob Storage to Azure SQL Database. The pipeline is defined as shown in the exhibit. The first row of each CSV file contains column headers. After a run, you notice that the first data row is missing from the target table. What is the most likely cause?

Accepted Answer

The 'skipLineCount' is set to 1, which skips the first data row after the header. Option B is correct because 'skipLineCount': 1 skips the first line after the header line; since header is already parsed, the first data row is skipped. Option A is wrong because 'recursive': true only affects subdirectories, not line skipping. Option C is wrong because 'writeBehavior': 'insert' is default and does not skip rows. Option D is wrong because 'enableStaging': false is fine for direct copy.

Answer

The 'writeBehavior' is set to 'insert', which does not overwrite existing rows

Answer

The 'recursive' setting is true, causing the pipeline to read files in subdirectories and duplicate data

Answer

The 'enableStaging' is false, which prevents PolyBase from loading data

Question 4

You are analyzing a Kusto query in Azure Data Explorer that calculates total sales per product for January 2024 and filters for products with sales over 10,000. The query uses the materialize() function. You notice that the query runs slower than expected. What is the primary reason the materialize() function may not be providing the expected performance benefit in this query?

Accepted Answer

The materialize() result is referenced only once in the query, so materialization adds unnecessary overhead. Option B is correct because materialize() only helps if the result is referenced multiple times; in this query, TotalSales is used only once, so materialize() adds overhead without benefit. Option A is wrong because the query is already using summarize. Option C is wrong because datetime filtering is fine. Option D is wrong because order by does not conflict with materialize.

Answer

The join with the Products table forces a shuffle that bypasses the materialized result

Answer

The query uses summarize, which already materializes results internally

Answer

The datetime range filter is not sargable, causing full table scan

Question 5

You are a Data Engineer at Contoso Ltd. The company operates an e-commerce platform that generates streaming data from user interactions (clicks, page views, purchases) at a rate of 50,000 events per second. The data is ingested into Azure Event Hubs. You need to design a data processing solution that meets the following requirements:

1. Real-time dashboards must display aggregated metrics (e.g., total sales, active users) with a latency of less than 2 seconds.
2. Historical data must be stored in Azure Data Lake Storage Gen2 in Delta Lake format for batch analytics.
3. The solution must support exactly-once semantics for the streaming output to both the dashboard and the data lake.
4. You must use Azure Stream Analytics as the primary processing engine.
5. The output to the dashboard should use Azure Power BI, while the output to the data lake should use Azure Synapse Analytics serverless SQL pool for querying.

You have configured an Azure Stream Analytics job with Event Hubs as input. For output, you added a Power BI dataset and an Azure Data Lake Storage Gen2 output. However, you discover that the Power BI dataset is being updated with duplicate records, and the data lake output sometimes misses small windows of data during job restarts. Which two actions should you take to meet the requirements? (Choose two.)

A. Change the Power BI output to use the 'Exactly once' output mode.
B. Change the Event Hubs compatibility level to 1.0 to guarantee exactly-once.
C. Enable checkpointing in the Stream Analytics job and use the 'Exactly once' output mode for the Azure Data Lake Storage Gen2 output.
D. Use a second Stream Analytics job: one for Power BI with 'At least once' mode, and another for the data lake with 'Exactly once' mode.

Accepted Answer

Enable checkpointing in the Stream Analytics job and use the 'Exactly once' output mode for the Azure Data Lake Storage Gen2 output.. Option A is correct because Power BI output supports 'Exactly once' mode to avoid duplicates. Option C is correct because enabling checkpointing ensures no data loss during restarts, and 'Exactly once' mode for ADLS Gen2 ensures exactly-once delivery. Option B is wrong because compatibility level 1.0 is outdated and does not guarantee exactly-once. Option D is wrong because it adds complexity and does not solve the issue of duplicates on Power BI.

Answer

Change the Event Hubs compatibility level to 1.0 to guarantee exactly-once.

Answer

Use a second Stream Analytics job: one for Power BI with 'At least once' mode, and another for the data lake with 'Exactly once' mode.

Question 6

A manufacturing company uses Azure Data Lake Storage Gen2 to store IoT sensor data. The data arrives in JSON format with a nested structure. You need to transform the data into a tabular format for downstream analytics using Azure Synapse Pipelines. Which data flow transformation should you use?

Accepted Answer

Flatten transformation. Option B is correct because the Flatten transformation in mapping data flows unpacks nested arrays into rows. Option A is wrong because the Aggregate transformation groups data but does not flatten nested structures. Option C is wrong because the Pivot transformation rotates rows to columns. Option D is wrong because the Window transformation calculates aggregated values over a range of rows.

Answer

Aggregate transformation

Answer

Window transformation

Answer

Pivot transformation

Question 7

Your company uses Azure Synapse Analytics to run a large-scale batch processing job every night. The job currently runs on a dedicated SQL pool and takes 4 hours. Management wants to reduce the runtime to under 2 hours without increasing cost. The job involves heavy compute operations with no data movement limitations. What should you do?

Accepted Answer

Enable result-set caching on the dedicated SQL pool.. Option C is correct because result-set caching stores query results in SSD storage, reducing compute time for repeated queries. Option A is wrong because increasing the service level (DWU) would increase cost. Option B is wrong because materialized views require storage but also incur compute for refresh. Option D is wrong because workload management separates resources but does not automatically reduce compute time without additional cost.

Answer

Create materialized views on frequently queried tables.

Answer

Increase the service level objective (DWU) of the dedicated SQL pool.

Answer

Implement workload management to prioritize the job.

Question 8

You are designing a data processing solution in Azure Synapse Analytics. The solution must support both batch and streaming data ingestion. Which Azure service should you use to ingest streaming data into Synapse Analytics?

Accepted Answer

Azure Event Hubs. Option A is correct because Azure Event Hubs is a big data streaming platform and event ingestion service that integrates with Synapse. Option B is wrong because Azure Data Factory is primarily for batch data integration. Option C is wrong because Azure Blob Storage is a storage service, not an ingestion service. Option D is wrong because Azure Analysis Services is for semantic modeling, not streaming ingestion.

Answer

Azure Data Factory

Answer

Azure Blob Storage

Answer

Azure Analysis Services

Question 9

You are troubleshooting a failed Azure Synapse Pipeline execution. The pipeline uses a Copy activity to load data from an on-premises SQL Server to Azure Data Lake Storage Gen2. The error indicates a 'Connection timeout' to the on-premises source. The Integration Runtime is Self-Hosted and has been running successfully for months. What is the most likely cause?

Accepted Answer

The on-premises network configuration has changed.. Option B is correct because a change in the on-premises network configuration (e.g., firewall rules, DNS) could block the Integration Runtime from reaching the SQL Server. Option A is wrong because the Integration Runtime was running, so installation is not the issue. Option C is wrong because the storage account is the destination, not the source. Option D is wrong because the Integration Runtime was successful before, so credentials are likely valid.

Answer

The SQL Server authentication credentials have expired.

Answer

The Self-Hosted Integration Runtime is not installed.

Answer

The Azure Storage account firewall is blocking access.

Question 10

You are building a data processing solution using Azure Databricks. The solution must process streaming data from Azure Event Hubs, join it with a static reference table stored in Azure Data Lake Storage Gen2 (Parquet format), and write the output to Azure Synapse Analytics. The reference table is updated daily. Which approach minimizes latency and ensures data consistency?

Accepted Answer

Use Spark Structured Streaming with a streaming join and cache the reference table as a static DataFrame.. Option D is correct because using Spark Structured Streaming with a streaming join and a static DataFrame for the reference table minimizes latency and ensures consistency by reading the reference table once. Option A is wrong because batch processing every hour adds latency. Option B is wrong because loading the reference table per batch increases overhead. Option C is wrong because foreachBatch is useful for batch writes but does not improve join performance.

Answer

Use Spark Structured Streaming with foreachBatch to write to Synapse.

Answer

Use Spark Structured Streaming with a streaming join and load the reference table in each micro-batch.

Answer

Use a batch job that runs every hour to process the data.

Question 11

You are designing a data processing pipeline in Azure Data Factory. The pipeline must copy data from Azure Blob Storage to Azure SQL Database and transform the data using a mapping data flow. The data flow includes a Derived Column transformation. What is the purpose of the Derived Column transformation?

Accepted Answer

Create new columns or modify existing columns using expressions.. The Derived Column transformation in Azure Data Factory mapping data flows is used to create new columns or modify existing columns by applying expressions. This allows you to perform calculations, string manipulations, or conditional logic directly within the data flow, enabling in-flight data transformation before writing to the sink.

Answer

Aggregate data by grouping rows.

Answer

Sort data in ascending or descending order.

Answer

Rename or drop columns.

Question 12

You are using Azure Stream Analytics to process real-time temperature data from IoT devices. The output must be written to Azure SQL Database. The job has been running successfully for weeks, but recently you notice that the output data has duplicate rows. The input events are unique. The job uses a windowed aggregation (TumblingWindow). What is the most likely cause of duplicates?

Accepted Answer

The job is being restarted and reprocessing data.. Option D is correct because when a Stream Analytics job recovers from a failure, it reprocesses input from the last checkpoint, which can cause duplicate output if the output is not idempotent. Option A is wrong because window size affects aggregation but not duplicates. Option B is wrong because late events can cause out-of-order results but not duplicates if handled correctly. Option C is wrong because the input is unique, so duplicates are not from input.

Answer

The job is not handling late-arriving events.

Answer

The input event hub is receiving duplicate events.

Answer

The tumbling window size is too small.

Question 13

You are designing a data processing solution using Azure Synapse Analytics serverless SQL pool. The solution must query data stored in Parquet files in Azure Data Lake Storage Gen2. The queries are ad-hoc and vary greatly. Which feature should you use to optimize query performance for frequently accessed data partitions?

Accepted Answer

Use OPENROWSET with explicit file path filtering.. Option C is correct because the OPENROWSET function with file path filtering can prune partitions by specifying the path to specific folders. Option A is wrong because materialized views are not supported in serverless SQL pool. Option B is wrong because result-set caching is for dedicated SQL pool. Option D is wrong because workload management is for dedicated SQL pool.

Answer

Implement workload management to prioritize queries.

Answer

Enable result-set caching on the serverless SQL pool.

Answer

Create materialized views on the Parquet files.

Question 14

You are implementing a data processing solution in Azure Databricks. The solution must read data from Azure Data Lake Storage Gen2, transform it using PySpark, and write the results back to a different location in the same storage account. You need to authenticate to the storage account securely without storing secrets in the notebook. What should you use?

Accepted Answer

Azure Key Vault-backed secret scope. Option A is correct because Azure Key Vault-backed secret scopes allow you to securely reference secrets without storing them in the notebook. Option B is wrong because Service Principals require managing client secrets. Option C is wrong because Access keys are long-lived and insecure. Option D is wrong because Shared access signatures can expose tokens.

Answer

Service Principal with a client secret stored in the notebook

Answer

Shared access signature (SAS) token

Answer

Storage account access key

Question 15

You are designing a batch processing solution in Azure Synapse Analytics using pipelines. The solution must load data from multiple sources (Azure Blob Storage, Azure SQL Database, and REST API) into a dedicated SQL pool. After loading, you need to run a stored procedure to aggregate the data. Which two activities should you include in the pipeline? (Choose two.)

Accepted Answer

Copy activity. Options B and D are correct. Copy activity is used to copy data from sources. Stored procedure activity is used to execute the aggregation procedure. Option A is wrong because Data Flow is for transformations, but the requirement is to load and then run a stored procedure. Option C is wrong because Execute Pipeline is for running another pipeline, not for executing a stored procedure. Option E is wrong because Azure Function activity is for custom code, not needed here.

Answer

Execute Pipeline activity

Answer

Azure Function activity

Answer

Data Flow activity