Courseiva

CCNA Develop data processing Questions

75 of 185 questions · Page 2/3 · Develop data processing · Answers revealed

Practice these questionsDomain overviewAll questions
76
MCQhard

You are implementing a Spark Structured Streaming job in Azure Databricks that reads from an Azure Event Hubs topic. The job must handle late-arriving data up to 10 minutes and produce aggregated results every 5 minutes. You need to configure the watermark and window. Which code snippet should you use?

A.df.withWatermark("eventTime", "10 minutes").groupBy(window("eventTime", "5 minutes", "5 minutes")).agg(avg("temperature"))
B.df.withWatermark("eventTime", "5 minutes").groupBy(window("eventTime", "10 minutes")).agg(avg("temperature"))
C.df.withWatermark("eventTime", "10 minutes").groupBy(window("eventTime", "5 minutes")).agg(avg("temperature"))
D.df.withWatermark("eventTime", "10 minutes").groupBy(window("eventTime", "10 minutes", "5 minutes")).agg(avg("temperature"))
AnswerC

This snippet sets a watermark of 10 minutes to allow late data up to that threshold and groups by a 5-minute tumbling window. The watermark defines how long the system waits for late events before finalizing a window, and the window defines the aggregation interval. This matches the requirement to handle late data up to 10 minutes and produce results every 5 minutes, assuming output mode is set appropriately.

Why this answer

The correct configuration requires a watermark of 10 minutes to accommodate late data up to that limit, and a 5-minute tumbling window to produce results every 5 minutes. The withWatermark method sets the watermark on the event time column, and the window function with a single duration creates a tumbling window. The output mode must be set to update or append depending on the sink requirements.

Exam trap

The trap here is swapping the watermark duration and window duration, or using a sliding window when a tumbling window is needed.

Practice this question →
77
MCQeasy

Your team is developing a data processing solution in Azure Synapse Analytics. You need to ensure that the solution can automatically scale compute resources based on workload demand for serverless SQL pools. Which feature should you configure?

A.Set a cache size for the serverless SQL pool
B.Configure a dedicated SQL pool with auto-scaling
C.Use workload classification to assign resources
D.Enable auto-resume and auto-pause on the serverless SQL pool endpoint
AnswerD

Incorrect. Auto-resume and auto-pause do not scale compute resources; they only manage when the pool is active. Serverless SQL pools scale automatically without this feature.

Why this answer

Serverless SQL pools in Azure Synapse Analytics automatically scale compute resources based on workload demand without requiring any configuration. None of the provided options enable this automatic scaling. Auto-resume and auto-pause only control the pool's active state, not its compute size.

Dedicated SQL pool features like auto-scaling, cache sizing, and workload classification do not apply to serverless pools.

Exam trap

Candidates often assume that serverless SQL pools require a scaling configuration or that auto-resume/auto-pause scales compute resources. In reality, scaling is automatic and not configurable; auto-resume/auto-pause only manage availability.

How to eliminate wrong answers

Option A is wrong because serverless SQL pools do not have a configurable cache size; caching is managed automatically by the service and cannot be set by the user. Option B is wrong because a dedicated SQL pool with auto-scaling is a separate resource type that scales compute by adding or removing Data Warehouse Units (DWUs), but the question specifically asks about serverless SQL pools, which do not use dedicated compute resources. Option C is wrong because workload classification is a feature for dedicated SQL pools (formerly SQL Data Warehouse) to assign resources and priorities to different workloads; serverless SQL pools do not support workload classification as they automatically manage resource allocation.

Practice this question →
78
MCQeasy

You need to incrementally load new and updated records from a source SQL Server database to Azure Synapse Dedicated SQL Pool. The source table has a LastModifiedDate column. Which Azure Data Factory feature should you use to implement incremental loading efficiently?

A.Alter Row transformation
B.Incremental copy (watermark) pattern using a Lookup activity and a Copy activity
C.Schedule trigger
D.Lookup activity alone
AnswerB

The watermark pattern uses a Lookup activity to retrieve the maximum LastModifiedDate already copied, then a Copy activity filters the source on LastModifiedDate greater than that value. This satisfies the incremental requirement, moving only new and updated rows rather than reloading the whole table each run.

Why this answer

The incremental copy (watermark) pattern using a Lookup activity and a Copy activity is the correct approach because it allows you to query the source table for the maximum LastModifiedDate value (the watermark), store it in a control table or variable, and then use a Copy activity with a WHERE clause to load only rows where LastModifiedDate is greater than the last run's watermark. This pattern is purpose-built for efficiently handling new and updated records in Azure Data Factory without reprocessing the entire dataset.

Exam trap

The trap here is that candidates often confuse a scheduling mechanism (Schedule trigger) with the actual data processing logic required for incremental loads, or they mistakenly think a single activity like Lookup or Alter Row can handle the entire incremental copy workflow without understanding the need for a watermark pattern.

How to eliminate wrong answers

Option A is wrong because Alter Row transformation is a data flow transformation used to mark rows for insert, update, upsert, or delete in a sink, but it does not provide the incremental loading logic or watermark mechanism needed to identify new/updated rows from a source. Option C is wrong because a Schedule trigger only defines when a pipeline runs (e.g., every hour), but it does not implement the incremental copy logic itself; you still need the watermark pattern inside the pipeline. Option D is wrong because a Lookup activity alone can retrieve the watermark value but cannot copy data; it must be combined with a Copy activity to actually move the incremental rows.

Practice this question →
79
MCQmedium

You are designing a data pipeline in Azure Synapse Analytics to ingest data from Azure Blob Storage into a dedicated SQL pool. The source files are CSV with varying row lengths, and you need to ensure optimal performance for reads. Which file format and compression should you recommend?

A.Avro with Deflate compression
B.CSV with Gzip compression
C.Parquet with Snappy compression
D.ORC with Zlib compression
AnswerC

Parquet is columnar, so Synapse reads only referenced columns and skips irrelevant data, while Snappy decompresses quickly at low CPU cost. This combination delivers the optimal read performance the variable-length CSV source rows cannot provide.

Why this answer

Parquet with Snappy compression is optimal for dedicated SQL pools in Azure Synapse Analytics because Parquet is a columnar format that enables efficient predicate pushdown and column pruning, reducing I/O. Snappy provides fast compression/decompression with minimal CPU overhead, which is critical for high-throughput reads in a distributed MPP environment.

Exam trap

Microsoft often tests the misconception that row-based formats like Avro or CSV are suitable for analytical workloads, but the trap here is that columnar formats (Parquet/ORC) are required for optimal read performance in Synapse dedicated SQL pools, and Snappy is preferred over Zlib for speed-critical pipelines.

How to eliminate wrong answers

Option A is wrong because Avro is a row-based format that does not support columnar pruning, leading to higher I/O for analytical queries on dedicated SQL pools. Option B is wrong because CSV with Gzip compression is row-oriented and not splittable at the row level, causing poor parallelism and slower read performance in Synapse. Option D is wrong because ORC with Zlib compression offers higher compression ratios but significantly slower decompression compared to Snappy, which can bottleneck read performance in Synapse's MPP engine.

Practice this question →
80
MCQeasy

You are creating an Azure Data Factory data flow that transforms data from an Azure SQL Database. The data flow must filter rows based on a column value and then aggregate the results. Which transformation should you use first?

A.Filter transformation
B.Derived Column transformation
C.Join transformation
D.Aggregate transformation
AnswerA

The Filter transformation allows you to select rows based on a condition. Applying it first reduces the number of rows that subsequent transformations must process, improving performance and ensuring that aggregation only includes relevant data. In a data flow, transformations are executed in the order they are defined, so placing Filter before Aggregate is the correct sequence to meet the requirement of filtering then aggregating.

Why this answer

Filtering rows before aggregation reduces the amount of data processed by the Aggregate transformation, improving performance and ensuring that only relevant rows are aggregated. The Filter transformation is designed to select rows based on a condition, making it the appropriate first step. Other transformations like Aggregate, Derived Column, or Join do not directly filter rows and would be less efficient or incorrect in this context.

Exam trap

The trap here is assuming that the order of transformations doesn't matter or that aggregation should come first for performance, when actually filtering first reduces the data volume and ensures correct aggregation results.

Practice this question →
81
MCQeasy

You are developing a data processing solution that requires aggregating sales data from multiple CSV files stored in Azure Data Lake Storage Gen2. The data should be cleansed and transformed before loading into Azure Synapse Analytics. Which Azure service should you use to implement a code-free transformation pipeline?

A.Azure HDInsight with Hive
B.Azure Analysis Services
C.Azure Data Factory with Mapping Data Flows
D.Azure Databricks with PySpark
AnswerC

Mapping Data Flows provide a visual, code-free canvas for joins, aggregations and transformations that run on Spark clusters. This satisfies the code-free transformation requirement while reading CSV files from Data Lake Storage Gen2 and loading Synapse.

Why this answer

Azure Data Factory with Mapping Data Flows allows code-free visual transformations. Azure Databricks and HDInsight require code. Azure Analysis Services is for tabular modeling, not data processing.

Practice this question →
82
MCQhard

You are implementing a solution in Azure Databricks that reads from a Delta Lake table and writes to another Delta Lake table. The pipeline must process data incrementally and handle updates and deletes from the source. Which feature should you use to read the changes?

A.Change data feed (CDF) on the source Delta table.
B.Auto Loader with schema inference.
C.Delta Lake's ACID transactions with optimistic concurrency.
D.Time travel using the versionAsOf option.
AnswerA

Change data feed (CDF) on a Delta table records row-level changes (inserts, updates, deletes) and allows downstream consumers to read only the changes since a specific version. This enables incremental processing and correctly handles updates and deletes, which is essential for maintaining an accurate target table without full reprocessing.

Why this answer

Change data feed (CDF) is the correct feature because it records row-level changes in a Delta table and allows reading them as a stream. This enables incremental processing that includes inserts, updates, and deletes, ensuring the target table stays in sync. Other options either do not provide change data or are not designed for this purpose.

Exam trap

The trap here is confusing time travel with change data capture; time travel lets you query old versions but does not give you the delta of changes between versions.

Practice this question →
83
MCQhard

You are implementing a Mapping Data Flow in Azure Synapse Analytics that reads from a Parquet source and writes to a Delta sink. The data flow includes a Surrogate Key transformation to generate unique keys for each row. You notice that when the data flow runs multiple times, the surrogate keys are not consistent across runs and sometimes overlap. You need to ensure that surrogate keys are unique and stable across runs. What should you do?

A.Use a Derived Column transformation with the `uuid()` function to generate a unique identifier for each row.
B.Use a Surrogate Key transformation with the 'Start value' set to a value that increments based on the maximum existing key in the sink.
C.Use a Surrogate Key transformation with the 'Start value' set to 1 and enable the 'Unique' option.
D.Use a Derived Column transformation to concatenate a business key from the source with a hash function to create a deterministic surrogate key.
AnswerD

Creating a deterministic surrogate key by hashing a business key ensures that the same source row always produces the same key, regardless of how many times the data flow runs. This provides stability and uniqueness as long as the business key is unique. It avoids overlaps and is the correct approach for stable surrogate keys.

Why this answer

To ensure surrogate keys are unique and stable across runs, you must derive them deterministically from the source data. Hashing a business key with a function like `sha2` or `md5` produces the same key for the same input, preventing overlaps and ensuring consistency. Sequential Surrogate Key transformations or random UUIDs do not provide stability across multiple executions.

Exam trap

The trap here is assuming that the Surrogate Key transformation generates keys that are stable across runs, when it actually restarts numbering each time.

Practice this question →
84
Multi-Selecteasy

Which TWO of the following are required components to set up a data pipeline that uses Change Data Capture (CDC) to incrementally load data from SQL Server to Azure Synapse using Azure Data Factory?

Select 2 answers
A.CDC enabled on the source SQL Server database and tables
B.A staging Azure Blob Storage account
C.A Lookup activity to get the last watermark
D.A stored procedure in the source database to capture changes
E.A linked service to the Azure Synapse dedicated SQL pool
AnswersA, E

CDC must be enabled on the source to track changes.

Why this answer

