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

Question 1

You are monitoring an Azure Data Factory pipeline that runs every hour. The pipeline uses a Copy activity to copy data from Azure SQL Database to Azure Blob Storage. Recently, the pipeline has been failing with a 'Timeout' error. The source SQL database has a large number of records. What should you do to resolve the timeout?

Accepted Answer

Enable staging and use PolyBase or COPY statement for the copy activity.. Option A is correct because enabling staging with PolyBase or the COPY statement offloads the data transfer to Azure Data Lake or Blob Storage, bypassing the bottleneck of the Copy activity's default data movement. This approach is specifically designed for large-scale data loads from Azure SQL Database, as it uses the database's bulk export capabilities and avoids the timeout by not relying on the Copy activity's internal query execution.

Answer

Decrease the 'writeBatchSize' to 1000.

Answer

Increase the 'timeout' value in the copy activity settings.

Answer

Use a query with 'queryTimeout' set to 7200 seconds.

Question 2

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

Accepted Answer

FTP. Option A is correct because FTP linked service supports username/password authentication. Option B is incorrect because HTTP linked service is for HTTP endpoints. Option C is incorrect because Azure Blob Storage linked service is for Azure blobs. Option D is incorrect because Rest service is for REST APIs.

Answer

Azure Blob Storage

Answer

HTTP

Answer

Rest service

Question 3

You are using Azure Synapse Analytics dedicated SQL pool to run a query that joins a large fact table (10 billion rows) and a small dimension table (1 million rows). The query is slow. Which distribution strategy should you use for the dimension table to improve performance?

Accepted Answer

Replicate the dimension table to all compute nodes.. Replicating the small dimension table (1 million rows) to all compute nodes eliminates data movement during the join with the large fact table (10 billion rows). In Azure Synapse dedicated SQL pool, replicated tables store a full copy on each distribution, so the join can be performed locally on every node without shuffling data across the network, drastically reducing query latency.

Answer

Round-robin distribute the dimension table.

Answer

Hash-distribute the dimension table on its primary key.

Answer

Hash-distribute the dimension table on the foreign key column.

Question 4

You are designing a data processing solution in Azure Synapse Analytics. The solution must support incremental loading of data from an Azure SQL Database to a dedicated SQL pool using PolyBase. Which approach should you use to minimize data movement and maximize performance?

Accepted Answer

Create external tables in the dedicated SQL pool that reference the source data, then use CREATE TABLE AS SELECT (CTAS) to load incrementally.. Option B is correct because using external tables with PolyBase in Azure Synapse Analytics allows you to directly query the source Azure SQL Database without moving the data first. The CREATE TABLE AS SELECT (CTAS) statement then loads only the incremental data into the dedicated SQL pool, minimizing data movement by leveraging PolyBase's parallel streaming capability for maximum performance.

Answer

Use the bcp utility to export data from Azure SQL Database to a text file, then bulk insert into the dedicated SQL pool.

Answer

Use Azure Data Factory with a copy activity to load data into staging tables, then merge into the target table.

Answer

Use Azure Databricks to read the source data, apply transformations, and write to the dedicated SQL pool using the Spark connector.

Question 5

You are troubleshooting a pipeline in Azure Data Factory that copies data from an Azure Blob Storage to an Azure Synapse Analytics dedicated SQL pool. The pipeline fails with the error: 'PolyBase requires a varchar(max) column to be less than 1 MB.' Which action should you take to resolve this issue?

Accepted Answer

Configure the copy activity to use staging via Azure Blob Storage.. The error indicates that PolyBase is being used for the copy operation, and it has a limitation that varchar(max) columns must be less than 1 MB. Configuring staging via Azure Blob Storage (option B) allows the copy activity to use PolyBase with staging, which automatically splits large varchar(max) values into manageable chunks, bypassing the 1 MB limit. This is the recommended approach in Azure Data Factory for loading large string data into Synapse dedicated SQL pools.

Answer

Modify the source data to truncate varchar(max) columns to 8000 characters.

Answer

Increase the 'batchSize' property in the copy activity to 10000.

Answer

