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

Question 1

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?

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

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 2

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 spreads rows evenly across all 60 distributions regardless of ProductID value, eliminating the skew caused by hot ProductIDs 100 and 200. Because no single distribution holds a disproportionate share of rows, parallel query processing is balanced and data movement between distributions during joins/aggregations is reduced for this fact table. It's a metadata-only change (CTAS/ALTER distribution) that doesn't require redesigning the table schema.

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 3

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.. Copy activity lands the raw JSON in Blob Storage, then a Mapping Data Flow with a Flatten transformation handles the nested array expansion, and a final Copy activity loads the flattened output into Azure SQL Database. This separation of ingestion, transformation, and load is the standard cost-effective ADF pattern for semi-structured sources.

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 4

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 the correct choice because it is a low-cost, highly scalable object store ideal for landing raw streaming data before processing. At 10,000 events/sec with sub-1 KB payloads, the volume is modest and Blob Storage's hot/cool/archive tiers let you minimize cost by choosing an appropriate access tier. It integrates natively with Azure Stream Analytics, Event Hubs Capture, and Azure Data Factory for downstream processing.

Answer

Azure Data Lake Storage Gen2

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Question 5

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 6

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

Add a 'RowNumber' transformation after the source, then filter rows where row number <= 10000 per partition.. The row limit in source settings applies globally, not per partition. The correct approach is to use a Window transformation to assign a row number per partition (partitioned by the same columns as the folder structure, e.g., year, month) and then filter rows where the row number is <= 10000. Option B describes this method (though 'RowNumber' is not an actual transformation name; it refers to the Window transformation with a row number function). Option A is incorrect because there is no 'Top N' transformation in Azure Data Factory mapping data flows. Option C is incorrect because the row limit setting does not support dynamic expressions based on partition variables. Option D is incorrect because Distinct removes duplicates but does not limit row count.

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.

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 7

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 8

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-scoped libraries is the correct choice because it provides a fully managed, serverless Apache Spark environment where you can install custom Python libraries (e.g., via PyPI or Maven) at the cluster level. This allows your custom Python scripts to run with all required dependencies without managing any infrastructure, directly meeting the requirement for a serverless compute option with custom library support.

Answer

Azure Functions with custom handlers

Answer

Azure Container Instances with a custom Docker image

Answer

Azure Synapse Analytics serverless SQL pool

Question 9

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 are A and B. Option A (using a staging table in Azure SQL Database before inserting into the final table) is a best practice for large data loads, enabling efficient truncate-and-reload or upsert operations. Option B (using staging via Azure Blob Storage when loading large volumes) improves performance by batching and parallelizing the load, reducing timeouts and resource contention. Option D is incorrect because PolyBase is not supported for Azure SQL Database; it is available only for Azure Synapse Analytics and SQL Server. Option C is incorrect because Azure Data Lake Storage is not the standard staging location for Azure SQL Database; Azure Blob Storage is used. Option E is incorrect because skipping staging can degrade performance for large data volumes.

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.

Question 10

Which TWO options are correct about using Azure Stream Analytics for real-time data processing?

Accepted Answer

You can join streaming data with static reference data for enrichment.. Option A is correct because Azure Stream Analytics natively supports joining a streaming input with a static reference data input (blob or SQL Database) to enrich events in real time, using the reference data join pattern. Option B is correct because Stream Analytics provides built-in temporal windowing functions — Tumbling, Hopping, Sliding, Session, and Snapshot windows — directly in its SQL-like query language. Option C is incorrect because outputs are limited to supported sinks (e.g., Blob Storage, Event Hub, SQL Database, Power BI, Cosmos DB, Azure Functions, Service Bus), not any Azure data store. Option D is incorrect because Stream Analytics provides at-least-once delivery, not guaranteed exactly-once semantics. Option E is incorrect because Stream Analytics does not support custom JavaScript or C# user-defined functions; it only supports SQL-like queries and JavaScript user-defined functions are not a feature (C# UDFs are not supported either).

Answer

Stream Analytics can output to any Azure data store without custom code.

Answer

Stream Analytics guarantees exactly-once processing semantics.

Answer

You can use custom JavaScript or C# functions for complex transformations.

Question 11

You are a data engineer at a retail company. You need to design a batch processing solution that ingests daily sales data from multiple stores. Each store uploads a CSV file to a dedicated folder in Azure Data Lake Storage Gen2. The files have the same schema but may have minor variations in column order and include null values. After ingestion, you must clean the data by removing rows with null values in the 'SalesAmount' column, convert the 'Date' column from string to date type, and aggregate sales by product category. The output should be stored as Parquet files partitioned by year and month in the same Data Lake. You need to choose a compute service and implement the transformation with minimal coding effort. The solution must be cost-effective and require no cluster management. What should you do?

Accepted Answer

