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

Question 1

You are monitoring an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Data Lake Storage Gen2. The pipeline runs daily and you need to be alerted if the copy activity takes longer than 30 minutes. What should you do?

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

Accepted Answer

Use Azure Monitor alerts on the ADFCopyActivityDuration metric for the copy activity, setting the threshold to 30 minutes.. Azure Monitor provides metrics for Azure Data Factory, including ADFCopyActivityDuration, which measures the time taken by copy activities. By creating an alert rule on this metric with a threshold of 30 minutes, you can receive notifications when the copy activity exceeds the desired duration. This is the most direct and native way to monitor activity-level performance.

Answer

Add a validation activity after the copy activity that checks the elapsed time and sends an email if it exceeds 30 minutes.

Answer

Create an alert in Azure Monitor that triggers when the pipeline's duration metric exceeds 30 minutes.

Answer

Configure a tumbling window trigger with a maximum concurrency of 1 and set a timeout of 30 minutes.

Question 2

You manage an Azure Synapse Analytics workspace with a dedicated SQL pool that contains a large fact table. Users report that a nightly ELT process inserting millions of rows via a Copy activity into a staging heap table is slow and consumes excessive transaction log space. You need to improve insert performance and reduce log pressure. What should you do?

Accepted Answer

Create the staging table as a heap and use the COPY statement with TABLOCK hint.. For bulk loading into a dedicated SQL pool staging table, minimal logging is achieved by inserting into a heap with the TABLOCK hint. This reduces transaction log growth and improves performance. Other options like result set caching or columnstore indexes do not address the log pressure or insert speed for large ELT operations, and distribution changes alone are insufficient.

Answer

Use the COPY statement with a columnstore index on the staging table.

Answer

Enable Result Set Caching on the staging table.

Answer

Change the staging table distribution to ROUND_ROBIN.

Question 3

You manage an Azure Synapse Analytics workspace that includes a dedicated SQL pool. You need to implement dynamic data masking on a column containing credit card numbers. The masking rule should allow users in the 'Finance' role to see the full credit card number, while all other users see only the last four digits. What should you do?

Accepted Answer

Create a mask using the default function on the column and grant UNMASK permission to the Finance role.. Dynamic data masking with the default function masks the column for all users except those with UNMASK permission. Granting UNMASK to the Finance role allows its members to see full data while others see only the last four digits. This approach is secure, requires no application changes, and aligns with the principle of least privilege.

Answer

Create a custom mask using the partial function and assign the Finance role to a database user with db_owner permissions.

Answer

Implement column-level encryption using Always Encrypted and provide the Finance role with the column master key.

Answer

Create a view that selects the credit card column and applies a CASE statement to mask the data for non-Finance users, then grant SELECT on the view to all users.

Question 4

You are a data engineer for a financial services company. You need to secure sensitive data in an Azure Synapse Analytics dedicated SQL pool that contains customer PII. You must implement column-level encryption for specific columns and ensure that only authorized users can decrypt the data. Which two actions should you perform? (Choose two.)

Accepted Answer

Use Column-Level Encryption (CLE) with symmetric keys and certificates.. Column-Level Encryption with symmetric keys and certificates is the supported method for encrypting specific columns in Azure Synapse Analytics dedicated SQL pools. To ensure only authorized users can decrypt, you must grant them permissions on the symmetric key. Together, these actions provide column-level encryption and controlled decryption, meeting the security requirements.

Answer

Use Always Encrypted with secure enclaves to encrypt the sensitive columns.

Answer

Configure Transparent Data Encryption (TDE) on the dedicated SQL pool.

Answer

Implement Dynamic Data Masking (DDM) on the sensitive columns.

Question 5

You are a data engineer for a healthcare company that stores patient data in Azure Data Lake Storage Gen2. You need to ensure that data is encrypted at rest and that access is controlled based on Azure AD identities. You also need to monitor and audit access to the data. Which two actions should you take? (Choose two.)

Accepted Answer

Enable Azure Storage Service Encryption with Microsoft-managed keys.. Azure Storage Service Encryption is enabled by default and encrypts data at rest, satisfying the encryption requirement. Azure Defender for Storage combined with diagnostic logs to Log Analytics provides monitoring and auditing of access, and works with Microsoft Entra ID identities for access control. The other options either misapply authentication methods (Microsoft Entra Domain Services), use less secure SAS tokens, or address data protection without meeting the encryption and auditing needs.

Answer

Configure Azure AD DS authentication for the storage account and assign POSIX ACLs.

Answer

Enable soft delete for blobs and containers to protect against accidental deletion.

Answer

