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

Question 1

Which TWO Azure services can be used to perform data transformation in a data pipeline? (Select two.)

Accepted Answer

Azure Data Factory. Azure Data Factory is a cloud-based ETL service that provides a code-free visual interface for orchestrating data movement and transformation at scale. It supports data flows, which allow you to perform transformations like aggregations, joins, and filtering without writing code, making it a correct choice for data transformation in a pipeline.

Answer

Azure Monitor

Answer

Azure Storage

Answer

Azure Event Hubs

Question 2

Refer to the exhibit. You are deploying an Azure Synapse Analytics dedicated SQL pool using the provided ARM template snippet. After deployment, you need to adjust the performance level to DW200c to handle increased workload. Which parameter should you modify?

Accepted Answer

sku.name. Sku.name. In an Azure Synapse Analytics dedicated SQL pool, the performance level (e.g., DW200c) is defined by the 'sku.name' parameter in the ARM template. Option A (storageAccountType) controls data redundancy, not performance. Option B (maxSizeBytes) sets the maximum storage size. Option C (collation) determines the default collation for the database, which affects language support. Therefore, to adjust performance to DW200c, modify the sku.name parameter.

Answer

storageAccountType

Answer

maxSizeBytes

Answer

collation

Question 3

Refer to the exhibit. You are reviewing an Azure Stream Analytics job query. The job has a stream input and a reference data input. The job is failing with the error 'Reference data input must be of type Reference, not Stream'. What is the cause of the error?

Accepted Answer

The input type is Stream; it should be Reference.. The error 'Reference data input must be of type Reference, not Stream' explicitly indicates that the input configured as Stream should be set to Reference type. In Azure Stream Analytics, reference data inputs are used for static lookup data, and they must be declared as Reference input type. Setting the input type to Stream when it should be Reference causes this error. Therefore, option C is correct. Option D is incorrect because the error message does not relate to output configuration; it is strictly about input type mismatch.

Answer

The input alias is incorrect.

Answer

The JOIN syntax is incorrect for reference data.

Answer

The output type is set to ReferenceData; it should be a different type.

Question 4

Your organization uses Azure Synapse Analytics dedicated SQL pool to store sales data. You need to design a data loading process for a nightly batch that inserts new rows and updates existing rows based on the business key. The table has a clustered columnstore index. Which approach minimizes table fragmentation?

Accepted Answer

Create a staging table, load data, then use CTAS and partition switching to replace the target partition.. Using a staging table with CREATE TABLE AS SELECT (CTAS) and then switching partitions replaces the entire partition without individual row modifications, which minimizes fragmentation in a clustered columnstore index. Option A (UPDATE and INSERT) is wrong because individual updates cause columnstore fragmentation. Option B (DELETE and INSERT in a single transaction) also involves row-level changes that fragment columnstore. Option C (MERGE) is wrong because MERGE operations on columnstore indexes cause significant fragmentation due to row-by-row modifications.

Answer

Use UPDATE for existing rows and INSERT for new rows.

Answer

Use DELETE and INSERT statements in a single transaction.

Answer

Use a MERGE statement to perform upserts.

Question 5

You are designing a data processing pipeline in Azure Synapse Analytics that reads streaming data from Azure Event Hubs, performs aggregations in real time, and writes results to Azure Cosmos DB for a dashboard. The data volume is 10,000 events per second with 2 KB each. The latency requirement is under 5 seconds from event ingestion to dashboard visibility. Which technology should you use for the real-time aggregation?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a fully managed, real-time analytics service designed specifically for low-latency stream processing. It can ingest data from Azure Event Hubs, perform windowed aggregations (e.g., tumbling, hopping, sliding windows) with sub-second latency, and output directly to Azure Cosmos DB, meeting the 5-second latency requirement for the dashboard.

Answer

Azure Synapse Spark with Structured Streaming

Answer

Azure Data Factory mapping data flows

Answer

Azure Synapse dedicated SQL pool with T-SQL queries

Question 6

Your team is developing a data processing solution in Azure Synapse Analytics. You need to ensure that the solution can automatically scale compute resources based on workload demand for serverless SQL pools. Which feature should you configure?

Accepted Answer

Enable auto-resume and auto-pause on the serverless SQL pool endpoint. Serverless SQL pools in Azure Synapse Analytics automatically scale compute resources based on workload demand without requiring any configuration. None of the provided options enable this automatic scaling. Auto-resume and auto-pause only control the pool's active state, not its compute size. Dedicated SQL pool features like auto-scaling, cache sizing, and workload classification do not apply to serverless pools.

Answer

Set a cache size for the serverless SQL pool

Answer

Configure a dedicated SQL pool with auto-scaling

Answer

Use workload classification to assign resources

Question 7

Your company uses Azure Data Lake Storage Gen2 as a data lake. You need to process CSV files that arrive in a 'raw' container, transform them into Parquet format, and write them to a 'curated' container. The transformation includes filtering out rows with null values in the 'customer_id' column and adding a partition column 'year' based on the 'order_date'. You use Azure Synapse Pipelines. Which activity should you use for the transformation?

Accepted Answer

Data flow activity. The Data Flow activity in Azure Synapse Pipelines is designed for code-free, visual data transformations at scale. It can directly read CSV files from the 'raw' container, filter out rows with null 'customer_id' values using a conditional split or filter transformation, derive a 'year' column from 'order_date' using a derived column transformation, and write the results as Parquet files to the 'curated' container—all without writing code. This makes it the optimal choice for this ETL scenario.

Answer

Stored procedure activity

Answer

