DP-203 Practice Test 11 — 15 Questions

Question 1

You are designing a data processing solution in Azure Synapse Analytics. The solution must prevent unauthorized access to data at rest and in transit. Which combination of features should you implement?

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

Accepted Answer

Enable Transparent Data Encryption (TDE) and enforce TLS 1.2.. Transparent Data Encryption (TDE) encrypts data at rest in Azure Synapse Analytics, and enforcing TLS 1.2 ensures encryption of data in transit. Option B (Azure RBAC and firewall rules) controls access but does not provide encryption. Option C (Always Encrypted and column-level security) is primarily for client-side encryption and access control, not comprehensive at-rest encryption. Option D (storing keys in Key Vault and enabling double encryption) relates to key management and infrastructure encryption, but the direct combination of TDE and TLS 1.2 is the required solution.

Answer

Use Azure RBAC and firewall rules.

Answer

Use Always Encrypted and column-level security.

Answer

Store encryption keys in Azure Key Vault and enable double encryption.

Question 2

Which THREE components are valid parts of the Microsoft Purview Data Map? (Choose THREE)

Accepted Answer

Scan rule sets. Correct answers: A, D, E. The Microsoft Purview Data Map consists of components that define how data sources are registered and scanned. Scan rule sets (A) are used to configure scanning rules for data sources. Data sources (D) are the actual data locations that are registered and scanned. Classifications (E) are the rules or patterns that identify data types (e.g., PII) during scanning; they are a core component of the Data Map. B (Sensitivity labels) is incorrect because sensitivity labels are part of Microsoft Information Protection, not a component of the Data Map itself, though they can be applied to assets in Purview. C (Data flows) is incorrect because data flows are part of Azure Data Factory, not the Data Map.

Answer

Sensitivity labels

Answer

Data flows

Question 3

You are optimizing an Azure Data Factory pipeline that moves data from Azure Blob Storage to Azure SQL Database. The pipeline currently uses a Copy activity with a staging setting enabled. You notice high DTU consumption on the Azure SQL Database during the copy operation. How can you reduce the impact on the source database?

Accepted Answer

Configure the Copy activity to use staging with Azure Blob Storage and then use a stored procedure to bulk insert.. Staging the copy to Azure Blob Storage and then using a stored procedure for bulk insert reduces the direct load on Azure SQL Database, lowering DTU consumption during the copy. Option B is incorrect because PolyBase is designed for Azure Synapse Analytics, not Azure SQL Database. Option C (parallel copy) can increase throughput but also increases DTU usage, not reducing it. Option D (increasing DTU) is a scaling measure, not a reduction technique.

Answer

Use PolyBase to load data directly from Blob Storage to Azure SQL Database.

Answer

Enable parallel copy in the Copy activity settings.

Answer

Increase the DTU of the Azure SQL Database to handle the load.

Question 4

Your Azure Synapse Analytics dedicated SQL pool is experiencing performance degradation. Queries that previously completed in seconds now take minutes. You suspect memory pressure and concurrency issues. What should you first review to diagnose the problem?

Accepted Answer

sys.dm_pdw_exec_requests. Sys.dm_pdw_exec_requests provides a list of all currently executing or recent requests in the dedicated SQL pool, including their status, resource class, and elapsed time. This is the best first step to identify queries that may be causing memory pressure or concurrency issues. Option A (sys.dm_pdw_resource_waits) shows only resource waits but does not show the actual requests causing the waits. Option B (sys.dm_pdw_waits) shows wait information but lacks the direct request context needed for diagnosis. Option C (sys.dm_pdw_query_stats_xe) provides query execution statistics but not real-time active requests, so it is less suitable for immediate troubleshooting.

Answer

sys.dm_pdw_resource_waits

Answer

sys.dm_pdw_waits

Answer

sys.dm_pdw_query_stats_xe

Question 5

You are monitoring an Azure Data Factory pipeline that copies data from Azure Blob Storage to Azure SQL Database. The pipeline fails intermittently with the error: 'Operation on target SQL table failed: String or binary data would be truncated.' Which action should you take to resolve this issue?

Accepted Answer

Increase the length of the destination columns in the SQL table to accommodate the source data.. The error indicates that source data length exceeds destination column length. Increasing column size resolves it. Option B is incorrect because the table already exists. Option C is incorrect because the error is not about connection. Option D is incorrect because the error is not about identity insert.

