DP-203 Practice Test 23 — 15 Questions

Question 1

Refer to the exhibit. A data engineer creates an external table in Azure Synapse Analytics pointing to Parquet files in ADLS Gen2. The query 'SELECT * FROM Sales' returns 0 rows, but the files exist. What is the most likely cause?

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

Accepted Answer

The files are stored in a different container or path.. When an external table in Azure Synapse Analytics points to Parquet files in ADLS Gen2 and returns 0 rows despite files existing, the most likely cause is that the files are stored in a different container or path than the one specified in the external table's location. This mismatch means the table is querying an empty or non-existent location.

Answer

The credential does not have read permission on the storage account.

Answer

The external table is not refreshed after creation.

Answer

The Parquet files are compressed with Gzip instead of Snappy.

Question 2

You are an administrator for an Azure Synapse Analytics dedicated SQL pool. You execute the T-SQL statements shown in the exhibit. The external table 'dbo.Orders' is created. Which statement about querying this external table is true?

Accepted Answer

You can query the external table using standard T-SQL SELECT statements.. An external table in Azure Synapse Analytics dedicated SQL pool is a read-only abstraction over data stored externally (e.g., in Azure Blob Storage or Azure Data Lake Store). You can query it directly using standard T-SQL SELECT statements without importing data into the pool, leveraging PolyBase to push down predicate filtering and read only the required data.

Answer

Querying the external table automatically imports data into a round-robin distribution.

Answer

The table cannot be queried until the data is imported into the dedicated SQL pool.

Answer

You must first create a PolyBase external table before querying.

Question 3

You have a Data Factory pipeline that runs a U-SQL script in Azure Data Lake Analytics. The script processes terabytes of data and outputs to a CSV file. The pipeline is failing with the error: 'The job failed with UserError: Script execution failed.' You need to troubleshoot the issue. Which approach should you take first?

Accepted Answer

Review the job logs in Azure Data Lake Analytics to identify the specific script error.. The most effective first step is to examine the detailed job logs in Data Lake Analytics, which contain the actual script error. Increasing parallelism or changing output format may not address the underlying script error. Moving to Azure Synapse is a larger architectural change.

Answer

Change the output format to Parquet to reduce file size.

Answer

Migrate the U-SQL script to Azure Synapse Spark pool.

Answer

Increase the degree of parallelism for the U-SQL job.

Question 4

Refer to the exhibit. A user with Storage Blob Data Reader role on the container rawdata cannot list files under /2023/07/. What is the most likely reason?

Accepted Answer

The directory ACL does not grant 'execute' permission to the user. In Azure Data Lake Storage Gen2, listing files in a directory requires both read (r) and execute (x) permissions on the directory itself. The Storage Blob Data Reader role grants read access to blob data but does not automatically grant the execute permission on directory ACLs. Without execute permission on the /2023/07/ directory, the user cannot traverse or list its contents, even though they can read blobs they know the path to.

Answer

The user does not have Storage Blob Data Contributor role

Answer

The user is not the owner of the directory

Answer

The container name is misspelled

Question 5

Which TWO features are available in Azure Data Lake Storage Gen2 but not in Azure Blob Storage? (Choose two.)

Accepted Answer

Hierarchical namespace. Azure Data Lake Storage Gen2 is built on top of Blob Storage but adds a hierarchical namespace (option A), which organizes objects into true directories and enables atomic directory operations and efficient rename/delete semantics that flat Blob Storage cannot provide. It also supports POSIX-compliant access control lists (option E), allowing fine-grained, file- and directory-level permissions (read/write/execute) that map to Hadoop and POSIX-style security models, which plain Blob Storage does not offer. The other options are not unique to ADLS Gen2: immutable storage (option B) is a Blob Storage feature (time-based retention and legal holds), soft delete for blobs (option C) is a Blob Storage data-protection feature, and lifecycle management policies (option D) are available for Blob Storage to transition or expire data across hot/cool/archive tiers.

Answer

Immutable storage

Answer

Soft delete for blobs

Answer

