CCNA Develop data processing Questions — Page 3 of 3

151

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.

Practice this question →

152

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.

Practice this question →

153

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.

Practice this question →

154

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.

Practice this question →

155

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.

Practice this question →

156

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.

Practice this question →

157

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.

Practice this question →

158

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.

Practice this question →

159

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.

Practice this question →

160

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.

Practice this question →

161

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.

Practice this question →

162

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.

Practice this question →

163

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.

Practice this question →

164

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.

Practice this question →

165

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.

Practice this question →

166

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.

Practice this question →

167

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.

Practice this question →

168

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.

Practice this question →

169

MCQmedium

You are troubleshooting a failed Azure Synapse Pipeline execution. The pipeline uses a Copy activity to load data from an on-premises SQL Server to Azure Data Lake Storage Gen2. The error indicates a 'Connection timeout' to the on-premises source. The Integration Runtime is Self-Hosted and has been running successfully for months. What is the most likely cause?

A.The SQL Server authentication credentials have expired.

B.The Self-Hosted Integration Runtime is not installed.

C.The on-premises network configuration has changed.

D.The Azure Storage account firewall is blocking access.

AnswerC

A self-hosted integration runtime that worked for months rules out credentials and installation faults. A connection timeout to the on-premises SQL Server therefore points to changed network configuration, such as firewall rules, routing, or proxy settings, blocking the runtime's outbound path to the source.

Why this answer

A 'Connection timeout' error when using a Self-Hosted Integration Runtime (SHIR) that has been running successfully for months indicates a network-level issue rather than an authentication or configuration problem. The most likely cause is a change in the on-premises network configuration (e.g., firewall rules, proxy settings, or DNS changes) that prevents the SHIR from reaching the SQL Server on the specified port (typically TCP 1433). Since the SHIR is already installed and was working, the timeout points to a connectivity break, not a credential or installation issue.

Exam trap

The trap here is that candidates confuse authentication errors (which produce specific error messages like 'Login failed') with connectivity errors (which produce timeouts), leading them to incorrectly select credential-related options when the symptom is clearly a network timeout.

How to eliminate wrong answers

Option A is wrong because expired SQL Server authentication credentials would result in an 'Authentication failed' or 'Login failed' error, not a 'Connection timeout'. Option B is wrong because if the Self-Hosted Integration Runtime were not installed, the pipeline would fail with a different error (e.g., 'Unable to connect to Integration Runtime' or 'Integration Runtime is offline'), not a timeout. Option D is wrong because the Azure Storage account firewall controls inbound access to the storage endpoint, not outbound connectivity from the SHIR to the on-premises SQL Server; a storage firewall issue would produce an error related to storage access (e.g., 403 Forbidden), not a timeout to the source.

Practice this question →

170

MCQhard

You have a Data Factory pipeline that runs a U-SQL script in Azure Data Lake Analytics. The script processes terabytes of data and outputs to a CSV file. The pipeline is failing with the error: 'The job failed with UserError: Script execution failed.' You need to troubleshoot the issue. Which approach should you take first?

A.Change the output format to Parquet to reduce file size.

B.Review the job logs in Azure Data Lake Analytics to identify the specific script error.

C.Migrate the U-SQL script to Azure Synapse Spark pool.

D.Increase the degree of parallelism for the U-SQL job.

AnswerB

Azure Data Lake Analytics records detailed job logs, including the vertex and script error behind UserError failures. Reviewing them first pinpoints the faulty U-SQL statement, avoiding guesswork before changing the terabytes-scale script or pipeline configuration.

Why this answer

The most effective first step is to examine the detailed job logs in Data Lake Analytics, which contain the actual script error. Increasing parallelism or changing output format may not address the underlying script error. Moving to Azure Synapse is a larger architectural change.

Practice this question →

171

MCQhard

You are designing a data processing solution for a healthcare organization. The solution must process streaming data from IoT devices and store it in Azure Data Lake Storage Gen2. The data must be available for both real-time dashboards and historical analysis. You need to minimize operational overhead. What should you do?

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

B.Use Azure Stream Analytics to output to both Power BI and Data Lake Storage

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

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

