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

Question 1

You manage an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Data Lake Storage Gen2. The pipeline uses a self-hosted integration runtime. You need to ensure that data movement is encrypted in transit and that the integration runtime can authenticate to the on-premises SQL Server without storing credentials in the pipeline. What should you do?

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

Accepted Answer

Configure the self-hosted integration runtime to use Windows authentication and enable encrypted connection in the linked service.. To avoid storing credentials, the self-hosted integration runtime can use Windows authentication, leveraging the identity of the service account under which it runs. This is a common approach for on-premises SQL Server. Enabling encrypted connection in the linked service ensures that data transferred between the integration runtime and SQL Server is encrypted. Together, these satisfy the encryption in transit and credential-free authentication requirements.

Answer

Configure the integration runtime to use a service principal with a client secret stored in Azure Key Vault, and enforce SSL encryption on the SQL Server.

Answer

Use a managed identity for the self-hosted integration runtime to authenticate to the on-premises SQL Server, and enable Always Encrypted on the database.

Answer

Store the SQL Server credentials in Azure Key Vault and reference them in the linked service, and use TLS 1.2 for the connection.

Question 2

You are implementing a solution to monitor an Azure Data Lake Storage Gen2 account for suspicious access patterns. You need to detect and alert on anonymous access attempts to the storage account. What should you do?

Accepted Answer

Enable Azure Defender for Storage and configure alerts for anomalous access patterns.. Azure Defender for Storage provides built-in threat detection and alerts for anomalous access, including anonymous access attempts. It integrates with Microsoft Defender for Cloud and can send alerts via Azure Monitor. The other options either do not provide automated alerting or focus on data protection rather than security monitoring.

Answer

Enable soft delete for blobs and set a retention policy to recover from accidental deletion.

Answer

Configure diagnostic settings to send logs to Azure Monitor and create a log alert for the 'AnonymousSuccess' operation.

Answer

Use Azure Storage Analytics to log all access and periodically review the logs manually.

Question 3

You have an Azure Synapse Analytics workspace with a dedicated SQL pool that contains a large fact table. Queries against the table are slow because data is skewed across distributions. You need to identify the distribution key that is causing the skew and then improve query performance. What should you do first?

Accepted Answer

Run DBCC PDW_SHOWSPACEUSED on the table to view the number of rows per distribution.. Distribution skew occurs when rows are unevenly distributed across the 60 distributions in a dedicated SQL pool. To diagnose it, you need to see row counts per distribution. DBCC PDW_SHOWSPACEUSED is the simplest built-in command that returns this information. Once you identify the skewed distribution, you can consider options such as changing the distribution key, using a different distribution method, or rebuilding the table. Indexing or updating statistics do not address the physical skew.

Answer

Create a columnstore index on the distribution key column to redistribute the data evenly.

Answer

Query sys.dm_pdw_nodes_db_partition_stats to view row counts per partition and distribution.

Answer

Run UPDATE STATISTICS on the table to update the distribution statistics.

Question 4

You are a data engineer for a retail company using Azure Data Factory. A pipeline copies data from an on-premises SQL Server to Azure Blob Storage daily. The pipeline has been running successfully for months. Recently, the pipeline started failing intermittently with authentication errors. You need to ensure the pipeline can authenticate to the on-premises SQL Server reliably. What should you do?

Accepted Answer

Store the SQL Server credentials in Azure Key Vault and reference them in the linked service.. Intermittent authentication failures often stem from expired or improperly managed credentials. Storing credentials in Azure Key Vault and referencing them in the linked service ensures secure, centralized management and automatic retrieval of valid secrets. This approach also supports credential rotation without modifying pipelines, which is essential for reliable daily operations.

Answer

Enable staging in the copy activity.

Answer

Increase the timeout value in the copy activity settings.

Answer

Use a self-hosted integration runtime with a service account.

Question 5

You have an Azure Synapse Analytics workspace with a dedicated SQL pool that contains a table with a clustered columnstore index. Users report that queries filtering on a column with low cardinality are slow. You need to improve query performance for these filters without changing the table's distribution or index type. What should you do?

Accepted Answer

Reorder the data in the columnstore index by the low-cardinality column.. Reordering the columnstore index data by the low-cardinality filter column improves segment elimination, because rowgroup segments become more selective. This reduces the amount of data read during queries, directly improving performance. The distribution and index type remain unchanged, satisfying the requirement. Resource class changes or additional indexes do not provide the same targeted benefit.

Answer

Increase the resource class of the users running the queries.

Answer

Create a secondary nonclustered index on the low-cardinality column.

Answer

Create a materialized view on the filtered column.