Lifecycle management policies

Question 6

Which TWO of the following Azure services can be used to orchestrate data pipelines that include data transformation?

Accepted Answer

Azure Synapse Pipelines. Azure Synapse Pipelines (A) is correct because it is the built-in pipeline orchestration engine in Azure Synapse Analytics, providing the same Data Factory-style activities (Copy, Data Flow, Stored Procedure, Notebook) that let you schedule and orchestrate data movement with transformation steps such as Mapping Data Flows. Azure Data Factory (B) is correct because it is Azure's dedicated cloud ETL/ELT orchestration service, where pipelines chain activities like Copy, Mapping Data Flow, Databricks notebook, and stored procedure activities to move and transform data. Azure Logic Apps (C) is a workflow/automation service for app and system integration, not a data pipeline orchestrator with transformation activities. Azure Databricks (D) is an Apache Spark analytics/compute platform that performs transformations but is typically invoked as an activity within a pipeline rather than being the orchestrator itself. Azure Functions (E) is a serverless compute service for running event-driven code, not a data pipeline orchestration service.

Answer

Azure Logic Apps

Answer

Azure Databricks

Answer

Azure Functions

Question 7

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 8

A company runs a streaming pipeline using Azure Stream Analytics to ingest IoT data and output to Azure SQL Database. They notice that the output latency increases over time and eventually the job fails with a timeout error. What is the most likely cause?

Accepted Answer

The Azure SQL Database target table lacks proper indexes.. The most likely cause is that the Azure SQL Database target table lacks proper indexes. Without indexes, each batch of output from Stream Analytics triggers full table scans for inserts or updates, causing cumulative latency. Over time, the backlog exceeds the job's timeout threshold (default 5 minutes for output), leading to failure.

Answer

The Stream Analytics job has a high late arrival tolerance.

Answer

The event hub is not partitioned correctly.

Answer

The event hub consumer group is misconfigured.

Question 9

You have an Azure Databricks workspace that processes sensitive data. The security team requires that all access to the workspace be authenticated using Microsoft Entra ID and that all API calls be audited. Which configuration should you implement?

Accepted Answer

Configure workspace to use Microsoft Entra ID authentication and enable diagnostic settings for audit logs.. To meet both requirements—Microsoft Entra ID authentication and audited API calls—you must configure the workspace to use Entra ID authentication and enable diagnostic settings to send audit logs to a destination like Log Analytics. This combination ensures identity-based access and a record of API activity.

Answer

Enable VNet injection and configure network security groups.

Answer

Deploy Azure Private Link and disable public access.

Answer

Configure personal access tokens for API access and enable cluster logs.

Question 10

You are using Azure Synapse Analytics to process streaming data from Azure Event Hubs. The data must be written to a Delta Lake table in ADLS Gen2 with exactly-once semantics. Which processing engine should you use?

Accepted Answer

Azure Databricks with Structured Streaming. Azure Databricks with Structured Streaming is the correct choice because it natively supports exactly-once semantics when writing to Delta Lake from Event Hubs. Structured Streaming uses checkpointing and a write-ahead log to ensure each record is processed exactly once, even in the face of failures. Azure Databricks runs on Spark, which integrates seamlessly with both Event Hubs (via the Event Hubs connector) and Delta Lake (as a sink). Other options are either batch-oriented or lack the necessary transactional guarantees for exactly-once delivery to Delta Lake.

Answer

Azure Synapse serverless SQL pool

Answer

Azure Synapse Pipeline with Mapping Data Flow

Answer

Azure Stream Analytics

Question 11

You are designing a batch processing solution for a data lake. Source files arrive daily in Parquet format in Azure Data Lake Storage Gen2. The data must be cleaned, aggregated, and loaded into an Azure Synapse SQL pool. The solution should minimize compute costs and management overhead. Which technology should you use for the transformation?

Accepted Answer