Change Data Capture (CDC) must be enabled on the source SQL Server database and the specific tables you intend to track. Without CDC enabled, SQL Server will not generate the change tracking tables (e.g., cdc.<capture_instance>_CT) that Azure Data Factory’s CDC connector reads to identify inserts, updates, and deletes. This is a prerequisite for any incremental load using the native CDC mechanism in ADF.

Exam trap

The trap here is that candidates often confuse CDC-based incremental loading with watermark-based incremental loading, leading them to incorrectly select a Lookup activity (Option C) or a staging storage account (Option B) as required components.

Practice this question →
85
MCQmedium

You are building a real-time dashboard to monitor user activity on a website. The data is ingested via Azure Event Hubs and must be aggregated every minute with a 30-second late-arrival tolerance. The aggregated results should be stored in Azure Cosmos DB for low-latency reads. Which Azure service should you use to perform the windowed aggregation?

A.Azure Stream Analytics with a tumbling window of 1 minute and a late-arrival policy of 30 seconds.
B.Azure Functions triggered by Event Hubs to aggregate data and write to Cosmos DB.
C.Azure Databricks with structured streaming and a sliding window.
D.Azure Analysis Services to process streaming data directly from Event Hubs.
AnswerA

Azure Stream Analytics natively supports tumbling windows, producing non-overlapping one-minute aggregates, and its event-ordering late-arrival tolerance of 30 seconds holds back results until stragglers arrive. This satisfies both the one-minute aggregation cadence and the 30-second tolerance, while writing output directly to Cosmos DB.

Why this answer

Azure Stream Analytics is the correct choice because it natively supports windowed aggregations (tumbling, hopping, sliding, session) and allows you to define a late-arrival policy to handle out-of-order events. A tumbling window of 1 minute with a late-arrival tolerance of 30 seconds meets the requirement exactly, and the output can be directly written to Azure Cosmos DB for low-latency reads.

Exam trap

The trap here is that candidates often confuse tumbling windows (fixed, non-overlapping) with sliding windows (continuous, overlapping) or assume that any compute service (like Functions or Databricks) can easily replicate Stream Analytics' built-in windowing and late-arrival handling, ignoring the complexity of state management and exactly-once semantics.

How to eliminate wrong answers

Option B is wrong because Azure Functions triggered by Event Hubs do not provide built-in windowing or late-arrival policy support; you would have to manually implement stateful aggregation, which is complex and error-prone. Option C is wrong because Azure Databricks with structured streaming uses a sliding window, not a tumbling window, and does not offer a native late-arrival policy configuration as simple as Stream Analytics; it also introduces unnecessary overhead for this real-time dashboard scenario. Option D is wrong because Azure Analysis Services is an OLAP engine for analytical queries on pre-aggregated data, not a real-time stream processing service; it cannot directly process streaming data from Event Hubs.

Practice this question →
86
Multi-Selecteasy

You are designing a data processing solution in Azure Data Factory that uses mapping data flows. You need to perform type conversions on incoming data. Which two transformations can be used to change data types? (Choose two.)

Select 2 answers
A.Conditional Split
B.Derived Column
C.Assert
D.Sort
E.Select
AnswersB, E

The Derived Column transformation lets you add or replace columns and explicitly set their data types through the expression builder, so incoming string values can be cast to integer, date or decimal within the mapping data flow.

Why this answer

Options B and E are correct. Derived Column can change data types through expressions, and Select can cast types during column projection. Option C (Assert) is incorrect because it only validates and routes rows based on conditions; it does not convert data types.

Option A (Conditional Split) and Option D (Sort) are also incorrect.

Practice this question →
87
MCQhard

You are designing a data processing solution for a financial services company. The solution must process sensitive customer data and comply with GDPR. The data will be stored in Azure Synapse Analytics. You need to ensure that only authorized users can view specific columns (e.g., credit card numbers). Which security feature should you implement?

A.Row-level security (RLS)
B.Column-level security
C.Dynamic data masking
D.Microsoft Defender for Cloud
AnswerB

Column-level security in Azure Synapse Analytics grants SELECT permissions on individual columns, so credit card numbers stay hidden from users lacking explicit column grants. This satisfies the GDPR requirement that only authorised users view specific columns, while the rest of the table remains queryable.

Why this answer

Column-level security (CLS) in Azure Synapse Analytics allows you to restrict access to specific columns in a table, such as credit card numbers, by granting or denying SELECT permissions on those columns. This directly meets the GDPR requirement to limit exposure of sensitive personal data to authorized users only, without affecting access to other columns.

Exam trap

The trap here is that candidates often confuse Dynamic data masking with access control, but masking only hides data from the UI while still allowing underlying access, whereas column-level security actually prevents unauthorized users from reading the column data at all.

How to eliminate wrong answers

Option A is wrong because Row-level security (RLS) restricts access to rows based on user identity or context, not columns, so it cannot limit visibility of specific columns like credit card numbers. Option C is wrong because Dynamic data masking obfuscates data at query time but does not prevent authorized users from viewing the original data if they have direct access; it is not a permission-based access control. Option D is wrong because Microsoft Defender for Cloud is a security monitoring and threat protection service, not a data access control feature for restricting column visibility in Synapse Analytics.

Practice this question →
88
MCQmedium

You are building an Azure Stream Analytics job that reads from an Azure Event Hubs input and writes to an Azure Synapse Analytics dedicated SQL pool. You need to compute a 5-minute tumbling window aggregation that outputs only once per window after all events for that window have arrived. Which query construct should you use?

A.A self-join on the input stream using DATEDIFF to group events into 5-minute buckets
B.GROUP BY with TUMBLINGWINDOW and a 5-minute window, emitting results when the window closes
C.A persistent SQL table in the dedicated SQL pool that stores events, with a scheduled stored procedure running every 5 minutes
D.GROUP BY with HOPPINGWINDOW and a 5-minute window and 1-minute hop
AnswerB

TUMBLINGWINDOW in Azure Stream Analytics defines fixed-size, non-overlapping, contiguous time intervals. When the window closes, the job emits a single aggregated result per window. This matches the requirement to output only once per 5-minute window after all events are processed, and it is the native construct for this pattern in Stream Analytics.

Why this answer

Tumbling windows are fixed-size, non-overlapping, and emit a single result when the window closes, which aligns exactly with the need to output once per 5-minute interval after all events arrive. Other window types or external scheduling mechanisms either emit too frequently or cannot guarantee completeness of the window before output.

Exam trap

The trap here is confusing tumbling windows with hopping windows, which overlap and emit multiple results per interval.

Practice this question →
89
MCQhard

You are developing an Azure Databricks notebook that processes streaming data from Azure Event Hubs and writes to a Delta Lake table. The stream must handle late-arriving data up to 30 minutes old and ensure that aggregations are computed correctly even if events arrive out of order. You need to minimize state store size and avoid unbounded growth. Which combination of features should you use?

A.Use a tumbling window with a 30-minute watermark and configure the state store with a retention duration of 30 minutes.
B.Use event-time processing with a watermark of 30 minutes and apply a 'dropDuplicates' operation on the event ID within the watermark.
C.Use event-time processing with a watermark of 30 minutes and apply a windowed aggregation with a tumbling window of 10 minutes.
D.Use a sliding window with a 30-minute watermark and set the output mode to 'complete'.
AnswerC

Event-time processing with a watermark of 30 minutes allows late data up to 30 minutes old to be included in the correct window. A tumbling window of 10 minutes defines the aggregation intervals. The watermark ensures that state for windows older than 30 minutes is cleaned up, preventing unbounded state growth. This combination correctly handles late data and manages state size.

Why this answer

Using event-time processing with a watermark of 30 minutes ensures that late-arriving data up to 30 minutes old is processed correctly. A tumbling window of 10 minutes defines the aggregation intervals, and the watermark automatically cleans up state for windows that are older than the watermark, preventing unbounded state growth. This approach balances correctness and resource usage.

Exam trap

The trap here is assuming that a retention duration can be set manually for the state store, or that complete output mode is suitable for late data; actually, the watermark controls state cleanup.

Practice this question →
90
MCQeasy

You are designing a data processing solution in Azure Synapse Analytics. The solution must use a dedicated SQL pool to store fact and dimension tables. The fact table is expected to have billions of rows. Which distribution strategy should you recommend for the fact table to optimize query performance and minimize data movement?

A.Round-robin distribution.
B.Partitioned table with a partition key.
C.Hash distribution on a column that is frequently used in joins and aggregations.
D.Replicated distribution.
AnswerC

Hash distribution spreads billions of fact rows evenly across distributions, and choosing a column frequently used in joins and aggregations enables collocated joins that eliminate shuffling. Round-robin would force data movement during joins, while replicated distribution cannot hold a fact table of this size.

Why this answer

Hash distribution on a column frequently used in joins and aggregations is the best choice for a fact table with billions of rows in a dedicated SQL pool. It distributes rows across distributions based on a hash of the distribution column, ensuring that rows with the same key value are co-located on the same distribution. This minimizes data movement during joins and aggregations, as the data required for these operations is already local to each distribution, significantly improving query performance.

Exam trap

The trap here is that candidates often confuse partitioning with distribution, thinking that partitioning alone can optimize data movement across nodes, but partitioning operates within a distribution and does not affect how data is distributed across compute resources.

How to eliminate wrong answers

Option A is wrong because round-robin distribution distributes rows evenly without considering data relationships, which leads to excessive data movement during joins and aggregations, degrading performance for large fact tables. Option B is wrong because partitioning is a data organization technique within a distribution, not a distribution strategy; it helps with data management and partition elimination but does not control how data is distributed across compute nodes, so it cannot minimize data movement across distributions. Option D is wrong because replicated distribution copies the entire table to each compute node, which is impractical for a fact table with billions of rows due to massive storage overhead and write performance penalties; it is intended for small dimension tables, not large fact tables.

Practice this question →
91
MCQhard

Refer to the exhibit. You are creating a serverless SQL table in Azure Synapse Analytics that reads Parquet files from the specified location. The folder contains multiple Parquet files with different schemas. When querying the table, you get an error about schema mismatch. What is the most likely reason?

A.The Parquet files are not using the .parquet extension.
B.The derivedModel option is set to false, which disables schema inference.
C.The serverless SQL pool infers schema from the first file and expects all files to have the same schema.
D.The recursive option is causing the table to include files from subfolders that have different schemas.
AnswerC

With OPENROWSET or an external table, the serverless SQL pool reads only the first file to derive column names and types, then applies that schema to every file. Differing schemas in later files therefore trigger a mismatch error at query time.

Why this answer

Azure Synapse serverless SQL pools infer the schema from the first Parquet file encountered in the specified location. When multiple Parquet files with different schemas exist, the pool expects all subsequent files to match that initial schema. If any file has a different schema (e.g., different column names, data types, or number of columns), a schema mismatch error is raised.

This behavior is by design, as serverless SQL does not merge or reconcile disparate schemas across files.

Exam trap

The trap here is that candidates assume serverless SQL can automatically handle heterogeneous schemas (like Spark does with mergeSchema), but in reality it requires all files to share the exact same schema as the first file it reads.

How to eliminate wrong answers

Option A is wrong because the .parquet extension is not required; serverless SQL can infer Parquet format from the file's binary header, not the file extension. Option B is wrong because the derivedModel option does not exist in serverless SQL table creation; schema inference is always enabled and cannot be disabled via such an option. Option D is wrong because the recursive option controls whether subfolders are scanned, but schema mismatch errors occur even without recursion if files in the same folder have different schemas; recursion is not the root cause.

Practice this question →
92
Multi-Selecthard

You are implementing a Spark Structured Streaming job in Azure Databricks that consumes from an Azure Event Hubs topic and writes to a Delta table. The stream must tolerate reprocessing after a cluster restart without producing duplicate rows in the Delta table. You need to configure the write path accordingly. (Choose two.)

Select 2 answers
A.Enable checkpointing by setting the checkpointLocation option on the writeStream.
B.Set the trigger to Trigger.Once so the stream reads the topic only one time.
C.Enable auto compaction on the Delta table to merge small files and eliminate repeated rows.
D.Configure the write mode as append and rely on the Delta table schema to reject duplicates.
E.Use foreachBatch with an idempotent MERGE INTO statement against the Delta table.
AnswersA, E