AnswerB

Stream Analytics performs the in-flight aggregation and routes one query to two sinks: Power BI for live dashboards and Data Lake Storage Gen2 for historical retention. This dual output satisfies both consumption patterns from a single managed, serverless job, minimising the operational overhead the stem demands.

Why this answer

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

Exam trap

The trap here is that candidates often overcomplicate the solution by choosing Databricks (Option C) for its flexibility, overlooking that Stream Analytics provides a simpler, fully managed approach with native dual-output support that minimizes operational overhead.

How to eliminate wrong answers

Option A is wrong because Azure Functions writing directly to Data Lake Storage introduces additional latency and operational complexity (e.g., managing function scaling, retries) and does not natively support real-time streaming output to Power BI without extra integration. Option C is wrong because Azure Databricks with Structured Streaming requires managing a cluster and incurs higher operational overhead compared to a fully managed service like Stream Analytics, and Power BI DirectQuery from Data Lake Storage is not optimized for real-time dashboards due to latency. Option D is wrong because Event Hubs Capture writes data in Avro format to Data Lake Storage in batches (e.g., every 5 minutes or 100 MB), which introduces latency unsuitable for real-time dashboards, and Power BI directly querying Event Hubs is not supported without an intermediary like Stream Analytics.

Practice this question →

172

MCQmedium

Your organization uses Azure Synapse Analytics. You need to design a data transformation pipeline that processes streaming data from Azure Event Hubs, performs aggregations over a 5-minute tumbling window, and loads the results into a dedicated SQL pool table. Which Azure service should you use to implement the streaming transformation?

A.Azure Stream Analytics

B.Azure Data Factory

C.Apache Spark for Azure Synapse

D.Azure Functions

AnswerA

Azure Stream Analytics natively consumes Event Hubs streams and supports tumbling windows, enabling the required five-minute aggregations before writing results to the dedicated SQL pool. This satisfies the stem's streaming-transformation requirement without building custom windowing logic in Spark or Functions.

Why this answer

Azure Stream Analytics is the appropriate service for real-time stream processing with windowed aggregations. Option B is wrong because Azure Data Factory is for batch orchestration. Option C is wrong because Spark Structured Streaming is for big data workloads but less integrated with SQL pools.

Option D is wrong because Azure Functions is not designed for streaming windowed aggregations.

Practice this question →

173

MCQmedium

You are building an Azure Stream Analytics job that ingests telemetry from Azure Event Hubs and writes aggregated results to an Azure Synapse Analytics dedicated SQL pool. The job must compute a five-minute tumbling window average per device and tolerate events that arrive up to three minutes late. During testing, you observe that events arriving after the window closes are silently dropped. You need to ensure late events are included in the correct window result. What should you configure in the Stream Analytics job?

A.Set the event ordering policy's Out-of-order events tolerance to 00:03:00.

B.Increase the streaming units allocated to the job to at least six.

C.Set the Event Hubs consumer group's message retention to seven days.

D.Configure the late arrival tolerance on the temporal window to 00:03:00.

AnswerD

Late arrival tolerance extends how long a temporal window such as a tumbling window stays open for events whose timestamp falls inside the window but that physically arrive afterward. Setting it to three minutes lets the job include those delayed telemetry events in the correct five-minute window aggregate instead of discarding them.

Why this answer

Tumbling windows emit once the window period elapses, and by default any event whose timestamp falls in that period but arrives after emission is discarded. The late arrival tolerance setting explicitly extends the window's acceptance period, so a three-minute value preserves the delayed device telemetry and aggregates it into the correct window. Scaling or retention settings do not affect timestamp-based window membership.

Exam trap

The trap here is confusing out-of-order tolerance, which reorders events relative to one another, with late arrival tolerance, which keeps a temporal window open for events that arrive after the window boundary.

Practice this question →

174

MCQmedium

You are designing a data lakehouse architecture in Azure using Delta Lake. The solution needs to process batch and streaming data from multiple sources, including IoT devices and CRM systems. You need to ensure data quality by enforcing schema validation and handling schema evolution. You also need to provide a unified catalog for querying. Which service should you use?

