DP-203 Secure, monitor, and optimize data storage and data processing Practice Test 4 — 15 Questions

Question 1

An Azure Data Factory pipeline runs multiple times daily, loading data from an on-premises SQL Server to Azure Blob Storage. You notice that the pipeline sometimes fails due to transient network errors. You need to implement a retry policy with exponential backoff. Which configuration should you apply?

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

Accepted Answer

Set the activity's retry property to 3 and enable exponential backoff.. Azure Data Factory activity retry now supports exponential backoff. You can enable it by setting the retry property and enabling exponential backoff. Option B correctly configures 3 retries with exponential backoff. Option C only sets a fixed interval without exponential backoff. Options A and D are incorrect because retry is configured at the activity level, not pipeline or trigger level.

Answer

Set the pipeline's retry property to 3 and retry interval to 60 seconds.

Answer

Set the activity's retry property to 3 and retry secs to 60.

Answer

Set the trigger's retry policy to 3 with exponential backoff.

Question 2

You are designing a solution to monitor the performance of Azure Synapse Analytics dedicated SQL pools. You need to identify queries that are consuming more than 100 GB of memory and have been running for more than 30 minutes. Which DMV should you query?

Accepted Answer

sys.dm_pdw_exec_requests. Sys.dm_pdw_exec_requests, is correct because it provides detailed information about active or recently completed queries, including memory consumption (estimated_memory_kb) and duration (start_time, total_elapsed_time). You can filter this DMV to find queries with memory > 100 GB and runtime > 30 minutes. Option A (sys.dm_pdw_resource_waits) shows resource wait statistics, not memory usage. Option B (sys.dm_pdw_nodes) provides node-level information, not query details. Option D (sys.dm_pdw_waits) shows wait types for queries, not memory or duration.

Answer

sys.dm_pdw_resource_waits

Answer

sys.dm_pdw_nodes

Answer

sys.dm_pdw_waits

Question 3

Your company uses Azure Blob Storage to store backups. You need to ensure that data is encrypted at rest using a customer-managed key stored in Azure Key Vault. Which feature should you enable?

Accepted Answer

Azure Storage Service Encryption with customer-managed keys. Azure Storage Service Encryption (SSE) encrypts data at rest and supports customer-managed keys stored in Azure Key Vault. Option A is incorrect because Azure Purview is a data governance service, not for encryption. Option B is incorrect because Azure Disk Encryption is used for virtual machine disks, not Blob Storage. Option D is incorrect because Azure Information Protection is for classification and labeling. Therefore, option C is correct.

Answer

Azure Purview

Answer

Azure Disk Encryption

Answer

Azure Information Protection

Question 4

You are optimizing an Azure Synapse Analytics dedicated SQL pool. A frequent query scans a large fact table and filters on a date column. You notice that the query uses a full table scan. What is the most effective way to improve query performance?

Accepted Answer

Partition the table on the date column.. Partitioning the table on the date column enables partition elimination, which allows the query to scan only the relevant partitions instead of the entire table. Option A is incorrect because a nonclustered index on the date column can help with point lookups but is less effective for large scans and filtering on a range of dates. Option B is incorrect because a clustered columnstore index improves compression and scan performance but does not provide partition elimination for date filtering. Option C is incorrect because round-robin distribution distributes data evenly but does not optimize for filtering on a specific column.

Answer

Create a nonclustered index on the date column.

Answer

Create a clustered columnstore index on the table.

Answer

Change the distribution to round-robin.

Question 5

You need to monitor the health of your Azure Data Factory pipelines and set up alerts for failures. Which Azure service should you use to collect and analyze pipeline run logs?

Accepted Answer

Azure Monitor. Azure Monitor is the primary service for collecting and analyzing pipeline run logs and metrics in Azure Data Factory. Option A is wrong because Azure Purview is a data governance service, not for monitoring. Option B is wrong because Azure Log Analytics is a component of Azure Monitor used for querying logs, but Azure Monitor itself is the service that collects and analyzes the logs. Option D is wrong because Azure Sentinel is a SIEM (Security Information and Event Management) solution, not for pipeline monitoring.

Answer

Azure Purview

Answer

Azure Log Analytics

Answer

Azure Sentinel

Question 6

Your organization uses Azure Data Lake Storage Gen2 for a data lake. You need to prevent accidental deletion of data by enabling a soft delete policy. Which configuration is required?

