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

Question 1

You manage an Azure Data Lake Storage Gen2 account that stores sensitive financial data. The data must be encrypted at rest, and access must be audited. You need to ensure that encryption keys are managed by your organization and that all access attempts are logged. Which TWO actions should you take? (Choose two.)

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

Accepted Answer

Enable diagnostic logging for the storage account and send logs to Azure Monitor.. To meet the requirements, you need customer-managed keys for organizational control over encryption keys, and diagnostic logging to audit all access attempts. Customer-managed keys are configured in Azure Key Vault and used by the storage account for encryption at rest. Diagnostic logs capture detailed access information and can be routed to Azure Monitor for analysis and retention.

Answer

Enable Azure Storage Service Encryption with Microsoft-managed keys.

Answer

Use Azure Private Link to restrict access to the storage account.

Answer

Enable Azure Defender for Storage.

Question 2

You manage an Azure Data Lake Storage Gen2 account that stores sensitive financial data. The data must be encrypted at rest, and access must be audited. You need to ensure that encryption keys are managed by your organization and that all access attempts are logged. Which TWO actions should you take? (Choose two.)

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

Accepted Answer

Partition the data by date into separate folders and update queries to filter on the date path.. Partitioning the dataset by date and aligning queries with the partition path allows the query engine to prune unnecessary folders, dramatically reducing the bytes scanned. This approach keeps the original JSON format and location, satisfying the constraints. It directly addresses the root cause: lack of data organization that forces full scans.

Answer

Enable hierarchical namespace on the storage account.

Answer

Convert the JSON files to Parquet format.

Answer

Increase the throughput of the storage account.

Question 3

You are optimizing an Azure Synapse Analytics dedicated SQL pool that contains a large fact table with over 1 billion rows. Queries frequently join this fact table with smaller dimension tables on a distribution key. You notice that many queries perform poorly due to data movement. You need to reduce data movement and improve query performance. Which two actions should you take? (Choose two.)

Accepted Answer

Use hash distribution on the fact table's join column.. Hash distributing the fact table on the join column and replicating the dimension tables co-locate related data, minimizing data movement during joins. This is a standard optimization in Azure Synapse Analytics dedicated SQL pools. Other options do not address the root cause of data movement.

Answer

Partition the fact table by date.

Answer

Use round-robin distribution for the fact table.

Answer

Create a clustered columnstore index on the fact table.

Question 4

You are configuring security for an Azure Data Lake Storage Gen2 account that stores sensitive data. You need to ensure that all data access is logged and that you can audit who accessed the data and when. You also need to retain the logs for 90 days. What should you do?

Accepted Answer

Configure diagnostic settings to send logs to a Log Analytics workspace.. Diagnostic settings in Azure allow you to export resource logs to a Log Analytics workspace, where you can analyze and retain them for up to 90 days (or longer with custom retention). This provides the necessary auditing capability for Data Lake Storage Gen2 access.

Answer

Enable soft delete for blobs.

Answer

Use Azure Storage Analytics logging to a storage account.

Answer

Enable Azure Defender for Storage.

Question 5

You have an Azure Data Lake Storage Gen2 account that contains a container named raw. The container has a folder hierarchy with millions of small files. You need to optimize read performance for an Azure Databricks job that reads these files. You also need to minimize storage costs. What should you do?

Accepted Answer

Compact the small files into larger files using a tool like Azure Data Factory or Databricks, and store them in a partitioned folder structure.. The presence of millions of small files creates significant overhead in listing, opening, and reading each file, which degrades performance and increases costs. Compacting these files into larger, partitioned files reduces the number of files and the amount of data scanned, improving read performance and lowering storage costs. The other options either do not address the root cause or increase costs.

Answer

Move the files to a premium storage tier to reduce latency.

Answer

Increase the number of partitions in the Databricks cluster to parallelize reads across more nodes.

Answer

Enable hierarchical namespace and use Azure Blob Storage APIs to read the files.

Question 6

You manage an Azure Synapse Analytics workspace. A dedicated SQL pool contains a table with a column named CustomerEmail that stores email addresses. You need to ensure that users who are not members of the DataPrivacy role see only a masked version of the email addresses when they query the table, while members of DataPrivacy see the actual values. The solution must minimize administrative effort. What should you do?

Accepted Answer

