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

Question 1

You are configuring security for an Azure Synapse Analytics workspace that uses a serverless SQL pool. The workspace is connected to Azure Data Lake Storage Gen2 via a managed identity. You need to ensure that only the Synapse workspace can access the storage account, and no other Azure service or user can access it directly. The storage account should not be accessible from the public internet. What should you do?

Accepted Answer

Configure a private endpoint for the storage account in the same virtual network as the Synapse workspace, and disable public network access.. Option A is correct because a private endpoint for the storage account, combined with disabling public network access, ensures only the Synapse workspace can access it via the managed identity. Option B is wrong because firewall rules alone do not restrict access to a specific service. Option C is wrong because allowing trusted Microsoft services is broader than just Synapse. Option D is wrong because managed identity alone does not restrict network access.

Answer

Configure the storage account firewall to allow only the Synapse workspace's public IP address.

Answer

Enable 'Allow trusted Microsoft services to access this storage account' on the firewall.

Answer

Use Azure RBAC to assign the Storage Blob Data Contributor role to the Synapse workspace managed identity.

Question 2

You manage an Azure Synapse Analytics dedicated SQL pool that contains a large fact table 'Orders' with 500 million rows. The table is hash-distributed on 'OrderDate' and uses a clustered columnstore index. Query performance has degraded over time. You check the system DMVs and find that the columnstore segments have poor quality, with many deleted rows and compressed rowgroups below 1 million rows. You need to improve query performance without blocking writes to the table. What should you do?

Accepted Answer

Run ALTER INDEX REORGANIZE with COMPRESS_ALL_ROW_GROUPS = ON.. Option B is correct because ALTER INDEX REORGANIZE with COMPRESS_ALL_ROW_GROUPS option rebuilds columnstore segments online without blocking. Option A is wrong because REBUILD is offline and blocks writes. Option C is wrong because dropping and recreating the index is offline. Option D is wrong because reclustering reorganizes data within a distribution but does not fix columnstore quality.

Answer

Drop and recreate the clustered columnstore index.

Answer

Re-cluster the table using a different distribution key.

Answer

Run ALTER INDEX REBUILD on the clustered columnstore index.

Question 3

Your company uses Azure Data Lake Storage Gen2 as a data lake. You need to monitor for unauthorized access attempts and ensure compliance with regulatory requirements. You want to be alerted when someone attempts to access data without proper permissions. What should you configure?

Accepted Answer

Enable diagnostic settings for the storage account, send logs to Log Analytics, and create an alert rule for 'StorageRead' failures.. Option D is correct because diagnostic settings send logs to Log Analytics, and Azure Monitor alerts can be created to detect unauthorized access. Option A is wrong because Azure Policy is for governance, not real-time alerts. Option B is wrong because RBAC is for access control, not monitoring. Option C is wrong because Azure Sentinel is for SIEM, but simpler solution is Log Analytics alerts.

Answer

Assign RBAC roles to limit access and rely on Azure Activity Log.

Answer

Configure Azure Policy to audit storage account access.

Answer

Enable Azure Sentinel and connect storage logs.

Question 4

You are designing a data pipeline in Azure Data Factory that reads data from an Azure SQL Database and writes to Azure Synapse Analytics dedicated SQL pool. The pipeline will run hourly and process incremental changes. You need to ensure that the pipeline handles retries for transient failures and logs all activities for audit purposes. What should you configure?

Accepted Answer

Enable pipeline logging to an Azure Storage account and set retry policy on copy activity.. Option B is correct because enabling pipeline logging to a storage account captures all activity, and retry policy in activities handles transient failures. Option A is wrong because Azure Monitor can monitor but does not log pipeline activities. Option C is wrong because Azure SQL Database audit is for database access, not pipeline. Option D is wrong because event-based trigger does not provide logging or retry.

Answer

Enable auditing on the Azure SQL Database and Azure Synapse SQL pool.

