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

Question 1

Which TWO strategies reduce data movement in Azure Synapse Analytics pipelines? (Choose two.)

Accepted Answer

Use serverless SQL pool to query data in place. Serverless SQL pools in Azure Synapse Analytics allow you to query data directly from files in Azure Data Lake Storage or other external sources without moving the data into a dedicated SQL pool. This eliminates data movement entirely by using the compute resources of the serverless pool to process queries in place, leveraging the OPENROWSET or CREATE EXTERNAL TABLE syntax to read data from its original location.

Answer

Use Data Flow to transform data before loading

Answer

Use Stored Procedure activity to insert data

Answer

Use Copy activity to move data from source to staging

Question 2

Which THREE actions improve the performance of a Delta table in Azure Databricks? (Choose three.)

Accepted Answer

Run VACUUM to remove old versions and compact small files. Running VACUUM on a Delta table removes old file versions that are no longer referenced by the Delta transaction log, freeing up storage and reducing the number of small files that must be read during queries. This compaction of small files improves I/O efficiency and query performance, especially in tables with frequent updates or deletes.

Answer

Disable auto-optimize to reduce overhead

Answer

Increase the number of shuffle partitions

Question 3

Refer to the exhibit. You have an Azure Data Factory pipeline that copies trade data from Azure Blob Storage to Azure SQL Database. The pipeline runs every hour and truncates the destination table before each copy. However, users report that data is missing during the copy window. What is the most likely cause?

Accepted Answer

The preCopyScript truncates the table before the copy completes, causing a period with no data. The preCopyScript truncates the destination table before the copy operation begins. During the copy window, if the copy takes time or fails, the table remains empty, causing users to see missing data. Option A is incorrect because the output dataset configuration does not cause this issue. Option B is incorrect because writeBatchSize affects performance but not data loss. Option D is incorrect because recursive setting affects source file selection, not data loss.

Answer

The output dataset is not correctly configured

Answer

The writeBatchSize is too low, causing timeouts

Answer

The source dataset is set to recursive, which includes unwanted files

Question 4

Refer to the exhibit. You created an external table in Azure Synapse Analytics serverless SQL pool to query Parquet files. Queries return no rows even though the files exist. What is the most likely issue?

Accepted Answer

The LOCATION path in the external table is relative to the data source, but the data source points to the wrong container or folder. The most likely issue is that the LOCATION path in the external table is relative to the data source. In the exhibit, the external data source points to the root container 'sales', and the external table LOCATION is 'parquet/sales/'. The combined path becomes 'sales/parquet/sales/', which is likely incorrect. The correct data source should point to the container containing the 'parquet' folder, or the LOCATION should be adjusted. Option A is incorrect because CREDENTIAL is already defined. Option B is incorrect because FILE_FORMAT is specified and valid for Parquet. Option C is incorrect because DATA_COMPRESSION is supported for Parquet.

Answer

The CREDENTIAL is missing

Answer

The FILE_FORMAT is incorrectly specified

Answer

The DATA_COMPRESSION is not supported for Parquet

Question 5

Refer to the exhibit. You have a mapping data flow in Azure Data Factory that aggregates sales data. The data flow runs successfully but the sink table contains only the total sum per run instead of per product. What is missing?

Accepted Answer

The aggregate transformation does not have a groupBy column. The aggregate transformation must include a groupBy column (e.g., ProductID) to produce per-product totals. Without a groupBy, the aggregate sums all rows into a single total per run. Option A is incorrect because filtering by date would not fix the lack of grouping. Option C is incorrect because a filter transformation is unrelated to aggregation grouping. Option D is incorrect because the sink append mode does not affect the aggregation logic.

Answer

The source dataset is not filtering by date

Answer

The data flow is missing a filter transformation

Answer

The sink dataset is not configured to append

Question 6

Your organization uses Azure Synapse Analytics. You need to design a data transformation pipeline that processes streaming data from Azure Event Hubs, performs aggregations over a 5-minute tumbling window, and loads the results into a dedicated SQL pool table. Which Azure service should you use to implement the streaming transformation?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the appropriate service for real-time stream processing with windowed aggregations. Option B is wrong because Azure Data Factory is for batch orchestration. Option C is wrong because Spark Structured Streaming is for big data workloads but less integrated with SQL pools. Option D is wrong because Azure Functions is not designed for streaming windowed aggregations.

Answer

Azure Data Factory

Answer

Apache Spark for Azure Synapse

Answer

Azure Functions

Question 7

You are building a data processing pipeline in Azure Synapse Analytics. The pipeline should read data from Azure Data Lake Storage Gen2 (Parquet files), apply transformations using a mapping data flow, and write the results to a dedicated SQL pool table. The source data contains personally identifiable information (PII). You need to mask the PII columns (e.g., email) using a data masking function within the data flow. Which transformation should you use?

Accepted Answer

Derived Column transformation. The Derived Column transformation in mapping data flows allows you to create new columns or modify existing ones using expressions, including built-in data masking functions like `mask()`, `maskEmail()`, or `substring()`. This is the correct transformation to apply PII masking on columns such as email addresses within the data flow pipeline before writing to the dedicated SQL pool.

Answer

Join transformation

Answer

Aggregate transformation

Answer

Pivot transformation

Question 8

