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

Question 1

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?

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

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 2

You are building an Azure Synapse Analytics pipeline that processes JSON files landing in Azure Data Lake Storage Gen2. The files contain nested arrays representing order line items. You need to flatten this nested structure into a tabular format within a Mapping Data Flow before loading to a dedicated SQL pool. The solution must minimize data movement and avoid writing intermediate files to storage. Which transformation should you use to flatten the nested arrays?

Accepted Answer

Flatten transformation. Flatten transformation is purpose-built for unrolling nested arrays into rows within Mapping Data Flows. It processes data in-memory during pipeline execution, eliminating the need for intermediate files. Derived Column, Aggregate, and Pivot transformations do not change the grain from array elements to rows, so they cannot produce the required tabular output from nested JSON.

Answer

Derived Column transformation

Answer

Aggregate transformation

Answer

Pivot transformation

Question 3

You are building an Azure Stream Analytics job that reads JSON events from an Azure Event Hub and writes aggregated results to an Azure Synapse Analytics dedicated SQL pool. The events include a field named `EventTime` that is sometimes missing or malformed. You need the job to process only events with a valid `EventTime` and route invalid events to a separate output for later inspection. What should you do?

Accepted Answer

In the Stream Analytics query, use a WITH clause to define a filtered stream that selects events where TRY_CAST(EventTime AS datetime) IS NOT NULL, and write the excluded events to a separate output using a second query.. Stream Analytics queries can filter and split streams using standard SQL expressions. TRY_CAST safely converts values and returns NULL for malformed input, allowing a filtered stream of valid events. A second query selecting the complement routes invalid events to a separate output. This approach keeps the job running without failures and provides a mechanism to inspect bad data later.

Answer

Configure the Event Hub input to use the JSON serialization format with the `EventTime` field marked as required, so the job automatically drops events missing that field.

Answer

Use a JavaScript user-defined function in the query to validate `EventTime` and throw an exception for invalid events, which Stream Analytics will automatically redirect to the job's error log.

Answer

Add a second output to the job that writes to Azure Blob Storage, and configure the Event Hub input to send malformed events to that output.

Question 4

You are using Azure Databricks to process a large dataset stored in Delta Lake. You need to reduce the number of files scanned during queries by organizing data into folders based on a commonly filtered column. Which Delta Lake feature should you implement?

Accepted Answer

Partitioning the Delta table by the filtered column. Partitioning a Delta table by a frequently filtered column creates separate folders for each distinct value, allowing Delta Lake to skip irrelevant folders during queries. This reduces the number of files scanned and improves performance. Z-ORDER, Change Data Feed, and OPTIMIZE address different aspects and do not provide folder-level organization.

Answer

Z-ORDER BY on the filtered column

Answer

Enabling Change Data Feed on the Delta table

Answer

Running OPTIMIZE with file compaction

Question 5

You have an Azure Databricks notebook that processes a large Delta table. The notebook uses a structured streaming query to read from the Delta table and write to another Delta table. The source table receives frequent updates and deletes. You need the streaming query to process both new data and changes (updates and deletes) from the source table. What should you do?

Accepted Answer

Enable Change Data Feed on the source Delta table and configure the streaming query to read the change data feed.. Delta Lake Change Data Feed (CDF) is the feature designed to capture row-level changes from a Delta table. When enabled, it records inserts, updates, and deletes, and a streaming query can read these changes as a stream. This allows downstream processing to react to all modifications, not just appends. Enabling CDF and reading the change data feed is the correct approach to meet the requirement.

Answer

Convert the source table to an external table and use `spark.readStream.format("delta").option("mergeSchema", "true")` to read all changes.

Answer

Use `readStream` with the `startingVersion` option set to the latest version and `maxFilesPerTrigger` set to 1.

Answer

Configure the streaming query to use `readStream` with the `ignoreDeletes` option set to true.

Question 6

You are developing an Azure Databricks notebook to process streaming data from Azure Event Hubs. The notebook must write the processed data to a Delta table with exactly-once processing guarantees. You need to configure the write operation. Which option should you use?

Accepted Answer

Write the stream to a Delta table using the `writeStream` method with `format("delta")` and a checkpoint location.. Structured streaming to Delta Lake with `writeStream` and a checkpoint location provides exactly-once processing because Delta's transaction log and the checkpoint work together to ensure each record is processed once. Other formats like Parquet lack transactional guarantees, and batch writes cannot handle streaming data. Writing to SQL via JDBC also lacks built-in exactly-once semantics.

