DP-203 Secure, monitor, and optimize data storage and data processing — Timed 100 Questions

Question 1

You are designing a data processing solution in Azure Databricks that uses Unity Catalog. The security team requires that all users authenticate using Microsoft Entra ID and that access to tables is governed by attribute-based access control (ABAC) using table tags. Which feature should you enable?

100 minute time limit — choose an answer to begin.

Accepted Answer

Table tags with access control lists (ACLs) in Unity Catalog.. In Unity Catalog, attribute-based access control (ABAC) is implemented through table tags combined with access control lists (ACLs). Table tags allow data to be classified, and ACLs can then be configured based on those tags to enforce attribute-based access. This directly satisfies the requirement of using table tags for ABAC. Option B (dynamic views with user context functions) can provide similar controls but does not inherently leverage table tags and is not the dedicated ABAC feature in Unity Catalog.

Answer

Column-level security masks.

Answer

Dynamic views with user context functions.

Answer

Row-level security filters.

Question 2

Your team uses Azure Data Factory to copy data from on-premises SQL Server to Azure Blob Storage. You need to ensure that data in transit is encrypted using TLS 1.2. What should you configure?

Accepted Answer

Ensure the Azure Data Factory pipeline uses HTTPS for the copy activity.. Azure Data Factory uses HTTPS by default, which enforces TLS encryption for data in transit. Option B is wrong because VPN is not required for TLS encryption. Option C is wrong because configuring the on-premises SQL Server to use SSL certificates is necessary but not sufficient; Azure Data Factory must also use HTTPS. Option D is wrong because Azure ExpressRoute provides a private connection but does not replace TLS.

Answer

Set up a site-to-site VPN between the on-premises network and Azure.

Answer

Configure the on-premises SQL Server to use SSL certificates.

Answer

Use Azure ExpressRoute with private peering.

Question 3

Refer to the exhibit. You are creating an Azure Storage account using an ARM template with the above snippet. After deployment, a security auditor reviews the configuration and notes that the storage account is not using a customer-managed key for encryption. What is the most likely reason?

Accepted Answer

The 'keyVersion' is missing a specific version, so Azure Storage defaults to Microsoft-managed key.. For Azure Storage customer-managed keys (CMK), the encryption key is stored in Azure Key Vault, and the storage account references it via the encryption keySource property set to 'Microsoft.Keyvault'. The keyVersion property is optional: if omitted, Azure Storage automatically uses the latest version of the key. However, if the keyVersion is specified but empty or malformed (e.g., missing the actual version GUID), the configuration may be invalid, causing the deployment to fall back to Microsoft-managed keys or fail. In the exhibit, the keyVersion is likely missing a specific version, so Azure Storage defaults to Microsoft-managed key.

Answer

The 'keySource' should be 'Microsoft.Storage' for customer-managed key.

Answer

The storage account requires double encryption to use customer-managed key.

Answer

The 'infrastructureEncryption' setting is enabled, which overrides customer-managed key.

Question 4

You are monitoring an Azure Synapse Analytics dedicated SQL pool and notice that some queries are taking longer than expected. You need to identify queries that are experiencing significant memory pressure. Which dynamic management view (DMV) should you query?

Accepted Answer

sys.dm_pdw_exec_requests. sys.dm_pdw_exec_requests is the DMV that provides details about all active and recent requests in a dedicated SQL pool, including memory usage metrics such as granted memory and used memory. By querying this DMV, you can identify queries experiencing significant memory pressure by comparing granted memory to actual memory used and looking for high values or wait types related to memory.

Answer

sys.dm_pdw_wait_stats

Answer

sys.dm_pdw_query_stats_xe

Answer

sys.dm_pdw_nodes_os_performance_counters

Question 5

You have an Azure Stream Analytics job that writes output to Azure Synapse Analytics. You need to ensure that the job can authenticate to Synapse Analytics using a managed identity. What should you do?

Accepted Answer

Enable system-assigned managed identity on the Stream Analytics job and configure the output to use it.. To authenticate to Azure Synapse Analytics using a managed identity, you should enable the system-assigned managed identity on the Stream Analytics job (it is enabled by default) and then configure the output to use managed identity authentication. Option A is correct. Option B is incorrect because a SAS token is not a managed identity. Option C is incorrect because while a user-assigned managed identity could be used, the system-assigned managed identity is sufficient and is the simpler option. Option D is incorrect because SQL Server authentication does not use managed identity and is less secure than managed identity authentication.

Answer

Generate a shared access signature (SAS) token for the Synapse Analytics workspace.

Answer

Create a user-assigned managed identity and assign it to the Stream Analytics job.

Answer

Configure the output to use SQL Server authentication with a username and password.

Question 6

