Courseiva

CCNA Develop data processing Questions

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

Practice these questionsDomain overviewAll questions
1
MCQhard

Your company runs a streaming job in Azure Stream Analytics that ingests data from Event Hubs and outputs to Azure Synapse Analytics. The job is failing with a 'Watermark delay' alert and the output to Synapse is delayed by over 30 minutes. The input rate is 5,000 events per second. The job uses a 1-minute tumbling window. What is the most likely cause of the delay?

A.The output schema in Synapse does not match the Stream Analytics output.
B.The Event Hubs has a large number of late-arriving events.
C.The tumbling window size is too large.
D.The Stream Analytics job is under-provisioned in terms of Streaming Units (SUs).
AnswerD

Insufficient Streaming Units cap the job's processing throughput, so it cannot keep pace with 5,000 events per second. The backlog grows, inflating watermark delay and delaying Synapse output beyond 30 minutes. Scaling SUs raises parallel processing capacity, clearing the bottleneck and restoring timely windowed output.

Why this answer

A watermark delay alert in Azure Stream Analytics indicates that the job is falling behind in processing incoming data. With an input rate of 5,000 events per second and a 1-minute tumbling window, the job requires sufficient Streaming Units (SUs) to keep up. Under-provisioned SUs cause backpressure, leading to output delays exceeding 30 minutes.

Exam trap

The trap here is that candidates may confuse a watermark delay alert with late-arriving events (Option B), but the alert indicates the job is falling behind overall, not just handling late data, and the 30-minute delay points to insufficient compute resources rather than data timing issues.

How to eliminate wrong answers

Option A is wrong because a schema mismatch between Stream Analytics output and Synapse would cause data write errors or failures, not a watermark delay alert or a 30-minute output delay. Option B is wrong because a large number of late-arriving events would increase the watermark delay but the alert specifically indicates the job is falling behind overall processing, not just handling late data; late events are managed by the late arrival policy and do not inherently cause a 30-minute delay. Option C is wrong because a 1-minute tumbling window is small and appropriate for real-time analytics; a larger window would reduce processing frequency, not cause delay.

Practice this question →
2
MCQeasy

You have an Azure Data Factory pipeline that must copy data from an on-premises Oracle database to Azure Blob Storage every night. The on-premises server cannot accept inbound connections, and no VPN or ExpressRoute is available. You need to enable connectivity with minimal administrative overhead. What should you deploy?

A.An Azure VPN Gateway with a site-to-site connection to the on-premises network.
B.A self-hosted integration runtime installed on a machine in the on-premises network.
C.An Azure ExpressRoute circuit with private peering to the on-premises datacenter.
D.An Azure Integration Runtime with a managed virtual network and private endpoints to the Oracle server.
AnswerB

The self-hosted integration runtime is designed exactly for this case: it runs on an on-premises machine and makes outbound connections to Azure Data Factory over HTTPS, so no inbound firewall rules or VPN are required. It supports Oracle as a source and Blob Storage as a sink, and installation is lightweight, matching the minimal-overhead requirement.

Why this answer

The self-hosted integration runtime is the standard Azure Data Factory mechanism for reaching on-premises data sources when inbound connectivity is not possible. It initiates outbound HTTPS connections to the Data Factory service, so it works without VPN, ExpressRoute, or firewall changes. It also supports the Oracle connector and Blob Storage sink required by this nightly copy pipeline.

Exam trap

The trap here is assuming network-level connectivity such as VPN or ExpressRoute is required for on-premises access, when the self-hosted integration runtime already solves it with outbound-only connections.

Practice this question →
3
MCQeasy

You are implementing a data processing solution in Azure Databricks. The solution must read data from Azure Data Lake Storage Gen2, transform it using PySpark, and write the results back to a different location in the same storage account. You need to authenticate to the storage account securely without storing secrets in the notebook. What should you use?

A.Service Principal with a client secret stored in the notebook
B.Azure Key Vault-backed secret scope
C.Shared access signature (SAS) token
D.Storage account access key
AnswerB

A Key Vault-backed secret scope satisfies the no-secrets-in-notebook constraint by retrieving credentials at runtime from Azure Key Vault rather than embedding them in code. Databricks resolves `dbutils.secrets.get()` calls through the scope, so storage access keys or service principal secrets never appear in the notebook or its revision history.

Why this answer

Azure Key Vault-backed secret scopes allow you to securely reference secrets without storing them in the notebook. Option A is wrong because storing a client secret directly in the notebook is insecure. Option C is wrong because SAS tokens can be exposed and are less secure.

Option D is wrong because storage account access keys are long-lived and should not be used in notebooks.

Practice this question →
4
MCQhard

You have a mission-critical pipeline that processes financial transactions in Azure Synapse Analytics. The pipeline uses Azure Data Factory with a mapping data flow to transform data. You need to ensure high availability and minimal data loss in case of a regional failure. What should you implement?

A.Store the source data in an RA-GRS storage account and use Azure Data Factory to copy from the secondary endpoint.
B.Configure the pipeline to retry on failure and manually restore from backup.
C.Use Azure SQL Database active geo-replication as the source.
D.Use Azure Synapse Link for Cosmos DB to enable near real-time analytics with multi-region writes.
AnswerA

Correct. GRS storage provides geo-redundancy, and the pipeline can be configured to read from the secondary endpoint during a regional failure, ensuring high availability and minimal data loss.

Why this answer

Using an RA-GRS storage account ensures geo-redundancy with a readable secondary endpoint, and Azure Data Factory can copy data from that secondary endpoint in case of a regional failure. This provides high availability and minimal data loss for the source data, while the mapping data flow transformation can proceed after the copy. Option D is incorrect because Azure Synapse Link for Cosmos DB changes the architecture (using Cosmos DB as source) and does not directly provide high availability for the existing ADF pipeline with mapping data flows; it introduces a different technology stack that may not meet the requirement of pipeline resilience.

Exam trap

Candidates may think that adding a new technology like Synapse Link solves HA, but it's about pipeline architecture, not the source system.

Practice this question →
5
MCQhard

You have a streaming pipeline using Azure Stream Analytics that ingests data from Event Hubs and outputs to Azure Synapse Analytics. The job has a high watermark delay and is falling behind. You need to reduce the latency. Which action should you take?

A.Add more partitions to the Event Hubs.
B.Increase the number of Streaming Units (SUs) for the Stream Analytics job.
C.Replace the output with Azure Functions for each event.
D.Change the input to a reference data input.
AnswerB

Streaming Units provision compute and throughput for the job; insufficient SUs cause the watermark delay to grow as the job falls behind. Increasing SUs adds parallel processing capacity, directly reducing latency, whereas partitioning or query changes alone may not resolve resource starvation.

Why this answer

Increasing the number of Streaming Units (SUs) for the Stream Analytics job allocates more compute resources, reducing latency. Adding more Event Hubs partitions may improve throughput but not directly reduce latency if the job is already bottlenecked. Switching to reference data input does not help.

Using Azure Functions for output may add overhead.

Practice this question →
6
Multi-Selecthard

You are optimizing the performance of a large-scale batch processing job in Azure Databricks. The job reads data from Azure Data Lake Storage Gen2, performs transformations, and writes results back. You notice that the job is I/O bound. Which THREE strategies can improve performance? (Choose three.)

Select 3 answers
A.Use Delta Lake format and optimize the table with Z-ordering on frequently filtered columns.
B.Increase the number of partitions in the DataFrame to improve parallelism.
C.Cache the DataFrame in memory after reading to avoid re-reading from disk.
D.Reduce the number of shuffle partitions to minimize data movement.
E.Enable autoscaling on the cluster to add more nodes during processing.
AnswersA, B, C

Delta Lake with Z-ordering co-locates related data in the same files using multi-dimensional clustering on frequently filtered columns, enabling data skipping that reads far fewer files. This directly reduces I/O against Azure Data Lake Storage Gen2, satisfying the stem's I/O-bound constraint.

Why this answer

Option A is correct because Delta Lake with Z-ordering co-locates related data in the same set of files, enabling data-skipping so the I/O-bound job reads far fewer bytes when filtering on those columns. Option B is correct because increasing DataFrame partitions spreads the read/transform workload across more concurrent tasks, raising parallelism and better saturating the storage throughput available to the cluster. Option C is correct because caching the DataFrame in memory (or Delta cache) prevents repeated reads from ADLS Gen2, directly cutting the disk I/O that is the bottleneck.

Option D is not correct because reducing shuffle partitions lowers parallelism for wide transformations and typically increases per-task data volume, which does not relieve I/O-bound reads. Option E is not correct because autoscaling adds compute nodes but does not by itself reduce the I/O volume or improve data layout, so it is not a targeted fix for an I/O-bound workload.

Exam trap

A common trap is thinking that reducing shuffle partitions (Option D) or enabling autoscaling (Option E) directly address I/O bottlenecks. However, shuffle partitions affect shuffle performance, not storage I/O, and autoscaling adds compute resources, not storage I/O bandwidth. Candidates may also incorrectly believe that caching is only beneficial for compute-bound jobs, but it also reduces I/O.

Practice this question →
7
MCQeasy

You are tasked with transforming data in an Azure Synapse Analytics pipeline using a mapping data flow. The source data contains a column 'FullName' in the format 'LastName, FirstName'. You need to split this into two separate columns: 'LastName' and 'FirstName'. Which transformation should you use?

A.Pivot transformation
B.Aggregate transformation
C.Lookup transformation
D.Derived Column transformation
AnswerD

Derived column can use expressions to split strings.

Why this answer

The Derived Column transformation is correct because it allows you to create new columns by applying expressions to existing data. In this case, you can use string functions like `split()` or `substring()` and `locate()` to parse 'FullName' into 'LastName' and 'FirstName' based on the comma delimiter. This transformation operates row-by-row, making it ideal for simple column splits.

Exam trap

The trap here is that candidates often confuse the Derived Column transformation with the Split transformation (which does not exist in mapping data flows) or mistakenly think the Pivot transformation can re-arrange column data, when in fact Derived Column is the correct choice for column-level string operations.

How to eliminate wrong answers

Option A is wrong because the Pivot transformation is used to rotate data from rows into columns by aggregating values, not for splitting a single column into multiple columns. Option B is wrong because the Aggregate transformation is designed to perform calculations like sum, count, or average over groups of rows, not for row-level string manipulation. Option C is wrong because the Lookup transformation is used to join data from a reference dataset based on a key, not to parse or split column values.

Practice this question →
8
MCQmedium

You are designing a data processing solution in Azure Synapse Analytics. The solution must process streaming data from Azure Event Hubs and store the results in a dedicated SQL pool. You need to choose the most appropriate service for near real-time ingestion with minimal latency. What should you use?

A.Azure Databricks with Structured Streaming
B.Azure Stream Analytics
C.Azure Data Factory
D.Azure Functions with Event Hub trigger
AnswerB

Azure Stream Analytics is a fully managed, serverless stream-processing engine that ingests directly from Event Hubs and writes to a dedicated SQL pool, delivering sub-second near real-time latency without provisioning clusters. This satisfies the stem's minimal-latency ingestion constraint, unlike batch-oriented alternatives such as Synapse pipelines or Spark structured streaming jobs.

Why this answer

Azure Stream Analytics is the correct choice because it is purpose-built for near real-time stream processing with sub-second latency, directly integrates with Azure Event Hubs as an input source and dedicated SQL pool as an output sink, and provides a SQL-like query language for defining transformations. This minimizes architectural complexity and latency compared to other services.

Exam trap

The trap here is that candidates often confuse 'near real-time' with 'batch processing' and choose Azure Data Factory (option C) because it is a familiar data integration tool, overlooking that it lacks native streaming capabilities and introduces latency from scheduled pipeline runs.

How to eliminate wrong answers

Option A is wrong because Azure Databricks with Structured Streaming introduces additional overhead from cluster startup time and micro-batch processing, which typically results in higher latency (seconds to minutes) compared to Stream Analytics' continuous processing model. Option C is wrong because Azure Data Factory is a batch-oriented ETL/ELT orchestration service that does not support native streaming ingestion; it polls sources on a schedule, introducing minutes of latency. Option D is wrong because Azure Functions with Event Hub trigger processes events one at a time in a serverless compute model, which can lead to cold-start delays and lacks built-in windowing, aggregation, and exactly-once semantics for streaming workloads.

Practice this question →
9
Multi-Selectmedium

You are designing an ETL process in Azure Data Factory. You need to transform data using Mapping Data Flows. Which THREE of the following transformations are available in Mapping Data Flows?

Select 3 answers
A.Pivot
B.Derived Column
C.Union All
D.Aggregate
E.Merge Join
AnswersA, B, D

Pivot is a native Mapping Data Flows transformation, reshaping rows into columns through an aggregate-style pivot operation on selected group-by and pivot keys. It satisfies the stem's requirement for transformations available within Mapping Data Flows, unlike pipeline activities or external compute, running on the Spark execution engine.

Why this answer

In Azure Data Factory Mapping Data Flows, the Pivot transformation (A) is a built-in transformation that reshapes data by turning unique row values into columns, which is why it is correct. The Derived Column transformation (B) is also available and is used to create new columns or modify existing ones using expression builder logic, making it correct. The Aggregate transformation (D) is likewise a native Mapping Data Flows transformation that performs group-by operations and aggregations such as SUM, AVG, COUNT, and MIN/MAX, so it is correct.

Union All (C) is not a Mapping Data Flows transformation; combining multiple streams is done with the Union transformation, which behaves like a union (not specifically 'Union All' as a named transformation). Merge Join (E) is not a Mapping Data Flows transformation either; joins are performed with the Join transformation, and the term 'Merge Join' refers to a SQL Server/SSIS-style operator rather than an ADF Mapping Data Flows transformation.

Exam trap

The trap here is that candidates confuse the 'Union All' and 'Merge Join' names from other tools (like SSIS or T-SQL) with the actual transformation names in Azure Data Factory Mapping Data Flows, leading them to select options that sound familiar but are not available.

Practice this question →
10
MCQmedium

You are troubleshooting a slow-running pipeline in Azure Data Factory that uses a Copy activity to transfer data from Azure Blob Storage to Azure Synapse Analytics. The pipeline processes about 100 GB of CSV files. The copy performance is poor even though the source and sink are in the same region. What is the most likely cause?

A.The copy activity is not using staging and PolyBase
B.The source and sink are in different Azure regions
C.The Data Integration Unit (DIU) setting is too low
D.The source files are compressed
AnswerA

