Microsoft Azure Data Engineer Associate DP-203 DP-203 Questions 376–450 | Page 6/7

376

MCQeasy

You are developing an Azure Databricks notebook that processes JSON files stored in Azure Data Lake Storage Gen2. You need to read the files into a DataFrame and automatically infer the schema. Which code should you use?

A.spark.read.format("json").schema("infer").load("abfss://container@storage.dfs.core.windows.net/path")

B.spark.read.text("abfss://container@storage.dfs.core.windows.net/path")

C.spark.read.option("inferSchema", "true").csv("abfss://container@storage.dfs.core.windows.net/path")

D.spark.read.json("abfss://container@storage.dfs.core.windows.net/path")

AnswerD

The spark.read.json method reads JSON files and infers the schema by default when no schema is provided. Using the abfss:// URI accesses ADLS Gen2 through the Azure Blob File System driver, which is the recommended protocol in Databricks. This single call returns a DataFrame with columns derived from the JSON structure, satisfying the requirement to infer the schema automatically.

Why this answer

The JSON data source in Spark automatically infers the schema when no schema is specified. Using spark.read.json with an abfss:// path reads the files from ADLS Gen2 and returns a DataFrame with inferred columns. The other options either misuse the API, use the wrong file format reader, or return unparsed text, so they do not meet the requirement.

Exam trap

The trap here is confusing the inferSchema option, which is used with the CSV reader, with schema inference for JSON, which happens by default without any option.

Full explanation →

377

MCQeasy

You are implementing a data pipeline using Azure Data Factory. The source is an on-premises SQL Server database. Which Azure Data Factory component is required to connect to the on-premises data source?

A.Azure Integration Runtime

B.Self-hosted Integration Runtime

C.Managed Virtual Network Integration Runtime

D.Azure Data Factory Gateway

AnswerB

A self-hosted integration runtime runs on a machine inside the on-premises network, giving Azure Data Factory a secure outbound channel to SQL Server without inbound firewall openings. The Azure-hosted runtime cannot reach on-premises sources directly.

Why this answer

A self-hosted integration runtime (IR) is required to connect Azure Data Factory to on-premises SQL Server because it provides the compute environment for data movement between on-premises networks and Azure. It must be installed on a machine inside the corporate firewall, enabling secure communication via outbound HTTPS (port 443) to Azure. This is the only IR type that can access private, on-premises data sources directly.

Exam trap

The trap here is that candidates often confuse the Self-hosted Integration Runtime with the Azure Integration Runtime, not realizing that only the self-hosted variant can bridge on-premises and cloud networks, while the Azure IR is restricted to cloud-to-cloud scenarios.

Why the other options are wrong

Azure IR runs in the cloud and cannot access on-premises networks directly.

Managed VNet IR is for secure access to Azure resources, not on-premises.

While historically called Gateway, the correct term is Self-hosted Integration Runtime.

Full explanation →

378

Multi-Selecthard

You are monitoring an Azure Data Lake Storage Gen2 account that stores streaming data from IoT devices. You notice that query performance on the data in Parquet format is degrading over time. You need to improve query performance for both current and future data. Which TWO actions should you take?

Select 2 answers

A.Move frequently accessed data to Azure SQL Database.

B.Partition the data by a column commonly used in filter conditions.

C.Convert the Parquet files to Delta Lake format and enable file compaction.

D.Enable soft delete on the storage account to optimize read performance.

E.Migrate the data to Azure NetApp Files for lower latency.

AnswersB, C

Partitioning by a frequently filtered column enables partition pruning, so queries scan only relevant folders rather than the whole dataset. This reduces I/O for both existing and newly arriving IoT data, directly addressing the degrading Parquet query performance described in the stem.

Why this answer

Option B is correct because partitioning the data by a column commonly used in filter conditions (for example, device ID or event date) enables partition pruning, so queries scan only the relevant folders instead of the entire dataset, which directly improves performance for both existing and newly arriving streaming data. Option C is correct because converting Parquet files to Delta Lake format and enabling file compaction addresses the small-file problem typical of streaming ingestion; OPTIMIZE compaction merges many small files into larger ones, reducing per-file overhead and metadata/listing costs, while Delta Lake adds a transaction log and data-skipping statistics that further accelerate queries. Option A is not appropriate because moving data to Azure SQL Database changes the storage platform rather than optimizing the Data Lake Gen2 Parquet data, and it is not a scalable fit for streaming IoT data.

Option D is incorrect because soft delete is a data-protection feature for recovering deleted blobs and has no effect on read/query performance. Option E is incorrect because Azure NetApp Files is a high-performance NFS/SMB file service, not a query-optimization solution for Parquet data in Data Lake Storage Gen2, and migrating would not address the small-file and partitioning issues.

Exam trap

The trap here is that candidates often confuse data protection features (like soft delete) or storage migration options (like Azure SQL or NetApp Files) with performance optimization techniques, failing to recognize that partitioning and file format optimization are the standard solutions for improving query performance on large-scale Parquet data in a data lake.

Full explanation →

379

MCQmedium

You are designing a data storage solution for a retail company that needs to store transaction data that is frequently updated and requires strong consistency. The solution must support complex queries and joins across multiple tables. Which Azure data service should you recommend?

A.Azure Cosmos DB

B.Azure SQL Database

C.Azure Synapse Analytics

D.Azure Table Storage

AnswerB

Azure SQL Database provides ACID transactions with strong consistency, supports frequent updates, and handles complex multi-table joins through its relational engine. This satisfies the transactional integrity and query complexity requirements, which analytical or key-value stores would not meet.

Why this answer

Azure SQL Database is a fully managed relational database service that provides strong consistency, supports complex queries and joins across multiple tables, and is optimized for frequently updated transaction data. It offers ACID compliance and built-in high availability, making it the ideal choice for this retail scenario.

Exam trap

The trap here is that candidates often choose Azure Cosmos DB for its low-latency and global distribution capabilities, overlooking that it does not provide native relational joins or the strong consistency required for transactional workloads, which Azure SQL Database is specifically designed for.

Why the other options are wrong

Cosmos DB is NoSQL and while it can be configured for strong consistency, it does not natively support complex joins across multiple tables as efficiently as a relational database.

Synapse is a data warehouse for analytics, not designed for transactional workloads with frequent updates.

Table Storage is a NoSQL key-value store with limited query capabilities and no support for complex joins.

Full explanation →

380

MCQeasy

You are designing a streaming job in Azure Stream Analytics. The job needs to count the number of events per device type every 10 seconds. The input is from Event Hubs. Which query should you use?

A.SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, SessionWindow(second, 10, 30)

B.SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, TumblingWindow(second, 10)

C.SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, HoppingWindow(second, 10, 1)

D.SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, SlidingWindow(second, 10)

AnswerB

TumblingWindow(second, 10) partitions events into fixed, non-overlapping 10-second intervals, satisfying the stem's requirement to count per device type every 10 seconds. Grouping by DeviceType alongside the window produces one count per device type per interval, which sliding or session windows cannot guarantee.

Why this answer

A TumblingWindow(second, 10) produces non-overlapping, fixed-size 10-second windows, which is exactly what is needed to count events per device type every 10 seconds. The GROUP BY clause groups by DeviceType and the window, ensuring each device type gets its own count per window. This query meets the requirement without overlapping or sliding behavior.

Exam trap

The trap here is that candidates confuse HoppingWindow with TumblingWindow, thinking a hop size of 1 second still produces 10-second intervals, but HoppingWindow emits results at every hop, not at the window duration, leading to incorrect output frequency.

How to eliminate wrong answers

Option A is wrong because SessionWindow(second, 10, 30) defines session windows based on inactivity gaps, not fixed 10-second intervals; the 30-second timeout means windows can be much longer than 10 seconds, violating the requirement. Option C is wrong because HoppingWindow(second, 10, 1) creates overlapping windows that emit results every 1 second, not every 10 seconds, leading to redundant counts. Option D is wrong because SlidingWindow(second, 10) produces a continuous stream of results for every event within the last 10 seconds, not discrete 10-second intervals, so it does not count events 'every 10 seconds' as a batch.

Full explanation →

381

MCQmedium

You are using Azure Synapse Analytics dedicated SQL pool to run a query that joins a large fact table (10 billion rows) and a small dimension table (1 million rows). The query is slow. Which distribution strategy should you use for the dimension table to improve performance?

A.Round-robin distribute the dimension table.

B.Hash-distribute the dimension table on its primary key.

C.Replicate the dimension table to all compute nodes.

D.Hash-distribute the dimension table on the foreign key column.

AnswerC

Replicating the small dimension table places a full copy on every compute node, eliminating data movement during joins with the 10-billion-row fact table. Because replicated tables are readable on all distributions, each node joins locally, which removes the shuffle that made the query slow.

Why this answer

Replicating the small dimension table (1 million rows) to all compute nodes eliminates data movement during the join with the large fact table (10 billion rows). In Azure Synapse dedicated SQL pool, replicated tables store a full copy on each distribution, so the join can be performed locally on every node without shuffling data across the network, drastically reducing query latency.

Exam trap

The trap here is that candidates often choose hash distribution on the foreign key (Option D) thinking it aligns the join keys, but they overlook that the fact table is typically distributed on a different column (e.g., its own primary key or a date column), so the join still requires data movement, whereas replication is the optimal strategy for small dimension tables in a star schema.

How to eliminate wrong answers

Option A is wrong because round-robin distribution spreads the dimension table evenly across distributions without any alignment with the fact table, causing all join operations to require data movement (shuffle) across nodes, which is highly inefficient for a large fact table. Option B is wrong because hash-distributing the dimension table on its primary key does not align with the fact table's distribution key (typically the foreign key), so the join will still require redistributing one or both tables unless the fact table is also hash-distributed on the same column. Option D is wrong because hash-distributing the dimension table on the foreign key column would scatter its rows across distributions, but the fact table is likely hash-distributed on a different column (e.g., its own primary key or a different foreign key), so the join would still cause data movement; moreover, dimension tables are typically small and benefit more from replication than from hash distribution.

Full explanation →

382

MCQmedium

Your company uses Azure Data Factory to orchestrate data movement. You need to monitor pipeline runs across multiple factories and create a dashboard that shows success and failure rates over the past 30 days. What is the most efficient approach?

A.Use the Data Factory monitoring UI to view runs for each factory individually.

B.Enable Azure Storage Analytics and query the logs stored in a storage account.

C.Configure diagnostic settings for each Data Factory to send logs to a Log Analytics workspace, then create a workbook using KQL queries.

D.Create alert rules in Azure Monitor for each pipeline failure and aggregate manually.

AnswerC

Routing diagnostic logs from every factory into a single Log Analytics workspace centralises cross-factory data, and a workbook built on KQL queries aggregates success and failure rates over 30 days. This satisfies the multi-factory dashboard requirement without per-factory tooling.

Why this answer

Diagnostic settings in Azure Data Factory can route ActivityRuns, PipelineRuns, TriggerRuns, and other logs to a Log Analytics workspace. Once logs from multiple factories land in the same workspace, KQL queries in an Azure Monitor workbook can aggregate success/failure counts across all factories over a 30-day window, giving a single consolidated dashboard. This is the most efficient, scalable approach for multi-factory monitoring.

Exam trap

DP-203 often tests whether candidates know that Data Factory monitoring UI is per-factory and that cross-factory aggregation requires diagnostic settings to Log Analytics — many pick the UI option because it 'looks' like monitoring.

How to eliminate wrong answers

Option A is wrong because the Data Factory monitoring UI is per-factory and does not natively aggregate across factories or provide a unified 30-day dashboard. Option B is wrong because Azure Storage Analytics logs storage account activity, not Data Factory pipeline runs, so it cannot answer pipeline success/failure questions. Option D is wrong because alert rules fire on individual failures and do not produce aggregated success/failure rate dashboards; manual aggregation is not efficient or reliable.

Full explanation →

383

MCQmedium

A manufacturing company uses Azure Data Lake Storage Gen2 to store IoT sensor data. The data arrives in JSON format with a nested structure. You need to transform the data into a tabular format for downstream analytics using Azure Synapse Pipelines. Which data flow transformation should you use?

A.Aggregate transformation

B.Flatten transformation

C.Window transformation

D.Pivot transformation

AnswerB

Flatten transformation unnests hierarchical JSON arrays into separate rows, converting the nested sensor structure into a tabular shape. This directly satisfies the stem's requirement to transform nested JSON into tabular output for downstream analytics, whereas derived column or aggregate transformations cannot unroll arrays.

Why this answer

The Flatten transformation in mapping data flows unpacks nested arrays into rows. Option A is wrong because the Aggregate transformation groups data but does not flatten nested structures. Option C is wrong because the Window transformation calculates aggregated values over a range of rows.

Option D is wrong because the Pivot transformation rotates rows to columns.

Full explanation →

384

MCQmedium

You are implementing a data processing solution in Azure Synapse Analytics using Spark pools. The solution reads Parquet files from Azure Data Lake Storage Gen2, performs transformations, and writes the results to a dedicated SQL pool. You need to optimize the write performance to the dedicated SQL pool. Which technique should you use?

A.Use the PolyBase connector with a staging location in Azure Blob Storage.

B.Use the 'spark.sql.sources.partitionOverwriteMode' setting to overwrite partitions.

