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

Question 1

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.. Options A and C are correct. A: Column-level security allows restricting access to sensitive columns. C: Transparent Data Encryption (TDE) encrypts data at rest. Option B is wrong because Dynamic Data Masking obfuscates data but does not prevent access at the storage level. Option D is wrong because firewall rules are not a best practice for protecting sensitive data within the database. Option E is wrong because Azure Storage encryption is not applicable to Synapse SQL pool data.

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 2

Your organization uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled. You need to implement a monitoring strategy to detect and alert on unusual access patterns that could indicate a security breach. Which THREE services or features should you use? (Choose three.)

Accepted Answer

Enable Microsoft Defender for Storage to get security alerts about unusual access patterns.. Options A, B, and D are correct. A: Diagnostic settings stream logs to Log Analytics. B: Microsoft Sentinel provides advanced security analytics. D: Microsoft Defender for Storage detects anomalies. Option C is wrong because Azure Monitor Metrics do not include access logs. Option E is wrong because Azure Policy is for governance, not monitoring.

Answer

Apply Azure Policy to enforce encryption and access policies.

Answer

Use Azure Monitor Metrics to track storage account transactions and latency.

Question 3

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline is failing with a 'Gateway is offline' error. What is the most likely cause?

Accepted Answer

The Self-Hosted Integration Runtime is not running or cannot connect to the Azure Data Factory service.. Option B is correct because the Self-Hosted Integration Runtime (SHIR) is required for on-premises data sources. If the SHIR is not running or unreachable, the pipeline fails. Option A is wrong because the SHIR is used, not Azure IR. Option C is wrong because the Azure IR cannot connect to on-premises networks. Option D is wrong because a VNet is optional for SHIR.

Answer

The Azure Integration Runtime is being used instead of a Self-Hosted Integration Runtime.

Answer

The Azure Integration Runtime is not configured to use the correct region.

Answer

The source SQL Server is not configured to allow remote connections from Azure.

Question 4

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.. Option C is correct because the stored procedure `sp_updatestats` updates statistics for all tables in the database. Option A is wrong because `UPDATE STATISTICS` requires specifying table and index. Option B is wrong because that procedure is for the master database. Option D is wrong because automatic update is enabled by default but may not trigger for all tables; manual update is more reliable.

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.

Question 5

You are using Azure Stream Analytics to process real-time data from an event hub and output to Azure Synapse Analytics. You need to ensure exactly-once delivery semantics to the output. What should you configure?

Accepted Answer

Set the output to 'Exactly Once' delivery mode.. Option A is correct because Azure Stream Analytics supports exactly-once delivery to Azure Synapse Analytics by enabling 'Exactly Once' output mode. Option B is wrong because checkpointing is for state management. Option C is wrong because event ordering does not guarantee exactly-once. Option D is wrong because the event hub is the input, not output.

Answer

Set the input to 'Exactly Once' consumption mode.

Answer

Configure event ordering and late arrival policies.

Answer

Enable checkpointing in the query.

Question 6

You are designing a data processing solution using Azure Databricks. You need to optimize costs while maintaining performance. Which TWO strategies should you implement? (Choose two.)

Accepted Answer

Use spot instances for non-critical, fault-tolerant workloads.. Options B and D are correct. B: Using spot instances reduces costs for fault-tolerant workloads. D: Autoscaling adjusts resources based on workload. Option A is wrong because all-purpose clusters are more expensive and not recommended for production jobs. Option C is wrong because using larger VMs may lead to underutilization and higher costs. Option E is wrong because Azure Databricks is not serverless in the traditional sense; you pay for running VMs.

Answer

Always choose the largest VM sizes to minimize cluster count.

Answer

Use all-purpose clusters for all workloads to avoid startup delays.

Answer

Use the serverless pricing tier to avoid paying for idle resources.

Question 7

Your organization uses Azure Purview for data governance. You need to ensure that sensitive data is properly classified and that access to it is monitored. Which THREE actions should you take? (Choose three.)

Accepted Answer

Create custom sensitivity labels in Microsoft Purview Information Protection and apply them to data sources.. Options A, B, and D are correct. A: Automated scanning classifies data. B: Labeling helps in applying sensitivity tags. D: Microsoft Defender for Cloud Apps can monitor access and generate alerts. Option C is wrong because Azure Policy does not directly classify or monitor data access. Option E is wrong because Azure Sentinel is for security incident detection, not data classification.

Answer

Define Azure Policy initiatives to enforce classification on all storage accounts.

Answer

Use Azure Sentinel to classify data as it is ingested.

Question 8

You use Azure Data Lake Storage Gen2 with a hierarchical namespace. You need to delegate permissions to a group of data scientists so they can create folders and upload files only within a specific directory path. What is the best way to achieve this?

Accepted Answer

Set ACL entries on the specific directory path granting read, write, and execute permissions to the users.. Option B is correct because Azure Data Lake Storage Gen2 supports POSIX-like ACLs that can be set on directory paths to grant granular permissions. Option A is wrong because RBAC roles are scoped to the entire storage account or container, not subdirectories. Option C is wrong because SAS tokens are scoped to the entire storage account or container. Option D is wrong because access policies are for shared access signatures, not granular directory permissions.

Answer

Use a stored access policy to grant permissions to the directory.

Answer

Generate a shared access signature (SAS) with permissions scoped to the specific directory.

Answer

Assign the Storage Blob Data Contributor role to the users at the storage account level.

