DP-203 Monitor and optimize data storage and processing Practice Test 2 — 15 Questions

Question 1

You are reviewing an Azure Policy assignment that uses the above JSON to define a role-based access control (RBAC) action. What is the primary purpose of this policy?

Accepted Answer

To authorize generation of a shared access signature (SAS) token for the storage account.. The policy JSON defines a role-based access control (RBAC) action that grants the 'Microsoft.Storage/storageAccounts/listAccountSas/action' permission. This specific action authorizes the generation of a shared access signature (SAS) token at the storage account level, not at the container or blob level. Therefore, the primary purpose is to allow the generation of an account SAS token, which provides delegated access to storage services.

Answer

To assign RBAC roles to users for the storage account.

Answer

To enable delegation of access to a specific blob.

Answer

To allow users to set permissions on storage account containers.

Question 2

Your company runs a critical data pipeline using Azure Data Factory (ADF) that ingests data from multiple sources into an Azure Synapse Analytics dedicated SQL pool. Recently, you have observed that the pipeline frequently fails with the error: 'Operation for target table failed: 'Cannot insert duplicate key row in object 'dbo.FactSales' with unique index 'PK_FactSales'. The duplicate key value is (20241001, 12345).'' The pipeline uses a Copy activity with a stored procedure sink that merges data into the fact table. The fact table has a clustered columnstore index and a unique constraint on (DateKey, ProductKey). You need to modify the pipeline to handle duplicates without losing data and without impacting performance significantly. What should you do?

Accepted Answer

Configure the Copy activity sink to use 'upsert' behavior with the unique key columns.. Option A is correct because Azure Data Factory's Copy activity supports native upsert behavior when using a stored procedure sink, allowing it to handle duplicate key violations by updating existing rows instead of failing. By specifying the unique key columns (DateKey, ProductKey) in the upsert configuration, the pipeline can merge incoming data into the fact table without requiring manual staging or pre-cleanup, minimizing performance impact by leveraging the existing clustered columnstore index and unique constraint.

Answer

Change the distribution of the fact table to round-robin and remove the unique constraint.

Answer

Use a staging table and then execute a T-SQL MERGE statement to update or insert.

Answer

Add a pre-copy script to delete existing rows that match the incoming data before the copy.

Question 3

A company uses Azure Data Lake Storage Gen2 to store sensor data. They notice that queries on the data are slow. Which feature should they enable to optimize query performance without moving data?

Accepted Answer

Enable hierarchical namespace on the storage account.. Enabling hierarchical namespace on Azure Data Lake Storage Gen2 organizes blobs into a directory hierarchy, which allows query engines like Azure Synapse Analytics and Apache Spark to perform directory-level pruning and partition elimination. This reduces the amount of data scanned during queries, directly improving performance without requiring data movement or restructuring.

Answer

Implement Change Data Capture (CDC).

Answer

Enable Azure Search on the storage account.

Answer

Use PolyBase to query the data.

Question 4

You have an Azure Synapse Analytics dedicated SQL pool. You notice that some queries are taking longer than expected. After reviewing the query plans, you see that some queries are spilling to tempdb. What should you do to reduce tempdb spills?

Accepted Answer

Increase the resource class for the user executing the queries.. Tempdb spills occur when a query requires more memory than is allocated to it, forcing intermediate results to be written to disk. Increasing the resource class for the user executing the queries allocates more memory to that user's queries, reducing the likelihood of spills. This directly addresses the memory constraint that causes spills in a dedicated SQL pool.

Answer

Redistribute the tables using hash distribution.

Answer

Rebuild all columnstore indexes.

Answer

Add partitioning to the tables.

Question 5

A data engineering team uses Azure Stream Analytics to process real-time IoT data. They notice that the job's watermark delay is increasing over time, and the output is falling behind. The input is from Event Hubs with 10 partitions. The job uses a 5-minute hopping window with a 1-minute hop. What is the most likely cause?

Accepted Answer

