DP-203 Practice Test 34 — 15 Questions

Question 1

You are implementing a data processing solution in Azure Databricks. The solution reads JSON files from Azure Data Lake Storage Gen2, performs complex transformations using PySpark, and writes the results to a Delta table. You need to ensure that the write operation is idempotent and can recover from failures without duplicating data. Which approach should you use?

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

Accepted Answer

Use the merge operation with a unique key to upsert records into the Delta table.. The merge operation in Delta Lake enables upserts based on a unique key, ensuring that rerunning the job after a failure does not duplicate data. It provides ACID transactions and idempotence, which are critical for reliable data processing. Append mode and overwrite mode do not offer the same guarantees, and temporary files with Copy activity lack transactional integrity.

Answer

Write the output in append mode and use a deduplication step after each run.

Answer

Write the output using the overwrite mode to replace the entire Delta table on each run.

Answer

Write the output to a temporary Parquet file and then use a Copy activity to move it to the Delta table.

Question 2

You are writing a T-SQL query against a dedicated SQL pool in Azure Synapse Analytics. The query aggregates a fact table containing billions of rows by joining it to a small dimension table. You observe that the join produces a large amount of data movement and the query runs slowly. You need to reduce data movement for this recurring pattern. What should you do?

Accepted Answer

Replicate the small dimension table so a copy exists on every distribution.. In a dedicated SQL pool, join performance depends heavily on whether matching rows already reside on the same distribution. A replicated table places a complete copy of the small dimension on every distribution, letting the engine perform the join locally against the large fact table. This eliminates the shuffle or broadcast of the dimension and is the recommended pattern for recurring joins with small dimensions.

Answer

Increase the resource class of the user running the query.

Answer

Change the fact table's distribution to ROUND_ROBIN.

Answer

Add a columnstore index to the dimension table.

Question 3

You are implementing a Mapping Data Flow in Azure Synapse Analytics that reads from a Parquet source and writes to a Delta sink. The data flow includes a Surrogate Key transformation to generate unique keys for each row. You notice that when the data flow runs multiple times, the surrogate keys are not consistent across runs and sometimes overlap. You need to ensure that surrogate keys are unique and stable across runs. What should you do?

Accepted Answer

Use a Derived Column transformation to concatenate a business key from the source with a hash function to create a deterministic surrogate key.. To ensure surrogate keys are unique and stable across runs, you must derive them deterministically from the source data. Hashing a business key with a function like `sha2` or `md5` produces the same key for the same input, preventing overlaps and ensuring consistency. Sequential Surrogate Key transformations or random UUIDs do not provide stability across multiple executions.

Answer

Use a Derived Column transformation with the `uuid()` function to generate a unique identifier for each row.

Answer

Use a Surrogate Key transformation with the 'Start value' set to a value that increments based on the maximum existing key in the sink.

Answer

Use a Surrogate Key transformation with the 'Start value' set to 1 and enable the 'Unique' option.

Question 4

You have an Azure Synapse Analytics dedicated SQL pool. A nightly ELT process loads data into a staging table using PolyBase, then transforms and inserts it into a large fact table. You need to minimize data movement during the transformation step and ensure the fact table is optimized for large range scans. Which table distribution and index should you choose for the fact table?

Accepted Answer

Use a hash-distributed table on the primary join key with a clustered columnstore index.. For a large fact table in a dedicated SQL pool, hash distribution on the most frequently joined column minimizes data movement during transformations and joins. Pairing it with a clustered columnstore index provides columnar compression and segment elimination, which accelerates the large range scans and aggregations typical of fact-table queries. Together they satisfy both stated requirements.

Answer

Use a round-robin distributed table with a clustered index on the date column.

Answer

Use a replicated table with a clustered columnstore index.

Answer

Use a hash-distributed table on the date column with a heap index.

Question 5

You are implementing a Spark Structured Streaming job in Azure Databricks that reads from an Azure Event Hubs topic. The job must handle late-arriving data up to 10 minutes and produce aggregated results every 5 minutes. You need to configure the watermark and window. Which code snippet should you use?

Accepted Answer

df.withWatermark("eventTime", "10 minutes").groupBy(window("eventTime", "5 minutes")).agg(avg("temperature")). The correct configuration requires a watermark of 10 minutes to accommodate late data up to that limit, and a 5-minute tumbling window to produce results every 5 minutes. The withWatermark method sets the watermark on the event time column, and the window function with a single duration creates a tumbling window. The output mode must be set to update or append depending on the sink requirements.

Answer

