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

Question 1

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?

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

Accepted Answer

The materialize() result is referenced only once in the query, so materialization adds unnecessary overhead. Materialize() only provides performance benefit when the materialized result is referenced multiple times. In this query, the materialized result is used only once, so the overhead of materialization (storing the result in memory) outweighs any benefit, potentially making the query slower. Option A is incorrect because there is no join with a Products table; the query uses a single table. Option B is incorrect because summarize does not inherently materialize results; it computes aggregations on the fly. Option C is incorrect because datetime range filters in Kusto are sargable and do not cause full table scans.

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 2

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

Change the Power BI output to use the 'Exactly once' output mode. [CORRECT]. Power BI output supports 'Exactly once' output mode, which prevents duplicate records in the dashboard. Option C is correct because enabling checkpointing and using 'Exactly once' output mode for Azure Data Lake Storage Gen2 ensures exactly-once delivery to the data lake and prevents data loss during job restarts. Option B is incorrect because changing Event Hubs compatibility level to 1.0 does not guarantee exactly-once semantics; it only affects the serialization format. Option D is incorrect because using a second Stream Analytics job adds unnecessary complexity, and 'At least once' mode for Power BI would still allow duplicates.

Answer

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

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. [wrong]

Question 3

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. 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 Window transformation calculates aggregated values over a range of rows. Option D is wrong because the Pivot transformation rotates rows to columns.

Answer

Aggregate transformation

Answer

Window transformation

Answer

Pivot transformation

Question 4

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. Azure Event Hubs is a big data streaming platform and event ingestion service that integrates with Synapse Analytics for streaming data. Option A is incorrect because Azure Data Factory is primarily for batch data integration, not real-time streaming. Option B is incorrect because Azure Blob Storage is a storage service, not an ingestion service. Option D is incorrect because Azure Analysis Services is for semantic modeling, not streaming ingestion.

Answer

Azure Data Factory

Answer

Azure Blob Storage

Answer

Azure Analysis Services

Question 5

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.. A 'Connection timeout' error when using a Self-Hosted Integration Runtime (SHIR) that has been running successfully for months indicates a network-level issue rather than an authentication or configuration problem. The most likely cause is a change in the on-premises network configuration (e.g., firewall rules, proxy settings, or DNS changes) that prevents the SHIR from reaching the SQL Server on the specified port (typically TCP 1433). Since the SHIR is already installed and was working, the timeout points to a connectivity break, not a credential or installation issue.

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 6

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.. Using Spark Structured Streaming with a streaming join and caching the reference table as a static DataFrame minimizes latency and ensures consistency by reading the reference table once and caching it for the duration of the stream. This avoids reloading the reference table in each micro-batch (as in option C) and avoids the latency of batch processing (as in option D). Option B, foreachBatch, is useful for writing to sinks like Synapse but does not address the join performance or consistency of the reference data.

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 7

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 8

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.. When an Azure Stream Analytics job is restarted, it may reprocess data from the last checkpoint or from the beginning of the input stream, depending on the configured start time and output policy. This reprocessing can cause duplicate rows in the output, especially when using windowed aggregations like TumblingWindow, because the same events are aggregated again and written to Azure SQL Database without deduplication logic.

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 9

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.. Using OPENROWSET with explicit file path filtering allows partition pruning by limiting the scan to the specified folders, thus improving query performance for frequently accessed partitions. Option A is wrong because workload management is a feature for dedicated SQL pool, not serverless. Option C is wrong because result-set caching is not supported in serverless SQL pool; it is a feature of dedicated SQL pool. Option D is wrong because materialized views are not supported in serverless 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 10

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. Azure Key Vault-backed secret scopes allow you to securely reference secrets without storing them in the notebook. Option A is wrong because storing a client secret directly in the notebook is insecure. Option C is wrong because SAS tokens can be exposed and are less secure. Option D is wrong because storage account access keys are long-lived and should not be used in notebooks.

Answer

Service Principal with a client secret stored in the notebook

Answer

Shared access signature (SAS) token

Answer

Storage account access key

Question 11

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 12

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 two transformations can be used to change data types? (Choose two.)

Accepted Answer

Derived Column. Options B and E are correct. Derived Column can change data types through expressions, and Select can cast types during column projection. Option C (Assert) is incorrect because it only validates and routes rows based on conditions; it does not convert data types. Option A (Conditional Split) and Option D (Sort) are also incorrect.

Answer

Conditional Split

Answer

Assert

Answer

Sort

Question 13

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.. 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 14

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.. 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 B is wrong because increasing the number of 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 15

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.. The correct approach is Option B: Use a Copy activity to ingest data from the HTTP source into Azure Blob Storage (staging), then a Data Flow activity with a Flatten transformation to flatten the JSON, and finally a Copy activity to load into SQL Database. This is cost-effective and maintainable because it separates ingestion and transformation, allows for schema drift, and uses serverless Data Flows. Option A is wrong because Lookup is for reading a single row/value, not for bulk data ingestion; ForEach with insert would be inefficient and costly. Option C is wrong because Data Flows cannot directly read from HTTP sources; they require a dataset that is staged in a supported store like Blob Storage. Option D is wrong because it performs no transformation, so the nested JSON would not be flattened for loading into SQL.

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.