Answer

Configure a tumbling window trigger to rerun the pipeline on failure.

Answer

Use an Azure Monitor alert to notify on failures.

Question 5

Your organization uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2. You notice that queries are taking longer than expected. You need to identify which queries are consuming the most resources and optimize them. What should you do first?

Accepted Answer

Query the sys.dm_exec_requests DMV to view running queries and their resource usage.. Option A is correct because DMVs like sys.dm_exec_requests provide real-time resource consumption for serverless SQL pool. Option B is wrong because query hints may help but you need to identify problematic queries first. Option C is wrong because diagnostic settings send logs to Log Analytics, but DMVs are immediate. Option D is wrong because statistics are already maintained by the serverless pool.

Answer

Use query hints to optimize execution plans.

Answer

Enable diagnostic settings and send query logs to Log Analytics.

Answer

Create statistics on all columns used in queries.

Question 6

You have an Azure Data Factory pipeline that copies data from an FTP server to Azure Blob Storage. The pipeline runs successfully most of the time, but occasionally fails with a 'FTP server connection refused' error during peak hours. You need to minimize these failures with minimal cost. What should you do?

Accepted Answer

Add a retry policy to the copy activity with a backoff interval.. Option D is correct because adding a retry policy with a short delay handles transient connection issues. Option A is wrong because increasing the number of parallel copies does not solve connection refused errors. Option B is wrong because Azure ExpressRoute is costly and unnecessary for FTP. Option C is wrong because migrating to SFTP may not resolve connection refused if the server is overloaded.

Answer

Set up Azure ExpressRoute to improve network reliability.

Answer

Migrate the FTP server to SFTP.

Answer

Increase the parallel copy count in the copy activity.

Question 7

Which TWO actions should you take to secure data at rest in Azure Synapse Analytics dedicated SQL pools?

Accepted Answer

Use Always Encrypted with secure enclaves for specific columns.. Option A is correct: Transparent Data Encryption (TDE) encrypts data at rest without application changes. Option D is correct: Always Encrypted protects sensitive columns with client-side encryption. Option B (Dynamic Data Masking) is for masking data in query results, not at rest. Option C (Azure RBAC) is for access control, not encryption. Option E (Column-level security) is for access control, not encryption.

Answer

Implement column-level security to filter sensitive columns.

Answer

Configure Dynamic Data Masking on tables containing sensitive data.

Answer

Assign Azure RBAC roles to restrict access to the storage account.

Question 8

Which THREE measures should you implement to monitor and optimize the performance of Azure Data Lake Storage Gen2?

Accepted Answer

Enable Storage Insights to monitor capacity and transactions.. Option A is correct: Metrics like 'Ingress' help identify bottlenecks. Option C is correct: Diagnostic logs provide request details for troubleshooting. Option D is correct: Azure Storage Analytics offers latency and throughput data. Option B is incorrect: Tiering is for cost optimization, not performance monitoring. Option E is incorrect: NSG flow logs are for network security, not storage performance.

Answer

Enable Network Security Group flow logs for the storage account subnet.

Answer

Configure lifecycle management policies to move cold data to archive tier.

Question 9

Which TWO configurations are recommended to secure data processing in Azure Synapse Pipelines?

Accepted Answer

Store connection strings and secrets in Azure Key Vault and reference them via linked services.. Option B is correct: Managed VNet ensures data integration runs in a secure network boundary. Option D is correct: Azure Key Vault integration stores secrets securely. Option A is incorrect: Public endpoint access reduces security. Option C is incorrect: Auto-resolve integration runtime may use public endpoints. Option E is incorrect: Self-hosted IR is for on-premises, not for cloud security.

Answer

Configure a self-hosted integration runtime on a public cloud VM.

Answer

Use the default Auto-resolve Integration Runtime for all data flows.

Answer

Allow all public IP addresses to access the Azure Synapse workspace.

Question 10