C.Use the JDBC connector with batch inserts and set the batch size to 10,000 rows.

D.Write the data to a Parquet file in Data Lake Storage Gen2 and then use a Synapse pipeline to load it.

AnswerA

The PolyBase connector in Azure Synapse Spark pools writes data to a staging area in Azure Blob Storage or Data Lake Storage Gen2, then uses PolyBase to load it into the dedicated SQL pool. This is the recommended approach for large data loads because it leverages the parallel bulk load capabilities of PolyBase, significantly improving write performance compared to row-by-row inserts.

Why this answer

The PolyBase connector is the most efficient way to write large datasets from Azure Synapse Spark pools to a dedicated SQL pool. It stages the data in Azure Blob Storage or Data Lake Storage Gen2 and then uses PolyBase to load it in parallel, which is much faster than JDBC batch inserts. This approach minimizes the load on the SQL pool and leverages its bulk load capabilities.

Exam trap

The trap here is assuming that increasing JDBC batch size is sufficient for performance, when actually PolyBase's parallel staging is far more efficient for large volumes.

Full explanation →

385

MCQeasy

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?

A.Increase the length of the destination columns in the SQL table to accommodate the source data.

B.Set 'enable identity insert' to true.

C.Use auto-create table option in the copy activity.

D.Enable staging copy to use PolyBase.

AnswerA

Direct fix for truncation error.

Why this answer

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.

Full explanation →

386

MCQmedium

You are a data engineer at a manufacturing company. You need to process sensor data from IoT devices that arrive in real time. The data is sent to Azure Event Hubs. You need to aggregate the data over 5-minute windows and store the results in Azure Data Lake Storage Gen2 in Parquet format. The solution should minimize cost and use serverless components. Which solution should you use?

A.Use Azure Stream Analytics to create a query with a tumbling window of 5 minutes, and output the results to Azure Data Lake Storage Gen2 in Parquet format.

B.Use Azure Databricks with Structured Streaming to read from Event Hubs, aggregate with a sliding window, and write to ADLS Gen2 in Parquet.

C.Use Azure Data Factory with a tumbling window trigger to run a pipeline every 5 minutes that copies data from Event Hubs to ADLS Gen2.

D.Use Azure Functions with an Event Hubs trigger to aggregate data in memory and write to ADLS Gen2.

AnswerA

Stream Analytics provides a fully managed, serverless engine with native tumbling-window aggregation over Event Hubs input, and writes Parquet directly to Data Lake Storage Gen2. This satisfies the real-time 5-minute windowing, serverless and cost-minimisation constraints without provisioning clusters.

Why this answer

Azure Stream Analytics is a fully managed, serverless real-time analytics service that natively supports tumbling windows and can output directly to Azure Data Lake Storage Gen2 in Parquet format. It minimizes operational cost and management overhead because there are no clusters to provision, and it integrates directly with Event Hubs as an input.

Exam trap

DP-203 often tests the confusion between tumbling, hopping, and sliding windows, and whether the candidate recognizes that Stream Analytics is the serverless streaming option versus Databricks or Functions.

How to eliminate wrong answers

Option B is wrong because Azure Databricks with Structured Streaming requires provisioning and managing a cluster, which is not serverless and increases cost — it also uses sliding windows, not the required tumbling windows. Option C is wrong because Azure Data Factory with a tumbling window trigger is a batch orchestration mechanism, not a real-time streaming aggregation solution, and it would not aggregate data in 5-minute windows natively. Option D is wrong because Azure Functions with an Event Hubs trigger would require custom in-memory aggregation logic, which is not reliable for windowed aggregation and does not scale well for streaming workloads.

Full explanation →

387

MCQeasy

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?

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

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

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

D.The Self-Hosted Integration Runtime is not running or cannot connect to the Azure Data Factory service.

AnswerD

The 'Gateway is offline' error occurs when the Self-Hosted Integration Runtime agent, which brokers connectivity between the on-premises SQL Server and Azure Data Factory, is stopped or loses outbound connectivity to the service. Restarting the runtime or restoring its network path resolves the failure.

Why this answer

The Self-Hosted Integration Runtime (SHIR) acts as the gateway between on-premises data sources and Azure Data Factory. If the SHIR is not running or cannot communicate with the Azure Data Factory service, the pipeline fails with a 'Gateway is offline' error. Option A is incorrect because using the Azure Integration Runtime for an on-premises source would cause a different error, not 'Gateway is offline'.

Option B is incorrect because region configuration for the Azure Integration Runtime is irrelevant when a SHIR is required. Option C is incorrect because the error relates to the gateway, not to SQL Server remote connection settings.

Full explanation →

388

MCQeasy

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?

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

B.Create a nonclustered index on the date column.

C.Ensure that queries include a predicate on the partitioning column to enable partition elimination.

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

AnswerC

Partition elimination allows the query engine to skip reading partitions that do not contain relevant data. By including a filter on the partitioning column, queries only scan the necessary partitions, reducing I/O and improving performance. This is a fundamental optimization for partitioned tables.

Why this answer

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.

Exam trap

The trap here is thinking that adding indexes or changing distribution will reduce data reads, when partition elimination is the key for partitioned tables.

Full explanation →

389

Multi-Selecthard

Which TWO options are recommended bulk loading methods for Azure Synapse SQL Pool? (Choose two.)

Select 2 answers

A.Using INSERT INTO VALUES

B.Using BCP utility

C.Using the COPY statement

D.Using SQL Server Integration Services (SSIS)

E.Using PolyBase to load from Azure Blob Storage

AnswersC, E

The COPY statement is a modern, high-performance bulk load method, but it is not the only valid option; the question requires exactly two answers.

Why this answer

For Azure Synapse SQL Pool, the recommended bulk loading methods are the COPY statement and PolyBase. BCP and SSIS are supported for some scenarios, but they are not the recommended bulk loading methods for large-scale data loads. INSERT INTO VALUES is row-by-row and is not recommended for bulk loading.

Exam trap

Do not confuse supported utilities with recommended bulk loading methods. BCP and SSIS can connect to Synapse SQL Pool, but for high-volume bulk loads, COPY and PolyBase are the recommended approaches.

Full explanation →

390

MCQhard

You are designing a data lake architecture for a healthcare company. The solution must support fine-grained access control at the file level, encryption at rest and in transit, and integration with Microsoft Purview for data lineage. Which storage solution should you recommend?

A.Azure NetApp Files.

B.Azure Files.

C.Azure Data Lake Storage Gen2 (ADLS Gen2).

D.Azure Blob Storage.

AnswerC

ADLS Gen2 combines a hierarchical namespace with POSIX ACLs for file-level access control, plus encryption at rest and TLS in transit. Its native integration with Microsoft Purview supplies the required data lineage, meeting all three healthcare compliance constraints in the scenario.

Why this answer

Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical namespace with POSIX-like access control lists (ACLs) for fine-grained file-level permissions, supports encryption at rest (Azure Storage Service Encryption) and in transit (TLS 1.2+), and natively integrates with Microsoft Purview for automated data lineage and cataloging. This makes it ideal for healthcare scenarios requiring strict compliance and auditability.

Exam trap

The trap here is that candidates often confuse Azure Blob Storage with ADLS Gen2, assuming blob storage's container-level permissions are sufficient for file-level control, but the hierarchical namespace and POSIX ACLs are exclusive to ADLS Gen2 and required for the fine-grained access described.

How to eliminate wrong answers

Option A is wrong because Azure NetApp Files provides NFS/SMB file shares with ACLs but lacks native integration with Microsoft Purview for data lineage and is not optimized for large-scale analytics workloads like data lakes. Option B is wrong because Azure Files offers SMB file shares with ACLs but does not support the hierarchical namespace or POSIX ACLs needed for fine-grained file-level control in a data lake, and its Purview integration is limited compared to ADLS Gen2. Option D is wrong because Azure Blob Storage provides encryption and Purview integration but lacks a hierarchical namespace and POSIX ACLs, making it impossible to enforce fine-grained access control at the individual file level.

Full explanation →

391

MCQmedium

A company uses Azure Synapse Analytics dedicated SQL pool. They need to ensure that only users with a specific Azure AD group can query a particular schema. Which approach should they use?

A.Configure a server-level firewall rule to block other users.

B.Use the GRANT statement to grant SELECT on the schema to the Azure AD group.

C.Create a row-level security policy on all tables in the schema.

D.Apply dynamic data masking to the schema.

AnswerB

GRANT SELECT on the schema to the Microsoft Entra ID group grants query permission to every member through group membership, which is the granular, group-scoped control the scenario requires. This restricts access to that schema without per-user grants.

Why this answer

The GRANT statement in Azure Synapse dedicated SQL pool allows you to assign permissions directly to Azure AD groups. By granting SELECT on the schema to the specific Azure AD group, only members of that group can query objects within that schema, meeting the requirement precisely.

Exam trap

The trap here is that candidates often confuse network-level controls (firewall rules) or data obfuscation techniques (masking, RLS) with access control, when the correct solution is a straightforward permission grant using T-SQL's GRANT statement.

How to eliminate wrong answers

Option A is wrong because server-level firewall rules control network access to the entire Azure SQL logical server, not granular schema-level access for specific Azure AD groups. Option C is wrong because row-level security (RLS) restricts access to specific rows within tables based on a predicate function, not entire schemas or tables at the schema level. Option D is wrong because dynamic data masking obfuscates sensitive data in query results but does not prevent users from querying the schema or seeing the underlying data with appropriate permissions.

Full explanation →

392

MCQeasy

You are a data engineer at a financial services company. You are developing a data processing pipeline that uses Azure Data Factory to copy transactional data from an Azure SQL Database to Azure Data Lake Storage Gen2. The pipeline runs daily and processes about 10 GB of data. You need to implement error handling for the pipeline. Specifically, if the copy activity fails due to a transient error, the pipeline should retry automatically. If the retry fails, the pipeline should log the error and send an email alert to the operations team. What should you do?

A.Configure the copy activity with retry policy (retry count = 2, retry interval = 30 seconds). Add a failure path to a web activity that calls an Azure Logic App to send an email.

B.Use Azure Functions to implement custom retry logic and send email.

C.Create an Azure Monitor alert for failed pipeline runs and configure an action group to send an email.

D.Set the pipeline retry to 2 and add a storage event trigger on the error file.

AnswerA

The copy activity's retry policy handles transient faults automatically, and the failure output path routes to a Web activity invoking a Logic App for email. This satisfies both stated requirements: automatic retry, then logging and alerting when retries are exhausted.

Why this answer

Configuring the copy activity's retry policy (retry count and interval) handles transient failures automatically at the activity level. Adding a failure dependency path to a Web activity that invokes a Logic App provides the required logging and email alert when retries are exhausted. This directly satisfies both the retry and notification requirements.

Exam trap

The trap is choosing Azure Monitor alerts alone, which notify but do not retry, or over-engineering with Functions when the built-in retry policy already meets the requirement.

How to eliminate wrong answers

Option B is wrong because Azure Functions custom retry logic is unnecessary complexity when the copy activity has a built-in retry policy, and it does not natively provide the email alerting described. Option C is wrong because Azure Monitor alerts notify on failure but do not implement the automatic retry of the copy activity itself. Option D is wrong because a storage event trigger on an error file does not exist as a native ADF mechanism and does not perform the retry or alerting described.

Full explanation →

393

Multi-Selecthard

You are optimizing an Azure Synapse Analytics dedicated SQL pool that contains a fact table with 10 billion rows. Queries frequently join this fact table to a dimension table on a column that is not the distribution column of either table. You need to reduce data movement during these joins. Which two actions should you take? (Choose two.)

Select 2 answers

A.Hash-distribute the fact table on the join column.

B.Create a materialized view that pre-joins the fact and dimension tables.

C.Hash-distribute the fact table on a different column.

D.Use a replicated table for the fact table.

E.Replicate the dimension table.

AnswersA, E

Hash-distributing the fact table on the join column ensures that rows with the same join key are colocated on the same compute node. When joining to a dimension table that is also hash-distributed on the same column (or replicated), data movement is minimized or eliminated, reducing shuffle operations and improving query performance.

Why this answer

To reduce data movement during joins in a dedicated SQL pool, you can either replicate small dimension tables so they are available locally on all nodes, or hash-distribute the large fact table on the join column to colocate matching rows. Both actions minimize shuffle. Replicating the fact table is impractical due to its size, and other options do not directly address data movement.

Exam trap

The trap here is thinking that any distribution change or materialized view will automatically reduce data movement, but the key is to align distribution with join columns or replicate small tables.

Full explanation →

394

MCQmedium

You are designing a data processing pipeline that ingests data from a REST API endpoint every hour. The API returns JSON data with a varying schema. You need to store the raw data in Azure Data Lake Storage Gen2 and later process it using Azure Databricks. Which file format should you use for the raw data storage?

A.Parquet

B.CSV

C.JSON

D.Avro

AnswerC

JSON preserves the API's varying schema without predefined column definitions, so each hourly payload lands intact in Data Lake Storage Gen2. Databricks then reads this semi-structured text, inferring or applying schema at processing time rather than at ingestion, which a fixed-schema format such as Parquet or Avro would block.

Why this answer