A.Azure Purview

B.Azure Data Lake Storage Gen2

C.Azure Synapse Analytics serverless SQL pool

D.Azure Databricks Unity Catalog

AnswerD

Unity Catalog provides centralised governance, fine-grained access control and a unified metastore across workspaces, while Delta Lake enforces schema validation and supports schema evolution. This satisfies the requirement for a unified catalog spanning batch and streaming sources such as IoT and CRM data.

Why this answer

Azure Databricks Unity Catalog is the only option that provides a unified governance layer across workspaces with built-in support for Delta Lake schema enforcement, schema evolution, and a centralized metastore for querying. It natively integrates with Delta Lake's transaction log to enforce schema-on-write and supports MERGE/ALTER operations for evolution. The other services provide storage or query capabilities but not unified cataloging with schema governance.

Exam trap

DP-203 often tests the confusion between governance/cataloging tools (Purview) and unified metastore with schema enforcement (Unity Catalog) — Purview catalogs metadata but does not enforce schema at write time.

How to eliminate wrong answers

Option A is wrong because Azure Purview is a data governance and cataloging service for discovery, lineage, and classification — it does not enforce schema validation or handle schema evolution at write time. Option B is wrong because ADLS Gen2 is just hierarchical blob storage; it has no catalog, no schema enforcement, and no Delta Lake transaction semantics. Option C is wrong because Synapse serverless SQL pool can query Delta files but does not provide a unified catalog with schema enforcement or evolution management across workspaces.

Practice this question →

175

MCQeasy

You are designing a data processing solution in Azure Databricks to transform streaming data from Azure Event Hubs. The data must be aggregated in 1-minute tumbling windows and written to Azure Synapse Analytics. Which Spark API should you use?

A.RDD API

B.Structured Streaming

C.Spark Streaming (DStreams)

D.DataFrame API with batch processing

AnswerB

Structured Streaming satisfies the 1-minute tumbling window requirement through its event-time windowing on the streaming DataFrame, aggregating Event Hubs data incrementally. It writes results to Azure Synapse Analytics via the Synapse connector, unlike DStreams (RDD-based, deprecated) or batch APIs, which cannot process continuous streams natively.

Why this answer

Structured Streaming is the correct choice because it provides native support for event-time-based aggregations, such as 1-minute tumbling windows, and integrates seamlessly with Azure Event Hubs as a streaming source and Azure Synapse Analytics as a streaming sink using the `foreachBatch` or `writeStream` API. It offers exactly-once semantics and automatic state management for windowed operations, which are essential for reliable streaming ETL.

Exam trap

The trap here is that candidates confuse the older Spark Streaming (DStreams) API with Structured Streaming, assuming both are equally capable for event-time windows, but DStreams lack native event-time support and are deprecated in favor of Structured Streaming.

How to eliminate wrong answers

Option A is wrong because the RDD API operates at a low level without built-in support for event-time windowing, stateful aggregation, or streaming sinks like Azure Synapse Analytics, requiring manual implementation of checkpointing and fault tolerance. Option C is wrong because Spark Streaming (DStreams) uses micro-batch processing with a DStream API that is now in maintenance mode and lacks native event-time handling, making tumbling window aggregations more complex and less efficient compared to Structured Streaming. Option D is wrong because the DataFrame API with batch processing is designed for static data, not continuous streaming; it cannot process unbounded data from Event Hubs in real time or maintain state for tumbling windows without additional custom orchestration.

Practice this question →

176

Multi-Selectmedium

Which TWO are valid ways to process data in Azure Synapse Analytics?

Select 2 answers

A.Use Logic Apps to run data transformations.

B.Use Azure Functions to process data in a serverless manner.

C.Use Synapse SQL pool to run T-SQL queries.

D.Use Power BI to transform data.

E.Use Synapse Spark notebooks to run Scala code.

AnswersC, E

A dedicated Synapse SQL pool is a provisioned MPP engine that executes T-SQL, including distributed queries, stored procedures and CETAS, directly against data held in its own distributions. This satisfies the requirement for a T-SQL-based processing path within Synapse Analytics.

Why this answer