The job is under-provisioned in terms of Streaming Units (SUs).. The increasing watermark delay and falling behind output indicate that the Stream Analytics job cannot keep up with the input throughput. With a 5-minute hopping window (1-minute hop) processing 10 Event Hubs partitions, the job requires sufficient Streaming Units (SUs) to handle the compute load. Under-provisioned SUs cause backpressure, leading to rising watermark delay as the job struggles to process events within the window boundaries.

Answer

The hopping window size is too large.

Answer

The late arrival tolerance is set too high.

Answer

The Event Hubs partition count does not match the Stream Analytics job's parallelism.

Question 6

You are designing a data pipeline that ingests JSON files from Azure Blob Storage into Azure Synapse Analytics using PolyBase. The files contain nested JSON arrays. What should you do to ensure that the data is loaded correctly?

Accepted Answer

Flatten the JSON arrays into a tabular format using Azure Data Factory or Databricks before loading.. Option A is correct because PolyBase in Azure Synapse Analytics cannot directly handle nested JSON arrays; it requires a flat, tabular structure. Azure Data Factory or Databricks can flatten the nested arrays into rows and columns before loading, ensuring compatibility with PolyBase's external table format.

Answer

Create an external table with the JSON file type and use a schema definition.

Answer

Use the OPENJSON function in T-SQL to parse the JSON during the load.

Answer

Use PolyBase with a JSON format file specifying the schema.

Question 7

Which TWO actions help optimize data storage costs in Azure Data Lake Storage Gen2?

Accepted Answer

Configure lifecycle management policies to move data to cool or archive tiers.. Option C is correct because Azure Data Lake Storage Gen2 supports lifecycle management policies that automatically transition data to cooler tiers (cool or archive) based on age or usage patterns. Moving infrequently accessed data to lower-cost tiers directly reduces storage costs without manual intervention.

Answer

Enable soft delete for blobs.

Answer

Enable geo-redundant storage (GRS) for the storage account.

Answer

Enable encryption at rest using customer-managed keys.

Question 8

Which THREE factors should you consider when choosing between rowstore and columnstore indexes in Azure Synapse Analytics?

Accepted Answer

The table size is expected to be over 1 TB.. Option C is correct because columnstore indexes in Azure Synapse Analytics are optimized for large-scale data warehousing workloads, where table sizes exceeding 1 TB benefit from high compression and columnar storage, significantly improving scan and aggregation performance. Rowstore indexes, in contrast, are less efficient for such large datasets due to higher I/O and storage overhead.

Answer

The table contains many NULL values in indexed columns.

Answer

The table will be partitioned frequently.

Question 9

You are a data engineer for a financial services company. You have an Azure Data Lake Storage Gen2 account containing historical trade data organized by date in the format 'yyyy/MM/dd'. Each day's data is stored as a collection of Parquet files. The data is used by a team of analysts who run ad-hoc queries using Azure Synapse Serverless SQL. Recently, the analysts have reported that queries scanning multiple months of data are slow. The storage account uses LRS with a general-purpose v2 tier. You have enabled hierarchical namespace. The data is not partitioned in any other way. You need to improve query performance without moving data or changing the storage tier. What should you do?

Accepted Answer

Create external tables with partition definition using the directory structure and ensure queries filter on the date column.. Option A is correct because Azure Synapse Serverless SQL can leverage the directory structure of Azure Data Lake Storage Gen2 as virtual partitions. By creating external tables with a partition definition that maps to the 'yyyy/MM/dd' folder hierarchy and ensuring queries filter on the date column, the serverless SQL engine performs partition elimination. This reduces the amount of data scanned, directly addressing the slow performance when querying multiple months of data without moving data or changing the storage tier.

Answer

Increase the query timeout setting in Azure Synapse Studio.

Answer

Redistribute the data using hash distribution on the date column.

Answer

Increase the data warehouse units (DWU) for the serverless SQL endpoint.

Question 10