C is correct because the raw data arrives from a REST API with a varying JSON schema, and storing it in JSON format preserves the exact structure and schema variability without data loss or transformation. JSON is schema-on-read, meaning the raw data can be ingested as-is into Azure Data Lake Storage Gen2 and later processed by Azure Databricks, which natively supports JSON parsing. This avoids premature schema enforcement that would occur with columnar or binary formats.

Exam trap

The trap here is that candidates often choose Parquet or Avro for their performance benefits, forgetting that raw data ingestion with varying schemas must prioritize schema flexibility over query optimization, which JSON uniquely provides.

How to eliminate wrong answers

Option A is wrong because Parquet is a columnar storage format that requires a fixed schema at write time, making it unsuitable for raw data with a varying schema; any schema mismatch would cause ingestion failures or data truncation. Option B is wrong because CSV is a flat, row-oriented format that cannot natively represent nested or hierarchical JSON structures without complex flattening, and it lacks schema flexibility for varying fields. Option D is wrong because Avro is a binary format with a schema embedded in the file, but it still requires a predefined schema for serialization, which conflicts with the requirement of a varying schema from the API.

Full explanation →

395

MCQmedium

You are writing a T-SQL query against a dedicated SQL pool in Azure Synapse Analytics. The query aggregates a fact table containing billions of rows by joining it to a small dimension table. You observe that the join produces a large amount of data movement and the query runs slowly. You need to reduce data movement for this recurring pattern. What should you do?

A.Increase the resource class of the user running the query.

B.Change the fact table's distribution to ROUND_ROBIN.

C.Replicate the small dimension table so a copy exists on every distribution.

D.Add a columnstore index to the dimension table.

AnswerC

A replicated table keeps a full copy on each distribution, so joins between a large distributed fact table and a small dimension can be completed locally without shuffling rows across the data movement service. For recurring joins against a genuinely small dimension, this removes the broadcast or shuffle step and is the standard way to cut data movement in a dedicated SQL pool.

Why this answer

In a dedicated SQL pool, join performance depends heavily on whether matching rows already reside on the same distribution. A replicated table places a complete copy of the small dimension on every distribution, letting the engine perform the join locally against the large fact table. This eliminates the shuffle or broadcast of the dimension and is the recommended pattern for recurring joins with small dimensions.

Exam trap

The trap here is assuming that indexing or added memory changes how rows are distributed for a join.

Full explanation →

396

MCQmedium

A company uses Azure Synapse Analytics with a dedicated SQL pool. They need to ensure that a team of data scientists can query all tables in the 'sales' schema but cannot modify any data or schema objects. Which role should the team be assigned?

A.db_owner

B.db_datareader

C.db_ddladmin

D.db_datawriter

AnswerB

Granting db_datareader provides SELECT permission on all user tables and views within the dedicated SQL pool database, satisfying the read-only requirement across the entire sales schema. It confers no INSERT, UPDATE, DELETE, or DDL rights, so data scientists cannot modify data or schema objects, matching the stated constraint precisely.

Why this answer

The `db_datareader` role grants read-only access to all user tables in a database, allowing the team to query all tables in the 'sales' schema without the ability to modify data or schema objects. This aligns perfectly with the requirement for data scientists to perform SELECT queries only.

Exam trap

The trap here is that candidates often confuse `db_datareader` with `db_datawriter` or assume `db_ddladmin` is required for querying, not realizing that read-only access is specifically granted by `db_datareader` without any write or schema modification capabilities.

How to eliminate wrong answers

Option A is wrong because `db_owner` provides full control over the database, including the ability to modify data and schema, which violates the requirement. Option C is wrong because `db_ddladmin` allows execution of Data Definition Language (DDL) commands like CREATE, ALTER, and DROP, enabling schema modifications. Option D is wrong because `db_datawriter` grants INSERT, UPDATE, and DELETE permissions, allowing data modification.

Full explanation →

397

MCQhard

You have an Azure Synapse Analytics dedicated SQL pool. You notice that some queries are taking longer than expected due to excessive data movement operations. You need to minimize data movement without changing the distribution columns. Which table design approach should you recommend?

A.Use replicated tables for small dimension tables

B.Use round-robin distribution for dimension tables

C.Use hash distribution for all tables

D.Use partitioning on join columns

AnswerA

Replicating small dimension tables places a full copy on every compute node, so joins against them avoid shuffle operations entirely. This eliminates data movement for dimension-to-fact joins without altering the distribution columns of the large fact tables.

Why this answer

Replicated tables are recommended for small dimension tables because they are copied to all compute nodes, avoiding data movement during joins. This reduces excessive data movement without changing distribution columns. Option B is incorrect because round-robin distribution distributes data evenly but does not reduce data movement for joins; it is typically used for staging tables.

Option C is incorrect because using hash distribution for all tables can lead to data movement when joining on different columns, and it is not a one-size-fits-all solution. Option D is incorrect because partitioning alone does not reduce data movement; it is used for data management and pruning, not for minimizing shuffle operations.

Full explanation →

398

MCQmedium

You are reviewing a Spark job definition in Azure Synapse Analytics. The job aggregates sales data. The job runs successfully but takes longer than expected. You notice that dynamic allocation is disabled and the executor instances are fixed at 10. The cluster has a maximum of 20 nodes. What is the most likely reason for the slow performance?

A.The file path is incorrect, causing data read errors.

B.The job cannot scale out beyond 10 executors because dynamic allocation is disabled.

C.The job is not parallelized because of a single partition.

D.The executor memory is too low for the aggregation.

AnswerB

With dynamic allocation disabled, Spark holds the executor count at the fixed value of 10, so it cannot request additional executors from the cluster's 20-node maximum. Enabling dynamic allocation lets Spark add executors during the aggregation's shuffle-heavy stages, which is the scaling constraint causing the slow runtime.

Why this answer

With dynamic allocation disabled and executor instances fixed at 10, the Spark job cannot utilize additional cluster resources even though the cluster supports up to 20 nodes. This means the job is artificially constrained to 10 executors, limiting parallelism and causing slower performance despite available compute capacity.

Exam trap

The trap here is that candidates may overlook the explicit configuration detail (dynamic allocation disabled, fixed 10 executors) and instead focus on generic performance issues like memory or partitioning, missing the direct scaling limitation.

How to eliminate wrong answers

Option A is wrong because an incorrect file path would cause job failures or data read errors, not simply slower performance; the job runs successfully. Option B is wrong because it is actually the correct answer. Option C is wrong because a single partition would cause extreme underutilization and likely very slow processing, but the question states the job aggregates sales data and runs successfully, implying some parallelism exists; the fixed executor count is the more direct bottleneck.

Option D is wrong because while low executor memory can cause spilling to disk and slowdowns, the question specifically highlights disabled dynamic allocation and fixed executors as the observed configuration, making insufficient scaling the primary issue.

Full explanation →

399

MCQmedium

You are using Azure Data Factory to copy data from an on-premises Oracle database to Azure Data Lake Storage Gen2. You need to ensure the copy activity can connect to the Oracle database without storing credentials in the pipeline JSON. What should you configure?

A.A parameterized dataset that prompts for credentials at runtime.

B.A self-hosted integration runtime with a service account that has Oracle access.

C.A managed private endpoint from the data factory to the Oracle server.

D.A linked service that references an Azure Key Vault secret for the Oracle credentials.

AnswerD

Azure Data Factory linked services can retrieve credentials from Azure Key Vault at runtime rather than embedding them in the pipeline or dataset JSON. By storing the Oracle password as a secret and referencing it from the linked service, you keep credentials out of the pipeline definition while still allowing the copy activity to authenticate. This is the supported pattern for secret management.

Why this answer

Azure Data Factory integrates with Azure Key Vault so linked services can reference secrets instead of embedding credentials. Storing the Oracle password as a Key Vault secret and referencing it from the linked service keeps sensitive values out of pipeline and dataset JSON. Network components such as integration runtimes and private endpoints address connectivity, not credential protection.

Exam trap

The trap here is assuming that a self-hosted integration runtime or a private endpoint also handles credential secrecy, when those components only solve connectivity.

Full explanation →

400

MCQhard

A data engineering team uses Azure Data Factory to load data from Azure SQL Database to Azure Data Lake Storage Gen2. They notice that the pipeline runs fail intermittently due to transient errors. They need to implement a retry policy with exponential backoff. What is the most efficient way to achieve this?

A.Use a 'Validation' activity before the copy to check source availability

B.Create a custom .NET activity to handle retries

C.Add a 'Until' loop with a wait activity in the pipeline

D.Configure the 'Retry' property on the copy activity with a count and exponential backoff interval

AnswerD

The copy activity's retry property automatically reattempts failed runs, and its exponential backoff interval progressively lengthens the delay between attempts, absorbing transient faults without custom pipeline logic. This is the native, most efficient mechanism for the stated requirement.

Why this answer

Azure Data Factory natively supports configuring a 'Retry' property on activities, including Copy activities, with an exponential backoff interval. This built-in mechanism automatically retries the activity upon transient failures without requiring custom logic, making it the most efficient and maintainable approach for handling intermittent errors.

Exam trap

The trap here is that candidates may overcomplicate the solution by choosing a custom loop or validation activity, overlooking that Azure Data Factory's native 'Retry' property with exponential backoff is the simplest and most efficient built-in mechanism for handling transient errors.

How to eliminate wrong answers

Option A is wrong because a 'Validation' activity only checks source availability before the copy starts; it does not retry the copy operation itself if a transient error occurs during data transfer. Option B is wrong because creating a custom .NET activity introduces unnecessary complexity, development overhead, and maintenance burden when Azure Data Factory already provides a native retry feature. Option C is wrong because an 'Until' loop with a wait activity requires manual implementation of retry logic and exponential backoff, which is less efficient and more error-prone than using the built-in 'Retry' property.

Full explanation →

401

MCQmedium

You are using Azure Purview to scan an Azure Data Lake Storage Gen2 account. After scanning, you notice that some files are not classified. What is the most likely reason?

A.The storage account is not registered in Purview

B.The files are in Parquet format

C.The classification rules are disabled

D.The file types are not included in the scan rule set

AnswerD

Purview only classifies file extensions covered by the scan rule set's classification rules. Files whose types fall outside that set are scanned for schema but never matched against classification patterns, so they remain unclassified despite a successful scan.

Why this answer

Azure Purview scans use a scan rule set that defines which file types are included for classification. If a file's extension or type is not listed in the rule set, Purview skips classification for that file even though the scan completes. This is the most common reason specific files remain unclassified after a successful scan.

Exam trap

DP-203 often tests the assumption that a completed scan means all files are classified, when in fact the scan rule set's file type inclusion determines what gets classified.

How to eliminate wrong answers

Option A is wrong because if the storage account were not registered in Purview, the scan itself would fail or not run at all, rather than selectively leaving some files unclassified. Option B is wrong because Parquet is a supported format in Purview and can be classified; the format alone does not prevent classification. Option C is wrong because disabling classification rules would affect all files, not just some, and would typically be an intentional global change rather than a selective gap.

Full explanation →

402

Multi-Selecteasy

Which TWO actions help optimize data storage costs in Azure Data Lake Storage Gen2?

Select 2 answers

A.Enable soft delete for blobs.

B.Enable geo-redundant storage (GRS) for the storage account.

C.Configure lifecycle management policies to move data to cool or archive tiers.

D.Enable encryption at rest using customer-managed keys.

E.Use locally redundant storage (LRS) for temporary data.

AnswersC, E

Lifecycle management policies automatically transition blobs from hot to cool or archive tiers based on age or last-access time, and archive storage carries the lowest per-gigabyte rate. This directly reduces storage costs for infrequently accessed data without manual intervention.

Why this answer

Option C is correct because lifecycle management policies automatically transition blobs from hot to cool or archive access tiers (or delete them) based on rules such as last-modified or last-access time, and the archive tier offers the lowest storage cost per GB, directly reducing storage spend. Option E is correct because locally redundant storage (LRS) replicates data three times within a single datacenter in the primary region and is the cheapest redundancy option, making it cost-effective for temporary or non-critical data that does not require regional or geographic durability. Option A is not a cost optimization—soft delete retains deleted blobs and continues to bill for that retained data, potentially increasing cost.

Option B is not a cost optimization because GRS replicates data to a secondary region, roughly doubling storage cost compared to LRS. Option D is not a cost optimization because customer-managed keys affect encryption control and key management (e.g., Key Vault costs) rather than reducing storage costs.

Exam trap

The trap here is that candidates often confuse cost-optimization features (like tiering) with data protection or security features (like soft delete, GRS, or encryption), which serve different purposes and may actually increase costs.

Full explanation →

403

MCQmedium

You are designing a data lake architecture using Azure Data Lake Storage Gen2. The data will be ingested from multiple sources with varying schemas. You need to organize the data in a way that supports both batch and streaming analytics while maintaining data lineage. Which folder structure convention should you use?

A.Organize by ingestion date only, with subfolders for each source.

B.Organize by source system, then by date.

C.Use a medallion architecture with three layers: bronze (raw), silver (cleaned), gold (aggregated).

D.Organize by file format (CSV, Parquet, JSON) and date.

AnswerC

The medallion architecture separates raw ingestion (bronze), validated and cleaned data (silver), and aggregated business-ready data (gold), accommodating varying source schemas while supporting both batch and streaming consumption. Each layer preserves lineage as data progressively transforms.

Why this answer

