DP-203 Practice Exam 51 — 20 Questions

Question 1

You are using Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. The query returns fewer rows than expected. What should you check first?

Accepted Answer

Verify the file path and pattern in the OPENROWSET query.. When using OPENROWSET in Azure Synapse serverless SQL pool to query Parquet files, the most common reason for fewer rows than expected is an incorrect file path or pattern. If the path or pattern is too restrictive (e.g., missing a wildcard or pointing to a subfolder instead of the root), the query will only read a subset of the files, resulting in fewer rows. This is the first thing to verify before investigating schema or permissions issues.

Answer

Ensure the external table has the correct schema definition.

Answer

Check that the Azure AD identity has read permissions on the storage account.

Answer

Check the compression codec used in the Parquet files.

Question 2

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 3

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 4

Your organization uses Azure Purview for data governance. You need to ensure that only authorized users can register data sources and create classification rules, while other data consumers can only search and browse the data catalog. What should you configure?

Accepted Answer

Assign the Data Curator role to curators and Data Reader role to consumers. Azure Purview uses roles: Data Curator can register sources and manage classifications; Data Reader can only search and browse. Option A is wrong because Data Source Administrator can only manage source registrations. Option C is wrong because Collection Admin manages collections. Option D is wrong because there is no 'Catalog Admin' role; Purview has built-in roles.

Answer

Assign the Catalog Admin role to curators and Data Reader role to consumers

Answer

Assign the Collection Admin role to curators and Data Reader role to consumers

Answer

Assign the Data Source Administrator role to curators and Data Reader role to consumers

Question 5

An Azure Synapse Analytics pipeline uses a Copy activity to ingest data from Azure Blob Storage into a dedicated SQL pool. You notice that the data load is slow. You need to improve performance by enabling staging. What is the primary benefit of using staging?

Accepted Answer

It allows PolyBase to use parallel loading for better throughput.. Staging allows PolyBase to bulk load data efficiently. Option A is wrong because staging may actually improve data consistency. Option B is wrong because staging reduces load on the SQL pool. Option D is wrong because staging uses blobs, not the SQL pool.

Answer

It reduces the amount of data scanned in the source.

Answer

It enables data validation before loading.

Answer

It transforms data into columnstore format before loading.

Question 6

You have an Azure Synapse Analytics workspace with Apache Spark pools. You need to monitor Spark application performance and identify stages that are taking the longest time. Which tool should you use?

Accepted Answer

Use the Spark UI available in Synapse Studio.. The Spark UI provides detailed information about stages, tasks, and executors. Option B is wrong because Azure Monitor metrics provide aggregate metrics but not stage-level details. Option C is wrong because Log Analytics queries can analyze logs but not as directly as Spark UI. Option D is wrong because Synapse Studio provides a job view but not as granular as Spark UI.

Answer

Run KQL queries in Log Analytics against Spark logs.

Answer

Query Azure Monitor metrics for the Spark pool.

Answer

Use the Synapse Pipeline monitoring view.

Question 7

Your company uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled. You need to optimize costs for a large dataset that is accessed only once a month for reporting. The data must be retained for 7 years. Which storage tier and lifecycle management rule should you configure?

Accepted Answer

Cool tier with lifecycle policy to Archive after 30 days. Cool tier is cost-effective for data accessed infrequently (once a month), and a lifecycle policy to move data to Archive after 30 days further reduces costs while meeting the 7-year retention requirement. Option A (Hot tier) is expensive for infrequent access. Option C (Premium tier) is designed for high-throughput workloads, not cost optimization. Option D (Archive tier with no lifecycle) incurs high retrieval costs for monthly access and doesn't leverage tiering.

Answer

Hot tier with no lifecycle policy

Answer

Premium tier with lifecycle policy to Cool after 30 days

Answer

Archive tier with no lifecycle policy

Question 8

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 9

Which TWO components are required to set up a streaming data pipeline using Azure Synapse Analytics? (Select two.)

Accepted Answer

Azure Event Hubs. To set up a streaming data pipeline in Azure Synapse Analytics, you need a streaming ingestion source and a processing engine. Azure Event Hubs (Option B) is the correct ingestion service for real-time streaming data. Azure Synapse Pipelines (or Spark) (Option E) provides the processing engine to transform and analyze the streaming data. Azure Data Factory (Option A) is primarily for batch data integration, not streaming. Azure Analysis Services (Option C) is for OLAP modeling, not streaming. Azure Blob Storage (Option D) is a storage destination, not a required component for streaming ingestion or processing.

