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

Question 1

Which TWO actions should you take to optimize a Spark job in Azure Synapse Analytics that is experiencing excessive shuffling and long execution times?

Accepted Answer

Broadcast one of the tables if it is small enough to fit in memory.. Option C is correct because broadcasting a small table (via broadcast join, e.g., spark.sql.autoBroadcastJoinThreshold or broadcast() hint) avoids a shuffle altogether by replicating the small dataset to every executor, eliminating the expensive shuffle exchange for that side of the join. Option E is correct because increasing spark.sql.shuffle.partitions (default 200) creates more, smaller shuffle partitions, which improves parallelism and reduces the per-task data volume and skew-related stragglers when the default partition count is too low for the data size. Option A is not appropriate here because coalescing reduces partition count, which lowers parallelism and can worsen skew rather than relieve shuffle pressure. Option B does not address shuffling; checkpointing truncates lineage for fault tolerance and iterative workloads, not shuffle optimization. Option D is wrong because disabling dynamic resource allocation removes the ability to scale executors up or down with workload demand, which typically hurts rather than helps performance.

Answer

Coalesce the number of partitions to reduce overhead.

Answer

Enable checkpointing to persist intermediate results.

Answer

Disable dynamic resource allocation.

Question 2

Which THREE factors should you consider when designing a real-time streaming solution using Azure Stream Analytics to process IoT data from thousands of devices?

Accepted Answer

The batch size for output to Azure Synapse Analytics to minimize write transactions.. Batching output to Azure Synapse Analytics reduces write transactions and improves cost efficiency. Option C is correct because windowing functions and late arrival policies are essential for handling out-of-order events in real-time streaming. Option E is correct because streaming units and partitioning determine throughput and scalability for processing IoT data. Option A is incorrect because reference data that changes every few seconds would require frequent reloads, defeating the purpose of static reference data in Stream Analytics. Option D is incorrect because latency requirements for Cosmos DB are not a primary design factor; Stream Analytics can write to Cosmos DB, but the main considerations are throughput and consistency, not latency.

Answer

The need to join input data with reference data that changes every few seconds.

Answer

The latency requirements for writing output to Azure Cosmos DB for NoSQL.

Question 3

Which TWO techniques should you use to secure sensitive data in Azure Synapse Analytics dedicated SQL pools when implementing column-level security?

Accepted Answer

Define dynamic data masking rules to obfuscate sensitive columns for unauthorized users.. Option A is correct because dynamic data masking (DDM) in Azure Synapse Analytics dedicated SQL pools applies masking rules at the column level, so unauthorized users see obfuscated values while privileged users with UNMASK permission see the actual data, directly addressing column-level protection of sensitive data. Option D is correct because column-level security in dedicated SQL pools is implemented by granting SELECT permission on specific columns (GRANT SELECT ON object (column) TO user) rather than the whole table, which restricts access precisely to the sensitive columns. Option B is not correct here because TDE encrypts data at rest at the database/file level and does not provide column-level access control. Option C is not correct because filter predicates in a security policy implement row-level security, not column-level restrictions. Option E is not correct because Always Encrypted is not supported for column-level encryption in dedicated SQL pools and is intended for client-side encryption scenarios in other Azure SQL offerings.

Answer

Enable Transparent Data Encryption (TDE) on the database.

Answer

Create a security policy with filter predicates to restrict access to specific columns.

Answer

Implement Always Encrypted to encrypt columns at the application level.

Question 4

You are a data engineer at a retail company. You need to develop a data processing solution in Azure Synapse Analytics that reads sales transactions from Parquet files stored in Azure Data Lake Storage Gen2, transforms the data by aggregating daily sales per store, and writes the results to a dedicated SQL pool table for reporting. The transformation logic must be reusable and maintained in a source control system. You want to minimize administrative overhead and leverage serverless resources where possible. Which approach should you recommend?

Accepted Answer