Option C is correct because a Synapse SQL pool (dedicated or serverless) is a core Synapse Analytics engine that executes T-SQL queries directly against data in the workspace, making it a valid data-processing method. Option E is correct because Synapse Spark notebooks run Apache Spark code, including Scala, Python, SQL, and R, and are a first-class way to transform and process data in Azure Synapse Analytics. Option A is not appropriate because Logic Apps is a workflow/automation service for orchestrating triggers and connectors, not a data transformation engine.

Option B is not the intended Synapse processing method because Azure Functions is a separate serverless compute service outside Synapse's built-in SQL and Spark engines. Option D is not correct because Power BI is a visualization and reporting tool; its data transformation (Power Query) is for modeling/reporting, not a Synapse data-processing workload.

Exam trap

The trap here is that candidates confuse general Azure services (Logic Apps, Functions, Power BI) with native Synapse Analytics processing capabilities, forgetting that only Synapse SQL and Synapse Spark are first-class compute engines within the service.

Practice this question →

177

MCQeasy

You need to perform incremental data loading from Azure SQL Database to Azure Data Lake Storage Gen2 using Azure Data Factory. Which approach is the most efficient?

A.Use a lookup activity to retrieve the last watermark value, then copy only new records with a filter.

B.Use a tumbling window trigger with a data flow that processes all data each time.

C.Use a mapping data flow with a full load and then use Azure Databricks to deduplicate.

D.Copy the entire table every time and use Azure Synapse serverless SQL to filter duplicates.

AnswerA

A watermark lookup captures the last-loaded value, and the copy activity's filter predicate then transfers only rows exceeding it, avoiding full-table reloads. This incremental pattern minimises data movement and pipeline duration compared with repeatedly copying the entire source table.

Why this answer

It uses a lookup activity to retrieve the last watermark value (e.g., a timestamp or incrementing key), then copies only new or changed records via a filter in the Copy activity. This minimizes data movement and processing time, making it the most efficient incremental loading approach in Azure Data Factory.

Exam trap

Microsoft often tests the misconception that any trigger-based or full-load approach can be adapted for incremental loading, but the key is minimizing data movement; candidates may overlook the watermark pattern and choose a full-load option because they think deduplication later solves the problem.

How to eliminate wrong answers

Option B is wrong because a tumbling window trigger with a data flow that processes all data each time performs a full load on every run, ignoring incremental logic and wasting resources. Option C is wrong because performing a full load followed by deduplication in Azure Databricks is inefficient; it moves all data repeatedly and adds unnecessary compute overhead. Option D is wrong because copying the entire table every time and using Azure Synapse serverless SQL to filter duplicates still transfers all data, incurring high egress costs and defeating the purpose of incremental loading.

Practice this question →

178

Multi-Selectmedium

You are designing a data processing solution in Azure Data Factory that must process files as they arrive in Azure Blob Storage. The solution must trigger a pipeline automatically when a new file is created, and then run a Databricks notebook to process the file. You need to configure the trigger and the pipeline activity. Which two actions should you perform? (Choose two.)

Select 2 answers

A.Add a Databricks Notebook activity to the pipeline and configure it to pass the file path as a parameter.

B.Create an event-based trigger of type 'BlobCreated' and configure it to start the pipeline when a file is created.

C.Add a Copy activity to the pipeline to copy the file to a staging location before processing.

D.Add a Stored Procedure activity to the pipeline to call a stored procedure in Azure SQL Database.

E.Create a tumbling window trigger and configure it to run every 5 minutes to check for new files.

AnswersA, B

The Databricks Notebook activity in Azure Data Factory allows you to run a notebook in Azure Databricks. By passing the file path as a parameter, the notebook can process the specific file that triggered the pipeline. This is the correct activity to use for running a Databricks notebook in this scenario.

Why this answer

To trigger a pipeline when a file is created in Blob Storage, you need an event-based trigger of type BlobCreated. To process the file with a Databricks notebook, you add a Databricks Notebook activity and pass the file path as a parameter. Tumbling window triggers are scheduled, not event-driven, and Copy or Stored Procedure activities do not run Databricks notebooks.

Exam trap