Create a shared access signature (SAS) with a long expiry and distribute it to all users.

Question 6

You are a data engineer for a financial services company. You store sensitive data in Azure Data Lake Storage Gen2. You need to implement a solution that allows you to monitor data access and detect anomalous access patterns, such as unusual file reads or changes in permissions. You also need to retain the logs for 90 days for compliance. Which two actions should you take? (Choose two.)

Accepted Answer

Configure Azure Defender for Storage to monitor for anomalous access and suspicious activity.. To monitor data access and detect anomalies in Azure Data Lake Storage Gen2, you should enable Azure Storage diagnostic logging and send logs to a Log Analytics workspace, which captures data-plane operations and allows for analysis and retention. Additionally, Microsoft Defender for Storage provides threat detection and alerts on suspicious activities, complementing the diagnostic logs. Together, they satisfy both monitoring and anomaly detection requirements.

Answer

Create an Azure Event Hubs namespace and configure the storage account to send diagnostic logs to it.

Answer

Enable Azure Monitor activity log and filter for storage account write operations.

Answer

Enable Azure Data Lake Storage Gen2 hierarchical namespace and configure POSIX ACLs on all directories.

Question 7

You are a data engineer for a healthcare analytics company. You have an Azure Synapse Analytics workspace with a dedicated SQL pool that contains a large fact table named dbo.FactClaims. The table is partitioned by ClaimDate and uses clustered columnstore index. You notice that queries filtering on ClaimDate and MemberID are performing poorly. You need to improve query performance while minimizing storage overhead. What should you do?

Accepted Answer

Create a materialized view that pre-aggregates data by ClaimDate and MemberID, and create a unique clustered index on the view.. A materialized view in Azure Synapse Analytics dedicated SQL pool stores pre-aggregated data and can significantly improve query performance for repetitive aggregations and filters. By creating a materialized view grouped by ClaimDate and MemberID, the optimizer can automatically rewrite queries to use the smaller, precomputed result set, reducing I/O and CPU. The unique clustered index ensures the view is maintained efficiently.

Answer

Create a nonclustered index on MemberID and include ClaimDate as an included column.

Answer

Create a secondary columnstore index on MemberID and ClaimDate.

Answer

Rebuild the clustered columnstore index and update statistics on the MemberID and ClaimDate columns.

Question 8

You are a data engineer at a financial services company using Azure Synapse Analytics. The dedicated SQL pool contains a table named dbo.Transactions with 2 billion rows, partitioned by TransactionDate. A nightly ETL process loads incremental data. Users report that point-lookups on TransactionID (a unique identifier) are slow. You need to improve query performance for these lookups while minimizing data movement during loads. What should you do?

Accepted Answer

Change the distribution to HASH(TransactionID).. Hashing on TransactionID ensures that rows with the same TransactionID reside on the same distribution, enabling efficient point lookups and reducing data movement during incremental loads that join on that key. The table remains partitioned by TransactionDate, which supports load performance. Other options either do not improve point lookups or introduce unnecessary overhead.

Answer

Add a nonclustered index on TransactionID.

Answer

Create a clustered columnstore index on the table.

Answer

Switch the table to a round-robin distribution.

Question 9

You are designing a monitoring solution for an Azure Synapse Analytics dedicated SQL pool. You need to identify queries that are experiencing performance issues due to excessive data movement. Which two actions should you perform? (Choose two.)

Accepted Answer

Query the sys.dm_pdw_request_steps DMV to examine the steps of long-running queries.. To identify queries with excessive data movement in a dedicated SQL pool, you should use the sys.dm_pdw_request_steps DMV to inspect the steps of long-running queries and the sys.dm_pdw_exec_requests DMV to find queries with high elapsed time. These DMVs provide the necessary details to pinpoint data movement operations and the queries responsible for them.

Answer

Monitor the DWU usage percentage in the Azure portal to detect data movement.

Answer

Enable Query Store on the dedicated SQL pool and review the top resource-consuming queries.

Answer

Create an Extended Events session to capture query execution plans.

Question 10

You are monitoring an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Data Lake Storage Gen2. The pipeline runs daily and you need to be alerted if the copy activity takes longer than 30 minutes. What should you do?

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

Accepted Answer

Increase the degree of copy parallelism in the copy activity.. Increasing the degree of copy parallelism enables concurrent reads and writes, which can dramatically improve copy throughput for large tables. This directly addresses the performance issue causing timeouts. Other options either do not improve performance or are irrelevant to the copy operation's speed.

Answer

Use a tumbling window trigger instead of a schedule trigger.

Answer

Set the copy activity timeout to a higher value.

Answer

