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

Question 1

You are using Azure Synapse Analytics to process data in a dedicated SQL pool. You need to load a large fact table from Parquet files in Azure Data Lake Storage Gen2. The table is distribution type HASH on a surrogate key. You want to maximize load performance. Which method should you use?

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

Accepted Answer

Use the COPY statement with a shared access signature (SAS) key.. The COPY statement in Azure Synapse Analytics is optimized for high-throughput loading of Parquet files into dedicated SQL pools. It supports parallel reads, handles distribution, and provides built-in error handling. Using a SAS key for authentication ensures secure access. Other methods like PolyBase or bcp are either less performant or not suitable for Parquet in this context.

Answer

Use bcp (bulk copy program) to load the data from a local file share.

Answer

Use PolyBase with an external table pointing to the Parquet files.

Answer

Use Azure Data Factory with a Copy activity and a staging blob.

Question 2

You are building an Azure Stream Analytics job that processes vehicle telemetry from an Azure Event Hub. The job must emit alerts to an Azure SQL Database whenever a vehicle's average speed over a 5-minute tumbling window exceeds 120 km/h. You need to ensure that late-arriving events with timestamps up to 10 minutes old are still included in the correct window. Which event ordering setting should you configure?

Accepted Answer

Set the late arrival tolerance to 10 minutes and the out-of-order tolerance to 10 minutes.. Late arrival tolerance controls how long an event can be delayed relative to the maximum timestamp seen and still be processed into its window. Out-of-order tolerance controls how long events can be out of sequence. To include events up to 10 minutes late, the late arrival tolerance must be at least 10 minutes, and out-of-order tolerance should be set to allow reordering. The correct combination ensures all events are aggregated accurately.

Answer

Set the late arrival tolerance to 0 and the out-of-order tolerance to 10 minutes.

Answer

Set the out-of-order tolerance to 10 minutes and leave the late arrival tolerance at its default of 5 seconds.

Answer

Set the late arrival tolerance to 10 minutes and the out-of-order tolerance to 0.

Question 3

You are designing a solution in Azure Synapse Analytics to process data using Apache Spark pools. The data resides in Azure Data Lake Storage Gen2. You need to ensure that the Spark pools can access the data securely without embedding credentials in notebooks. Which two actions should you perform? (Choose two.)

Accepted Answer

Configure the Spark pool to use the Synapse workspace's managed identity by enabling the `fs.azure.account.auth.type` setting to `ManagedIdentity` for the storage account.. To securely access ADLS Gen2 from Synapse Spark pools without credentials, you assign a managed identity to the Synapse workspace and grant it the necessary role on the storage account. Then you configure the Spark pool to use that managed identity for authentication by setting the appropriate Spark configuration. This eliminates credentials and leverages Microsoft Entra ID integration.

Answer

Store the storage account key in Azure Key Vault and reference it in Spark configuration using `spark.conf.set`.

Answer

Create a service principal and store its client secret in a configuration file within the Spark pool's libraries.

Answer

Enable Azure AD DS and join the Spark pool nodes to the domain, then use Kerberos authentication to access the storage account.

Question 4

You are optimizing an Azure Databricks job that processes a large Delta Lake table partitioned by date. Analysts frequently query a single recent date, but the job's writes create many small files per partition. You need to reduce query latency for the recent-date queries while keeping the ability to time travel to earlier versions. What should you do?

Accepted Answer

Run OPTIMIZE on the table to compact small files, then run VACUUM with a retention period that preserves time travel history.. Small files inflate the number of file reads per query, so compaction via OPTIMIZE is the direct fix for latency on recent-date queries. Time travel depends on retaining old data files and transaction log entries, so VACUUM must use a retention period at least as long as the desired history. Combining a normal OPTIMIZE with a longer VACUUM retention satisfies both performance and history requirements.

Answer

Run OPTIMIZE with ZORDER BY on the date column and retain the transaction log.

Answer

Run VACUUM with a zero-hour retention to remove all unreferenced files immediately.

Answer

Convert the table to Parquet and rewrite the partitions with a single file each.

Question 5

You are designing a streaming pipeline in Azure Stream Analytics that reads JSON telemetry from an Azure Event Hub and writes aggregated results to an Azure Synapse Analytics dedicated SQL pool. During peak hours the job shows a growing input backlog and high SU utilization. You need to reduce the backlog while keeping the query logic unchanged. What should you do first?

Accepted Answer

Repartition the Event Hub input and the query so partitions align, then scale streaming units. Throughput in Stream Analytics is governed first by whether the job can run in parallel. That requires a partitioned input and a query that uses PARTITION BY aligned to the same key. Once partitioning aligns, each partition is processed independently and additional streaming units translate directly into more parallel compute, clearing the backlog. Scaling SUs without that alignment yields no meaningful gain.

Answer

Enable late arrival tolerance and increase the out-of-order event window

Answer

Switch the output from the dedicated SQL pool to a Power BI streaming dataset

Answer

Increase the number of streaming units allocated to the job

