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

Question 1

You are designing a data processing solution in Azure Synapse Analytics. The solution must use a dedicated SQL pool to store fact and dimension tables. The fact table is expected to have billions of rows. Which distribution strategy should you recommend for the fact table to optimize query performance and minimize data movement?

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

Accepted Answer

Hash distribution on a column that is frequently used in joins and aggregations.. Hash distribution on a column frequently used in joins and aggregations is the best choice for a fact table with billions of rows in a dedicated SQL pool. It distributes rows across distributions based on a hash of the distribution column, ensuring that rows with the same key value are co-located on the same distribution. This minimizes data movement during joins and aggregations, as the data required for these operations is already local to each distribution, significantly improving query performance.

Answer

Round-robin distribution.

Answer

Partitioned table with a partition key.

Answer

Replicated distribution.

Question 2

Refer to the exhibit. You are deploying an Azure Synapse Analytics workspace using an ARM template. The template defines a managed virtual network integration runtime. You need to ensure that the integration runtime can run mapping data flows with a time-to-live (TTL) of 10 minutes. What is the purpose of the 'timeToLive' property in this configuration?

Accepted Answer

It defines how long the cluster will be kept alive after a data flow completes, allowing subsequent data flows to reuse the cluster.. The 'timeToLive' property in an Azure Synapse Analytics managed virtual network integration runtime controls how long the cluster remains alive after a mapping data flow completes. By setting a TTL of 10 minutes, subsequent data flows can reuse the same warm cluster, avoiding the 5–10 minute cold start time for new clusters. This optimizes performance and reduces latency for consecutive data flow executions.

Answer

It sets the timeout for the integration runtime to connect to the data sources.

Answer

It specifies the maximum duration a data flow activity can run before timing out.

Answer

It determines the maximum number of concurrent data flows that can run on the cluster.

Question 3

You are designing a near-real-time data processing solution for a retail company. The source is a Kafka cluster on-premises. The target is an Azure Synapse Dedicated SQL Pool. The solution must handle up to 10,000 events per second with less than 5-minute latency. Which Azure service should you use to ingest the data?

Accepted Answer

Azure Event Hubs (with Kafka protocol support). Azure Event Hubs with Kafka protocol support is the correct choice because it provides a fully managed, high-throughput data ingestion service that can handle up to 10,000 events per second with sub-second latency, and it natively supports the Kafka protocol, allowing direct integration with your on-premises Kafka cluster without custom code or additional gateways. This meets the near-real-time requirement (<5-minute latency) and scales to the specified throughput.

Answer

Azure Data Lake Storage Gen2

Answer

Azure IoT Hub

Answer

Azure Stream Analytics

Question 4

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline uses a self-hosted integration runtime and runs successfully during business hours. However, after a recent network security update, the pipeline fails with a connection error to the on-premises SQL Server. What is the most likely cause?

Accepted Answer

A firewall rule on the on-premises SQL Server is blocking the self-hosted integration runtime.. The self-hosted integration runtime (SHIR) connects to on-premises SQL Server via TCP port 1433 by default. A recent network security update likely added a firewall rule on the SQL Server or the on-premises network that blocks outbound or inbound traffic on this port, preventing the SHIR from establishing a connection. Since the pipeline ran successfully before the update, the most probable cause is a new firewall restriction targeting the SHIR's IP address or subnet.

Answer

The Azure Blob Storage account has been moved to a different subscription.

Answer

The self-hosted integration runtime node has been de-registered from Azure Data Factory.

Answer

The blob container has reached its maximum capacity.

Question 5

Your company runs a streaming job in Azure Stream Analytics that ingests data from Event Hubs and outputs to Azure Synapse Analytics. The job is failing with a 'Watermark delay' alert and the output to Synapse is delayed by over 30 minutes. The input rate is 5,000 events per second. The job uses a 1-minute tumbling window. What is the most likely cause of the delay?

Accepted Answer