Enable staging in the copy activity by using an intermediate Azure Blob Storage account.

Question 11

You manage an Azure Synapse Analytics workspace with a dedicated SQL pool. A nightly ETL job loads data into a staging table using PolyBase, then runs transformations. You notice that the job sometimes fails with '110802;An internal DMS error occurred' and occasionally runs much slower than expected. You need to reduce these errors and improve load performance without changing the data model. What should you do?

Accepted Answer

Split the large load into multiple smaller batches and use a staging table with heap distribution.. PolyBase loads into dedicated SQL pools can fail with internal DMS errors when loading large volumes into a staging table that has a clustered columnstore index or when the load exceeds available memory. Splitting the load into smaller batches reduces memory pressure, and using a heap for the staging table eliminates index maintenance overhead and improves load speed. This approach is a documented best practice for staging tables in Synapse dedicated SQL pools.

Answer

Increase the DWU setting of the dedicated SQL pool before the load.

Answer

Enable result set caching on the dedicated SQL pool.

Answer

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

Question 12

You manage an Azure Synapse Analytics workspace with a dedicated SQL pool that contains a table named dbo.FactSales. The table is distributed using hash distribution on SalesKey. A nightly ETL process performs a large INSERT INTO dbo.FactSales SELECT ... FROM staging. You notice that the nightly load is slow and data is skewed across distributions. You need to improve load performance and reduce data movement. What should you do?

Accepted Answer

Rebuild the table as a clustered columnstore index with a different distribution column that has high cardinality and even distribution.. The root cause of slow loads and skew is an inappropriate hash distribution column. Choosing a high-cardinality, evenly distributed column improves parallel load performance and reduces data movement during joins. Rebuilding the table as a clustered columnstore index further optimizes analytical query performance. The other options either do not address skew or introduce different performance issues.

Answer

Add a secondary index on the distribution column to improve insert performance.

Answer

Change the table to a heap and create a nonclustered index on SalesKey.

Answer

Partition the table by date and switch to round-robin distribution.

Question 13

You are a data engineer at a retail company. You have an Azure Synapse Analytics dedicated SQL pool that contains a large fact table. Queries that filter on the column 'TransactionDate' are performing poorly. You need to improve query performance for these filters. What should you do?

Accepted Answer

Create a nonclustered index on the TransactionDate column.. Creating a nonclustered index on the TransactionDate column allows the dedicated SQL pool to efficiently seek to the relevant rows, reducing the data scanned for queries that filter on that column. While columnstore indexes and partitioning can also improve performance, a nonclustered index is the most direct and effective solution for highly selective filters on a single column. Result set caching does not improve query execution when the cache is missed.

Answer

Enable result set caching on the dedicated SQL pool.

Answer

Create a clustered columnstore index on the fact table.

Answer

Partition the fact table by TransactionDate using RANGE RIGHT FOR VALUES.

Question 14

You have an Azure Data Lake Storage Gen2 account that contains a large volume of CSV files. You need to implement a solution that automatically enforces retention policies by deleting files older than 90 days, while ensuring that files under a legal hold are not deleted. The solution must minimize development effort. What should you use?

Accepted Answer

Azure Storage lifecycle management policies with blob index tags to exclude files under legal hold.. Azure Storage lifecycle management policies provide a no-code way to automatically delete blobs after a specified period. By using blob index tags to identify files under legal hold, you can configure a lifecycle rule with a tag-based filter to exclude those blobs from deletion. This meets the retention requirement while respecting legal holds, and it minimizes development effort because it is a native, declarative feature of Azure Storage.

Answer

Azure Data Factory with a custom .NET activity that checks file age and legal hold status.

Answer

Azure Logic Apps with a recurrence trigger that lists files and deletes old ones not under legal hold.

Answer

Azure Functions with a timer trigger that enumerates files and deletes those older than 90 days.

Question 15

You manage an Azure Synapse Analytics workspace with a dedicated SQL pool that contains a large fact table. Queries that filter on a date column and join to a dimension table are running slowly. You need to improve query performance by reducing data movement during joins. The date column is used in most queries. What should you do?

Accepted Answer

Change the distribution of the fact table to hash-distributed on the join key used with the dimension table.. In a dedicated SQL pool, data movement during joins is a primary performance bottleneck. Distributing a large fact table on the join key ensures that rows with the same key value reside on the same distribution, so joins can be performed locally without shuffling data across nodes. This minimizes network overhead and speeds up queries.

Answer

Create a nonclustered index on the date column and update statistics.

Answer

Create a clustered columnstore index on the date column.

Answer

Change the distribution of the fact table to hash-distributed on the date column.