Your team uses Azure Data Factory to orchestrate data movement. You need to monitor pipeline runs and set up alerts when a pipeline fails more than three times in an hour. What is the most efficient approach?

Accepted Answer

Create an Azure Monitor metric alert for the 'Failed pipeline runs' metric with a threshold of 3 in 1 hour.. Azure Monitor metric alerts natively support the 'Failed pipeline runs' metric emitted by Azure Data Factory, and the alert rule condition can be configured with a threshold of 3 within a 1-hour aggregation window. This is the most efficient approach because it uses the built-in platform metric with no additional log ingestion, query authoring, or external orchestration. The alert fires automatically when the count exceeds three failures in the rolling hour.

Answer

Create an alert rule in Azure Data Factory based on the 'Failed pipeline runs' metric.

Answer

Configure diagnostic settings to send logs to Log Analytics and create a log alert.

Answer

Use a Logic App to periodically check the pipeline run status and send notifications.

Question 7

You are optimizing the performance of an Azure Synapse Analytics dedicated SQL pool. Which TWO actions can help reduce data movement during query execution?

Accepted Answer

Use replicated tables for small dimension tables.. Option B is correct because replicated tables place a full copy of a small dimension table on every compute node, so joins between a large distributed fact table and that dimension require no data movement across nodes (the dimension is already local). Option E is correct because hash-distributing a large fact table on its join key columns colocates rows with matching join values on the same distribution, eliminating the shuffle (data movement) needed when joining to another table distributed on the same key. Option A is wrong because hashing on a column not used in joins provides no join colocation and can actually increase data movement during joins. Option C is wrong because round-robin distribution spreads rows evenly without regard to join keys, so joins typically require shuffling/moving data to align matching rows. Option D is wrong because increasing the resource class for the loading user only allocates more memory/CPU to that user's load operations and does not affect query-time data movement between distributions.

Answer

Use hash distribution on a column that is not used in joins.

Answer

Use round-robin distribution for large fact tables.

Answer

Increase the resource class for the loading user.

Question 8

You need to secure access to an Azure Data Lake Storage Gen2 account. Which THREE methods can you use to authenticate and authorize access?

Accepted Answer

Shared access signatures (SAS).. Option B (Shared access signatures, SAS) is correct because ADLS Gen2 exposes the Blob service REST API, and a SAS token grants time-limited, scoped delegated access (service, account, or user delegation SAS) to containers or blobs without sharing the account key. Option D (Access control lists, ACLs) is correct because ADLS Gen2 supports a hierarchical namespace where POSIX-style ACLs on directories and files provide fine-grained read/write/execute permissions for Microsoft Entra ID principals, in addition to RBAC. Option E (Azure RBAC roles) is correct because built-in roles such as Storage Blob Data Owner, Contributor, and Reader can be assigned at the subscription, resource group, storage account, container, or file-system scope to authorize data-plane access. Option A (SQL connection strings) is not applicable because connection strings are used by SQL clients to reach Azure SQL Database or similar relational services, not to authenticate against ADLS Gen2's Blob/DFS endpoints.

Answer

SQL connection strings.

Answer

Managed identities.

Question 9

You are monitoring the performance of an Azure Data Factory pipeline that uses a Copy activity to load data into Azure Synapse Analytics. Which THREE metrics should you monitor to identify potential performance bottlenecks?

Accepted Answer

Throughput (data read/written per second).. Option A (Throughput, data read/written per second) is correct because it directly quantifies how fast the Copy activity moves data, and a low or fluctuating throughput reveals the actual bottleneck in the source-to-sink transfer. Option D (Copy activity duration) is correct because it isolates the time spent in the copy operation itself, letting you distinguish copy slowness from other pipeline activities. Option E (Data read and written metrics) is correct because these counters show the volume and rate of bytes read from the source and written to Azure Synapse Analytics, exposing throttling, backpressure, or sink-side limits. Option B (Integration runtime CPU utilization) is not among the marked answers, and option C (Pipeline run duration) is also unmarked, so neither should be selected as a required bottleneck metric for this scenario.

Answer

Integration runtime CPU utilization.

Answer

Pipeline run duration.

Question 10

Refer to the exhibit. You are configuring an Azure Purview data policy for Azure Storage. The policy above is intended to audit all access events. However, the security team complains that not all read events are being audited. What is the most likely reason?

Accepted Answer

The storage account is not enabled for Purview policy enforcement.. The security team reports that not all read events are being audited. The policy uses 'ALL_ACTIONS' which should include read events, so the issue is not with the action group or predicate. The most likely reason is that the storage account has not been enabled for Purview policy enforcement. Azure Purview requires the 'AllowPurviewPolicyEnforcement' property to be enabled on the storage account for policies to take effect. Without this, no auditing events are captured, even if the policy is correctly defined.

Answer

