DP-203 Multiple Select — 50 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

Question 11

Which TWO Azure services can be used to implement a data lakehouse architecture with Delta Lake?

Accepted Answer

Azure Databricks. Azure Databricks is correct because it provides a unified analytics platform with native Delta Lake support, enabling ACID transactions, schema enforcement, and time travel on data lakes. Delta Lake is an open-source storage layer that brings reliability to data lakes, and Azure Databricks is the primary service for running Delta Lake workloads at scale.

Answer

Azure Data Factory

Answer

Azure Cosmos DB

Answer

Azure SQL Database

Question 12

Which TWO options are correct for configuring a copy activity in Azure Data Factory to load data from Azure Blob Storage to Azure SQL Database?

Accepted Answer

Use a staging table in Azure SQL Database before inserting into the final table.. Correct options are A and B. Option A (using a staging table in Azure SQL Database before inserting into the final table) is a best practice for large data loads, enabling efficient truncate-and-reload or upsert operations. Option B (using staging via Azure Blob Storage when loading large volumes) improves performance by batching and parallelizing the load, reducing timeouts and resource contention. Option D is incorrect because PolyBase is not supported for Azure SQL Database; it is available only for Azure Synapse Analytics and SQL Server. Option C is incorrect because Azure Data Lake Storage is not the standard staging location for Azure SQL Database; Azure Blob Storage is used. Option E is incorrect because skipping staging can degrade performance for large data volumes.

Answer

Use Azure Data Lake Storage as the staging location for better throughput.

Answer

Use PolyBase to load directly from Blob Storage to Azure SQL Database.

Answer

Always skip staging to reduce latency.

Question 13

Which TWO actions should you take to secure data in transit between an Azure Synapse Analytics serverless SQL pool and a client application?

Accepted Answer

Configure the serverless SQL pool to enforce TLS 1.2 connections.. The correct answers are B and C. Enforcing TLS 1.2 ensures encryption in transit using a modern, secure protocol (B). Using Azure Virtual Network service endpoints for the SQL pool keeps traffic within the Azure backbone network, adding a layer of network security (C). Option A (Azure RBAC) controls authentication and authorization, not encryption. Option D (disable SSL) would expose data in transit. Option E (ExpressRoute) provides private connectivity but does not inherently encrypt data; it's more for network isolation.

Answer

Use Azure RBAC to restrict access to the SQL pool.

Answer

Disable SSL encryption to reduce latency.

Answer

Use Azure ExpressRoute to connect to the SQL pool.

Question 14

Which THREE components are valid parts of the Microsoft Purview Data Map? (Choose THREE)

Accepted Answer

Scan rule sets. Correct answers: A, D, E. The Microsoft Purview Data Map consists of components that define how data sources are registered and scanned. Scan rule sets (A) are used to configure scanning rules for data sources. Data sources (D) are the actual data locations that are registered and scanned. Classifications (E) are the rules or patterns that identify data types (e.g., PII) during scanning; they are a core component of the Data Map. B (Sensitivity labels) is incorrect because sensitivity labels are part of Microsoft Information Protection, not a component of the Data Map itself, though they can be applied to assets in Purview. C (Data flows) is incorrect because data flows are part of Azure Data Factory, not the Data Map.

Answer

Sensitivity labels

Answer

Data flows

Question 15

Which TWO configurations are recommended to secure data processing in Azure Synapse Pipelines?

Accepted Answer

Store connection strings and secrets in Azure Key Vault and reference them via linked services.. Storing connection strings and secrets in Azure Key Vault and referencing them via linked services ensures secrets are stored securely and not exposed in pipeline definitions. Option D is correct: Enabling Managed Virtual Network (VNet) isolates data flows within a managed network boundary, preventing public network access. Option A is incorrect: Configuring a self-hosted integration runtime on a public cloud VM does not necessarily improve security; it may expose the runtime to the public internet. Option B is incorrect: Using the default Auto-resolve Integration Runtime is not recommended for secure data processing because it may use public endpoints and lacks network isolation. Option E is incorrect: Allowing all public IP addresses to access the Azure Synapse workspace exposes the workspace to potential security threats.