PolyBase dramatically improves load performance.

Why this answer

The Copy activity in Azure Data Factory uses PolyBase or COPY statement (staging) to bulk load data into Azure Synapse Analytics. Without staging and PolyBase, the default insert method is row-by-row, which is extremely slow for large datasets like 100 GB of CSV files. Enabling staging with PolyBase allows parallel, high-throughput loading, which is essential for performance at this scale.

Exam trap

The trap here is that candidates often assume DIU settings are the primary performance lever, but for Synapse sinks, the staging/PolyBase mechanism is the critical factor that can improve performance by orders of magnitude.

How to eliminate wrong answers

Option B is wrong because the question explicitly states that the source and sink are in the same region, so cross-region latency is not the issue. Option C is wrong because Data Integration Units (DIUs) control parallelism within the Copy activity, but even with maximum DIUs, the row-by-row insert into Synapse is the bottleneck, not the DIU setting. Option D is wrong because compressed source files can actually improve performance by reducing network transfer time, and ADF can decompress them efficiently; compression is not inherently a cause of poor copy performance.

Practice this question →
11
MCQeasy

You are running a Python script in Azure Databricks that reads a CSV file from DBFS. The script runs successfully in an interactive notebook but fails when executed as a job with the error: 'Path does not exist: dbfs:/tmp/data.csv'. What is the most likely cause?

A.The job is using a different runtime that does not support Python.
B.The file is too large for DBFS.
C.The job cluster does not have permission to access DBFS.
D.The file was uploaded to the workspace filesystem, not to DBFS.
AnswerD

Workspace files are not automatically in DBFS.

Why this answer

The most likely cause is that the file was uploaded to the workspace filesystem (Workspace), not to DBFS. In interactive notebooks, the workspace filesystem is accessible via a symlink that makes it appear under `dbfs:/`, but when running as a job, the path `dbfs:/tmp/data.csv` does not resolve to workspace files. The file must be in the DBFS root or mounted location to be accessible via that path.

Option D is correct. Option A is incorrect because both interactive and job clusters support Python. Option B is incorrect because file size is not indicated as an issue.

Option C is incorrect because the job cluster automatically has permission to access DBFS.

Practice this question →
12
MCQhard

You have an Azure Databricks notebook that processes a large Delta table. The notebook uses a structured streaming query to read from the Delta table and write to another Delta table. The source table receives frequent updates and deletes. You need the streaming query to process both new data and changes (updates and deletes) from the source table. What should you do?

A.Convert the source table to an external table and use `spark.readStream.format("delta").option("mergeSchema", "true")` to read all changes.
B.Use `readStream` with the `startingVersion` option set to the latest version and `maxFilesPerTrigger` set to 1.
C.Configure the streaming query to use `readStream` with the `ignoreDeletes` option set to true.
D.Enable Change Data Feed on the source Delta table and configure the streaming query to read the change data feed.
AnswerD

Delta Lake Change Data Feed (CDF) records row-level changes (inserts, updates, deletes) in the source table. By enabling CDF and reading the change data feed in a streaming query, you can process all changes. This is the designed mechanism for capturing changes from a Delta table in a streaming fashion, and it supports both updates and deletes.

Why this answer

Delta Lake Change Data Feed (CDF) is the feature designed to capture row-level changes from a Delta table. When enabled, it records inserts, updates, and deletes, and a streaming query can read these changes as a stream. This allows downstream processing to react to all modifications, not just appends.

Enabling CDF and reading the change data feed is the correct approach to meet the requirement.

Exam trap

The trap here is assuming that a standard Delta streaming read can capture updates and deletes, when it only processes new files unless Change Data Feed is enabled.

Practice this question →
13
MCQhard

You are designing a data processing solution for a retail company that uses Azure Synapse Analytics. The solution must process point-of-sale (POS) data from multiple stores. The data arrives in CSV files in Azure Data Lake Storage Gen2. Each store sends a file every hour. You need to process the files as they arrive and load the data into a dedicated SQL pool. The solution must handle late-arriving files (files that arrive after the scheduled processing time) and ensure that the data is consistent. Which approach should you use?

A.Use Azure Data Factory with a Copy activity to load data into a staging table, then use a Data Flow activity to perform upserts.
B.Use Azure Databricks to read the CSV files, perform upserts, and write to the dedicated SQL pool using JDBC.
C.Use PolyBase to create external tables on the CSV files and then use CREATE TABLE AS SELECT to load into the dedicated SQL pool.
D.Use Azure Data Factory with a Copy activity to load data into a staging table in the dedicated SQL pool, then use a Stored Procedure activity to merge the data into the final table.
AnswerD

Staging then merging via a Stored Procedure activity makes the load idempotent: late-arriving files are upserted on business keys rather than duplicated. This satisfies both the hourly arrival pattern and the consistency requirement in the dedicated SQL pool.

Why this answer

The recommended approach for handling late-arriving files and ensuring data consistency when loading into a dedicated SQL pool is to use Azure Data Factory with a Copy activity to load data into a staging table, then use a Stored Procedure activity to merge the data into the final table. This pattern allows for upserts and handles late-arriving data by merging based on business keys.

Exam trap

DP-203 often tests the choice between different data loading patterns, where candidates might overlook the need for upserts and late-arriving data handling, opting for simpler append-only methods like PolyBase CTAS, which do not ensure consistency.

How to eliminate wrong answers

Option A is wrong because while Data Flow can perform upserts, it may not be as efficient or straightforward for handling late-arriving files and ensuring consistency in a dedicated SQL pool; Data Flows are more suited for complex transformations but can be slower and more expensive for simple upserts. Option B is wrong because using Azure Databricks with JDBC to write to a dedicated SQL pool can work, but it introduces additional complexity and cost, and may not be the most streamlined approach for this scenario. Option C is wrong because PolyBase with CREATE TABLE AS SELECT (CTAS) is for bulk loading and does not handle upserts or late-arriving data; it would append data, leading to duplicates.

Practice this question →
14
MCQhard

Your organization uses Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. You notice that queries are slow when filtering on a date column. You need to improve query performance without increasing costs. What should you do?

A.Increase the maximum query concurrency limit
B.Provision a dedicated SQL pool with more DTUs
C.Create a clustered columnstore index on the date column
D.Partition the data by date in the data lake (e.g., folder structure: /year=*/month=*/day=*)
AnswerD

Partitioning Parquet files into year/month/day folders lets the serverless SQL pool prune irrelevant files, reading only matching partitions instead of scanning the whole dataset. This reduces data scanned per query, improving performance without raising cost, since serverless billing is per terabyte processed.

Why this answer

Partitioning the data by date in the data lake (e.g., /year=*/month=*/day=*) allows the serverless SQL pool to leverage partition elimination. When querying with a filter on the date column, the pool can read only the relevant partitions (folders) instead of scanning all Parquet files, drastically reducing I/O and improving query performance at no additional cost.

Exam trap

The trap here is that candidates often confuse serverless SQL pool with dedicated SQL pool and incorrectly choose to create indexes or scale resources, not realizing that serverless SQL pool relies on external data partitioning and file-skipping techniques rather than internal indexing or provisioning.

How to eliminate wrong answers

Option A is wrong because increasing the maximum query concurrency limit does not improve the performance of a single query; it only allows more concurrent queries to run, which could even degrade individual query performance due to resource contention. Option B is wrong because provisioning a dedicated SQL pool with more DTUs increases costs and is not a serverless SQL pool feature; serverless SQL pool scales automatically and does not use DTUs, so this would be an expensive and incorrect solution. Option C is wrong because clustered columnstore indexes are not supported in serverless SQL pool; they are a feature of dedicated SQL pools, and creating one on a date column in a serverless context is not possible.

Practice this question →
15
MCQmedium

You are developing an Azure Databricks notebook that processes streaming data from Azure Event Hubs using Structured Streaming. The stream writes to a Delta Lake table. You need to ensure that the stream can recover from failures and continue processing from where it left off without reprocessing all data. You also need to minimize the impact on the source. What should you configure?

A.Use the foreachBatch sink and manually store the offset in an Azure SQL Database.
B.Set the checkpoint location to a path in Azure Data Lake Storage Gen2 and use the Delta table as the sink.
C.Enable auto-compaction on the Delta table and set the trigger to continuous.
D.Set the startingOffsets option to earliest and enable watermarking.
AnswerB

Structured Streaming uses checkpointing to track progress and recover from failures. Setting a checkpoint location in durable storage like Azure Data Lake Storage Gen2 allows the stream to resume from the last committed offset after a restart. Writing to a Delta table provides exactly-once semantics when combined with checkpointing, ensuring no data loss or duplication. This configuration meets both recovery and minimal source impact requirements.

Why this answer

Checkpointing is the mechanism that enables Structured Streaming to recover from failures by storing progress information in durable storage. Combining a checkpoint location in Azure Data Lake Storage Gen2 with a Delta Lake sink ensures exactly-once processing and allows the stream to resume from the last offset. Other options either lack checkpointing or introduce unnecessary complexity and source load.

Exam trap

The trap here is thinking that Delta Lake alone provides fault tolerance, when in fact checkpointing must be configured separately to track streaming progress and enable recovery.

Practice this question →
16
MCQhard

Refer to the exhibit. You are deploying an Azure Synapse Analytics dedicated SQL pool using the provided ARM template snippet. After deployment, you need to adjust the performance level to DW200c to handle increased workload. Which parameter should you modify?

A.storageAccountType
B.maxSizeBytes
C.collation
D.sku.name
AnswerD

The performance level of a dedicated SQL pool is defined by the sku.name property, which carries values such as DW200c. Modifying sku.name in the ARM template sets the pool to the required DW200c performance level.

Why this answer

In an Azure Synapse Analytics dedicated SQL pool ARM template, the performance level (e.g., DW100c, DW200c, DW300c) is specified in the sku.name property of the Microsoft.Synapse/workspaces/sqlPools resource. To change the performance level to DW200c, you modify sku.name. The other parameters control storage redundancy, maximum size, and collation, none of which affect the compute performance level.

Exam trap

The trap is confusing the performance level (sku.name) with capacity limits (maxSizeBytes) — candidates who see 'performance' and 'size' in the same resource often pick maxSizeBytes, but performance tier is always the SKU.

How to eliminate wrong answers

Option A is wrong because storageAccountType controls the storage redundancy/type (e.g., LRS, GRS) for the SQL pool's backing storage, not the compute performance level. Option B is wrong because maxSizeBytes sets the maximum data size for the pool (in bytes), which is a capacity limit, not a performance tier. Option C is wrong because collation defines the database collation (sorting and comparison rules) and has no bearing on the DWU/performance level.

Practice this question →
17
MCQhard

You are monitoring an Azure Stream Analytics job that processes data from an IoT hub. The job's output to Azure Synapse Analytics is experiencing high latency. The job's SU% utilization is at 90%. Which action will most likely reduce the latency?

A.Increase the number of Streaming Units (SUs) allocated to the job.
B.Decrease the watermark delay interval.
C.Increase the late arrival tolerance window.
D.Increase the number of partitions in the output table.
AnswerA

SU% utilisation at 90% means the job is near its allocated streaming capacity, so processing falls behind the IoT Hub input rate and output latency grows. Adding Streaming Units provides more compute parallelism, allowing the backlog to drain faster.

Why this answer

The job's SU% utilization is at 90%, indicating that the current Streaming Units (SUs) are nearly saturated, causing a processing bottleneck. Increasing the number of SUs allocates more compute resources (CPU and memory) to the Stream Analytics job, allowing it to process incoming IoT data faster and reduce the latency to Azure Synapse Analytics. This directly addresses the high utilization issue, which is the most likely root cause of the latency.

Exam trap

The trap here is that candidates often confuse output-side tuning (like partitioning or sink configuration) with the actual processing bottleneck, overlooking that high SU% utilization directly indicates the Stream Analytics job itself is the limiting factor.

How to eliminate wrong answers

Option B is wrong because decreasing the watermark delay interval would make the job emit results more frequently, but it does not increase processing capacity; with SU utilization already at 90%, this could worsen backpressure and increase latency. Option C is wrong because increasing the late arrival tolerance window only allows the job to handle out-of-order events for a longer period; it does not improve throughput or reduce latency caused by resource saturation. Option D is wrong because increasing the number of partitions in the output table improves write parallelism on the Synapse side, but the bottleneck is the Stream Analytics job's processing capacity (90% SU utilization), not the output sink's partitioning.

Practice this question →
18
MCQmedium

You are building a data pipeline that uses Azure Data Factory to copy data from a REST API to Azure Blob Storage. The REST API returns JSON data in pages of 1000 records each. The total number of records is 50,000. Which activity or feature should you use to loop through the pages?

A.Use a ForEach activity to iterate over a fixed number of pages.
B.Use a Lookup activity to retrieve the total number of pages and then use a ForEach.
C.Use an Until activity to loop until the API returns no more pages.
D.Use a Copy activity with pagination rules enabled in the source.
AnswerD

Copy activity pagination rules let the source iterate REST API pages automatically using properties such as NextPageUrl or absolute URLs, retrieving all 50,000 records without a ForEach loop. This satisfies the stem's requirement to traverse 1,000-record pages efficiently.

Why this answer

Azure Data Factory's Copy activity supports pagination rules that automatically handle paging through REST API responses. By configuring pagination rules in the source dataset, the Copy activity can iterate through pages until all data is copied, without needing explicit looping activities. This is the most efficient and recommended approach.

Exam trap

DP-203 often tests the preference for built-in pagination in Copy activity over manual looping, where candidates may overcomplicate with ForEach or Until activities.

How to eliminate wrong answers

Option A is wrong because a ForEach activity with a fixed number of pages is inflexible and requires knowing the exact page count in advance. Option B is wrong because using a Lookup to get total pages then ForEach is more complex and unnecessary when pagination rules exist. Option C is wrong because an Until activity would require custom logic to detect the end of pages, which is more complex than built-in pagination.

Option D is correct because Copy activity with pagination rules is designed for this scenario.

Practice this question →
19
MCQmedium

Refer to the exhibit. You are deploying an Azure Synapse Analytics workspace using an ARM template. The template defines a managed virtual network integration runtime. You need to ensure that the integration runtime can run mapping data flows with a time-to-live (TTL) of 10 minutes. What is the purpose of the 'timeToLive' property in this configuration?