Create a dynamic data masking rule on the CustomerEmail column using the email() masking function and grant UNMASK to the DataPrivacy role.. Dynamic data masking is designed to limit sensitive data exposure by masking it to non-privileged users. Applying an email() mask to the CustomerEmail column ensures that users without the UNMASK permission see a masked format, while granting UNMASK to the DataPrivacy role allows those members to see the actual values. This approach requires only a one-time configuration and no changes to queries or applications.

Answer

Enable Always Encrypted with a column encryption key and configure the DataPrivacy role as the only role with access to the key.

Answer

Apply a column-level security policy using GRANT SELECT ON the CustomerEmail column to the DataPrivacy role.

Answer

Implement row-level security with a predicate function that filters rows based on the user's role.

Question 7

You are designing a security strategy for an Azure Data Lake Storage Gen2 account that stores sensitive data. You need to ensure that data is encrypted at rest using customer-managed keys. What should you configure?

Accepted Answer

Configure a customer-managed key in Azure Key Vault and associate it with the storage account.. Customer-managed keys for Azure Storage encryption require you to create and manage a key in Azure Key Vault and then configure the storage account to use that key. This gives you control over encryption at rest. Microsoft-managed keys are the default but do not meet the requirement. Infrastructure encryption adds a second layer but does not change key management. Azure Disk Encryption protects VM disks, not the data lake. Therefore, the correct approach is to use a customer-managed key from Key Vault.

Answer

Enable Azure Storage Service Encryption with Microsoft-managed keys.

Answer

Enable infrastructure encryption (double encryption) on the storage account.

Answer

Use Azure Disk Encryption on the virtual machines that access the data lake.

Question 8

You have an Azure Data Lake Storage Gen2 account that contains sensitive data. You need to implement a solution that enforces access control at the file and folder level, and also allows you to audit access. You want to minimize administrative effort. What should you do?

Accepted Answer

Enable hierarchical namespace and configure POSIX access control lists (ACLs) on files and folders, and enable Azure Storage logging to capture access.. To enforce access control at the file and folder level, you need to enable hierarchical namespace and use POSIX ACLs. This allows granular permissions on directories and files. To audit access, enable Azure Storage logging or integrate with Azure Monitor. This solution minimizes administrative effort because ACLs can be inherited and managed centrally. The other options either provide only coarse-grained access control or focus on network security rather than access auditing.

Answer

Use shared access signatures (SAS) with specific permissions and IP restrictions, and enable Azure Defender for Storage.

Answer

Configure Azure Private Endpoints and enable firewall rules to restrict access to the storage account.

Answer

Enable Azure role-based access control (RBAC) on the storage account and assign the Storage Blob Data Contributor role to users.

Question 9

You are monitoring an Azure Data Factory pipeline that copies data from an Azure SQL Database to an Azure Data Lake Storage Gen2 account. The pipeline runs hourly. You notice that the copy activity sometimes takes much longer than expected. You need to identify the cause of the performance variability. Which action should you take first?

Accepted Answer

Enable Azure Monitor diagnostic settings for the Data Factory and analyze the Copy activity duration metrics in Azure Monitor Logs.. To diagnose performance variability in an Azure Data Factory copy activity, you need detailed telemetry. Enabling diagnostic settings and analyzing metrics and logs in Azure Monitor provides insights into activity duration, data read/written, and errors. This data helps pinpoint whether the issue is due to source throttling, network latency, or other factors.

Answer

Configure a self-hosted integration runtime on a virtual machine with more memory and CPU.

Answer

Increase the Data Integration Units (DIUs) for the copy activity to the maximum allowed.

Answer

Enable Azure SQL Database Auditing and review the audit logs for long-running queries.

Question 10

You have an Azure Synapse Analytics dedicated SQL pool that contains a large fact table named FactSales. The table is partitioned by date and has a clustered columnstore index. You notice that queries filtering on a specific date range are slow. You need to improve query performance for these queries. What should you do?

Accepted Answer

Ensure that the queries use a predicate on the partitioning column so that partition elimination occurs.. Partition elimination is a key performance feature in dedicated SQL pools. When queries filter on the partitioning column with a sargable predicate, the engine can prune partitions and read only the relevant data. This reduces the amount of data scanned and speeds up queries. Ensuring that queries are written to take advantage of partition elimination is the most direct solution for slow date-range queries.