Use a serverless SQL pool to query the Parquet files via OPENROWSET, then use CETAS to write the aggregated results to the dedicated SQL pool using an external table.. A serverless SQL pool can query Parquet files directly in ADLS Gen2 via OPENROWSET without provisioning compute, and CETAS (CREATE EXTERNAL TABLE AS SELECT) persists the aggregated result as an external table — which can be created in the dedicated SQL pool's storage layer and queried from it. The T-SQL transformation logic lives in a .sql file that can be checked into Git, satisfying the source-control requirement, and no Spark cluster or ADF pipeline infrastructure needs to be managed.

Answer

Use Azure Data Factory with Mapping Data Flows to read Parquet files, perform aggregations, and write to the dedicated SQL pool.

Answer

Create an Azure Synapse Spark notebook that reads Parquet files, performs aggregation using PySpark, and writes the results to the dedicated SQL pool using the Spark Synapse connector.

Answer

Use PolyBase in a dedicated SQL pool to create external tables over the Parquet files, then use INSERT...SELECT to load aggregated data into the target table.

Question 5

Your company is building a real-time dashboard for monitoring website traffic. The data is ingested from web servers into Azure Event Hubs. You need to design a stream processing solution using Azure Stream Analytics that computes the number of unique visitors per minute, per country, and outputs the results to Azure Synapse Analytics for reporting. The solution must handle out-of-order events with a maximum late arrival of 10 seconds. You also need to ensure exactly-once semantics for the output. Which combination of settings should you use?

Accepted Answer

Use a Tumbling window of size 1 minute, set late arrival policy to 10 seconds, and configure output to Azure Synapse Analytics with `Exactly once` semantics.. A Tumbling window of 1 minute produces non-overlapping, fixed-interval aggregates — exactly what 'unique visitors per minute' requires. Setting the late arrival policy to 10 seconds handles out-of-order events within the stated tolerance, and configuring the Synapse output with Exactly once semantics satisfies the exactly-once requirement. This is the only combination that matches all three constraints.

Answer

Use a Sliding window of length 1 minute, set out-of-order policy to 10 seconds, and use 'At least once' output mode.

Answer

Use a Session window with timeout of 1 minute and maximum duration of 10 seconds, set late arrival to 10 seconds, and use 'Exactly once' output mode.

Answer

Use a Hopping window of size 1 minute and hop 1 minute, set late arrival policy to 10 seconds, and use 'Exactly Once' output mode.

Question 6

You are designing a batch processing solution for a financial services company that processes transactions from multiple sources. The data is stored in Azure Data Lake Storage Gen2 in Parquet format. You need to perform complex transformations including joins, aggregations, and window functions, and then load the results into an Azure Synapse Analytics dedicated SQL pool. The transformations must be written in SQL and executed on a serverless Spark cluster to minimize costs. You want to manage the code in a Git repository and automate the deployment using Azure DevOps. Which approach should you recommend?

Accepted Answer

Create a Spark job definition in Azure Synapse that reads Parquet files, performs transformations using PySpark, and writes to the dedicated SQL pool using the Spark Synapse connector.. The requirement is SQL-based transformations on a serverless Spark cluster with Git-managed code and Azure DevOps automation. A Synapse Spark job definition runs on the serverless Spark pool, supports Spark SQL (and PySpark), integrates natively with the Synapse dedicated SQL pool via the Spark Synapse connector, and can be version-controlled in Git and deployed through Azure DevOps. This matches every stated constraint: SQL transformations, serverless Spark, Synapse dedicated SQL pool target, and CI/CD.

Answer

Use Azure Databricks with Auto Loader to read from Data Lake Storage, perform transformations using Spark SQL, and write to the dedicated SQL pool via JDBC.

Answer

Use Azure Data Factory with Mapping Data Flows to visually design transformations and write to the dedicated SQL pool.

Answer

Use a serverless SQL pool in Azure Synapse to query the Parquet files using T-SQL, then use CETAS to write the results to the dedicated SQL pool.

Question 7

You are developing an Azure Databricks notebook that reads streaming data from Azure Event Hubs and writes to a Delta table. The pipeline must handle late-arriving data up to 10 minutes and avoid duplicates. You need to implement exactly-once processing semantics. Which combination of features should you use?

Accepted Answer