Accepted Answer

Enable blob soft delete on the storage account.. Azure Data Lake Storage Gen2 supports blob soft delete, which protects against accidental deletion by retaining deleted blobs for a specified retention period. Option A is incorrect because Azure Resource Manager locks prevent the deletion or modification of the storage account itself, not the data within it. Option B is incorrect because Azure Backup is designed for backing up VMs, SQL databases, and other workloads, not for managing soft delete of blobs. Option C is incorrect because blob versioning preserves previous versions of blobs, but it does not prevent deletion of the current version; soft delete is specifically for recovery from accidental deletion.

Answer

Apply an Azure Resource Manager lock.

Answer

Configure Azure Backup for the storage account.

Answer

Enable blob versioning.

Question 7

An Azure Synapse Analytics pipeline uses a Copy activity to ingest data from Azure Blob Storage into a dedicated SQL pool. You notice that the data load is slow. You need to improve performance by enabling staging. What is the primary benefit of using staging?

Accepted Answer

It allows PolyBase to use parallel loading for better throughput.. Staging allows PolyBase to bulk load data efficiently. Option A is wrong because staging may actually improve data consistency. Option B is wrong because staging reduces load on the SQL pool. Option D is wrong because staging uses blobs, not the SQL pool.

Answer

It reduces the amount of data scanned in the source.

Answer

It enables data validation before loading.

Answer

It transforms data into columnstore format before loading.

Question 8

Which TWO actions can you take to optimize the performance of an Azure Synapse Analytics dedicated SQL pool? (Choose two.)

Accepted Answer

Replicate small dimension tables.. Replicating small dimension tables across all distributions reduces data movement during joins and improves query performance. Option E is correct because hash distribution on a column used in joins and aggregations ensures that rows with the same key are colocated on the same distribution, minimizing data shuffling. Option A is incorrect: scaling up increases resources but is not a targeted performance optimization and may not address underlying distribution or indexing issues. Option C is incorrect: heap indexes are generally not optimal for fact tables because they lack compression and indexing benefits; clustered columnstore indexes are recommended. Option D is incorrect: round-robin distribution distributes data evenly but does not reduce data movement for joins and aggregations, often leading to poor query performance.

Answer

Scale up the SQL pool to a higher DWU.

Answer

Use heap indexes for fact tables.

Answer

Use round-robin distribution for all large fact tables.

Question 9

Which THREE security features are available in Azure Data Lake Storage Gen2 to protect data at rest and in transit? (Choose three.)

Accepted Answer

Azure Storage firewalls and virtual network rules. Options A, C, and D are correct. Azure Storage firewalls and virtual network rules provide network-level security to restrict access. Encryption at rest using Storage Service Encryption (SSE) protects data at rest with AES-256 encryption. HTTPS ensures data in transit is encrypted. Option B (Azure Information Protection) is a classification and labeling service, not a built-in storage security feature. Option E (Azure Policy) is for governance and compliance, not direct data protection.

Answer

Azure Information Protection

Answer

Azure Policy

Question 10

Which TWO monitoring metrics in Azure Monitor for Azure Synapse Analytics dedicated SQL pool can help identify performance bottlenecks? (Choose two.)

Accepted Answer

Queued queries. Queued queries (B) indicate concurrency bottlenecks when queries are waiting for resources, while data movement (shuffle) metrics (D) help identify performance issues caused by data redistribution during query execution. CPU percentage (A) is a general resource metric but not a specific bottleneck indicator for dedicated SQL pool. Storage used (C) relates to capacity planning, not performance bottlenecks. DWU used (E) shows overall resource consumption but does not pinpoint specific bottlenecks.

Answer

CPU percentage

Answer

Storage used

Answer

DWU used

Question 11

You are monitoring an Azure Data Factory pipeline that runs daily. You notice that some runs are failing due to transient network errors. You want to automatically retry the failed activities with a 5-minute delay, up to 3 times. How should you configure this?

Accepted Answer

On each activity, set 'Retry' to 3 and 'Retry interval' to 00:05:00.. Azure Data Factory activities have a 'Retry' property that can be set to 3, and 'Retry interval' to 00:05:00. Option A is too low (1 retry). Option B is wrong because retry is per activity, not at pipeline level. Option D is wrong because the default retry is 0.

Answer

