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

Question 1

You are implementing a solution in Azure Synapse Analytics to process data using Spark pools. The data is stored in Parquet files in Azure Data Lake Storage Gen2. You need to create an external table in a serverless SQL pool that points to this data. Which T-SQL statement should you use?

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

Accepted Answer

CREATE EXTERNAL TABLE ... WITH (LOCATION = 'abfss://container@storage.dfs.core.windows.net/path', DATA_SOURCE = ...). The correct statement is CREATE EXTERNAL TABLE with LOCATION pointing to the ADLS Gen2 path using the abfss:// protocol and a DATA_SOURCE that references a credential. This creates a metadata object in the serverless SQL pool that can be queried like a regular table. It is the standard method for exposing Parquet data in ADLS Gen2 to serverless SQL pools.

Answer

CREATE VIEW ... AS SELECT * FROM OPENROWSET(BULK 'path', FORMAT='PARQUET')

Answer

CREATE TABLE ... WITH (DISTRIBUTION = HASH(column), LOCATION = ...)

Answer

CREATE EXTERNAL TABLE ... WITH (LOCATION = 'wasbs://container@storage.blob.core.windows.net/path')

Question 2

You are developing an Azure Synapse Analytics dedicated SQL pool solution that ingests data from Parquet files in Azure Data Lake Storage Gen2. The data must be loaded into a clustered columnstore index table. You need to maximize query performance and minimize load time. Which loading method should you use?

Accepted Answer

Use the COPY statement to load Parquet files directly into the table. The COPY statement is purpose-built for high-performance bulk loading from Azure Data Lake Storage Gen2 into dedicated SQL pools, with native support for Parquet and clustered columnstore indexes. It avoids the extra steps and format limitations of bcp, PolyBase with CTAS, or ADF staging.

Answer

Use Azure Data Factory with a Copy activity that uses a staging table and then inserts into the target table

Answer

Use PolyBase with an external table pointing to the Parquet files and then CTAS

Answer

Use bcp to import the Parquet files into the table

Question 3

You are building an Azure Stream Analytics job that processes telemetry from Azure IoT Hub. The job must use a JavaScript user-defined function to convert a raw temperature reading from Celsius to Fahrenheit for each event. You need to ensure the UDF is available to the query. What should you do?

Accepted Answer

Define the JavaScript user-defined function in a separate .js file and upload it to the Stream Analytics job's blob storage container, then reference it in the query with the UDF name.. Stream Analytics supports JavaScript user-defined functions that are uploaded from a .js file to a storage account linked to the job. The function is then available in the query. Inline definitions, Azure SQL functions, and Azure Functions are not supported for this purpose.

Answer

Write the JavaScript function directly inside the Stream Analytics query editor and prefix it with the keyword FUNCTION.

Answer

Register the JavaScript function as a scalar function in an Azure SQL Database and call it from the Stream Analytics query using a cross-database reference.

Answer

Create an Azure Function that performs the conversion and invoke it from the Stream Analytics query using the JavaScript UDF syntax.

Question 4

You have an Azure Stream Analytics job that reads from an event hub and writes to an Azure Synapse Analytics dedicated SQL pool. The job must compute a 5-minute tumbling window aggregation over incoming events. The dedicated SQL pool is currently under heavy load and can only accept writes every 10 minutes. You need to ensure that the Stream Analytics job does not overwhelm the dedicated SQL pool while still producing accurate 5-minute aggregations. What should you do?

Accepted Answer

Write the aggregated results to Azure Blob Storage, then use Azure Data Factory to load them into the dedicated SQL pool every 10 minutes.. The Stream Analytics job must produce 5-minute tumbling window aggregations, but the dedicated SQL pool can only handle writes every 10 minutes. Directly writing to the SQL pool would either fail or cause performance issues. By outputting to Blob Storage, the aggregation logic remains intact, and a separate orchestration process (Azure Data Factory) can batch-load the results at a safe interval, ensuring both accuracy and system stability.

Answer

Configure the output to use batch size and batch interval settings to buffer writes.

Answer

Increase the streaming units of the Stream Analytics job to handle the load.

Answer

Use a tumbling window of 10 minutes instead of 5 minutes to match the SQL pool's write capability.

Question 5

You are building an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline must run every 15 minutes and only copy rows that have been modified since the last run. You need to implement a solution that tracks the last modified timestamp. Which component should you use?

Accepted Answer

A stored procedure activity that updates a control table with the last run timestamp.. Incremental loads require a persistent watermark to track the last processed timestamp. A control table updated by a stored procedure activity after each run provides this. The pipeline can look up the last timestamp and use it to filter source rows. This pattern ensures only new or modified rows are copied, avoiding duplicates and missed data.

Answer

A tumbling window trigger with a watermark column in the source dataset.

Answer

A schedule trigger with a recurrence of 15 minutes and a copy activity that uses a dynamic query with a timestamp variable.

Answer

An event-based trigger that fires when a new file is written to Blob Storage.

Question 6