The medallion architecture (bronze, silver, gold) is the recommended pattern for Azure Data Lake Storage Gen2 when handling multiple sources with varying schemas. It supports both batch and streaming by storing raw data in bronze, applying incremental transformations in silver, and serving aggregated views in gold, while maintaining data lineage through clear layer boundaries and audit columns.

Exam trap

The trap here is that candidates often choose Option B (source then date) because it seems logical for organization, but they overlook the requirement to support both batch and streaming analytics while maintaining data lineage, which the medallion architecture explicitly addresses through layered transformations.

How to eliminate wrong answers

Option A is wrong because organizing by ingestion date only, with subfolders for each source, lacks schema evolution support and makes it difficult to trace data lineage across transformations. Option B is wrong because organizing by source system then by date does not provide a standardized processing pipeline for both batch and streaming, and it fails to separate raw, cleaned, and aggregated states. Option D is wrong because organizing by file format and date ignores the need for schema management and lineage tracking, and it does not facilitate incremental processing or data quality checks across layers.

Full explanation →

404

MCQhard

You are designing a data processing solution using Azure Databricks with Delta Lake. The data is partitioned by date and ingested daily. You notice that the Delta table has many small files, causing slow read performance. Which strategy should you recommend to optimize the table for faster queries?

A.Run OPTIMIZE on the table to compact small files.

B.Run ZORDER BY on the date column.

C.Run VACUUM to delete old files.

D.Increase the number of partitions by adding a new partition column.

AnswerA

OPTIMIZE compacts many small Parquet files into larger ones, typically around 1 GB, reducing per-file overhead and metadata scanning during reads. This directly addresses the small-file problem created by daily ingestion, delivering faster queries without altering the partitioning scheme.

Why this answer

Running OPTIMIZE on a Delta Lake table compacts many small files into larger ones, reducing the number of files that need to be read during queries. This directly addresses the slow read performance caused by the small file problem, which is common in daily partitioned ingestion. OPTIMIZE uses bin-packing to merge files up to a target size (default 256 MB), improving scan efficiency without changing the data.

Exam trap

The trap here is that candidates may confuse ZORDER BY (which improves data skipping but not file count) with OPTIMIZE (which reduces file count), or mistakenly think VACUUM or adding partitions solves the small file problem, when in fact they either don't address it or make it worse.

How to eliminate wrong answers

Option B is wrong because ZORDER BY is used to colocate related information within files to improve data skipping, but it does not reduce the number of small files; it only reorganizes data within existing files. Option C is wrong because VACUUM removes old, unreferenced files for storage cleanup and compliance, but it does not compact small files or improve read performance. Option D is wrong because increasing the number of partitions (e.g., by adding a new partition column) would create even more small files, worsening the small file problem and degrading read performance further.

Full explanation →

405

Matchingmedium

Match each Azure security feature to its description.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts

Matches

Role-based access control for Azure resources

Cloud-based identity and access management service

Manage cryptographic keys and secrets

Private connectivity to Azure services over VNet

Why these pairings

The correct matches are: Azure AD for identity and access management, Key Vault for secrets management, RBAC for fine-grained access control, and Managed Identity for automatically managed identities. Common confusions include swapping Azure AD with Managed Identity and Key Vault with RBAC.

Full explanation →

406

Multi-Selectmedium

Which TWO options are correct approaches to handle schema drift in Azure Data Factory Mapping Data Flows?

Select 2 answers

A.Use a conditional split to route rows with different schemas to separate sinks.

B.Define a rigid schema in the source dataset and reject rows that don't match.

C.Disable schema drift to improve performance.

D.Enable 'Allow schema drift' in the source transformation.

E.Use a derived column transformation to provide default values for missing columns.

AnswersD, E

This allows the data flow to handle changing columns.

Why this answer

Enabling 'Allow schema drift' in the source transformation is the primary mechanism in Mapping Data Flows to handle incoming columns that are not defined in the dataset schema. This setting allows the data flow to dynamically adapt to changes in the source data structure, such as new or missing columns, without requiring manual schema updates.

Exam trap

The trap here is that candidates often confuse handling schema drift with data routing or error handling, and they overlook that enabling schema drift is the foundational step that must be taken before any other transformations can work with the drifted columns.

Full explanation →

407

Multi-Selectmedium

A company is designing a data storage solution for a global application that requires low-latency reads and writes for user session data. The solution must support automatic failover across multiple Azure regions. Which TWO Azure services meet these requirements?

Select 2 answers

A.Azure Table Storage

B.Azure Cache for Redis

C.Azure Blob Storage

D.Azure Cosmos DB

E.Azure SQL Database

AnswersB, D

Supports geo-replication and automatic failover.

Why this answer

Azure Cache for Redis is correct because it provides an in-memory data store with sub-millisecond latency for both reads and writes, making it ideal for user session data. It supports automatic failover across Azure regions through geo-replication, where data from a primary cache is asynchronously replicated to a secondary cache in a paired region, ensuring high availability and disaster recovery.

Exam trap

The trap here is that candidates often confuse Azure Cosmos DB's multi-region writes with the specific requirement for low-latency session data, but Cosmos DB, while supporting automatic failover, has higher latency than an in-memory cache like Redis for frequent, small reads and writes typical of session state.

Full explanation →

408

MCQmedium

You have an Azure Data Factory pipeline that executes a stored procedure in Azure SQL Database. The pipeline fails with an error indicating that the stored procedure ran out of memory. What change should you make to the pipeline to resolve this?

A.Add a retry policy to the stored procedure activity.

B.Increase the pipeline activity timeout.

C.Use a Self-Hosted Integration Runtime instead of Azure IR.

D.Scale up the Azure SQL Database to a higher service tier.

AnswerD

The out-of-memory error originates in the Azure SQL Database engine executing the stored procedure, so the database's own memory limit is the bottleneck. Scaling to a higher service tier increases allocated memory, allowing the procedure to complete without failing.

Why this answer

The error indicates that the stored procedure ran out of memory, which is a resource limitation at the database level, not a transient failure or timeout issue. Scaling up the Azure SQL Database to a higher service tier (e.g., from Standard to Premium or increasing DTU/vCore count) provides more memory and compute resources, directly resolving the out-of-memory condition.

Exam trap

The trap here is that candidates confuse pipeline-level retries or timeouts with database-level resource constraints, assuming that retrying or waiting longer will fix a memory exhaustion error, which is a hard resource limit that requires scaling the database.

How to eliminate wrong answers

Option A is wrong because a retry policy only re-executes the activity on transient failures (e.g., network blips), but an out-of-memory error is a persistent resource constraint that will recur on retry. Option B is wrong because increasing the pipeline activity timeout extends the duration the pipeline waits for completion, but does not address the underlying memory shortage in the database. Option C is wrong because using a Self-Hosted Integration Runtime shifts data movement or activity execution to an on-premises or VM-based runtime, but does not affect the memory allocation of the Azure SQL Database where the stored procedure runs.

Full explanation →

409

MCQeasy

You are developing a data processing solution in Azure Synapse Analytics. The solution must use a serverless SQL pool to query Parquet files stored in Azure Data Lake Storage Gen2. Which authentication method should you use to ensure that the queries use the identity of the caller and adhere to Azure role-based access control (RBAC) permissions?

A.Microsoft Entra ID pass-through authentication.

B.Storage account key.

C.Shared access signature (SAS) token.

D.Service principal with a secret.

AnswerA

Microsoft Entra ID pass-through authentication lets the serverless SQL pool forward the caller's token to ADLS Gen2, so access is evaluated against that user's Azure RBAC assignments. This satisfies the requirement that queries use the caller's identity rather than a shared credential.

Why this answer

Microsoft Entra ID pass-through authentication (option A) is correct because it allows the serverless SQL pool to use the caller's identity when accessing Azure Data Lake Storage Gen2. This ensures that Azure RBAC permissions (e.g., Storage Blob Data Reader) assigned to the user are evaluated for each query, providing fine-grained access control without exposing storage account keys or tokens.

Exam trap

The trap here is that candidates often confuse 'service principal' (a fixed identity) with 'user identity' and select option D, not realizing that pass-through authentication is the only method that preserves the caller's individual RBAC permissions.

How to eliminate wrong answers

Option B (Storage account key) is wrong because it uses a shared secret that grants full administrative access to the storage account, bypassing RBAC and the caller's identity entirely. Option C (Shared access signature token) is wrong because it delegates access based on a pre-signed URI with fixed permissions and expiry, not the caller's identity, and does not enforce RBAC. Option D (Service principal with a secret) is wrong because it authenticates as a fixed application identity rather than the individual caller, so RBAC permissions are evaluated against the service principal, not the user who submitted the query.

Full explanation →

410

MCQmedium

Your organization uses Azure Synapse Analytics dedicated SQL pool to store sales data. You need to design a data loading process for a nightly batch that inserts new rows and updates existing rows based on the business key. The table has a clustered columnstore index. Which approach minimizes table fragmentation?

A.Use UPDATE for existing rows and INSERT for new rows.

B.Use DELETE and INSERT statements in a single transaction.

C.Use a MERGE statement to perform upserts.

D.Create a staging table, load data, then use CTAS and partition switching to replace the target partition.

AnswerD

Staging plus CTAS and partition switching writes new columnstore rowgroups atomically, avoiding the small-rowgroup fragmentation that row-by-row inserts or updates cause in a clustered columnstore index. This satisfies the minimise-fragmentation requirement for the nightly batch.

Why this answer

CTAS with partition switching is the recommended pattern for dedicated SQL pools because it writes new data into a new distribution/partition and swaps it in via ALTER TABLE ... SWITCH, avoiding row-by-row UPDATE/DELETE operations that create fragmentation and delta-store bloat on clustered columnstore indexes. Because the target partition is replaced atomically, the clustered columnstore index remains well-compressed with minimal deleted-row overhead.

Exam trap

DP-203 often tests the misconception that MERGE is the 'best practice' upsert for Synapse dedicated SQL pools, when in fact MERGE and row-level DML on clustered columnstore indexes cause fragmentation that CTAS + partition switching avoids.

How to eliminate wrong answers

Option A is wrong because row-by-row UPDATE and INSERT on a clustered columnstore index generates delta-store rows and deleted-row markers that degrade compression and require periodic REORGANIZE/REBUILD. Option B is wrong because DELETE followed by INSERT in a transaction marks rows as deleted in the columnstore and inserts into the delta store, producing the same fragmentation and requiring index maintenance. Option C is wrong because MERGE on a clustered columnstore index still performs row-level UPDATE/INSERT/DELETE operations that accumulate deleted rows and delta-store entries, worsening fragmentation.

Full explanation →

411

Multi-Selecteasy

Which TWO options are valid methods to load data from on-premises SQL Server into Azure Synapse Analytics?

Select 2 answers

A.SQL Server Integration Services (SSIS) package

B.Azure Data Factory with incremental copy

C.PolyBase from external table

AnswersA, B

SQL Server Integration Services is a valid method because it is a full-featured ETL tool that can connect to on-premises SQL Server and load data directly into Azure Synapse Analytics using connectors like the Synapse destination adapter.

Why this answer

Both SQL Server Integration Services (SSIS) and Azure Data Factory are fully capable of loading data from on-premises SQL Server into Azure Synapse Analytics. SSIS provides a mature, high-performance ETL tool that can directly target Synapse using the SQL Server Destination or the Azure Synapse Analytics Destination. Azure Data Factory, with its self-hosted integration runtime and incremental copy feature, offers a modern, cloud-native orchestration solution for scheduled and reliable data ingestion.

PolyBase from external table, while useful for querying external data, is not primarily a data loading method; it requires additional steps to persist the data into Synapse tables and does not directly load from on-premises SQL Server without external staging.

Exam trap

The trap is that candidates may view SSIS as outdated or legacy, but it remains a fully supported and effective method for loading data into Azure Synapse Analytics. Meanwhile, PolyBase is often mistakenly thought of as a loading method, but it is primarily a query engine for external data.

Full explanation →

412

Multi-Selectmedium

Which TWO actions should you take when monitoring Azure Data Lake Storage Gen2 to detect security threats?

Select 2 answers

A.Use Azure Security Center and Azure Defender for Storage.

B.Enable diagnostic settings for the storage account and send logs to Azure Sentinel.

C.Enable soft delete for blobs to recover from accidental deletions.

D.Configure firewall and virtual network service endpoints.

E.Set up alerting on the 'Transactions' metric.

AnswersA, B

Microsoft Defender for Storage surfaces anomalous access and upload activity on Data Lake Storage Gen2 accounts, while Azure Security Center centralises those alerts for investigation. Together they satisfy the requirement to detect security threats against the storage account.

Why this answer

Option A is correct because Azure Security Center (now Microsoft Defender for Cloud) with Azure Defender for Storage provides threat detection specifically for storage accounts, including anomalous access patterns, suspicious IP addresses, and unusual data exfiltration attempts against Data Lake Storage Gen2. Option B is correct because enabling diagnostic settings on the storage account and streaming the logs (such as StorageRead, StorageWrite, and StorageDelete) to Azure Sentinel allows security teams to correlate events, build analytics rules, and detect threats across the environment. Option C is incorrect because soft delete is a data protection and recovery feature for accidental deletion, not a security threat detection mechanism.

Option D is incorrect because firewall and virtual network service endpoints restrict network access to the storage account, which is a preventive control rather than a monitoring or detection action. Option E is incorrect because alerting on the 'Transactions' metric only tracks volume and availability of requests, not security-relevant behavior or threat indicators.

