DP-203 Practice Exam 54 — 20 Questions

Question 1

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 2

You are designing a data processing solution for a global company. Data must be processed in near real-time and aggregated by region. You need to minimize latency for downstream consumers. Which Azure service should you use for stream processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a fully managed stream processing engine designed for near real-time analytics on high-volume data streams. It can ingest data from sources like Azure Event Hubs or IoT Hub, apply SQL-based transformations, and output aggregated results to sinks such as Azure Synapse or Power BI with sub-second latency, meeting the requirement for minimal downstream latency.

Answer

Azure Batch

Answer

Azure Data Factory

Answer

Azure Synapse Pipelines

Question 3

You are designing a data processing solution in Azure Synapse Analytics that uses serverless SQL pools to query Parquet files in Azure Data Lake Storage Gen2. The files are partitioned by year and month. You need to optimize query performance and reduce data scanned. What should you do?

Accepted Answer

Create views that filter on partition columns.. Option C is correct because serverless SQL pools in Azure Synapse Analytics support partition elimination only when queries use views or inline queries that explicitly filter on partition columns (e.g., year, month) in the WHERE clause. This allows the pool to skip scanning irrelevant partitions, reducing data scanned and improving performance. Creating views that encapsulate these filters ensures consistent partition pruning across queries.

Answer

Use CREATE EXTERNAL TABLE AS SELECT (CETAS) to create new external tables.

Answer

Use OPENROWSET with the DATA_SOURCE parameter.

Answer

Increase the number of files per partition.

Question 4

You need to orchestrate a data pipeline that includes a Python script and a Data Flow in Azure Synapse Analytics. The Python script must run before the Data Flow. Which activity should you use to run the Python script?

Accepted Answer

Notebook activity configured to use a Python kernel. A Notebook activity in Azure Synapse Analytics can be configured to use a Python kernel, allowing you to run a Python script directly within the pipeline. This is the correct choice because the requirement is to execute a Python script before a Data Flow, and the Notebook activity supports Python execution natively in Synapse pipelines.

Answer

Web activity

Answer

Stored Procedure activity

Answer

HDInsight Hive activity

Question 5

You have a pipeline in Azure Data Factory that copies data from on-premises SQL Server to Azure Blob Storage. The pipeline fails with a 'Connection timed out' error. You have already verified that the Integration Runtime is running and the SQL Server firewall allows connections from the Integration Runtime. What should you check next?

Accepted Answer

Check if the SQL Server is configured to allow remote connections and that TCP/IP is enabled. Option C is correct because the 'Connection timed out' error, despite the Integration Runtime being running and the firewall allowing connections, typically indicates that SQL Server is not listening on the expected TCP port. This often happens when TCP/IP is disabled in SQL Server Configuration Manager or remote connections are not enabled. Without TCP/IP enabled, the Integration Runtime cannot establish a network connection to the SQL Server instance, leading to a timeout.

Answer

Ensure the Integration Runtime is registered and online

Answer

Check if the Blob Storage endpoint is accessible from the Integration Runtime

Answer

Verify that the SQL Server login credentials are correct

Question 6

Your organization is using Azure Synapse Analytics dedicated SQL pool. You notice that queries are running slower than expected. Upon reviewing the execution plans, you see that some queries are performing table scans instead of seeks on large fact tables. What is the most likely cause?

Accepted Answer

The statistics on the tables are outdated or missing.. Outdated or missing statistics prevent the Azure Synapse Analytics dedicated SQL pool query optimizer from accurately estimating row counts and data distribution. Without reliable statistics, the optimizer may incorrectly choose a table scan over a more efficient index seek or partition elimination, leading to slower query performance on large fact tables.

Answer

The tables are distributed using round-robin distribution.

Answer

Result-set caching is disabled.

Answer

The resource class for the user is set to smallrc.

Question 7

You are using Azure Data Lake Storage Gen2 as the data lake for your organization. You need to process files in the 'incoming' folder using a scheduled Azure Databricks notebook. After processing, the files should be moved to the 'processed' folder. The files are large (up to 10 GB) and you want to minimize the time to move them. Which approach should you use?

Accepted Answer

Use the Azure Databricks dbutils.fs.mv() to move the file.. Option A is correct because `dbutils.fs.mv()` performs a metadata-only rename operation on Azure Data Lake Storage Gen2, which is instantaneous regardless of file size. This avoids any data movement, making it the fastest approach for moving large files (up to 10 GB) between folders within the same storage account.

Answer

Use Azure Data Factory with a Copy activity to move the file, then delete the source.

Answer

Change the file's metadata to update its directory path.

Answer

