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

Question 1

Refer to the exhibit. You are running the KQL query in Azure Data Explorer. The query returns no results, but you know there is data in the table T. What is the most likely issue?

Accepted Answer

The between operator syntax is incorrect; should be 'between (startTime..endTime)' without spaces around the dots. The KQL `between` operator requires the range syntax `between(datetime1..datetime2)` with no spaces around the two dots. The query uses `between (startTime .. endTime)` with spaces, which is invalid syntax and causes the query to return no results even though data exists in table T.

Answer

The bin function is used incorrectly; should be bin(Timestamp, 1h) but placed in the wrong clause

Answer

The datetime format is incorrect; use ISO 8601

Answer

The table T does not exist in the database

Question 2

Refer to the exhibit. You are deploying the ARM template to create an Azure Synapse Analytics workspace. The deployment fails because the managed resource group cannot be created. What is the most likely reason?

Accepted Answer

The managedResourceGroupName property is not allowed; Azure Synapse creates the resource group automatically. The `managedResourceGroupName` property is not allowed in an ARM template for Azure Synapse Analytics. Azure Synapse automatically creates and manages its own managed resource group (e.g., `managed-rg-<workspace-name>`) to hold provisioned resources like Spark pools and SQL pools. Specifying this property in the template causes a validation error, as the service requires full control over the naming and lifecycle of this group.

Answer

The apiVersion is invalid for this resource type

Answer

The location is missing the 'properties' section

Answer

The storage account URL must use the blob endpoint, not DFS

Question 3

You are designing a data pipeline in Azure Synapse Analytics that ingests streaming taxi trip data from Azure Event Hubs. The data must be processed in near real-time and stored in a dedicated SQL pool. The pipeline should handle late-arriving data (up to 30 minutes late) without reprocessing the entire stream. Which Azure service should you use to process the streaming data?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time stream processing with native support for Event Hubs as an input and dedicated SQL pool as an output. It can handle late-arriving data via its built-in 'late arrival' window (configurable up to 30 minutes) using event time processing, without requiring reprocessing of the entire stream.

Answer

Azure Databricks Structured Streaming

Answer

Azure Data Factory

Answer

Azure Functions with Event Hubs trigger

Question 4

You are troubleshooting a Synapse Spark notebook that fails when reading Parquet files from Azure Data Lake Storage Gen2. The error message indicates 'Permission denied'. The notebook uses a managed identity (System-assigned) for authentication. The Data Lake Storage account has a firewall enabled with 'Allow Azure services on the trusted services list' turned on. The storage account's RBAC role assignments include 'Storage Blob Data Contributor' for the managed identity. What is the most likely cause of the failure?

Accepted Answer

The storage account firewall does not have a 'Resource instances' exception for the managed identity. When a storage account firewall is enabled with 'Allow Azure services on the trusted services list' turned on, it only allows trusted Azure platform services to access the storage account, but it does not automatically grant access to a specific managed identity. To allow a managed identity to bypass the firewall, you must add a 'Resource instances' exception for that managed identity's resource (e.g., the Synapse workspace). Without this explicit exception, the managed identity's request is blocked by the firewall, resulting in a 'Permission denied' error even though the RBAC role assignment is correct.

Answer

Parquet files require special permissions that are not granted by RBAC roles

Answer

The managed identity has not been granted the 'Storage Blob Data Reader' role in addition to 'Storage Blob Data Contributor'

Answer

The notebook is using an incorrect connection string with account key

Question 5

You need to execute a T-SQL stored procedure in Azure Synapse dedicated SQL pool that performs a large data load. The stored procedure takes approximately 45 minutes to run. You want to monitor the progress and see the statement text currently being executed. Which dynamic management view (DMV) should you query?

Accepted Answer

sys.dm_pdw_exec_requests (with status='running'). B is correct because `sys.dm_pdw_exec_requests` with the filter `WHERE status = 'running'` returns the currently executing requests in an Azure Synapse dedicated SQL pool, including the full statement text in the `command` column. This allows you to see the exact T-SQL being executed during the 45-minute data load, enabling progress monitoring.

Answer

sys.dm_workload_management_workload_groups_stats

Answer

sys.dm_pdw_waits

Answer

sys.dm_pdw_exec_requests

Question 6

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

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

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 9

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 10

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, code-free visual component of Azure Data Factory that lets you design transformations on a canvas (source, transformations, sink) running on Spark, so it satisfies the requirement of transforming data without writing custom code. Data Flow Script (C) is the built-in, auto-generated script behind every Mapping Data Flow; it is a native ADF component produced by the visual designer and can be edited, so it is also a built-in transformation component that does not require you to author custom code from scratch. Copy Activity (B) is excluded because it only moves data from source to sink and performs no transformation logic. Azure Function Activity (D) and Stored Procedure Activity (E) are excluded because they invoke external code (an Azure Function or a database stored procedure) that you must write yourself, which is custom code rather than a built-in code-free transformation component.

Answer

Copy Activity

Answer

Azure Function Activity

Answer

Stored Procedure Activity

Question 11

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. Option B is correct because the Synapse workspace's managed identity can be granted RBAC roles (e.g., Storage Blob Data Reader) on the ADLS Gen2 account and used by the serverless SQL pool via CREATE CREDENTIAL with IDENTITY = 'Managed Identity'. Option D is correct because Microsoft Entra ID (Azure AD) pass-through authentication lets the serverless SQL pool forward the user's Entra token to ADLS Gen2, enabling per-user access control through RBAC/ACLs. Option E is correct because a SAS token can be supplied in the serverless SQL pool's CREATE CREDENTIAL (SECRET = '<SAS token>') or in OPENROWSET to authorize access to the storage account. Option A is not valid because serverless SQL pool does not support authenticating with a storage account key. Option C is not valid because service principal authentication with a certificate is not a supported credential type for serverless SQL pool; service principals, if used, require a client secret rather than a certificate.

Answer

Storage account key

Answer

Service principal with certificate

Question 12

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

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 14

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 'Failure happened on 'Source' side. ErrorCode=SqlOperationFailed' indicates that the Copy activity cannot connect to the on-premises SQL Server. Since the question states there are no firewall restrictions, the most likely cause is that the self-hosted integration runtime (SHIR) is not properly configured, registered, or running. The SHIR is required to bridge the on-premises network to Azure, and if it is not correctly set up, the source connection will fail.

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