Use Azure Data Factory with a Mapping Data Flow. Configure the source to read all CSV files from the ADLS Gen2 folder. Add a Filter transformation to remove null SalesAmount, a Derived Column to parse the Date, and an Aggregate to sum sales by category. Sink to ADLS Gen2 as Parquet with partition by year and month.. Azure Data Factory Mapping Data Flows provide a low-code, visual way to build transformations. It can read multiple CSV files from a folder, filter null SalesAmount, derive the Date column, aggregate by category, and sink to Parquet with year/month partitioning — all without cluster management and with minimal coding, meeting the cost-effective and no-cluster-management requirements.

Answer

Use Azure Synapse Serverless SQL pool. Create external tables over the CSV files, write a T-SQL query to filter, cast, aggregate, and use CETAS to write Parquet partitions.

Answer

Use Azure Synapse Dedicated SQL pool. Load CSV files via PolyBase, transform with T-SQL, and use CREATE TABLE AS SELECT to output partitioned Parquet.

Answer

Use Azure Databricks with a PySpark notebook. Mount the ADLS Gen2 storage, read CSV files with schema inference, filter, cast, aggregate, and write partitioned Parquet.

Question 12

You are developing a real-time data processing solution for a financial services company. The system ingests stock trade data from Azure Event Hubs at 50,000 events per second. Each event is a JSON object with fields: TradeID, Symbol, Price, Quantity, Timestamp. You need to calculate a 5-minute rolling average of the trade price per symbol and store the result in Azure Cosmos DB for low-latency queries. Additionally, you need to detect anomalies where the price deviates more than 10% from the rolling average within the same window, and send alerts to Azure Event Grid. You must minimize latency and ensure that the processing is stateful across multiple partitions. What should you do?

Accepted Answer

Create an Azure Stream Analytics job. Define input from Event Hubs. Use a Tumbling window of 5 minutes to compute average price per symbol. Add a custom function to compare each event's price to the average and output anomalies. Write to Cosmos DB via the Azure Cosmos DB output adapter and to Event Grid via the Event Grid output adapter.. Azure Stream Analytics is a fully managed, real-time analytics service that supports stateful processing across multiple partitions. It provides built-in windowing functions (like Tumbling, Hopping, Sliding) and output adapters for Cosmos DB and Event Grid. It minimizes latency and management overhead.

Answer

Use Azure Functions with Event Hubs trigger. In each function invocation, compute the rolling average using a distributed cache (Redis) and detect anomalies. Write to Cosmos DB and Event Grid via output bindings.

Answer

Use Azure Synapse Pipelines with a Data Flow. Set up a streaming Data Flow from Event Hubs, compute rolling average using window functions, and sink to Cosmos DB and Event Grid.

Answer

Use Azure Databricks with Structured Streaming. Read from Event Hubs using Kafka API. Perform windowed aggregations and anomaly detection using Spark SQL. Write to Cosmos DB via the Azure Cosmos DB Spark connector and to Event Grid via HTTP sink.

Question 13

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.. SQL Server change tracking reliably captures all changes including deletes and out-of-order updates, and the watermark table mechanism ensures no rows are missed. Option A is incorrect because CDC can miss rows when timestamps are out of order. Option C is incorrect because it requires schema changes (triggers) and may impact source performance. Option D is incorrect because the 'LastUpdated' column is not always increasing, so queries based on 'last run time' can miss rows.

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 14

You are building a data transformation in Azure Databricks using PySpark. The data includes a column 'timestamp' in string format 'yyyy-MM-dd HH:mm:ss'. You need to convert this to a timestamp type and extract the date part for partitioning. Which code snippet should you use?

Accepted Answer

df.withColumn('date', to_date('timestamp', 'yyyy-MM-dd HH:mm:ss')). `to_date` with the format string 'yyyy-MM-dd HH:mm:ss' converts the string column to a date type, extracting only the date part (year, month, day) as required for partitioning. This matches the requirement to convert the timestamp string to a date for partitioning, not a full timestamp.

Answer

df.withColumn('date', col('timestamp').cast('date'))

Answer

df.withColumn('date', to_timestamp('timestamp', 'yyyy-MM-dd HH:mm:ss'))

Answer

df.withColumn('date', to_date('timestamp'))

Question 15

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.. Column pruning (selecting only required columns) reduces the amount of data shuffled, decreasing shuffle size. Option A is incorrect because coalescing reduces the number of partitions but does not reduce the data volume shuffled. Option B is incorrect because increasing the number of shuffle partitions can increase shuffle overhead, not reduce it. Option C is incorrect because broadcasting all tables is impractical for large datasets (10 TB) and does not reduce shuffle data.

Answer

Coalesce the number of partitions before the shuffle.

Answer

Increase the number of shuffle partitions.

Answer

Broadcast all tables to avoid shuffles.