df.withWatermark("eventTime", "10 minutes").groupBy(window("eventTime", "5 minutes", "5 minutes")).agg(avg("temperature"))

Answer

df.withWatermark("eventTime", "5 minutes").groupBy(window("eventTime", "10 minutes")).agg(avg("temperature"))

Answer

df.withWatermark("eventTime", "10 minutes").groupBy(window("eventTime", "10 minutes", "5 minutes")).agg(avg("temperature"))

Question 6

You are creating an Azure Data Factory pipeline that must copy data from an on-premises Oracle database to Azure Blob Storage every night. The on-premises network restricts inbound connections. You need to configure the integration runtime. What should you do?

Accepted Answer

Create a self-hosted integration runtime and install it on a machine in the on-premises network.. When an on-premises network blocks inbound connections, the only viable way for Azure Data Factory to reach an on-premises Oracle database is a self-hosted integration runtime. It runs inside the network, makes outbound connections to the ADF service, and bridges to local data sources. Azure IR, Azure-SSIS IR, and managed virtual network private endpoints cannot reach the on-premises database under these restrictions.

Answer

Create an Azure integration runtime and allow inbound connections from Azure Data Factory service tags.

Answer

Create an Azure-SSIS integration runtime and join it to an Azure virtual network.

Answer

Use the Azure Data Factory managed virtual network and private endpoint to connect to the on-premises Oracle database.

Question 7

You are developing an Azure Databricks notebook that processes JSON files stored in Azure Data Lake Storage Gen2. You need to read the files into a DataFrame and automatically infer the schema. Which code should you use?

Accepted Answer

spark.read.json("abfss://container@storage.dfs.core.windows.net/path"). The JSON data source in Spark automatically infers the schema when no schema is specified. Using spark.read.json with an abfss:// path reads the files from ADLS Gen2 and returns a DataFrame with inferred columns. The other options either misuse the API, use the wrong file format reader, or return unparsed text, so they do not meet the requirement.

Answer

spark.read.format("json").schema("infer").load("abfss://container@storage.dfs.core.windows.net/path")

Answer

spark.read.text("abfss://container@storage.dfs.core.windows.net/path")

Answer

spark.read.option("inferSchema", "true").csv("abfss://container@storage.dfs.core.windows.net/path")

Question 8

You are optimizing an Azure Synapse Analytics dedicated SQL pool that processes large fact tables. You need to improve query performance for a common join between a fact table and a dimension table. The fact table is distributed using hash distribution on a column that is not the join key. The dimension table is small and replicated. You want to minimize data movement during the join. What should you do?

Accepted Answer

Change the distribution of the fact table to hash distribute on the join key.. To minimize data movement during a join in a dedicated SQL pool, the distribution key of the large fact table should match the join key. This co-locates matching rows on the same distribution, avoiding shuffling. Round-robin distribution scatters data, materialized views do not change distribution, and columnstore indexes improve storage and scan efficiency but not data movement.

Answer

Add a clustered columnstore index on the join key column.

Answer

Change the distribution of the fact table to round-robin.

Answer

Create a materialized view that pre-joins the fact and dimension tables.

Question 9

You are building an Azure Stream Analytics job that reads from an Azure Event Hub capturing device telemetry. The job must emit results into an Azure Synapse Analytics dedicated SQL pool. You need to minimize latency and avoid intermediate storage. What should you do?

Accepted Answer

Configure the Stream Analytics job to output directly to the dedicated SQL pool using the Azure Synapse Analytics output adapter.. Stream Analytics provides a first-class output adapter for Azure Synapse Analytics dedicated SQL pools, enabling direct writes without staging. This minimizes latency and avoids intermediate storage, matching the requirement. The other approaches insert extra services or storage layers, adding delay and operational overhead that the scenario specifically seeks to avoid.

Answer

Configure the job to output to Azure Cosmos DB, then use a Synapse pipeline to copy data into the dedicated SQL pool.

Answer

Use Azure Functions as the output to insert rows into the dedicated SQL pool.

Answer

Write the Stream Analytics output to Azure Blob Storage, then use PolyBase in the dedicated SQL pool to load the data.

Question 10

You are implementing a mapping data flow in Azure Data Factory that processes data from an Azure SQL Database. The data flow includes a derived column transformation that adds a new column based on a complex expression. You need to ensure the expression handles null values appropriately. Which function should you use to replace null values with a default?

Accepted Answer

coalesce. The coalesce function is designed to return the first non-null value from a list, making it ideal for replacing nulls with a default. In a derived column transformation, you can write coalesce(column, 'default') to substitute nulls. Other functions like isNull, nullIf, and iif serve different purposes: isNull checks for null, nullIf creates null, and iif requires a condition. Therefore, coalesce is the correct function for this scenario.