Question 6

You are designing a monitoring solution for an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Data Lake Storage Gen2. You need to receive an alert when the pipeline fails or when the copy activity takes longer than 60 minutes. The solution must minimize cost and configuration effort. What should you do?

Accepted Answer

Enable Azure Monitor alerts on the Data Factory metrics for failed runs and activity duration.. Azure Data Factory emits metrics to Azure Monitor, including PipelineFailedRuns and ActivityDuration. You can create alert rules on these metrics to trigger notifications when a pipeline fails or when the copy activity duration exceeds 60 minutes. This is a native, low-cost, and minimal-configuration solution that meets the monitoring requirements without custom code or additional services.

Answer

Use Azure Logic Apps to poll the Data Factory activity logs and send an email when failures occur.

Answer

Configure diagnostic settings to send logs to a Log Analytics workspace and create log search alerts.

Answer

Create an Azure Event Grid subscription for Data Factory events and trigger an Azure Function to send alerts.

Question 7

You are a data engineer for a retail company that uses Azure Synapse Analytics dedicated SQL pool. You need to monitor query performance and identify queries that are consuming the most resources. You also need to receive alerts when query execution time exceeds a threshold. Which two actions should you take? (Choose two.)

Accepted Answer

Use dynamic management views (DMVs) such as sys.dm_pdw_exec_requests to query active and recent requests.. To monitor query performance and set alerts, you should enable Azure Monitor and send diagnostic logs to Log Analytics, which allows you to query logs and create alert rules. Additionally, querying DMVs like sys.dm_pdw_exec_requests provides detailed execution data. Together, these enable both proactive monitoring and alerting on query duration.

Answer

Configure SQL Server Profiler to trace queries and save traces to a file share.

Answer

Create an Azure Monitor alert rule based on the DTU percentage metric of the dedicated SQL pool.

Answer

Enable Query Store on the dedicated SQL pool to automatically capture query plans and runtime statistics.

Question 8

You have an Azure Synapse Analytics dedicated SQL pool. You need to monitor the pool to identify queries that are waiting on memory grants. Which dynamic management view (DMV) should you query?

Accepted Answer

sys.dm_pdw_waits. To identify queries waiting on memory grants in a dedicated SQL pool, query sys.dm_pdw_waits and filter for wait types related to memory grants. This DMV provides the wait type, the request ID, and other details. Joining with sys.dm_pdw_exec_requests gives you the query text. Other DMVs like sys.dm_pdw_exec_requests, sys.dm_pdw_nodes_exec_requests, or sys.dm_pdw_exec_sessions do not directly expose wait types, so they are not the primary source for this specific monitoring task.

Answer

sys.dm_pdw_exec_sessions

Answer

sys.dm_pdw_nodes_exec_requests

Answer

sys.dm_pdw_exec_requests

Question 9

You are optimizing an Azure Synapse Analytics dedicated SQL pool that contains a large fact table with 100 billion rows. The table is currently distributed using hash distribution on a column that has high cardinality but is frequently used in joins. Queries often 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

Create a clustered columnstore index on the fact table if one does not already exist.. To reduce data movement in a dedicated SQL pool, you should align the distribution of large tables with their join keys and use clustered columnstore indexes for efficient storage and retrieval. Hash-distributing the fact table and dimension tables on the join columns with matching data types enables collocated joins, eliminating shuffles. Clustered columnstore indexes improve compression and query performance for large fact tables. These two actions directly address the data movement and performance issues.

Answer

Partition the fact table by date to improve query performance.

Answer

Add a replicated table hint to all dimension tables in the queries.

Answer

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

Question 10

You are a data engineer for a logistics company. You have an Azure Data Lake Storage Gen2 account that contains sensitive shipment data. You need to ensure that only users in the 'DataAnalysts' Azure AD group can read the data, while users in the 'DataEngineers' group can read and write. You want to use Azure role-based access control (RBAC) and access control lists (ACLs). What should you do?

Accepted Answer

Assign the Storage Blob Data Reader role to the DataAnalysts group and the Storage Blob Data Contributor role to the DataEngineers group at the container scope.. Azure RBAC roles for storage data access are the correct approach. Storage Blob Data Reader allows read-only access to blob data, while Storage Blob Data Contributor allows read, write, and delete. Assigning these roles at the container scope restricts access to that container, meeting the requirements for both groups without using ACLs.

Answer

Assign the Storage Account Contributor role to both groups and use shared access signatures (SAS) to restrict access.

Answer

Assign the Reader role to the DataAnalysts group and the Contributor role to the DataEngineers group at the storage account scope.

Answer