A.It defines how long the cluster will be kept alive after a data flow completes, allowing subsequent data flows to reuse the cluster.
B.It sets the timeout for the integration runtime to connect to the data sources.
C.It specifies the maximum duration a data flow activity can run before timing out.
D.It determines the maximum number of concurrent data flows that can run on the cluster.
AnswerA

The timeToLive property sets the idle period before the data flow cluster shuts down. With 10 minutes configured, the cluster persists after a flow completes, letting subsequent mapping data flows reuse the warm cluster and avoid cold-start provisioning delays.

Why this answer

The 'timeToLive' property in an Azure Synapse Analytics managed virtual network integration runtime controls how long the cluster remains alive after a mapping data flow completes. By setting a TTL of 10 minutes, subsequent data flows can reuse the same warm cluster, avoiding the 5–10 minute cold start time for new clusters. This optimizes performance and reduces latency for consecutive data flow executions.

Exam trap

The trap here is that candidates confuse 'timeToLive' with activity timeout or concurrency limits, because all three involve time or capacity constraints, but TTL specifically governs cluster reuse after a data flow completes, not execution duration or parallelism.

How to eliminate wrong answers

Option B is wrong because the connection timeout to data sources is configured separately via linked service properties or the 'connectVia' runtime settings, not through the 'timeToLive' property. Option C is wrong because the maximum duration a data flow activity can run is set by the activity's 'timeout' property in the pipeline, not by the integration runtime's TTL. Option D is wrong because the maximum number of concurrent data flows is controlled by the 'concurrency' property on the integration runtime, not by 'timeToLive'.

Practice this question →
20
MCQhard

You are developing an Azure Synapse Analytics serverless SQL pool solution that queries Parquet files in Azure Data Lake Storage Gen2. Analysts run ad-hoc queries with predicates on a high-cardinality column named TransactionId, and each query scans the entire folder, causing high cost. You need to reduce the amount of data scanned per query without changing the file format. What should you do?

A.Create an external table over the folder and enable statistics on the TransactionId column.
B.Create a partitioned table in a dedicated SQL pool and load the Parquet data into it.
C.Use OPENROWSET with a filepath() predicate to restrict the query to specific files.
D.Create a partitioned external table where the folder structure is organized by a low-cardinality key and query only the relevant partitions.
AnswerD

Partitioning the external table on a low-cardinality column such as date or region lets the serverless engine prune entire folders that cannot match the predicate. Queries that filter on that partition column then scan only the relevant directories, sharply reducing bytes read. This preserves the Parquet format and works with the existing file layout once the folder hierarchy reflects the partition key.

Why this answer

Serverless SQL pool reduces cost by reading fewer bytes, and partition elimination is the primary lever for that when the underlying layout supports it. Organizing folders by a low-cardinality key and exposing it through a partitioned external table allows the engine to skip irrelevant directories. Predicates on a high-cardinality column cannot prune files because their values are scattered across every file.

Exam trap

The trap here is expecting statistics or filepath filtering to skip data for a high-cardinality predicate, when only partition elimination on a low-cardinality key can prune files.

Practice this question →
21
Multi-Selectmedium

Which TWO actions can you take to optimize the performance of a dedicated SQL pool in Azure Synapse Analytics when loading large volumes of data?

Select 2 answers
A.Create nonclustered indexes on all columns of the target table
B.Use ROUND_ROBIN distribution for the staging table
C.Set the row group size to 100,000 rows for optimal compression
D.Enable change tracking on the target table
E.Use CREATE TABLE AS SELECT (CTAS) with partition switching
AnswersB, E

ROUND_ROBIN distributes staging rows evenly across all distributions without requiring a distribution key, avoiding skew and data-movement overhead during the load. This maximises parallel ingestion throughput into the staging table before the final CTAS into the production table.

Why this answer

Option B is correct because a ROUND_ROBIN distributed staging table spreads incoming rows evenly across all distributions without requiring a distribution key, which maximizes parallel ingestion throughput and avoids data movement during the load before the data is redistributed into the final table. Option E is correct because CTAS performs a parallel, minimally logged bulk operation that creates a new table with the desired distribution and indexing in one step, and combining it with partition switching lets you swap the fully loaded table into the target quickly and efficiently. Option A is not appropriate because nonclustered indexes on every column slow down bulk loads and consume extra storage; dedicated SQL pools rely primarily on clustered columnstore indexes.

Option C is wrong because the optimal row group size for columnstore compression is around 1,048,576 rows (1 million), not 100,000, which yields smaller, less efficient row groups. Option D is incorrect because change tracking is used for incremental data synchronization scenarios and does not improve bulk load performance.

Exam trap

The trap here is that candidates often confuse the purpose of indexes and distribution types, mistakenly thinking that adding indexes on all columns will speed up loading, when in fact it degrades performance, and they overlook that ROUND_ROBIN is specifically designed for fast staging loads, not for query performance.

Practice this question →
22
MCQeasy

Refer to the exhibit. You have a mapping data flow in Azure Data Factory that aggregates sales data. The data flow runs successfully but the sink table contains only the total sum per run instead of per product. What is missing?

A.The source dataset is not filtering by date
B.The aggregate transformation does not have a groupBy column
C.The data flow is missing a filter transformation
D.The sink dataset is not configured to append
AnswerB

An aggregate transformation without a groupBy column collapses all rows into a single group, producing one total sum per run. Adding ProductID as the groupBy column makes the aggregation produce one row per product, matching the expected sink output.

Why this answer

In an ADF mapping data flow, the Aggregate transformation computes aggregates based on the columns listed in the Group By tab. If no groupBy column is specified, the transformation treats the entire dataset as a single group, producing one row with the total sum. Adding the product column to groupBy makes the aggregation produce one row per product.

Exam trap

The trap here is confusing row-level filtering or sink configuration with aggregation granularity — candidates overlook that an empty Group By in the Aggregate transformation collapses all rows into a single global total.

How to eliminate wrong answers

Option A is wrong because date filtering affects which rows are included, not the granularity of the aggregation — the result would still be a single total. Option C is wrong because a filter transformation only removes rows; it does not change the grouping behavior of the aggregate. Option D is wrong because append vs. overwrite on the sink affects how results are written, not whether the aggregation is per-product or global.

Practice this question →
23
MCQhard

You are building a batch processing solution in Azure Synapse Analytics that reads data from a dedicated SQL pool, applies complex transformations using Synapse Spark, and writes the results back to the dedicated SQL pool. The pipeline must run on a schedule and handle transient failures with retries. Which approach should you use?

A.Use Azure Batch with a custom application to run Spark jobs
B.Use Azure Synapse Pipelines with a Notebook activity that runs Spark code
C.Use Azure Functions to trigger Spark jobs on demand
D.Use Azure Databricks with Auto Loader and Delta Live Tables
AnswerB

A Notebook activity inside Azure Synapse Pipelines runs the Spark transformation code while the surrounding pipeline supplies scheduling and built-in retry policies for transient failures. This satisfies both the batch orchestration and resilience requirements without external tooling.

Why this answer

Azure Synapse Pipelines with a Notebook activity is the correct approach because it natively integrates Synapse Spark for complex transformations and supports scheduling and retry policies for transient failures. This allows you to read from a dedicated SQL pool, process data in Spark, and write back to the pool without external orchestration, leveraging the built-in pipeline reliability features.

Exam trap

The trap here is that candidates may confuse Azure Batch or Azure Functions as viable alternatives for Spark job orchestration, overlooking the native integration and retry capabilities of Synapse Pipelines within the same service.

How to eliminate wrong answers

Option A is wrong because Azure Batch is a general-purpose batch computing service that does not natively integrate with Synapse Spark or dedicated SQL pools, requiring custom application development and lacking the seamless scheduling and retry capabilities of Synapse Pipelines. Option C is wrong because Azure Functions are event-driven and stateless, not designed for long-running Spark jobs or built-in retry logic for transient failures in a scheduled batch pipeline. Option D is wrong because Azure Databricks with Auto Loader and Delta Live Tables is a separate platform that does not natively integrate with Azure Synapse dedicated SQL pools, requiring additional connectivity setup and missing the unified orchestration provided by Synapse Pipelines.

Practice this question →
24
MCQmedium

You are partitioning a large fact table in Azure Synapse Dedicated SQL Pool by date. The table is used for queries that filter on CustomerID and Date. You want to minimize data movement. Which distribution strategy should you use?

A.Round-robin distribution
B.Hash distribution on CustomerID
C.Replicate distribution
D.Hash distribution on Date
AnswerB

Hash distribution on CustomerID co-locates rows sharing that value on the same compute node, so joins and aggregations filtering by CustomerID avoid reshuffling data across nodes. Date partitioning prunes partitions independently of distribution, satisfying both predicates while minimising data movement.

Why this answer

Hash distribution on CustomerID is correct because queries filtering on CustomerID and Date will benefit from collocated joins and aggregations when CustomerID is the distribution key. Since the table is large and partitioned by Date, hash distribution on CustomerID minimizes data movement by ensuring that rows with the same CustomerID reside on the same distribution node, allowing filters on Date to be applied locally within each partition.

Exam trap

The trap here is that candidates often assume partitioning and distribution should be on the same column (Date) to optimize date-range queries, but this ignores that distribution on the join key (CustomerID) is what minimizes data movement for the most common query pattern involving both CustomerID and Date filters.

How to eliminate wrong answers

Option A is wrong because round-robin distribution distributes rows evenly without any logical grouping, causing every query that filters on CustomerID to require full data movement across all distributions. Option C is wrong because replicate distribution copies the entire table to each distribution node, which is impractical for a large fact table due to storage overhead and write performance degradation. Option D is wrong because hash distribution on Date would cause data movement for queries filtering on CustomerID, as the distribution key does not align with the join or filter column, and partitioning on Date already provides local pruning without needing distribution on the same column.

Practice this question →
25
MCQmedium

You have an Azure Synapse Analytics workspace with a dedicated SQL pool. You need to create an external table that references Parquet files stored in Azure Data Lake Storage Gen2. The external table will be used for ad-hoc queries. Which statement correctly describes the required components?

A.You only need to create an external data source and an external file format.
B.You must create a PolyBase external table using the CREATE EXTERNAL TABLE AS SELECT (CETAS) statement.
C.You must use the OPENROWSET function instead of an external table.
D.You must create a database scoped credential, an external data source, and an external file format.
AnswerD

To create an external table in a dedicated SQL pool, you need a database scoped credential for authentication, an external data source pointing to the storage location, and an external file format defining the file type (Parquet). These components enable the pool to access and interpret the external data.

Why this answer

Creating an external table in a dedicated SQL pool requires three components: a database scoped credential for authentication, an external data source for the storage location, and an external file format for Parquet. These are essential for the pool to read the external data. Other options are either incomplete or describe different features.

Exam trap

The trap here is assuming that only a data source and file format are needed, forgetting the database scoped credential for authentication.

Practice this question →
26
MCQeasy

You need to transform JSON data containing nested arrays into a tabular format for analysis in Azure Synapse Analytics. Which transformation in Azure Data Factory or Synapse Pipelines should you use?

A.Join transformation
B.Derived Column transformation
C.Aggregate transformation
D.Flatten transformation
AnswerD

The Flatten transformation unnests nested arrays and structures, expanding each element into separate rows while preserving parent columns. This satisfies the requirement to convert hierarchical JSON into a tabular shape suitable for downstream analysis in Azure Synapse Analytics.

Why this answer

The Flatten transformation is specifically designed to denormalize nested JSON arrays into a tabular format by expanding array elements into multiple rows while preserving parent attributes. In Azure Data Factory and Synapse Pipelines, this transformation handles complex hierarchical structures like arrays of objects, making it the correct choice for converting JSON with nested arrays into a row-based dataset suitable for analysis in Azure Synapse Analytics.

Exam trap

The trap here is that candidates often confuse the Flatten transformation with the Unpivot transformation, which pivots columns into rows but does not handle nested JSON arrays, or they mistakenly think the Derived Column transformation can handle array expansion through expressions.

How to eliminate wrong answers

Option A is wrong because the Join transformation combines two streams based on matching keys, not for flattening nested arrays within a single JSON document. Option B is wrong because the Derived Column transformation creates or modifies columns using expressions but cannot expand nested arrays into multiple rows. Option C is wrong because the Aggregate transformation performs grouping and summarization operations (e.g., SUM, COUNT) and does not handle the denormalization of nested array structures.

Practice this question →
27
MCQeasy

Refer to the exhibit. You are reviewing an Azure Stream Analytics job query. The job has a stream input and a reference data input. The job is failing with the error 'Reference data input must be of type Reference, not Stream'. What is the cause of the error?

A.The input alias is incorrect.
B.The JOIN syntax is incorrect for reference data.
C.The input type is Stream; it should be Reference.
D.The output type is set to ReferenceData; it should be a different type.
AnswerC

Stream Analytics classifies each input as Stream or Reference at creation. The query joins reference data, but the input was configured as Stream, so the engine rejects it; changing the input type to Reference resolves the error.

Why this answer

The error 'Reference data input must be of type Reference, not Stream' explicitly indicates that the input configured as Stream should be set to Reference type. In Azure Stream Analytics, reference data inputs are used for static lookup data, and they must be declared as Reference input type. Setting the input type to Stream when it should be Reference causes this error.

Therefore, option C is correct. Option D is incorrect because the error message does not relate to output configuration; it is strictly about input type mismatch.

Exam trap

Candidates may be misled by the wording 'Reference data input' and think it's about output, but the error is straightforward: the input type is wrong. The trap is overthinking or assuming a complex output issue when the error message directly states the input type must be Reference.

How to eliminate wrong answers

Option A is wrong because the input alias being incorrect would cause a different error, such as 'Input alias not found' or 'Invalid input alias', not a type mismatch error. Option B is wrong because the JOIN syntax for reference data (e.g., using temporal join with ON clause) is correct; the error is about the input type, not the syntax. Option C is wrong because it states the input type is Stream when it should be Reference, which is actually the correct diagnosis of the problem, but the question asks for the cause of the error, and the answer option D correctly identifies that the output type is not the issue; the error is caused by the input being Stream instead of Reference, so C is a distractor that describes the problem rather than the cause as framed in the options.

Practice this question →
28
MCQmedium

You are designing a data processing solution in Azure using Azure Data Lake Storage Gen2 as the storage layer. You need to ensure that data ingested from various sources is immutable and can be used for both batch and streaming workloads. Which storage design pattern should you implement?