Azure Synapse Pipelines with mapping data flows.. Azure Synapse Pipelines with mapping data flows provide a serverless, code-free transformation service that runs on managed Spark clusters, minimizing management overhead and costs. Mapping data flows can directly read Parquet from ADLS Gen2, perform cleaning and aggregation, and load into Azure Synapse SQL pool without requiring cluster management. Option A (Azure HDInsight with Spark) requires manual cluster provisioning and management, increasing operational overhead. Option C (custom SSIS package) is legacy, not cloud-native, and requires an integration runtime for execution. Option D (Azure Databricks with Auto Loader) provides powerful stream and batch processing but incurs higher costs for a simple batch job due to cluster management and DBU consumption.

Answer

Azure HDInsight with Spark jobs scheduled in Azure Data Factory.

Answer

Azure Data Factory with a custom SSIS package.

Answer

Azure Databricks with an Auto Loader pipeline.

Question 12

You are monitoring an Azure Data Factory pipeline that runs hourly. The pipeline executes a stored procedure in an Azure SQL Database. Recently, you have observed that the pipeline occasionally fails with a 'Deadlock' error when the stored procedure runs. The Azure SQL Database is configured with the 'Read Committed Snapshot' isolation level enabled. You need to resolve the deadlock issue with minimal impact on performance. The stored procedure updates multiple tables in a single transaction and is critical for reporting. What should you do?

Accepted Answer

Add retry logic in the Data Factory pipeline for the stored procedure activity. Adding retry logic in the Data Factory pipeline for the stored procedure activity is the least invasive fix. Deadlocks are transient by nature — SQL Server chooses a victim and rolls back its transaction — so retrying the activity typically succeeds on the next attempt without changing isolation levels or query semantics.

Answer

Change the stored procedure to use NOLOCK hints

Answer

Remove the transaction from the stored procedure

Answer

Disable the 'Read Committed Snapshot' isolation level

Question 13

You are using Azure Purview to scan an Azure Data Lake Storage Gen2 account. After scanning, you notice that some files are not classified. What is the most likely reason?

Accepted Answer

The file types are not included in the scan rule set. Azure Purview scans use a scan rule set that defines which file types are included for classification. If a file's extension or type is not listed in the rule set, Purview skips classification for that file even though the scan completes. This is the most common reason specific files remain unclassified after a successful scan.

Answer

The storage account is not registered in Purview

Answer

The files are in Parquet format

Answer

The classification rules are disabled

Question 14

You are running an Azure Stream Analytics job that reads from an Event Hub and writes to a Power BI dataset. The job is falling behind and processing latency is increasing. What should you do to improve performance?

Accepted Answer

Increase the number of Streaming Units (SUs) allocated to the job.. Azure Stream Analytics scales throughput by allocating Streaming Units (SUs), which represent a bundle of CPU, memory, and network resources. When a job falls behind, increasing SUs provides more parallel processing capacity, directly reducing latency. This is the standard vertical scaling mechanism for Stream Analytics and is the correct first response to performance degradation.

Answer

Use a reference data input to filter events.

Answer

Change the output to Azure Blob Storage instead of Power BI.

Answer

Decrease the size of events sent to the Event Hub.

Question 15

Which TWO Azure services can be used to monitor Azure Data Factory pipeline runs and set up alerts?

Accepted Answer

Log Analytics. Azure Monitor (option D) is the native platform for collecting metrics and activity/diagnostic logs from Azure Data Factory and for creating alert rules (metric alerts, log search alerts, activity log alerts) that trigger on pipeline run failures or durations, so it directly satisfies the monitoring-and-alerting requirement. Log Analytics (option A) is the service where ADF diagnostic logs are stored and queried with KQL, and it powers log search alerts (via Azure Monitor) on pipeline run records such as PipelineRun and ActivityRun, making it a valid way to monitor runs and set alerts. Microsoft Sentinel (B) is a SIEM/SOAR for security analytics, not pipeline operational monitoring; Azure Policy (C) enforces governance/compliance rules rather than monitoring runs; and Azure Advisor (E) only provides best-practice recommendations, not run monitoring or alerting.

Answer

Microsoft Sentinel

Answer

Azure Policy

Answer

Azure Advisor