Answer

Write the stream to a Parquet file using `writeStream` with `format("parquet")` and a checkpoint location.

Answer

Write the stream to an Azure SQL Database using `writeStream` with `format("jdbc")` and a checkpoint location.

Answer

Use `write` instead of `writeStream` to write the DataFrame to a Delta table.

Question 7

You are building an Azure Data Factory pipeline that must copy data from an on-premises SQL Server to Azure Blob Storage. The pipeline runs on a schedule every hour. You need to ensure that the copy activity can securely access the on-premises SQL Server. What should you configure?

Accepted Answer

A self-hosted integration runtime installed on a machine in the on-premises network.. To copy data from an on-premises SQL Server, Azure Data Factory requires a self-hosted integration runtime. This component is installed on a machine in the on-premises network and handles the data movement securely. Other options either do not provide on-premises connectivity or are meant for different Azure services.

Answer

A VPN gateway between the on-premises network and Azure.

Answer

An Azure Integration Runtime with a managed virtual network.

Answer

An Azure SQL Database linked service with a private endpoint.

Question 8

You are creating an Azure Data Factory pipeline that must copy data from an on-premises SQL Server to Azure Blob Storage daily. The on-premises network restricts inbound connections, and you need a secure connection without exposing the SQL Server to the public internet. What should you use to connect to the on-premises SQL Server?

Accepted Answer

Self-hosted integration runtime. To connect Azure Data Factory to an on-premises SQL Server without exposing it to the internet, you install a self-hosted integration runtime on a machine in the on-premises network. This runtime initiates outbound connections to Azure, so no inbound firewall rules are needed. It is the correct and standard solution for hybrid data movement with Data Factory.

Answer

Azure ExpressRoute

Answer

Azure Integration Runtime with a managed virtual network

Answer

Azure Private Link

Question 9

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 10

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 11

You are using Azure Synapse Analytics dedicated SQL pool to process large fact tables. You need to improve query performance for joins between a large fact table and a small dimension table. The dimension table is less than 2 GB. What should you do?

Accepted Answer

Replicate the dimension table to all distributions.. Replicating a small dimension table in a dedicated SQL pool ensures each distribution has a local copy, eliminating data movement during joins with large fact tables. Hash and round-robin distributions do not guarantee colocation, and columnstore indexes do not solve data movement.

Answer

Round-robin distribute the dimension table.

Answer

Create a clustered columnstore index on the dimension table.

Answer

Hash distribute the dimension table on the join key.

Question 12

You have an Azure Synapse Analytics workspace with a dedicated SQL pool. You need to create an external table that references Parquet files stored in Azure Data Lake Storage Gen2. The external table will be used for ad-hoc queries. Which statement correctly describes the required components?

Accepted Answer

You must create a database scoped credential, an external data source, and an external file format.. Creating an external table in a dedicated SQL pool requires three components: a database scoped credential for authentication, an external data source for the storage location, and an external file format for Parquet. These are essential for the pool to read the external data. Other options are either incomplete or describe different features.

Answer

You only need to create an external data source and an external file format.

Answer

You must create a PolyBase external table using the CREATE EXTERNAL TABLE AS SELECT (CETAS) statement.

Answer

You must use the OPENROWSET function instead of an external table.

Question 13

You are designing a data processing solution in Azure Data Factory that must process files as they arrive in Azure Blob Storage. The solution must trigger a pipeline automatically when a new file is created, and then run a Databricks notebook to process the file. You need to configure the trigger and the pipeline activity. Which two actions should you perform? (Choose two.)

Accepted Answer

Add a Databricks Notebook activity to the pipeline and configure it to pass the file path as a parameter.. To trigger a pipeline when a file is created in Blob Storage, you need an event-based trigger of type BlobCreated. To process the file with a Databricks notebook, you add a Databricks Notebook activity and pass the file path as a parameter. Tumbling window triggers are scheduled, not event-driven, and Copy or Stored Procedure activities do not run Databricks notebooks.

Answer

Add a Copy activity to the pipeline to copy the file to a staging location before processing.

Answer

Add a Stored Procedure activity to the pipeline to call a stored procedure in Azure SQL Database.

Answer

Create a tumbling window trigger and configure it to run every 5 minutes to check for new files.

Question 14

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 15

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.