Use foreachBatch with a MERGE operation into the Delta table, and enable checkpointing.. Exactly-once processing in Azure Databricks streaming requires an idempotent sink and reliable checkpointing. Using foreachBatch with a MERGE operation into a Delta table ensures that each micro-batch is applied atomically and duplicates are avoided by matching on a unique key. Checkpointing ensures that progress is tracked and recovery is consistent.

Answer

Use a tumbling window of 10 minutes and write to a Parquet file sink.

Answer

Use watermarking with a 10-minute delay and deduplicate using dropDuplicates on a unique event ID.

Answer

Use the Delta table as a sink with the 'mergeSchema' option set to true.

Question 8

You are implementing an Azure Databricks job that processes Parquet files from Azure Data Lake Storage Gen2. The job must merge new data into an existing Delta table and preserve the ability to query previous versions of the table. You need to ensure that the merge operation does not rewrite the entire table and that historical versions remain accessible. Which Delta Lake feature should you use?

Accepted Answer

MERGE INTO with the Delta table's transaction log and time travel enabled by default. Delta Lake's transaction log automatically records each transaction, enabling time travel to earlier versions without additional configuration. MERGE INTO updates only the files that contain matching rows, avoiding a full table rewrite. Together, these features meet both the performance and historical-access requirements.

Answer

CREATE TABLE AS SELECT with a new table name for each merge batch

Answer

VACUUM with a retention period of 0 hours to remove old files after each merge

Answer

MERGE INTO with delta.enableChangeDataFeed set to true

Question 9

You are building an Azure Synapse Analytics pipeline that processes streaming data from Azure Event Hubs. The pipeline uses a Spark pool and writes to a Delta table in the primary storage account. You need to ensure that the pipeline can process data incrementally and handle late-arriving data with minimal reprocessing. What should you implement?

Accepted Answer

Use Structured Streaming in Azure Databricks with a checkpoint location and a Delta table as the sink.. Structured Streaming in Azure Databricks with checkpointing and a Delta sink provides the necessary state management and ACID guarantees to process Event Hubs data incrementally. Checkpoints track offsets and allow recovery, while Delta Lake's MERGE enables upserts for late-arriving data without reprocessing the entire dataset. This meets the requirements for minimal reprocessing and incremental processing.

Answer

Use Azure Data Factory with a Copy activity to pull events from Event Hubs into a Delta table.

Answer

Use a tumbling window trigger in Azure Synapse Pipelines to process data in fixed intervals.

Answer

Use Azure Stream Analytics with a JavaScript user-defined function to write to Delta Lake.

Question 10

You are implementing a streaming pipeline in Azure Databricks that reads from an Azure Event Hubs topic using Structured Streaming. The pipeline must write to a Delta Lake table and support exactly-once processing with checkpointing. You need to ensure the write operation is idempotent and recoverable after failures. Which two configurations should you set on the writeStream operation? (Choose two.)

Accepted Answer

Set the queryName option to a unique, stable name for the streaming query.. Exactly-once streaming to Delta Lake relies on durable checkpointing and a stable query identity. Setting checkpointLocation to ADLS Gen2 persists offsets and commit metadata so restarts resume correctly, while a unique queryName ensures the query and its checkpoint subdirectory remain consistent across restarts. Delta Lake's transactional writes then make each micro-batch idempotent, completing the exactly-once guarantee.

Answer

Set the outputMode to 'update' and enable the spark.sql.streaming.forceDeleteTempCheckpointLocation option.

Answer

Set the trigger to Trigger.Once() to process all available data in a single batch.

Answer

Set the outputMode to 'complete' so that the entire result table is rewritten each micro-batch.

Question 11

You are designing an Azure Data Factory pipeline that must process files landing in Azure Blob Storage. The pipeline needs to handle files that arrive at unpredictable times and must avoid reprocessing files that were already processed. You need to implement a solution that meets these requirements. Which two actions should you perform? (Choose two.)

Accepted Answer

Use a tumbling window trigger with a self-dependent pipeline to process new files and store processed file names in a control table. Both a tumbling window trigger with a self-dependent pipeline and a control table, and an event-based trigger with logging to Azure Table Storage, provide reliable processing of unpredictably arriving files while preventing duplicates. The other options either reprocess files, delete source data, or require unnecessary custom code.

Answer

