DP-203 Practice Test 35 — 15 Questions

Question 1

You are developing an Azure Databricks notebook that processes streaming data from Azure Event Hubs and writes to a Delta Lake table. The stream must handle late-arriving data up to 30 minutes old and ensure that aggregations are computed correctly even if events arrive out of order. You need to minimize state store size and avoid unbounded growth. Which combination of features should you use?

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

Accepted Answer

Use event-time processing with a watermark of 30 minutes and apply a windowed aggregation with a tumbling window of 10 minutes.. Using event-time processing with a watermark of 30 minutes ensures that late-arriving data up to 30 minutes old is processed correctly. A tumbling window of 10 minutes defines the aggregation intervals, and the watermark automatically cleans up state for windows that are older than the watermark, preventing unbounded state growth. This approach balances correctness and resource usage.

Answer

Use a tumbling window with a 30-minute watermark and configure the state store with a retention duration of 30 minutes.

Answer

Use event-time processing with a watermark of 30 minutes and apply a 'dropDuplicates' operation on the event ID within the watermark.

Answer

Use a sliding window with a 30-minute watermark and set the output mode to 'complete'.

Question 2

You are developing an Azure Synapse Analytics serverless SQL pool solution that queries Parquet files in Azure Data Lake Storage Gen2. Analysts run ad-hoc queries with predicates on a high-cardinality column named TransactionId, and each query scans the entire folder, causing high cost. You need to reduce the amount of data scanned per query without changing the file format. What should you do?

Accepted Answer

Create a partitioned external table where the folder structure is organized by a low-cardinality key and query only the relevant partitions.. Serverless SQL pool reduces cost by reading fewer bytes, and partition elimination is the primary lever for that when the underlying layout supports it. Organizing folders by a low-cardinality key and exposing it through a partitioned external table allows the engine to skip irrelevant directories. Predicates on a high-cardinality column cannot prune files because their values are scattered across every file.

Answer

Create an external table over the folder and enable statistics on the TransactionId column.

Answer

Create a partitioned table in a dedicated SQL pool and load the Parquet data into it.

Answer

Use OPENROWSET with a filepath() predicate to restrict the query to specific files.

Question 3

You maintain an Azure Stream Analytics job that reads from an Event Hubs input and writes to an Azure Synapse Analytics dedicated SQL pool. During peak load the job produces late-arriving events that are dropped, and downstream reports show missing rows. You must retain and process events that arrive after the watermark by up to several minutes without changing the input. What should you configure?

Accepted Answer

Set the event ordering policy out-of-order tolerance window to the required number of minutes.. Late-arriving events are governed by the event ordering policy, whose out-of-order tolerance window defines how long the job accepts events behind the current watermark. Setting the window to cover the observed delay lets those events participate in temporal queries, restoring the missing rows without touching the upstream Event Hubs configuration or scaling compute.

Answer

Change the output to use a partitioned write pattern with a batch size of one row.

Answer

Configure the Event Hubs input to use a consumer group with a longer retention period.

Answer

Increase the streaming units allocated to the job so more events are processed per second.

Question 4

You are creating an Azure Synapse Analytics pipeline that must copy data from an Azure SQL Database into a dedicated SQL pool. The destination table already exists and the pipeline must append new rows without truncating existing data. Which staging and load option should you configure in the Copy activity?

Accepted Answer

Set the sink write behavior to insert and use PolyBase or COPY statement staging. When the destination table already exists and new rows must be appended, the dedicated SQL pool sink uses the insert write behavior, which does not truncate. Pairing it with staging through PolyBase or the COPY statement gives the high-throughput bulk load path recommended for this connector pair, preserving existing rows while adding the incoming set.

Answer

Set the sink write behavior to truncate and rely on the pre-copy script to reinsert

Answer

Use a stored procedure sink that executes a MERGE statement against the destination table

Answer

Set the sink write behavior to upsert and provide a key column mapping

Question 5

You are using Azure Data Factory to copy data from an Azure SQL Database to an Azure Data Lake Storage Gen2 account. The copy activity is failing intermittently with a timeout error. You need to improve the throughput and reliability of the copy operation. What should you do?

Accepted Answer

Increase the degree of copy parallelism and enable staged copy.. Intermittent timeouts often occur when a single connection cannot sustain the load. Increasing the degree of copy parallelism opens more concurrent connections, distributing the load. Staged copy separates the read and write phases, reducing the chance of timeouts during the write to ADLS Gen2. Together, these settings improve both throughput and reliability for large-scale copies.