Use the Azure Databricks dbutils.fs.cp() to copy the file to the processed folder, then delete the original.

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 THREE components are required to implement a change data capture (CDC) solution in Azure Data Factory for incremental loading from Azure SQL Database to Azure Synapse Analytics?

Accepted Answer

A Lookup activity to retrieve the last processed watermark.. Option B is correct because a Lookup activity is required to retrieve the last processed watermark (e.g., a timestamp or LSN) from a control table or source query. This watermark defines the incremental boundary for CDC, ensuring only new or changed rows are loaded in each pipeline run. Without it, the pipeline would have no way to track progress and would either reload all data or miss changes.

Answer

A Mapping Data Flow to transform the data before load.

Answer

Azure Functions to process change data.

Question 10

You are designing a data processing solution in Azure Synapse Analytics. The solution must process streaming data from Azure Event Hubs and store the results in a dedicated SQL pool. You need to choose the most appropriate service for near real-time ingestion with minimal latency. What should you use?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is purpose-built for near real-time stream processing with sub-second latency, directly integrates with Azure Event Hubs as an input source and dedicated SQL pool as an output sink, and provides a SQL-like query language for defining transformations. This minimizes architectural complexity and latency compared to other services.

Answer

Azure Databricks with Structured Streaming

Answer

Azure Data Factory

Answer

Azure Functions with Event Hub trigger

Question 11

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 12

You are partitioning a large fact table in Azure Synapse Dedicated SQL Pool by date. The table is used for queries that filter on CustomerID and Date. You want to minimize data movement. Which distribution strategy should you use?

Accepted Answer

Hash distribution on CustomerID. Hash distribution on CustomerID is correct because queries filtering on CustomerID and Date will benefit from collocated joins and aggregations when CustomerID is the distribution key. Since the table is large and partitioned by Date, hash distribution on CustomerID minimizes data movement by ensuring that rows with the same CustomerID reside on the same distribution node, allowing filters on Date to be applied locally within each partition.

Answer

Round-robin distribution

Answer

Replicate distribution

Answer

Hash distribution on Date

Question 13

Which TWO of the following are required components to set up a data pipeline that uses Change Data Capture (CDC) to incrementally load data from SQL Server to Azure Synapse using Azure Data Factory?

Accepted Answer

CDC enabled on the source SQL Server database and tables. Option A is correct because Change Data Capture (CDC) must be enabled on the source SQL Server database and the specific tables you intend to track. Without CDC enabled, SQL Server will not generate the change tracking tables (e.g., cdc.<capture_instance>_CT) that Azure Data Factory’s CDC connector reads to identify inserts, updates, and deletes. This is a prerequisite for any incremental load using the native CDC mechanism in ADF.

Answer

A staging Azure Blob Storage account

Answer

A Lookup activity to get the last watermark

Answer

A stored procedure in the source database to capture changes

Question 14

You are designing a real-time analytics solution for IoT devices that emit telemetry data every second. The data must be aggregated every minute and stored in Azure SQL Database for historical analysis. You need to minimize latency and operational overhead. Which approach should you recommend?

Accepted Answer

Use Azure Stream Analytics with a tumbling window of 1 minute and output to Azure SQL Database. Option C is correct because Azure Stream Analytics natively supports real-time stream processing with tumbling windows, allowing you to aggregate IoT telemetry data every minute and output directly to Azure SQL Database with minimal latency. This approach avoids the overhead of managing clusters (Databricks) or orchestrating batch loads (Data Factory), directly meeting the requirement for low latency and operational simplicity.

Answer

Use Azure Databricks with Structured Streaming to aggregate and write to SQL Database

Answer

Use Event Hubs Capture to store raw data in blob storage, then use Azure Data Factory to load into SQL Database hourly

Answer

Use Azure Functions to process events and write to SQL Database

Question 15

You are optimizing a data pipeline in Azure Data Factory that uses a Copy activity to transfer data from an Azure SQL Database to a dedicated SQL pool in Azure Synapse Analytics. The source table has 500 million rows and the copy operation is taking too long. You need to reduce the copy duration. Which configuration change will have the most impact?

Accepted Answer

Enable staging and use PolyBase as the copy method for the sink.. Enabling staging with PolyBase as the copy method for the sink is the most impactful change because PolyBase leverages the massively parallel processing (MPP) architecture of Azure Synapse Analytics to load data in parallel directly into the dedicated SQL pool. This bypasses the single-threaded bottleneck of the standard INSERT-based copy method, dramatically reducing the time required to ingest 500 million rows.

Answer

Change the copy behavior to 'sequential' to reduce load on the source.

Answer

Increase the degree of copy parallelism (DOP) to the maximum value supported.

Answer

Split the source data into multiple smaller files and use multiple copy activities running in parallel.