Contoso Ltd. runs a real-time analytics solution on Azure Databricks with data streaming from Event Hubs. They need to ensure that all data in transit between Event Hubs and Databricks is encrypted using TLS 1.2 or higher. Currently, the Event Hubs namespace is configured with the default TLS version (1.0). The Databricks cluster uses a public endpoint. Compliance requires that only TLS 1.2 is accepted. You need to configure the environment to enforce TLS 1.2 without disrupting ongoing streaming. What should you do?

Accepted Answer

Update the Event Hubs namespace to require TLS 1.2, then modify the Databricks streaming job's connection string to include 'TransportType=AmqpTls' and restart the streaming job.. Step 1: Update the Event Hubs namespace to require TLS 1.2 via 'Minimum TLS version' setting. Step 2: Configure the Databricks cluster to connect using TLS 1.2 by setting spark.conf to 'spark.eventhubs.connectionString' with TLS parameter. Step 3: Restart the streaming job to apply new connection settings. Option D includes all steps in order. Option A reboots without config change. Option B uses Azure CLI but doesn't cover Databricks. Option C only updates Event Hubs but not Databricks.

Answer

Change the Event Hubs namespace minimum TLS version to 1.2 in the Azure portal, then reboot the Databricks cluster.

Answer

In the Event Hubs namespace, set 'Minimum TLS version' to 1.2 and redeploy the Databricks cluster with a new init script that forces TLS 1.2.

Answer

Use Azure CLI to set the Event Hubs namespace TLS version to 1.2 and update the Databricks cluster's Spark configuration to use TLS 1.2.

Question 11

You are a data engineer at Northwind Traders. You have an Azure Synapse Analytics workspace with dedicated SQL pools. You need to monitor query performance to identify slow-running queries and understand resource consumption. The solution must provide historical data for the last 30 days and allow alerting when queries exceed a certain duration. You also need to export the data to a Log Analytics workspace for correlation with other metrics. What should you use?

Accepted Answer

Enable Diagnostic Settings to stream SQL pool metrics and logs to a Log Analytics workspace, then create alert rules and use KQL queries for historical analysis.. Option B is correct: Azure Monitor diagnostic settings can send Synapse SQL pool logs and metrics to Log Analytics. Then you can create alerts and run queries over historical data. Option A is incorrect: Dynamic Management Views (DMVs) only provide current state, not historical 30-day data. Option C is incorrect: Query Store retains data for a limited time and doesn't integrate natively with Log Analytics. Option D is incorrect: Azure Storage Analytics is for storage accounts, not Synapse SQL.

Answer

Enable Diagnostic Settings for the dedicated SQL pool to send logs to Azure Storage, and query DMVs for historical data.

Answer

Use Azure Storage Analytics to analyze logs from the storage account backing the SQL pool.

Answer

Configure SQL Server Query Store and export data to Azure Blob Storage using elastic query.

Question 12

Your company uses Azure Data Factory to orchestrate data pipelines that ingest data from on-premises SQL Server to Azure Data Lake Storage Gen2. The network team has implemented a firewall that only allows outbound traffic on port 443. The on-premises SQL Server is not accessible via public endpoint. You need to configure a secure connection that complies with the firewall rules and uses managed identity for authentication. What should you use?

Accepted Answer

Install a Self-hosted Integration Runtime on an on-premises VM, register it with Azure Data Factory using managed identity, and configure the pipeline to use this IR for the SQL Server connection.. Option B is correct: A self-hosted integration runtime (IR) installed on-premises with managed identity can connect to SQL Server using private network, then communicate with Azure Data Factory over port 443. Option A is incorrect: Azure IR cannot connect to on-premises SQL Server without a public endpoint. Option C is incorrect: Azure VPN Gateway requires additional network configuration and doesn't use managed identity. Option D is incorrect: ExpressRoute is a private connection but doesn't address authentication.

Answer

