DP-203 Practice Test 44 — 15 Questions

Question 1

You are implementing a streaming solution using Azure Stream Analytics. The input is from an IoT Hub receiving telemetry from thousands of devices. The output is to Azure Synapse Analytics dedicated SQL pool. The requirement is to compute rolling averages over a 5-minute tumbling window and write results every minute. Which windowing function and output configuration should you use?

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

Accepted Answer

Use a HoppingWindow with size 5 minutes and hop 1 minute.. A HoppingWindow with a size of 5 minutes and a hop of 1 minute allows you to compute rolling averages over a 5-minute window while producing results every minute. This satisfies the requirement of outputting results at a higher frequency than the window duration, which is not possible with a TumblingWindow (which only outputs at the end of the window) or a SlidingWindow (which outputs on each event, not at fixed intervals).

Answer

Use a TumblingWindow with duration of 5 minutes and output every 5 minutes.

Answer

Use a SlidingWindow with duration 5 minutes and output every 1 minute.

Answer

Use a SessionWindow with timeout 5 minutes and maximum duration 10 minutes.

Question 2

You are monitoring an Azure Cosmos DB account using Azure Monitor. The 'Normalized RU Consumption' metric for a container is consistently above 90%. You need to ensure that the container can handle the load without throttling. What should you do?

Accepted Answer

Increase the provisioned throughput (RU/s) for the container.. The 'Normalized RU Consumption' metric indicates the percentage of provisioned throughput (RU/s) being used. Consistently above 90% means the container is operating near its capacity limit, risking throttling (HTTP 429 errors) during traffic spikes. Increasing the provisioned throughput (RU/s) directly raises the capacity, allowing the container to handle the load without throttling.

Answer

Change the partition key to a different property.

Answer

Switch the account to serverless mode.

Answer

Modify the indexing policy to exclude unused paths.

Question 3

You are designing a data processing pipeline in Azure Synapse Analytics that uses a mapping data flow with Azure Integration Runtime (IR). The pipeline runs slowly and you notice that the IR's data movement is limited by the number of cores. Which configuration should you adjust to improve performance?

Accepted Answer

Increase the 'Data Flow Compute Type' and 'Core Count' in the Azure IR settings.. The Azure Integration Runtime (IR) for mapping data flows uses a Spark cluster, and its performance is directly tied to the compute resources allocated. By increasing the 'Data Flow Compute Type' (e.g., from General Purpose to Memory Optimized) and the 'Core Count' (e.g., from 4 to 8 or 16 cores), you provide more parallel processing power, which directly addresses the core-limited data movement bottleneck.

Answer

Enable staging for the copy activity within the data flow.

Answer

Use a Self-Hosted IR instead of Azure IR for data flows.

Answer

Increase the 'Number of partitions' in the source transformation.

Question 4

You are reviewing an Azure PowerShell script that sets permissions on a directory in Azure Data Lake Storage Gen2. The script sets a default ACL for a user on the path 'sales/2024/01/'. What is the effect of the -DefaultScope parameter?

Accepted Answer

The ACL is inherited by all new child items created under this directory.. The -DefaultScope parameter in Azure Data Lake Storage Gen2 sets a default ACL entry. Default ACLs do not set permissions on the current directory; instead, they define permissions that are inherited by new child items (files and subdirectories) created under that directory. Therefore, option B is correct. Option A is incorrect because default ACLs do not replace the access ACL; access ACLs are set separately without -DefaultScope. Option C is incorrect because default ACLs do not apply to existing items; they only affect future items. Option D is incorrect because default ACLs apply to both new files and new subdirectories.

Answer

The ACL replaces the existing access ACL on the directory.

Answer

The ACL is applied to all existing files and subdirectories recursively.

Answer

The ACL is applied only to files, not subdirectories.

Question 5

You are troubleshooting an Azure Databricks job that writes data to Azure Data Lake Storage Gen2. The job fails with '403 Forbidden' error. The Databricks workspace uses a managed identity (system-assigned) for authentication. What should you verify?

Accepted Answer

The managed identity has 'Storage Blob Data Contributor' role assigned to the storage account. The 403 Forbidden error indicates that authentication succeeded but authorization failed. For a managed identity to write data to Azure Data Lake Storage Gen2, it must be assigned the 'Storage Blob Data Contributor' RBAC role on the storage account. This role grants read, write, and delete permissions for blobs and directories. Without this role assignment, the managed identity cannot write data, resulting in a 403 error. Even if the storage account name is correct (A), the firewall is configured (B), or a private endpoint exists (C), the missing RBAC role assignment will cause the failure.