Exam trap

The trap here is that candidates often confuse data protection features (like soft delete) or network controls (like firewalls) with active threat detection, overlooking that only dedicated security monitoring tools (Azure Security Center/Defender and Sentinel) can identify and alert on security threats in real time.

Full explanation →

413

MCQeasy

You are processing streaming data from IoT devices using Azure Stream Analytics. The data includes temperature readings and device IDs. You need to calculate the average temperature per device over a 5-minute window, sliding every 1 minute. Which window function should you use?

A.Hop window

B.Session window

C.Sliding window

D.Tumbling window

AnswerA

A hopping window advances by a fixed hop interval while spanning a longer window size, so a five-minute window sliding every minute is expressed as Hop(5m, 1m). Tumbling windows cannot overlap, and sliding windows in Stream Analytics are triggered by events rather than clock time, so neither meets the per-device one-minute cadence.

Why this answer

A Hop window in Azure Stream Analytics allows you to specify a window size (5 minutes) and a hop size (1 minute), creating overlapping windows that slide forward every minute. This matches the requirement to calculate the average temperature per device over a 5-minute period, recalculated every minute, as the hop window outputs results at each hop interval while retaining data across overlapping windows.

Exam trap

The trap here is that candidates confuse 'sliding' with 'hopping' — a Sliding window in Stream Analytics is event-driven and does not produce periodic outputs, whereas a Hop window is time-driven and explicitly supports overlapping fixed-size windows with a hop interval.

How to eliminate wrong answers

Option B is wrong because a Session window groups events based on inactivity gaps (session timeout), not fixed time intervals, and would not produce consistent 5-minute windows sliding every 1 minute. Option C is wrong because a Sliding window in Stream Analytics outputs results only when an event occurs (e.g., for each new event), not at fixed time intervals, and does not support a predefined hop size. Option D is wrong because a Tumbling window is a series of fixed-size, non-overlapping contiguous time windows (e.g., every 5 minutes), which cannot produce overlapping windows that slide every 1 minute.

Full explanation →

414

MCQeasy

You are developing a real-time data processing solution using Azure Stream Analytics. The input is an Azure Event Hubs stream with JSON data containing a 'timestamp' field. You need to output the average temperature per device every minute using a tumbling window. Which query should you use?

A.SELECT DeviceId, AVG(Temperature) AS AvgTemp FROM Input TIMESTAMP BY Timestamp GROUP BY DeviceId, SlidingWindow(minute, 1)

B.SELECT DeviceId, AVG(Temperature) AS AvgTemp FROM Input TIMESTAMP BY Timestamp GROUP BY DeviceId, TumblingWindow(minute, 1)

C.SELECT DeviceId, AVG(Temperature) AS AvgTemp FROM Input TIMESTAMP BY Timestamp GROUP BY DeviceId, SessionWindow(minute, 1, 1)

D.SELECT DeviceId, AVG(Temperature) AS AvgTemp FROM Input TIMESTAMP BY Timestamp GROUP BY DeviceId, HopWindow(minute, 1, 1)

AnswerB

TumblingWindow(minute, 1) produces fixed, non-overlapping one-minute windows, satisfying the per-minute aggregation requirement, while TIMESTAMP BY Timestamp makes Stream Analytics use the event's own timestamp rather than arrival time. Grouping by DeviceId alongside the window yields one average temperature per device per minute, exactly as specified.

Why this answer

A tumbling window is a fixed, non-overlapping time window that groups events into distinct time segments. Using `TumblingWindow(minute, 1)` with `TIMESTAMP BY Timestamp` ensures that the average temperature per device is computed over each one-minute interval without overlap, which matches the requirement of 'every minute'.

Exam trap

The trap here is that candidates confuse `SlidingWindow` or `HopWindow` with `TumblingWindow`, not realizing that only `TumblingWindow` produces non-overlapping, fixed-interval outputs required for a simple per-minute average.

How to eliminate wrong answers

Option A is wrong because `SlidingWindow` produces a continuous output for every event, not fixed intervals, and would not give a single average per minute. Option C is wrong because `SessionWindow` groups events based on inactivity gaps, not fixed time boundaries, and would not produce a consistent per-minute result. Option D is wrong because `HopWindow` creates overlapping windows with a hop size smaller than the window size, leading to multiple outputs per minute and not a single non-overlapping aggregation.

Full explanation →

415

MCQeasy

You need to configure encryption for an Azure SQL Database to protect data at rest. Which Azure service or feature should you enable?

A.Dynamic Data Masking

B.Always Encrypted

C.Azure Information Protection

D.Transparent Data Encryption (TDE)

AnswerD

TDE performs real-time encryption and decryption of the database, associated backups, and transaction log files at rest using a symmetric database encryption key, with no application changes required. It directly satisfies the data-at-rest protection requirement for Azure SQL Database.

Why this answer

Transparent Data Encryption (TDE) is the correct choice because it performs real-time I/O encryption and decryption of the data and log files at rest, protecting against unauthorized access to the physical storage media. TDE uses an AES-256 encryption algorithm and is fully transparent to the application, requiring no changes to the database schema or queries.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking (DDM) with encryption, thinking it protects data at rest, when in fact it only masks output and does not encrypt the underlying storage.

How to eliminate wrong answers

Option A is wrong because Dynamic Data Masking (DDM) is a data masking feature that obfuscates sensitive data in query results to unauthorized users, but it does not encrypt data at rest. Option B is wrong because Always Encrypted is a client-side encryption technology that protects sensitive data in transit and at rest by encrypting columns with keys stored on the client, but it is not a database-level encryption for all data at rest and requires application changes. Option C is wrong because Azure Information Protection (AIP) is a classification and labeling service for documents and emails, not a database encryption feature for Azure SQL Database.

Full explanation →

416

MCQmedium

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?

A.Enable Transparent Data Encryption (TDE) and enforce TLS 1.2.

B.Use Azure RBAC and firewall rules.

C.Use Always Encrypted and column-level security.

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

AnswerA

TDE performs real-time encryption and decryption of the database, log and backup files at rest, while enforced TLS 1.2 encrypts data moving between clients and the pool. Together they satisfy the stem's dual requirement to protect data both at rest and in transit.

Why this answer

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.

Full explanation →

417

MCQhard

Refer to the exhibit. You submit a Spark job in Azure Synapse Analytics using the Azure CLI. The job runs slowly during the shuffle phase. The input data is about 200 GB. Which configuration change would best improve performance for this shuffle-heavy workload?

A.Increase the number of executors to 4.

B.Change executor size to 'Large' to increase memory per executor.

C.Increase spark.sql.shuffle.partitions to 800.

D.Decrease spark.sql.shuffle.partitions to 200 to reduce overhead.

AnswerC

Shuffle partitions default to 200, which is too few for 200 GB and leaves each task handling roughly 1 GB. Raising spark.sql.shuffle.partitions to 800 increases parallelism across the shuffle stage, reducing per-task data volume and skew.

Why this answer

Spark's shuffle phase is governed by spark.sql.shuffle.partitions, which defaults to 200. With 200 GB of input, 200 partitions means roughly 1 GB per partition, causing large shuffle blocks, disk spills, and long task runtimes. Raising it to 800 creates smaller, more parallel partitions that better utilize the cluster's cores and reduce per-task memory pressure, which is the standard tuning lever for shuffle-heavy jobs.

Exam trap

DP-203 often tests the misconception that adding executors or memory fixes shuffle slowness — the real lever is partition count, and candidates must recognize that the default 200 is almost always too low for large datasets.

How to eliminate wrong answers

Option A is wrong because increasing executors to 4 without increasing partitions just adds workers that each still process oversized partitions — the parallelism bottleneck remains. Option B is wrong because a larger executor size increases memory per executor but does not address the root cause of too few partitions; it may even worsen GC pauses and reduce the number of executors that fit on a node. Option D is wrong because decreasing partitions to 200 is the default and would make each partition even larger, increasing spills and skew — the opposite of what's needed.

Full explanation →

418

MCQeasy

A logistics company uses Azure Blob Storage to store shipping manifests as block blobs. The manifests are accessed frequently for the first 30 days, then rarely accessed for the next 60 days, and after 90 days they must be retained for seven years for compliance but are almost never accessed. You need to minimize storage costs while ensuring the data remains available for compliance audits. What should you do?

A.Create a lifecycle management policy that deletes blobs after 90 days and stores them in Azure Backup for seven years.

B.Move blobs to the Archive tier after 30 days and keep them there for seven years.

C.Enable soft delete and versioning, and leave all blobs in the Hot tier for seven years.

D.Move blobs to the Cool access tier after 30 days, then to the Archive tier after 90 days.

AnswerD

The Cool tier is designed for data that is infrequently accessed but still requires rapid retrieval, which matches the 30-90 day period. The Archive tier is for data that is rarely accessed and can tolerate hours of retrieval latency, making it ideal for long-term compliance retention beyond 90 days. This tiering strategy minimizes storage costs while keeping data available, though Archive retrieval may take hours.

Why this answer

Azure Blob Storage access tiers optimize costs based on access frequency. The Cool tier is cost-effective for data accessed infrequently but still requiring quick retrieval, suitable for the 30-90 day period. The Archive tier offers the lowest storage cost for data that can tolerate hours of retrieval latency, ideal for long-term compliance retention.

Transitioning blobs through these tiers minimizes costs while ensuring availability for audits.

Exam trap

The trap here is moving data to Archive too early, which incurs early deletion penalties and makes data inaccessible for the period when it is still occasionally needed.

Full explanation →

419

MCQhard

You deploy the Azure Security Center automation shown in the exhibit. What is the purpose of this automation?

A.It configures Azure Monitor to log high-severity alerts.

B.It applies an Azure Policy to remediate high-severity alerts.

C.It sends high-severity security alerts to an Event Hub for further processing.

D.It creates incidents in Azure Sentinel for high-severity alerts.

AnswerC

The automation triggers on high-severity security alerts and routes them to an Event Hub, enabling downstream consumption by external SIEM or processing systems. This satisfies the requirement to forward alerts beyond Microsoft Defender for Cloud's native notifications for further automated processing.

Why this answer

The automation in Azure Security Center (now Microsoft Defender for Cloud) is configured to export high-severity security alerts to an Azure Event Hub. This is a built-in continuous export feature that streams alerts to an Event Hub for further processing by downstream systems like SIEM, SOAR, or custom applications. The correct option directly matches this purpose: sending high-severity alerts to an Event Hub.

Exam trap

DP-203 often tests the distinction between continuous export to Event Hub versus other integrations like Azure Monitor, Azure Policy, or Sentinel, causing candidates to confuse the purpose of each service.

How to eliminate wrong answers

Option A is wrong because Azure Monitor is used for collecting and analyzing telemetry, not for exporting Security Center alerts; the automation does not configure Azure Monitor logging. Option B is wrong because Azure Policy is used for enforcing organizational standards and compliance, not for remediating alerts; the automation does not apply policies. Option D is wrong because creating incidents in Azure Sentinel is a separate integration; the automation shown exports to Event Hub, not directly to Sentinel.

Full explanation →

420

Matchingmedium

Match each Azure Synapse Analytics component to its function.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts

Matches

Distributed query engine for relational data

Apache Spark runtime for big data processing

Data integration and orchestration

Web-based IDE for developing analytics solutions

Why these pairings

In Azure Synapse Analytics, Dedicated SQL pool provides provisioned compute for relational data, Serverless SQL pool queries data lake files on-demand, Apache Spark pool handles distributed processing with Spark, and Pipeline orchestrates workflows. Common confusions include mixing serverless and provisioned capabilities or attributing Spark functionality to SQL pools.

Full explanation →

421

MCQeasy

You are using Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. The query returns fewer rows than expected. What should you check first?

A.Ensure the external table has the correct schema definition.

B.Check that the Azure AD identity has read permissions on the storage account.

C.Check the compression codec used in the Parquet files.

D.Verify the file path and pattern in the OPENROWSET query.

AnswerD

OPENROWSET reads only the files matched by the specified path and wildcard pattern; an incorrect folder, filename or pattern silently omits files, so verifying that location first explains missing rows before investigating schema or format issues.

Why this answer

When using OPENROWSET in Azure Synapse serverless SQL pool to query Parquet files, the most common reason for fewer rows than expected is an incorrect file path or pattern. If the path or pattern is too restrictive (e.g., missing a wildcard or pointing to a subfolder instead of the root), the query will only read a subset of the files, resulting in fewer rows. This is the first thing to verify before investigating schema or permissions issues.

Exam trap

The trap here is that candidates often jump to schema or permission issues first, but the most frequent cause of missing rows in serverless SQL pool queries is an overly restrictive file path or pattern in the OPENROWSET query.

How to eliminate wrong answers

Option A is wrong because an incorrect schema definition would typically cause data type conversion errors or NULL values, not a reduction in row count; the query would still read all rows but might fail to parse them. Option B is wrong because if the Azure AD identity lacked read permissions, the query would fail entirely with an authorization error, not return fewer rows. Option C is wrong because the compression codec (e.g., snappy, gzip) does not affect the number of rows returned; Parquet files are self-describing and the serverless SQL pool automatically handles decompression regardless of codec.

Full explanation →

422