Checkpointing records the progress of each micro-batch, including which offsets have been processed, in durable storage. After a cluster restart, the stream resumes from the last committed offset rather than reprocessing the entire topic. Without a checkpoint location, Structured Streaming cannot track progress and will either fail to restart or re-read from the beginning, which is the primary cause of duplicate rows.

Why this answer

Exactly-once behavior at the sink requires both a durable record of processed offsets and an idempotent write. Checkpointing supplies the first by persisting progress so a restart resumes correctly, and a MERGE keyed on a business identifier supplies the second by making replay harmless. Neither append mode, one-time triggers, nor file compaction can prevent logical duplicates because none of them compare row identity across batches.

Exam trap

The trap here is treating Delta Lake as if it enforced primary keys, when in fact it accepts duplicate rows unless the write logic itself is made idempotent.

Practice this question →
93
MCQeasy

You are designing a data pipeline that uses Azure Data Factory to load data from an FTP server to Azure Data Lake Storage. The FTP server requires authentication with username and password. Which type of linked service should you create?

A.FTP
B.Azure Blob Storage
C.HTTP
D.Rest service
AnswerA

The FTP linked service is purpose-built for FTP servers requiring username and password authentication, storing those credentials securely in Azure Data Factory. It provides the connection details needed to read files from the FTP source into Azure Data Lake Storage.

Why this answer

Azure Data Factory provides a native FTP connector that supports username/password authentication for connecting to FTP servers. This linked service type is specifically designed to handle the FTP protocol (RFC 959) and allows you to copy data directly from an FTP server to Azure Data Lake Storage without requiring any additional gateways or custom activities.

Exam trap

The trap here is that candidates often confuse the FTP connector with the HTTP or REST connector because they think all file transfers can be handled by generic web protocols, but FTP has its own distinct authentication and command set that requires a dedicated connector.

How to eliminate wrong answers

Option B (Azure Blob Storage) is wrong because it is a destination or source for Azure's blob storage service, not a connector for external FTP servers; it cannot authenticate against an FTP server. Option C (HTTP) is wrong because the HTTP connector uses HTTP/HTTPS protocols and does not support FTP-specific authentication or directory listing commands. Option D (Rest service) is wrong because REST connectors are designed for RESTful APIs using JSON/XML payloads, not for FTP protocol operations like LIST or RETR.

Practice this question →
94
MCQeasy

You are creating an Azure Data Factory pipeline that must copy data from an on-premises Oracle database to Azure Blob Storage every night. The on-premises network restricts inbound connections. You need to configure the integration runtime. What should you do?

A.Create an Azure integration runtime and allow inbound connections from Azure Data Factory service tags.
B.Create an Azure-SSIS integration runtime and join it to an Azure virtual network.
C.Use the Azure Data Factory managed virtual network and private endpoint to connect to the on-premises Oracle database.
D.Create a self-hosted integration runtime and install it on a machine in the on-premises network.
AnswerD

A self-hosted integration runtime is installed on a machine inside the on-premises network and makes outbound connections to Azure Data Factory. This design works when inbound connections are blocked because the IR initiates outbound traffic over HTTPS. It can then connect locally to the Oracle database and copy data to Azure Blob Storage, satisfying the nightly copy requirement.

Why this answer

When an on-premises network blocks inbound connections, the only viable way for Azure Data Factory to reach an on-premises Oracle database is a self-hosted integration runtime. It runs inside the network, makes outbound connections to the ADF service, and bridges to local data sources. Azure IR, Azure-SSIS IR, and managed virtual network private endpoints cannot reach the on-premises database under these restrictions.

Exam trap

The trap here is assuming that an Azure integration runtime or private endpoint can reach on-premises data when inbound connections are blocked.

Practice this question →
95
MCQmedium

You are authoring an Azure Databricks notebook that reads Parquet files from Azure Data Lake Storage Gen2 and must write results to a Delta table. Users report that queries against the Delta table return stale data after each notebook run, even though the write succeeds. You need to ensure readers always see the latest committed data. What should you do?

A.Ensure the write commits through the Delta transaction log and readers query the table by name, not by a cached DataFrame
B.Write the results with overwrite mode to the same Delta table path each run
C.Convert the Delta table to a Parquet table and rely on the file system for consistency
D.Call refreshTable or invalidate the cache on the reader session after each write
AnswerA

Delta Lake guarantees snapshot isolation through the _delta_log transaction log; a successful commit makes new data visible to new queries. Stale reads usually occur when a DataFrame was cached earlier or when a reader holds an old snapshot. Writing through the log and querying the table by name ensures each read sees the latest committed version.

Why this answer

Delta Lake provides snapshot isolation via its transaction log, so a committed write is immediately visible to subsequent reads. Stale results typically come from caching a DataFrame or holding an old snapshot rather than from the table itself. Reading the table by name, without a cached DataFrame, ensures the reader resolves the latest committed version at query time.

Exam trap

The trap here is blaming Delta's commit mechanism for stale reads, when caching in the reader session is the usual culprit.

Practice this question →
96
MCQmedium

You are running a pipeline in Azure Data Factory that uses a Mapping Data Flow. The data flow reads from Azure SQL Database and writes to Azure Synapse Analytics. You find that the data flow is very slow. Which configuration change would most likely improve performance?

A.Set the 'Staging' option to 'Use staging'
B.Increase the 'Compute type' to 'Memory Optimized' and the 'Core count'
C.Enable staging for the sink and use PolyBase
D.Set the 'Partition option' to 'Round robin' on the source
AnswerB

Mapping data flows run on Spark clusters; Memory Optimized compute with a higher core count increases available memory and parallelism, reducing spills and speeding the SQL Database to Synapse transfer. This addresses the slow data flow's resource bottleneck.

Why this answer

Mapping Data Flows in Azure Data Factory execute on Spark clusters. The default compute configuration may not provide sufficient memory or parallelism for large data volumes. Increasing the 'Compute type' to 'Memory Optimized' and raising the 'Core count' directly allocates more memory and processing cores to the Spark cluster, which accelerates transformations and data movement between Azure SQL Database and Azure Synapse Analytics.

Exam trap

The trap here is that candidates confuse Mapping Data Flow performance tuning with Copy Activity optimizations, such as PolyBase or staging, which are irrelevant to Spark-based data flows.

How to eliminate wrong answers

Option A is wrong because setting 'Staging' to 'Use staging' in a Mapping Data Flow is not a valid configuration; staging is used for copy activities, not for data flows. Option C is wrong because enabling staging for the sink and using PolyBase is a performance optimization for Copy Activity, not for Mapping Data Flow, which uses Spark-native connectors. Option D is wrong because setting the 'Partition option' to 'Round robin' on the source distributes data evenly but does not address the root cause of slow performance, which is insufficient compute resources for the Spark cluster.

Practice this question →
97
MCQeasy

You are using Azure Databricks to process a large dataset stored in Delta Lake. You need to reduce the number of files scanned during queries by organizing data into folders based on a commonly filtered column. Which Delta Lake feature should you implement?

A.Partitioning the Delta table by the filtered column
B.Z-ORDER BY on the filtered column
C.Enabling Change Data Feed on the Delta table
D.Running OPTIMIZE with file compaction
AnswerA

Partitioning a Delta table physically organizes data into separate folders based on the partition column. When queries filter on that column, Delta Lake can skip entire folders, reducing the amount of data scanned. This directly meets the requirement to organize data into folders for improved query performance.

Why this answer

Partitioning a Delta table by a frequently filtered column creates separate folders for each distinct value, allowing Delta Lake to skip irrelevant folders during queries. This reduces the number of files scanned and improves performance. Z-ORDER, Change Data Feed, and OPTIMIZE address different aspects and do not provide folder-level organization.

Exam trap

The trap here is confusing Z-ORDER BY with partitioning, when only partitioning creates a folder-based layout.

Practice this question →
98
MCQmedium

You are designing a data processing solution that uses Azure Databricks to transform large datasets. You need to ensure that the processing is cost-effective and can scale to handle variable workloads. Which cluster configuration should you recommend?

A.Use an auto-scaling cluster with spot instances.
B.Use a fixed-size cluster with premium tier.
C.Use a Photon-accelerated cluster with premium tier.
D.Use an interactive cluster with a large number of workers.
AnswerA

Auto-scaling adjusts worker count to match variable workload demand, while spot instances cut compute cost substantially for fault-tolerant Spark jobs. Together they satisfy the cost-effectiveness and variable-scale constraints, provided spot eviction is tolerated by the transformation workload.

Why this answer

Auto-scaling clusters in Azure Databricks dynamically adjust the number of workers based on workload demands, ensuring cost-effectiveness by scaling down during low activity. Spot instances (Azure Spot VMs) further reduce costs by using unused Azure capacity at a significant discount, making this combination ideal for variable workloads where fault tolerance is acceptable.

Exam trap

The trap here is that candidates often assume premium tier or Photon acceleration automatically improves cost-effectiveness, but these features address performance or governance, not the core requirement of scaling with variable workloads and minimizing cost via spot pricing.

How to eliminate wrong answers

Option B is wrong because a fixed-size cluster cannot scale to handle variable workloads, leading to either over-provisioning (higher costs) or under-provisioning (performance degradation). Option C is wrong because Photon-accelerated clusters are optimized for high-performance SQL and DataFrame workloads, but they do not inherently address cost-effectiveness for variable workloads; the premium tier adds features like role-based access control but does not enable scaling or spot pricing. Option D is wrong because an interactive cluster with a large number of workers is designed for ad-hoc analysis and collaboration, not for cost-effective batch processing; it lacks auto-scaling and spot instance support, leading to higher costs during idle periods.

Practice this question →
99
MCQeasy

You are designing a data processing solution for a marketing company that uses Azure Synapse Analytics. The solution needs to process customer data from multiple sources, including CRM and web analytics. The data must be cleansed and transformed before loading into a dedicated SQL pool. The transformations include string manipulations, date conversions, and lookups. You need to choose a serverless transformation approach that integrates with Azure Synapse pipelines. Which approach should you use?

A.Use Azure Stream Analytics to transform the data in real time.
B.Use PolyBase to load data and then use T-SQL stored procedures to transform.
C.Use Azure Databricks notebooks with Spark to perform transformations.
D.Use mapping data flows in Azure Synapse pipelines.
AnswerD

Correct. Mapping data flows in Azure Synapse pipelines are serverless, provide a visual interface for data transformations, and integrate directly with Azure Synapse pipelines, making them ideal for cleansing and transforming data before loading into a dedicated SQL pool.

Why this answer

Mapping data flows in Azure Synapse pipelines provide a serverless, visual interface for data transformations, including string manipulations, date conversions, and lookups, seamlessly integrating with Synapse pipelines. Option A is wrong because Azure Stream Analytics is designed for real-time streaming, not batch transformations. Option B is wrong because PolyBase is a data loading technology, not a transformation service, and T-SQL stored procedures are not serverless.

Option C is wrong because Azure Databricks requires an active cluster, making it not serverless.

Practice this question →
100
MCQmedium

You are building an Azure Stream Analytics job that reads JSON events from an Azure Event Hub and writes aggregated results to an Azure Synapse Analytics dedicated SQL pool. The events include a field named `EventTime` that is sometimes missing or malformed. You need the job to process only events with a valid `EventTime` and route invalid events to a separate output for later inspection. What should you do?

A.Configure the Event Hub input to use the JSON serialization format with the `EventTime` field marked as required, so the job automatically drops events missing that field.
B.Use a JavaScript user-defined function in the query to validate `EventTime` and throw an exception for invalid events, which Stream Analytics will automatically redirect to the job's error log.
C.Add a second output to the job that writes to Azure Blob Storage, and configure the Event Hub input to send malformed events to that output.
D.In the Stream Analytics query, use a WITH clause to define a filtered stream that selects events where TRY_CAST(EventTime AS datetime) IS NOT NULL, and write the excluded events to a separate output using a second query.
AnswerD