Question 6

You are developing an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The copy activity must use a self-hosted integration runtime. You need to ensure that the copy activity can connect to the SQL Server. Which authentication method should you use in the linked service for the SQL Server?

Accepted Answer

Windows authentication. For on-premises SQL Server accessed via a self-hosted integration runtime, Windows authentication is the most secure and straightforward method. It leverages the credentials of the Windows account running the integration runtime service, eliminating the need to store credentials in the linked service. SQL authentication is possible but less secure. Microsoft Entra ID and managed identity are not supported for on-premises SQL Server in this context.

Answer

Managed identity authentication

Answer

SQL authentication

Answer

Azure Active Directory authentication

Question 7

You are building an Azure Stream Analytics job that reads from an Azure Event Hubs input. The job must compute a 5-minute tumbling window aggregation and write results to an Azure Synapse Analytics dedicated SQL pool. You notice that results are frequently delayed by several minutes. You need to reduce end-to-end latency while ensuring each event is processed exactly once. What should you do?

Accepted Answer

Set the output batch size to 1 and enable exactly-once delivery by using the SQL pool output with the appropriate compatibility level.. The delay stems from output batching, not from insufficient compute or windowing configuration. Setting the batch size to 1 forces immediate writes after each window, cutting latency. Exactly-once delivery to the dedicated SQL pool is supported when the output is configured properly, so the job can meet both the latency and correctness requirements without adding extra services.

Answer

Set the Stream Analytics job's compatibility level to 1.2 and enable late arrival tolerance of 5 minutes.

Answer

Increase the number of Streaming Units and set the output batch size to 1.

Answer

Configure the job to use a Azure SQL Database output with batch size 1 and then replicate to the SQL pool.

Question 8

You are implementing a data processing solution in Azure Databricks that reads from Azure Data Lake Storage Gen2, transforms data using PySpark, and writes to Delta Lake. The solution must support ACID transactions and allow concurrent writes from multiple users. You need to configure the Delta Lake table to handle schema evolution and enforce data quality. Which two actions should you perform? (Choose two.)

Accepted Answer

Use the MERGE INTO statement to upsert data and enforce data quality constraints.. Enabling auto-merge schema allows Delta Lake to adapt to changing data structures, supporting schema evolution. Using MERGE INTO provides atomic upserts and can enforce data quality through conditional logic, ensuring ACID transactions and concurrent write safety. Together, these actions address the requirements for schema evolution and data quality in a multi-user Delta Lake environment.

Answer

Configure the table to use Z-Ordering on a frequently filtered column to improve data quality.

Answer

Use the OPTIMIZE command to compact small files and enforce data quality.

Answer

Set the table property delta.appendOnly to true to ensure ACID compliance.

Question 9

You are building an Azure Stream Analytics job that reads from an Azure Event Hubs input and writes to an Azure Synapse Analytics dedicated SQL pool. The job must handle late-arriving events up to 15 minutes and produce exactly-once output to the SQL pool. Which output configuration should you use?

Accepted Answer

Configure the output to Azure Synapse Analytics with the 'Upsert' write mode and set the event ordering policy to allow late events up to 15 minutes.. The correct approach is to use the native Azure Synapse Analytics output with Upsert write mode, which performs an idempotent MERGE operation. Combined with an event ordering policy that tolerates late events up to 15 minutes, the job can process late data and maintain exactly-once semantics. Append mode or intermediate storage cannot provide the required upsert and exactly-once guarantees.

Answer

Use an Azure Function output that writes to the dedicated SQL pool with a MERGE statement, and rely on Event Hubs to deduplicate events.

Answer

Use a Blob Storage output with a custom partitioning scheme and then load the data into the dedicated SQL pool using PolyBase.

Answer

Configure the output to Azure Synapse Analytics with the 'Append' write mode and set the event ordering policy to drop late events after 5 minutes.

Question 10

You are developing an Azure Databricks notebook that processes streaming data from Azure Event Hubs using Structured Streaming. The notebook writes to a Delta table in Azure Data Lake Storage Gen2. You need to ensure that the streaming query processes each event exactly once and can recover from failures without data loss or duplication. The checkpoint location is configured. Which write mode and trigger should you use?

Accepted Answer

Use append mode with a processing time trigger and set the checkpoint location to a reliable storage.. Exactly-once processing in Azure Databricks Structured Streaming relies on checkpointing and idempotent sinks. Delta Lake supports idempotent writes via its transaction log, and append mode is the standard for streaming ingestion. A processing time trigger enables continuous micro-batches. Update mode and continuous trigger are not suitable for this sink, and once trigger is for batch execution. The checkpoint location must be durable for recovery.

Answer

Use append mode with a processing time trigger of 1 minute.

Answer

Use update mode with a continuous trigger.

Answer

Use append mode with a once trigger.

Question 11

You are designing a data processing solution in Azure Synapse Analytics using Spark pools. You need to implement a transformation that reads JSON data from Azure Data Lake Storage Gen2, flattens nested arrays, and writes the result to a Delta table. You must ensure the solution handles schema evolution and provides ACID transactions. Which two features should you use? (Choose two.)