MCQmedium

Match the Azure service to its primary data processing use case. Drag each service on the left to the correct use case on the right. Services: Azure Databricks, Azure Stream Analytics, Azure Data Factory, Azure Synapse Analytics Use Cases: - Real-time event processing - Orchestration of ETL pipelines - Big data analytics with Spark - Enterprise data warehousing

A.Azure Databricks - Big data analytics with Spark

B.Azure Stream Analytics - Real-time event processing

C.Azure Data Factory - Orchestration of ETL pipelines

D.Azure Synapse Analytics - Enterprise data warehousing

AnswerA, B, C, D

Azure Databricks provides a managed Apache Spark environment, matching the "big data analytics with Spark" use case precisely. Its distributed in-memory engine handles large-scale transformation and machine learning workloads, satisfying the stem's requirement for Spark-based processing rather than orchestration, streaming, or warehousing.

Why this answer

Each Azure service maps to its canonical primary use case: Azure Databricks is the managed Apache Spark platform for big data analytics and ML; Azure Stream Analytics handles real-time event stream processing; Azure Data Factory is the cloud ETL/ELT orchestration service; and Azure Synapse Analytics is the enterprise data warehousing and analytics service. These are the standard DP-203 service-to-purpose mappings.

Exam trap

The trap is confusing overlapping services — for example, picking Synapse for Spark workloads or Databricks for orchestration — because both Synapse and Databricks support Spark, and both ADF and Synapse support pipelines.

How to eliminate wrong answers

There are no incorrect options in this matching question — all four service-to-use-case pairings (A, B, C, D) are correct by definition. Any distractor would arise only from swapping services (e.g., assigning Stream Analytics to warehousing), which the question does not do.

Full explanation →

423

MCQeasy

You need to transform data in Azure Databricks using Apache Spark. The data is stored in Delta Lake format in Azure Data Lake Storage Gen2. Which method should you use to read the data into a Spark DataFrame?

A.spark.read.parquet('abfss://container@storage.dfs.core.windows.net/path')

B.spark.read.format('delta').load('abfss://container@storage.dfs.core.windows.net/path')

C.spark.read.csv('abfss://container@storage.dfs.core.windows.net/path')

D.spark.read.json('abfss://container@storage.dfs.core.windows.net/path')

AnswerB

Using the Delta format reader lets Spark consult the Delta transaction log for schema, partitioning and file listing, rather than inferring structure from raw Parquet files. The abfss:// URI satisfies the stem's Azure Data Lake Storage Gen2 constraint, and Delta Lake's ACID guarantees are preserved on read.

Why this answer

The data is stored in Delta Lake format, which requires using the 'delta' format reader in Spark to properly read the transaction log and schema. The `spark.read.format('delta').load()` method is the standard way to read Delta tables, leveraging the Delta Lake protocol for ACID transactions and time travel capabilities.

Exam trap

The trap here is that candidates may assume Delta Lake files are just Parquet files and use `spark.read.parquet()`, missing the critical role of the Delta transaction log for consistency and ACID compliance.

How to eliminate wrong answers

Option A is wrong because `spark.read.parquet()` reads only Parquet files and ignores Delta Lake's transaction log, leading to stale or inconsistent data. Option C is wrong because `spark.read.csv()` is for CSV files, not Delta Lake format. Option D is wrong because `spark.read.json()` is for JSON files, not Delta Lake format.

Full explanation →

424

MCQhard

You have an Azure Data Lake Storage Gen2 account that contains a container named raw with millions of small JSON files, each under 1 MB. A daily Azure Data Factory pipeline reads these files and writes them to a curated container as Parquet files. You notice that the pipeline runs slowly and you want to optimize read performance. What should you do first?

A.Use a binary copy and then convert to Parquet in a downstream process.

B.Enable hierarchical namespace on the storage account.

C.Increase the degree of copy parallelism in the Azure Data Factory Copy activity.

D.Compact the small files into larger files before processing.

AnswerD

Compacting many small files into larger files (e.g., 256 MB to 1 GB) reduces the overhead of opening and reading each file, significantly improving read performance in Azure Data Factory and downstream analytics. This is a recommended best practice for data lakes, as it minimizes the number of read operations and maximizes throughput per file.

Why this answer

The primary performance bottleneck when reading millions of small files is the per-file overhead. Compacting them into larger files reduces the number of read operations and improves throughput. Other options like increasing parallelism or enabling hierarchical namespace do not address the root cause, and binary copy would not solve the small file issue for analytics.

Exam trap

The trap here is focusing on parallelism or namespace features, while overlooking that the sheer number of small files is the main performance inhibitor.

Full explanation →

425

Multi-Selectmedium

Which TWO actions can you take to optimize query performance in Azure Synapse Analytics dedicated SQL pool?

Select 2 answers

A.Use hash distribution on a low-cardinality column

B.Use round-robin distribution for fact tables

C.Use replicated tables for small dimension tables

D.Create materialized views for common aggregations

E.Increase the DWU setting after every query

AnswersC, D

Replicating small dimension tables places a full copy on every compute node, eliminating data movement during joins with large fact tables. This directly removes the shuffle overhead that dominates query time in a dedicated SQL pool's distributed architecture.

Why this answer

Option C is correct because replicated tables in a dedicated SQL pool copy small dimension tables to every compute node, eliminating data movement (shuffle) during joins with large fact tables and thereby improving query performance. Option D is correct because materialized views precompute and persist the results of common aggregations, so repeated queries against large fact tables can read the smaller pre-aggregated result set instead of rescanning and re-aggregating the base data. Option A is wrong because hash distribution should be on a high-cardinality column that distributes rows evenly; low-cardinality columns cause data skew and uneven work distribution.

Option B is wrong because round-robin distribution is best for staging or temporary tables, whereas fact tables benefit from hash distribution on a frequently joined column to minimize data movement. Option E is wrong because increasing DWU is a scaling action, not a query optimization technique, and doing it after every query is wasteful and does not address query design or data distribution issues.

Full explanation →

426

Drag & Dropmedium

Drag and drop the steps to set up Azure Data Lake Storage Gen2 hierarchical namespace for a data lake into the correct order.

Drag or tap steps into the slots.

Steps

Order

1Step 1

2Step 2

3Step 3

4Step 4

Why this order

The storage account must have hierarchical namespace enabled. Then create a container, directories, set permissions, and upload data.

Full explanation →

427

MCQhard

You are designing a data processing solution using Azure Databricks with Delta Lake. You need to ensure ACID transactions and schema enforcement. Which feature should you enable?

A.Auto Loader

B.Delta Lake format

C.Photon engine

D.Unity Catalog

AnswerB

Delta Lake format stores data as Parquet with a transaction log, providing ACID guarantees and enforcing the table schema on writes. Enabling it satisfies both requirements: atomic, isolated transactions and rejection of non-conforming records, unlike plain Parquet.

Why this answer

Delta Lake is the correct choice because it provides ACID transactions (atomicity, consistency, isolation, durability) and schema enforcement (schema-on-write) on top of cloud storage like Azure Data Lake Storage. These features are inherent to the Delta Lake format, which uses a transaction log to track changes and enforce data integrity, making it ideal for reliable data processing in Azure Databricks.

Exam trap

Microsoft often tests the distinction between features that provide data governance (Unity Catalog) versus features that provide data reliability at the storage layer (Delta Lake), leading candidates to confuse Unity Catalog's metadata management with Delta Lake's transactional guarantees.

How to eliminate wrong answers

Option A is wrong because Auto Loader is a feature for incrementally ingesting new files from cloud storage, not for providing ACID transactions or schema enforcement. Option C is wrong because the Photon engine is a high-performance vectorized query engine that accelerates query execution but does not manage ACID transactions or schema constraints. Option D is wrong because Unity Catalog is a centralized metadata and governance layer for managing data assets, permissions, and lineage, but it does not directly enforce ACID transactions or schema enforcement at the table level.

Full explanation →

428

MCQhard

A company uses Azure Stream Analytics to process real-time data from IoT devices. They need to ensure that the output to Azure Synapse Analytics is optimized for high throughput and low latency. What should they configure in the Stream Analytics job?

A.Use Azure SQL Database output instead of Azure Synapse Analytics.

B.Partition the output by a key and use a columnstore index in the target table.

C.Use a single partition for the output to simplify processing.

D.Disable batching to reduce latency.

AnswerB

Columnstore compression plus partitioning the output by a key lets Stream Analytics write in parallel batches, raising throughput and cutting latency into Synapse. This directly satisfies the stem's high-throughput, low-latency constraint by avoiding row-by-row inserts and single-writer bottlenecks.

Why this answer

Partitioning the output by a key and using a columnstore index in the target Synapse table optimizes for high throughput and low latency. Partitioning distributes the write load across multiple nodes, while columnstore indexes provide high compression and fast query performance for analytical workloads. This combination is recommended for Stream Analytics to Synapse ingestion at scale.

Exam trap

DP-203 often tests the misconception that disabling batching reduces latency, but in reality, it increases overhead and reduces throughput; batching is crucial for efficient bulk writes.

How to eliminate wrong answers

Option A is wrong because switching to Azure SQL Database does not optimize for high throughput and low latency; Azure SQL Database is a row-store OLTP database and is not designed for the high-volume analytical ingestion that Synapse handles. Option C is wrong because using a single partition creates a bottleneck and reduces parallelism, leading to higher latency and lower throughput. Option D is wrong because disabling batching increases the number of small write operations, which can increase overhead and reduce throughput; batching is essential for efficient bulk ingestion.

Full explanation →

429

Multi-Selectmedium

Which TWO components are required to set up a streaming data pipeline using Azure Synapse Analytics? (Select two.)

Select 2 answers

A.Azure Data Factory

B.Azure Event Hubs

C.Azure Analysis Services

D.Azure Blob Storage

E.Azure Synapse Pipelines (or Spark)

AnswersB, E

Azure Event Hubs provides the ingestion endpoint for high-throughput streaming data, satisfying the pipeline's requirement for a scalable event broker. Synapse Spark or Stream Analytics can then read from it for near-real-time processing. Without an ingestion source, no streaming pipeline exists, making Event Hubs a required component.

Why this answer

To set up a streaming data pipeline in Azure Synapse Analytics, you need a streaming ingestion source and a processing engine. Azure Event Hubs (Option B) is the correct ingestion service for real-time streaming data. Azure Synapse Pipelines (or Spark) (Option E) provides the processing engine to transform and analyze the streaming data.

Azure Data Factory (Option A) is primarily for batch data integration, not streaming. Azure Analysis Services (Option C) is for OLAP modeling, not streaming. Azure Blob Storage (Option D) is a storage destination, not a required component for streaming ingestion or processing.

Full explanation →

430

MCQmedium

You are monitoring an Azure Synapse Analytics dedicated SQL pool. You need to identify queries that are currently running and consuming the most resources. Which dynamic management view (DMV) should you query?

A.sys.dm_pdw_exec_sessions

B.sys.dm_pdw_nodes_exec_requests

C.sys.dm_pdw_waits

D.sys.dm_pdw_exec_requests

AnswerD

sys.dm_pdw_exec_requests returns information about all requests currently executing or recently completed in the dedicated SQL pool, including resource usage metrics like CPU and memory. It is the primary DMV for monitoring active queries and identifying resource-intensive operations. By querying this view, you can see which queries are consuming the most resources and take appropriate action.

Why this answer

sys.dm_pdw_exec_requests is the correct DMV for monitoring currently running queries and their resource consumption in a dedicated SQL pool. It includes columns like cpu_time, total_elapsed_time, and memory_usage, allowing you to identify resource-heavy queries. The other DMVs either provide node-level detail, session information, or wait statistics, which are less direct for this specific monitoring task.

Exam trap

The trap here is confusing session-level, node-level, or wait-level DMVs with request-level DMVs that directly report resource usage.

Full explanation →

431

MCQmedium

A company uses Azure Synapse Analytics dedicated SQL pool. They notice that queries against a large fact table are running slower over time. The table is hash-distributed on a date key and has a clustered columnstore index. Which action should you take to improve query performance?

A.Add a non-clustered index on frequently filtered columns.

B.Change the distribution column to a column with higher cardinality.

C.Change the distribution to round-robin.

D.Rebuild the clustered columnstore index.

AnswerD

Repeated DML creates open rowgroups and fragmented delta stores in the clustered columnstore index, degrading scan efficiency. Rebuilding reorganises data into compressed rowgroups, restoring columnstore compression and eliminating the rowgroup fragmentation that slows queries against the large fact table.

Why this answer

Over time, columnstore indexes can become fragmented due to insert, update, and delete operations, leading to compressed row groups that are not optimally sized or have deleted records. Rebuilding the clustered columnstore index reorganizes the data into fully compressed row groups, removes deleted rows, and restores the high compression and segment elimination that columnstore indexes rely on for fast query performance.

Exam trap

The trap here is that candidates may assume performance degradation is always due to data skew or distribution choice, overlooking the common real-world issue of columnstore index fragmentation from ongoing DML operations.

How to eliminate wrong answers

Option A is wrong because adding a non-clustered index on frequently filtered columns would introduce additional index maintenance overhead and is unlikely to outperform the existing columnstore index for large fact tables; columnstore indexes already excel at scanning and filtering large datasets. Option B is wrong because changing the distribution column to one with higher cardinality does not address the root cause of performance degradation over time, which is index fragmentation, not data skew or distribution inefficiency. Option C is wrong because changing the distribution to round-robin would eliminate data locality for joins and aggregations, likely worsening query performance, and does not resolve the fragmentation issue.