Stream Analytics supports T-SQL-like expressions, including TRY_CAST, which returns NULL instead of failing on malformed input. By defining a filtered stream with a WITH clause and writing two queries—one for valid events and one for the complement—you can route valid events to Synapse and invalid events to a separate output. This is the supported pattern for conditional routing.

Why this answer

Stream Analytics queries can filter and split streams using standard SQL expressions. TRY_CAST safely converts values and returns NULL for malformed input, allowing a filtered stream of valid events. A second query selecting the complement routes invalid events to a separate output.

This approach keeps the job running without failures and provides a mechanism to inspect bad data later.

Exam trap

The trap here is assuming that the Event Hub input or serialization settings can automatically drop or reroute malformed events, when routing must be implemented in the query with multiple outputs.

Practice this question →
101
MCQmedium

A company uses Azure Synapse Analytics dedicated SQL pool. The data engineering team notices that queries against a large fact table are running slowly. The table uses round-robin distribution and has a columnstore index. The team wants to improve query performance without adding more resources. Which action should the team take?

A.Keep round-robin distribution but increase the degree of parallelism.
B.Change the distribution to hash on multiple columns.
C.Change the distribution to hash on the column that is most frequently used in joins.
D.Rebuild the table as a heap to improve insert performance.
AnswerC

Hash distribution on a join key reduces data shuffling.

Why this answer

Hash-distributing the large fact table on the column most frequently used in joins minimizes data movement during query processing, improving performance. Round-robin distribution distributes data evenly but does not optimize for join operations. Hash distribution on a join key ensures that rows with the same key value are placed in the same distribution, reducing shuffling.

Option A is incorrect because increasing the degree of parallelism does not address the distribution issue and may not improve performance without additional resources. Option B is incorrect because hash on multiple columns is not supported in Azure Synapse dedicated SQL pool; only a single column can be used as the distribution key. Option D is incorrect because a heap table would lack indexing, degrading query performance for analytical workloads.

Practice this question →
102
MCQeasy

You have a pipeline in Azure Data Factory that copies data from on-premises SQL Server to Azure Blob Storage. The pipeline fails with a 'Connection timed out' error. You have already verified that the Integration Runtime is running and the SQL Server firewall allows connections from the Integration Runtime. What should you check next?

A.Ensure the Integration Runtime is registered and online
B.Check if the Blob Storage endpoint is accessible from the Integration Runtime
C.Check if the SQL Server is configured to allow remote connections and that TCP/IP is enabled
D.Verify that the SQL Server login credentials are correct
AnswerC

With the Integration Runtime running and firewall verified, the remaining network-layer cause is SQL Server itself. If remote connections are disabled or TCP/IP is off, the instance only accepts local named-pipe connections, so the Integration Runtime's TCP attempt times out.

Why this answer

The 'Connection timed out' error, despite the Integration Runtime being running and the firewall allowing connections, typically indicates that SQL Server is not listening on the expected TCP port. This often happens when TCP/IP is disabled in SQL Server Configuration Manager or remote connections are not enabled. Without TCP/IP enabled, the Integration Runtime cannot establish a network connection to the SQL Server instance, leading to a timeout.

Exam trap

The trap here is that candidates assume a 'Connection timed out' error is always a firewall or network issue, overlooking the SQL Server-side protocol configuration that must be explicitly enabled for remote TCP connections.

How to eliminate wrong answers

Option A is wrong because the question states that the Integration Runtime is already verified as running, so re-checking its registration and online status is redundant and does not address the timeout. Option B is wrong because the error is a connection timeout to SQL Server, not to Blob Storage; the pipeline fails before data transfer begins, so Blob Storage accessibility is irrelevant at this stage. Option D is wrong because incorrect login credentials would result in an authentication error (e.g., 'Login failed'), not a 'Connection timed out' error, which is a network-level issue.

Practice this question →
103
MCQhard

You are designing a near-real-time data processing solution for a retail company. The source is a Kafka cluster on-premises. The target is an Azure Synapse Dedicated SQL Pool. The solution must handle up to 10,000 events per second with less than 5-minute latency. Which Azure service should you use to ingest the data?

A.Azure Event Hubs (with Kafka protocol support)
B.Azure Data Lake Storage Gen2
C.Azure IoT Hub
D.Azure Stream Analytics
AnswerA

Event Hubs natively ingests Kafka protocol traffic, so the on-premises producers need no reconfiguration, and its partitioned throughput scales past 10,000 events per second, meeting the sub-five-minute latency requirement before loading into the dedicated SQL pool.

Why this answer

Azure Event Hubs with Kafka protocol support is the correct choice because it provides a fully managed, high-throughput data ingestion service that can handle up to 10,000 events per second with sub-second latency, and it natively supports the Kafka protocol, allowing direct integration with your on-premises Kafka cluster without custom code or additional gateways. This meets the near-real-time requirement (<5-minute latency) and scales to the specified throughput.

Exam trap

The trap here is that candidates often confuse Azure Stream Analytics as an ingestion service, but it is a processing engine that requires an ingestion layer (like Event Hubs) first, and they may overlook that Azure Event Hubs natively supports the Kafka protocol, making it the direct replacement for Kafka ingestion in Azure.

How to eliminate wrong answers

Option B (Azure Data Lake Storage Gen2) is wrong because it is a hierarchical file store designed for batch analytics and data lake storage, not a real-time event ingestion service; it cannot natively consume Kafka streams or provide sub-5-minute latency for streaming data. Option C (Azure IoT Hub) is wrong because it is optimized for device-to-cloud telemetry from IoT devices, not for high-throughput event streams from a Kafka cluster, and it imposes device identity and throttling limits that are unsuitable for 10,000 events per second from a non-IoT source. Option D (Azure Stream Analytics) is wrong because it is a stream processing engine that requires an input source (like Event Hubs) to ingest data; it cannot directly ingest from Kafka on-premises and is not an ingestion service itself.

Practice this question →
104
MCQeasy

You are designing a data processing solution in Azure Synapse Analytics. The solution must support both batch and streaming data ingestion. Which Azure service should you use to ingest streaming data into Synapse Analytics?

A.Azure Data Factory
B.Azure Blob Storage
C.Azure Event Hubs
D.Azure Analysis Services
AnswerC

Azure Event Hubs ingests high-throughput streaming data and integrates natively with Synapse Analytics through its dedicated connector, satisfying the stem's streaming ingestion requirement. Unlike batch-only pipelines, Event Hubs captures continuous telemetry in real time, landing events directly into Synapse SQL pools or Spark tables for immediate processing.

Why this answer

Azure Event Hubs is Microsoft's managed, real-time event-ingestion service designed for high-throughput streaming scenarios, and it integrates natively with Azure Synapse Analytics (via the Synapse Event Hubs connector or Spark Structured Streaming) to land streaming data into dedicated SQL pools, Spark pools, or the lake. It is the canonical answer for streaming ingestion into Synapse.

Exam trap

DP-203 often tests the confusion between batch ingestion (Data Factory) and streaming ingestion (Event Hubs / IoT Hub / Kafka), so candidates who pick Data Factory for 'ingestion' miss the streaming requirement.

How to eliminate wrong answers

Option A is wrong because Azure Data Factory is a batch-oriented orchestration and ETL service — it can move data on schedules or triggers but is not designed for continuous, low-latency streaming ingestion. Option B is wrong because Azure Blob Storage is a storage layer, not an ingestion service; it can hold streamed data but does not itself ingest streams. Option D is wrong because Azure Analysis Services is a semantic/tabular modeling engine for BI, not a data-ingestion service.

Practice this question →
105
MCQmedium

You are building an Azure Data Factory pipeline that processes files from Azure Blob Storage. The pipeline uses a Mapping Data Flow to transform the data and then writes the output to Azure Data Lake Storage Gen2. You need to ensure that the Data Flow can handle schema drift, where incoming files may have additional columns not present in the initial schema. What should you configure in the Data Flow?

A.Enable 'Allow schema drift' in the source transformation and use 'Auto mapping' in the sink transformation.
B.Define a fixed schema in the source projection and use a Derived Column transformation to add new columns.
C.Use a Parameterized dataset and pass the schema as a parameter at runtime.
D.Set the source dataset to use a wildcard file path and enable 'Recursive' in the source options.
AnswerA

Enabling schema drift in the source allows the Data Flow to read columns that are not defined in the projection. Auto mapping in the sink ensures that any new columns are written to the destination. Together, they handle schema drift without manual intervention, which is required for this scenario.

Why this answer

In Mapping Data Flow, schema drift is enabled at the source transformation, allowing it to read columns not defined in the projection. The sink must also be configured to write those columns, typically using auto mapping. This combination ensures that additional columns are processed and persisted, which is necessary when incoming files have evolving schemas.

Exam trap

The trap here is confusing file path parameters or wildcards with schema drift handling, when schema drift is a specific Data Flow setting.

Practice this question →
106
MCQmedium

You are designing a data processing solution for a global company. Data must be processed in near real-time and aggregated by region. You need to minimize latency for downstream consumers. Which Azure service should you use for stream processing?

A.Azure Batch
B.Azure Stream Analytics
C.Azure Data Factory
D.Azure Synapse Pipelines
AnswerB

Azure Stream Analytics is a fully managed, serverless real-time engine with native windowing and temporal functions, so regional aggregations over streaming data are computed continuously with sub-second latency. This satisfies the near real-time processing and minimal downstream latency requirements without managing clusters.

Why this answer

Azure Stream Analytics is the correct choice because it is a fully managed stream processing engine designed for near real-time analytics on high-volume data streams. It can ingest data from sources like Azure Event Hubs or IoT Hub, apply SQL-based transformations, and output aggregated results to sinks such as Azure Synapse or Power BI with sub-second latency, meeting the requirement for minimal downstream latency.

Exam trap

The trap here is that candidates often confuse Azure Data Factory or Synapse Pipelines with stream processing because they support 'real-time' triggers, but these services are fundamentally batch-oriented and cannot achieve the sub-second latency required for continuous stream aggregation.

How to eliminate wrong answers

Option A is wrong because Azure Batch is a batch processing service for running large-scale parallel jobs, not designed for near real-time stream processing; it introduces significant latency due to job scheduling and queuing. Option C is wrong because Azure Data Factory is an ETL and data orchestration service focused on batch data movement and transformation, lacking native support for continuous stream processing. Option D is wrong because Azure Synapse Pipelines are built on the same orchestration engine as Data Factory and are intended for batch-oriented workflows, not for real-time stream aggregation.

Practice this question →
107
MCQmedium

You are developing an Azure Stream Analytics job that ingests telemetry from Azure Event Hubs and writes results to an Azure Synapse Analytics dedicated SQL pool. The job must compute a 5-minute tumbling window aggregation and write the aggregated rows to the dedicated SQL pool. You need to configure the output so that each window's aggregated rows are written efficiently. What should you do?

A.Configure the output to use Event Hubs and then use a Synapse pipeline to read from Event Hubs and write to the dedicated SQL pool.
B.Configure the output to use Blob Storage and then use an Azure Data Factory Copy activity to load the data into the dedicated SQL pool.
C.Configure the output to use Azure Synapse Analytics and specify the database and table. Set the batch size to a value that matches the expected number of rows per window.
D.Configure the output to use Azure SQL Database and then create a linked server in the dedicated SQL pool to pull the data.
AnswerC

The Azure Synapse Analytics output in Stream Analytics uses bulk insert via PolyBase or COPY, and the batch size controls how many rows are sent per bulk operation. Setting it appropriately for the 5-minute window volume ensures efficient writes and avoids excessive small transactions. This is the correct approach for this scenario.

Why this answer

Stream Analytics provides a native Azure Synapse Analytics output that uses bulk insert mechanisms. The batch size setting controls rows per bulk operation, which directly impacts write efficiency. Writing directly to the dedicated SQL pool avoids intermediate storage and extra processing steps, meeting the requirement for efficient writes of windowed aggregates.

Exam trap

The trap here is assuming that any output must go through Blob Storage or Event Hubs before reaching a dedicated SQL pool, when a direct output is available and more efficient.

Practice this question →
108
MCQhard