Use a custom activity that polls the container and maintains a checkpoint file in the same container

Answer

Use a storage event trigger that deletes the source file after successful processing

Answer

Use a schedule trigger that runs every 5 minutes and processes all files in the container without tracking

Question 12

You are building an Azure Stream Analytics job that reads from an Azure Event Hubs input. Events arrive with an ApplicationTimestamp property in their JSON payload, but the job's output shows events being processed with the arrival time instead of that property. You need the job to use the ApplicationTimestamp value for temporal calculations such as windowing. What should you configure?

Accepted Answer

Set the event ordering policy in the job's Event Hubs input to use ApplicationTimestamp as the TIMESTAMP BY column.. Azure Stream Analytics defaults to arrival time as the event timestamp. To make the engine evaluate windows, joins, and other temporal operations against a value carried in the payload, the job must declare that field through TIMESTAMP BY. Configuring it in the Event Hubs input's event ordering settings applies the selection to every query that reads that input, which resolves the observed behavior.

Answer

Increase the streaming units allocated to the Stream Analytics job.

Answer

Change the Event Hubs input's consumer group to a dedicated group used only by this job.

Answer

Enable late-arrival tolerance on the job's output and set it to the maximum allowed value.

Question 13

You are developing an Azure Databricks notebook that reads Parquet files from Azure Data Lake Storage Gen2, performs transformations, and writes the result to a Delta table. The notebook runs on a cluster with autoscaling enabled. You notice that the write operation is slow and often fails with 'FileAlreadyExistsException' when multiple jobs run concurrently. You need to ensure that concurrent writes do not fail and that the table remains consistent. What should you do?

Accepted Answer

Ensure that all writes use Delta Lake's ACID transactions by writing to the Delta table directly, and set the isolation level to Serializable.. Delta Lake's ACID transactions and optimistic concurrency control manage concurrent writes. Writing directly to a Delta table ensures that the transaction log serializes conflicting operations. Setting the isolation level to Serializable provides the strongest consistency, preventing FileAlreadyExistsException. Other options address performance or schema but not concurrency control.

Answer

Enable optimistic concurrency control by setting the Delta table property 'delta.autoOptimize.optimizeWrite' to true.

Answer

Use the 'mergeSchema' option when writing to the Delta table to allow schema evolution.

Answer

Partition the Delta table by a high-cardinality column to distribute writes across multiple directories.

Question 14

You are implementing a streaming pipeline in Azure Databricks that reads from an Azure Event Hubs topic using Structured Streaming and writes to a Delta Lake table. The solution must achieve exactly-once processing semantics and allow the stream to resume after a cluster restart without reprocessing or losing events. You need to identify which TWO configurations or features are required to meet these goals. (Choose two.)

Accepted Answer

Configure a checkpoint location for the streaming query.. Exactly-once processing with resumability requires two things: durable progress tracking and idempotent output. The checkpoint location stores consumed offsets and state so the query resumes without gaps or duplicates, while Delta Lake's transactional, idempotent writes with a unique event key prevent duplicate records if a micro-batch is retried. Scaling and trigger settings affect performance only.

Answer

Set the trigger to ProcessingTime with a one-second interval.

Answer

Increase the Event Hubs partition count to improve parallelism.

Answer

Enable automatic scaling of the Databricks cluster.

Question 15

You are creating an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The copy must run every 15 minutes and only transfer rows that have been modified since the last run. You have a column named LastModifiedDate in the source table. What should you use to track the last copied value?

Accepted Answer

A tumbling window trigger with a watermark column and a stored procedure to update the watermark.. Incremental copy requires tracking the last modified value. A tumbling window trigger can be combined with a watermark column and a mechanism to persist the watermark, such as a stored procedure. This enables the pipeline to dynamically filter rows based on the last run's maximum LastModifiedDate, ensuring only new or changed data is copied every 15 minutes.

Answer

A schedule trigger with a fixed start time and a Copy activity that uses a query with a static date filter.

Answer

A tumbling window trigger with a Copy activity that uses a query with a parameterized date filter but no watermark storage.

Answer

An event-based trigger that listens for changes in the source table and a Copy activity with a dynamic filter.