You are implementing a mapping data flow in Azure Data Factory that writes to a Delta Lake folder in Azure Data Lake Storage Gen2. The flow processes several million rows and you must ensure that partially written files are never visible to downstream readers if the flow fails midway. You need to configure the sink so that all writes become visible only after the entire data flow run succeeds. What should you do?

Accepted Answer

Use the sink's Delta format with the transactional commit option so the write is committed atomically at the end of the data flow run.. Delta Lake maintains a transaction log that makes writes atomic once committed. In a mapping data flow, enabling the Delta sink's transactional commit defers publication until the run finishes, so files written during execution are not referenced by the log if the run fails. Downstream readers therefore see either the previous consistent state or the fully committed result, satisfying the atomicity requirement without extra pipeline stages.

Answer

Set the sink's write order to sequential and enable the single partition option.

Answer

Write to a staging folder and copy the output to the final folder with a Copy activity after the data flow.

Answer

Enable the sink's Delta merge option and specify a unique key column for upserts.

Question 7

You are developing an Azure Synapse Analytics pipeline that uses a Mapping Data Flow to process data. You need to ensure that the Data Flow can handle large volumes of data efficiently. Which two actions should you take? (Choose two.)

Accepted Answer

Enable the 'Optimize' tab and set the partitioning to 'Round Robin' for the source transformation.. To efficiently process large data volumes in Mapping Data Flow, partitioning is critical. Using a partitioned distribution in the sink enables parallel writes, and setting the source partitioning to Round Robin distributes the load evenly. These actions maximize throughput and resource utilization. Other options either reduce parallelism or are irrelevant to production performance.

Answer

Disable the 'Debug' mode to reduce overhead during production runs.

Answer

Increase the Data Flow cluster size to the maximum number of cores available.

Answer

Set the Data Flow to use a single partition to avoid coordination overhead.

Question 8

You are building an Azure Stream Analytics job that ingests device telemetry from an Azure Event Hub. The job must compute a rolling 5-minute average temperature for each device, emitting results only when at least 10 events are present in the window. You need to write the query. Which query should you use?

Accepted Answer

SELECT deviceId, AVG(temperature) AS avgTemp INTO output FROM input TIMESTAMP BY eventTime GROUP BY deviceId, TumblingWindow(minute, 5) HAVING COUNT(*) >= 10. The requirement is for a non-overlapping 5-minute window that emits a single average per device when at least 10 events occur. TumblingWindow provides exactly that: fixed-size, non-overlapping intervals. The HAVING clause filters on the event count within each window. Other window types either overlap (Sliding, Hopping) or depend on inactivity (Session), which would not produce the desired periodic output.

Answer

SELECT deviceId, AVG(temperature) AS avgTemp INTO output FROM input TIMESTAMP BY eventTime GROUP BY deviceId, SlidingWindow(minute, 5) HAVING COUNT(*) >= 10

Answer

SELECT deviceId, AVG(temperature) AS avgTemp INTO output FROM input TIMESTAMP BY eventTime GROUP BY deviceId, HoppingWindow(minute, 5, 1) HAVING COUNT(*) >= 10

Answer

SELECT deviceId, AVG(temperature) AS avgTemp INTO output FROM input TIMESTAMP BY eventTime GROUP BY deviceId, SessionWindow(minute, 5) HAVING COUNT(*) >= 10

Question 9

You are designing a data processing solution in Azure Synapse Analytics. A Spark pool notebook must read Parquet files from Azure Data Lake Storage Gen2, apply transformations, and write the result to a dedicated SQL pool table. The notebook runs under a Synapse workspace managed identity. You need to grant the minimum permissions required for the notebook to read the source files and write to the dedicated SQL pool. What should you do?

Accepted Answer

Assign the Storage Blob Data Reader role on the storage account and grant INSERT and SELECT on the target table in the dedicated SQL pool.. Least privilege means granting only what the workload needs. The notebook reads Parquet files, so Storage Blob Data Reader on the storage account suffices, and it writes to one dedicated SQL pool table, so INSERT and SELECT on that table are enough. Broader roles such as Contributor or db_owner, or key-based access, exceed the requirement and increase risk.

Answer

Store the storage account key in Azure Key Vault and grant the workspace managed identity GET on the secret.

Answer

Assign the Contributor role on the resource group that contains both the storage account and the Synapse workspace.

Answer

Assign the Storage Blob Data Contributor role on the storage account and the db_owner role in the dedicated SQL pool.

Question 10

You are designing a solution in Azure Synapse Analytics to process data using Spark pools and dedicated SQL pools. You need to ensure that data processed in Spark notebooks can be efficiently consumed by SQL queries with minimal latency. You plan to use external tables. Which two actions should you perform to enable seamless access to Spark-processed data from the dedicated SQL pool? (Choose two.)

Accepted Answer

Write the Spark output to Parquet files in Azure Data Lake Storage Gen2 and create an external table in the dedicated SQL pool that points to that location.. To enable seamless access to Spark-processed data from a dedicated SQL pool, write the Spark output in a format that PolyBase supports, such as Parquet, in Azure Data Lake Storage Gen2. Then create an external table in the SQL pool that points to that location using PolyBase. This allows SQL queries to read the data directly without moving it, minimizing latency and leveraging the columnar format for performance.

