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

Question 1

Your team is building a real-time dashboard using Azure Stream Analytics. The data source is an Azure Event Hub that receives clickstream events. You need to output aggregated data (counts per page per minute) to an Azure SQL Database for reporting. The query must handle late-arriving events and ensure exactly-once semantics. Which Stream Analytics feature should you use?

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

Accepted Answer

Use the Input Order section in the Stream Analytics job configuration to set a late arrival tolerance window.. The Input Order section in Azure Stream Analytics job configuration allows you to set a late arrival tolerance window, which handles late-arriving events by buffering them for a specified duration. This ensures that events arriving after their timestamp are still included in the correct window for aggregation, supporting exactly-once semantics when combined with checkpointing and idempotent output to Azure SQL Database.

Answer

Use a temporal window function with a 'late arrival' policy specified in the query.

Answer

Define a watermark in the query to specify a maximum out-of-order tolerance.

Answer

Set the event ordering policy to 'Adjust' to reorder events within a certain time window.

Question 2

You are using Azure Data Lake Storage Gen2 as the data lake for your organization. You need to process files in the 'incoming' folder using a scheduled Azure Databricks notebook. After processing, the files should be moved to the 'processed' folder. The files are large (up to 10 GB) and you want to minimize the time to move them. Which approach should you use?

Accepted Answer

Use the Azure Databricks dbutils.fs.mv() to move the file.. `dbutils.fs.mv()` performs a metadata-only rename operation on Azure Data Lake Storage Gen2, which is instantaneous regardless of file size. This avoids any data movement, making it the fastest approach for moving large files (up to 10 GB) between folders within the same storage account.

Answer

Use Azure Data Factory with a Copy activity to move the file, then delete the source.

Answer

Change the file's metadata to update its directory path.

Answer

Use the Azure Databricks dbutils.fs.cp() to copy the file to the processed folder, then delete the original.

Question 3

You are designing a data processing pipeline in Azure Data Factory that uses a Mapping Data Flow. You need to handle errors gracefully, such as when a row fails to convert a column value. Which TWO actions should you take? (Choose two.)

Accepted Answer

Configure the sink transformation to allow errors and log error rows to a separate file.. Configuring the sink transformation to allow errors and log error rows to a separate file enables graceful error handling in Mapping Data Flows. This approach captures rows that fail during transformation (e.g., type conversion errors) and writes them to a designated error output, allowing the pipeline to continue processing valid rows. Option E is correct because a Conditional Split transformation can proactively identify rows that are likely to cause errors based on a condition (e.g., checking for null or invalid data types) and route them to a separate path for logging or remediation, preventing them from reaching the sink and causing failures.

Answer

Wrap the data flow in a Try-Catch activity in the pipeline.

Answer

Set the data flow's error handling to 'Abort on error' to stop processing on first failure.

Answer

Enable schema drift on the source to automatically handle data type mismatches.

Question 4

You are using Azure Stream Analytics to process real-time data from an IoT hub. The output is sent to Azure Blob Storage for long-term storage. You need to ensure that the output files are partitioned by date and hour for easy querying. Which THREE configurations should you set? (Choose three.)

Accepted Answer

Use a path pattern that includes {date} and {time} tokens.. Azure Stream Analytics supports custom path patterns for Blob Storage output, where {date} and {time} tokens automatically resolve to the processing date and hour (in UTC). This allows partitioning output files into a folder structure like 'YYYY/MM/DD/HH', enabling efficient querying by date and hour without post-processing.

Answer

Configure the event ordering policy to adjust late events.

Answer

Set the compatibility level to 1.2 or higher.

Question 5

Refer to the exhibit. You are an Azure data engineer responsible for ensuring that all storage accounts used in data pipelines enforce HTTPS traffic. You apply the Azure Policy definition shown above. Later, a data engineer creates a new storage account with 'Enable secure transfer' set to Disabled. What will happen when the policy is evaluated?

Accepted Answer

The storage account creation will be denied and the request will fail.. The Azure Policy definition shown in the exhibit uses the 'Deny' effect for the 'Secure transfer to storage accounts should be enabled' policy. When a data engineer attempts to create a storage account with 'Enable secure transfer' set to Disabled, the policy evaluation occurs at resource creation time (before the resource is provisioned). Because the request violates the policy's condition (HTTPS traffic not enforced), Azure Resource Manager denies the creation request and returns a failure, preventing the non-compliant storage account from being created.