Answer

Configure a self-hosted integration runtime on a public cloud VM.

Answer

Use the default Auto-resolve Integration Runtime for all data flows.

Answer

Allow all public IP addresses to access the Azure Synapse workspace.

Question 16

You are developing a data pipeline in Azure Data Factory that ingests data from multiple on-premises SQL Server databases to Azure Data Lake Storage Gen2. The data volume is about 1 TB per day. You need to ensure the pipeline can handle the volume and provide monitoring and alerting. Which THREE components should you include?

Accepted Answer

Self-hosted integration runtime. Correct answers: B, C, and E. Self-hosted integration runtime (B) is required for connectivity to on-premises SQL Server databases. Azure Monitor (C) provides monitoring and alerting for the pipeline. Data flow activity (E) allows data transformation at scale. Option A is incorrect because Azure Synapse Analytics pipeline is not used in this context; the pipeline is in Azure Data Factory. Option D is incorrect because Power BI is for visualization, not data ingestion.

Answer

Azure Synapse Analytics pipeline

Answer

Power BI

Question 17

Which TWO are required to run a stored procedure in Azure SQL Database from Azure Data Factory?

Accepted Answer

A linked service to Azure SQL Database.. To run a stored procedure in Azure SQL Database from Azure Data Factory, only two components are required: a linked service to Azure SQL Database, which defines the connection and authentication, and a stored procedure activity in the pipeline that specifies the stored procedure name and parameters directly. A dataset is not required because the stored procedure activity can define the stored procedure reference inline. Options like a self-hosted integration runtime or staging storage are unnecessary for this task.

Answer

A dataset that references the stored procedure.

Answer

A self-hosted integration runtime.

Answer

A staging blob storage account.

Question 18

You are monitoring an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. You notice frequent failures due to transient network errors. Which TWO actions should you take to improve reliability?

Accepted Answer

Enable fault tolerance in the copy activity to skip incompatible rows.. Options C and D are correct. Enabling fault tolerance allows the copy activity to skip incompatible rows and continue, while configuring a retry policy automatically retries the activity on failure due to transient errors. Option A is incorrect because deploying a self-hosted IR in Azure does not address transient network errors; it is used for connectivity to on-prem data stores. Option B is incorrect because staged copy is for copying large datasets efficiently, not for handling transient errors. Option E is incorrect because increasing parallelism improves throughput but does not improve reliability against transient failures.

Answer

Deploy a self-hosted integration runtime on a VM in Azure.

Answer

Use staged copy with Azure Data Lake as intermediate storage.

Answer

Increase the degree of copy parallelism.

Question 19

Which TWO strategies can be used to optimize storage costs for historical data in Azure Data Lake Storage Gen2?

Accepted Answer

Implement lifecycle management policies to move data to archive tier. Azure Blob Storage lifecycle management policies allow you to automatically transition data from hot to cool to archive tiers based on age or last modification time. Moving historical data to the archive tier significantly reduces storage costs, as archive is the lowest-cost storage tier, though it incurs higher retrieval latency and costs.

Answer

Enable soft delete to recover data

Answer

Use geo-redundant storage (GRS) for durability

Answer

Encrypt data with Azure Storage Service Encryption

Question 20

A company uses Azure Data Lake Storage Gen2 as the data lake. The data engineering team needs to ensure that sensitive data such as credit card numbers are masked when queried by non-admin users. The solution must be implemented within the data lake without moving data to another store. Which TWO features should they use? (Choose two.)

Accepted Answer

Azure Synapse Serverless SQL with dynamic data masking. Azure Synapse Serverless SQL can query data directly from Azure Data Lake Storage Gen2 and supports dynamic data masking (DDM) to obfuscate sensitive columns like credit card numbers from non-admin users. This allows masking to be applied at query time without moving or transforming the underlying data in the lake.

Answer

Azure Policy to audit access

Answer

Azure SQL Database dynamic data masking

Answer

Azure Storage blob-level access policies