The Stream Analytics job is under-provisioned in terms of Streaming Units (SUs).. A watermark delay alert in Azure Stream Analytics indicates that the job is falling behind in processing incoming data. With an input rate of 5,000 events per second and a 1-minute tumbling window, the job requires sufficient Streaming Units (SUs) to keep up. Under-provisioned SUs cause backpressure, leading to output delays exceeding 30 minutes.

Answer

The output schema in Synapse does not match the Stream Analytics output.

Answer

The Event Hubs has a large number of late-arriving events.

Answer

The tumbling window size is too large.

Question 6

You have a dedicated SQL pool in Azure Synapse that stores a fact table with over 100 billion rows. Query performance is degrading over time. You notice that the table is hash-distributed on a column with many duplicate values. What is the most likely impact?

Accepted Answer

Data is unevenly distributed across distributions, causing some distributions to be overloaded.. D is correct because a hash-distributed table with a column that has many duplicate values leads to data skew. When the hash function maps many rows to the same distribution, some distributions become overloaded with data while others are underutilized. This imbalance causes query performance to degrade as the overloaded distributions become bottlenecks for processing.

Answer

Statistics on the table are outdated.

Answer

The table is not properly partitioned.

Answer

Data compression is not working efficiently.

Question 7

You are designing a data processing solution in Azure Synapse Analytics. The solution must use a dedicated SQL pool to store fact and dimension tables. The fact table is expected to have billions of rows. Which distribution strategy should you recommend for the fact table to optimize query performance and minimize data movement?

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

Accepted Answer

Column-level security. Column-level security (CLS) in Azure Synapse Analytics allows you to restrict access to specific columns in a table, such as credit card numbers, by granting or denying SELECT permissions on those columns. This directly meets the GDPR requirement to limit exposure of sensitive personal data to authorized users only, without affecting access to other columns.

Answer

Row-level security (RLS)

Answer

Dynamic data masking

Answer

Microsoft Defender for Cloud

Question 8

You are processing streaming data from IoT devices using Azure Stream Analytics. The data includes temperature readings and device IDs. You need to calculate the average temperature per device over a 5-minute window, sliding every 1 minute. Which window function should you use?

Accepted Answer

Hop window. A Hop window in Azure Stream Analytics allows you to specify a window size (5 minutes) and a hop size (1 minute), creating overlapping windows that slide forward every minute. This matches the requirement to calculate the average temperature per device over a 5-minute period, recalculated every minute, as the hop window outputs results at each hop interval while retaining data across overlapping windows.

Answer

Session window

Answer

Sliding window

Answer

Tumbling window

Question 9

Which TWO Azure services can be used to perform real-time data processing on streaming data?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics (B) is purpose-built for real-time stream processing: it ingests continuous data from sources such as Azure Event Hubs, IoT Hub, or Blob Storage and runs SQL-like queries with temporal windows to produce low-latency outputs. Azure Databricks (E) also supports real-time processing because its Structured Streaming engine (built on Apache Spark) can continuously process streaming data from Event Hubs, Kafka, or IoT Hub with exactly-once semantics. Azure Synapse Analytics dedicated SQL pool (A) is a batch-oriented MPP data warehouse, not a real-time streaming engine, so it does not fit. Azure Data Factory (C) is an orchestration/ETL service for scheduled batch data movement and transformation, not continuous stream processing. Azure Logic Apps (D) is a workflow automation and integration service triggered by events, but it does not perform real-time analytical stream processing.

Answer

Azure Synapse Analytics (dedicated SQL pool)

Answer

Azure Data Factory

Answer

Azure Logic Apps

Question 10

You are reviewing a Spark job definition in Azure Synapse Analytics. The job aggregates sales data. The job runs successfully but takes longer than expected. You notice that dynamic allocation is disabled and the executor instances are fixed at 10. The cluster has a maximum of 20 nodes. What is the most likely reason for the slow performance?

Accepted Answer

