DP-203 Develop data processing Practice Test 18 — 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?

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

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 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 3

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 4

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 5

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 6

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 7

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?

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

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 8

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 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 THREE options are valid ways to transform data in Azure Synapse Analytics?

Accepted Answer

Use T-SQL scripts in a dedicated SQL pool.. T-SQL scripts are a native and primary method for transforming data within a dedicated SQL pool in Azure Synapse Analytics. You can use CREATE TABLE AS SELECT (CTAS), INSERT...SELECT, and other T-SQL statements to perform complex transformations like aggregations, joins, and data cleansing directly on the distributed data, leveraging the MPP (Massively Parallel Processing) engine for high performance.

Answer

Use Power Query online in Synapse pipelines.

Answer

Use Azure Machine Learning pipelines for data wrangling.

Question 11

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.. Correct: A (reference data joins for enrichment) and B (windowing functions for time-based aggregations). Option C is wrong because while Stream Analytics supports many output sinks, not all Azure data stores are supported out-of-the-box; some require custom code or connectors. Option D is wrong because Stream Analytics provides at-least-once processing semantics, not exactly-once; exactly-once depends on the sink configuration and output adapter. Option E is incorrect because Stream Analytics natively supports custom JavaScript functions for complex transformations, but it does not natively support C# functions; C# functions require integration with Azure Functions, making this option misleading.

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 12

Which TWO options are correct approaches to handle schema drift in Azure Data Factory Mapping Data Flows?

Accepted Answer

Enable 'Allow schema drift' in the source transformation.. Enabling 'Allow schema drift' in the source transformation is the primary mechanism in Mapping Data Flows to handle incoming columns that are not defined in the dataset schema. This setting allows the data flow to dynamically adapt to changes in the source data structure, such as new or missing columns, without requiring manual schema updates.

Answer

Use a conditional split to route rows with different schemas to separate sinks.

Answer

Define a rigid schema in the source dataset and reject rows that don't match.

Answer

Disable schema drift to improve performance.

Question 13

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 code-free visual interface for transformations. They can read multiple CSV files from ADLS Gen2, handle schema variations, filter nulls, parse dates, aggregate, and write partitioned Parquet files. This meets the requirements of minimal coding, cost-effectiveness (pay-per-execution), and no cluster management. A is incorrect because while Azure Synapse Serverless SQL pool can query CSV files and use CETAS to write Parquet, it requires T-SQL coding, handling schema drift is more complex, and partitioning via CETAS can be cumbersome. Also, serverless SQL is optimized for ad-hoc querying rather than batch ETL. B is incorrect because Azure Synapse Dedicated SQL pool requires provisioning and managing a dedicated cluster, incurring ongoing costs even when idle. It also involves more coding with T-SQL and PolyBase, and does not align with 'no cluster management' requirement. D is incorrect because Azure Databricks requires creating and managing a cluster, and coding in PySpark or Scala, which contradicts 'minimal coding effort' and 'no cluster management'.

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 14

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 (Option D) is the most appropriate service for this scenario. It can ingest from Event Hubs, perform windowed aggregations (e.g., Tumbling window for rolling average), detect anomalies using conditional logic, and output to both Cosmos DB and Event Grid. It handles partitioning automatically and provides stateful processing with low latency. Option A uses Azure Functions, which are not designed for high-throughput stateful stream processing across partitions; each function invocation is stateless and would require an external cache (Redis) adding latency and complexity. Option B incorrectly describes Azure Synapse Pipelines with Data Flow, which is a batch-oriented ETL tool and not suitable for real-time streaming. Option C uses Azure Databricks Structured Streaming, which is more complex to manage and requires additional configuration for state management and output to multiple sinks, resulting in higher operational overhead compared to Stream Analytics.

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 15

A company uses Azure Synapse Analytics dedicated SQL pool. The data engineering team notices that queries against a large fact table are running slowly. The table uses round-robin distribution and has a columnstore index. The team wants to improve query performance without adding more resources. Which action should the team take?

Accepted Answer

Change the distribution to hash on the column that is most frequently used in joins.. Hash-distributing the large fact table on the column most frequently used in joins minimizes data movement during query processing, improving performance. Round-robin distribution distributes data evenly but does not optimize for join operations. Hash distribution on a join key ensures that rows with the same key value are placed in the same distribution, reducing shuffling. Option A is incorrect because increasing the degree of parallelism does not address the distribution issue and may not improve performance without additional resources. Option B is incorrect because hash on multiple columns is not supported in Azure Synapse dedicated SQL pool; only a single column can be used as the distribution key. Option D is incorrect because a heap table would lack indexing, degrading query performance for analytical workloads.

Answer

Keep round-robin distribution but increase the degree of parallelism.

Answer

Change the distribution to hash on multiple columns.

Answer

Rebuild the table as a heap to improve insert performance.