A.Store data in a normalized relational database structure.
B.Implement a medallion architecture with bronze, silver, and gold layers.
C.Use a data vault model with hubs, links, and satellites.
D.Design a star schema with fact and dimension tables.
AnswerB

The medallion pattern layers bronze raw, silver cleansed and gold curated data in ADLS Gen2. Bronze preserves ingested data immutably for replay, while silver and gold serve batch and streaming consumers, meeting the immutability and dual-workload constraints.

Why this answer

The medallion architecture (bronze, silver, gold) is the correct pattern because it enforces immutability at the bronze layer (raw ingested data is never modified), while providing progressively refined, query-optimized views for both batch and streaming workloads in Azure Data Lake Storage Gen2. This design supports schema-on-read, enables reprocessing from raw data, and aligns with lakehouse principles for unified analytics.

Exam trap

The trap here is that candidates confuse data modeling patterns (star schema, data vault) with storage layer design patterns, assuming any structured approach ensures immutability, when in fact only the medallion architecture explicitly separates raw immutable storage from refined layers for batch and streaming workloads.

How to eliminate wrong answers

Option A is wrong because a normalized relational database structure is designed for transactional consistency (OLTP) and does not support immutability or efficient storage of raw, schema-on-read data in a data lake; it also introduces coupling that hinders reprocessing. Option C is wrong because a data vault model (hubs, links, satellites) is a data warehouse modeling technique for auditing and historical tracking, not a storage pattern for immutability or unified batch/streaming in a data lake. Option D is wrong because a star schema with fact and dimension tables is a dimensional modeling approach for analytical queries in a data warehouse, not a pattern for raw data immutability or handling streaming ingestion in Azure Data Lake Storage Gen2.

Practice this question →
29
MCQmedium

You are developing an Azure Databricks notebook that processes a large Delta Lake table. You must add a derived column that depends on the latest value of a watermark stored in a small reference table, and the notebook must refresh this value before each micro-batch. You need to ensure the reference data is re-read on every micro-batch rather than cached once. Which approach should you use?

A.Persist the reference table with the MEMORY_AND_DISK storage level before starting the stream.
B.Broadcast the reference table as a static DataFrame and join it to the streaming DataFrame.
C.Enable the spark.databricks.delta.cache.enabled option on the streaming query.
D.Use the foreachBatch sink and, inside the function, query the reference table with a fresh read on each invocation.
AnswerD

foreachBatch gives you a Python or Scala function that runs once per micro-batch with the batch's DataFrame. Performing a fresh read of the reference table inside that function guarantees the watermark is retrieved anew for every micro-batch, which satisfies the refresh requirement while still allowing DataFrame operations on the batch.

Why this answer

Structured Streaming captures static DataFrames when a query starts, so joins against them do not see later changes. foreachBatch executes a user-defined function for each micro-batch and permits arbitrary operations, including a fresh read of the reference table. Reading the watermark inside that function ensures the latest value is used for the derived column on every micro-batch, which is precisely the stated requirement.

Exam trap

The trap here is assuming that caching or persisting the reference table will keep it current across micro-batches.

Practice this question →
30
MCQeasy

Your organization uses Azure Data Lake Storage Gen2 (ADLS Gen2) and wants to transform data using Azure Databricks. The data is stored in Parquet format. You need to read the data into a Spark DataFrame. Which DataFrame reader method should you use?

A.spark.read.avro()
B.spark.read.json()
C.spark.read.parquet()
D.spark.read.csv()
AnswerC

Parquet is a columnar file format, and Spark's DataFrameReader exposes a dedicated parquet method that reads such files directly from ADLS Gen2 into a DataFrame, inferring the schema automatically without needing an explicit schema definition or generic format call.

Why this answer

The data is stored in Parquet format, and the Spark DataFrame reader method `spark.read.parquet()` is specifically designed to read Parquet files, which is a columnar storage format optimized for big data processing in Azure Databricks.

Exam trap

The trap here is that candidates may confuse file format reader methods (e.g., using `spark.read.avro()` for Parquet data) due to assuming all binary formats are interchangeable, but each reader method is strictly tied to its specific file format.

How to eliminate wrong answers

Option A is wrong because `spark.read.avro()` is used for Avro format, not Parquet. Option B is wrong because `spark.read.json()` is for JSON files, which are text-based and not columnar like Parquet. Option D is wrong because `spark.read.csv()` is for CSV files, which are row-based and lack the compression and schema efficiency of Parquet.

Practice this question →
31
MCQeasy

You are creating an Azure Data Factory pipeline that must copy data from an on-premises SQL Server to Azure Blob Storage daily. The on-premises network restricts inbound connections, and you need a secure connection without exposing the SQL Server to the public internet. What should you use to connect to the on-premises SQL Server?

A.Azure ExpressRoute
B.Azure Integration Runtime with a managed virtual network
C.Azure Private Link
D.Self-hosted integration runtime
AnswerD

A self-hosted integration runtime is installed on a machine within the on-premises network. It establishes outbound connections to Azure Data Factory, allowing the service to dispatch copy activities to it. This enables secure access to on-premises data sources without inbound firewall openings. It is the standard mechanism for connecting Data Factory to private network resources.

Why this answer

To connect Azure Data Factory to an on-premises SQL Server without exposing it to the internet, you install a self-hosted integration runtime on a machine in the on-premises network. This runtime initiates outbound connections to Azure, so no inbound firewall rules are needed. It is the correct and standard solution for hybrid data movement with Data Factory.

Exam trap

The trap here is selecting a networking service like ExpressRoute or Private Link, which solve connectivity but do not provide the execution engine needed for Data Factory to access on-premises data.

Practice this question →
32
MCQeasy

You need to process a large dataset that contains personally identifiable information (PII). The data must be anonymized before being used for analytics. Which Azure service should you use to apply column-level masking dynamically?

A.Azure API Management policies
B.Azure Synapse Analytics dynamic data masking
C.Azure Data Lake Storage access control lists (ACLs)
D.Azure Purview classification and labeling
AnswerB

Dynamic data masking in Azure Synapse Analytics applies masking rules at query time on designated columns, so PII stays intact at rest while unauthorised users see obfuscated values. This satisfies the requirement to anonymise PII dynamically for analytics without altering the underlying stored data.

Why this answer

Azure Synapse Analytics provides dynamic data masking at the column level. Azure Purview is for data governance. Azure API Management is for APIs.

Azure Data Lake Storage does not provide masking.

Practice this question →
33
Multi-Selectmedium

You are using Azure Synapse Analytics to process data in a dedicated SQL pool. You need to ensure that queries against a large fact table perform well. The fact table is partitioned by date and distributed by a product key. Which two actions should you take? (Choose two.)

Select 2 answers
A.Create a nonclustered index on the date column.
B.Create a clustered columnstore index on the fact table.
C.Create a replicated table for the fact table.
D.Use round-robin distribution for the fact table.
E.Use hash distribution on the product key.
AnswersB, E

Clustered columnstore indexes are the default and most efficient storage format for large fact tables in dedicated SQL pools. They provide high compression and batch mode execution, significantly improving query performance for analytical workloads. They are particularly effective when combined with partitioning and distribution, as they allow segment elimination and parallel scans.

Why this answer

For a large fact table in a dedicated SQL pool, a clustered columnstore index provides optimal compression and query performance. Hash distribution on the product key ensures that joins with dimension tables on that key are co-located, reducing data movement. Together, these actions address both storage and distribution for analytical queries.

Exam trap

The trap here is assuming that traditional row-store indexes like nonclustered indexes are beneficial, when columnstore indexes are the preferred choice for fact tables in dedicated SQL pools.

Practice this question →
34
MCQmedium

You are developing a data processing pipeline for a gaming company that uses Azure Databricks. The pipeline processes game event data from Azure Event Hubs. You need to detect cheating patterns by analyzing events in real time. The solution must be able to handle high throughput and low latency. The output should be written to Azure Cosmos DB for real-time dashboards. Which approach should you use?

A.Use Azure Databricks Structured Streaming to read from Event Hubs, use Spark SQL and machine learning to detect cheating patterns, and write to Cosmos DB using the Azure Cosmos DB Spark connector.
B.Use Azure Functions with Event Hubs trigger to process each event and write to Cosmos DB.
C.Use Azure Data Factory with continuous copy to load data into Cosmos DB and then use Azure Synapse Analytics to detect patterns.
D.Use Azure Stream Analytics to query the stream for cheating patterns and output to Cosmos DB.
AnswerA

Structured Streaming reads Event Hubs incrementally, satisfying the low-latency, high-throughput requirement, while Spark SQL and MLlib detect cheating patterns in-stream. The Azure Cosmos DB Spark connector writes micro-batch results directly to Cosmos DB, enabling real-time dashboards without a separate serving layer.

Why this answer

Azure Databricks Structured Streaming is designed for scalable, low-latency stream processing and integrates with Event Hubs and Cosmos DB via connectors. It allows using Spark SQL and machine learning libraries to detect cheating patterns in real time. This approach handles high throughput and provides the necessary analytics capabilities.

The other options either lack the required real-time processing or the advanced analytics.

Exam trap

DP-203 often tests the choice between stream processing services. The trap is assuming Azure Stream Analytics is always the best for real-time analytics, but when advanced machine learning and Spark capabilities are needed, Databricks Structured Streaming is the correct choice.

How to eliminate wrong answers

Option B is wrong because Azure Functions with Event Hubs trigger is suitable for lightweight, event-driven processing but not for complex stream analytics with machine learning at scale; it may not handle high throughput and low latency as efficiently. Option C is wrong because Azure Data Factory with continuous copy is for data movement, not real-time pattern detection, and Azure Synapse Analytics is more batch-oriented. Option D is wrong because Azure Stream Analytics can query streams but lacks the advanced machine learning and Spark SQL capabilities of Databricks, and may not handle the complexity of cheating pattern detection as well.

Practice this question →
35
MCQeasy

You are developing an Azure Databricks notebook to process streaming data from Azure Event Hubs. The notebook must write the processed data to a Delta table with exactly-once processing guarantees. You need to configure the write operation. Which option should you use?

A.Write the stream to a Delta table using the `writeStream` method with `format("delta")` and a checkpoint location.
B.Write the stream to a Parquet file using `writeStream` with `format("parquet")` and a checkpoint location.
C.Write the stream to an Azure SQL Database using `writeStream` with `format("jdbc")` and a checkpoint location.
D.Use `write` instead of `writeStream` to write the DataFrame to a Delta table.
AnswerA

Using `writeStream` with Delta format and a checkpoint location enables structured streaming with exactly-once semantics. Delta Lake's transaction log ensures idempotent writes, and the checkpoint tracks progress to avoid reprocessing. This is the correct approach for streaming ingestion into Delta tables with exactly-once guarantees.

Why this answer

Structured streaming to Delta Lake with `writeStream` and a checkpoint location provides exactly-once processing because Delta's transaction log and the checkpoint work together to ensure each record is processed once. Other formats like Parquet lack transactional guarantees, and batch writes cannot handle streaming data. Writing to SQL via JDBC also lacks built-in exactly-once semantics.

Exam trap

The trap here is confusing at-least-once with exactly-once, and assuming any streaming sink with a checkpoint provides exactly-once guarantees.

Practice this question →
36
Multi-Selectmedium

You are designing a data processing solution using Azure Databricks. You need to read data from Azure Data Lake Storage Gen2, transform it using Spark SQL, and write to a Delta table. Which TWO configurations are required to ensure optimal performance for large datasets?

Select 2 answers
A.Disable automatic schema detection to reduce overhead.
B.Use Delta Lake's OPTIMIZE command to compact small files.
C.Use Delta Lake Z-order optimization on frequently filtered columns.
D.Cache the entire DataFrame in memory after reading.
E.Enable auto-compaction in Spark configuration.
AnswersB, C

Compacting small files improves read performance.

Why this answer

The OPTIMIZE command in Delta Lake compacts small files into larger ones, reducing the number of files that Spark must read during subsequent queries and writes. This is critical for large datasets where many small files can cause significant overhead in file listing and task scheduling. Option C is correct because Z-order optimization on frequently filtered columns improves data skipping, allowing Delta Lake to prune irrelevant files during scans, which dramatically reduces I/O and speeds up query performance.

Exam trap

The trap here is that candidates confuse auto-compaction as a Spark configuration (Option E) when it is actually a Delta Lake table property, and they overlook that caching (Option D) is not beneficial for write-heavy pipelines with large datasets.

Practice this question →
37
MCQeasy

You need to process streaming data from Azure Event Hubs and store the results in Azure Cosmos DB for a real-time dashboard. The solution must handle duplicate events and ensure exactly-once processing. Which Azure service should you use?

A.Azure Data Factory
B.Azure Functions with Event Hubs trigger
C.Azure Stream Analytics
D.Azure Databricks Structured Streaming
AnswerC

Supports exactly-once semantics with Event Hubs.

Why this answer

(Azure Stream Analytics) is correct because it provides exactly-once processing when configured with Event Hubs and Cosmos DB output. Option A (Azure Data Factory) is batch-oriented. Option B (Azure Functions) may have at-least-once guarantees.

Option D (Azure Databricks) can achieve exactly-once but requires more configuration.

Practice this question →
38
Multi-Selecthard

You are designing a data processing solution for a retail company that uses Azure Databricks. The solution needs to process streaming sales data from Event Hubs and batch data from Azure Data Lake Storage Gen2. You need to ensure that the solution can handle late-arriving data and maintain exactly-once semantics. Which TWO technologies should you use?

Select 2 answers
A.Delta Lake
B.Azure Databricks Structured Streaming
C.PolyBase
D.Azure Stream Analytics
E.Azure Data Factory
AnswersA, B

Provides ACID transactions and supports exactly-once semantics.

Why this answer

Delta Lake is correct because it provides ACID transactions, schema enforcement, and time travel capabilities, which are essential for handling late-arriving data and ensuring exactly-once semantics when combined with Structured Streaming. It allows you to merge late records into existing Delta tables using merge operations (upserts) without corrupting the data state.

Exam trap

The trap here is that candidates often confuse Azure Stream Analytics with Databricks Structured Streaming, assuming both can achieve exactly-once semantics with Delta Lake, but Stream Analytics does not write directly to Delta tables and lacks the transactional guarantees needed for idempotent late-arriving data processing in Databricks.

Practice this question →
39
MCQhard

You have an Azure Synapse Analytics dedicated SQL pool. A nightly ELT process loads a 500 GB staging table and then applies transformations using a stored procedure. The procedure performs many single-row updates against a large fact table, and the load now exceeds its window. You need to reduce the duration of the transformation step. What should you do?