Answer

Rebuild the clustered columnstore index on the FactSales table.

Answer

Create a nonclustered index on the date column.

Answer

Switch the table distribution to round-robin.

Question 11

You are monitoring an Azure Synapse Analytics dedicated SQL pool. You notice that queries are occasionally queued due to concurrency limits. You need to reduce the impact of concurrency limits on query performance. What should you do?

Accepted Answer

Implement workload management with workload groups and classifier functions to allocate resources and control concurrency.. Workload management in Azure Synapse Analytics dedicated SQL pools enables you to create workload groups, define resource allocation, and set concurrency limits. By using classifier functions to route queries to appropriate groups, you can ensure that critical queries get resources and are not blocked by concurrency limits. This is the most effective way to manage concurrency and resource allocation.

Answer

Create a new dedicated SQL pool and move half of the tables to it.

Answer

Increase the number of data warehouse units (DWUs) for the dedicated SQL pool.

Answer

Change the distribution of the largest tables to round-robin.

Question 12

You are optimizing an Azure Synapse Analytics dedicated SQL pool. You need to reduce the amount of data read from storage during queries that filter on a date column. The fact table is partitioned by month on the date column. What should you do to improve query performance?

Accepted Answer

Ensure that queries include a predicate on the partitioning column to enable partition elimination.. Partition elimination is a technique where the query optimizer skips partitions that do not match the query's filter criteria. By ensuring queries filter on the partitioning column, you minimize the data scanned, reducing I/O and improving performance. This is especially effective for large fact tables partitioned by date.

Answer

Increase the resource class of the queries to allocate more memory.

Answer

Create a nonclustered index on the date column.

Answer

Change the distribution to round-robin to evenly spread data across distributions.

Question 13

You are designing a security strategy for an Azure Data Lake Storage Gen2 account that stores sensitive financial data. The data must be encrypted at rest using customer-managed keys stored in Azure Key Vault. You also need to ensure that only specific Azure services can access the storage account. What should you do?

Accepted Answer

Configure a customer-managed key for encryption and set the storage firewall to allow access from selected Azure services.. Customer-managed keys in Azure Key Vault enable encryption at rest with keys controlled by the organization. Configuring the storage firewall to allow access from selected Azure services ensures that only trusted services can reach the data. Together, these settings satisfy the encryption and access restriction requirements. Other options address network isolation or threat detection but not the specific encryption and service-level access controls needed.

Answer

Configure Azure Private Link and enable soft delete.

Answer

Enable infrastructure encryption and use shared access signatures (SAS) for access.

Answer

Enable Azure Defender for Storage and configure a private endpoint.

Question 14

You are monitoring an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline occasionally fails with a timeout error. You need to identify the cause of the failures and receive proactive alerts when similar issues occur. What should you do?

Accepted Answer

Enable diagnostic settings to send logs to Azure Monitor and create an alert rule on the pipeline failure metric.. Diagnostic settings in Azure Data Factory can route detailed logs and metrics to Azure Monitor, enabling analysis of pipeline failures. Creating an alert rule on failure metrics ensures you are notified proactively. This combination provides both root-cause investigation and ongoing monitoring. Other options either mask failures, improve performance without visibility, or offer generic recommendations.

Answer

Configure a tumbling window trigger with retry policy.

Answer

Use Azure Advisor recommendations for Data Factory.

Answer

Increase the DIU count for the copy activity.

Question 15

You are optimizing an Azure Synapse Analytics dedicated SQL pool that experiences performance degradation during peak hours. You need to reduce query execution time by improving data distribution and reducing data movement. Which two actions should you take? (Choose two.)

Accepted Answer

Convert small dimension tables to replicated tables.. Hash distributing large fact tables on a high-cardinality join column colocates matching rows, minimizing data movement during joins. Replicating small dimension tables eliminates the need to shuffle them across nodes. Together, these actions directly reduce data movement and improve query performance in a dedicated SQL pool. Other options either add resources without addressing distribution or introduce inappropriate indexing.

Answer

Implement table partitioning on a date column in the fact tables.

Answer

Create a clustered index on all columns of the fact tables.

Answer

Increase the number of distributions by scaling the dedicated SQL pool to a higher service level.