Answer

The storage account name is correct

Answer

The storage account firewall is configured to allow Azure services

Answer

A private endpoint is configured between Databricks and the storage account

Question 6

Which TWO strategies reduce data movement in Azure Synapse Analytics pipelines? (Choose two.)

Accepted Answer

Use serverless SQL pool to query data in place. Serverless SQL pools in Azure Synapse Analytics allow you to query data directly from files in Azure Data Lake Storage or other external sources without moving the data into a dedicated SQL pool. This eliminates data movement entirely by using the compute resources of the serverless pool to process queries in place, leveraging the OPENROWSET or CREATE EXTERNAL TABLE syntax to read data from its original location.

Answer

Use Data Flow to transform data before loading

Answer

Use Stored Procedure activity to insert data

Answer

Use Copy activity to move data from source to staging

Question 7

Your team is developing a real-time data processing solution using Azure Stream Analytics. The input is from Azure Event Hubs. The output must be written to Azure SQL Database. You need to ensure that the processing guarantees exactly-once semantics for the output. Which output configuration should you use?

Accepted Answer

Set output mode to 'Exactly once'. Azure Stream Analytics supports exactly-once semantics for output to Azure SQL Database when the output mode is set to 'Exactly once'. This ensures that each event is processed and written exactly once, preventing duplicates even in the event of failures or retries. The 'Exactly once' mode uses internal checkpointing and transactional writes to guarantee this behavior.

Answer

Set output mode to 'When possible'

Answer

Set output mode to 'At least once'

Answer

Set output mode to 'Best effort'

Question 8

A data engineer is designing a monitoring solution for Azure Data Factory pipelines. They need to be alerted when a pipeline run fails or when the duration exceeds a threshold. The solution must minimize cost and operational overhead. Which approach should they use?

Accepted Answer

Use Azure Monitor metrics and activity logs to create alert rules for pipeline failures and duration.. Azure Monitor provides native, cost-effective alerting for Azure Data Factory pipelines using metrics (e.g., pipeline run duration) and activity logs (e.g., pipeline run failures). This approach requires no additional compute or log ingestion costs, as alerts are configured directly on the resource's monitoring data, minimizing both cost and operational overhead.

Answer

Configure Azure Event Grid to send pipeline run events to Azure Functions for alerting.

Answer

Send all pipeline run logs to Log Analytics and create alert rules based on custom log searches.

Answer

Create an Azure Logic App that runs every minute to check pipeline run status via REST API.

Question 9

You are tuning an Azure Stream Analytics job that reads from an Event Hub and writes to an Azure Synapse Analytics table. The job's SU% utilization is consistently at 90%. Which action would most likely reduce the SU% utilization?

Accepted Answer

Increase the number of streaming units (SU) allocated to the job.. Increasing the number of streaming units (SU) allocated to the job directly adds more compute resources, which reduces the SU% utilization by distributing the workload across more SUs. Since the job is consistently at 90% utilization, adding SUs lowers the per-SU load, preventing throttling and improving throughput. This is the standard scaling approach for Azure Stream Analytics when SU% is high.

Answer

Decrease the Event Hub throughput units.

Answer

Partition the output table in Azure Synapse Analytics.

Answer

Use a reference data join to filter events.

Question 10

You are using Azure Synapse SQL Pool to store a large fact table partitioned by date. Queries frequently filter on a specific date range and aggregate by a column called 'product_id'. Which table distribution and indexing strategy will minimize query execution time?

Accepted Answer

Hash distribution on product_id with clustered columnstore index. Hash distribution on product_id ensures that rows with the same product_id are co-located on the same distribution, enabling efficient local aggregation without data movement. The clustered columnstore index provides high compression and fast scan performance for large fact tables, especially when queries filter on a date range and aggregate by product_id.

Answer

Hash distribution on product_id with clustered rowstore index

Answer

Replicated table with clustered columnstore index

Answer

Round-robin distribution with clustered columnstore index

Question 11

You are deploying an Azure Synapse workspace using an ARM template. The template includes a Managed integration runtime with 'AutoResolve' location and a TTL of 10 minutes for data flows. After deployment, you notice that the first data flow execution takes a long time to start. What is the most likely cause?