Answer

Set 'enable identity insert' to true.

Answer

Use auto-create table option in the copy activity.

Answer

Enable staging copy to use PolyBase.

Question 6

You are designing a data processing solution in Azure Synapse Analytics. The solution must prevent unauthorized access to data at rest and in transit. Which combination of features should you implement?

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

Accepted Answer

Azure Stream Analytics.. Azure Stream Analytics is the correct choice because it natively integrates with Azure Event Hubs and dedicated SQL pools, supports exactly-once semantics through checkpointing and output deduplication, and provides built-in handling for late-arriving data via configurable late arrival tolerance windows and out-of-order event policies.

Answer

Azure Data Factory with tumbling window trigger.

Answer

Azure Functions with Event Hubs trigger.

Answer

Azure HDInsight Spark Structured Streaming.

Question 7

You need to monitor an Azure Data Factory pipeline for failures and send an email notification when a pipeline run fails. Which Azure service should you use to create an alert based on the pipeline run metrics?

Accepted Answer

Azure Monitor. Azure Monitor can create alerts based on ADF metrics like 'Failed pipeline runs'. Option A is wrong because Microsoft Sentinel is for security. Option C is wrong because Azure Service Health monitors Azure service health, not pipeline runs. Option D is wrong because Azure Log Analytics is for log queries, not alerting.

Answer

Microsoft Sentinel

Answer

Azure Service Health

Answer

Azure Log Analytics

Question 8

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.. Storing connection strings and secrets in Azure Key Vault and referencing them via linked services ensures secrets are stored securely and not exposed in pipeline definitions. Option D is correct: Enabling Managed Virtual Network (VNet) isolates data flows within a managed network boundary, preventing public network access. Option A is incorrect: Configuring a self-hosted integration runtime on a public cloud VM does not necessarily improve security; it may expose the runtime to the public internet. Option B is incorrect: Using the default Auto-resolve Integration Runtime is not recommended for secure data processing because it may use public endpoints and lacks network isolation. Option E is incorrect: Allowing all public IP addresses to access the Azure Synapse workspace exposes the workspace to potential security threats.

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 9

You are developing a data pipeline in Azure Data Factory that ingests data from multiple on-premises SQL Server databases to Azure Data Lake Storage Gen2. The data volume is about 1 TB per day. You need to ensure the pipeline can handle the volume and provide monitoring and alerting. Which THREE components should you include?

Accepted Answer

Self-hosted integration runtime. Correct answers: B, C, and E. Self-hosted integration runtime (B) is required for connectivity to on-premises SQL Server databases. Azure Monitor (C) provides monitoring and alerting for the pipeline. Data flow activity (E) allows data transformation at scale. Option A is incorrect because Azure Synapse Analytics pipeline is not used in this context; the pipeline is in Azure Data Factory. Option D is incorrect because Power BI is for visualization, not data ingestion.

Answer

Azure Synapse Analytics pipeline

Answer

Power BI

Question 10

You are troubleshooting a Synapse Spark notebook that fails when reading Parquet files from Azure Data Lake Storage Gen2. The error message indicates 'Permission denied'. The notebook uses a managed identity (System-assigned) for authentication. The Data Lake Storage account has a firewall enabled with 'Allow Azure services on the trusted services list' turned on. The storage account's RBAC role assignments include 'Storage Blob Data Contributor' for the managed identity. What is the most likely cause of the failure?

Accepted Answer

The storage account firewall does not have a 'Resource instances' exception for the managed identity. When a storage account firewall is enabled with 'Allow Azure services on the trusted services list' turned on, it only allows trusted Azure platform services to access the storage account, but it does not automatically grant access to a specific managed identity. To allow a managed identity to bypass the firewall, you must add a 'Resource instances' exception for that managed identity's resource (e.g., the Synapse workspace). Without this explicit exception, the managed identity's request is blocked by the firewall, resulting in a 'Permission denied' error even though the RBAC role assignment is correct.

Answer

Parquet files require special permissions that are not granted by RBAC roles

Answer

The managed identity has not been granted the 'Storage Blob Data Reader' role in addition to 'Storage Blob Data Contributor'

Answer

The notebook is using an incorrect connection string with account key

Question 11