Use Azure ExpressRoute to connect the on-premises network to Azure, then use an Azure Integration Runtime with a VNet injection.

Answer

Set up a point-to-site VPN from Azure to on-premises and use an Azure Integration Runtime with a VNet integration.

Answer

Use an Azure Integration Runtime with a public endpoint and configure a firewall rule to allow the Azure IR IP addresses.

Question 13

Your organization uses Azure SQL Database with Active Geo-Replication for disaster recovery. You need to ensure that all connections to the database use Microsoft Entra ID authentication and that access is audited. You also want to minimize the attack surface by disabling SQL authentication. What should you do?

Accepted Answer

Enable 'Azure AD-only authentication' in the Azure SQL Database server settings and remove all SQL Server authenticated logins.. Option C is correct: Set 'Azure AD-only authentication' to 'True' in the Azure SQL Database server properties. This disables SQL authentication and enforces Entra ID. Option A is incorrect: Azure RBAC controls management plane, not database access. Option B is incorrect: SQL authentication is still enabled. Option D is incorrect: Conditional Access policies work with Entra ID but don't disable SQL authentication.

Answer

Configure Conditional Access policies to require MFA for database access.

Answer

Create a server-level firewall rule to allow only specific IP addresses and enable SQL authentication.

Answer

Create an Azure RBAC role to restrict access to the database and assign it to users.

Question 14

You are designing a data processing solution in Azure Synapse Analytics. The solution must process streaming data from IoT devices and store it in a dedicated SQL pool for reporting. The data volume is high (millions of events per hour), and you need to optimize for both ingestion speed and query performance. You also need to ensure that the data can be partitioned by date for efficient maintenance. Which architecture should you recommend?

Accepted Answer

Ingest data to Azure Data Lake Storage Gen2 in Delta format, then use PolyBase to load into a dedicated SQL pool partitioned by date.. Option B is correct: Stream data to Azure Data Lake Storage (ADLS) in Delta format, then use PolyBase to load into Synapse dedicated SQL pool. Delta format provides fast reads and optimizations. Option A is incorrect: Direct streaming to SQL pool is not efficient for high volumes. Option C is incorrect: Azure SQL Database is not designed for large-scale analytics. Option D is incorrect: Event Hubs Capture stores data in Avro, which is less performant for queries than Delta.

Answer

Use Azure Stream Analytics to write directly to a dedicated SQL pool with a time-based window.

Answer

Store data in Azure SQL Database with elastic scaling and use linked server queries.

Answer

Use Event Hubs Capture to store data in Avro files in Blob Storage and then query with external tables.

Question 15

Your company has a data lake in Azure Data Lake Storage Gen2 that stores sensitive customer information. You need to implement fine-grained access control so that data engineers can read all data, data scientists can read only anonymized data, and auditors can view access logs. The solution must use Azure role-based access control (RBAC) and access control lists (ACLs). You also need to enable auditing of read operations. What should you do?

Accepted Answer

Assign Storage Blob Data Contributor RBAC role to data engineers at the storage account level, use ACLs to deny read access to raw data for data scientists, and enable diagnostic settings for read requests to Log Analytics.. Option D is correct: Use Azure RBAC roles for coarse permissions (e.g., Storage Blob Data Contributor for engineers), use ACLs for fine-grained control (e.g., deny read for data scientists on raw data), and enable diagnostic settings to log read operations to Log Analytics. Option A is incorrect: Storage Analytics logs are deprecated. Option B is incorrect: Lifecycle management is for tiering, not access control. Option C is incorrect: RBAC only cannot provide granular control at file level.

Answer

Use Azure RBAC to assign Storage Blob Data Reader to data scientists, and configure lifecycle management to move raw data to archive tier.

Answer

Assign RBAC roles at the storage account level and enable Storage Analytics logs for read operations.

Answer

Assign RBAC roles at the container level to grant read access to all users, and use Azure Policy to audit access.