Accepted Answer

The TTL setting is too low, causing the cluster to be recreated frequently.. The first data flow execution takes a long time because the TTL (time-to-live) setting of 10 minutes causes the cluster to be deallocated shortly after the previous run. When a new data flow starts after the TTL expires, a new cluster must be provisioned from scratch, which adds significant startup latency. A higher TTL (e.g., 60 minutes) would keep the cluster warm for subsequent executions, reducing cold-start delays.

Answer

The core count of 8 is insufficient for the data flow.

Answer

The AutoResolve location cannot be used for Managed IR.

Answer

The integration runtime type should be 'Self-Hosted' for data flows.

Question 12

Which TWO actions should you take to optimize performance of a dedicated SQL pool in Azure Synapse Analytics when loading large volumes of data?

Accepted Answer

Use a large batch size (e.g., 100 MB) for each copy operation.. Using a large batch size (e.g., 100 MB) for each copy operation minimizes the number of round trips and transaction commits, which significantly improves throughput when loading large volumes of data into a dedicated SQL pool. The PolyBase or COPY statement in Azure Synapse performs best when batches are large enough to leverage parallel processing and reduce overhead from frequent small writes.

Answer

Disable index on the target table after loading.

Answer

Use a small batch size (e.g., 1 MB) for each copy operation.

Answer

Use clustered columnstore index on the target table during load.

Question 13

You are running a Spark job in Azure Synapse Analytics that reads from a Delta Lake table and performs multiple transformations. The job fails with an out-of-memory error on the executors. Which action should you take first to resolve the issue?

Accepted Answer

Increase the executor memory setting in the Spark configuration.. An out-of-memory error on executors indicates that the available memory per executor is insufficient for the data being processed. Increasing the executor memory setting in the Spark configuration directly addresses this by allocating more heap space, allowing transformations to complete without spilling to disk or failing. This is the first and most straightforward action to take before optimizing partitioning or caching.

Answer

Enable checkpointing to truncate the lineage.

Answer

Decrease the number of partitions to reduce overhead.

Answer

Use the cache() action on intermediate DataFrames.

Question 14

You are a data engineer for a global e-commerce company. The company uses Azure Synapse Analytics dedicated SQL pool for its data warehouse. The environment includes a large fact table 'Sales' distributed by hash on 'CustomerID', and dimension tables 'Customer' (hash-distributed on 'CustomerID') and 'Product' (replicated). Recently, queries that join Sales and Customer are performing poorly. You run a query to check data skew on the Sales table and find that one distribution has 40% more rows than the average. Additionally, the Customer table has high data movement during joins. You need to optimize the performance of these joins. What should you do?

Accepted Answer

Change the distribution of the Customer table to replicated.. To change the distribution of the Customer table to replicated. In Azure Synapse Analytics dedicated SQL pool, replicated tables are small enough to be cached on each compute node, eliminating the need to move data during joins. Since Customer is a dimension table, it is likely small enough to benefit from replication. This directly addresses the high data movement during joins. Option B (increasing DWU) would allocate more resources but does not fix the root cause of data movement and may only mask the issue. Option C (changing Sales to round-robin) is not recommended because fact tables in star schemas should be hash-distributed on a join key to minimize data movement. Option D (changing Sales distribution key to ProductID) would not improve the join with Customer unless the join is on ProductID, but the problem is with the Sales-Customer join; changing the distribution key to ProductID would affect the join with Product, not Customer, and could worsen the skew issue.

Answer

Increase the data warehouse performance level (DWU) to allocate more resources.

Answer

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

Answer

Change the distribution key of the Sales table to 'ProductID' to align with the Product table.

Question 15

You are using Azure Data Factory to copy data from Azure Blob Storage to Azure SQL Database. The copy operation fails with error 'Cannot insert duplicate key'. What is the most likely cause and solution?

Accepted Answer

The sink table has a primary key, and the source contains duplicate rows; enable upsert in the copy activity. The error 'Cannot insert duplicate key' indicates that the sink table in Azure SQL Database has a primary key or unique constraint, and the source data contains rows with duplicate key values. Enabling upsert in the copy activity allows Azure Data Factory to handle duplicates by updating existing rows instead of failing, resolving the conflict.

Answer

The source files are in an incompatible format; convert to CSV

Answer

The sink table schema does not match the source; update the schema

Answer

The copy activity is not using staging; enable staging