Answer

Configure the copy activity to use a single thread with a larger batch size.

Answer

Set the copy activity's fault tolerance to skip incompatible rows.

Answer

Change the source dataset to use a stored procedure that returns all rows at once.

Question 6

You have an Azure Databricks notebook that processes a large Delta table and must be orchestrated from Azure Data Factory on a schedule. The notebook accepts two parameters, the source path and a run date. You need the pipeline to pass these values at runtime and to surface notebook failures as pipeline failures. Which activity configuration should you use?

Accepted Answer

A Databricks Notebook activity with base parameters defined as key-value pairs bound to pipeline parameters.. The Databricks Notebook activity is the native orchestrator for running an existing notebook from Data Factory. Base parameters passed as key-value pairs become notebook widgets and can be bound to pipeline parameters, and the activity reports the notebook run outcome so a failed notebook fails the pipeline run as required.

Answer

An Azure Function activity that triggers the notebook through a Databricks personal access token.

Answer

A Web activity that calls the Databricks Jobs API to submit a one-time run with notebook parameters.

Answer

A Databricks Jar activity that reads parameters from environment variables set on the cluster.

Question 7

You are implementing a data processing solution in Azure Synapse Analytics using Spark pools. The solution reads Parquet files from Azure Data Lake Storage Gen2, performs transformations, and writes the results to a dedicated SQL pool. You need to optimize the write performance to the dedicated SQL pool. Which technique should you use?

Accepted Answer

Use the PolyBase connector with a staging location in Azure Blob Storage.. The PolyBase connector is the most efficient way to write large datasets from Azure Synapse Spark pools to a dedicated SQL pool. It stages the data in Azure Blob Storage or Data Lake Storage Gen2 and then uses PolyBase to load it in parallel, which is much faster than JDBC batch inserts. This approach minimizes the load on the SQL pool and leverages its bulk load capabilities.

Answer

Use the 'spark.sql.sources.partitionOverwriteMode' setting to overwrite partitions.

Answer

Use the JDBC connector with batch inserts and set the batch size to 10,000 rows.

Answer

Write the data to a Parquet file in Data Lake Storage Gen2 and then use a Synapse pipeline to load it.

Question 8

You are creating an Azure Data Factory data flow that transforms data from an Azure SQL Database. The data flow must filter rows based on a column value and then aggregate the results. Which transformation should you use first?

Accepted Answer

Filter transformation. Filtering rows before aggregation reduces the amount of data processed by the Aggregate transformation, improving performance and ensuring that only relevant rows are aggregated. The Filter transformation is designed to select rows based on a condition, making it the appropriate first step. Other transformations like Aggregate, Derived Column, or Join do not directly filter rows and would be less efficient or incorrect in this context.

Answer

Derived Column transformation

Answer

Join transformation

Answer

Aggregate transformation

Question 9

You are implementing a solution in Azure Databricks that reads from a Delta Lake table and writes to another Delta Lake table. The pipeline must process data incrementally and handle updates and deletes from the source. Which feature should you use to read the changes?

Accepted Answer

Change data feed (CDF) on the source Delta table.. Change data feed (CDF) is the correct feature because it records row-level changes in a Delta table and allows reading them as a stream. This enables incremental processing that includes inserts, updates, and deletes, ensuring the target table stays in sync. Other options either do not provide change data or are not designed for this purpose.

Answer

Auto Loader with schema inference.

Answer

Delta Lake's ACID transactions with optimistic concurrency.

Answer

Time travel using the versionAsOf option.

Question 10

You are designing an Azure Synapse Analytics pipeline that uses a Mapping Data Flow to transform data from Azure Data Lake Storage Gen2. The data flow must handle schema drift, where new columns can appear in the source files over time. You need to ensure that the new columns are automatically included in the sink output without modifying the data flow. What should you do?

Accepted Answer

Enable Allow schema drift on the source and sink, and use the sink's automatic mapping.. Schema drift in Mapping Data Flows requires enabling Allow schema drift on both the source and the sink. The source reads new columns at runtime, and the sink's automatic mapping writes them to the destination without predefined column mappings. Using derived columns, Select, or Flatten transformations does not automatically propagate unknown columns and would require manual changes when the schema evolves.

Answer

Enable Allow schema drift on the source transformation and use a derived column to map new columns.

Answer

Use a Select transformation to rename columns and enable schema drift on the sink only.

