DP-203 Multiple Select — 10 Questions

Question 1

Which TWO Azure services can be used to implement a data lake architecture for storing structured, semi-structured, and unstructured data?

Accepted Answer

Azure Blob Storage with hierarchical namespace. Azure Data Lake Storage Gen2 (ADLS Gen2) is built on top of Azure Blob Storage and adds a hierarchical namespace, enabling a true data lake architecture. It supports storing structured, semi-structured, and unstructured data at scale, with POSIX-like access control lists and directory-level operations. Azure Blob Storage with hierarchical namespace enabled is essentially the same service as ADLS Gen2, providing the same data lake capabilities.

Answer

Azure SQL Database

Answer

Azure Synapse Analytics

Answer

Azure Cosmos DB

Question 2

You are designing a delta lake architecture in Azure Synapse Analytics. Which TWO practices should you follow to ensure ACID transactions and data consistency?

Accepted Answer

Use the Delta Lake transaction log for all write operations. The Delta Lake transaction log is the core mechanism that enables ACID transactions. Every write operation (insert, update, delete, merge) is recorded as an atomic commit in the transaction log, ensuring that concurrent readers see a consistent snapshot and that partial writes are never visible. Without this log, Delta Lake cannot guarantee atomicity or isolation.

Answer

Disable schema evolution to prevent accidental schema changes

Answer

Run OPTIMIZE commands frequently to compact small files

Answer

Store data in CSV format to simplify schema enforcement

Question 3

Which THREE are best practices for optimizing query performance in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Use materialized views for complex aggregations. Materialized views precompute and store the results of complex aggregations, such as SUM, COUNT, AVG, or GROUP BY operations, in Azure Synapse dedicated SQL pool. When a query references the same aggregation pattern, the optimizer can automatically substitute the materialized view, significantly reducing compute and I/O overhead by avoiding full table scans and recomputation. This is a best practice for improving performance on repetitive analytical workloads.

Answer

Use the largest resource class for all queries

Answer

Use round-robin distribution for large fact tables

Question 4

You are building a real-time processing solution using Azure Stream Analytics. The solution must handle out-of-order events and late arrivals. Which THREE mechanisms should you configure in the Stream Analytics job?

Accepted Answer

Set an 'Out-of-order tolerance' window in the event ordering settings.. Azure Stream Analytics allows you to configure an 'Out-of-order tolerance' window in the event ordering settings. This window defines the maximum time difference that out-of-order events can be reordered before being considered late. By setting this tolerance, you ensure that events arriving slightly out of sequence are still processed correctly, which is critical for real-time analytics where event order matters.

Answer

Adjust the 'Streaming units' to handle higher throughput.

Answer

Enable 'Event hub capture' to store raw events for reprocessing.

Question 5

Which TWO are benefits of using Azure Databricks Auto Loader for incremental data ingestion?

Accepted Answer

It can process new files as they arrive in cloud storage.. Azure Databricks Auto Loader is designed to incrementally ingest new data files as they arrive in cloud storage (e.g., Azure Data Lake Storage Gen2 or Blob Storage) by using a notification-based or directory-listing approach. It automatically tracks which files have already been processed using a structured streaming checkpoint mechanism, eliminating the need for manual checkpoint management even at high data volumes. This makes options A and B correct because Auto Loader's core value is its ability to discover and process new files without manual intervention and to handle large-scale ingestion reliably.

Answer

It automatically evolves the schema without any configuration.

Answer

It provides sub-second latency for real-time streaming.

Answer

It provides built-in deduplication of records.

Question 6

Which TWO Azure services can be used to perform data transformation in a data pipeline? (Select two.)

Accepted Answer

Azure Data Factory. Azure Data Factory is a cloud-based ETL service that provides a code-free visual interface for orchestrating data movement and transformation at scale. It supports data flows, which allow you to perform transformations like aggregations, joins, and filtering without writing code, making it a correct choice for data transformation in a pipeline.

Answer

Azure Monitor

Answer

Azure Storage

Answer

Azure Event Hubs

Question 7

Which THREE statements are true about partitioning in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Partition switching can be used to quickly load data into a table.. Partition switching in Azure Synapse Analytics dedicated SQL pool allows you to quickly load data into a table by switching a partition from a staging table into the target table. This operation is metadata-only and does not require data movement, making it highly efficient for incremental data loads.

Answer

Partitions are automatically aligned with distributions.

Answer

Partitioning is only supported on tables with clustered rowstore indexes.

Question 8

Which TWO actions can you take to optimize the performance of an Azure Synapse Analytics dedicated SQL pool? (Choose two.)

Accepted Answer

Replicate small dimension tables.. Replicating small dimension tables across all distributions reduces data movement during joins and improves query performance. Option E is correct because hash distribution on a column used in joins and aggregations ensures that rows with the same key are colocated on the same distribution, minimizing data shuffling. Option A is incorrect: scaling up increases resources but is not a targeted performance optimization and may not address underlying distribution or indexing issues. Option C is incorrect: heap indexes are generally not optimal for fact tables because they lack compression and indexing benefits; clustered columnstore indexes are recommended. Option D is incorrect: round-robin distribution distributes data evenly but does not reduce data movement for joins and aggregations, often leading to poor query performance.

Answer

Scale up the SQL pool to a higher DWU.

Answer

Use heap indexes for fact tables.

Answer

Use round-robin distribution for all large fact tables.

Question 9

Which TWO techniques should you use to secure sensitive data in Azure Synapse Analytics dedicated SQL pools when implementing column-level security?

Accepted Answer

Define dynamic data masking rules to obfuscate sensitive columns for unauthorized users.. Dynamic data masking obfuscates sensitive columns for unauthorized users at query time, providing column-level security without altering the underlying data. Option D is correct because column-level security in Azure Synapse dedicated SQL pools uses GRANT SELECT on specific columns to authorized users, restricting access to sensitive columns. Option B is incorrect because Transparent Data Encryption (TDE) encrypts data at rest, not at the column level. Option C is incorrect because security policies with filter predicates are used for row-level security, not column-level security. Option E is incorrect because Always Encrypted encrypts columns at the application level, which is separate from column-level security in Synapse.

Answer

Enable Transparent Data Encryption (TDE) on the database.

Answer

Create a security policy with filter predicates to restrict access to specific columns.

Answer

Implement Always Encrypted to encrypt columns at the application level.

Question 10

Which THREE metrics from Azure Monitor should be used to diagnose performance bottlenecks in an Azure Data Factory pipeline?

Accepted Answer

Pipeline Succeeded Rerun Count. Pipeline Succeeded Rerun Count (A) is correct because a high number of reruns indicates that the pipeline is repeatedly failing and retrying, which directly points to a performance bottleneck such as resource contention or throttling. This metric helps identify pipelines that are not completing successfully on the first attempt, signaling underlying issues that degrade throughput.

Answer

Blob Capacity

Answer

SQL Pool DWU Used