Answer

Azure Data Factory

Answer

Azure Analysis Services

Answer

Azure Blob Storage

Question 10

You are working with a Delta Lake table in Azure Databricks. The table is updated frequently with new data and occasionally with updates to existing rows. You need to optimize read performance for queries that filter on a specific date column. The table is partitioned by date. Which optimization technique should you apply?

Accepted Answer

Run ZORDER BY on the date column.. ZORDER BY on the date column co-locates related data within each partition, significantly reducing the amount of data scanned for queries that filter on that column. This is especially beneficial for a frequently updated Delta table where data is already partitioned by date, as ZORDER BY optimizes the layout within partitions without changing the partition scheme.

Answer

Run OPTIMIZE on the table.

Answer

Run ANALYZE STATISTICS on the table.

Answer

Run VACUUM to clean up old versions.

Question 11

A data processing job in Azure Synapse Analytics writes results to a table in the dedicated SQL pool. After a failure, the job restarts from the beginning, causing duplicates. Which design pattern should you implement to ensure idempotent writes?

Accepted Answer

Use a staging table and then swap partitions with the target table.. Using a staging table with partition swapping ensures idempotent writes by atomically replacing the target partition with a fully loaded staging partition. This avoids duplicates even if the job restarts, as the swap operation is transactional and the staging table can be truncated before each run. In Azure Synapse dedicated SQL pool, partition switching is a metadata-only operation that provides consistency without data movement.

Answer

Use a TRUNCATE statement before each insert.

Answer

Use a MERGE statement with a unique key to upsert data.

Answer

Use CREATE TABLE AS SELECT (CTAS) with a unique constraint.

Question 12

A company ingests streaming data from multiple sources into Azure Event Hubs. The data must be stored in Azure Data Lake Storage Gen2 in Parquet format, partitioned by date and hour. The solution must minimize cost and processing latency. Which THREE actions should be taken?

Accepted Answer

Use Azure Stream Analytics to read from Event Hubs and write to Data Lake Storage in Parquet format.. Azure Stream Analytics (C) is ideal for this scenario because it processes streaming data from Event Hubs in real time and can write directly to Azure Data Lake Storage Gen2 in Parquet format, which provides efficient compression and columnar storage for analytics. Additionally, configuring the output partitioning by date and hour (D) ensures the data is organized in the required structure without post-processing. Azure Databricks (E) can also process the stream from Event Hubs and write Parquet with partitioning, offering flexibility for complex transformations while still meeting latency requirements, though it may incur slightly higher cost than Stream Analytics for simple pipelines. Options A and B are incorrect: Event Hubs Capture writes Avro, not Parquet, and Data Factory introduces batch latency.

Answer

Enable Event Hubs Capture to automatically write data to Data Lake Storage in Avro format.

Answer

Use Azure Data Factory to copy data from Event Hubs to Data Lake Storage every 5 minutes.

Question 13

A company uses Azure Synapse Analytics dedicated SQL pool. They notice that queries against a large fact table are running slower over time. The table is hash-distributed on a date key and has a clustered columnstore index. Which action should you take to improve query performance?

Accepted Answer

Rebuild the clustered columnstore index.. Over time, columnstore indexes can become fragmented due to insert, update, and delete operations, leading to compressed row groups that are not optimally sized or have deleted records. Rebuilding the clustered columnstore index reorganizes the data into fully compressed row groups, removes deleted rows, and restores the high compression and segment elimination that columnstore indexes rely on for fast query performance.

Answer

Add a non-clustered index on frequently filtered columns.

Answer

Change the distribution column to a column with higher cardinality.

Answer

Change the distribution to round-robin.

Question 14

A company uses Azure Synapse Analytics serverless SQL pool to query data in ADLS Gen2. Users report that queries against Parquet files are slow. What should you recommend to improve query performance?

Accepted Answer

Create external tables with statistics on relevant columns.. In Azure Synapse serverless SQL pool, external tables do not automatically have statistics. Without statistics, the query optimizer cannot generate efficient execution plans, leading to poor performance on Parquet files. Creating statistics on relevant columns enables the optimizer to estimate cardinality and choose better join and filter strategies, significantly improving query speed.

Answer

Create clustered columnstore indexes on the external tables.

Answer

Convert the Parquet files to CSV format for faster reads.

Answer

Partition the data into many small files.

Question 15

You have an Azure Data Lake Storage Gen2 account that stores parquet files. You need to ensure that files containing personally identifiable information (PII) are automatically classified and tagged. Which Azure service should you integrate?

