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

Question 1

Your organization uses Azure Data Lake Storage Gen2 (ADLS Gen2) and wants to transform data using Azure Databricks. The data is stored in Parquet format. You need to read the data into a Spark DataFrame. Which DataFrame reader method should you use?

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

Accepted Answer

spark.read.parquet(). The data is stored in Parquet format, and the Spark DataFrame reader method `spark.read.parquet()` is specifically designed to read Parquet files, which is a columnar storage format optimized for big data processing in Azure Databricks.

Answer

spark.read.avro()

Answer

spark.read.json()

Answer

spark.read.csv()

Question 2

Which TWO components are required to set up a streaming data pipeline using Azure Synapse Analytics? (Select two.)

Accepted Answer

Azure Event Hubs. To set up a streaming data pipeline in Azure Synapse Analytics, you need a streaming ingestion source and a processing engine. Azure Event Hubs (Option B) is the correct ingestion service for real-time streaming data. Azure Synapse Pipelines (or Spark) (Option E) provides the processing engine to transform and analyze the streaming data. Azure Data Factory (Option A) is primarily for batch data integration, not streaming. Azure Analysis Services (Option C) is for OLAP modeling, not streaming. Azure Blob Storage (Option D) is a storage destination, not a required component for streaming ingestion or processing.

Answer

Azure Data Factory

Answer

Azure Analysis Services

Answer

Azure Blob Storage

Question 3

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 4

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. In an Azure Synapse Analytics dedicated SQL pool ARM template, the performance level (e.g., DW100c, DW200c, DW300c) is specified in the sku.name property of the Microsoft.Synapse/workspaces/sqlPools resource. To change the performance level to DW200c, you modify sku.name. The other parameters control storage redundancy, maximum size, and collation, none of which affect the compute performance level.

Answer

storageAccountType

Answer

maxSizeBytes

Answer

collation

Question 5

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 6

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.. CTAS with partition switching is the recommended pattern for dedicated SQL pools because it writes new data into a new distribution/partition and swaps it in via ALTER TABLE ... SWITCH, avoiding row-by-row UPDATE/DELETE operations that create fragmentation and delta-store bloat on clustered columnstore indexes. Because the target partition is replaced atomically, the clustered columnstore index remains well-compressed with minimal deleted-row overhead.

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 7

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

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. A broadcast join is the most efficient approach when joining a streaming DataFrame with a static reference table. The static table is loaded as a DataFrame from Delta Lake, and Spark broadcasts it to all executors, avoiding a shuffle of the large streaming data. This is ideal because the static table is typically small enough to fit in memory, and it eliminates the need for watermarking or state management required in stream-stream joins.

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 11

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. Option B is correct because a ROUND_ROBIN distributed staging table spreads incoming rows evenly across all distributions without requiring a distribution key, which maximizes parallel ingestion throughput and avoids data movement during the load before the data is redistributed into the final table. Option E is correct because CTAS performs a parallel, minimally logged bulk operation that creates a new table with the desired distribution and indexing in one step, and combining it with partition switching lets you swap the fully loaded table into the target quickly and efficiently. Option A is not appropriate because nonclustered indexes on every column slow down bulk loads and consume extra storage; dedicated SQL pools rely primarily on clustered columnstore indexes. Option C is wrong because the optimal row group size for columnstore compression is around 1,048,576 rows (1 million), not 100,000, which yields smaller, less efficient row groups. Option D is incorrect because change tracking is used for incremental data synchronization scenarios and does not improve bulk load performance.

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 12

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. Option A is correct because enabling 'Allow schema drift' on the source transformation in a mapping data flow lets the flow read columns that are not defined in the dataset schema at design time, so newly arriving columns flow through instead of being dropped. Option C is correct because column pattern matching (rule-based mapping) lets you define rules such as name or type patterns so that columns with similar names are automatically mapped without editing the flow for every new column. Option B is not appropriate because manually adding a derived column for each new column defeats the purpose of automated drift handling and requires redeployment for every schema change. Option D is wrong because assertion rules validate row conditions and can fail or reject rows, but they do not adapt the flow to new or renamed columns. Option E is wrong because a fixed schema mapping ignores unknown columns, which is the opposite of handling schema drift.

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 13

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

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 15

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

Increase the service level objective (DWU) of the dedicated SQL pool.. Increasing the service level objective (DWU) of the dedicated SQL pool provides more compute resources, directly reducing the runtime of compute-heavy batch jobs. Since the pool runs for fewer hours, the total cost (DWU × hours) may remain the same or even decrease, meeting the requirement to reduce runtime without increasing cost. Materialized views (A) and result-set caching (D) benefit repeated queries, not a unique nightly job. Workload management (C) only prioritizes resources, without increasing overall compute power.

Answer

Create materialized views on frequently queried tables.

Answer

Implement workload management to prioritize the job.

Answer

Enable result-set caching on the dedicated SQL pool.