A.Enable result set caching on the stored procedure so repeated executions reuse computed results.
B.Increase the resource class of the user running the stored procedure to grant more memory per query.
C.Convert the single-row updates to a CTAS-based pattern that creates a new table from a SELECT joining staging and fact data, then renames it.
D.Add a clustered columnstore index to the staging table to accelerate the updates.
AnswerC

Dedicated SQL pool is optimized for bulk, set-based operations, and CREATE TABLE AS SELECT writes results in parallel with minimal logging. Building a new fact table from a join of staging and existing fact data avoids the row-by-row overhead of updates, which are slow and log-heavy. Renaming via RENAME OBJECT swaps the new table into place atomically.

Why this answer

Dedicated SQL pool performs best with set-based, minimally logged bulk operations. Replacing many single-row updates with a CREATE TABLE AS SELECT that joins staging to the fact table, followed by a RENAME OBJECT to swap tables, eliminates row-level logging and locking, cutting the transformation step to a parallel bulk write.

Exam trap

The trap here is tuning memory or indexes when the real cost is the row-by-row update pattern that dedicated SQL pool handles poorly.

Practice this question →
40
MCQhard

You maintain an Azure Stream Analytics job that reads from an Event Hubs input and writes to an Azure Synapse Analytics dedicated SQL pool. During peak load the job produces late-arriving events that are dropped, and downstream reports show missing rows. You must retain and process events that arrive after the watermark by up to several minutes without changing the input. What should you configure?

A.Change the output to use a partitioned write pattern with a batch size of one row.
B.Set the event ordering policy out-of-order tolerance window to the required number of minutes.
C.Configure the Event Hubs input to use a consumer group with a longer retention period.
D.Increase the streaming units allocated to the job so more events are processed per second.
AnswerB

The out-of-order tolerance window tells the job how long to wait for events whose application timestamps are earlier than events already processed. Raising it to cover the observed delay allows those late events to be admitted and included in windowed aggregates instead of being discarded, which directly addresses the missing rows without modifying the Event Hubs input.

Why this answer

Late-arriving events are governed by the event ordering policy, whose out-of-order tolerance window defines how long the job accepts events behind the current watermark. Setting the window to cover the observed delay lets those events participate in temporal queries, restoring the missing rows without touching the upstream Event Hubs configuration or scaling compute.

Exam trap

The trap here is treating missing late events as a throughput problem and scaling streaming units, when the real control is the temporal tolerance in the event ordering policy.

Practice this question →
41
MCQeasy

You are designing a batch processing solution for a data lake. Source files arrive daily in Parquet format in Azure Data Lake Storage Gen2. The data must be cleaned, aggregated, and loaded into an Azure Synapse SQL pool. The solution should minimize compute costs and management overhead. Which technology should you use for the transformation?

A.Azure HDInsight with Spark jobs scheduled in Azure Data Factory.
B.Azure Synapse Pipelines with mapping data flows.
C.Azure Data Factory with a custom SSIS package.
D.Azure Databricks with an Auto Loader pipeline.
AnswerB

Mapping data flows run on Synapse Spark clusters that scale down when idle, and the visual transformation logic needs no cluster management. This satisfies the stem's cost-minimisation and low-overhead constraints while cleaning and aggregating the Parquet files.

Why this answer

Azure Synapse Pipelines with mapping data flows provide a serverless, code-free transformation service that runs on managed Spark clusters, minimizing management overhead and costs. Mapping data flows can directly read Parquet from ADLS Gen2, perform cleaning and aggregation, and load into Azure Synapse SQL pool without requiring cluster management. Option A (Azure HDInsight with Spark) requires manual cluster provisioning and management, increasing operational overhead.

Option C (custom SSIS package) is legacy, not cloud-native, and requires an integration runtime for execution. Option D (Azure Databricks with Auto Loader) provides powerful stream and batch processing but incurs higher costs for a simple batch job due to cluster management and DBU consumption.

Practice this question →
42
MCQhard

You are a data engineer for a healthcare company that processes patient data. You have an Azure Databricks workspace with a cluster configured for data processing. You need to implement a solution that processes streaming data from Azure Event Hubs, enriches it with reference data stored in Azure Cosmos DB, and writes the output to Delta Lake in Azure Data Lake Storage Gen2. The solution must ensure that the data processing is fault-tolerant and can handle schema evolution. The reference data is updated infrequently. You need to choose an approach that minimizes complexity and cost. What should you do?

A.Use Azure Databricks Auto Loader with Delta Live Tables to ingest streaming data, and use Change Data Capture from Cosmos DB to update the reference data inline.
B.Use Azure Data Factory to copy data from Event Hubs to Azure Data Lake Storage Gen2 in batches, then use Azure Databricks to process and enrich with Cosmos DB.
C.Use Azure Stream Analytics to ingest from Event Hubs, join with Cosmos DB reference data, and output to Azure Data Lake Storage Gen2 in Parquet format.
D.Use Azure Databricks Structured Streaming to read from Event Hubs, use a streaming static join to enrich with reference data from Cosmos DB, and write to Delta Lake. Enable schema evolution on the Delta table.
AnswerD

Simplifies processing and handles schema evolution.

Why this answer

Azure Databricks Structured Streaming can read from Event Hubs, and a streaming static join allows enriching with reference data from Cosmos DB (which is updated infrequently). Writing to Delta Lake with schema evolution enabled handles schema changes. This approach minimizes complexity and cost by using native Databricks features without additional services.

Exam trap

The trap is that candidates might choose Delta Live Tables or Stream Analytics for streaming, but the question emphasizes minimizing complexity and cost, making Structured Streaming with static join the best fit.

How to eliminate wrong answers

Option A is wrong because using Change Data Capture from Cosmos DB adds complexity and may not be necessary for infrequently updated reference data; also, Auto Loader with Delta Live Tables is more for batch or file-based ingestion, not directly for Event Hubs streaming. Option B is wrong because Azure Data Factory batch copying introduces latency and does not provide true streaming; it also adds cost and complexity. Option C is wrong because Azure Stream Analytics cannot directly join with Cosmos DB reference data in a streaming manner without additional setup, and outputting Parquet does not provide Delta Lake's schema evolution and ACID benefits.

Practice this question →
43
MCQmedium

You are designing a streaming data solution for IoT devices that generate 10,000 events per second. The data must be processed with sub-second latency and then stored in Azure Data Lake Storage Gen2 for archival. Which Azure service should you use for the stream processing?

A.Azure HDInsight including Spark Structured Streaming
B.Azure Stream Analytics
C.Azure Data Factory
D.Azure Event Hubs
AnswerB

Azure Stream Analytics provides fully managed, sub-second stream processing with built-in windowing and direct output to Data Lake Storage Gen2. Its low-latency engine handles 10,000 events per second while archiving to ADLS Gen2, meeting both latency and storage requirements.

Why this answer

Azure Stream Analytics is the correct choice because it is purpose-built for real-time stream processing with sub-second latency, and it natively integrates with Azure Data Lake Storage Gen2 for output. It can handle 10,000 events per second using its streaming unit scaling, and its SQL-like query language allows for low-latency transformations without the overhead of cluster management.

Exam trap

The trap here is that candidates often confuse Azure Event Hubs (ingestion) with Azure Stream Analytics (processing), or they overcomplicate the solution by choosing HDInsight Spark when a simpler, fully managed service meets the sub-second latency requirement.

How to eliminate wrong answers

Option A is wrong because Azure HDInsight including Spark Structured Streaming introduces additional latency from cluster startup and resource allocation, and it is overkill for a simple streaming pipeline that does not require complex batch or machine learning workloads. Option C is wrong because Azure Data Factory is an orchestration and ETL service designed for batch data movement and transformation, not for sub-second streaming processing. Option D is wrong because Azure Event Hubs is a data ingestion service that can receive 10,000 events per second, but it does not perform stream processing; it only acts as a buffer or event broker before processing.

Practice this question →
44
MCQmedium

You are designing a data processing solution for an e-commerce company that uses Azure Synapse Analytics. The solution must process clickstream data from a web application. The data arrives in JSON format through Azure Event Hubs. You need to load the data into a dedicated SQL pool every 5 minutes with minimal latency. The data volume is about 100 MB every 5 minutes. You want to use PolyBase for loading. Which approach should you use?

A.Use Azure Stream Analytics to transform the JSON data and output directly to the dedicated SQL pool.
B.Use Azure Data Factory with a Copy activity to copy data from Event Hubs to Azure Data Lake Storage Gen2 as JSON files, then use a PolyBase activity to load from ADLS Gen2 to the dedicated SQL pool.
C.Use Azure Databricks to read from Event Hubs, transform the data, and write to the dedicated SQL pool using JDBC.
D.Use PolyBase directly from Event Hubs to dedicated SQL pool by creating an external data source that points to Event Hubs.
AnswerB

PolyBase cannot read Event Hubs directly; it queries external tables over ADLS Gen2 or Blob Storage. Landing the JSON via a Copy activity first satisfies the 5-minute, 100 MB latency requirement, then a PolyBase activity loads it into the dedicated SQL pool.

Why this answer

Using Azure Data Factory to copy data from Event Hubs to ADLS Gen2 as JSON files, then using PolyBase to load into the dedicated SQL pool, is the recommended approach. PolyBase can efficiently load large volumes from ADLS Gen2, and Data Factory provides a scalable, low-latency pipeline. This approach leverages PolyBase's parallel loading capabilities and is cost-effective for 100 MB every 5 minutes.

Exam trap

DP-203 often tests the misconception that PolyBase can directly connect to Event Hubs, but it requires an intermediate storage layer like ADLS Gen2.

How to eliminate wrong answers

Option A is wrong because Azure Stream Analytics can output to a dedicated SQL pool, but it may not be as efficient for batch loading every 5 minutes and could incur higher costs. Option C is wrong because Azure Databricks with JDBC writes may not scale as well as PolyBase and could be more expensive. Option D is wrong because PolyBase cannot directly connect to Event Hubs as an external data source; it requires a supported data source like ADLS Gen2 or Blob Storage.

Practice this question →
45
MCQhard

You are optimizing a pipeline in Azure Data Factory that copies data from Azure Blob Storage to Azure Synapse Analytics. The pipeline uses a copy activity with PolyBase. The data is partitioned by date in Blob Storage. You notice that the load is slow. What is the most likely cause?

A.The source files are stored in Azure Blob Storage instead of Data Lake Storage Gen2
B.The source files are in CSV format instead of Parquet
C.The source files are too many and too small (e.g., thousands of 1 MB files)
D.The sink table has a clustered columnstore index
AnswerC

PolyBase reads many small files inefficiently because each file incurs separate metadata and open/close overhead, and parallelism is limited by file count rather than volume. Thousands of 1 MB files therefore throttle throughput, making file granularity—not total data size—the bottleneck in this Blob Storage to Synapse copy.

Why this answer

PolyBase in Azure Synapse Analytics performs best when reading large, contiguous files. When the source contains thousands of small files (e.g., 1 MB each), PolyBase must initiate a separate read operation for each file, causing excessive overhead from file open/close operations and metadata requests. This dramatically reduces throughput compared to reading fewer, larger files.

Exam trap

The trap here is that candidates often focus on file format (Parquet vs. CSV) or storage type (Blob vs. ADLS Gen2) as the primary performance factor, when in reality the number and size of files is a more common and impactful bottleneck in PolyBase loads.

How to eliminate wrong answers

Option A is wrong because Azure Blob Storage is fully supported as a PolyBase source; Data Lake Storage Gen2 offers hierarchical namespace benefits but does not inherently improve PolyBase load speed. Option B is wrong because while Parquet is more efficient for analytics due to columnar storage and compression, CSV is still a valid PolyBase source and the primary bottleneck here is file count, not format. Option D is wrong because a clustered columnstore index is actually recommended for Synapse Analytics tables to improve query performance and compression; it does not slow down the PolyBase load itself.

Practice this question →
46
MCQhard

You are developing an Azure Databricks notebook that reads a large Delta table, performs a join with a smaller reference table, and writes the result back to Delta Lake. The job runs on a cluster with autoscaling enabled and frequently spills to disk during the join. You need to reduce shuffle and improve performance without changing the result. Which action should you take?

A.Repartition the large Delta table by the join key before the join.
B.Cache the large Delta table in memory before performing the join.
C.Broadcast the smaller reference table in the join using a broadcast hint.
D.Increase the spark.sql.shuffle.partitions value from the default to a much larger number.
AnswerC

Broadcasting the smaller table replicates it to every executor, eliminating the shuffle of the large Delta table during the join. This directly reduces disk spills and network I/O, which are the observed symptoms. Because the reference table is small, the memory overhead is acceptable, and the join result is unchanged, satisfying the requirement to improve performance without altering output.

Why this answer

When joining a large Delta table with a small reference table, broadcasting the small table avoids shuffling the large table entirely. Each executor receives a copy of the small table and performs the join locally, which removes the network exchange and disk spills observed in the job. The result set is identical, and the memory cost is modest because the broadcast side is small.

Exam trap

The trap here is reaching for shuffle-partition tuning or caching when the real fix for a large-to-small join is broadcasting the small side to eliminate the shuffle altogether.

Practice this question →
47
Multi-Selectmedium

Which TWO Azure services can be used to perform real-time data processing on streaming data?

Select 2 answers
A.Azure Synapse Analytics (dedicated SQL pool)
B.Azure Stream Analytics
C.Azure Data Factory
D.Azure Logic Apps
E.Azure Databricks
AnswersB, E

Azure Stream Analytics runs continuous SQL-like queries over event streams from Event Hubs or IoT Hub, emitting results with sub-second latency. This satisfies the real-time processing requirement, since batch engines and storage services cannot transform unbounded data as it arrives.

Why this answer

Azure Stream Analytics (B) is purpose-built for real-time stream processing: it ingests continuous data from sources such as Azure Event Hubs, IoT Hub, or Blob Storage and runs SQL-like queries with temporal windows to produce low-latency outputs. Azure Databricks (E) also supports real-time processing because its Structured Streaming engine (built on Apache Spark) can continuously process streaming data from Event Hubs, Kafka, or IoT Hub with exactly-once semantics. Azure Synapse Analytics dedicated SQL pool (A) is a batch-oriented MPP data warehouse, not a real-time streaming engine, so it does not fit.