Your company uses Azure Synapse Analytics to run a large-scale batch processing job every night. The job currently runs on a dedicated SQL pool and takes 4 hours. Management wants to reduce the runtime to under 2 hours without increasing cost. The job involves heavy compute operations with no data movement limitations. What should you do?

A.Create materialized views on frequently queried tables.
B.Increase the service level objective (DWU) of the dedicated SQL pool.
C.Implement workload management to prioritize the job.
D.Enable result-set caching on the dedicated SQL pool.
AnswerB

Dedicated SQL pool compute scales linearly with data warehouse units, so doubling DWU from the current level roughly halves the four-hour runtime to about two hours. Because DWU is billed per hour, the higher rate applies for half the duration, keeping total cost broadly unchanged.

Why this answer

Increasing the service level objective (DWU) of the dedicated SQL pool provides more compute resources, directly reducing the runtime of compute-heavy batch jobs. Since the pool runs for fewer hours, the total cost (DWU × hours) may remain the same or even decrease, meeting the requirement to reduce runtime without increasing cost. Materialized views (A) and result-set caching (D) benefit repeated queries, not a unique nightly job.

Workload management (C) only prioritizes resources, without increasing overall compute power.

Exam trap

Candidates often assume that increasing DWU always increases cost, but total cost can remain constant when runtime decreases proportionally. They may also mistakenly believe materialized views or result-set caching can significantly speed up a large, non-repetitive batch job.

How to eliminate wrong answers

Option A is wrong because creating materialized views pre-aggregates data and can improve query performance, but it does not directly reduce runtime for the existing heavy compute operations without additional storage cost and may not address the specific repeated query patterns. Option B is wrong because increasing the DWU (service level objective) would scale up compute resources and reduce runtime, but it directly increases cost, violating the 'without increasing cost' constraint. Option C is wrong because workload management prioritizes concurrent jobs and allocates resources, but it does not reduce the total compute required for the job itself; it only affects scheduling and resource contention, not the runtime of a single large batch job.

Practice this question →
109
MCQhard

Refer to the exhibit. You have an Azure Data Factory pipeline that performs an incremental load from an Azure SQL Database source to a target Azure SQL Database. The pipeline uses a watermark column approach. After running the pipeline, you notice that the target table is empty. What is the most likely cause of this issue?

A.The dependency condition should be 'Completed' instead of 'Succeeded'.
B.The WatermarkQuery activity failed, causing the CopyData activity to be skipped.
C.The watermark query returns the maximum LastModified value, but the copy query uses the same value to filter, resulting in zero rows.
D.The CopyData activity runs before the WatermarkQuery activity completes.
AnswerC

Using the same watermark value in both the retrieval and the filter predicate excludes every row, because the copy query selects records strictly greater than that maximum. The watermark must be stored before the run and compared against the new maximum.

Why this answer

The most likely cause is that the watermark query returns the maximum LastModified value, and the copy query uses that same value to filter, resulting in zero rows. In a watermark-based incremental load, the copy query should filter for rows where the watermark column is greater than the last watermark value, not equal to it. Using the same value would only capture rows with that exact timestamp, which may be none if no new rows have that exact value.

Exam trap

DP-203 often tests the watermark pattern; candidates may overlook the comparison operator in the filter query, assuming that using the watermark value directly is correct, but it must be a greater-than comparison.

How to eliminate wrong answers

Option A is wrong because the dependency condition 'Completed' vs 'Succeeded' is not the issue; if the WatermarkQuery failed, the pipeline would not proceed if the condition is 'Succeeded', but the symptom is an empty target table, not a failed pipeline. Option B is wrong because if the WatermarkQuery activity failed, the CopyData activity would be skipped only if the dependency condition is set to 'Succeeded' and the query failed; but the question states the pipeline ran and the target is empty, implying the CopyData ran but copied zero rows. Option D is wrong because if the CopyData activity runs before the WatermarkQuery completes, it might use a stale watermark, but the most likely cause is the filter condition using the same value.

Practice this question →
110
MCQmedium

You have an Azure Databricks notebook that processes a large Delta table and must be orchestrated from Azure Data Factory on a schedule. The notebook accepts two parameters, the source path and a run date. You need the pipeline to pass these values at runtime and to surface notebook failures as pipeline failures. Which activity configuration should you use?

A.An Azure Function activity that triggers the notebook through a Databricks personal access token.
B.A Web activity that calls the Databricks Jobs API to submit a one-time run with notebook parameters.
C.A Databricks Notebook activity with base parameters defined as key-value pairs bound to pipeline parameters.
D.A Databricks Jar activity that reads parameters from environment variables set on the cluster.
AnswerC

The Databricks Notebook activity supports base parameters that are passed to the notebook as widgets, and those values can be bound to pipeline parameters or expressions. Failures returned by the notebook propagate to the activity and fail the pipeline run, satisfying both the parameter-passing and error-surfacing requirements in a single activity configuration.

Why this answer

The Databricks Notebook activity is the native orchestrator for running an existing notebook from Data Factory. Base parameters passed as key-value pairs become notebook widgets and can be bound to pipeline parameters, and the activity reports the notebook run outcome so a failed notebook fails the pipeline run as required.

Exam trap

The trap here is reaching for the Jobs API through a Web activity, which submits work asynchronously and does not translate notebook failure into pipeline failure without extra polling.

Practice this question →
111
Multi-Selecthard

Which THREE of the following are best practices for optimizing performance of Delta Lake tables in Azure Synapse Analytics? (Choose three.)

Select 3 answers
A.Run the OPTIMIZE command to compact small files and improve read performance.
B.Periodically run VACUUM to remove old versions of files that are no longer needed.
C.Partition the table on columns that are used in WHERE clauses to enable partition pruning.
D.Partition on high-cardinality columns like UserID to maximize parallelism.
E.Use Z-order on columns that are frequently used in filter predicates.
AnswersA, C, E

OPTIMIZE compacts many small files into larger ones, reducing per-file overhead and metadata pressure during reads. This satisfies the small-file performance constraint, since Delta Lake read latency grows with file count; bin compaction via OPTIMIZE restores efficient scan throughput in Synapse.

Why this answer

Option A is correct because the OPTIMIZE command compacts many small Parquet files into fewer, larger files, reducing per-file overhead and metadata work so that Delta Lake scans and reads run faster. Option C is correct because partitioning on columns commonly used in WHERE clauses lets the engine perform partition pruning, skipping entire directories of data that cannot match the filter and cutting I/O. Option E is correct because Z-ordering (for example, OPTIMIZE ...

ZORDER BY (col)) co-locates related data within files, so data-skipping statistics let queries with frequent filter predicates read far fewer rows. Option B is not a performance optimization: VACUUM deletes unreferenced old files to reclaim storage and control cost, and running it too aggressively can break time travel and concurrent readers. Option D is wrong because partitioning on high-cardinality columns such as UserID creates a huge number of tiny partitions and files, which increases metadata overhead and degrades performance rather than maximizing parallelism.

Exam trap

The trap here is that candidates often confuse high-cardinality partitioning with parallelism, not realizing that excessive partitions cause metadata bloat and slow down queries, while Z-order is a complementary technique for non-partition columns.

Practice this question →
112
MCQeasy

You have an Azure Databricks notebook that processes data from a Delta table. The notebook runs slowly due to many small files. You need to optimize the Delta table for faster reads. Which Delta Lake operation should you run?

A.Run CONVERT TO DELTA on the underlying Parquet files.
B.Run OPTIMIZE to compact small files.
C.Run DESCRIBE HISTORY to analyze file sizes.
D.Run VACUUM to delete old files.
AnswerB

OPTIMIZE compacts many small files into larger ones, reducing per-file overhead and metadata pressure so reads scan fewer files. This directly addresses the small-file problem causing the slow notebook, and it is the Delta Lake operation designed for bin compaction.

Why this answer

The OPTIMIZE command in Delta Lake compacts many small files into larger ones by rewriting data files based on the table's partitioning scheme. This reduces the number of files that need to be read during queries, significantly improving read performance. Since the notebook is slow due to many small files, OPTIMIZE directly addresses the root cause.

Exam trap

The trap here is that candidates confuse VACUUM (which cleans up old files) with OPTIMIZE (which compacts files), or think DESCRIBE HISTORY is a performance-tuning command rather than a diagnostic tool.

How to eliminate wrong answers

Option A is wrong because CONVERT TO DELTA is used to convert existing Parquet files into a Delta table format, not to compact small files within an already existing Delta table. Option C is wrong because DESCRIBE HISTORY only shows the transaction log of operations performed on the table, such as writes and compactions; it does not modify or optimize file sizes. Option D is wrong because VACUUM removes old, unreferenced data files that are no longer needed for time travel or rollback, but it does not compact or merge small files into larger ones.

Practice this question →
113
MCQhard

You are building a data processing pipeline in Azure Synapse Analytics. The pipeline should read data from Azure Data Lake Storage Gen2 (Parquet files), apply transformations using a mapping data flow, and write the results to a dedicated SQL pool table. The source data contains personally identifiable information (PII). You need to mask the PII columns (e.g., email) using a data masking function within the data flow. Which transformation should you use?

A.Derived Column transformation
B.Join transformation
C.Aggregate transformation
D.Pivot transformation
AnswerA

Derived Column can apply expressions, including hash functions like SHA2 for masking PII.

Why this answer

The Derived Column transformation in mapping data flows allows you to create new columns or modify existing ones using expressions, including built-in data masking functions like `mask()`, `maskEmail()`, or `substring()`. This is the correct transformation to apply PII masking on columns such as email addresses within the data flow pipeline before writing to the dedicated SQL pool.

Exam trap

The trap here is that candidates may confuse the Derived Column transformation with the Select transformation (which can also rename or drop columns but does not support expression-based masking), or assume that masking must be done in the sink (dedicated SQL pool) rather than within the data flow itself.

How to eliminate wrong answers

Option B (Join transformation) is wrong because it is used to combine rows from two sources based on a matching condition, not to mask or transform column values. Option C (Aggregate transformation) is wrong because it performs grouping and aggregation operations (e.g., SUM, COUNT) and does not support per-row data masking functions. Option D (Pivot transformation) is wrong because it rotates rows into columns for reshaping data, not for applying masking or transformations to individual column values.

Practice this question →
114
MCQmedium

You are building an Azure Synapse Analytics pipeline that uses a Mapping Data Flow to transform data from Azure Data Lake Storage Gen2. The data flow includes a derived column transformation that uses a custom expression to calculate a new field. You need to debug the data flow and preview the output at each transformation. Which feature should you use?

A.Add a Sink transformation and write to a temporary folder, then read the data manually.
B.Enable the 'Data flow debug' toggle and use the Data Preview tab in each transformation.
C.Run the pipeline with a trigger and check the output in the Monitor hub.
D.Use the 'Debug' button on the pipeline canvas and set breakpoints on the data flow activity.
AnswerB

Data flow debug mode allows you to interactively preview data at each transformation. By enabling the debug toggle and using the Data Preview tab, you can inspect the output of the derived column and verify the expression logic before publishing the pipeline.

Why this answer

Azure Synapse Mapping Data Flows provide a debug mode that enables interactive data preview at each transformation. By turning on the debug toggle and using the Data Preview tab, you can see the results of expressions and transformations immediately, which is essential for validating logic before deploying the pipeline.

Exam trap

The trap here is assuming that pipeline debugging or monitoring can replace data flow debug mode, when interactive preview is needed for transformation-level validation.

Practice this question →
115
MCQmedium

You are building a real-time dashboard that displays sales data from an Azure SQL Database. The dashboard must refresh every 30 seconds with minimal latency. You need to choose the appropriate Azure service for data processing and visualization. Which service should you use?

A.Azure Analysis Services with a tabular model and a scheduled refresh every 30 seconds.
B.Azure Data Explorer (ADX) with a continuous export to Power BI.
C.Power BI with DirectQuery mode and configure automatic page refresh.
D.Azure Synapse Serverless SQL pool with Power BI import mode.
AnswerC