Answer

Use a Flatten transformation and enable schema drift on the source.

Question 11

You are building an Azure Data Factory pipeline that calls an external REST API returning a JSON array of records. The API paginates results using a 'nextLink' field in the response body, and the number of pages varies per run. You must ingest all pages into Azure Blob Storage in a single pipeline run. Which activity configuration should you use?

Accepted Answer

A Copy activity with an HTTP source and pagination rule set to 'NextLink' using the body field that contains the next page URL.. When a REST API advertises the next page through a body field such as nextLink, the HTTP connector's pagination rule must be set to that field. The Copy activity then requests each returned URL in turn until the field is missing, capturing a variable number of pages in one run without custom looping.

Answer

A Copy activity with an HTTP source and no pagination rule, relying on the API to return all records in one response.

Answer

A Web activity followed by a ForEach activity that iterates over a hardcoded page count.

Answer

A Copy activity with an HTTP source and a range-based pagination rule that increments a query parameter until no records are returned.

Question 12

You have an Azure Synapse Analytics workspace with a dedicated SQL pool. You need to create an external table that references Parquet files stored in Azure Data Lake Storage Gen2. The external table will be used for ad-hoc queries. Which statement correctly describes the required components?

Accepted Answer

You must create a database scoped credential, an external data source, and an external file format.. Creating an external table in a dedicated SQL pool requires three components: a database scoped credential for authentication, an external data source for the storage location, and an external file format for Parquet. These are essential for the pool to read the external data. Other options are either incomplete or describe different features.

Answer

You only need to create an external data source and an external file format.

Answer

You must create a PolyBase external table using the CREATE EXTERNAL TABLE AS SELECT (CETAS) statement.

Answer

You must use the OPENROWSET function instead of an external table.

Question 13

You have an Azure Synapse Analytics dedicated SQL pool that is used for reporting. You notice that the tempdb database is growing rapidly and causing queries to fail. Which two actions should you take to mitigate the issue? (Select two.)

Accepted Answer

Reduce the degree of parallelism (MAXDOP) for the workload.. The correct actions are C and E. Reducing MAXDOP lowers the degree of parallelism, which directly reduces the number of concurrent tempdb allocations per query, and optimizing queries that perform large sorts or hash joins minimizes the spills to tempdb that cause it to grow rapidly. Increasing DWU (B) scales compute resources but does not reduce tempdb consumption per query, and it can even increase parallelism. Enabling result-set caching (A) is not a feature of dedicated SQL pools in this context and does not address tempdb growth from sorts and hash joins. Moving tempdb to a separate storage account (D) is not supported in Azure Synapse dedicated SQL pools, as tempdb is managed by the service.

Answer

Enable result-set caching to reduce query reruns.

Answer

Increase the service level (DWU) of the dedicated SQL pool.

Answer

Move tempdb to a separate storage account.

Question 14

You are monitoring an Azure Synapse Analytics dedicated SQL pool and notice that some queries are experiencing high wait times due to concurrency slots being exhausted. You need to optimize the workload to reduce contention. Which three actions should you take? (Select three.)

Accepted Answer

Create workload groups with different importance levels.. In an Azure Synapse dedicated SQL pool, concurrency-slot exhaustion is best addressed by workload management: creating workload groups with different importance levels (B) lets critical queries get priority, workload isolation (C) caps the resources a group can consume so one workload cannot starve others, and workload classification (D) routes incoming queries into the correct workload group based on labels, login, or time. Together these three actions directly reduce contention for the fixed number of concurrency slots. Increasing DWU (A) adds resources but does not manage contention among competing queries, and result-set caching (E) improves repeated-read latency but does not relieve concurrency-slot pressure.

Answer

Increase the data warehouse service level (DWU).

Answer

Enable result-set caching for frequently executed queries.

Question 15

You are using Azure Synapse Analytics dedicated SQL pool to process large fact tables. You need to improve query performance for joins between a large fact table and a small dimension table. The dimension table is less than 2 GB. What should you do?

Accepted Answer

Replicate the dimension table to all distributions.. Replicating a small dimension table in a dedicated SQL pool ensures each distribution has a local copy, eliminating data movement during joins with large fact tables. Hash and round-robin distributions do not guarantee colocation, and columnstore indexes do not solve data movement.

Answer

Round-robin distribute the dimension table.

Answer

Create a clustered columnstore index on the dimension table.

Answer

Hash distribute the dimension table on the join key.