Disable PolyBase in the sink settings and use bulk insert instead.

Question 6

You are developing a data processing solution in Azure Synapse Analytics. The solution must use a serverless SQL pool to query Parquet files stored in Azure Data Lake Storage Gen2. Which authentication method should you use to ensure that the queries use the identity of the caller and adhere to Azure role-based access control (RBAC) permissions?

Accepted Answer

Microsoft Entra ID pass-through authentication.. Microsoft Entra ID pass-through authentication (option A) is correct because it allows the serverless SQL pool to use the caller's identity when accessing Azure Data Lake Storage Gen2. This ensures that Azure RBAC permissions (e.g., Storage Blob Data Reader) assigned to the user are evaluated for each query, providing fine-grained access control without exposing storage account keys or tokens.

Answer

Storage account key.

Answer

Shared access signature (SAS) token.

Answer

Service principal with a secret.

Question 7

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

Accepted Answer

The watermark query returns the maximum LastModified value, but the copy query uses the same value to filter, resulting in zero rows.. Option D is correct because the query uses the output of WatermarkQuery (which returns the maximum LastModified) as the filter condition, but it should filter for rows where LastModified is greater than the previous watermark, not the current maximum. This results in no rows being selected because no rows have LastModified > maximum. Option A is wrong because the pipeline succeeded. Option B is wrong because the watermark query executes before the copy activity. Option C is wrong because the dependency condition is correct.

Answer

The dependency condition should be 'Completed' instead of 'Succeeded'.

Answer

The WatermarkQuery activity failed, causing the CopyData activity to be skipped.

Answer

The CopyData activity runs before the WatermarkQuery activity completes.

Question 8

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?

Accepted Answer

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

Answer

Azure Data Factory with tumbling window trigger.

Answer

Azure Functions with Event Hubs trigger.

Answer

Azure HDInsight Spark Structured Streaming.

Question 9

You are developing a data processing pipeline in Azure Synapse Analytics. The pipeline uses a mapping data flow to transform data from Azure Data Lake Storage Gen2 to a dedicated SQL pool. The data flow includes a Derived Column transformation that uses the expression: `iif(isNull(Column1), 'Default', Column1)`. However, the transformation is not handling NULL values correctly. What is the most likely cause?

Accepted Answer