Answer

The storage account will be created, but the policy will be evaluated later during a compliance scan.

Answer

The storage account will be created with HTTPS enabled automatically.

Answer

The storage account will be created, but an audit event will be logged.

Question 6

Refer to the exhibit. You are querying the sys.external_tables view in an Azure Synapse Analytics serverless SQL pool. The query returns no rows, but you believe that external tables have been created. What is the most likely reason?

Accepted Answer

The external tables were created using OPENROWSET, not CREATE EXTERNAL TABLE, so they do not appear in sys.external_tables.. The user believes external tables have been created, but if they were created using CREATE EXTERNAL TABLE, they would appear in sys.external_tables. Since no rows are returned, it indicates that the external tables were not created via CREATE EXTERNAL TABLE; instead, the user likely used OPENROWSET to query data directly, which does not create a catalog entry. Therefore, the correct answer is C.

Answer

The external tables are using PolyBase, which is not supported in serverless SQL pool.

Answer

Serverless SQL pool does not support external tables; you must use a dedicated SQL pool.

Answer

The user does not have permission to view the sys.external_tables view.

Question 7

Refer to the exhibit. You are reviewing an ARM template snippet for an Azure Synapse Analytics workspace. The template defines an integration runtime. A colleague asks whether this integration runtime can be used to copy data from an on-premises SQL Server database to Azure Blob Storage. What should you answer?

Accepted Answer

Yes, because the integration runtime is self-hosted and can access on-premises data sources.. The ARM template snippet defines a self-hosted integration runtime, which is designed to be installed on a local machine inside the corporate network. This allows it to directly connect to on-premises SQL Server databases using native drivers (e.g., ADO.NET) and then copy data to Azure Blob Storage via the Azure cloud. Self-hosted IRs are the correct choice for hybrid data movement scenarios where the source is on-premises and the destination is cloud-based.

Answer

No, you need an Azure integration runtime that is configured for on-premises access.

Answer

No, you need a managed virtual network integration runtime for on-premises sources.

Answer

Yes, but only if the integration runtime is installed on a domain-joined machine.

Question 8

You are designing a data processing solution in Azure Synapse Analytics. The solution must process streaming data from Azure Event Hubs and store the results in a dedicated SQL pool. You need to choose the most appropriate service for near real-time ingestion with minimal latency. What should you use?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is purpose-built for near real-time stream processing with sub-second latency, directly integrates with Azure Event Hubs as an input source and dedicated SQL pool as an output sink, and provides a SQL-like query language for defining transformations. This minimizes architectural complexity and latency compared to other services.

Answer

Azure Databricks with Structured Streaming

Answer

Azure Data Factory

Answer

Azure Functions with Event Hub trigger

Question 9

In Azure Synapse Analytics serverless SQL pool, you query Parquet files stored in Azure Data Lake Storage Gen2. You notice that queries are slow. Which configuration change is most likely to improve performance?

Accepted Answer

Use OPENROWSET with a properly defined schema and file format. OPENROWSET with an explicitly defined schema and file format (e.g., FORMAT='PARQUET') enables the serverless SQL pool to bypass schema inference, which is a costly runtime operation. By providing a proper schema and file format, the query engine can directly read the Parquet metadata and column statistics, significantly reducing I/O and CPU overhead. This is the most direct and effective performance tuning change for querying Parquet files in a serverless SQL pool.

Answer

Partition the data in Azure Data Lake Storage Gen2

Answer

Create a pipeline in Azure Synapse to preprocess the data

Answer

Increase the DWU setting of the serverless SQL pool

Question 10

You are designing a data processing solution for a financial services company. The solution must process sensitive customer data in Azure Databricks with encryption at rest and in transit, and must also support row-level security (RLS). Which combination of features should you use?

Accepted Answer

Use Azure Databricks with Delta Lake and enable row-level security on the Delta table. Delta Lake in Azure Databricks now supports row-level security (RLS) natively through Delta Sharing and Spark SQL predicates, allowing you to enforce data access policies directly on the Delta table without external systems. Combined with Azure Databricks' built-in encryption at rest (using Azure Storage Service Encryption or customer-managed keys) and in transit (TLS 1.2+), this meets all requirements without introducing additional services.

Answer

Use Azure SQL Database with row-level security and connect Databricks via JDBC

Answer

Use Azure Databricks with Azure Key Vault for encryption and implement RLS in application code