Your team is using Azure Data Factory to orchestrate a data pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline runs successfully during testing. However, after moving to production, you notice that the pipeline fails intermittently with connectivity errors. You need to ensure reliable data transfer. What should you implement?

Accepted Answer

Use Self-Hosted Integration Runtime. A Self-Hosted Integration Runtime provides a dedicated gateway for on-premises connectivity, enabling Azure Data Factory to access on-premises data sources reliably. Option A is wrong because Azure Integration Runtime cannot connect to on-premises networks. Option B is wrong because PolyBase is used for loading data into Azure Synapse Analytics, not for resolving connectivity issues. Option C is wrong because Azure VPN Gateway is a general VPN solution and does not directly address Data Factory connectivity to on-premises sources.

Answer

Use Azure Integration Runtime (IR)

Answer

Use PolyBase for data transfer

Answer

Configure Azure VPN Gateway

Question 9

You are optimizing an Azure Synapse Analytics dedicated SQL pool. A fact table named Sales is partitioned by date and has a hash-distributed column ProductKey. You notice that queries filtering on OrderDate are performing poorly. You need to improve query performance for date range filters. What should you do?

Accepted Answer

Ensure the table is partitioned on the same column used in WHERE clause (OrderDate) and enable partition elimination.. Partitioning the Sales table on OrderDate enables partition elimination, which allows the query optimizer to scan only the relevant partitions when filtering by date ranges. This reduces I/O and improves performance significantly, as the fact table is already hash-distributed on ProductKey, and partitioning on a different column used in WHERE clauses is a best practice in Azure Synapse Analytics dedicated SQL pools.

Answer

Create a non-clustered index on OrderDate.

Answer

Add ProductKey as a distribution column alongside the existing hash.

Answer

Change the distribution to round-robin to spread data evenly.

Question 10

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?

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 11

You are developing a real-time data processing solution using Azure Stream Analytics. The input is from an Azure Event Hub, and the output is to an Azure Synapse Analytics dedicated SQL pool. You need to ensure exactly-once delivery semantics to the SQL pool. What should you configure?

Accepted Answer

Use the Azure Synapse Analytics output adapter with exactly-once semantics.. The Azure Synapse Analytics output adapter for Stream Analytics supports exactly-once delivery semantics to a dedicated SQL pool. This is achieved through built-in mechanisms that ensure each event is written exactly once, even in failure scenarios. Option A is incorrect because checkpointing is used for job recovery and state management, not for guaranteeing exactly-once output delivery. Option C is incorrect because custom deserializers handle data format conversion, not delivery guarantees. Option D is incorrect because event ordering by timestamp helps with time-based processing but does not provide delivery semantics like exactly-once.

Answer

Enable checkpointing in the query.

Answer

Use a custom deserializer.

Answer

Configure event ordering by timestamp.

Question 12

You are working with a Delta Lake table in Azure Databricks. The table is updated frequently with new data and occasionally with updates to existing rows. You need to optimize read performance for queries that filter on a specific date column. The table is partitioned by date. Which optimization technique should you apply?

Accepted Answer

Run ZORDER BY on the date column.. ZORDER BY on the date column co-locates related data within each partition, significantly reducing the amount of data scanned for queries that filter on that column. This is especially beneficial for a frequently updated Delta table where data is already partitioned by date, as ZORDER BY optimizes the layout within partitions without changing the partition scheme.

Answer

Run OPTIMIZE on the table.

Answer

Run ANALYZE STATISTICS on the table.

Answer

Run VACUUM to clean up old versions.

Question 13

Your organization has an Azure Data Factory pipeline that executes a series of activities to transform data. One of the activities is an Azure Databricks notebook that should run only if the previous activity succeeds. You need to configure the pipeline to handle failures gracefully and send an email alert if the Databricks activity fails. What should you do?

Accepted Answer

Add a failure output path from the Databricks activity to a Web activity that calls an email API.. You can configure a failure output path from the Databricks activity and attach a Web activity that calls an email API to send an alert when the Databricks activity fails. Option B is incorrect because retry and timeout settings do not send email alerts. Option C is incorrect because a Schedule trigger does not handle activity-level failures directly. Option D is incorrect because setting a dependency to 'Succeeded' only runs subsequent activities on success, not on failure.

Answer

Configure a retry policy and a timeout for the Databricks activity.

Answer

Use a Schedule trigger to run the pipeline and check for failures using Azure Monitor.

Answer

Set a dependency condition on the Databricks activity to 'Succeeded' and add a Send Email activity on the success path.

Question 14

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 15

Which THREE considerations are important when designing a data processing solution using Azure Databricks for ETL workloads? (Select three.)

Accepted Answer

Auto-scaling of compute resources. Auto-scaling of compute resources ensures that the cluster can adjust to workload demands, optimizing cost and performance. Option B is correct because Delta Lake provides ACID transactions, schema enforcement, and optimization features that enhance data reliability in ETL pipelines. Option E is correct because proper cluster configuration and autoscaling are essential for managing resources efficiently. Option C is incorrect because Azure Databricks uses a pay-as-you-go model, not a fixed pricing model, making it more flexible. Option D is incorrect because minimum network bandwidth is not a typical design consideration for Databricks ETL workloads, as network performance is generally sufficient.

Answer

Fixed pricing model

Answer

Minimum network bandwidth