Azure Data Factory (C) is an orchestration/ETL service for scheduled batch data movement and transformation, not continuous stream processing. Azure Logic Apps (D) is a workflow automation and integration service triggered by events, but it does not perform real-time analytical stream processing.

Exam trap

Candidates often assume that any service capable of ingesting streaming data (like Synapse dedicated SQL pool) qualifies as real-time processing, or they overlook Databricks because it is often associated with batch analytics. The trap is that Synapse's dedicated SQL pool is a data warehouse, not a streaming engine; Databricks' Structured Streaming is a powerful real-time processing engine on Azure.

Practice this question →
48
Multi-Selecteasy

Which TWO techniques can you use to handle schema drift in Azure Data Factory mapping data flows?

Select 2 answers
A.Enable 'Allow schema drift' in the source transformation
B.Use derived column transformation to handle each new column manually
C.Use column pattern matching to automatically map columns with similar names
D.Use assertion rules to reject rows with unknown columns
E.Use a fixed schema mapping to ignore unknown columns
AnswersA, C

Enabling 'Allow schema drift' in the source transformation lets mapping data flows read columns absent from the defined schema and carry them through the pipeline, directly satisfying the requirement to handle drifting source structures without failure.

Why this answer

Option A is correct because enabling 'Allow schema drift' on the source transformation in a mapping data flow lets the flow read columns that are not defined in the dataset schema at design time, so newly arriving columns flow through instead of being dropped. Option C is correct because column pattern matching (rule-based mapping) lets you define rules such as name or type patterns so that columns with similar names are automatically mapped without editing the flow for every new column. Option B is not appropriate because manually adding a derived column for each new column defeats the purpose of automated drift handling and requires redeployment for every schema change.

Option D is wrong because assertion rules validate row conditions and can fail or reject rows, but they do not adapt the flow to new or renamed columns. Option E is wrong because a fixed schema mapping ignores unknown columns, which is the opposite of handling schema drift.

Exam trap

The trap here is that candidates often confuse 'handling schema drift' with 'ignoring or rejecting unknown columns' (options D and E), or they think manual column-by-column handling (option B) is a valid technique, when in fact ADF provides automated drift handling through the source setting and pattern matching.

Practice this question →
49
MCQhard

Refer to the exhibit. You have an Azure Data Factory pipeline that copies data from a CSV file in Blob Storage to a Synapse dedicated SQL pool table named dbo.Sales. The pipeline fails. The error message indicates that the 'Amount' column in the sink table does not allow NULLs but the source contains NULL values. What is the best way to resolve this issue without losing data?

A.Use a Mapping Data Flow with a Derived Column transformation to replace NULLs with 0
B.Add a filter in the copy activity to exclude rows with NULL Amount
C.Modify the sink table to have a default value for the Amount column
D.Change the sink table column to allow NULLs
AnswerA

A Derived Column transformation evaluates an expression per row, replacing NULL 'Amount' values with 0 before the sink write. This satisfies the sink's NOT NULL constraint while preserving every source row, avoiding data loss that filtering or rejecting rows would cause.

Why this answer

A Mapping Data Flow with a Derived Column transformation allows you to replace NULL values in the 'Amount' column with a default value (e.g., 0) before writing to the Synapse dedicated SQL pool. This resolves the NULL constraint violation without losing any rows, as the data is transformed inline within the pipeline. The copy activity alone cannot perform such transformations, making the Mapping Data Flow the appropriate choice for this ETL scenario.

Exam trap

The trap here is that candidates often assume a default value on the column will automatically replace NULLs during a bulk insert, but in Azure Synapse and most SQL databases, a default only applies when the column is not referenced in the INSERT statement, not when NULL is explicitly provided.

How to eliminate wrong answers

Option B is wrong because filtering out rows with NULL Amount would cause data loss, which violates the requirement to not lose data. Option C is wrong because adding a default value to the sink table column only applies when a column is omitted from an INSERT statement; the copy activity explicitly inserts NULLs, which still violates the NOT NULL constraint regardless of a default. Option D is wrong because changing the sink table column to allow NULLs would alter the schema, potentially breaking downstream dependencies or business rules that require Amount to be non-null.

Practice this question →
50
MCQhard

You are implementing a mapping data flow in Azure Data Factory that joins a large fact table in Azure Synapse Analytics with a slowly changing dimension (SCD) table in Azure SQL Database. The fact table has 500 million rows and the dimension has 2 million rows. You need to optimize the join performance and minimize data movement. The dimension table is small enough to fit in memory. Which join type should you configure in the data flow?

A.Sort-merge join
B.Cross join
C.Broadcast join
D.Hash join
AnswerC

A broadcast join sends the smaller dataset to all compute nodes, allowing the larger dataset to be partitioned and processed locally without shuffling. Since the dimension table has only 2 million rows and fits in memory, broadcasting it eliminates the need to shuffle the 500-million-row fact table, drastically reducing data movement and improving performance. This is the optimal choice for joining a large fact with a small dimension.

Why this answer

Broadcast join is designed for scenarios where one dataset is small enough to fit in memory. By broadcasting the dimension table, the large fact table is not shuffled, minimizing data movement and improving performance. Other join types like sort-merge or hash without broadcast would require shuffling the large fact table, leading to unnecessary data movement and slower execution.

Exam trap

The trap here is assuming that a hash join always avoids shuffling, when in mapping data flows only a broadcast join explicitly sends the small table to all nodes to avoid moving the large table.

Practice this question →
51
MCQmedium

You are building an Azure Data Factory pipeline that calls an external REST API returning a JSON array of records. The API paginates results using a 'nextLink' field in the response body, and the number of pages varies per run. You must ingest all pages into Azure Blob Storage in a single pipeline run. Which activity configuration should you use?

A.A Copy activity with an HTTP source and no pagination rule, relying on the API to return all records in one response.
B.A Web activity followed by a ForEach activity that iterates over a hardcoded page count.
C.A Copy activity with an HTTP source and a range-based pagination rule that increments a query parameter until no records are returned.
D.A Copy activity with an HTTP source and pagination rule set to 'NextLink' using the body field that contains the next page URL.
AnswerD

The Copy activity's HTTP connector supports pagination rules that read the next page URL from a response body field. Pointing the rule at the 'nextLink' property makes the activity follow each server-provided link until the field is absent or null. This handles a variable number of pages within one activity and writes the combined result to Blob Storage.

Why this answer

When a REST API advertises the next page through a body field such as nextLink, the HTTP connector's pagination rule must be set to that field. The Copy activity then requests each returned URL in turn until the field is missing, capturing a variable number of pages in one run without custom looping.

Exam trap

The trap here is choosing range or offset pagination when the API hands back an explicit next-page URL in the response body.

Practice this question →
52
MCQmedium

You are building an Azure Stream Analytics job that reads from an Azure Event Hub capturing device telemetry. The job must emit results into an Azure Synapse Analytics dedicated SQL pool. You need to minimize latency and avoid intermediate storage. What should you do?

A.Configure the job to output to Azure Cosmos DB, then use a Synapse pipeline to copy data into the dedicated SQL pool.
B.Configure the Stream Analytics job to output directly to the dedicated SQL pool using the Azure Synapse Analytics output adapter.
C.Use Azure Functions as the output to insert rows into the dedicated SQL pool.
D.Write the Stream Analytics output to Azure Blob Storage, then use PolyBase in the dedicated SQL pool to load the data.
AnswerB

Stream Analytics natively supports Azure Synapse Analytics as an output, writing directly to a dedicated SQL pool table via the built-in connector. This avoids staging the data in Blob Storage or Data Lake, reducing end-to-end latency and eliminating an extra hop. The connector batches rows for efficient inserts, so it suits near-real-time ingestion into a dedicated SQL pool without custom code.

Why this answer

Stream Analytics provides a first-class output adapter for Azure Synapse Analytics dedicated SQL pools, enabling direct writes without staging. This minimizes latency and avoids intermediate storage, matching the requirement. The other approaches insert extra services or storage layers, adding delay and operational overhead that the scenario specifically seeks to avoid.

Exam trap

The trap here is assuming that a dedicated SQL pool cannot be written to directly from a streaming job and that data must always be staged in Blob Storage or Data Lake first.

Practice this question →
53
MCQmedium

You are building an Azure Synapse Analytics pipeline that processes JSON files landing in Azure Data Lake Storage Gen2. The files contain nested arrays representing order line items. You need to flatten this nested structure into a tabular format within a Mapping Data Flow before loading to a dedicated SQL pool. The solution must minimize data movement and avoid writing intermediate files to storage. Which transformation should you use to flatten the nested arrays?

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

The Flatten transformation in Mapping Data Flows is specifically designed to unroll nested arrays into multiple rows, preserving parent column values. It operates in-memory during the data flow execution, so no intermediate storage is required. This directly addresses the requirement to flatten nested JSON arrays without writing intermediate files, making it the correct choice for this scenario.

Why this answer

Flatten transformation is purpose-built for unrolling nested arrays into rows within Mapping Data Flows. It processes data in-memory during pipeline execution, eliminating the need for intermediate files. Derived Column, Aggregate, and Pivot transformations do not change the grain from array elements to rows, so they cannot produce the required tabular output from nested JSON.

Exam trap

The trap here is assuming that any transformation that manipulates columns can flatten arrays, but only Flatten explicitly unrolls nested structures into rows.

Practice this question →
54
Multi-Selectmedium

Which THREE options are valid ways to transform data in Azure Synapse Analytics?

Select 3 answers
A.Use Power Query online in Synapse pipelines.
B.Use T-SQL scripts in a dedicated SQL pool.
C.Use Mapping Data Flows in Synapse pipelines.
D.Use Spark notebooks in Synapse Spark pools.
E.Use Azure Machine Learning pipelines for data wrangling.
AnswersB, C, D

T-SQL is a primary way to transform data in Synapse SQL pools.

Why this answer

T-SQL scripts are a native and primary method for transforming data within a dedicated SQL pool in Azure Synapse Analytics. You can use CREATE TABLE AS SELECT (CTAS), INSERT...SELECT, and other T-SQL statements to perform complex transformations like aggregations, joins, and data cleansing directly on the distributed data, leveraging the MPP (Massively Parallel Processing) engine for high performance.

Exam trap

The trap here is that candidates often confuse Power Query Online (a Power BI/ADF feature) with Mapping Data Flows (a Synapse pipeline activity), or assume Azure Machine Learning pipelines are valid for data wrangling in Synapse, when in fact they are separate services for ML lifecycle management.

Practice this question →
55
MCQmedium

You are designing a data processing solution in Azure Synapse Analytics. The solution must process streaming data from Azure Event Hubs and store the results in a dedicated SQL pool. The solution must support exactly-once semantics and handle late-arriving data. Which Azure service should you use to implement this solution?

A.Azure Data Factory with tumbling window trigger.
B.Azure Stream Analytics.
C.Azure Functions with Event Hubs trigger.
D.Azure HDInsight Spark Structured Streaming.
AnswerB

Azure Stream Analytics provides built-in exactly-once processing guarantees and event-time handling with configurable late-arrival and out-of-order tolerances, satisfying both constraints. Alternatives such as Spark Structured Streaming or Event Hubs Capture require additional engineering to achieve the same delivery and watermark semantics.

Why this answer

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

Exam trap

The trap here is that candidates often confuse batch-oriented services like Azure Data Factory with streaming solutions, or assume that any event-driven compute (like Azure Functions) can provide exactly-once semantics and late-arriving data handling without understanding the specialized streaming engine requirements.

How to eliminate wrong answers

Option A is wrong because Azure Data Factory with a tumbling window trigger is a batch-oriented orchestration service that cannot process streaming data in real time; it lacks native support for exactly-once semantics in streaming contexts and cannot handle late-arriving data with event-time ordering. Option C is wrong because Azure Functions with an Event Hubs trigger processes events individually or in small batches, does not provide built-in exactly-once output guarantees to a dedicated SQL pool, and lacks native support for late-arriving data handling such as watermarking or out-of-order policies. Option D is wrong because Azure HDInsight Spark Structured Streaming requires significant manual configuration for exactly-once semantics (e.g., managing checkpoint locations and idempotent sinks) and does not offer the same level of integrated, low-latency output to dedicated SQL pools as Azure Stream Analytics; it also adds operational overhead for cluster management.

Practice this question →
56
Multi-Selectmedium

You are building an Azure Stream Analytics job that processes JSON telemetry from Azure Event Hubs. The events contain a nested array field named `readings` with sensor values. You need to transform the data so that each sensor reading becomes a separate output row, and then write the results to Azure Synapse Analytics. Which two actions should you perform? (Choose two.)

Select 2 answers
A.Use the STREAMING TIMESTAMP BY clause to reorder events and then apply a windowing function to explode the array.
B.Use the CROSS APPLY operator with the GetArrayElements function to expand the `readings` array into individual events.
C.Configure the output to Azure Synapse Analytics with the 'Allow user-defined functions' setting enabled.
D.Use the GetArrayElements function in a subquery to extract array elements and then use a JOIN to correlate them with the parent event.
E.Use the JavaScript UDF to parse the JSON and return a single concatenated string of all readings.
AnswersB, D

CROSS APPLY combined with GetArrayElements is the correct method in Stream Analytics to flatten a nested array. It returns one row per array element, enabling downstream processing of each sensor reading separately. This is exactly what the scenario requires to transform nested JSON into a flat rowset for Synapse Analytics.

Why this answer

The requirement is to flatten a nested array into separate rows. In Azure Stream Analytics, this is achieved by using GetArrayElements with either CROSS APPLY or a JOIN in a subquery. Both methods return one row per array element, which can then be written to Synapse Analytics.

Other options do not perform the necessary row expansion.

Exam trap

The trap here is assuming that a JavaScript UDF or output setting can flatten arrays, when the correct approach requires a specific array-expanding function in the query.

Practice this question →
57
Multi-Selecthard

You are implementing a medallion architecture in Azure Databricks. The silver layer must contain deduplicated, conformed records, and the gold layer must serve aggregated reporting tables. You need to choose Delta Lake operations that support incremental, idempotent updates as new bronze files arrive. Which two operations should you use? (Choose two.)

Select 2 answers
A.Use CREATE OR REPLACE TABLE AS SELECT to rebuild the silver table from all bronze files each run
B.Stream bronze files into the silver table with Structured Streaming and foreachBatch applying MERGE
C.Write the gold aggregates with overwrite mode partitioned by report date
D.OPTIMIZE the silver table with ZORDER BY the business key after each load
E.MERGE INTO the silver table using the bronze staging data matched on a business key
AnswersB, E