Question 9

You are designing a data pipeline using Azure Synapse Pipelines. The pipeline ingests data from multiple sources, performs transformations using a notebook, and loads the results into a dedicated SQL pool. You need to ensure that if the notebook fails, the entire pipeline stops and sends an alert. What is the most efficient way to configure this?

Accepted Answer

Add a 'Fail' activity after the notebook activity and connect the notebook's failure output to it. Configure the Fail activity to send an alert.. Option B is correct because in Azure Synapse Pipelines (or Azure Data Factory), you can set the activity's 'Failure path' to go to a 'Fail' activity that terminates the pipeline and can trigger an alert via webhook or email. Option A is wrong because setting retry to 0 does not stop the pipeline; it just doesn't retry. Option C is wrong because a wildcard error path would still allow other activities to run if not explicitly failed. Option D is wrong because the default behavior is to continue if there's no error path defined.

Answer

Set the notebook activity's error path to a webhook activity that sends an alert, and then set a wildcard error path for the pipeline.

Answer

Set the notebook activity's retry count to 0, and configure an alert on the pipeline run failure.

Answer

No configuration needed; by default, a failed activity stops the entire pipeline.

Question 10

A company uses Azure Synapse Analytics dedicated SQL pool. They notice that queries against a large fact table are slow. They have already created statistics on all columns used in WHERE clauses and JOIN predicates. What should they do next to improve query performance?

Accepted Answer

Partition the table on a frequently filtered column.. Option B is correct because partitioning can help with partition elimination, reducing data scanned. Option A is wrong because they already created statistics. Option C is wrong because increasing DWU might help but is not the best first step. Option D is wrong because result-set caching is for repeated queries, not for improving scan efficiency.

Answer

Enable result-set caching.

Answer

Increase the DWU setting for the dedicated SQL pool.

Answer

Create additional statistics on all columns.

Question 11

You need to ensure that data in an Azure Data Lake Storage Gen2 account is encrypted at rest using a customer-managed key. Which feature should you configure?

Accepted Answer

Azure Key Vault integration with Storage Service Encryption. Option C is correct because ADLS Gen2 supports encryption at rest with customer-managed keys via Azure Key Vault. Option A is wrong because Azure Information Protection is for labeling, not encryption at rest. Option B is wrong because Azure Disk Encryption is for VMs. Option D is wrong because Azure Storage Service Encryption uses Microsoft-managed keys by default; customer-managed keys require Key Vault.

Answer

Azure Information Protection

Answer

Azure Storage Service Encryption with Microsoft-managed keys

Answer

Azure Disk Encryption

Question 12

An organization is using Azure Data Factory to ingest data from multiple on-premises SQL Server databases into Azure Synapse Analytics. They need to ensure that sensitive data is masked during ingestion before landing in the staging area. What is the best approach?

Accepted Answer

Use a Mapping Data Flow with derived column transformations to mask sensitive columns.. Option D is correct because Data Flow allows transformation steps including column masking. Option A is wrong because Azure SQL Database dynamic data masking is applied at query time, not during copy. Option B is wrong because Azure Purview is for governance, not data masking. Option C is wrong because Azure Policy is for compliance, not data masking at source.

Answer

Apply an Azure Policy that masks sensitive data in Azure Synapse Analytics.

Answer

Use Azure SQL Database dynamic data masking on the source databases.

Answer

Use Azure Purview to classify and mask sensitive data automatically.

Question 13

A company uses Azure Databricks for data processing. They want to monitor the performance of Spark jobs and set up alerts for job failures. Which Azure service should they use?

Accepted Answer

Azure Monitor. Option C is correct because Azure Monitor can collect metrics and logs from Azure Databricks and set up alerts. Option A is wrong because Azure Log Analytics is part of Azure Monitor but is not the service name for alerting. Option B is wrong because Azure Sentinel is a SIEM, not for monitoring job performance. Option D is wrong because Azure Advisor provides recommendations, not monitoring and alerts.

Answer

Azure Advisor

Answer

Azure Sentinel

Answer

Azure Log Analytics

Question 14

You are designing a data pipeline in Azure Data Factory that copies data from Azure Blob Storage to Azure SQL Database. The data contains personally identifiable information (PII). What should you use to protect the data during transit?

Accepted Answer

Encryption over HTTPS/TLS. Option B is correct because Azure Data Factory always encrypts data in transit using TLS. Option A is wrong because Azure Information Protection is for labeling, not transit encryption. Option C is wrong because Azure Disk Encryption is for at-rest encryption of disks. Option D is wrong because Azure Storage Service Encryption is for at-rest encryption.

Answer

Azure Information Protection

Answer

Azure Disk Encryption

Answer

Azure Storage Service Encryption

Question 15

A company uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2. They notice that queries are slow and want to improve performance by reducing the amount of data read. What is the most effective strategy?

Accepted Answer

Partition the data by a frequently filtered column and use file elimination in queries.. Option A is correct because partitioning the data in the lake and using partition elimination reduces data read. Option B is wrong because OPENROWSET with CSV reads all files; it does not reduce data read. Option C is wrong because CETAS is for creating external tables, not for improving query performance directly. Option D is wrong because increasing the number of compute nodes is not possible in serverless; it's auto-scaling.

Answer

Increase the number of compute nodes in the serverless pool.

Answer

Use OPENROWSET with CSV format instead of Parquet.

Answer

Create external tables using CETAS and query them.