You are designing a data lake on Azure Data Lake Storage Gen2. The data will be used by both batch processing (Spark) and interactive querying (Azure Synapse Serverless SQL). The data is partitioned by date and stored as Parquet. What is the optimal folder structure to minimize cross-partition scans for both workloads?

Accepted Answer

/year/month/day/ (e.g., /2023/12/25/). (/year/month/day/) is optimal because it aligns with Hive-style partitioning, which both Spark and Azure Synapse Serverless SQL can leverage for partition pruning. Spark uses partition discovery to read only relevant directories, and Synapse Serverless SQL uses the file path metadata to filter partitions, minimizing cross-partition scans and reducing data read overhead.

Answer

All files in a single folder

Answer

/yyyy-mm-dd/ (e.g., /2023-12-25/)

Answer

Files named by date (e.g., data_20231225.parquet)

Question 12

Which TWO are required to run a stored procedure in Azure SQL Database from Azure Data Factory?

Accepted Answer

A linked service to Azure SQL Database.. To run a stored procedure in Azure SQL Database from Azure Data Factory, only two components are required: a linked service to Azure SQL Database, which defines the connection and authentication, and a stored procedure activity in the pipeline that specifies the stored procedure name and parameters directly. A dataset is not required because the stored procedure activity can define the stored procedure reference inline. Options like a self-hosted integration runtime or staging storage are unnecessary for this task.

Answer

A dataset that references the stored procedure.

Answer

A self-hosted integration runtime.

Answer

A staging blob storage account.

Question 13

You are designing a data processing solution in Azure Synapse Analytics. The solution must prevent unauthorized access to data at rest and in transit. Which combination of features should you implement?

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

Accepted Answer

Use Azure Stream Analytics to output to both Power BI and Data Lake Storage. Azure Stream Analytics can directly output to both Power BI (for real-time dashboards) and Azure Data Lake Storage Gen2 (for historical analysis) in a single job, minimizing operational overhead by avoiding the need for multiple services or custom code. This serverless, fully managed service handles streaming data processing with low latency and integrates natively with Azure IoT Hub or Event Hubs for ingestion, making it ideal for healthcare IoT scenarios.

Answer

Ingest data via Azure Functions and write to Data Lake Storage; use Power BI to query Data Lake

Answer

Use Azure Databricks with Structured Streaming to write to Data Lake Storage and use Power BI DirectQuery

Answer

Ingest data to Azure Event Hubs, then use Event Hubs Capture to store in Data Lake Storage; use Power BI with Event Hubs

Question 14

You are reviewing an Azure Resource Manager template for an Azure SQL Database auditing policy. Based on the exhibit, which of the following is true?

Accepted Answer

Audit logs will be written to Azure Blob Storage.. The template shows the 'state' property set to 'Enabled' and includes a 'storageEndpoint' property, which indicates that audit logs are configured to be written to Azure Blob Storage. Option A is incorrect because 'retentionDays' is set to 90 days, so logs are not retained indefinitely. Option B is incorrect because 'auditActionsAndGroups' is specified in the template, meaning default audit actions and groups are not used. Option C is incorrect because the destination is Blob Storage, not Azure Log Analytics, as indicated by the 'storageEndpoint' property.

Answer

Audit logs will be retained indefinitely.

Answer

The audit policy will use default audit actions and groups.

Answer

Audit logs will be sent to Azure Log Analytics.

Question 15

You are designing a batch processing pipeline that reads CSV files from Azure Blob Storage, performs aggregations using Azure Databricks, and writes results to Azure Synapse Analytics. The pipeline must handle schema drift (new columns appearing in source files). Which approach should you recommend?

Accepted Answer

Use Spark with mergeSchema option when reading, and write using a Delta table to evolve schema automatically.. Spark's `mergeSchema` option, when used with Delta Lake, automatically evolves the schema to accommodate new columns in CSV files. This allows the batch pipeline to handle schema drift without manual intervention, and writing to a Delta table ensures the schema evolution is persisted and compatible with downstream writes to Azure Synapse Analytics.

Answer

Use Azure Data Factory mapping data flows with schema drift enabled, mapping to a fixed sink schema.

Answer

Define a fixed schema in the source and ignore any new columns.

Answer

Use Azure Stream Analytics to pre-process and enforce schema.