Full explanation →

432

MCQhard

A company uses Azure Synapse Analytics serverless SQL pool to query data in ADLS Gen2. Users report that queries against Parquet files are slow. What should you recommend to improve query performance?

A.Create external tables with statistics on relevant columns.

B.Create clustered columnstore indexes on the external tables.

C.Convert the Parquet files to CSV format for faster reads.

D.Partition the data into many small files.

AnswerA

Creating external tables with statistics on relevant columns lets the serverless SQL pool use metadata to build better execution plans, pruning row groups and reducing data scanned. This directly addresses the slow Parquet query constraint, since statistics on join and filter columns cut the bytes read per query.

Why this answer

In Azure Synapse serverless SQL pool, external tables do not automatically have statistics. Without statistics, the query optimizer cannot generate efficient execution plans, leading to poor performance on Parquet files. Creating statistics on relevant columns enables the optimizer to estimate cardinality and choose better join and filter strategies, significantly improving query speed.

Exam trap

The trap here is that candidates confuse external table capabilities with dedicated SQL pool features, assuming that indexes like columnstore can be applied to external tables, or that file format changes (CSV) or file count adjustments are the primary performance levers, when in fact statistics are the critical missing piece for serverless SQL pool optimization.

How to eliminate wrong answers

Option B is wrong because clustered columnstore indexes are not supported on external tables in serverless SQL pool; they are only applicable to tables in dedicated SQL pools. Option C is wrong because CSV format is slower than Parquet for analytical queries due to lack of compression, columnar storage, and predicate pushdown; converting to CSV would degrade performance. Option D is wrong because partitioning data into many small files increases metadata overhead and file open operations, which slows down queries in serverless SQL pool; optimal performance is achieved with a moderate number of reasonably sized files.

Full explanation →

433

MCQhard

You are designing a data storage solution for a global retail company that uses Azure Synapse Analytics dedicated SQL pool. The fact table is partitioned by date and contains 10 years of sales data. You need to implement a rolling window that keeps only the most recent 3 years of data while loading new daily data with minimal impact on concurrent queries. What should you do?

A.Use PolyBase to load new data directly into the main table and truncate the oldest partition.

B.Use partition switching to load new data into a staging table, switch it into the main table, and switch out the oldest partition to a staging table for deletion.

C.Use CREATE TABLE AS SELECT (CTAS) to rebuild the entire fact table with only the most recent 3 years of data each day.

D.Use DELETE statements to remove data older than 3 years and INSERT statements to add new daily data.

AnswerB

Partition switching is a metadata operation that moves a partition from one table to another almost instantaneously. By loading new data into a staging table and then switching it in, you avoid expensive data movement and minimize impact on concurrent queries. Switching out the oldest partition to a staging table and then dropping it efficiently removes old data. This approach is the recommended method for rolling window scenarios in dedicated SQL pools.

Why this answer

Partition switching is the most efficient way to implement a rolling window in a dedicated SQL pool. It allows new data to be loaded into a staging table and then switched into the main table as a metadata operation, and the oldest partition can be switched out and dropped. This minimizes data movement and lock contention, preserving performance for concurrent queries.

Exam trap

The trap here is assuming that DELETE or CTAS are acceptable for daily rolling windows; they cause heavy data movement and locking, whereas partition switching is a metadata-only operation.

Full explanation →

434

MCQmedium

You have an Azure Data Lake Storage Gen2 account that stores parquet files. You need to ensure that files containing personally identifiable information (PII) are automatically classified and tagged. Which Azure service should you integrate?

A.Azure Policy

B.Microsoft Sentinel

C.Microsoft Defender for Cloud

D.Microsoft Purview

AnswerD

Microsoft Purview provides automated data classification and sensitivity labelling across data sources, including Azure Data Lake Storage Gen2. Its scanning engine detects PII using built-in classification rules and applies tags automatically, satisfying the requirement to classify and tag files without manual intervention.

Why this answer

Microsoft Purview provides automated data classification and labeling for Azure Storage, including Azure Data Lake Storage Gen2. Option A is wrong because Azure Policy enforces rules but does not classify content. Option B is wrong because Microsoft Sentinel is a SIEM, not for classification.

Option C is wrong because Microsoft Defender for Cloud is for security posture, not data classification.

Full explanation →

435

MCQmedium

You are designing a solution to store large amounts of log data that is written once and accessed rarely. The data must be retained for 7 years for compliance. After 30 days, the data should be moved to a lower-cost storage tier. After 1 year, the data should be archived. Which Azure Storage lifecycle management policy should you implement for an Azure Data Lake Storage Gen2 account?

A.Transition to cool tier after 30 days; delete after 7 years.

B.Transition to cool tier after 30 days; transition to archive tier after 365 days; delete after 2555 days (7 years).

C.Transition to archive tier after 30 days; delete after 7 years.

D.Transition to cool tier after 30 days; transition to cool tier again after 365 days.

AnswerB

Lifecycle management rules apply tier transitions and deletion by last-modified age, so cool at 30 days, archive at 365 days and deletion at 2555 days exactly match the stated retention and cost requirements for the rarely accessed log data.

Why this answer

It aligns with the specified lifecycle requirements: transition to cool tier after 30 days for cost savings, transition to archive tier after 365 days for long-term retention, and delete after 2555 days (7 years) for compliance. Azure Data Lake Storage Gen2 supports lifecycle management policies that automate tier transitions and deletion based on age, ensuring data is moved to lower-cost storage as access patterns change.

Exam trap

The trap here is that candidates may confuse the required tiering order (cool then archive) with direct archiving after 30 days (Option C) or fail to include a deletion rule (Option D), missing the 7-year compliance requirement.

How to eliminate wrong answers

Option A is wrong because it deletes the data after 7 years but does not include a transition to the archive tier after 1 year, which is required by the compliance policy to archive data after 365 days. Option C is wrong because it transitions to archive tier after only 30 days, which violates the requirement to keep data in a lower-cost tier (cool) for the first year before archiving. Option D is wrong because it transitions to cool tier again after 365 days, which does not archive the data as required, and it lacks a deletion rule for the 7-year retention period.

Full explanation →

436

Multi-Selecthard

You are designing a data lake architecture using Azure Data Lake Storage Gen2. You need to optimize query performance for Azure Synapse Analytics serverless SQL. Which three design considerations should you follow? (Choose three.)

Select 3 answers

A.Store data in Parquet format

B.Partition files by date to enable partition elimination

C.Compress files using snappy or gzip

D.Use many small files (under 64 MB) to increase parallelism

E.Store data in nested folder structures for better organization

AnswersA, B, C

Why this answer

Parquet is a columnar storage format that reduces I/O by reading only the columns needed for a query, which significantly improves performance in Azure Synapse serverless SQL. It also supports efficient compression and encoding schemes, making it ideal for analytical workloads on Azure Data Lake Storage Gen2.

Exam trap

The trap here is that candidates often confuse file size optimization with parallelism, assuming smaller files increase parallelism, but in serverless SQL, too many small files cause excessive metadata requests and reduce throughput, while larger files enable better batch processing.

Why the other options are wrong

Small files cause overhead; larger files (128 MB+) are recommended.

Deeply nested folders increase file listing time, impacting performance.

Full explanation →

437

Multi-Selecteasy

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

Select 2 answers

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

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

C.Store connection strings and secrets in Azure Key Vault and reference them via linked services.

D.Enable Managed Virtual Network (VNet) to isolate data flows.

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

AnswersC, D

Azure Key Vault centralises secret storage, and linked services retrieve credentials at runtime instead of embedding them in pipeline definitions. This satisfies the stem's security requirement by removing plaintext connection strings from artefacts, notebooks and activity configurations.

Why this answer

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.

Full explanation →

438

MCQeasy

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?

A.Configure Conditional Access policies to require MFA for database access.

B.Enable 'Azure AD-only authentication' in the Azure SQL Database server settings and remove all SQL Server authenticated logins.

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

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

AnswerB

Disables SQL authentication and enforces Entra ID.

Why this answer

Enabling 'Azure AD-only authentication' in the Azure SQL Database server settings disables SQL authentication entirely, forcing all connections to use Microsoft Entra ID (formerly Azure AD). Removing all SQL Server authenticated logins further reduces the attack surface by eliminating those credentials. This meets the requirements of using Entra ID authentication and disabling SQL authentication.

Exam trap

DP-203 often tests the difference between authentication and authorization, and candidates may confuse Azure RBAC (authorization) with Entra ID authentication, leading them to choose RBAC options.

How to eliminate wrong answers

Option A is wrong because Conditional Access policies can enforce MFA but do not disable SQL authentication; SQL logins would still be possible. Option C is wrong because creating a firewall rule and enabling SQL authentication does not meet the requirement to use Entra ID authentication and disable SQL authentication; it actually allows SQL authentication. Option D is wrong because Azure RBAC roles manage access to Azure resources, not database-level authentication; they do not control SQL authentication or Entra ID authentication for database connections.

Full explanation →

439

MCQeasy

A data engineer needs to store semi-structured JSON logs from multiple sources in Azure. The logs must be queryable using T-SQL and support schema-on-read. Which Azure service should be used?

A.Azure Synapse serverless SQL pool with JSON files in ADLS Gen2.

B.Azure Data Factory mapping data flows.

C.Azure Cosmos DB Core (SQL) API.

D.Azure SQL Database with JSON columns.

AnswerA

Synapse serverless SQL pool queries JSON files in ADLS Gen2 using T-SQL with OPENROWSET, applying schema-on-read so no ingestion or schema definition is required. This satisfies both the T-SQL query requirement and the schema-on-read constraint for semi-structured logs.

Why this answer

Azure Synapse serverless SQL pool can query JSON files stored in ADLS Gen2 using T-SQL, supporting schema-on-read by inferring the schema from the file content at query time. This makes it ideal for semi-structured logs that need to be queried without predefined schema.

Exam trap

The trap here is that candidates often confuse schema-on-read with schema-on-write, picking Azure SQL Database or Cosmos DB because they support JSON, but those require predefined schemas or containers, failing the schema-on-read requirement.

How to eliminate wrong answers

Option B is wrong because Azure Data Factory mapping data flows are designed for ETL/ELT transformations, not for direct T-SQL querying of data at rest. Option C is wrong because Azure Cosmos DB Core (SQL) API stores data as JSON but does not support schema-on-read; it requires a defined container schema and uses its own SQL dialect, not standard T-SQL. Option D is wrong because Azure SQL Database with JSON columns requires a predefined table schema and does not support schema-on-read for external files; it stores JSON in relational columns, not as files.

Full explanation →

440

MCQeasy

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?

A.Microsoft Sentinel

B.Azure Monitor

C.Azure Service Health

D.Azure Log Analytics

AnswerB

Azure Monitor ingests Azure Data Factory pipeline run metrics and supports metric alerts with action groups, which trigger email notifications when a run fails. This directly satisfies the requirement to alert on pipeline run failures without custom code or polling.

Why this answer

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.

Full explanation →

441

MCQeasy

You are using Azure Synapse Analytics to process streaming data from Azure Event Hubs. The data must be written to a Delta Lake table in ADLS Gen2 with exactly-once semantics. Which processing engine should you use?

A.Azure Databricks with Structured Streaming

B.Azure Synapse serverless SQL pool

C.Azure Synapse Pipeline with Mapping Data Flow

D.Azure Stream Analytics

AnswerA

Azure Databricks Structured Streaming natively supports Delta Lake sinks with idempotent writes and checkpointing, delivering exactly-once semantics when consuming Event Hubs. This satisfies the stem's exactly-once requirement, which plain Spark or Synapse streaming cannot guarantee without additional transactional handling.

Why this answer

Azure Databricks with Structured Streaming is the correct choice because it natively supports exactly-once semantics when writing to Delta Lake from Event Hubs. Structured Streaming uses checkpointing and a write-ahead log to ensure each record is processed exactly once, even in the face of failures. Azure Databricks runs on Spark, which integrates seamlessly with both Event Hubs (via the Event Hubs connector) and Delta Lake (as a sink).

Other options are either batch-oriented or lack the necessary transactional guarantees for exactly-once delivery to Delta Lake.

Exam trap

Candidates often assume that Azure Synapse Pipeline with Mapping Data Flow is suitable for real-time streaming because it can handle incremental data loads, but it is actually a batch transformation tool. The correct streaming engine for exactly-once semantics with Delta Lake is Azure Databricks Structured Streaming.

How to eliminate wrong answers

Option A is wrong because Azure Databricks with Structured Streaming can write to Delta Lake with exactly-once semantics, but it is not a native Azure Synapse Analytics component; the question specifies using Azure Synapse Analytics, making Databricks an external service. Option B is wrong because Azure Synapse serverless SQL pool is designed for on-demand querying of data in data lakes, not for processing streaming data or writing to Delta Lake tables with exactly-once semantics. Option D is wrong because Azure Stream Analytics does not natively support Delta Lake as an output sink; it writes to Azure Blob Storage, ADLS Gen2, or Event Hubs in formats like Parquet or Avro, but lacks the transactional capabilities required for exactly-once semantics in Delta Lake.