Set the pipeline's 'Concurrency' to 3 and 'Retry' to 1.

Answer

Leave the default settings as they are because Azure Data Factory automatically retries failed activities 3 times.

Answer

Configure a 'Retry' policy on the pipeline itself, setting maximum retries to 3 and retry interval to 5 minutes.

Question 12

Your team uses Azure Databricks for data processing. You need to implement a cost-control strategy that automatically terminates idle clusters after 30 minutes of inactivity, but allows users to override this policy for specific workloads that require long-running clusters. What is the most efficient approach?

Accepted Answer

Create a cluster policy that enforces auto-termination with a default of 30 minutes, but allows users to override the value for specific clusters.. Cluster policies in Azure Databricks allow admins to define rules (e.g., auto-termination) while allowing users to choose different values within allowed limits. Option A is wrong because manual termination is not automatic. Option B is wrong because cluster policies are not enforced by Azure Policy. Option C is wrong because auto-termination is a cluster-level setting, not a global policy.

Answer

Instruct all users to set auto-termination to 30 minutes on each cluster they create.

Answer

Configure a global auto-termination setting in the Azure Databricks workspace that terminates all clusters after 30 minutes of inactivity.

Answer

Use Azure Policy to enforce a tag that triggers a function to terminate idle clusters.

Question 13

You are a data engineer for a company that uses Azure Synapse Analytics dedicated SQL pool. You need to implement security best practices to protect sensitive data. Which TWO actions should you take? (Choose two.)

Accepted Answer

Enable Transparent Data Encryption (TDE) on the dedicated SQL pool.. Options C and E are correct. C: Transparent Data Encryption (TDE) encrypts data at rest in the dedicated SQL pool, protecting against unauthorized access to the physical storage. E: Column-level security restricts access to sensitive columns based on user roles or identities, ensuring that only authorized users can view or modify sensitive data. A is incorrect because firewall rules control network access, not data protection within the database. B is incorrect because Azure Storage encryption is enabled by default for all storage accounts and is not a security measure specific to Synapse SQL pool. D is incorrect because Dynamic Data Masking obfuscates data from all users, but it does not prevent access; users can still see the original data if they have permissions, unlike column-level security which can block access entirely.

Answer

Configure a firewall rule to allow only specific IP addresses.

Answer

Enable Azure Storage encryption for the underlying storage.

Answer

Use Dynamic Data Masking to obfuscate sensitive data from all users.

Question 14

Your organization uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled. You need to implement a monitoring strategy to detect and alert on unusual access patterns that could indicate a security breach. Which THREE services or features should you use? (Choose three.)

Accepted Answer

Enable Microsoft Defender for Storage to get security alerts about unusual access patterns.. Options A, C, and D are correct. A: Microsoft Defender for Storage provides security alerts for unusual access patterns. C: Ingesting logs into Microsoft Sentinel allows creation of analytics rules to detect anomalous patterns. D: Diagnostic settings on the storage account collect read, write, and delete logs necessary for analysis. Option B is incorrect because Azure Policy is used for governance and enforcement of policies, not for monitoring access patterns. Option E is incorrect because Azure Monitor Metrics track transaction counts and latency but do not include detailed access logs required for detecting unusual patterns.

Answer

Apply Azure Policy to enforce encryption and access policies.

Answer

Use Azure Monitor Metrics to track storage account transactions and latency.

Question 15

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline is failing with a 'Gateway is offline' error. What is the most likely cause?

Accepted Answer

The Self-Hosted Integration Runtime is not running or cannot connect to the Azure Data Factory service.. The Self-Hosted Integration Runtime (SHIR) acts as the gateway between on-premises data sources and Azure Data Factory. If the SHIR is not running or cannot communicate with the Azure Data Factory service, the pipeline fails with a 'Gateway is offline' error. Option A is incorrect because using the Azure Integration Runtime for an on-premises source would cause a different error, not 'Gateway is offline'. Option B is incorrect because region configuration for the Azure Integration Runtime is irrelevant when a SHIR is required. Option C is incorrect because the error relates to the gateway, not to SQL Server remote connection settings.

Answer

The Azure Integration Runtime is being used instead of a Self-Hosted Integration Runtime.

Answer

The Azure Integration Runtime is not configured to use the correct region.

Answer

The source SQL Server is not configured to allow remote connections from Azure.