The trap here is confusing event-based triggers with scheduled triggers, and assuming any activity can run a Databricks notebook.

Practice this question →

179

MCQmedium

You have an Azure Synapse Analytics dedicated SQL pool. A nightly ELT process loads data into a staging table using PolyBase, then transforms and inserts it into a large fact table. You need to minimize data movement during the transformation step and ensure the fact table is optimized for large range scans. Which table distribution and index should you choose for the fact table?

A.Use a round-robin distributed table with a clustered index on the date column.

B.Use a replicated table with a clustered columnstore index.

C.Use a hash-distributed table on the date column with a heap index.

D.Use a hash-distributed table on the primary join key with a clustered columnstore index.

AnswerD

Hash distribution on the primary join key co-locates matching rows on the same distribution, minimizing data movement during joins and transformations. A clustered columnstore index compresses data and is optimized for large range scans and aggregations, which is exactly the workload described. This combination is the recommended pattern for large fact tables in a dedicated SQL pool.

Why this answer

For a large fact table in a dedicated SQL pool, hash distribution on the most frequently joined column minimizes data movement during transformations and joins. Pairing it with a clustered columnstore index provides columnar compression and segment elimination, which accelerates the large range scans and aggregations typical of fact-table queries. Together they satisfy both stated requirements.

Exam trap

The trap here is choosing distribution or indexing based on load convenience rather than on the join keys and scan patterns that dominate the transformation and reporting workload.

Practice this question →

180

MCQmedium

You are implementing a streaming pipeline in Azure Stream Analytics that reads from an Azure Event Hub and writes aggregated results to an Azure Synapse Analytics dedicated SQL pool. The query groups events into 30-second windows. You need to ensure that the job can handle late-arriving events up to 2 minutes after the window closes without dropping them. What should you configure?

A.Set the event ordering policy's late arrival tolerance to 2 minutes in the job's Event Ordering settings.

B.Change the window type from Tumbling to Sliding with a 2-minute duration.

C.Enable the 'Adjust event ordering' option and set the out-of-order tolerance to 2 minutes.

D.Increase the streaming units of the Stream Analytics job to accommodate the late events.

AnswerA

The late arrival tolerance in the event ordering policy tells Stream Analytics how long to wait for out-of-order events before finalizing a window. Setting it to 2 minutes ensures events arriving up to 2 minutes late are included in the correct window. This directly addresses the requirement without changing the query or output.

Why this answer

Late-arriving events are handled by the late arrival tolerance in the event ordering policy. This setting defines how long Stream Analytics waits for events that arrive after the window end before finalizing results. Configuring it to 2 minutes ensures events up to 2 minutes late are included.

Other options affect performance or window semantics but not late-event inclusion.

Exam trap

The trap here is confusing out-of-order tolerance with late arrival tolerance, which control different aspects of event timing.

Practice this question →

181

MCQmedium

You are designing a data pipeline to ingest streaming data from IoT devices into Azure Synapse Analytics. The data must be available for querying with minimal latency, but you also need to handle spikes in throughput without data loss. Which service should you use as the ingestion layer?

A.Azure Data Lake Storage Gen2

B.Azure Blob Storage

C.Azure IoT Hub

D.Azure Event Hubs

AnswerD

Azure Event Hubs ingests millions of events per second with partitioned consumer groups, buffering telemetry during throughput spikes so no data is dropped. Its native capture and Stream Analytics integration feed Synapse with minimal latency, satisfying both the spike-tolerance and low-latency querying constraints in the stem.

Why this answer

Azure Event Hubs is the correct choice because it is a fully managed, real-time data ingestion service optimized for high-throughput streaming data from millions of IoT devices. It provides at-least-once delivery, supports partitioning for massive scale, and integrates natively with Azure Synapse Analytics via the Synapse Pipeline or Event Hubs Capture to handle throughput spikes without data loss.

Exam trap

The trap here is that candidates often confuse Azure IoT Hub with Event Hubs, assuming IoT Hub is the default for all IoT streaming, but IoT Hub is designed for device management and lower-throughput telemetry, whereas Event Hubs is the dedicated high-throughput ingestion service for analytics pipelines.

