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

Question 1

Your team is using Azure Synapse Analytics to process sensitive customer data. You need to ensure that column-level security is applied to a specific table so that only users with a certain role can view certain columns. Which feature should you use?

Accepted Answer

Column-level security (CLS). Option A is correct because column-level security in Azure Synapse restricts column access based on user's group membership. Option B is incorrect because row-level security restricts rows, not columns. Option C is incorrect because dynamic data masking obfuscates data but does not restrict access. Option D is incorrect because Azure Purview is a data governance service, not for column-level security.

Answer

Row-level security (RLS)

Answer

Azure Purview data policies

Answer

Dynamic data masking (DDM)

Question 2

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?

Accepted Answer

Set the activity's retry property to 3 and enable exponential backoff.. Option C is correct because the retry policy with exponential backoff is configured in the activity's retry property. Option A is wrong because pipeline-level retry is a simple retry, not exponential backoff. Option B is wrong because the retry property at pipeline level is not for activities. Option D is wrong because the activity-level retry secs property is for fixed interval, not exponential backoff.

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 3

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. Option B is correct because sys.dm_pdw_exec_requests provides query execution details including memory and duration. Option A is wrong because sys.dm_pdw_nodes is for node-level info. Option C is wrong because sys.dm_pdw_resource_waits shows waits, not memory usage. Option D is wrong because sys.dm_pdw_waits shows wait types, not memory consumption.

Answer

sys.dm_pdw_resource_waits

Answer

sys.dm_pdw_nodes

Answer

sys.dm_pdw_waits

Question 4

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. Option A is correct because Azure Storage Encryption supports customer-managed keys via Key Vault. Option B is incorrect because Azure Information Protection is for classification and labeling. Option C is incorrect because Azure Disk Encryption is for VMs, not Blob Storage. Option D is incorrect because Azure Purview is a data governance service.

Answer

Azure Purview

Answer

Azure Disk Encryption

Answer

Azure Information Protection

Question 5

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.. Option C is correct because a partitioned table on the date column allows partition elimination. Option A is wrong because a clustered columnstore index is good for scans but partition elimination is more effective for filtering. Option B is wrong because round-robin distribution does not help with filtering. Option D is wrong because a nonclustered index on date may help but partition elimination is more effective for large scans.

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 6

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. Option B is correct because Azure Monitor collects and analyzes pipeline run logs and metrics. Option A is wrong because Azure Purview is for data governance. Option C is wrong because Azure Sentinel is a SIEM. Option D is wrong because Azure Log Analytics is part of Azure Monitor but the question asks for the service that collects and analyzes logs.

Answer

Azure Purview

Answer

Azure Log Analytics

Answer

Azure Sentinel

Question 7

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.. Option A is correct because Azure Data Lake Storage Gen2 supports soft delete for blobs. Option B is wrong because Azure Backup is for VM and other sources, not for Data Lake. Option C is wrong because versioning is different from soft delete. Option D is wrong because Azure Resource Manager locks prevent resource deletion, not data deletion.

Answer

Apply an Azure Resource Manager lock.

Answer

Configure Azure Backup for the storage account.

Answer

Enable blob versioning.

Question 8

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.. Option C is correct because 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 9

You are responsible for securing an Azure Synapse Analytics workspace. You need to ensure that only authorized users can query the serverless SQL pool. Which authentication method should you use?

Accepted Answer

Microsoft Entra ID authentication. Option B is correct because Microsoft Entra ID authentication is recommended for serverless SQL pool. Option A is wrong because SQL authentication is less secure and not recommended. Option C is wrong because managed identity is for service-to-service, not user queries. Option D is wrong because Azure Key Vault stores secrets, not authentication.

Answer

Managed identity authentication

Answer

SQL authentication

Answer

Azure Key Vault authentication

Question 10

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.. Options A and E are correct. A improves query performance by distributing data for parallelism. E reduces data movement by colocating joins on the same distribution. B is wrong because round-robin distributes data evenly but does not reduce data movement. C is wrong because heaps are not optimal for data warehousing. D is wrong because increasing DWU may help but is not always the best optimization.

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 11

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, B, and D are correct. A ensures data is encrypted at rest using AES-256. B provides data integrity for data in transit. D provides network-level security. C is wrong because Azure Information Protection is for classification, not a built-in storage security feature. E is wrong because Azure Policy is for governance, not data protection.

Answer

Azure Information Protection

Answer

Azure Policy

Question 12

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. Options A and C are correct. A indicates concurrency bottlenecks. C indicates data movement bottlenecks. B is wrong because it shows resource usage, not bottlenecks. D is wrong because it shows storage usage. E is wrong because it shows DWU usage, not specific bottlenecks.

Answer

CPU percentage

Answer

Storage used

Answer

DWU used

Question 13

Your organization uses Azure Synapse Analytics serverless SQL pools to query data in Azure Data Lake Storage Gen2. You need to ensure that only users with specific Microsoft Entra ID roles can query the data. What should you configure?

Accepted Answer

Assign the Storage Blob Data Contributor role to the users on the Azure Data Lake Storage Gen2 account.. Option A is correct because Azure Synapse Analytics serverless SQL pools rely on Microsoft Entra ID (formerly Azure AD) tokens and RBAC roles to authorize access to data in Azure Data Lake Storage Gen2. The Storage Blob Data Contributor role grants read and write access to data. Option B is wrong because SAS tokens are shared secrets, not tied to user identity. Option C is wrong because managed identity is not suitable for per-user authorization. Option D is wrong because firewall rules control network access, not user-level authorization.

Answer

Generate a shared access signature (SAS) token for the storage account and include it in the external table definition.

Answer

Configure an IP firewall rule on the storage account to allow only the SQL pool's outbound IP addresses.

Answer

Create a managed identity for the SQL pool and grant it access to the storage account.

Question 14

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.. Option C is correct because 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 15

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