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

Question 1

You are optimizing an Azure Synapse serverless SQL pool query that queries Parquet files in Azure Data Lake Storage. The query takes longer than expected. You notice that the query reads more data than necessary. What is the most effective way to reduce the amount of data scanned?

Accepted Answer

Create external tables with explicit schema and partition by a frequently filtered column. Option B is correct because partitioning external tables in Azure Synapse serverless SQL allows the query engine to perform partition elimination, reading only the subdirectories that match the filter criteria. This directly reduces the amount of data scanned from Parquet files in ADLS, addressing the core issue of reading unnecessary data.

Answer

Split large Parquet files into smaller files of 100 MB each

Answer

Use SELECT with column pruning to only retrieve necessary columns

Answer

Increase the query's resource allocation by using a larger service level objective

Question 2

You have a pipeline in Azure Data Factory that copies data from on-premises SQL Server to Azure Blob Storage. The pipeline fails with a 'Connection timed out' error. You have already verified that the Integration Runtime is running and the SQL Server firewall allows connections from the Integration Runtime. What should you check next?

Accepted Answer

Check if the SQL Server is configured to allow remote connections and that TCP/IP is enabled. Option C is correct because the 'Connection timed out' error, despite the Integration Runtime being running and the firewall allowing connections, typically indicates that SQL Server is not listening on the expected TCP port. This often happens when TCP/IP is disabled in SQL Server Configuration Manager or remote connections are not enabled. Without TCP/IP enabled, the Integration Runtime cannot establish a network connection to the SQL Server instance, leading to a timeout.

Answer

Ensure the Integration Runtime is registered and online

Answer

Check if the Blob Storage endpoint is accessible from the Integration Runtime

Answer

Verify that the SQL Server login credentials are correct

Question 3

You are designing a data processing solution for a financial services company. The solution must process sensitive customer data from multiple sources. You need to ensure that the data is encrypted at rest and in transit, and that access to the data is audited. Which combination of Azure services should you use?

Accepted Answer

Azure Data Lake Storage (encrypted at rest), Azure Synapse Analytics (TDE and SSL), and Microsoft Purview. Option A is correct because Azure Data Lake Storage provides encryption at rest using Azure Storage Service Encryption (SSE) with 256-bit AES, and Azure Synapse Analytics supports Transparent Data Encryption (TDE) for at-rest encryption and SSL/TLS for in-transit encryption. Microsoft Purview enables data governance and auditing by capturing lineage, classification, and access activity logs, meeting the compliance requirements for sensitive financial data.

Answer

Azure Blob Storage (encrypted at rest), Azure HDInsight, and Azure Log Analytics

Answer

Azure SQL Database (TDE and SSL), Azure Analysis Services, and Microsoft Purview

Answer

Azure Data Lake Storage (encrypted at rest), Microsoft Fabric, and Azure Monitor

Question 4

You are running a data transformation pipeline in Azure Synapse Spark that writes output to Delta tables. You notice that the job eventually slows down and then fails with an out-of-memory error. The input data size is 1 TB, and the cluster has 10 nodes with 16 GB memory each. What is the most likely cause?

Accepted Answer

The data is not partitioned properly, leading to large partitions that exceed executor memory. The most likely cause is that the data is not partitioned properly, leading to large partitions that exceed executor memory. In Azure Synapse Spark, each executor has a limited memory (16 GB per node in this cluster), and if a single partition is too large to fit in memory, the task processing that partition will fail with an out-of-memory error. Proper partitioning ensures that data is evenly distributed across executors, preventing any single partition from overwhelming available memory.

Answer

The driver node does not have enough memory to collect the results

Answer

The Delta table is being written in non-optimized format causing memory pressure

Answer

The transformation involves a wide dependency causing excessive shuffle

Question 5

You are using Azure Data Factory to copy data from an Azure SQL Database to Azure Synapse dedicated SQL pool. The copy activity uses PolyBase as the copy method. The activity fails with the error 'Operation not supported: PolyBase cannot write to a table with clustered columnstore index'. What should you do to resolve this error?

Accepted Answer

Create the target table as a heap or with clustered index. PolyBase in Azure Data Factory cannot write directly to a table that has a clustered columnstore index (CCI). The sink table must be a heap or have a clustered index for PolyBase to work. Option B correctly identifies this requirement, as creating the target table as a heap or with a clustered index resolves the error.

Answer

Use an external table as the sink instead of a regular table

Answer

Enable staging with blob storage and use 'Allow PolyBase'

Answer

Change the copy method from PolyBase to Bulk Insert

Question 6

Which TWO of the following are built-in components of Azure Data Factory that can be used to perform data transformation without writing custom code?

Accepted Answer

Mapping Data Flow. Mapping Data Flow (A) is a built-in Azure Data Factory component that allows you to design data transformations visually using a graphical interface, without writing any code. It executes at scale on Azure Databricks clusters and supports operations like joins, aggregations, and pivots, making it a code-free transformation tool.

Answer

Copy Activity

Answer

Azure Function Activity

Answer

Stored Procedure Activity

Question 7

Which THREE of the following are valid ways to authenticate to Azure Data Lake Storage Gen2 from Azure Synapse serverless SQL pool?

Accepted Answer