Full explanation →

442

MCQeasy

A healthcare organization needs to store electronic health records (EHR) in a format that supports schema flexibility and complex nested data. The solution must allow fast queries by patient ID and enable analytics with Azure Synapse. Which data store should you choose?

A.Azure Table Storage

B.Azure Data Lake Storage Gen2 with files in JSON format

C.Azure Cosmos DB with analytical store enabled

D.Azure SQL Database with JSON columns

AnswerC

Azure Cosmos DB with analytical store enabled satisfies the schema-flexibility and nested-data requirements through its schema-agnostic JSON document model, while partitioning on patient ID delivers fast point reads. The analytical store provides columnar, Synapse-linked querying without impacting transactional throughput, meeting the analytics constraint.

Why this answer

Azure Cosmos DB with analytical store enabled is the correct choice because it provides schema flexibility for complex nested EHR data, supports fast point reads by patient ID via its indexed partition key, and the analytical store enables efficient analytics with Azure Synapse through the Synapse Link feature, which automatically synchronizes operational data into a columnar format optimized for large-scale queries.

Exam trap

The trap here is that candidates often choose Azure SQL Database with JSON columns (Option D) because they assume relational databases can handle JSON, but they overlook the requirement for schema flexibility and native analytical store integration, which Cosmos DB with analytical store uniquely provides for hybrid transactional/analytical processing (HTAP) workloads.

How to eliminate wrong answers

Option A is wrong because Azure Table Storage is a key-value store that does not support complex nested data structures or schema flexibility for hierarchical EHR records, and it lacks native integration with Azure Synapse for analytics. Option B is wrong because while Azure Data Lake Storage Gen2 with JSON files can store nested data, it does not provide fast point queries by patient ID without additional indexing or processing, and it requires separate ETL for analytics rather than real-time analytical store access. Option D is wrong because Azure SQL Database with JSON columns imposes a fixed relational schema and does not offer the same level of schema flexibility as a NoSQL document store; JSON columns also complicate indexing and nested query performance, and it lacks a built-in analytical store for seamless Synapse integration.

Full explanation →

443

MCQeasy

You need to monitor the performance of an Azure Synapse Analytics dedicated SQL pool. Which DMV should you query to find queries that are currently running and their execution status?

A.sys.dm_pdw_nodes

B.sys.dm_pdw_request_steps

C.sys.dm_pdw_errors

D.sys.dm_pdw_exec_requests

AnswerD

sys.dm_pdw_exec_requests returns one row per request with a status column showing Running, Completed, or Failed, plus submit and start times. It satisfies the requirement to identify currently executing queries and their execution status in a dedicated SQL pool.

Why this answer

The correct DMV is sys.dm_pdw_exec_requests, which provides information about all requests currently executing or recently completed on the dedicated SQL pool. It includes status, submit time, and other details, making it the primary view for monitoring active queries. This DMV is specifically designed for tracking request-level execution.

Exam trap

DP-203 often tests the distinction between DMVs for different purposes; candidates may confuse request-level with step-level or error-level views, but exec_requests is the go-to for current queries.

How to eliminate wrong answers

Option A is wrong because sys.dm_pdw_nodes provides information about the compute nodes in the appliance, not about queries. Option B is wrong because sys.dm_pdw_request_steps shows the individual steps of a distributed query, but it does not give the overall status of a request; it's used for deeper analysis of a specific request. Option C is wrong because sys.dm_pdw_errors returns error information, not running queries.

Full explanation →

444

Multi-Selecteasy

Which TWO features of Azure Databricks help manage data governance and security for sensitive data?

Select 2 answers

A.Structured Streaming

B.Secret Scopes

C.Auto Loader

D.Delta Live Tables

E.Unity Catalog

AnswersB, E

Secret Scopes store credentials in Databricks-backed or Microsoft Entra ID-backed vaults, then expose them through redacted references in notebooks and jobs. This prevents hard-coded secrets in code, directly satisfying the governance requirement to protect sensitive authentication material used by analytics workloads.

Why this answer

Secret Scopes (B) allow secure storage and referencing of sensitive credentials (e.g., API keys, database passwords) in Azure Databricks, preventing hardcoding in notebooks. Unity Catalog (E) provides fine-grained access control, data lineage, and centralized metadata management across workspaces, enabling governance of sensitive data through policies and auditing.

Exam trap

The trap here is that candidates confuse data processing features (Structured Streaming, Auto Loader, Delta Live Tables) with governance/security tools, because all are part of the Databricks ecosystem but serve fundamentally different purposes.

Full explanation →

445

MCQeasy

Your company uses Azure Blob Storage to store backups. You need to ensure that data is encrypted at rest using a customer-managed key stored in Azure Key Vault. Which feature should you enable?

A.Azure Purview

B.Azure Disk Encryption

C.Azure Storage Service Encryption with customer-managed keys

D.Azure Information Protection

AnswerC

Storage Service Encryption with customer-managed keys wraps the account's data encryption key with a Key Vault key, so Microsoft cannot decrypt the backups. This satisfies the requirement for encryption at rest under your own key control, unlike platform-managed keys.

Why this answer

Azure Storage Service Encryption (SSE) encrypts data at rest and supports customer-managed keys stored in Azure Key Vault. Option A is incorrect because Azure Purview is a data governance service, not for encryption. Option B is incorrect because Azure Disk Encryption is used for virtual machine disks, not Blob Storage.

Option D is incorrect because Azure Information Protection is for classification and labeling. Therefore, option C is correct.

Full explanation →

446

MCQmedium

You are building a streaming pipeline in Azure Stream Analytics that reads JSON events from an Azure Event Hub and writes to Azure Synapse Analytics. The events include a nested array of sensor readings. You need to flatten this array so each reading becomes a separate row. Which Stream Analytics feature should you use?

A.The TIMESTAMP BY clause with a partition key

B.A JavaScript user-defined function that iterates over the array and emits multiple rows

C.CROSS APPLY with the ARRAY elements

D.A tumbling window with a GROUP BY on the sensor ID

AnswerC

CROSS APPLY is a Stream Analytics query language construct that expands an array into multiple rows, producing a row for each element in the array. This is the correct way to flatten nested arrays in Stream Analytics. It works with the GetArrayElements function to unnest the array and can be combined with a SELECT to project the individual sensor readings.

Why this answer

CROSS APPLY is designed to unnest arrays in Stream Analytics queries. It takes an array expression and returns a row for each element, allowing downstream processing of individual sensor readings. The other options either aggregate, add metadata, or cannot emit multiple rows.

Thus, CROSS APPLY is the only feature that meets the flattening requirement.

Exam trap

The trap here is assuming that a JavaScript user-defined function can output multiple rows, but it cannot; only CROSS APPLY or GetArrayElements can unnest arrays.

Full explanation →

447

MCQmedium

Refer to the exhibit. A user with Storage Blob Data Reader role on the container rawdata cannot list files under /2023/07/. What is the most likely reason?

A.The directory ACL does not grant 'execute' permission to the user

B.The user does not have Storage Blob Data Contributor role

C.The user is not the owner of the directory

D.The container name is misspelled

AnswerA

Listing a directory requires execute permission on every parent path, not just read on the blobs. Without execute on the directory ACL, the user cannot traverse /2023/07/, so enumeration fails even though Storage Blob Data Reader grants read access to blob data.

Why this answer

In Azure Data Lake Storage Gen2, listing files in a directory requires both read (r) and execute (x) permissions on the directory itself. The Storage Blob Data Reader role grants read access to blob data but does not automatically grant the execute permission on directory ACLs. Without execute permission on the /2023/07/ directory, the user cannot traverse or list its contents, even though they can read blobs they know the path to.

Exam trap

The trap here is that candidates assume the Storage Blob Data Reader role is sufficient for all read operations, but Azure Data Lake Storage Gen2 requires explicit ACL execute permission on directories for listing and traversal, which is a common point of confusion between flat blob storage and hierarchical namespace storage.

How to eliminate wrong answers

Option B is wrong because Storage Blob Data Contributor role is not required for listing files; the issue is specifically the missing execute permission on the directory ACL, not the role level. Option C is wrong because ownership of the directory is not a prerequisite for listing its contents; ACLs (not ownership) control access to list and traverse. Option D is wrong because the container name being misspelled would cause a different error (e.g., container not found), not a permission-denied error when attempting to list files.

Full explanation →

448

MCQhard

You have a production pipeline in Azure Data Factory that copies data from an on-premises SQL Server to Azure Blob Storage using a self-hosted integration runtime. The pipeline fails intermittently with a 'Connection closed' error. The data volume is 50 GB per run. What should you first troubleshoot to resolve this issue?

A.Increase the memory and CPU resources on the self-hosted integration runtime machine and check network stability.

B.Increase the 'connection timeout' setting in the linked service to 30 minutes.

C.Change the copy activity to use staged copy with Azure Blob Storage as an intermediate store.

D.Disable fault tolerance in the copy activity to improve performance.

AnswerA

Intermittent 'Connection closed' errors on a self-hosted integration runtime copying 50 GB typically stem from resource exhaustion or unstable network throughput on the runtime host. Increasing CPU and memory and verifying network stability addresses the constrained capacity causing dropped connections before changing pipeline configuration.

Why this answer

The 'Connection closed' error in a self-hosted integration runtime (SHIR) during large 50 GB transfers typically stems from resource exhaustion or unstable network connectivity on the SHIR host machine. When the SHIR runs out of memory or CPU, or when the network drops packets during long-running transfers, the TCP connection to the on-premises SQL Server is terminated mid-copy. Increasing memory/CPU and verifying network stability directly addresses the root cause of intermittent connection drops.

Exam trap

DP-203 often tests the misconception that increasing timeout values or changing copy modes fixes connection errors, when the real issue is usually SHIR resource exhaustion or network reliability.

How to eliminate wrong answers

Option B is wrong because increasing the connection timeout only helps when the initial connection cannot be established in time; it does not prevent an already-established connection from being closed mid-transfer due to resource or network issues. Option C is wrong because staged copy is a performance optimization for large data movement through a staging store, not a fix for connection instability — the same connection drop would still occur. Option D is wrong because disabling fault tolerance reduces the copy activity's ability to skip incompatible rows; it does nothing to resolve connection closures and may actually reduce reliability.

Full explanation →

449

MCQhard

You are optimizing an Azure Synapse Analytics dedicated SQL pool that processes large fact tables. You need to improve query performance for a common join between a fact table and a dimension table. The fact table is distributed using hash distribution on a column that is not the join key. The dimension table is small and replicated. You want to minimize data movement during the join. What should you do?

A.Add a clustered columnstore index on the join key column.

B.Change the distribution of the fact table to hash distribute on the join key.

C.Change the distribution of the fact table to round-robin.

D.Create a materialized view that pre-joins the fact and dimension tables.

AnswerB

Hash distributing the fact table on the join key aligns the data so that rows with the same join key values are co-located. This eliminates the need for data movement during the join with the dimension table, which is already replicated. This is the most effective way to minimize data movement and improve query performance.

Why this answer

To minimize data movement during a join in a dedicated SQL pool, the distribution key of the large fact table should match the join key. This co-locates matching rows on the same distribution, avoiding shuffling. Round-robin distribution scatters data, materialized views do not change distribution, and columnstore indexes improve storage and scan efficiency but not data movement.

Exam trap

The trap here is assuming that any performance optimization like indexing or materialized views will reduce data movement, when distribution alignment is the key factor.

Full explanation →

450

MCQhard

Your team is running a critical Azure Stream Analytics job that writes results to Azure SQL Database. Recently, the job has been failing with high latency and occasional data loss. You need to monitor the job's performance and set up alerts for when the watermark delay exceeds a threshold. What should you use?

A.Application Insights SDK integration in the job.

B.Azure Log Analytics workspace connected to the job diagnostics logs.

C.Azure Monitor metrics for the Stream Analytics job.

D.Azure Data Explorer for querying job performance data.

AnswerC

Azure Monitor exposes the Stream Analytics watermark delay metric, which directly quantifies the latency causing failures. Alert rules on that metric trigger when the threshold is breached, satisfying the monitoring and alerting requirement without custom instrumentation.

Why this answer

Azure Monitor metrics for the Stream Analytics job provide built-in performance metrics such as Watermark Delay, Input Events, Output Events, and Runtime Errors, and you can create alert rules on these metrics when the watermark delay exceeds a threshold. This is the native, recommended way to monitor Stream Analytics job performance and set up alerts.

Exam trap

DP-203 often tests the difference between Azure Monitor metrics and Log Analytics for Stream Analytics; candidates who assume diagnostic logs are required for alerting choose Log Analytics instead of the native metric-based alerting approach.

How to eliminate wrong answers

Option A is wrong because Application Insights SDK integration is not a native Stream Analytics monitoring mechanism; Stream Analytics does not support embedding the Application Insights SDK directly in the job. Option B is wrong because while Log Analytics can ingest diagnostic logs, the watermark delay is exposed as a metric, and Azure Monitor metrics with alert rules is the direct and recommended approach for threshold-based alerting. Option D is wrong because Azure Data Explorer is a separate analytics service for querying large datasets; it is not the built-in monitoring and alerting tool for Stream Analytics job metrics.

Full explanation →

Microsoft Azure Data Engineer Associate DP-203 (DP-203) — Questions 376–450