Notebook activity with PySpark

Answer

Copy data activity

Question 8

You are building a batch processing solution in Azure Synapse Analytics that reads data from a dedicated SQL pool, applies complex transformations using Synapse Spark, and writes the results back to the dedicated SQL pool. The pipeline must run on a schedule and handle transient failures with retries. Which approach should you use?

Accepted Answer

Use Azure Synapse Pipelines with a Notebook activity that runs Spark code. Azure Synapse Pipelines with a Notebook activity is the correct approach because it natively integrates Synapse Spark for complex transformations and supports scheduling and retry policies for transient failures. This allows you to read from a dedicated SQL pool, process data in Spark, and write back to the pool without external orchestration, leveraging the built-in pipeline reliability features.

Answer

Use Azure Batch with a custom application to run Spark jobs

Answer

Use Azure Functions to trigger Spark jobs on demand

Answer

Use Azure Databricks with Auto Loader and Delta Live Tables

Question 9

Your team is developing a real-time data processing solution using Azure Stream Analytics. The input is from Azure Event Hubs. The output must be written to Azure SQL Database. You need to ensure that the processing guarantees exactly-once semantics for the output. Which output configuration should you use?

Accepted Answer

Set output mode to 'Exactly once'. Azure Stream Analytics supports exactly-once semantics for output to Azure SQL Database when the output mode is set to 'Exactly once'. This ensures that each event is processed and written exactly once, preventing duplicates even in the event of failures or retries. The 'Exactly once' mode uses internal checkpointing and transactional writes to guarantee this behavior.

Answer

Set output mode to 'When possible'

Answer

Set output mode to 'At least once'

Answer

Set output mode to 'Best effort'

Question 10

You are designing a data processing pipeline in Azure Data Factory that ingests data from an on-premises SQL Server database to Azure Data Lake Storage Gen2. The data volume is large (500 GB). The network connection between on-premises and Azure is limited to 100 Mbps. You need to minimize the time to transfer the initial full load while ensuring data integrity. Which approach should you recommend?

Accepted Answer

Use Azure Data Box to copy the data offline. Azure Data Box physically ships the data, bypassing network bandwidth limitations for large initial loads. Option A is wrong because it would take over 11 hours even at full bandwidth, and network may not be stable. Option B is wrong because it compresses but still uses network. Option C is wrong because VPN adds overhead.

Answer

Use Azure Data Factory copy activity with parallel connections

Answer

Use Azure ExpressRoute to increase bandwidth

Answer

Compress the data using GZip and use copy activity

Question 11

Your organization uses Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. You notice that queries are slow when filtering on a date column. You need to improve query performance without increasing costs. What should you do?

Accepted Answer

Partition the data by date in the data lake (e.g., folder structure: /year=*/month=*/day=*). Partitioning the data by date in the data lake (e.g., /year=*/month=*/day=*) allows the serverless SQL pool to leverage partition elimination. When querying with a filter on the date column, the pool can read only the relevant partitions (folders) instead of scanning all Parquet files, drastically reducing I/O and improving query performance at no additional cost.

Answer

Increase the maximum query concurrency limit

Answer

Provision a dedicated SQL pool with more DTUs

Answer

Create a clustered columnstore index on the date column

Question 12

Your team is developing a data processing solution that uses Azure Databricks to transform streaming data from Azure Event Hubs. The transformation includes joining the stream with a static reference table stored in Azure Data Lake Storage Gen2. You need to implement the join efficiently. Which approach should you use?

Accepted Answer

Use a broadcast join with the static DataFrame loaded from Delta Lake. When joining a streaming DataFrame with a static reference table, Spark Structured Streaming can optimize the join by broadcasting the static DataFrame to all nodes, avoiding shuffles. This is efficient since the reference data is small and static. Option A is wrong because watermarks are used for stream-stream joins, not streaming-static joins. Option C is wrong because foreachBatch with a batch join adds unnecessary complexity and does not leverage built-in optimizations. Option D is wrong because converting a static table to a stream is not appropriate; the reference table is static and should be read as a batch DataFrame.

Answer

Use a watermark on both sides and perform a stream-stream join

Answer

Use foreachBatch to micro-batch the stream and perform a batch join

Answer

Use a stream-stream join by converting the static table to a stream

Question 13

Which TWO actions can you take to optimize the performance of a dedicated SQL pool in Azure Synapse Analytics when loading large volumes of data?

Accepted Answer

Use ROUND_ROBIN distribution for the staging table. Using ROUND_ROBIN distribution for a staging table ensures that data is evenly distributed across all distributions without any data movement, which is the fastest way to load data into a dedicated SQL pool. This distribution type is ideal for staging tables because it minimizes load time and avoids the overhead of hash distribution key computation during the initial data ingestion phase.

Answer

Create nonclustered indexes on all columns of the target table

Answer

Set the row group size to 100,000 rows for optimal compression

Answer

Enable change tracking on the target table

Question 14

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, B, and D 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). Azure Event Grid (option D) enables event-driven triggers for the pipeline. Option C (Azure Batch) is wrong because it is designed for large-scale parallel computing, not event-driven triggers. Option E (Azure Logic Apps) is wrong because it is more for workflow orchestration with connectors, not custom code execution.

Answer

Azure Batch

Answer

Azure Logic Apps

Question 15

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. Enabling 'Allow schema drift' in the source transformation tells Azure Data Factory (ADF) mapping data flows to dynamically accept incoming columns that are not defined in the schema at design time. This is the primary built-in mechanism for handling schema drift without manual intervention, as it automatically propagates new columns through the data flow.

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