DirectQuery pushes queries to Azure SQL Database at render time, so the dashboard reflects near-real-time data rather than a cached import. Automatic page refresh at 30-second intervals satisfies the minimal-latency, frequent-refresh constraint without scheduled dataset reloads.

Why this answer

Power BI with DirectQuery mode is the correct choice because it allows real-time queries directly against Azure SQL Database, supporting automatic page refresh every 30 seconds with minimal latency. Azure Analysis Services requires data processing and is not designed for sub-minute refreshes. Azure Data Explorer is optimized for time-series data, not for direct connection to Azure SQL Database for real-time dashboards.

Azure Synapse Serverless SQL pool is meant for querying data lakes, not for real-time visualization with frequent refreshes.

Practice this question →
116
MCQmedium

You are designing an Azure Synapse Analytics pipeline that uses a Mapping Data Flow to transform data from Azure Data Lake Storage Gen2. The data flow must handle schema drift, where new columns can appear in the source files over time. You need to ensure that the new columns are automatically included in the sink output without modifying the data flow. What should you do?

A.Enable Allow schema drift on the source transformation and use a derived column to map new columns.
B.Use a Select transformation to rename columns and enable schema drift on the sink only.
C.Enable Allow schema drift on the source and sink, and use the sink's automatic mapping.
D.Use a Flatten transformation and enable schema drift on the source.
AnswerC

Enabling Allow schema drift on both the source and sink allows the data flow to read new columns from the source and write them to the sink without explicit mapping. The sink's automatic mapping picks up the drifted columns at runtime. This satisfies the requirement to include new columns automatically without editing the data flow when the source schema changes.

Why this answer

Schema drift in Mapping Data Flows requires enabling Allow schema drift on both the source and the sink. The source reads new columns at runtime, and the sink's automatic mapping writes them to the destination without predefined column mappings. Using derived columns, Select, or Flatten transformations does not automatically propagate unknown columns and would require manual changes when the schema evolves.

Exam trap

The trap here is enabling schema drift only on the source or only on the sink, while both are required for new columns to flow through automatically.

Practice this question →
117
MCQeasy

You are building an Azure Data Factory pipeline that must copy data from an on-premises SQL Server to Azure Blob Storage. The pipeline runs on a schedule every hour. You need to ensure that the copy activity can securely access the on-premises SQL Server. What should you configure?

A.A VPN gateway between the on-premises network and Azure.
B.A self-hosted integration runtime installed on a machine in the on-premises network.
C.An Azure Integration Runtime with a managed virtual network.
D.An Azure SQL Database linked service with a private endpoint.
AnswerB

A self-hosted integration runtime acts as a bridge between Azure Data Factory and on-premises data sources. It is installed on a machine within the corporate network and can securely connect to on-premises SQL Server. This is the standard method for hybrid data movement, ensuring secure and reliable connectivity without exposing the on-premises server to the internet.

Why this answer

To copy data from an on-premises SQL Server, Azure Data Factory requires a self-hosted integration runtime. This component is installed on a machine in the on-premises network and handles the data movement securely. Other options either do not provide on-premises connectivity or are meant for different Azure services.

Exam trap

The trap here is confusing network connectivity (like VPN) with the specific integration runtime needed for data factory to access on-premises data.

Practice this question →
118
MCQmedium

You are using Azure Data Factory to copy data from an Azure SQL Database to an Azure Data Lake Storage Gen2 account. The copy activity is failing intermittently with a timeout error. You need to improve the throughput and reliability of the copy operation. What should you do?

A.Increase the degree of copy parallelism and enable staged copy.
B.Configure the copy activity to use a single thread with a larger batch size.
C.Set the copy activity's fault tolerance to skip incompatible rows.
D.Change the source dataset to use a stored procedure that returns all rows at once.
AnswerA

Increasing the degree of copy parallelism allows multiple concurrent connections to the source, improving throughput. Enabling staged copy uses a temporary staging area in Blob Storage or ADLS Gen2 to decouple extraction and loading, which can improve reliability and performance for large datasets. This combination addresses both throughput and intermittent timeouts.

Why this answer

Intermittent timeouts often occur when a single connection cannot sustain the load. Increasing the degree of copy parallelism opens more concurrent connections, distributing the load. Staged copy separates the read and write phases, reducing the chance of timeouts during the write to ADLS Gen2.

Together, these settings improve both throughput and reliability for large-scale copies.

Exam trap

The trap here is assuming that fault tolerance or larger batch sizes solve timeouts, when the real fix is increasing parallelism and using staging.

Practice this question →
119
MCQhard

You are processing a large dataset in Azure Synapse Analytics using a dedicated SQL pool. You need to load data from Parquet files in Azure Data Lake Storage Gen2 into a staging table, then transform and load into a fact table. The fact table is partitioned by date. You want to maximize query performance and minimize data movement. Which technique should you use?

A.Use Azure Data Factory Copy activity to load data into the fact table, then run an UPDATE statement to transform
B.Use CETAS to export the transformed data to Azure Data Lake Storage Gen2, then use PolyBase to load into the fact table
C.Use BULK INSERT to load data directly into the fact table
D.Use PolyBase to load data into a staging table, then use CREATE TABLE AS SELECT (CTAS) to transform and load into the fact table
AnswerD

PolyBase efficiently loads Parquet files from Azure Data Lake Storage Gen2 into a staging table. CTAS then creates a new table with the transformed data, which can be partitioned and distributed optimally. This approach minimizes data movement because CTAS writes directly to the new table, and you can define the distribution and partitioning to match the fact table's requirements, improving query performance.

Why this answer

PolyBase is the most efficient way to load Parquet files into a staging table in a dedicated SQL pool. CTAS then transforms and loads the data into a new table, allowing you to define the distribution and partitioning to optimize query performance. This minimizes data movement because the transformation and load happen within the SQL pool.

The other options either use unsupported features, cause inefficient updates, or add unnecessary data movement.

Exam trap

The trap here is assuming that BULK INSERT supports Parquet files; it does not, and using it would fail or require conversion.

Practice this question →
120
MCQmedium

You are designing a streaming solution in Azure Synapse Analytics using the serverless SQL pool to query streaming data in real-time. The data is ingested via Azure Event Hubs and processed using Azure Stream Analytics. The output of Stream Analytics is written to Azure Data Lake Storage Gen2 in Delta Lake format. You need to ensure that the serverless SQL pool can query the latest data with minimal latency. Which approach should you use?

A.Ingest data directly from Event Hubs into serverless SQL pool using CETAS (CREATE EXTERNAL TABLE AS SELECT).
B.Use a materialized view in serverless SQL pool that refreshes every minute.
C.Load the streaming data into a dedicated SQL pool using a scheduled pipeline and then query it from serverless SQL pool.
D.Create an external table in serverless SQL pool that points to the Delta Lake folder and query it directly.
AnswerD

Serverless SQL pool supports Delta Lake format and can query it as soon as data is written.

Why this answer

Serverless SQL pool can directly query Delta Lake format files stored in Azure Data Lake Storage Gen2 by creating an external table with LOCATION pointing to the Delta folder. This allows real-time querying of the latest streaming data without any data movement or transformation, achieving minimal latency since Stream Analytics writes continuously to Delta Lake.

Exam trap

The trap here is that candidates may confuse serverless SQL pool with dedicated SQL pool and assume features like materialized views or scheduled pipelines are available, or they may think CETAS can directly ingest from Event Hubs, which is not supported.

How to eliminate wrong answers

Option A is wrong because CETAS creates a new external table by selecting data from a source, but it cannot ingest data directly from Event Hubs; it requires a source like an existing external table or file set, and it does not support real-time streaming ingestion. Option B is wrong because serverless SQL pool does not support materialized views; materialized views are a feature of dedicated SQL pool, not serverless. Option C is wrong because loading data into a dedicated SQL pool via a scheduled pipeline introduces batch latency (scheduled intervals), which contradicts the requirement for minimal latency in a streaming solution.

Practice this question →
121
MCQeasy

You need to transform semi-structured JSON data into a tabular format for analysis in Azure Synapse Analytics. The data is stored in ADLS Gen2. Which feature should you use to query the JSON data directly without loading it into a table?

A.Use the OPENROWSET function in a serverless SQL pool.
B.Use Azure Data Factory to flatten the JSON and store as Parquet.
C.Create an external table using PolyBase.
D.Use the COPY INTO command in a dedicated SQL pool.
AnswerA

OPENROWSET in a serverless SQL pool queries JSON files directly from ADLS Gen2, using WITH clauses to shred semi-structured documents into relational columns. This satisfies the stem's constraint of querying without loading into a table, since serverless pools read files in place and charge only for data processed.

Why this answer

OPENROWSET in Synapse serverless SQL can query JSON files directly. Option B (Azure Data Factory) flattens JSON but is an orchestration tool, not a direct query method. Option C (PolyBase) requires external tables.

Option D (COPY INTO) loads data into a table, not direct query.

Practice this question →
122
MCQhard

You are developing an Azure Data Factory pipeline that processes data from an on-premises SQL Server. The pipeline uses a self-hosted integration runtime. You need to ensure that the pipeline can handle schema changes in the source table without failing. What should you do?

A.Configure the Copy activity to use a stored procedure that dynamically generates the column list.
B.Use a Mapping Data Flow with 'Allow schema drift' enabled and a sink that supports schema evolution.
C.Enable the 'Allow schema drift' option in the Copy activity source settings.
D.Set the 'Auto create table' option in the sink dataset to handle new columns.
AnswerB

Mapping Data Flows support schema drift, allowing the flow to handle new or missing columns at runtime. When enabled, the data flow can process source data with varying schemas without failing. Combined with a sink like Delta Lake or a database that supports schema evolution, this provides a robust solution for schema changes.

Why this answer

Mapping Data Flows with 'Allow schema drift' enabled can dynamically handle schema changes by reading and writing columns that are not predefined. This prevents pipeline failures due to new or removed columns. When paired with a sink that supports schema evolution, such as Delta Lake, the data flow can automatically adapt to changing schemas, ensuring continuous data processing.

Exam trap

The trap here is confusing the schema drift capability of Mapping Data Flows with features of the Copy activity, which does not support automatic schema drift.

Practice this question →
123
Multi-Selectmedium

You are designing a batch processing solution in Azure Synapse Analytics using pipelines. The solution must load data from multiple sources (Azure Blob Storage, Azure SQL Database, and REST API) into a dedicated SQL pool. After loading, you need to run a stored procedure to aggregate the data. Which two activities should you include in the pipeline? (Choose two.)

Select 2 answers
A.Execute Pipeline activity
B.Copy activity
C.Azure Function activity
D.Stored procedure activity
E.Data Flow activity
AnswersB, D

The Copy activity moves data from each source (Blob Storage, Azure SQL Database, REST API) into the dedicated SQL pool, satisfying the multi-source ingestion requirement. A subsequent activity then invokes the stored procedure for aggregation.

Why this answer

The Copy activity (B) is correct because it is the native Azure Synapse Analytics/Data Factory pipeline activity designed to ingest data from disparate sources such as Azure Blob Storage, Azure SQL Database, and REST API connectors into a dedicated SQL pool sink, handling schema mapping and parallel loading. The Stored procedure activity (D) is correct because it invokes a stored procedure in the dedicated SQL pool after the load completes, which is exactly what is needed to run the aggregation logic on the loaded data. The Execute Pipeline activity (A) only calls another pipeline and does not itself move or transform data, so it does not satisfy the load or aggregation requirement.

The Azure Function activity (C) runs custom code in an Azure Function, which is unnecessary here since built-in Copy and Stored procedure activities cover the scenario. The Data Flow activity (E) performs code-free transformations in a Spark-based execution engine, but the requirement is to load data and then aggregate via a stored procedure, not to transform data in a mapping data flow.

Practice this question →
124
MCQmedium

You are developing an Azure Databricks notebook that processes streaming data from Azure Event Hubs. The notebook must write the processed data to a Delta table. You need to ensure that the stream can handle late data and update previously written records. Which Delta Lake feature should you use?

A.Use the append mode to write new records to the Delta table.
B.Use the ignore mode to skip writing if the table already exists.
C.Use the overwrite mode to replace the entire Delta table with the new batch.
D.Use the MERGE INTO statement to upsert records into the Delta table.
AnswerD