Accepted Answer

Microsoft Purview. Microsoft Purview provides automated data classification and labeling for Azure Storage, including Azure Data Lake Storage Gen2. Option A is wrong because Azure Policy enforces rules but does not classify content. Option B is wrong because Microsoft Sentinel is a SIEM, not for classification. Option C is wrong because Microsoft Defender for Cloud is for security posture, not data classification.

Answer

Azure Policy

Answer

Microsoft Sentinel

Answer

Microsoft Defender for Cloud

Question 16

Your Azure Synapse Analytics dedicated SQL pool is experiencing performance degradation. You suspect that the workload is generating excessive data movement due to suboptimal distribution. Which dynamic management view (DMV) should you query to identify queries that are causing significant data movement?

Accepted Answer

sys.dm_pdw_exec_requests. Sys.dm_pdw_exec_requests shows query steps including data movement operations. Option A is wrong because sys.dm_pdw_node_status shows node health. Option C is wrong because sys.dm_pdw_errors shows error details. Option D is wrong because sys.dm_pdw_waits shows wait states.

Answer

sys.dm_pdw_node_status

Answer

sys.dm_pdw_errors

Answer

sys.dm_pdw_waits

Question 17

Your Azure Data Lake Storage Gen2 account stores sensitive data. You need to audit who accesses the data and when, and you want to send the audit logs to a Log Analytics workspace for analysis. What should you configure?

Accepted Answer

Diagnostic settings on the storage account. Diagnostic settings on the storage account can stream audit logs (like read, write, delete) to Log Analytics for analysis. Option A is incorrect because Azure Activity Logs capture control plane operations, not data plane access. Option B is incorrect because Microsoft Sentinel is a SIEM that would consume logs from diagnostic settings, not a direct configuration for log collection. Option C is incorrect because Azure Monitor alerts are for notifications based on metrics or logs, not for collecting logs.

Answer

Azure Activity Logs

Answer

Microsoft Sentinel

Answer

Azure Monitor alerts

Question 18

A company uses Azure Synapse Analytics dedicated SQL pool. They need to load data from Azure Data Lake Storage Gen2 (ADLS Gen2) incrementally. Which PolyBase external table configuration supports incremental loading without reprocessing historical data?

Accepted Answer

Create an external table partitioned by date column with a partition location pattern.. Partitioning an external table by a date column with a partition location pattern allows PolyBase to perform partition elimination during queries. This means only the partitions containing new or modified data are read from ADLS Gen2, enabling incremental loading without scanning historical data.

Answer

Create an external table with a clustered index on the date column.

Answer

Create an external table on ORC files with a clustered columnstore index.

Answer

Create an external table with round-robin distribution on the date column.

Question 19

A healthcare organization needs to store electronic health records (EHR) in a format that supports schema flexibility and complex nested data. The solution must allow fast queries by patient ID and enable analytics with Azure Synapse. Which data store should you choose?

Accepted Answer

Azure Cosmos DB with analytical store enabled. Azure Cosmos DB with analytical store enabled is the correct choice because it provides schema flexibility for complex nested EHR data, supports fast point reads by patient ID via its indexed partition key, and the analytical store enables efficient analytics with Azure Synapse through the Synapse Link feature, which automatically synchronizes operational data into a columnar format optimized for large-scale queries.

Answer

Azure Table Storage

Answer

Azure Data Lake Storage Gen2 with files in JSON format

Answer

Azure SQL Database with JSON columns

Question 20

Refer to the exhibit. You are an Azure data engineer responsible for ensuring that all storage accounts used in data pipelines enforce HTTPS traffic. You apply the Azure Policy definition shown above. Later, a data engineer creates a new storage account with 'Enable secure transfer' set to Disabled. What will happen when the policy is evaluated?

Accepted Answer

The storage account creation will be denied and the request will fail.. The Azure Policy definition shown in the exhibit uses the 'Deny' effect for the 'Secure transfer to storage accounts should be enabled' policy. When a data engineer attempts to create a storage account with 'Enable secure transfer' set to Disabled, the policy evaluation occurs at resource creation time (before the resource is provisioned). Because the request violates the policy's condition (HTTPS traffic not enforced), Azure Resource Manager denies the creation request and returns a failure, preventing the non-compliant storage account from being created.

Answer

The storage account will be created, but the policy will be evaluated later during a compliance scan.

Answer

The storage account will be created with HTTPS enabled automatically.

Answer

The storage account will be created, but an audit event will be logged.