The job cannot scale out beyond 10 executors because dynamic allocation is disabled.. With dynamic allocation disabled and executor instances fixed at 10, the Spark job cannot utilize additional cluster resources even though the cluster supports up to 20 nodes. This means the job is artificially constrained to 10 executors, limiting parallelism and causing slower performance despite available compute capacity.

Answer

The file path is incorrect, causing data read errors.

Answer

The job is not parallelized because of a single partition.

Answer

The executor memory is too low for the aggregation.

Question 11

You are designing a data pipeline in Azure Synapse Analytics to ingest data from Azure Blob Storage into a dedicated SQL pool. The source files are CSV with varying row lengths, and you need to ensure optimal performance for reads. Which file format and compression should you recommend?

Accepted Answer

Parquet with Snappy compression. Parquet with Snappy compression is optimal for dedicated SQL pools in Azure Synapse Analytics because Parquet is a columnar format that enables efficient predicate pushdown and column pruning, reducing I/O. Snappy provides fast compression/decompression with minimal CPU overhead, which is critical for high-throughput reads in a distributed MPP environment.

Answer

Avro with Deflate compression

Answer

CSV with Gzip compression

Answer

ORC with Zlib compression

Question 12

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline uses a self-hosted integration runtime. You notice that the copy activity fails intermittently with the error: 'Failure happened on 'Source' side. ErrorCode=SqlOperationFailed'. The on-premises SQL Server is under heavy load during business hours. What is the most likely cause?

Accepted Answer

The SQL Server is experiencing resource contention or timeout due to heavy load.. The error 'SqlOperationFailed' on the source side indicates that the SQL Server itself is failing to complete the query or data extraction operation. Under heavy load, the SQL Server may experience resource contention (CPU, memory, I/O) or reach query timeout thresholds, causing the copy activity to fail intermittently. This is consistent with the described scenario of heavy load during business hours.

Answer

The Azure Blob Storage account is throttling requests.

Answer

The authentication method to SQL Server is incorrect.

Answer

The self-hosted integration runtime is not connected to the network.

Question 13

You need to transform data in Azure Synapse Analytics using a language that supports procedural logic and error handling. Which option should you use?

Accepted Answer

T-SQL stored procedures. T-SQL stored procedures are the correct choice because they support procedural logic (e.g., IF/ELSE, loops, TRY/CATCH) and error handling within Azure Synapse Analytics dedicated SQL pools. This allows you to encapsulate complex data transformation logic, handle runtime errors gracefully, and manage transactions, which is not possible with declarative objects like views or external tables.

Answer

CREATE VIEW

Answer

PolyBase

Answer

CREATE EXTERNAL TABLE

Question 14

You are designing a data processing solution in Azure Databricks to transform streaming data from Azure Event Hubs. The data must be aggregated in 1-minute tumbling windows and written to Azure Synapse Analytics. Which Spark API should you use?

Accepted Answer

Structured Streaming. Structured Streaming is the correct choice because it provides native support for event-time-based aggregations, such as 1-minute tumbling windows, and integrates seamlessly with Azure Event Hubs as a streaming source and Azure Synapse Analytics as a streaming sink using the `foreachBatch` or `writeStream` API. It offers exactly-once semantics and automatic state management for windowed operations, which are essential for reliable streaming ETL.

Answer

RDD API

Answer

Spark Streaming (DStreams)

Answer

DataFrame API with batch processing

Question 15

You are designing a data processing solution that uses Azure Databricks to transform large datasets. You need to ensure that the processing is cost-effective and can scale to handle variable workloads. Which cluster configuration should you recommend?

Accepted Answer

Use an auto-scaling cluster with spot instances.. Auto-scaling clusters in Azure Databricks dynamically adjust the number of workers based on workload demands, ensuring cost-effectiveness by scaling down during low activity. Spot instances (Azure Spot VMs) further reduce costs by using unused Azure capacity at a significant discount, making this combination ideal for variable workloads where fault tolerance is acceptable.

Answer

Use a fixed-size cluster with premium tier.

Answer

Use a Photon-accelerated cluster with premium tier.

Answer

Use an interactive cluster with a large number of workers.