The MERGE INTO statement allows you to perform upserts (inserts, updates, and deletes) on a Delta table based on a matching condition. In streaming scenarios, you can use foreachBatch to apply MERGE INTO, enabling updates to existing records when late data arrives. This supports the requirement to update previously written records and handle late data effectively.

Why this answer

To handle late data and update existing records in a Delta table from a stream, you should use MERGE INTO within foreachBatch. This allows upserts based on a unique key, ensuring that late-arriving events update the correct records. Append, overwrite, and ignore modes do not provide the required update capability.

Exam trap

The trap here is assuming that append mode is sufficient for streaming writes, without considering the need to update existing records for late data.

Practice this question →
125
MCQhard

You are designing a batch processing solution using Azure Databricks. The data source is a large Parquet dataset stored in Azure Data Lake Storage Gen2 (ADLS Gen2). The processing requires joining two datasets: one with 10 billion rows and another with 1 million rows. The cluster uses Photon runtime. Which optimization should you apply to minimize shuffle?

A.Broadcast the smaller table (1 million rows) to all worker nodes.
B.Increase the cluster size to reduce shuffle overhead.
C.Create bucketed tables on the join key for both datasets.
D.Use Delta Lake and optimize file layout with OPTIMIZE command.
AnswerA

Broadcasting the smaller table avoids shuffling the large table, significantly reducing data movement.

Why this answer

Broadcasting the smaller table (1 million rows) to all worker nodes is the correct optimization because it eliminates the need for a full shuffle during the join. With Photon runtime, broadcast joins are highly efficient as they replicate the small table to each executor, allowing map-side joins that avoid costly data movement across the network. Given the 10:1 row ratio, the 1-million-row table is well within the default broadcast threshold (10 MB compressed, configurable via spark.sql.autoBroadcastJoinThreshold), making this the most effective shuffle-minimization technique.

Exam trap

The trap here is that candidates often assume increasing cluster size (Option B) is a universal performance fix, but the DP-203 exam specifically tests the understanding that shuffle reduction techniques like broadcast joins are more impactful than simply adding more nodes, especially when one dataset is small enough to fit in executor memory.

How to eliminate wrong answers

Option B is wrong because increasing cluster size does not reduce shuffle overhead; it only adds more parallelism, which can actually increase shuffle traffic and does not address the fundamental need to avoid shuffling large datasets. Option C is wrong because creating bucketed tables on the join key requires both datasets to be bucketed with the same number of buckets and a compatible bucketing scheme; while this can reduce shuffle, it involves significant upfront data reorganization and is not as immediate or lightweight as broadcasting the small table. Option D is wrong because using Delta Lake and the OPTIMIZE command improves file layout and read performance (e.g., bin-packing small files) but does not directly reduce shuffle during a join operation; shuffle reduction requires join-specific optimizations like broadcast or bucketing.

Practice this question →
126
MCQmedium

You are building an Azure Stream Analytics job that reads JSON events from an Azure Event Hub. Each event contains a nested array property named 'readings' with multiple sensor values. You need to output one row per sensor reading to an Azure Synapse Analytics dedicated SQL pool. The query must flatten the array. Which query syntax should you use?

A.SELECT deviceId, readings.value FROM input UNNEST(input.readings) AS readings
B.SELECT deviceId, reading FROM input CROSS APPLY GetArrayElements(input.readings) AS reading
C.SELECT deviceId, EXPLODE(input.readings) AS value FROM input
D.SELECT deviceId, reading.value FROM input CROSS APPLY GetArrayElements(input.readings) AS reading
AnswerD

This is correct because Azure Stream Analytics supports GetArrayElements as a streaming function that returns a table of array elements, and CROSS APPLY is the documented way to flatten a nested array into multiple rows. The alias reading exposes the element via reading.value, producing one output row per sensor reading, which matches the required one-row-per-reading output to the dedicated SQL pool.

Why this answer

Azure Stream Analytics flattens nested arrays using the built-in GetArrayElements function combined with CROSS APPLY. GetArrayElements returns a table with a 'value' column for each element, and CROSS APPLY expands those elements into separate rows. Referencing reading.value projects the scalar sensor value, producing one output row per reading as required.

Exam trap

The trap here is assuming that ANSI SQL set-returning functions such as UNNEST or Spark-style EXPLODE work in Azure Stream Analytics, when only GetArrayElements with CROSS APPLY is supported.

Practice this question →
127
Multi-Selecteasy

Which TWO are benefits of using Azure Databricks Auto Loader for incremental data ingestion?

Select 2 answers
A.It can process new files as they arrive in cloud storage.
B.It can handle large volumes of data without manual checkpointing.
C.It automatically evolves the schema without any configuration.
D.It provides sub-second latency for real-time streaming.
E.It provides built-in deduplication of records.
AnswersA, B

Auto Loader uses cloud-native file notification or directory listing to detect new files as they land in storage, satisfying the incremental ingestion requirement. This streaming mechanism avoids reprocessing existing data, so arriving files are picked up automatically without manual intervention.

Why this answer

Option A is correct because Auto Loader is designed to incrementally and efficiently detect and process new files as they land in cloud storage (e.g., Azure Data Lake Storage or Blob Storage) using file notification or directory listing modes, making it ideal for streaming ingestion of arriving data. Option B is correct because Auto Loader manages the ingestion state, including checkpoints and the schema/state tracking, so it can scale to large volumes of files without requiring manual checkpoint management by the user. Option C is not correct because while Auto Loader supports schema inference and schema evolution, evolution typically requires enabling and configuring options such as cloudFiles.schemaEvolutionMode and related settings, so it is not automatic without any configuration.

Option D is not correct because Auto Loader is a file-based incremental ingestion mechanism and does not guarantee sub-second latency real-time streaming. Option E is not correct because Auto Loader does not provide built-in record-level deduplication; deduplication must be implemented separately, for example with dropDuplicates or Delta Lake MERGE logic.

Exam trap

The trap here is that candidates often confuse Auto Loader's schema inference (which is automatic on first read) with automatic schema evolution (which requires explicit configuration), and they also mistakenly assume file-based ingestion can achieve sub-second latency or provide built-in deduplication, which are not features of this service.

Practice this question →
128
Multi-Selecteasy

Which TWO Azure services can be used to perform data transformation in a data pipeline? (Select two.)

Select 2 answers
A.Azure Data Factory
B.Azure Monitor
C.Azure Storage
D.Azure Event Hubs
E.Azure Databricks
AnswersA, E

Data Factory offers mapping data flows and compute activities for transformation.

Why this answer

Azure Data Factory is a cloud-based ETL service that provides a code-free visual interface for orchestrating data movement and transformation at scale. It supports data flows, which allow you to perform transformations like aggregations, joins, and filtering without writing code, making it a correct choice for data transformation in a pipeline.

Exam trap

The trap here is that candidates often confuse data ingestion services (like Event Hubs) or storage services (like Azure Storage) with transformation services, forgetting that transformation requires compute engines like Data Factory or Databricks.

Practice this question →
129
Multi-Selecthard

Which THREE components are required to implement a real-time data processing solution using Azure Stream Analytics?

Select 3 answers
A.Power BI as the output sink
B.Azure Data Factory pipeline for orchestration
C.An input source such as Azure Event Hubs or IoT Hub
D.An output sink such as Azure Synapse Analytics or Blob Storage
E.A Stream Analytics job with a defined query
AnswersC, D, E

Streaming input is required for real-time processing.

Why this answer

Azure Stream Analytics requires a streaming input source to ingest real-time data. Azure Event Hubs and IoT Hub are the primary services that provide high-throughput, low-latency event ingestion, which Stream Analytics can consume via its built-in connector. Without a streaming input, the job cannot process real-time data.

Exam trap

The trap here is that candidates often assume Power BI is a required output for real-time dashboards, but Stream Analytics can function without any visualization sink, and the exam focuses on the minimal required components: input, job with query, and output sink.

Practice this question →
130
MCQmedium

You are designing a data processing solution for an e-commerce company. The company receives millions of clickstream events per hour from their website and needs to aggregate the data by product category and windowed time intervals for real-time dashboards. You need to minimize latency and cost. Which service should you use?

A.Azure Databricks Structured Streaming
B.Azure Data Factory
C.Azure Stream Analytics
D.Azure Synapse Pipelines
AnswerC

Stream Analytics performs windowed, stateful aggregation over streaming input with built-in tumbling, hopping and sliding windows, aggregating clickstream events by product category in near real time. Its consumption-based pricing keeps cost low for continuous high-volume dashboards.

Why this answer

(Azure Stream Analytics) is the best choice because it is purpose-built for real-time stream processing, supports windowed aggregations, and integrates with Power BI for dashboards. Option A (Azure Databricks Structured Streaming) can handle streaming but is more complex and typically more expensive for simple aggregations. Option B (Azure Data Factory) is for batch data movement, not real-time.

Option D (Azure Synapse Pipelines) is for orchestrating data movement, not real-time processing.

Practice this question →
131
MCQhard

You have an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline uses a self-hosted integration runtime. You notice that the copy activity fails intermittently with the error: 'Failure happened on 'Source' side. ErrorCode=SqlOperationFailed'. The on-premises SQL Server is under heavy load during business hours. What is the most likely cause?

A.The SQL Server is experiencing resource contention or timeout due to heavy load.
B.The Azure Blob Storage account is throttling requests.
C.The authentication method to SQL Server is incorrect.
D.The self-hosted integration runtime is not connected to the network.
AnswerA

Under heavy business-hours load, the on-premises SQL Server cannot service the copy activity's queries within the timeout window, so the source-side SqlOperationFailed error surfaces intermittently. The self-hosted integration runtime simply relays this; the constraint is server resource contention, not connectivity or credentials.

Why this answer

The error 'SqlOperationFailed' on the source side indicates that the SQL Server itself is failing to complete the query or data extraction operation. Under heavy load, the SQL Server may experience resource contention (CPU, memory, I/O) or reach query timeout thresholds, causing the copy activity to fail intermittently. This is consistent with the described scenario of heavy load during business hours.

Exam trap

The trap here is that candidates may confuse a source-side error with a sink-side error, or assume that any intermittent failure must be a network or connectivity issue, rather than recognizing that SQL Server resource contention under heavy load is a classic cause of intermittent 'SqlOperationFailed' errors.

How to eliminate wrong answers

Option B is wrong because Azure Blob Storage throttling would produce an error on the 'Sink' side (e.g., 'StorageError' or 'BlobOperationFailed'), not on the 'Source' side. Option C is wrong because an incorrect authentication method would cause a persistent authentication failure (e.g., 'Login failed for user') on every attempt, not intermittent failures. Option D is wrong because if the self-hosted integration runtime were not connected to the network, the pipeline would fail consistently with a connectivity error (e.g., 'Unable to connect to Integration Runtime'), not an intermittent SQL operation error.

Practice this question →
132
MCQeasy

You are designing a data processing pipeline in Azure Data Factory. The pipeline must copy data from Azure Blob Storage to Azure SQL Database and transform the data using a mapping data flow. The data flow includes a Derived Column transformation. What is the purpose of the Derived Column transformation?

A.Aggregate data by grouping rows.
B.Create new columns or modify existing columns using expressions.
C.Sort data in ascending or descending order.
D.Rename or drop columns.
AnswerB

The Derived Column transformation generates new columns or overwrites existing ones by evaluating expression-language formulas against incoming stream data, satisfying the pipeline's requirement to transform data within the mapping data flow. Unlike Select, which only renames or drops columns, it computes values, enabling calculated fields during the Blob Storage to Azure SQL Database copy.

Why this answer

The Derived Column transformation in Azure Data Factory mapping data flows is used to create new columns or modify existing columns by applying expressions. This allows you to perform calculations, string manipulations, or conditional logic directly within the data flow, enabling in-flight data transformation before writing to the sink.

Exam trap

The trap here is that candidates confuse the Derived Column transformation with the Select transformation, assuming it is used for renaming or dropping columns, when in fact Derived Column is specifically for creating or modifying column values via expressions.

How to eliminate wrong answers