Answer

Enable automatic statistics creation on the external table to improve query performance.

Answer

Convert the Parquet files to CSV format before creating the external table.

Answer

Configure the Spark pool to use the same Azure Synapse workspace managed identity as the dedicated SQL pool.

Question 11

You are designing a data processing solution in Azure Data Factory that uses Mapping Data Flows to transform data from Azure Blob Storage to Azure Synapse Analytics. You need to ensure that the data flow can handle large volumes of data efficiently and minimize data movement. Which two actions should you perform? (Choose two.)

Accepted Answer

Optimize the data flow by removing unnecessary transformations and using a single data flow activity.. Partitioning the source enables parallel reads, and optimizing transformations by removing unnecessary steps reduces data movement. These actions improve performance and scalability for large datasets. Other options like single partitioning or broadcast joins are not appropriate for large tables. Memory optimization alone does not address the core issue of data movement.

Answer

Use a single partition in the sink transformation to ensure data integrity.

Answer

Set the 'Compute type' to 'Memory optimized' for the data flow activity.

Answer

Use a broadcast join for large tables to reduce shuffling.

Question 12

You are creating an Azure Data Factory pipeline that must copy data from an on-premises SQL Server to Azure Blob Storage. The copy activity must run on a schedule every hour. What is the simplest way to achieve this?

Accepted Answer

Create a tumbling window trigger with a recurrence of one hour.. Tumbling window triggers are the recommended way to schedule periodic pipelines in Azure Data Factory. They support recurrence, state, and backfilling. Schedule triggers are deprecated, and copy activities cannot self-schedule. Event-based triggers are for Azure events, not on-premises SQL Server.

Answer

Create an event-based trigger that fires when a file appears in the source SQL Server.

Answer

Configure the copy activity to run continuously with a polling interval.

Answer

Use a schedule trigger with a recurrence of one hour.

Question 13

You are building an Azure Data Factory pipeline that must process only the files that arrived in an Azure Data Lake Storage Gen2 container since the previous pipeline run. The pipeline runs every 15 minutes. You need to ensure each file is processed exactly once without scanning the entire container. What should you configure?

Accepted Answer

An event-based trigger subscribed to the Blob Created event on the storage account. Event-based triggers are the mechanism Azure Data Factory provides to react to new blobs rather than poll a container. Because the trigger body includes the exact folder and file name, a downstream activity can process only the newly arrived object, and each event corresponds to one file creation. This delivers both the incremental scope and the single-processing guarantee the scenario demands.

Answer

A tumbling window trigger with a self-hosted integration runtime and a Lookup activity reading a watermark table

Answer

A schedule trigger with a Copy activity using a wildcard file path filter

Answer

A tumbling window trigger with a Get Metadata activity and a ForEach activity iterating over child items

Question 14

You are developing an Azure Stream Analytics job that processes real-time telemetry from Azure Event Hubs. The job must output aggregated results to an Azure Synapse Analytics dedicated SQL pool. You need to ensure the job processes events with low latency and handles out-of-order events within a 30-second window. Which windowing function should you use?

Accepted Answer

Sliding window. Sliding windows are designed to handle out-of-order events by including events that arrive within the window duration, even if they are late. Setting a 30-second sliding window allows the job to wait for late events up to that period, ensuring accurate aggregation with low latency. Tumbling, hopping, and session windows do not provide the same explicit tolerance for out-of-order events in this context.

Answer

Tumbling window

Answer

Session window

Answer

Hopping window

Question 15

You are building an Azure Stream Analytics job that reads JSON events from an Azure Event Hub. Each event includes a nested property named 'device.temperature.value' that must be cast to a float for downstream calculations. The job must be resilient to malformed events where the property is missing or not numeric. You need to write a Transact-SQL query that extracts the value without failing the entire job. Which expression should you use?

Accepted Answer

SELECT TRY_CAST(GetRecordPropertyValue(GetRecordPropertyValue(GetRecordPropertyValue(EventHubInput, 'device'), 'temperature'), 'value') AS float) AS Temperature FROM EventHubInput. Nested JSON in Azure Stream Analytics requires chaining GetRecordPropertyValue for each level of the object hierarchy. The correct expression extracts device, then temperature, then value, and applies TRY_CAST to safely convert to float. Using CAST or a single dotted path will not handle the nested structure or malformed data, leading to NULL results or job failures.

Answer

SELECT TRY_CAST(GetRecordPropertyValue(GetRecordPropertyValue(EventHubInput, 'device'), 'temperature.value') AS float) AS Temperature FROM EventHubInput

Answer

SELECT CAST(GetRecordPropertyValue(EventHubInput, 'device.temperature.value') AS float) AS Temperature FROM EventHubInput

Answer

SELECT TRY_CAST(GetRecordPropertyValue(GetArrayElement(EventHubInput, 0), 'device.temperature.value') AS float) AS Temperature FROM EventHubInput