DP-203 Develop data processing Practice Test 6 — 15 Questions

Question 1

Which THREE factors should you consider when choosing between Azure Data Factory Mapping Data Flows and Azure Synapse Spark pools for data transformation?

Accepted Answer

Ease of use: Mapping Data Flows provide a visual designer, while Spark requires code.. Azure Data Factory Mapping Data Flows offer a visual, no-code designer for building data transformations, which lowers the barrier for users who are not proficient in programming. In contrast, Azure Synapse Spark pools require writing code in languages like PySpark, Scala, or SQL, making them more suitable for developers comfortable with coding. This distinction directly addresses ease of use as a key factor in choosing between the two services.

Answer

Scheduling: Only Data Flows can be scheduled via triggers.

Answer

Data volume: Data Flows are limited to 100 GB, while Spark can handle petabytes.

Question 2

You are reviewing a Mapping Data Flow in Azure Data Factory that copies data from a CSV file to an Azure Synapse table. The data flow uses 'allowSchemaDrift: true' and 'validateSchema: false'. After running the pipeline, you notice that the target table does not have the expected columns. The CSV file sometimes has extra columns. What is the most likely reason?

Accepted Answer

The sink mapping is explicitly defined and does not include auto-mapping for drifted columns.. When 'allowSchemaDrift' is enabled on the source, drifted columns are detected but will only be written to the sink if auto-mapping is used. If the sink mapping is explicitly defined (e.g., column-by-column mappings), it overrides auto-mapping and drifted columns are ignored. Since the target table is missing expected columns, the explicit mapping likely excludes the drifted columns.

Answer

Schema drift is not enabled on the source.

Answer

The sink dataset has a fixed schema that does not allow drift.

Answer

The source dataset has a fixed schema that does not include extra columns.

Question 3

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

Accepted Answer

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

Answer

The file path is incorrect, causing data read errors.

Answer

The job is not parallelized because of a single partition.

Answer

The executor memory is too low for the aggregation.

Question 4

You are deploying an Azure Synapse workspace using an ARM template. The template includes a Managed integration runtime with 'AutoResolve' location and a TTL of 10 minutes for data flows. After deployment, you notice that the first data flow execution takes a long time to start. What is the most likely cause?

Accepted Answer

The TTL setting is too low, causing the cluster to be recreated frequently.. The first data flow execution takes a long time because the TTL (time-to-live) setting of 10 minutes causes the cluster to be deallocated shortly after the previous run. When a new data flow starts after the TTL expires, a new cluster must be provisioned from scratch, which adds significant startup latency. A higher TTL (e.g., 60 minutes) would keep the cluster warm for subsequent executions, reducing cold-start delays.

Answer

The core count of 8 is insufficient for the data flow.

Answer

The AutoResolve location cannot be used for Managed IR.

Answer

The integration runtime type should be 'Self-Hosted' for data flows.

Question 5

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

Accepted Answer

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

Answer

Avro with Deflate compression

Answer

CSV with Gzip compression

Answer

ORC with Zlib compression

Question 6

You are using Azure Synapse Pipelines to perform an incremental load from Azure SQL Database to Azure Synapse Analytics. You need to identify rows that have changed since the last load. Which approach should you use?

Accepted Answer

Enable Change Data Capture (CDC) on the source table.. Change Data Capture (CDC) on the source Azure SQL Database captures insert, update, and delete operations in change tables, enabling Azure Synapse Pipelines to efficiently identify only the changed rows since the last load. This approach minimizes data movement and processing overhead compared to full or snapshot-based comparisons, making it the recommended pattern for incremental loads in Synapse Pipelines.

Answer

Compare the current data with a snapshot using T-SQL MERGE.

Answer

Truncate and reload the entire table daily.

Answer

Use a watermark column such as LastModifiedDate.

Question 7

You have an Azure Stream Analytics job that reads from an Event Hub and writes to Azure SQL Database. The job processes high-velocity IoT sensor data. You notice that the output to SQL Database is slower than expected and the job's watermark delay is increasing. What should you do to improve throughput?

Accepted Answer