Answer

nullIf

Answer

iif

Answer

isNull

Question 11

You are building a streaming pipeline in Azure Stream Analytics that reads JSON events from an Azure Event Hub and writes to Azure Synapse Analytics. The events include a nested array of sensor readings. You need to flatten this array so each reading becomes a separate row. Which Stream Analytics feature should you use?

Accepted Answer

CROSS APPLY with the ARRAY elements. CROSS APPLY is designed to unnest arrays in Stream Analytics queries. It takes an array expression and returns a row for each element, allowing downstream processing of individual sensor readings. The other options either aggregate, add metadata, or cannot emit multiple rows. Thus, CROSS APPLY is the only feature that meets the flattening requirement.

Answer

The TIMESTAMP BY clause with a partition key

Answer

A JavaScript user-defined function that iterates over the array and emits multiple rows

Answer

A tumbling window with a GROUP BY on the sensor ID

Question 12

You are using Azure Synapse Analytics to process data in a dedicated SQL pool. You need to ensure that queries against a large fact table perform well. The fact table is partitioned by date and distributed by a product key. Which two actions should you take? (Choose two.)

Accepted Answer

Create a clustered columnstore index on the fact table.. For a large fact table in a dedicated SQL pool, a clustered columnstore index provides optimal compression and query performance. Hash distribution on the product key ensures that joins with dimension tables on that key are co-located, reducing data movement. Together, these actions address both storage and distribution for analytical queries.

Answer

Create a nonclustered index on the date column.

Answer

Create a replicated table for the fact table.

Answer

Use round-robin distribution for the fact table.

Question 13

You are authoring an Azure Databricks notebook that reads Parquet files from Azure Data Lake Storage Gen2 and must write results to a Delta table. Users report that queries against the Delta table return stale data after each notebook run, even though the write succeeds. You need to ensure readers always see the latest committed data. What should you do?

Accepted Answer

Ensure the write commits through the Delta transaction log and readers query the table by name, not by a cached DataFrame. Delta Lake provides snapshot isolation via its transaction log, so a committed write is immediately visible to subsequent reads. Stale results typically come from caching a DataFrame or holding an old snapshot rather than from the table itself. Reading the table by name, without a cached DataFrame, ensures the reader resolves the latest committed version at query time.

Answer

Write the results with overwrite mode to the same Delta table path each run

Answer

Convert the Delta table to a Parquet table and rely on the file system for consistency

Answer

Call refreshTable or invalidate the cache on the reader session after each write

Question 14

You are implementing a Spark Structured Streaming job in Azure Databricks that consumes from an Azure Event Hubs topic and writes to a Delta table. The stream must tolerate reprocessing after a cluster restart without producing duplicate rows in the Delta table. You need to configure the write path accordingly. (Choose two.)

Accepted Answer

Enable checkpointing by setting the checkpointLocation option on the writeStream.. Exactly-once behavior at the sink requires both a durable record of processed offsets and an idempotent write. Checkpointing supplies the first by persisting progress so a restart resumes correctly, and a MERGE keyed on a business identifier supplies the second by making replay harmless. Neither append mode, one-time triggers, nor file compaction can prevent logical duplicates because none of them compare row identity across batches.

Answer

Set the trigger to Trigger.Once so the stream reads the topic only one time.

Answer

Enable auto compaction on the Delta table to merge small files and eliminate repeated rows.

Answer

Configure the write mode as append and rely on the Delta table schema to reject duplicates.

Question 15

You are implementing a Spark Structured Streaming job in Azure Databricks that reads from an Azure Event Hubs topic and writes to a Delta table. The job must handle late-arriving data up to 10 minutes and aggregate counts per device every 5 minutes. Which combination of settings should you use?

Accepted Answer

Use a tumbling window of 5 minutes and set watermark to 10 minutes on the event timestamp.. A tumbling window of 5 minutes with a 10-minute watermark on the event timestamp satisfies both the aggregation interval and the late-data tolerance. The tumbling window ensures non-overlapping 5-minute counts, and the watermark allows events up to 10 minutes late to be included. Other options use incorrect window sizes, slide intervals, or watermark durations that fail the requirements.

Answer

Use a sliding window of 5 minutes with a 10-minute slide interval and set watermark to 5 minutes.

Answer

Use a tumbling window of 10 minutes and set watermark to 5 minutes.

Answer

Use a hopping window of 5 minutes with a 5-minute hop and set watermark to 10 minutes.