The filter predicate is set to 'true', which only captures a subset of events.

Answer

The action group 'ALL_ACTIONS' does not include read events.

Answer

The policy excludes the 'Read' action by default.

Question 11

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.. Setting the activity's retry property to 3 and enabling exponential backoff is correct because retry policies in Azure Data Factory are configured at the activity level, not the pipeline or trigger level. Exponential backoff increases the wait time between retries, which is ideal for transient network errors. This configuration directly addresses the requirement.

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 12

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 13

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.. In Azure Synapse Analytics dedicated SQL pool, partitioning a large fact table on a date column is the most effective way to improve query performance when queries filter on that date column. Partitioning enables partition elimination, so the query only scans the relevant partitions instead of the entire table. While a clustered columnstore index (B) is the default and provides compression and performance benefits, it does not by itself eliminate full table scans for date filters unless partitioning is also applied. A nonclustered index (A) is not ideal for large fact tables in Synapse, and changing distribution to round-robin (C) would likely worsen performance for joins and aggregations.

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 14

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 correct service to collect and analyze pipeline run logs and set up alerts for failures in Azure Data Factory. Azure Data Factory natively integrates with Azure Monitor, which captures metrics and diagnostic logs, and allows you to create alert rules based on pipeline run status, duration, and other metrics.

Answer

Azure Purview

Answer

Azure Log Analytics

Answer

Azure Sentinel

Question 15

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 16

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.. Option B is correct because replicating small dimension tables in a dedicated SQL pool places a full copy of each dimension on every compute node, eliminating data movement (shuffle) during joins with large fact tables and thereby improving query performance. Option E is correct because hash distribution on a column frequently used in joins and aggregations co-locates related rows on the same distribution, minimizing costly data movement across nodes for those operations. Option A is not the intended optimization here since scaling to a higher DWU increases cost and capacity rather than being a targeted performance-tuning action for data distribution and movement. Option C is incorrect because heap indexes on fact tables are generally used for staging or small tables; large fact tables typically benefit from clustered columnstore indexes. Option D is incorrect because round-robin distribution for large fact tables causes data movement during joins, whereas hash distribution is preferred for large fact tables.

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 17

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. A rising count of queued or waiting requests in a dedicated SQL pool directly signals concurrency limits or insufficient resources, indicating a performance bottleneck where queries wait for slots or DWUs. Option D (Data movement (shuffle) metrics) is correct because dedicated SQL pools are MPP systems, and heavy shuffle/data movement during query execution often reveals poorly distributed tables or skewed joins that degrade query performance. Option A (CPU percentage) is a general node-level metric but is not one of the dedicated SQL pool-specific bottleneck indicators emphasized for this scenario. Option C (Storage used) reflects capacity consumption rather than query performance bottlenecks. Option E (DWU used) shows service-level utilization/scale, but it is not the specific diagnostic metric for identifying query-level bottlenecks like queuing and data movement.

Answer

CPU percentage

Answer

Storage used

Answer

DWU used

Question 18

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 19

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.. Option C is correct because Transparent Data Encryption (TDE) is a built-in Azure Synapse dedicated SQL pool security feature that encrypts data at rest, protecting the underlying storage files and backups from unauthorized access; it is enabled by default but should be explicitly verified and managed as a best practice. Option E is correct because column-level security (CLS) in dedicated SQL pool allows you to control access to specific columns containing sensitive data based on the user's identity or role, using GRANT and DENY statements, which directly restricts who can read sensitive columns. Option A is not a data protection best practice for sensitive data inside the pool; firewall rules control network access, not data-level security, and are already part of the platform's network perimeter. Option B is unnecessary because Azure Storage encryption is enabled by default for all Azure Storage accounts and TDE already encrypts the dedicated SQL pool's data at rest. Option D is incorrect because Dynamic Data Masking does not obfuscate data from all users; it only masks data for non-privileged users, while users with UNMASK permission see the actual data, so it does not fully protect sensitive data from all users.

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 20

Your Azure Synapse Analytics dedicated SQL pool query performance is degrading over time. You suspect that the statistics might be outdated. What is the most efficient way to update statistics for all tables in the pool?

Accepted Answer

Execute `sp_updatestats` in the user database.. `sp_updatestats` updates statistics for all tables in the current user database efficiently. Option A is wrong: while automatic statistics update is enabled by default, it may not immediately address performance degradation if the statistics are outdated; waiting is not proactive. Option B is wrong: `sp_updatestats` in the master database updates only system tables, not user tables. Option D is wrong: running `UPDATE STATISTICS` on each table individually is less efficient than using `sp_updatestats` for all tables.

Answer

Enable automatic statistics update and wait for the system to update them.

Answer

Execute `sp_updatestats` in the master database.

Answer

Run `UPDATE STATISTICS` on each table individually.