Partition the output by a column like DeviceId.. Partitioning the output by a column like DeviceId allows Azure Stream Analytics to write to multiple SQL Database tables or use partitioned tables, enabling parallel writes. This reduces contention and improves throughput because the job can distribute the load across multiple write operations, directly addressing the bottleneck caused by high-velocity IoT sensor data overwhelming a single output stream.

Answer

Disable late arrival and out-of-order event handling.

Answer

Increase the Streaming Units (SU) of the job.

Answer

Decrease the window size in the query.

Question 8

Your team is building a real-time dashboard in Power BI that displays sales data from Azure Stream Analytics. The data must be updated every 5 seconds with low latency. Which output type should you configure in Stream Analytics to achieve this?

Accepted Answer

Power BI. Power BI is the correct output type because Azure Stream Analytics has a native Power BI output connector that supports real-time streaming datasets, enabling sub-second latency for dashboards. This connector pushes data directly to Power BI's streaming API, which refreshes visuals automatically every 5 seconds as required.

Answer

Azure Blob Storage

Answer

Azure SQL Database

Answer

Azure Event Hubs

Question 9

You are designing a data processing solution in Azure Synapse Analytics that uses serverless SQL pools to query Parquet files in Azure Data Lake Storage Gen2. The files are partitioned by year and month. You need to optimize query performance and reduce data scanned. What should you do?

Accepted Answer

Create views that filter on partition columns.. Serverless SQL pools in Azure Synapse Analytics support partition elimination only when queries use views or inline queries that explicitly filter on partition columns (e.g., year, month) in the WHERE clause. This allows the pool to skip scanning irrelevant partitions, reducing data scanned and improving performance. Creating views that encapsulate these filters ensures consistent partition pruning across queries.

Answer

Use CREATE EXTERNAL TABLE AS SELECT (CETAS) to create new external tables.

Answer

Use OPENROWSET with the DATA_SOURCE parameter.

Answer

Increase the number of files per partition.

Question 10

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

Accepted Answer

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

Answer

The Azure Blob Storage account is throttling requests.

Answer

The authentication method to SQL Server is incorrect.

Answer

The self-hosted integration runtime is not connected to the network.

Question 11

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

Accepted Answer

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

Answer

CREATE VIEW

Answer

PolyBase

Answer

CREATE EXTERNAL TABLE

Question 12

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

Accepted Answer

Use a large batch size (e.g., 100 MB) for each copy operation.. Using a large batch size (e.g., 100 MB) for each copy operation minimizes the number of round trips and transaction commits, which significantly improves throughput when loading large volumes of data into a dedicated SQL pool. The PolyBase or COPY statement in Azure Synapse performs best when batches are large enough to leverage parallel processing and reduce overhead from frequent small writes.

Answer

Disable index on the target table after loading.

Answer

Use a small batch size (e.g., 1 MB) for each copy operation.

Answer

Use clustered columnstore index on the target table during load.

Question 13

Which THREE components are required to implement a change data capture (CDC) solution in Azure Data Factory for incremental loading from Azure SQL Database to Azure Synapse Analytics?

Accepted Answer

A Lookup activity to retrieve the last processed watermark.. A Lookup activity is required to retrieve the last processed watermark (e.g., a timestamp or LSN) from a control table or source query. This watermark defines the incremental boundary for CDC, ensuring only new or changed rows are loaded in each pipeline run. Without it, the pipeline would have no way to track progress and would either reload all data or miss changes.

Answer

A Mapping Data Flow to transform the data before load.

Answer

Azure Functions to process change data.

Question 14

Which TWO data processing activities in Azure Data Factory allow you to run custom code?

Accepted Answer

Custom activity. The Custom activity in Azure Data Factory allows you to execute custom code by running a .NET or PowerShell script in an Azure Batch service. This enables you to implement complex transformation logic that is not natively supported by ADF's built-in activities, such as custom data validation or integration with third-party APIs.

Answer

Data Flow activity

Answer

Copy activity

Answer

Stored Procedure activity

Question 15

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

Accepted Answer

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

Answer

RDD API

Answer

Spark Streaming (DStreams)

Answer

DataFrame API with batch processing