Answer

Use Azure Synapse Analytics dedicated SQL pool with dynamic data masking and always encrypted

Question 11

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline fails intermittently with timeout errors. You need to improve reliability. What should you do?

Accepted Answer

Enable fault tolerance and use staging. Enabling fault tolerance with staging in Azure Data Factory allows the copy activity to automatically retry transient failures (such as timeout errors) by staging intermediate data in Azure Blob Storage. This mechanism uses a two-phase commit approach: data is first written to a staging location, and then committed to the final sink only after successful validation, which isolates the pipeline from intermittent source or sink failures.

Answer

Use a self-hosted integration runtime with high availability

Answer

Change the source to Azure SQL Database

Answer

Increase the degree of copy parallelism

Question 12

You are designing a data processing solution for a healthcare organization. The solution must process streaming data from IoT devices and store it in Azure Data Lake Storage Gen2. The data must be available for both real-time dashboards and historical analysis. You need to minimize operational overhead. What should you do?

Accepted Answer

Use Azure Stream Analytics to output to both Power BI and Data Lake Storage. Azure Stream Analytics can directly output to both Power BI (for real-time dashboards) and Azure Data Lake Storage Gen2 (for historical analysis) in a single job, minimizing operational overhead by avoiding the need for multiple services or custom code. This serverless, fully managed service handles streaming data processing with low latency and integrates natively with Azure IoT Hub or Event Hubs for ingestion, making it ideal for healthcare IoT scenarios.

Answer

Ingest data via Azure Functions and write to Data Lake Storage; use Power BI to query Data Lake

Answer

Use Azure Databricks with Structured Streaming to write to Data Lake Storage and use Power BI DirectQuery

Answer

Ingest data to Azure Event Hubs, then use Event Hubs Capture to store in Data Lake Storage; use Power BI with Event Hubs

Question 13

You are monitoring an Azure Synapse Analytics pipeline that runs daily. The pipeline uses a data flow to transform data. You notice that the data flow is slow and consumes a lot of compute resources. Which action can you take to optimize performance?

Accepted Answer

Partition the data flow by a key column. Partitioning the data flow by a key column (Option D) enables parallel processing across multiple compute nodes, reducing data shuffling and improving throughput. In Azure Synapse data flows, partitioning distributes the workload evenly, which directly addresses the slow performance and high resource consumption observed in the pipeline.

Answer

Use a staging table to load data before transformation

Answer

Increase the batch size in the data flow

Answer

Use PolyBase to load data into the dedicated SQL pool

Question 14

You need to process a large dataset stored as CSV files in Azure Data Lake Storage Gen2 using Azure Databricks. The processing involves several transformations and aggregations. You want to minimize shuffle operations. Which approach should you use?

Accepted Answer

Use Delta Lake and apply Z-ordering on the columns used in filters and aggregations. Z-ordering in Delta Lake co-locates related data within files based on specified columns, which significantly reduces the amount of data scanned during filter and aggregation operations. By minimizing the data that needs to be read, Z-ordering inherently reduces shuffle operations because fewer partitions need to be exchanged across the cluster during transformations. This approach is specifically designed to optimize query performance on large datasets in Azure Databricks without increasing the number of shuffle stages.

Answer

Cache the data in memory after reading

Answer

Use bucketing with a fixed number of buckets

Answer

Partition the data by a high-cardinality column

Question 15

You are developing a data processing solution in Azure Synapse Analytics. The solution must support both batch and streaming data ingestion into a dedicated SQL pool. You need to ensure that data from streaming sources is available for queries within 5 seconds. Which approach should you use?

Accepted Answer

Use Azure Stream Analytics with a custom SQL function that writes directly to the dedicated SQL pool. Azure Stream Analytics can output directly to Azure Synapse Analytics dedicated SQL pool using the built-in Azure Synapse Analytics output adapter, which supports high-throughput, low-latency writes. This enables streaming data to be available for queries within seconds, meeting the 5-second latency requirement without intermediate storage or batch processing steps.

Answer

Use Azure Databricks with Structured Streaming, write to Data Lake Storage, and then use PolyBase to load into SQL pool

Answer

Use Azure Data Factory with tumbling window triggers to load data from Event Hubs every 5 seconds

Answer

Use Event Hubs Capture to write to Data Lake Storage, then use PolyBase to load into the SQL pool every 5 seconds