Option A is wrong because aggregating data by grouping rows is the purpose of the Aggregate transformation, not the Derived Column transformation. Option C is wrong because sorting data is performed by the Sort transformation, which reorders rows based on column values. Option D is wrong because renaming or dropping columns is handled by the Select transformation, which allows you to include, exclude, or alias columns.

Practice this question →
133
MCQhard

You are implementing a data processing solution in Azure Databricks. The solution reads JSON files from Azure Data Lake Storage Gen2, performs complex transformations using PySpark, and writes the results to a Delta table. You need to ensure that the write operation is idempotent and can recover from failures without duplicating data. Which approach should you use?

A.Use the merge operation with a unique key to upsert records into the Delta table.
B.Write the output in append mode and use a deduplication step after each run.
C.Write the output using the overwrite mode to replace the entire Delta table on each run.
D.Write the output to a temporary Parquet file and then use a Copy activity to move it to the Delta table.
AnswerA

Delta Lake's merge operation allows you to upsert records based on a unique key, making the write idempotent. If the job fails and is rerun, the merge will update existing records and insert new ones without creating duplicates. This satisfies the requirement for idempotent and recoverable writes.

Why this answer

The merge operation in Delta Lake enables upserts based on a unique key, ensuring that rerunning the job after a failure does not duplicate data. It provides ACID transactions and idempotence, which are critical for reliable data processing. Append mode and overwrite mode do not offer the same guarantees, and temporary files with Copy activity lack transactional integrity.

Exam trap

The trap here is assuming that append mode with post-deduplication is sufficient for idempotence, when merge is the designed mechanism for upserts.

Practice this question →
134
MCQhard

You are implementing a Spark Structured Streaming job in Azure Databricks that reads from an Azure Event Hubs topic and writes to a Delta table. The job must handle late-arriving data up to 10 minutes and aggregate counts per device every 5 minutes. Which combination of settings should you use?

A.Use a tumbling window of 5 minutes and set watermark to 10 minutes on the event timestamp.
B.Use a sliding window of 5 minutes with a 10-minute slide interval and set watermark to 5 minutes.
C.Use a tumbling window of 10 minutes and set watermark to 5 minutes.
D.Use a hopping window of 5 minutes with a 5-minute hop and set watermark to 10 minutes.
AnswerA

A tumbling window of 5 minutes creates non-overlapping aggregation intervals, and a watermark of 10 minutes allows late data up to that delay to be included in the correct window. This matches the requirement for 5-minute counts and tolerance for 10-minute late arrivals. Watermarking also enables state cleanup for long-running streams, preventing unbounded state growth.

Why this answer

A tumbling window of 5 minutes with a 10-minute watermark on the event timestamp satisfies both the aggregation interval and the late-data tolerance. The tumbling window ensures non-overlapping 5-minute counts, and the watermark allows events up to 10 minutes late to be included. Other options use incorrect window sizes, slide intervals, or watermark durations that fail the requirements.

Exam trap

The trap here is mixing up the window duration with the watermark duration; the watermark must be at least as long as the maximum expected late arrival, while the window defines the aggregation period.

Practice this question →
135
MCQeasy

You need to orchestrate a data pipeline that includes a Python script and a Data Flow in Azure Synapse Analytics. The Python script must run before the Data Flow. Which activity should you use to run the Python script?

A.Notebook activity configured to use a Python kernel
B.Web activity
C.Stored Procedure activity
D.HDInsight Hive activity
AnswerA

A Notebook activity runs the Python script in a Spark pool with a Python kernel, and its success output can be wired as the dependency that gates the subsequent Data Flow activity, satisfying the required run-before ordering.

Why this answer

A Notebook activity in Azure Synapse Analytics can be configured to use a Python kernel, allowing you to run a Python script directly within the pipeline. This is the correct choice because the requirement is to execute a Python script before a Data Flow, and the Notebook activity supports Python execution natively in Synapse pipelines.

Exam trap

The trap here is that candidates may confuse a Notebook activity with a Web activity or a Stored Procedure activity, thinking they can execute arbitrary code, but only the Notebook activity supports Python execution natively in Synapse pipelines.

How to eliminate wrong answers

Option B is wrong because a Web activity calls an HTTP/S endpoint (e.g., a REST API) and cannot run a Python script directly; it is used for invoking external services, not for executing code within Synapse. Option C is wrong because a Stored Procedure activity executes SQL stored procedures in a database, which is not designed for running Python scripts. Option D is wrong because an HDInsight Hive activity runs Hive queries on an HDInsight cluster, not Python scripts; it is meant for HiveQL, not Python execution.

Practice this question →
136
MCQmedium

You are using Azure Synapse Analytics dedicated SQL pool to process large fact tables. You need to improve query performance for joins between a large fact table and a small dimension table. The dimension table is less than 2 GB. What should you do?

A.Replicate the dimension table to all distributions.
B.Round-robin distribute the dimension table.
C.Create a clustered columnstore index on the dimension table.
D.Hash distribute the dimension table on the join key.
AnswerA

In a dedicated SQL pool, replicated tables are copied to every distribution. For small dimension tables under 2 GB, replication eliminates data movement during joins with large fact tables, improving performance. This is the recommended strategy for star schema joins.

Why this answer

Replicating a small dimension table in a dedicated SQL pool ensures each distribution has a local copy, eliminating data movement during joins with large fact tables. Hash and round-robin distributions do not guarantee colocation, and columnstore indexes do not solve data movement.

Exam trap

The trap here is focusing on indexing or distribution methods that do not eliminate data movement for joins with small tables.

Practice this question →
137
MCQeasy

You are running a Spark job in Azure Synapse Analytics that reads from a Delta Lake table and performs multiple transformations. The job fails with an out-of-memory error on the executors. Which action should you take first to resolve the issue?

A.Enable checkpointing to truncate the lineage.
B.Decrease the number of partitions to reduce overhead.
C.Increase the executor memory setting in the Spark configuration.
D.Use the cache() action on intermediate DataFrames.
AnswerC

Executor out-of-memory errors arise when each executor's JVM heap cannot hold the partition data during transformations. Raising spark.executor.memory gives those executors more heap, directly relieving the constraint. Partition tuning or skew handling may follow, but increasing memory is the quickest first action.

Why this answer

An out-of-memory error on executors indicates that the available memory per executor is insufficient for the data being processed. Increasing the executor memory setting in the Spark configuration directly addresses this by allocating more heap space, allowing transformations to complete without spilling to disk or failing. This is the first and most straightforward action to take before optimizing partitioning or caching.

Exam trap

The trap here is that candidates often confuse memory issues with partitioning or caching optimizations, but the immediate fix for an out-of-memory error is to increase executor memory, not to reduce parallelism or persist data.

How to eliminate wrong answers

Option A is wrong because checkpointing truncates the lineage and helps with recovery and plan optimization, but it does not directly increase available memory or resolve an out-of-memory error. Option B is wrong because decreasing the number of partitions reduces parallelism and can actually increase memory pressure per partition, worsening the out-of-memory issue. Option D is wrong because using cache() persists intermediate DataFrames in memory, which consumes additional memory and can exacerbate the out-of-memory error rather than resolving it.

Practice this question →
138
MCQeasy

Your team is developing a data processing solution that uses Azure Databricks to transform streaming data from Azure Event Hubs. The transformation includes joining the stream with a static reference table stored in Azure Data Lake Storage Gen2. You need to implement the join efficiently. Which approach should you use?

A.Use a watermark on both sides and perform a stream-stream join
B.Use a broadcast join with the static DataFrame loaded from Delta Lake
C.Use foreachBatch to micro-batch the stream and perform a batch join
D.Use a stream-stream join by converting the static table to a stream
AnswerB

The static reference table is small, so broadcasting it to every executor node eliminates the shuffle required by a sort-merge join. Loading it from Delta Lake gives a cached, versioned DataFrame, making the stream-to-static join efficient.

Why this answer

A broadcast join is the most efficient approach when joining a streaming DataFrame with a static reference table. The static table is loaded as a DataFrame from Delta Lake, and Spark broadcasts it to all executors, avoiding a shuffle of the large streaming data. This is ideal because the static table is typically small enough to fit in memory, and it eliminates the need for watermarking or state management required in stream-stream joins.

Exam trap

DP-203 often tests the misconception that stream-stream joins are always required for joining streaming data, but when one side is static, a broadcast join is more efficient and simpler.

How to eliminate wrong answers

Option A is wrong because stream-stream joins require watermarks on both sides to handle late data and state cleanup, which adds complexity and latency, and is unnecessary when one side is static. Option C is wrong because foreachBatch processes data in micro-batches, which can be less efficient and does not leverage Spark's built-in broadcast join optimization for streaming-static joins. Option D is wrong because converting the static table to a stream forces a stream-stream join, which requires watermarks and stateful processing, increasing overhead and complexity.

Practice this question →
139
MCQhard

Refer to the exhibit. You have an Azure Synapse Analytics workspace. You need to ensure that data processing jobs can access the Data Lake Storage Gen2 account using a managed identity. What should you do?

A.Use the SQL admin login credentials to access the storage account
B.Enable the system-assigned managed identity on the Synapse workspace and assign it the 'Storage Blob Data Contributor' role on the storage account
C.Create a private endpoint connection between the workspace and the storage account
D.Configure the storage account firewall to allow access from the Synapse workspace
AnswerB

A system-assigned managed identity gives the Synapse workspace a Microsoft Entra ID service principal, and granting it Storage Blob Data Contributor on the account authorises read and write access to blob data without storing secrets.

Why this answer

Azure Synapse Analytics supports system-assigned managed identities, which provide a secure, passwordless authentication method for accessing Azure Data Lake Storage Gen2. By enabling the managed identity on the Synapse workspace and assigning it the 'Storage Blob Data Contributor' role, you grant the workspace's data processing jobs the necessary permissions to read, write, and delete data in the storage account without managing credentials.

Exam trap

The trap here is that candidates often confuse network-level access controls (firewall rules or private endpoints) with identity-based authorization (RBAC), mistakenly thinking that allowing network traffic alone is sufficient for data access.

How to eliminate wrong answers

Option A is wrong because using SQL admin login credentials to access a storage account is not supported; SQL authentication is for database access, not for Azure Storage RBAC. Option C is wrong because creating a private endpoint ensures network-level isolation and private connectivity, but it does not grant the identity permissions to access the storage account; RBAC role assignment is still required. Option D is wrong because configuring the storage account firewall to allow access from the Synapse workspace only controls network traffic, not authentication or authorization; the managed identity still needs the appropriate RBAC role to perform data operations.

Practice this question →
140
MCQeasy

You are using Azure Databricks to process a large dataset stored in Azure Data Lake Storage Gen2. The data is in Parquet format and you need to optimize read performance for a query that filters on a specific column. What should you do?

A.Cache the entire dataset in memory using the Databricks cache.
B.Increase the number of shuffle partitions in the Spark session configuration.
C.Convert the Parquet files to CSV format to enable predicate pushdown.
D.Partition the data by the filter column when writing the Parquet files.
AnswerD

Partitioning the data by the filter column organizes files into directories based on column values. When querying with a filter on that column, Databricks can prune irrelevant partitions, reading only the necessary data. This significantly reduces I/O and improves performance for large datasets. It is a standard optimization technique for Parquet in data lakes.

Why this answer

Partitioning Parquet data by the filter column allows Spark to skip reading irrelevant partitions, drastically reducing I/O. This is a fundamental optimization for large datasets in data lakes. Other options either do not address the read pattern or could worsen performance.

Partitioning is a best practice for improving query performance on filtered columns.

Exam trap

The trap here is thinking that caching or shuffle tuning can replace the need for physical data organization like partitioning for filter queries.

Practice this question →
141
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.

Practice this question →
142
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.

Practice this question →
143
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.

Practice this question →
144
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.

Practice this question →
145
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.

Practice this question →
146
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.

Practice this question →
147
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.

Practice this question →
148
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.

Practice this question →
149
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.

Practice this question →
150
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.

Practice this question →
← PreviousPage 2 of 3 · 185 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Develop data processing questions.

Start 20-question session