Accepted Answer

Write the flattened data to a Delta table using the .option("mergeSchema", "true") setting.. To flatten nested arrays in Spark SQL, the explode function is used to convert array elements into rows. For schema evolution in Delta Lake, the mergeSchema option allows automatic schema merging during writes. Together, these enable the transformation and storage of evolving JSON data into a Delta table with ACID transactions. The other options either reference unsupported functions or incorrect configurations for Azure Synapse Spark pools.

Answer

Store the data in Parquet format to ensure ACID transactions.

Answer

Enable autoMerge and schema evolution by setting spark.databricks.delta.schema.autoMerge.enabled to true.

Answer

Use the flatten function in Spark SQL to flatten nested arrays.

Question 12

You are building an Azure Stream Analytics job that reads telemetry from an Azure Event Hub. The incoming stream contains occasional duplicate events caused by at-rest retries from the upstream producer. You must remove duplicate events that share the same DeviceId and EventTime within a 10-minute window, and the output must be written to Azure Synapse Analytics. Which query construct should you use?

Accepted Answer

Use a TIMESTAMP BY clause on EventTime and a DEDUP operation with a 10-minute window and DeviceId as the key.. Removing repeated telemetry requires comparing each event against other events for the same device within a bounded time range. A windowed deduplication pattern that establishes event time with TIMESTAMP BY and selects the first record per DeviceId in a 10-minute window eliminates the repeats while preserving the original event. Aggregating windows alone cannot discard identical rows because they operate on groups rather than individual event identity.

Answer

Use a TIMESTAMP BY clause on EventTime and a SessionWindow with a 10-minute timeout, then group by DeviceId.

Answer

Use a TIMESTAMP BY clause on EventTime and a HoppingWindow of 10 minutes, then group by DeviceId.

Answer

Use a TIMESTAMP BY clause on EventTime and a TumblingWindow of 10 minutes, then group by DeviceId.

Question 13

You are implementing a solution in Azure Data Factory that uses Mapping Data Flows to transform data. The data flow reads from a Parquet source and writes to a Delta sink. You need to ensure that the data flow handles schema drift, allowing new columns added at the source to be automatically included in the sink. What should you enable in the data flow?

Accepted Answer

Enable the 'Allow schema drift' option in both the source and sink transformations, and use the 'Auto-mapping' feature in the sink.. To handle schema drift in Mapping Data Flows, you must enable 'Allow schema drift' on the source to read new columns, and on the sink to write them. Additionally, using auto-mapping in the sink ensures that new columns are automatically mapped to the destination. This end-to-end configuration allows the data flow to adapt to schema changes without manual updates.

Answer

Use a Derived Column transformation to manually add all possible columns and set them to NULL if missing.

Answer

Configure the sink to use the 'Update method' as 'Allow insert' and enable 'Merge schema' in the Delta sink settings.

Answer

Enable the 'Allow schema drift' option in the source transformation.

Question 14

You are using Azure Data Factory to copy data from an on-premises Oracle database to Azure Synapse Analytics. The copy activity uses a self-hosted integration runtime. You notice that the copy performance is slower than expected, and you need to optimize it. The source table has a clustered index on a numeric column, and the data volume is approximately 500 GB. You have already increased the degree of copy parallelism. What should you do next to improve performance?

Accepted Answer

Enable partitioning in the copy activity and specify a dynamic range partition based on the indexed column.. For large-scale copies from an on-premises database with an indexed column, enabling partitioning with dynamic range partitioning allows parallel extraction. This leverages the source index to divide data evenly, enabling multiple concurrent reads. Combined with increased parallelism, this maximizes throughput. Other options like DIUs or staging may help but do not directly address the source read bottleneck.

Answer

Increase the Data Integration Units (DIUs) for the copy activity to the maximum allowed.

Answer

Enable staging in the copy activity and use a PolyBase-compatible staging store.

Answer

Change the copy activity to use a binary copy for faster throughput.

Question 15

You are designing a data processing solution in Azure Synapse Analytics that uses both a dedicated SQL pool and a serverless SQL pool. You need to process Parquet files stored in Azure Data Lake Storage Gen2. Which two actions should you perform to query the Parquet files using the serverless SQL pool? (Choose two.)

Accepted Answer

Use OPENROWSET with the BULK option and specify the location of the Parquet files.. To query Parquet files in a serverless SQL pool, you typically use OPENROWSET with the BULK option and FORMAT = 'PARQUET'. Additionally, you must create an external data source that points to the storage account, which provides the necessary connection information and credentials. Creating external tables or using CETAS are not required for basic querying, and PolyBase is not used in serverless SQL pools.

Answer

Use the CETAS (CREATE EXTERNAL TABLE AS SELECT) statement to export the data.

Answer

Create an external table using the Parquet files.

Answer

Configure a PolyBase external table in the dedicated SQL pool.