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

Question 1

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

Accepted Answer

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

Answer

A staging Azure Blob Storage account

Answer

A Lookup activity to get the last watermark

Answer

A stored procedure in the source database to capture changes

Question 2

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?

Accepted Answer

Use a Mapping Data Flow with a Derived Column transformation to replace NULLs with 0. 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.

Answer

Add a filter in the copy activity to exclude rows with NULL Amount

Answer

Modify the sink table to have a default value for the Amount column

Answer

Change the sink table column to allow NULLs

Question 3

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?

Accepted Answer

Azure Stream Analytics. 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.

Answer

Azure HDInsight including Spark Structured Streaming

Answer

Azure Data Factory

Answer

Azure Event Hubs

Question 4

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?

Accepted Answer

Flatten transformation. 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.

Answer

Join transformation

Answer

Derived Column transformation

Answer

Aggregate transformation

Question 5

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

Accepted Answer

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

Answer

Alter Row transformation

Answer

Schedule trigger

Answer

Lookup activity alone

Question 6

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?

Accepted Answer

Hash distribution on CustomerID. 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.

Answer

Round-robin distribution

Answer

Replicate distribution

Answer

Hash distribution on Date

Question 7

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

Accepted Answer

Run the OPTIMIZE command to compact small files and improve read performance.. Option A is correct because the OPTIMIZE command compacts many small Parquet files into fewer, larger files, reducing per-file overhead and metadata work so that Delta Lake scans and reads run faster. Option C is correct because partitioning on columns commonly used in WHERE clauses lets the engine perform partition pruning, skipping entire directories of data that cannot match the filter and cutting I/O. Option E is correct because Z-ordering (for example, OPTIMIZE ... ZORDER BY (col)) co-locates related data within files, so data-skipping statistics let queries with frequent filter predicates read far fewer rows. Option B is not a performance optimization: VACUUM deletes unreferenced old files to reclaim storage and control cost, and running it too aggressively can break time travel and concurrent readers. Option D is wrong because partitioning on high-cardinality columns such as UserID creates a huge number of tiny partitions and files, which increases metadata overhead and degrades performance rather than maximizing parallelism.

Answer

Periodically run VACUUM to remove old versions of files that are no longer needed.

Answer

Partition on high-cardinality columns like UserID to maximize parallelism.

Question 8

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

Accepted Answer

Amazon S3. 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.

Answer

Power BI Dataset

Answer

Azure DevOps

Answer

Azure Analysis Services

Question 9

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

Accepted Answer

The serverless SQL pool infers schema from the first file and expects all files to have the same schema.. Azure Synapse serverless SQL pools infer the schema from the first Parquet file encountered in the specified location. When multiple Parquet files with different schemas exist, the pool expects all subsequent files to match that initial schema. If any file has a different schema (e.g., different column names, data types, or number of columns), a schema mismatch error is raised. This behavior is by design, as serverless SQL does not merge or reconcile disparate schemas across files.

Answer

The Parquet files are not using the .parquet extension.

Answer

The derivedModel option is set to false, which disables schema inference.

Answer

The recursive option is causing the table to include files from subfolders that have different schemas.

Question 10

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

Accepted Answer

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

Answer

Web activity

Answer

Stored Procedure activity

Answer

HDInsight Hive activity

Question 11

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?

Accepted Answer

The statistics on the tables are outdated or missing.. 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.

Answer

The tables are distributed using round-robin distribution.

Answer

Result-set caching is disabled.

Answer

The resource class for the user is set to smallrc.

Question 12

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

Accepted Answer

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

Answer

Ingest data directly from Event Hubs into serverless SQL pool using CETAS (CREATE EXTERNAL TABLE AS SELECT).

Answer

Use a materialized view in serverless SQL pool that refreshes every minute.

Answer

Load the streaming data into a dedicated SQL pool using a scheduled pipeline and then query it from serverless SQL pool.

Question 13

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

Accepted Answer

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

Answer

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

Answer

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

Answer

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

Question 14

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?

Accepted Answer

Use the Azure Databricks dbutils.fs.mv() to move the file.. `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.

Answer

Use Azure Data Factory with a Copy activity to move the file, then delete the source.

Answer

Change the file's metadata to update its directory path.

Answer

Use the Azure Databricks dbutils.fs.cp() to copy the file to the processed folder, then delete the original.

Question 15

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.)

Accepted Answer

Use Delta Lake format and optimize the table with Z-ordering on frequently filtered columns.. 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.

Answer

Reduce the number of shuffle partitions to minimize data movement.

Answer

Enable autoscaling on the cluster to add more nodes during processing.