Managed identity of the Synapse workspace. Managed identity of the Synapse workspace (Option B) is correct because Azure Synapse serverless SQL pool can use the workspace's system-assigned managed identity to authenticate to Azure Data Lake Storage Gen2 without storing any credentials. This is enabled by granting the managed identity the appropriate RBAC role (e.g., Storage Blob Data Contributor) on the storage account, and then using the `IDENTITY='Managed Identity'` parameter in the `OPENROWSET` or `CREATE EXTERNAL TABLE AS SELECT` statements. It provides a secure, credential-free authentication method that is fully supported by serverless SQL pool.

Answer

Storage account key

Answer

Service principal with certificate

Question 8

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. Option A is correct because 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 9

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. Option D is correct: Adding a derived column transformation in a Mapping Data Flow allows you to replace NULLs with a default value. Option A is wrong because it changes the sink schema which may not be allowed. Option B is wrong because filtering out rows with NULLs would lose data. Option C is wrong because setting the column to allow NULLs might not be permitted and could impact downstream processes.

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 10

Refer to the exhibit. You have created an external table in Azure Synapse serverless SQL pool as shown. You run a query: SELECT ProductID, SUM(Amount) FROM dbo.ExternalSales WHERE SaleDate > '2024-01-01' GROUP BY ProductID. The query is slow and scans all files in the /sales/ folder, which contains data from 2023 and 2024. The files are partitioned by year and month in the folder structure, e.g., /sales/year=2023/month=01/. What should you do to improve query performance?

Accepted Answer

Recreate the external table with a partition definition on SaleDate column using the folder structure. Option A is correct because the query performance is slow due to full file scanning. By recreating the external table with a partition definition on the SaleDate column that maps to the folder structure (e.g., /sales/year=2023/month=01/), Azure Synapse serverless SQL pool can perform partition elimination, reading only the relevant partitions for the WHERE clause filter (SaleDate > '2024-01-01'). This drastically reduces the amount of data scanned, improving query speed.

Answer

Recreate the external table with a partition on ProductID

Answer

Create statistics on the SaleDate column

Answer

Change the file format to CSV to improve read performance

Question 11

Refer to the exhibit. You have an Azure Synapse pipeline that runs a Spark notebook daily. The notebook uses the inputDate parameter to filter data. The notebook successfully processes data for '2024-01-01' but fails for '2024-01-02' with an error that the 'sales' table does not exist. The 'sales' table is created daily by a preceding job. What is the most likely cause?

Accepted Answer

The notebook expects a table named 'sales_20240102' but the preceding job creates 'sales_20240101'. The error indicates that the notebook is looking for a table named 'sales_20240102' (based on the inputDate parameter for '2024-01-02'), but the preceding job creates a table named 'sales_20240101' (the previous day's table). This mismatch occurs because the notebook dynamically constructs the table name using the inputDate parameter, and the preceding job likely creates the table with a date suffix that does not align with the current inputDate. The correct answer is A because the table naming convention is inconsistent between the two processes.

Answer

The notebook activity should have a dependency on the job that creates the table

Answer

The Spark pool does not have permissions to read the storage account where the table data is stored

Answer

The pipeline parameter 'inputDate' is not being passed to the notebook correctly

Question 12

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 designed for real-time stream processing with sub-second latency and can output to Azure Data Lake Storage Gen2. Option A (HDInsight including Spark) is batch-oriented and has higher latency. Option C (Data Factory) is for orchestration, not streaming. Option D (Event Hubs) is an ingestion service, not a processing service.

Answer

Azure HDInsight including Spark Structured Streaming

Answer

Azure Data Factory

Answer

Azure Event Hubs

Question 13

You are troubleshooting a Synapse Pipeline that runs a Copy activity from an on-premises SQL Server to Azure Synapse Dedicated SQL Pool. The pipeline fails with the error: 'Failure happened on 'Source' side. ErrorCode=SqlOperationFailed.' The on-premises SQL Server has no firewall restrictions. What is the most likely cause?

Accepted Answer

The self-hosted integration runtime is not configured properly.. The error indicates a failure on the source side. Since the on-premises SQL Server has no firewall restrictions, the most likely cause is that the self-hosted integration runtime is not installed or configured correctly, preventing connectivity from the cloud to the on-premises network. Option A is plausible but less likely given the error message. Option C is not a typical cause for a source-side error. Option D is about staging, which is not mentioned.

Answer

Staging is not enabled for the Copy activity.

Answer

The destination table in Synapse has a different schema.

Answer

The SQL Server credentials in the linked service are incorrect.

Question 14

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 15

Refer to the exhibit. The pipeline fails with a 'Type mismatch' error. The source file has a column 'Name' of type String, and the destination table expects 'FullName' of type String. What is the most likely cause of the failure?

Accepted Answer

The source file does not contain a column named 'Name'.. The pipeline fails with a 'Type mismatch' error because the source file does not contain a column named 'Name'. When the source dataset is configured to read a 'Name' column but the actual file lacks that column, Azure Data Factory or Synapse Pipelines cannot map it to the sink column 'FullName', resulting in a type mismatch error. The error message is misleading because the mismatch is not about data types but about missing source columns, which the service treats as a type incompatibility.

Answer

The recursive setting on the source store is causing duplicate reads.

Answer

The source file has leading/trailing whitespace in the column names.

Answer

The sink column 'FullName' is expecting a different data type than String.