Set POSIX ACLs on the container to grant read permissions to DataAnalysts and read/write permissions to DataEngineers.

Question 11

You are optimizing an Azure Synapse Analytics dedicated SQL pool that contains a large fact table distributed by hash on a date column. Queries frequently join this table to a smaller dimension table on a product key and filter by product category. You need to improve query performance. Which TWO actions should you take? (Choose two.)

Accepted Answer

Change the distribution column of the fact table to the product key.. The join between the fact and dimension tables on the product key is the performance bottleneck, so aligning the fact table's distribution column with that key allows local joins and avoids shuffling. Using a clustered columnstore index on the fact table further improves scan and aggregation efficiency for analytical queries. Together, these changes target the core join and filter pattern.

Answer

Create a nonclustered index on the product key column of the fact table.

Answer

Enable result set caching on the dedicated SQL pool.

Answer

Partition the fact table by the date column and rebuild statistics.

Question 12

Your organization uses Azure Synapse Analytics dedicated SQL pool. You need to ensure that all data at rest in the SQL pool is encrypted using a customer-managed key stored in Azure Key Vault. What should you configure?

Accepted Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault.. Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault encrypts the dedicated SQL pool's data at rest (data files, log files, backups) and allows the organization to control and rotate the encryption key. TDE is the native at-rest encryption mechanism for Azure Synapse dedicated SQL pools. Configuring it with a customer-managed key in Key Vault meets the requirement precisely.

Answer

Implement Always Encrypted with column encryption keys stored in Azure Key Vault.

Answer

Configure Dynamic Data Masking to obfuscate sensitive data.

Answer

Enable Azure Storage Service Encryption with a customer-managed key.

Question 13

You have an Azure Databricks workspace that uses a managed resource group. The security team requires that all cluster nodes use no public IP addresses and that all outbound traffic goes through a firewall. What should you configure?

Accepted Answer

Deploy the workspace in a VNet with forced tunneling enabled and a firewall.. To ensure no public IPs on cluster nodes and all outbound traffic routed through a firewall, you must deploy the Azure Databricks workspace into your own VNet (VNet injection) with forced tunneling enabled, and route egress through a firewall such as Azure Firewall or a user-defined route to an NVA. VNet injection gives you control over the subnets, NSGs, and routing, and forced tunneling redirects all outbound traffic to your inspection appliance. This is the documented architecture for secure, no-public-IP Databricks deployments.

Answer

Configure service endpoints for Azure Storage and Azure Data Lake Storage.

Answer

Apply network security groups (NSGs) to the subnet that restrict outbound traffic.

Answer

Enable Azure Private Link for the Databricks workspace.

Question 14

Your organization uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled. You need to grant a service principal read and write access to a specific directory without granting access to the parent directories. What should you use?

Accepted Answer

Set ACLs on the directory with default ACLs for the service principal.. Azure RBAC roles for Azure Storage cannot be scoped to a directory or file; they are assigned at the storage account or container level. To grant a service principal read and write access to a specific directory in an Azure Data Lake Storage Gen2 account with hierarchical namespace enabled, use access control lists (ACLs) on that directory. Default ACLs on the directory will be inherited by newly created child items. Option A is incorrect because the Storage Blob Data Contributor role cannot be assigned at the directory level. Option B is incorrect because a managed identity is an identity, not a permission mechanism. Option C is incorrect because stored access policies are used for shared access signatures (SAS), not for granting directory access.

Answer

Assign the Storage Blob Data Contributor role at the directory level using RBAC.

Answer

Use a managed identity and assign it to the directory.

Answer

Create a stored access policy on the directory.

Question 15

Refer to the exhibit. You are reviewing the workload classifier configuration for an Azure Synapse Analytics dedicated SQL pool. You notice that the 'HeavyLoader' classifier has a queryExecutionTimeoutSeconds of 0. What is the implication of this setting?

Accepted Answer

Queries classified as 'HeavyLoader' will not have a timeout.. When queryExecutionTimeoutSeconds is set to 0, it means no timeout is enforced. Queries classified as 'HeavyLoader' can run indefinitely without being terminated by the timeout mechanism. Option A is incorrect because a value of 0 does not mean indefinite waiting for resources; it refers to the timeout duration. Option B is incorrect; 0 is a valid configuration that disables the timeout. Option D is incorrect; a timeout of 0 does not cause immediate timeout but rather no timeout.

Answer

Queries classified as 'HeavyLoader' will wait indefinitely for resources.

Answer

The configuration is invalid; queryExecutionTimeoutSeconds must be greater than 0.

Answer

Queries classified as 'HeavyLoader' will timeout immediately.