How to eliminate wrong answers

Option A is wrong because Azure Data Lake Storage Gen2 is a hierarchical file storage service designed for batch and analytical workloads, not for real-time streaming ingestion; it lacks native event streaming and buffering capabilities. Option B is wrong because Azure Blob Storage is an object storage service for unstructured data, not built for low-latency, high-throughput event ingestion; it would require additional services like Event Hubs to capture streaming data. Option C is wrong because Azure IoT Hub is a managed service for bi-directional communication with IoT devices, but it is not optimized for high-volume streaming ingestion into Synapse; it is better suited for device management and command/control scenarios, and its default throughput is lower than Event Hubs for pure data ingestion.

Practice this question →

182

MCQmedium

Refer to the exhibit. You have an Azure Data Factory pipeline that copies trade data from Azure Blob Storage to Azure SQL Database. The pipeline runs every hour and truncates the destination table before each copy. However, users report that data is missing during the copy window. What is the most likely cause?

A.The output dataset is not correctly configured

B.The writeBatchSize is too low, causing timeouts

C.The preCopyScript truncates the table before the copy completes, causing a period with no data

D.The source dataset is set to recursive, which includes unwanted files

AnswerC

The preCopyScript runs before the copy activity begins, truncating the destination table. Until the copy writes rows, the table is empty, so users querying during that window see missing data. This is the specific mechanism causing the reported gap, not a pipeline scheduling or mapping fault.

Why this answer

The preCopyScript truncates the destination table before the copy activity begins writing data, creating a window where the table is empty or partially populated. Since the pipeline runs every hour and users query the table during the copy window, they see missing data until the copy completes. This is the classic 'truncate-then-load' anti-pattern in ADF that causes data availability gaps.

The correct fix is to use a staging table with an atomic swap or to use incremental loading instead of truncation.

Exam trap

DP-203 often tests the misconception that preCopyScript is a safe way to refresh data — candidates must recognize that truncating before a copy creates a data availability gap, and the exam expects you to identify this as the root cause of 'missing data during the copy window' symptoms.

How to eliminate wrong answers

Option A is wrong because an incorrectly configured output dataset would typically cause the copy to fail entirely or write to the wrong location — it would not produce a consistent pattern of missing data only during the copy window. Option B is wrong because a low writeBatchSize would cause slower performance or timeouts, not a predictable data gap during every hourly run — and timeouts would manifest as pipeline failures, not silent data absence. Option D is wrong because a recursive source setting would include extra files (potentially duplicating or adding unwanted data), not cause missing data during the copy window — the symptom described is a temporal gap, not data contamination.

Practice this question →

183

MCQeasy

Your team is developing a data processing solution using Azure Databricks. The data is stored in Delta Lake format in Azure Data Lake Storage Gen2. You need to ensure that when multiple jobs concurrently write to the same Delta table, the operations are atomic and consistent. Which Delta Lake feature should you use?

A.Enable Optimized Write on the Delta table.

B.Enable Auto Optimize on the Delta table.

C.Rely on Delta Lake's built-in ACID transactions.

D.Use Dynamic Partition Pruning in your Spark jobs.

AnswerC

Delta Lake's ACID transaction log serialises concurrent writes, guaranteeing atomicity and consistency across simultaneous jobs. This satisfies the stem's requirement for atomic, consistent operations when multiple jobs write to the same Delta table, without needing external locking mechanisms or manual coordination.

Why this answer

Delta Lake provides built-in ACID (Atomicity, Consistency, Isolation, Durability) transactions that guarantee atomic and consistent concurrent writes. When multiple jobs write to the same Delta table, Delta Lake uses a transaction log (stored as JSON files in the `_delta_log` directory) to serialize writes, ensuring that each write is either fully committed or rolled back, preventing partial updates or data corruption.

Exam trap

The trap here is that candidates confuse performance-tuning features (Optimized Write, Auto Optimize, Dynamic Partition Pruning) with transactional guarantees, assuming they provide atomicity or consistency when they only address file layout or query speed.

How to eliminate wrong answers