Question 16

You are building a real-time dashboard to monitor user activity on a website. The data is ingested via Azure Event Hubs and must be aggregated every minute with a 30-second late-arrival tolerance. The aggregated results should be stored in Azure Cosmos DB for low-latency reads. Which Azure service should you use to perform the windowed aggregation?

Accepted Answer

Azure Stream Analytics with a tumbling window of 1 minute and a late-arrival policy of 30 seconds.. Azure Stream Analytics is the correct choice because it natively supports windowed aggregations (tumbling, hopping, sliding, session) and allows you to define a late-arrival policy to handle out-of-order events. A tumbling window of 1 minute with a late-arrival tolerance of 30 seconds meets the requirement exactly, and the output can be directly written to Azure Cosmos DB for low-latency reads.

Answer

Azure Functions triggered by Event Hubs to aggregate data and write to Cosmos DB.

Answer

Azure Databricks with structured streaming and a sliding window.

Answer

Azure Analysis Services to process streaming data directly from Event Hubs.

Question 17

You are designing a data processing pipeline in Azure Synapse Analytics that uses a mapping data flow with Azure Integration Runtime (IR). The pipeline runs slowly and you notice that the IR's data movement is limited by the number of cores. Which configuration should you adjust to improve performance?

Accepted Answer

Increase the 'Data Flow Compute Type' and 'Core Count' in the Azure IR settings.. The Azure Integration Runtime (IR) for mapping data flows uses a Spark cluster, and its performance is directly tied to the compute resources allocated. By increasing the 'Data Flow Compute Type' (e.g., from General Purpose to Memory Optimized) and the 'Core Count' (e.g., from 4 to 8 or 16 cores), you provide more parallel processing power, which directly addresses the core-limited data movement bottleneck.

Answer

Enable staging for the copy activity within the data flow.

Answer

Use a Self-Hosted IR instead of Azure IR for data flows.

Answer

Increase the 'Number of partitions' in the source transformation.

Question 18

You are building a data pipeline in Azure Data Factory to copy data from an on-premises SQL Server database to Azure Blob Storage. The pipeline must run daily and handle incremental updates. The on-premises SQL Server table has a LastModifiedDate column that is updated when a row changes. What is the most efficient way to implement incremental loads?

Accepted Answer

Use a Lookup activity to get the maximum LastModifiedDate from the destination, then use a Copy activity with a query that filters rows where LastModifiedDate > that value.. Option B is correct because it uses a Lookup activity to retrieve the maximum LastModifiedDate from the destination (Azure Blob Storage), then passes that value as a parameter to a Copy activity that queries only rows where LastModifiedDate exceeds it. This minimizes data transfer by reading only new or changed rows, and it avoids the overhead of enabling Change Data Capture or performing full loads, making it the most efficient approach for incremental loads in Azure Data Factory.

Answer

Enable Change Data Capture (CDC) on the SQL Server database and use an ADF mapping data flow to read changes.

Answer

Use a tumbling window trigger with a window size of 1 day and copy all data from the source each time.

Answer

Perform a full load every day and use a Delete activity to remove duplicates.

Question 19

Your team is developing a data processing solution using Azure Databricks. The data is stored in Delta Lake format in Azure Data Lake Storage Gen2. You need to ensure that when multiple jobs concurrently write to the same Delta table, the operations are atomic and consistent. Which Delta Lake feature should you use?

Accepted Answer

Rely on Delta Lake's built-in ACID transactions.. Delta Lake provides built-in ACID (Atomicity, Consistency, Isolation, Durability) transactions that guarantee atomic and consistent concurrent writes. When multiple jobs write to the same Delta table, Delta Lake uses a transaction log (stored as JSON files in the `_delta_log` directory) to serialize writes, ensuring that each write is either fully committed or rolled back, preventing partial updates or data corruption.

Answer

Enable Optimized Write on the Delta table.

Answer

Enable Auto Optimize on the Delta table.

Answer

Use Dynamic Partition Pruning in your Spark jobs.

Question 20

You are using Azure Stream Analytics to process real-time data from an Event Hub. Which TWO of the following are valid output sinks?

Accepted Answer

Azure Synapse Analytics. Azure Stream Analytics supports Azure Synapse Analytics as a native output sink, allowing you to write real-time streaming results directly into dedicated SQL pools for high-performance analytics. This is achieved via the built-in Azure Synapse Analytics output adapter, which uses PolyBase or COPY INTO for efficient bulk ingestion.

Answer

Azure Queue Storage

Answer

Azure Files

Answer

Azure Table Storage

DP-203 Practice Exam 54 — 20 Questions

Exhibit

DP-203 Practice Exam 54 — 20 Questions

Exhibit