A company uses Azure Synapse Analytics with dedicated SQL pools. They notice that query performance degrades significantly during peak hours. They have already scaled up the Data Warehouse Units (DWU) to the maximum. Which action should they take next to improve performance?

Accepted Answer

Enable result-set caching.. When a dedicated SQL pool is already at maximum DWU, further scaling is not possible. Enabling result-set caching stores query results in the SSD-based cache of the SQL pool, allowing repeated queries to be served directly from cache without re-scanning data or re-computing aggregations. This reduces I/O and CPU pressure during peak hours, improving performance for recurring queries without requiring additional compute resources.

Answer

Rebuild all clustered columnstore indexes.

Answer

Increase the number of concurrency slots.

Answer

Move the data to Azure Data Lake Storage Gen2.

Question 11

A data engineer is monitoring Azure Data Lake Storage Gen2 costs and notices high transaction costs for a specific container. The container stores Parquet files used by Azure Databricks for read-heavy analytics. The files are accessed frequently by multiple jobs. What is the most cost-effective way to reduce transaction costs?

Accepted Answer

Enable Azure CDN to cache the files.. Option D is correct because enabling Azure CDN caches the frequently accessed Parquet files at edge locations, reducing the number of direct read requests to Azure Data Lake Storage Gen2. This lowers transaction costs (both read and list operations) while maintaining low-latency access for read-heavy analytics workloads. The CDN serves cached content, so the storage account incurs fewer billable transactions.

Answer

Move the data to Azure Blob Storage cool tier.

Answer

Increase the Parquet file size to maximize block size.

Answer

Convert the container to Azure Files.

Question 12

A company runs a streaming pipeline using Azure Stream Analytics to ingest IoT data and output to Azure SQL Database. They notice that the output latency increases over time and eventually the job fails with a timeout error. What is the most likely cause?

Accepted Answer

The Azure SQL Database target table lacks proper indexes.. The most likely cause is that the Azure SQL Database target table lacks proper indexes. Without indexes, each batch of output from Stream Analytics triggers full table scans for inserts or updates, causing cumulative latency. Over time, the backlog exceeds the job's timeout threshold (default 5 minutes for output), leading to failure.

Answer

The Stream Analytics job has a high late arrival tolerance.

Answer

The event hub is not partitioned correctly.

Answer

The event hub consumer group is misconfigured.

Question 13

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.. Option B is correct because 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 14

A company uses Azure Synapse Analytics dedicated SQL pool for a data warehouse. They notice that some queries are using more memory than expected, causing resource contention. Which TWO actions should they take to diagnose and optimize memory usage?

Accepted Answer

Increase the resource class for the users running the heavy queries.. Option B is correct because increasing the resource class for users running heavy queries allocates more memory to those queries, reducing resource contention by ensuring they have sufficient memory to execute efficiently. Option D is correct because querying sys.dm_pdw_exec_requests DMV allows you to identify queries with high memory grants, which is the first step in diagnosing which queries are consuming excessive memory and need optimization.

Answer

Enable result-set caching.

Answer

Scale up the DWU setting.

Answer

Rebuild clustered columnstore indexes.

Question 15

A data engineer is optimizing an Azure Data Lake Storage Gen2 account used for big data analytics. The account contains billions of small files (under 1 MB). The analytics jobs are slow and cost more than expected. Which THREE actions should the engineer take to improve performance and reduce costs?

Accepted Answer

Convert data to columnar file formats such as Parquet.. Option A is correct because converting data to columnar formats like Parquet reduces the amount of data read during analytics queries, as only the necessary columns are scanned. This significantly improves query performance and lowers I/O costs, especially for big data workloads on Azure Data Lake Storage Gen2.

Answer

Move data to the cool tier to reduce storage costs.

Answer

Enable soft delete to protect against accidental deletion.

DP-203 Monitor and optimize data storage and processing Practice Test 2 — 15 Questions

Exhibit

DP-203 Monitor and optimize data storage and processing Practice Test 2 — 15 Questions

Exhibit