The function names in the expression are case-sensitive; use 'isNull' instead of 'isnull'.. Option C is correct because the `iif` function in Azure Synapse Analytics mapping data flows is case-sensitive. The expression uses `isNull` (with a capital 'N'), but the correct function name is `isNull` (with a capital 'N' and lowercase 'ull'? Actually, the correct function is `isNull` with a capital 'N' and lowercase 'ull'? Wait, the expression in the question uses `isNull(Column1)` which is correct; the issue is that the question says the expression uses `iif(isNull(Column1), 'Default', Column1)` but the answer option C says use 'isNull' instead of 'isnull'. The trap is that the function name is case-sensitive; the correct function is `isNull` (capital 'N'), not `isnull` (all lowercase). The expression in the question already uses `isNull` with capital 'N', but the answer option C suggests using 'isNull' instead of 'isnull' — this implies the candidate might have typed `isnull` (all lowercase) which would fail. The most likely cause is that the function name was typed incorrectly with wrong casing, as mapping data flows are case-sensitive for function names.

Answer

The Derived Column transformation does not support the iif function; use a Conditional Split instead.

Answer

The column data type is not string; convert Column1 to string first.

Answer

The expression must use ternary operator syntax: `Column1 == null ? 'Default' : Column1`.

Question 10

You are designing a data processing solution in Azure Synapse Analytics. The solution must use a serverless SQL pool to query data in Azure Data Lake Storage Gen2. The data is stored in Delta Lake format. Which of the following statements is true regarding querying Delta Lake tables with serverless SQL pool?

Accepted Answer

You can use the OPENROWSET function with the BULK option and FORMAT='DELTA' to query Delta Lake tables.. Serverless SQL pool in Azure Synapse Analytics supports querying Delta Lake tables directly using the OPENROWSET function with the BULK option and FORMAT='DELTA'. This allows you to read Delta Lake data stored in Azure Data Lake Storage Gen2 without needing to convert it to Parquet or create external tables first. The Delta format is natively supported, enabling time travel and schema evolution features.

Answer

Serverless SQL pool supports Delta Lake only if the files are in Parquet format.

Answer

Serverless SQL pool cannot query Delta Lake tables; you must convert them to Parquet first.

Answer

You must create an external table in serverless SQL pool using the CREATE EXTERNAL TABLE statement with the Delta format.

Question 11

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

Accepted Answer

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

Answer

Round-robin distribution.

Answer

Partitioned table with a partition key.

Answer

Replicated distribution.

Question 12

You are developing a data processing pipeline in Azure Data Factory. The pipeline must copy data from an on-premises SQL Server to Azure Blob Storage. The data volumes are large, and the network connection is unreliable. Which configuration should you use to ensure resilience and resume capability?

Accepted Answer

Configure the copy activity with fault tolerance and enable 'Skip incompatible rows'. Use a self-hosted integration runtime for the source.. Option B is correct because using a self-hosted integration runtime enables connectivity to on-premises SQL Server, and enabling fault tolerance with 'Skip incompatible rows' allows the copy activity to continue despite transient failures or data inconsistencies. This configuration ensures resilience by automatically retrying failed operations and skipping rows that cause errors, which is critical for large data volumes over an unreliable network.

Answer

Use a staging copy with Azure Blob Storage as an intermediate store.

Answer

Increase the 'parallelCopies' setting to 10 and use a staging table.

Answer

Use PolyBase to load data directly into Azure Synapse Analytics.

Question 13

You are designing a data processing solution in Azure Synapse Analytics. The solution must use a serverless SQL pool to query data in Azure Data Lake Storage Gen2. The data is stored as Parquet files partitioned by date. Which TWO of the following statements are true regarding querying this data? (Select TWO.)

Accepted Answer

You can use the filepath() function in the query to retrieve the partition column values.. Option A is correct because the `filepath()` function in a serverless SQL pool query returns the file path of the row being read. When data is partitioned by date in Azure Data Lake Storage Gen2, the partition column values are embedded in the folder structure (e.g., `/year=2023/month=01/day=15/`). Using `filepath(1)`, `filepath(2)`, etc., you can extract these values directly in the query without needing to parse the path manually.

Answer

You must create an external table to query Parquet files; OPENROWSET is not supported.

Answer

You can create indexes on the serverless SQL pool to improve query performance.

Answer

You can only query a single file at a time; wildcards are not supported.

Question 14

You are developing a data processing pipeline in Azure Data Factory. The pipeline uses a mapping data flow to transform data. You need to ensure that the data flow can handle schema drift from the source. Which THREE of the following actions should you take? (Select THREE.)

Accepted Answer

Enable 'Allow schema drift' in the source settings.. Option B is correct because enabling 'Allow schema drift' in the source settings of a mapping data flow tells Azure Data Factory to accept incoming columns that are not defined in the source schema. This is essential for handling schema drift, as it prevents the pipeline from failing when new columns appear in the source data.

Answer

Use a 'Select' transformation to explicitly choose only the known columns.

Answer

Add a 'Derived Column' transformation for every possible column that might be added.

Question 15

You are designing a data processing solution in Azure Synapse Analytics. The solution must use a dedicated SQL pool to support both batch and near-real-time data ingestion. Which TWO of the following methods can you use to ingest data into a dedicated SQL pool? (Select TWO.)

Accepted Answer

CREATE TABLE AS SELECT (CTAS) from external tables.. CREATE TABLE AS SELECT (CTAS) from external tables is correct because it allows you to load data from external storage (e.g., Azure Blob Storage or Azure Data Lake Storage) into a dedicated SQL pool in a single, parallelized operation. This method leverages the MPP (Massively Parallel Processing) architecture of Synapse SQL pools to efficiently ingest large volumes of data for batch processing.

Answer

COPY INTO command.

Answer

Azure Logic Apps with SQL connector.

Answer

Azure Data Factory with a copy activity using native sink.