Structured Streaming with foreachBatch lets each micro-batch run a MERGE against the silver table, combining incremental ingestion with idempotent upserts keyed on a business key. Checkpointing tracks processed offsets so already-consumed files are not reprocessed, and the MERGE guarantees that any replay converges to the same state. This matches the incremental, idempotent requirement precisely.

Why this answer

Incremental, idempotent updates in a medallion pipeline rely on keyed upserts rather than full rebuilds. MERGE INTO delivers atomic, key-based convergence, and Structured Streaming with foreachBatch applies that same MERGE per micro-batch while checkpointing progress. Together they let new bronze files flow into silver without duplication, and the gold aggregates can then be computed from the conformed silver data.

Exam trap

The trap here is equating file compaction or full-table rebuilds with idempotent incremental processing, when only keyed upserts provide that guarantee.

Practice this question →
58
MCQmedium

You are using Azure Data Lake Storage Gen2 as the data lake for your organization. You need to process files in the 'incoming' folder using a scheduled Azure Databricks notebook. After processing, the files should be moved to the 'processed' folder. The files are large (up to 10 GB) and you want to minimize the time to move them. Which approach should you use?

A.Use the Azure Databricks dbutils.fs.mv() to move the file.
B.Use Azure Data Factory with a Copy activity to move the file, then delete the source.
C.Change the file's metadata to update its directory path.
D.Use the Azure Databricks dbutils.fs.cp() to copy the file to the processed folder, then delete the original.
AnswerA

The `dbutils.fs.mv()` command performs a metadata rename operation within the same ADLS Gen2 account, so no file data is copied. This satisfies the requirement to minimise move time for files up to 10 GB, since relocating between the `incoming` and `processed` folders is near-instantaneous rather than proportional to file size.

Why this answer

`dbutils.fs.mv()` performs a metadata-only rename operation on Azure Data Lake Storage Gen2, which is instantaneous regardless of file size. This avoids any data movement, making it the fastest approach for moving large files (up to 10 GB) between folders within the same storage account.

Exam trap

The trap here is that candidates often assume moving large files requires copying, but Azure Data Lake Storage Gen2's hierarchical namespace enables instant metadata-only renames, making `dbutils.fs.mv()` the optimal choice.

How to eliminate wrong answers

Option B is wrong because Azure Data Factory Copy activity physically copies the file data across folders, which is slower and incurs additional read/write costs, even though it can delete the source afterward. Option C is wrong because Azure Data Lake Storage Gen2 does not support moving files by changing metadata; directory paths are part of the file's hierarchical namespace and cannot be updated via metadata alone. Option D is wrong because `dbutils.fs.cp()` performs a full data copy, which is time-consuming for large files, and then requires an explicit delete, adding unnecessary overhead compared to a rename.

Practice this question →
59
MCQhard

You are implementing a real-time analytics solution using Azure Stream Analytics. The job ingests data from Azure Event Hubs and must output to an Azure SQL Database. You need to ensure that the job can handle out-of-order events and produce accurate aggregations over 5-minute windows. Which setting should you configure?

A.Set the 'Late arrival tolerance' to 5 minutes and use a Sliding Window of 5 minutes.
B.Set the 'Event ordering' to 'Adjust' and use a Session Window with a 5-minute timeout.
C.Set the 'Out-of-order tolerance' to 0 and use a Hopping Window of 5 minutes with a 1-minute hop.
D.Set the 'Out-of-order tolerance' to 5 minutes and use a Tumbling Window of 5 minutes.
AnswerD

The out-of-order tolerance allows events to be accepted up to 5 minutes late, and a Tumbling Window aggregates events into non-overlapping 5-minute intervals. This combination ensures that late events are included in the correct window, producing accurate aggregations.

Why this answer

To handle out-of-order events and aggregate over fixed 5-minute windows, configure the out-of-order tolerance to 5 minutes and use a Tumbling Window. The tolerance allows late events to be processed, and the Tumbling Window provides non-overlapping, fixed-duration aggregations. Other window types or settings do not meet both requirements simultaneously.

Exam trap

The trap here is mixing up out-of-order tolerance with late arrival tolerance, and choosing overlapping or dynamic windows instead of the fixed Tumbling Window.

Practice this question →
60
MCQeasy

Your team runs Azure Data Factory pipelines that must copy files from an on-premises file share to Azure Data Lake Storage Gen2 on a nightly schedule. The on-premises network blocks inbound connections and the data must not be exposed to the public internet. You need to enable connectivity without opening firewall ports. What should you deploy?

A.A self-hosted integration runtime installed on a machine in the on-premises network.
B.An Azure ExpressRoute circuit provisioned through a connectivity provider.
C.An Azure VPN Gateway with a site-to-site connection to the on-premises network.
D.An Azure integration runtime with a managed virtual network enabled.
AnswerA

A self-hosted integration runtime runs inside the on-premises network and initiates outbound connections to Azure over HTTPS, so no inbound firewall ports are required. It performs the copy from the file share and transfers data to Data Lake Storage Gen2, satisfying the connectivity and exposure constraints. Data Factory dispatches activities to this runtime rather than reaching the share directly.

Why this answer

A self-hosted integration runtime is installed inside the on-premises network and opens only outbound connections to Azure, so the firewall needs no inbound rules. It executes the copy from the file share and writes to Data Lake Storage Gen2, giving Data Factory a reachable execution host without exposing the internal share to the internet.

Exam trap

The trap here is assuming that any private network connection such as a VPN or ExpressRoute also provides the execution host, when the runtime itself is what reads the on-premises file share.

Practice this question →
61
MCQeasy

You are creating an Azure Synapse Analytics pipeline that must copy data from an Azure SQL Database into a dedicated SQL pool. The destination table already exists and the pipeline must append new rows without truncating existing data. Which staging and load option should you configure in the Copy activity?

A.Set the sink write behavior to truncate and rely on the pre-copy script to reinsert
B.Use a stored procedure sink that executes a MERGE statement against the destination table
C.Set the sink write behavior to insert and use PolyBase or COPY statement staging
D.Set the sink write behavior to upsert and provide a key column mapping
AnswerC

For a dedicated SQL pool sink, the Copy activity offers insert as the write behavior, which appends rows without truncating. Enabling staging with PolyBase or the COPY statement loads through Azure Blob Storage or Data Lake Storage, which is the recommended high-throughput path for this connector pair. This appends new rows while preserving existing data in the destination table.

Why this answer

When the destination table already exists and new rows must be appended, the dedicated SQL pool sink uses the insert write behavior, which does not truncate. Pairing it with staging through PolyBase or the COPY statement gives the high-throughput bulk load path recommended for this connector pair, preserving existing rows while adding the incoming set.

Exam trap

The trap here is reaching for a MERGE or upsert pattern when the requirement is a simple append, which the insert write behavior already provides.

Practice this question →
62
MCQhard

You are analyzing a Kusto query in Azure Data Explorer that calculates total sales per product for January 2024 and filters for products with sales over 10,000. The query uses the materialize() function. You notice that the query runs slower than expected. What is the primary reason the materialize() function may not be providing the expected performance benefit in this query?

A.The join with the Products table forces a shuffle that bypasses the materialized result
B.The query uses summarize, which already materializes results internally
C.The datetime range filter is not sargable, causing full table scan
D.The materialize() result is referenced only once in the query, so materialization adds unnecessary overhead
AnswerD

materialize() caches an intermediate result to avoid recomputation when referenced multiple times. With only a single reference, the query pays the cost of writing and reading the cached table without any reuse benefit, so the overhead outweighs the saving and slows execution.

Why this answer

Materialize() only provides performance benefit when the materialized result is referenced multiple times. In this query, the materialized result is used only once, so the overhead of materialization (storing the result in memory) outweighs any benefit, potentially making the query slower. Option A is incorrect because there is no join with a Products table; the query uses a single table.

Option B is incorrect because summarize does not inherently materialize results; it computes aggregations on the fly. Option C is incorrect because datetime range filters in Kusto are sargable and do not cause full table scans.

Practice this question →
63
Multi-Selecteasy

You are optimizing a Spark DataFrame transformation in Azure Synapse Analytics. The DataFrame has 20 columns and 100 million rows. You notice that the job is slow due to many small files being written to the output. Which two actions can you take to reduce the number of output files? (Choose two.)

Select 2 answers
A.Use coalesce() to reduce the number of partitions without a shuffle.
B.Enable caching on the DataFrame before writing.
C.Apply bucketing on a column to group data.
D.Increase the number of partitions using repartition() with a larger number.
E.Use repartition() with a smaller number of partitions.
AnswersA, E

coalesce() merges partitions into the requested count without a full shuffle, so each task writes fewer, larger files. On a 100-million-row DataFrame this directly reduces the small-file problem while avoiding the network cost of repartitioning.

Why this answer

Option A is correct because coalesce() reduces the number of partitions by merging existing partitions without performing a full shuffle, which directly decreases the number of output files written and is efficient when reducing partitions. Option E is correct because repartition() with a smaller number of partitions also reduces the partition count, and although it triggers a shuffle, it results in fewer output files being written. Option B is incorrect because caching only stores the DataFrame in memory or disk to speed up repeated access; it does not change the number of partitions or output files.

Option C is incorrect because bucketing organizes data into buckets for join or query optimization and does not reduce the number of files written by a DataFrame write operation. Option D is incorrect because increasing partitions with repartition() using a larger number would create more partitions and therefore more output files, worsening the small-file problem.

Exam trap

The trap here is that candidates often confuse `coalesce()` with `repartition()`, assuming both cause a shuffle, or they mistakenly think increasing partitions (Option D) will improve performance when it actually exacerbates the small-file issue.

Practice this question →
64
MCQmedium

You are building an Azure Stream Analytics job that reads JSON telemetry from an Azure Event Hub, calculates a 5-minute tumbling window average per device, and writes results to an Azure Synapse Analytics dedicated SQL pool. The stream must handle occasional bursts of late-arriving events by including events that arrive up to 3 minutes after the window closes. You need to configure the job's event ordering settings to meet the late-arrival requirement while minimizing memory usage. What should you do?

A.Increase the streaming units for the job and leave the default event ordering settings unchanged.
B.Set the late arrival tolerance to 3 minutes and the out-of-order tolerance to 0 seconds.
C.Set the out-of-order tolerance to 3 minutes and the late arrival tolerance to 0 seconds.
D.Set both the late arrival tolerance and the out-of-order tolerance to 3 minutes.
AnswerB

Late arrival tolerance defines how long Stream Analytics waits for events that arrive after the window end before finalizing window results. Setting it to 3 minutes directly satisfies the requirement. Out-of-order tolerance controls reordering of events within the stream, not late arrivals, so setting it to 0 seconds minimizes buffering and memory, which is appropriate here because the scenario only requires handling late-arriving events.

Why this answer

Late-arriving events are handled by the late arrival tolerance, which delays window finalization to include events that arrive after the window end. Out-of-order tolerance is separate and controls reordering within the stream. To meet the 3-minute late-arrival requirement while minimizing memory, set late arrival tolerance to 3 minutes and keep out-of-order tolerance low, as only late arrivals need extended buffering.

Exam trap

The trap here is confusing late arrival tolerance with out-of-order tolerance, assuming both control late events when only late arrival tolerance extends the window past its end time.

Practice this question →
65
MCQmedium

You are designing a data transformation solution for a retail company. The company receives daily CSV files from 200 stores via SFTP. The files must be cleaned, validated, and aggregated before loading into Azure Synapse dedicated SQL pool. The solution must minimize administrative overhead and support easy monitoring. Which approach do you recommend?

A.Use Azure Functions to process each file and write to Synapse via REST API
B.Use PolyBase external tables to load raw data and then use T-SQL stored procedures for transformation
C.Use Azure Databricks with Python notebooks to process the files and write to Synapse
D.Use Azure Data Factory with Mapping Data Flows to clean, validate, and aggregate the data, then load into Synapse SQL pool
AnswerD

Mapping Data Flows provide a code-free, visually monitored transformation canvas inside Azure Data Factory, handling the clean, validate and aggregate steps across 200 SFTP sources without managing clusters. This satisfies the low administrative overhead and easy monitoring constraints while loading into the dedicated SQL pool.

Why this answer

Azure Data Factory (ADF) with Mapping Data Flows provides a fully managed, code-free ETL service that can read CSV files from SFTP, perform cleaning, validation, and aggregation at scale using Spark clusters, and load the results directly into Azure Synapse dedicated SQL pool via the PolyBase sink. This minimizes administrative overhead by eliminating infrastructure management and supports easy monitoring through ADF’s built-in integration with Azure Monitor and pipeline run views.

Exam trap

The trap here is that candidates often overestimate the simplicity of Azure Functions for batch ETL or assume PolyBase alone handles transformations, when in fact ADF Mapping Data Flows are purpose-built for visual, scalable, and monitorable ETL with minimal overhead.

How to eliminate wrong answers

Option A is wrong because Azure Functions are stateless, event-driven compute units that lack native connectors for SFTP and Synapse, requiring custom code for file parsing, state management, and batch loading, which increases administrative overhead and complexity. Option B is wrong because PolyBase external tables can only load raw data into staging tables, but the transformation logic (cleaning, validation, aggregation) would need to be implemented in T-SQL stored procedures, which are harder to monitor, scale, and maintain compared to a visual ETL tool like ADF. Option C is wrong because Azure Databricks with Python notebooks introduces significant administrative overhead for cluster management, notebook orchestration, and monitoring, and requires more specialized skills than ADF’s low-code Mapping Data Flows, making it less suitable for minimizing overhead.

Practice this question →
66
Multi-Selecteasy

Which TWO of the following are supported sources for Azure Data Factory Copy activity? (Choose two.)

Select 2 answers
A.Power BI Dataset
B.Azure DevOps
C.Amazon S3
D.Azure Blob Storage
E.Azure Analysis Services
AnswersC, D

Amazon S3 is supported via the Amazon S3 connector.

Why this answer

Amazon S3 is correct (C) because Azure Data Factory provides a native Amazon S3 connector that the Copy activity can use as a source, reading objects via the S3 API with linked-service credentials. Azure Blob Storage is correct (D) because it is one of ADF's core supported stores, and the Copy activity can read blobs through the Azure Blob Storage connector. Power BI Dataset (A) is not a Copy activity source; ADF interacts with Power BI only for certain dataset-based scenarios, not as a copy source.