Option A is wrong because Optimized Write is a performance feature that reduces the number of small files written by coalescing partitions, but it does not provide atomicity or consistency guarantees for concurrent writes. Option B is wrong because Auto Optimize is a Delta Lake feature that automatically compacts small files and optimizes file layout, but it does not handle transactional concurrency or atomicity. Option D is wrong because Dynamic Partition Pruning is a Spark SQL optimization that improves query performance by skipping irrelevant partitions during joins, not a mechanism for ensuring atomic or consistent concurrent writes.

Practice this question →

184

MCQeasy

You are designing a data processing solution in Azure Databricks. The data is stored in Azure Data Lake Storage Gen2 and you need to perform transformations using Apache Spark. The security requirements mandate that all data in transit must be encrypted and that the storage account must not be accessible from the public internet. What should you configure?

A.Enable the storage account firewall and add a private endpoint for Azure Databricks to use.

B.Disable TLS on the storage account and use a shared access signature (SAS) token for authentication.

C.Use Azure Databricks with VNet injection and configure a service endpoint for the storage account.

D.Configure the storage account to use HTTPS only and enable firewall rules to allow only Azure services.

AnswerA

A private endpoint gives Azure Databricks a private IP path to the storage account, removing public internet exposure, while the storage firewall blocks all other networks. Traffic over this private link is encrypted in transit, satisfying both stated requirements.

Why this answer

It satisfies both security requirements: encrypting data in transit and preventing public internet access. A private endpoint uses Azure Private Link to connect Azure Databricks to the storage account over the Microsoft backbone network, ensuring all traffic stays within the Azure network and is encrypted via TLS. The storage account firewall is then configured to deny all public traffic, so only the private endpoint can access the storage account, meeting the 'not accessible from the public internet' mandate.

Exam trap

The trap here is confusing service endpoints (which still use public endpoints) with private endpoints (which use private IPs and fully isolate the resource from the internet), leading candidates to pick Option C thinking VNet injection plus service endpoints provides complete public internet isolation.

How to eliminate wrong answers

Option B is wrong because disabling TLS removes encryption in transit, violating the requirement that all data in transit must be encrypted; SAS tokens provide authentication but do not encrypt the channel. Option C is wrong because a service endpoint still exposes the storage account to the public internet (it only restricts source IPs via the firewall), so it does not meet the 'not accessible from the public internet' requirement; VNet injection alone does not enforce private connectivity. Option D is wrong because enabling HTTPS only and allowing only Azure services still leaves the storage account accessible from the public internet (Azure services can originate from public IPs), failing the requirement to block all public internet access.

Practice this question →

185

MCQeasy

You need to transform data in Azure Synapse Analytics using a language that supports procedural logic and error handling. Which option should you use?

A.T-SQL stored procedures

B.CREATE VIEW

C.PolyBase

D.CREATE EXTERNAL TABLE

AnswerA

T-SQL stored procedures provide procedural constructs such as IF, WHILE, TRY/CATCH and transactions within Synapse, satisfying the requirement for procedural logic and error handling. Spark notebooks and Mapping Data Flows offer transformation but not native T-SQL error-handling semantics against dedicated SQL pools.

Why this answer

T-SQL stored procedures are the correct choice because they support procedural logic (e.g., IF/ELSE, loops, TRY/CATCH) and error handling within Azure Synapse Analytics dedicated SQL pools. This allows you to encapsulate complex data transformation logic, handle runtime errors gracefully, and manage transactions, which is not possible with declarative objects like views or external tables.

Exam trap

The trap here is that candidates confuse PolyBase's ability to query external data with the ability to perform procedural transformations, overlooking that PolyBase is a query engine, not a programming construct for logic and error handling.

How to eliminate wrong answers

Option B is wrong because CREATE VIEW creates a read-only virtual table that cannot contain procedural logic or error handling; it is purely declarative. Option C is wrong because PolyBase is a data virtualization technology for querying external data sources (e.g., Azure Blob Storage) using T-SQL, but it does not support procedural logic or error handling itself. Option D is wrong because CREATE EXTERNAL TABLE defines a schema for external data but provides no procedural capabilities or error handling; it is a metadata object for PolyBase queries.

Practice this question →