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

Question 1

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?

Choose an answer to begin — your selection is scored in the full session.

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 2

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 3

Refer to the exhibit. The pipeline executes a Spark job definition that reads from the input path and writes to the output path. The job fails with an error indicating that the output path already exists. The job is configured with default behavior. What configuration change should you make to allow the job to overwrite the existing output?

Accepted Answer

Add a Delete activity before the Spark job to delete the output folder if it exists.. The default behavior of Spark in Azure Synapse Analytics is to fail if the output path already exists. By adding a Delete activity before the Spark job, you explicitly remove the existing folder, allowing the subsequent Spark job to write without conflict. This approach works with any save mode, including the default 'error' mode, and is a common pattern in Synapse pipelines for idempotent data processing.

Answer

Modify the Spark job definition to include 'overwrite' save mode.

Answer

Change the output path in the arguments to a non-existing folder.

Answer

Set the pipeline property 'outputMode' to 'overwrite' on the SynapseSparkJob activity.

Question 4

You need to process a large number of small files (each < 1 MB) from Azure Blob Storage in Azure Synapse Analytics. The processing is I/O-bound due to many small file operations. Which approach should you use to improve performance?

Accepted Answer

Use 'spark.sql.files.maxPartitionBytes' to coalesce small files into larger partitions.. `spark.sql.files.maxPartitionBytes` controls the maximum number of bytes packed into a single partition when reading files. By increasing this value, Spark coalesces many small files into fewer, larger partitions, reducing the overhead of task scheduling and I/O operations. This directly addresses the I/O-bound bottleneck caused by processing numerous small files in Azure Synapse Analytics.

Answer

Use wildcard paths to read multiple files at once.

Answer

Enable optimized write on the Spark session.

Answer

Convert the files to a binary format like Avro before processing.

Question 5

Which TWO of the following are valid ways to handle late-arriving data in a streaming solution with Azure Stream Analytics? (Choose two.)

Accepted Answer

Configure the 'late arrival tolerance' window in the event ordering settings up to 21 days.. Azure Stream Analytics allows you to configure a 'late arrival tolerance' window in the event ordering settings, which can be set up to a maximum of 21 days. This window defines how long the service will wait to accommodate events that arrive after their timestamp, reordering them within that tolerance before processing. Option E is correct because a temporal join (e.g., using LATERAL or JOIN with DATEDIFF) can combine a late-arriving event with historical data from a reference or stream window, enabling you to retroactively correct aggregations or state.

Answer

Reprocess the entire stream from the beginning when late data is detected.

Answer

Implement a custom Azure Function as a 'LateDataHandler' in the query.

Answer

Use a reference data input to store late-arriving events.

Question 6

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

Accepted Answer

Amazon S3. Amazon S3 is a supported source for Azure Data Factory Copy activity because ADF includes a built-in Amazon S3 connector that allows data ingestion from S3 into Azure. This connector uses the S3 REST API to read objects, supporting both public and private buckets via access keys or IAM roles.

Answer

Power BI Dataset

Answer

Azure DevOps

Answer

Azure Analysis Services

Question 7

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 8

You are building a data processing solution that requires exactly-once semantics when writing to Azure Event Hubs from Azure Stream Analytics. Which output format should you configure?

Accepted Answer

Avro. Azure Stream Analytics supports exactly-once semantics when writing to Event Hubs only when using Avro serialization. This is because Avro provides a compact binary format with embedded schema, enabling Stream Analytics to track and deduplicate events precisely during output, which is required for exactly-once delivery. JSON, CSV, and Parquet do not support the necessary metadata and checkpointing mechanisms for exactly-once guarantees in this specific integration.

Answer

JSON

Answer

CSV

Answer

Parquet

Question 9

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 10

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 11

You are building a data pipeline in Azure Data Factory to copy data from an on-premises SQL Server database to Azure Blob Storage. The pipeline must run daily and handle incremental updates. The on-premises SQL Server table has a LastModifiedDate column that is updated when a row changes. What is the most efficient way to implement incremental loads?

Accepted Answer

Use a Lookup activity to get the maximum LastModifiedDate from the destination, then use a Copy activity with a query that filters rows where LastModifiedDate > that value.. It uses a Lookup activity to retrieve the maximum LastModifiedDate from the destination (Azure Blob Storage), then passes that value as a parameter to a Copy activity that queries only rows where LastModifiedDate exceeds it. This minimizes data transfer by reading only new or changed rows, and it avoids the overhead of enabling Change Data Capture or performing full loads, making it the most efficient approach for incremental loads in Azure Data Factory.

Answer

Enable Change Data Capture (CDC) on the SQL Server database and use an ADF mapping data flow to read changes.

Answer

Use a tumbling window trigger with a window size of 1 day and copy all data from the source each time.

Answer

Perform a full load every day and use a Delete activity to remove duplicates.

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

Your company uses Azure Synapse Analytics and has deployed a pipeline that uses a Mapping Data Flow to transform data. The data flow reads from a source in Azure Blob Storage and writes to a dedicated SQL pool. You notice that the data flow is running slowly and consuming a lot of Data Flow cluster resources. You need to improve performance without increasing the cluster size. Which action should you take?

Accepted Answer

Add a partitioning step in the data flow to distribute the data across partitions based on a key column.. Adding a partitioning step in the Mapping Data Flow distributes data across partitions based on a key column, which allows parallel processing across the cluster's nodes. This reduces data shuffling and improves throughput without increasing the cluster size, directly addressing the performance bottleneck caused by skewed or unpartitioned data.

Answer

Use a self-hosted integration runtime instead of the default auto-resolve IR.

Answer

Increase the batch size in the data flow settings to reduce the number of round trips.

Answer

Change the source format to Delta Lake to leverage optimizations.

Question 14

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 15

You are optimizing a data pipeline in Azure Data Factory that uses a Copy activity to transfer data from an Azure SQL Database to a dedicated SQL pool in Azure Synapse Analytics. The source table has 500 million rows and the copy operation is taking too long. You need to reduce the copy duration. Which configuration change will have the most impact?

Accepted Answer

Enable staging and use PolyBase as the copy method for the sink.. Enabling staging with PolyBase as the copy method for the sink is the most impactful change because PolyBase leverages the massively parallel processing (MPP) architecture of Azure Synapse Analytics to load data in parallel directly into the dedicated SQL pool. This bypasses the single-threaded bottleneck of the standard INSERT-based copy method, dramatically reducing the time required to ingest 500 million rows.

Answer

Change the copy behavior to 'sequential' to reduce load on the source.

Answer

Increase the degree of copy parallelism (DOP) to the maximum value supported.

Answer

Split the source data into multiple smaller files and use multiple copy activities running in parallel.