Azure DevOps (B) is not a data store connector for Copy activity sources. Azure Analysis Services (E) is an analytical model, not a supported Copy activity source store.

Exam trap

The trap here is that candidates often confuse Azure Analysis Services (a semantic model) with Azure SQL Database or Azure Synapse, which are valid sources, leading them to incorrectly select it as a supported source for Copy activity.

Practice this question →
67
MCQmedium

You have a dedicated SQL pool in Azure Synapse that stores a fact table with over 100 billion rows. Query performance is degrading over time. You notice that the table is hash-distributed on a column with many duplicate values. What is the most likely impact?

A.Statistics on the table are outdated.
B.The table is not properly partitioned.
C.Data compression is not working efficiently.
D.Data is unevenly distributed across distributions, causing some distributions to be overloaded.
AnswerD

Hash distribution on a high-duplicate column funnels many identical values into the same distribution, producing data skew. Some distributions hold disproportionate rows, so those nodes process far more data, degrading query performance across the pool.

Why this answer

D is correct because a hash-distributed table with a column that has many duplicate values leads to data skew. When the hash function maps many rows to the same distribution, some distributions become overloaded with data while others are underutilized. This imbalance causes query performance to degrade as the overloaded distributions become bottlenecks for processing.

Exam trap

The trap here is that candidates often confuse distribution skew with partitioning or statistics issues, but the key clue is the mention of 'many duplicate values' in the hash-distributed column, which directly points to data skew as the root cause.

How to eliminate wrong answers

Option A is wrong because outdated statistics can cause suboptimal query plans, but the primary issue described is data skew due to hash distribution on a column with many duplicates, not statistics freshness. Option B is wrong because partitioning is a separate concept from distribution; while partitioning can help with partition elimination, it does not address the fundamental data skew caused by hash distribution on a high-duplicate column. Option C is wrong because data compression efficiency is affected by data patterns and storage, not directly by distribution skew; compression works at the page level and is not the root cause of query performance degradation from uneven distribution.

Practice this question →
68
MCQeasy

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 and runs successfully during business hours. However, after a recent network security update, the pipeline fails with a connection error to the on-premises SQL Server. What is the most likely cause?

A.A firewall rule on the on-premises SQL Server is blocking the self-hosted integration runtime.
B.The Azure Blob Storage account has been moved to a different subscription.
C.The self-hosted integration runtime node has been de-registered from Azure Data Factory.
D.The blob container has reached its maximum capacity.
AnswerA

The self-hosted integration runtime initiates outbound connections to the on-premises SQL Server, so a new firewall rule on that server blocking the runtime's host is the most likely cause. The runtime itself is unaffected by Azure-side network changes, and credentials were unchanged.

Why this answer

The self-hosted integration runtime (SHIR) connects to on-premises SQL Server via TCP port 1433 by default. A recent network security update likely added a firewall rule on the SQL Server or the on-premises network that blocks outbound or inbound traffic on this port, preventing the SHIR from establishing a connection. Since the pipeline ran successfully before the update, the most probable cause is a new firewall restriction targeting the SHIR's IP address or subnet.

Exam trap

The trap here is that candidates may confuse a source-side connectivity failure with a sink-side or authentication issue, but the question explicitly states a 'connection error to the on-premises SQL Server,' which points directly to network or firewall blocking at the source, not to storage capacity or SHIR registration status.

How to eliminate wrong answers

Option B is wrong because moving an Azure Blob Storage account to a different subscription does not affect the connectivity between the on-premises SHIR and the on-premises SQL Server; it would only change the storage account's resource ID and require updating linked service credentials, not cause a connection error to SQL Server. Option C is wrong because if the SHIR node were de-registered, the pipeline would fail with an integration runtime not found error, not a connection error to the on-premises SQL Server; the error message would reference the SHIR status, not a network-level timeout or refused connection. Option D is wrong because a blob container reaching maximum capacity (5 TB per container) would result in a storage write error (e.g., 403 or 409) when copying data, not a connection error to the on-premises SQL Server; the error would occur at the sink, not the source.

Practice this question →
69
MCQhard

You are optimizing a data pipeline in Azure Synapse Analytics that loads data from a CSV file in ADLS Gen2 into a dedicated SQL pool using PolyBase. The load is slow and you need to improve performance. Which action would be MOST effective?

A.Increase the service level (DWU) of the dedicated SQL pool.
B.Change the file format from CSV to Avro.
C.Combine the CSV files into fewer, larger files before loading.
D.Use Azure Data Factory to stage the data in Azure Blob Storage before loading.
AnswerC

PolyBase parallelises reads across files, so many small CSVs create excessive per-file overhead and metadata operations. Consolidating into fewer, larger files lets each reader stream more data per task, directly addressing the throughput bottleneck in the load.

Why this answer

Combining many small CSV files into fewer, larger files reduces the number of file open/close operations and minimizes the overhead of PolyBase's external file enumeration and parallel split logic. PolyBase performs best when each file is at least 256 MB, as it can then assign a full file to each distribution, avoiding the overhead of splitting tiny files across multiple threads.

Exam trap

The trap here is that candidates assume scaling up (DWU) or changing file formats always improves performance, but the exam specifically tests the understanding that PolyBase's parallel processing is most efficient when file sizes align with distribution boundaries, making file consolidation the most effective optimization.

How to eliminate wrong answers

Option A is wrong because increasing DWU scales resources but does not address the root cause of slow PolyBase loads—file fragmentation and metadata overhead—and may incur unnecessary cost. Option B is wrong because changing to Avro improves compression and schema evolution but does not reduce the number of file operations; the performance gain from file count reduction is more direct. Option D is wrong because staging in Azure Blob Storage adds an extra copy step without reducing the number of files PolyBase must process; the bottleneck remains the file enumeration and split overhead.

Practice this question →
70
MCQmedium

You are implementing a mapping data flow in Azure Data Factory that processes data from an Azure SQL Database. The data flow includes a derived column transformation that adds a new column based on a complex expression. You need to ensure the expression handles null values appropriately. Which function should you use to replace null values with a default?

A.nullIf
B.iif
C.coalesce
D.isNull
AnswerC

The coalesce function returns the first non-null value from a list of expressions. In a derived column transformation, you can use coalesce(column, default) to replace nulls with a default value. This is the standard way to handle nulls in Azure Data Factory mapping data flows. It is efficient and works with various data types, ensuring that null values are substituted appropriately.

Why this answer

The coalesce function is designed to return the first non-null value from a list, making it ideal for replacing nulls with a default. In a derived column transformation, you can write coalesce(column, 'default') to substitute nulls. Other functions like isNull, nullIf, and iif serve different purposes: isNull checks for null, nullIf creates null, and iif requires a condition.

Therefore, coalesce is the correct function for this scenario.

Exam trap

The trap here is confusing functions that check for nulls with those that replace them, or using a more complex conditional expression when a dedicated function exists.

Practice this question →
71
MCQhard

Your team uses Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. The query performance is inconsistent, and some queries take a long time to execute. You need to improve query performance. What should you do?

A.Increase the MAXDOP setting in the query
B.Create statistics on the columns used in joins and filters
C.Move the data to a dedicated SQL pool
D.Convert the Parquet files to CSV format
AnswerB

Statistics help the optimizer generate efficient plans.

Why this answer

(Create statistics on the columns used in joins and filters) is correct because serverless SQL pool relies on statistics for optimal query plans. Option A (Increase the maximum degree of parallelism) is not directly applicable. Option C (Convert to CSV) would degrade performance.

Option D (Use a dedicated SQL pool) may be an option but not the best immediate step.

Practice this question →
72
MCQmedium

A data engineering team is building a batch processing solution for a financial services company. Data is ingested daily from multiple sources into Azure Data Lake Storage Gen2 in CSV format. The data must be transformed (filtered, aggregated, joined) and loaded into Azure Synapse Analytics dedicated SQL pool. The team must optimize for cost and performance. The total data volume is 2 TB per day. The team has the following options: Option A: Use Azure Data Factory pipelines with copy activity to load raw CSV files into Synapse staging tables, then use T-SQL stored procedures in Synapse to perform transformations. Option B: Use Azure Databricks with Auto Loader to incrementally ingest CSV files, perform transformations in Spark, and write the results to Synapse using the Spark Synapse connector. Option C: Use Azure Data Factory with mapping data flows to transform the data in a serverless environment and then write to Synapse. Option D: Use Azure Synapse Pipelines (built on ADF) with a notebook activity that runs a PySpark notebook in Synapse Spark pool to transform and load data. Which option should the team choose to minimize cost and management overhead while meeting performance requirements?

A.Option B
B.Option C
C.Option A
D.Option D
AnswerB

Serverless, cost-effective, low overhead.

Why this answer

Correct answer: B (Option C — Azure Data Factory mapping data flows). Mapping data flows execute on a serverless Azure Data Factory integration runtime, scaling automatically and costing only per run, which minimizes cost and management overhead. Answer choice A (Option B, Azure Databricks with Auto Loader) requires managing Spark clusters.

Answer choice C (Option A, Azure Data Factory copy activity plus T-SQL stored procedures) requires staging tables and stored procedures. Answer choice D (Option D, Synapse Pipelines with a notebook activity) requires managing Synapse Spark pools. Therefore, mapping data flows are the most cost-effective and lowest-overhead option.

Practice this question →
73
MCQhard

You are designing a real-time analytics solution for IoT devices that emit telemetry data every second. The data must be aggregated every minute and stored in Azure SQL Database for historical analysis. You need to minimize latency and operational overhead. Which approach should you recommend?

A.Use Azure Databricks with Structured Streaming to aggregate and write to SQL Database
B.Use Event Hubs Capture to store raw data in blob storage, then use Azure Data Factory to load into SQL Database hourly
C.Use Azure Stream Analytics with a tumbling window of 1 minute and output to Azure SQL Database
D.Use Azure Functions to process events and write to SQL Database
AnswerC

A tumbling window aggregates each minute's one-second telemetry into a single row, cutting write volume and latency while satisfying the one-minute aggregation requirement. Stream Analytics is fully managed, so operational overhead stays low, and its native Azure SQL Database output sinks results directly for historical analysis.

Why this answer

Azure Stream Analytics natively supports real-time stream processing with tumbling windows, allowing you to aggregate IoT telemetry data every minute and output directly to Azure SQL Database with minimal latency. This approach avoids the overhead of managing clusters (Databricks) or orchestrating batch loads (Data Factory), directly meeting the requirement for low latency and operational simplicity.

Exam trap

The trap here is that candidates often over-engineer the solution by choosing Databricks (Option A) for its flexibility, overlooking that Stream Analytics is purpose-built for low-latency, windowed aggregations with minimal operational overhead, while Databricks adds unnecessary complexity for simple time-based aggregations.

How to eliminate wrong answers

Option A is wrong because Azure Databricks with Structured Streaming introduces significant operational overhead for cluster management and is overkill for simple minute-level aggregation, plus it adds latency from Spark job initialization and checkpointing. Option B is wrong because Event Hubs Capture stores raw data in blob storage, and using Azure Data Factory to load hourly into SQL Database introduces at least 60 minutes of latency, failing the real-time requirement. Option D is wrong because Azure Functions are stateless and event-driven, lacking built-in windowing capabilities for time-based aggregation, so you would need to implement custom state management (e.g., using Durable Functions or external storage), increasing complexity and latency.

Practice this question →
74
MCQmedium

Your organization is using Azure Synapse Analytics dedicated SQL pool. You notice that queries are running slower than expected. Upon reviewing the execution plans, you see that some queries are performing table scans instead of seeks on large fact tables. What is the most likely cause?

A.The statistics on the tables are outdated or missing.
B.The tables are distributed using round-robin distribution.
C.Result-set caching is disabled.
D.The resource class for the user is set to smallrc.
AnswerA

Dedicated SQL pool relies on statistics to estimate cardinality and choose seeks over scans. Outdated or missing statistics mislead the optimiser into underestimating selectivity, producing full table scans on large fact tables. Updating statistics restores accurate costing and enables index seeks.

Why this answer

Outdated or missing statistics prevent the Azure Synapse Analytics dedicated SQL pool query optimizer from accurately estimating row counts and data distribution. Without reliable statistics, the optimizer may incorrectly choose a table scan over a more efficient index seek or partition elimination, leading to slower query performance on large fact tables.

Exam trap

The trap here is that candidates often confuse performance issues caused by distribution type or resource class with the optimizer's reliance on statistics, overlooking that even with optimal distribution and sufficient resources, stale statistics force scans instead of seeks.

How to eliminate wrong answers

Option B is wrong because round-robin distribution evenly distributes data across distributions without considering join keys, which can cause data movement but does not directly cause table scans instead of seeks; scans are a symptom of missing statistics or poor index usage. Option C is wrong because result-set caching only affects repeated execution of the same query by storing results, not the initial query plan choice between scan and seek. Option D is wrong because the resource class (e.g., smallrc) controls memory and concurrency slots for the user, not the query optimizer's decision to use scans versus seeks; scans occur regardless of resource class if statistics are stale.

Practice this question →
75
MCQeasy

You are developing an Azure Data Factory pipeline that must call an external REST API, parse the JSON response, and load selected fields into an Azure SQL Database. The API requires a bearer token that expires every hour, so the pipeline must obtain a fresh token before each call. You need to implement the token acquisition and header injection without writing custom code in a data flow. What should you use?

A.A Web activity to fetch the token, followed by a Copy activity whose REST source uses the token in an Authorization header.
B.A REST linked service configured with a system-assigned managed identity.
C.A Web activity that calls the token endpoint and a Set variable activity to store the token.
D.A Mapping Data Flow with a REST source and a derived column that generates the token.
AnswerA

This approach uses a Web activity to call the token endpoint and capture the bearer token, then passes it as an Authorization header on the REST source of a Copy activity. It keeps everything declarative within the pipeline, refreshes the token per run, and avoids custom code, which matches the stated requirement.

Why this answer

The pipeline needs to fetch a short-lived bearer token and attach it to an outgoing REST call. A Web activity can invoke the token endpoint and return the token value, which is then supplied as an Authorization header on the REST source of a Copy activity. This pattern is fully declarative, refreshes the token on every run, and satisfies the no-custom-code constraint.

Exam trap

The trap here is reaching for managed identity for any REST authentication, when managed identity only works with services that accept Azure AD tokens and cannot retrieve a third-party bearer token.

Practice this question →
Page 1 of 3 · 185 questions totalNext →

Ready to test yourself?

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

Start 20-question session