DP-203 Practice Test 46 — 15 Questions

Question 1

You are building a data processing solution in Azure Synapse Analytics. The solution requires creating a table that stores sales transactions. The table will be used for both point-of-sale lookups and large aggregation queries. The data is not updated frequently. Which table distribution should you recommend?

Accepted Answer

ROUND_ROBIN. ROUND_ROBIN is the correct choice because the table is used for both point-of-sale lookups (single-row queries) and large aggregation queries, and the data is not updated frequently. ROUND_ROBIN distributes data evenly across all distributions without a hash key, which provides the best overall performance for mixed workloads where no single distribution key optimizes both lookup and aggregation patterns. It avoids data skew and allows parallel processing for aggregations while still supporting efficient lookups when combined with appropriate indexes.

Answer

HASH on TransactionID

Answer

HASH on SalesDate

Answer

REPLICATE

Question 2

Which TWO are valid methods to load data into Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

PolyBase. PolyBase is a valid method for loading data into Azure Synapse Analytics dedicated SQL pool because it uses the T-SQL language to access and combine data from external sources like Azure Blob Storage or Azure Data Lake Store without needing to move the data first. The COPY statement is also valid as it provides a high-throughput, flexible ingestion mechanism that supports various file formats and error handling options directly via T-SQL.

Answer

BCp utility

Answer

Azure Data Factory Copy Activity

Answer

BULK INSERT

Question 3

Which Azure service is primarily used for orchestrating data pipelines in a cloud-native ETL workflow?

Accepted Answer

Azure Data Factory. Azure Data Factory (ADF) is the correct answer because it is a cloud-native, serverless data integration service specifically designed for orchestrating and automating data pipelines. ADF provides a code-free visual interface, supports over 90 built-in connectors, and enables complex ETL/ELT workflows with control flow, data flow, and trigger-based scheduling, making it the primary orchestration tool in Azure.

Answer

Azure Synapse Analytics

Answer

Azure HDInsight

Answer

Azure Databricks

Question 4

You have an Azure Data Lake Storage Gen2 account that stores large volumes of parquet files. A reporting application frequently queries a specific subset of data filtered by a 'region' column. To minimize query latency and cost, which optimization should you implement?

Accepted Answer

Partition the data by region in the folder structure.. Partitioning the data by region in the folder structure (e.g., /region=NorthAmerica/...) enables Azure Data Lake Storage Gen2 and query engines like Azure Synapse or PolyBase to perform partition pruning. This skips scanning irrelevant files entirely, reducing I/O and query latency while lowering cost by minimizing data processed.

Answer

Create a clustered index on the region column.

Answer

Compress the parquet files using gzip.

Answer

Enable hierarchical namespace on the storage account.

Question 5

Which of the following are valid activities in an Azure Data Factory pipeline? (Choose two.)

Accepted Answer

Copy. A is correct because the Copy activity is a fundamental data movement activity in Azure Data Factory (ADF) that allows you to ingest data from a wide range of supported source stores (e.g., Azure Blob Storage, SQL Server) to sink stores (e.g., Azure Synapse Analytics, Azure Data Lake Storage). It uses the underlying integration runtime to perform the actual data transfer, supporting both built-in connectors and self-hosted runtimes for on-premises sources. This makes it a core, valid activity for building ETL/ELT pipelines.

Answer

Assign

Answer

Execute Pipeline

Question 6

You are designing a data processing solution using Azure Databricks. The data is stored in Delta Lake format. You need to ensure that when you read the latest version of the table, you only see committed data and not uncommitted transactions. Which isolation level should you use?

Accepted Answer

SnapshotIsolation. Snapshot isolation is the correct choice because it provides a consistent view of the table by reading only the latest committed data, ignoring any uncommitted transactions. In Delta Lake, snapshot isolation ensures that readers see a snapshot of the table at a specific version, which includes only committed changes, making it ideal for read consistency without blocking concurrent writes.

Answer

WriteSerializable

Answer

Serializable

Answer

ReadUncommitted

Question 7

You are designing a data processing solution that requires exactly-once processing semantics for streaming data. Which two Azure services support exactly-once processing? (Choose two.)

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics supports exactly-once processing by using checkpointing and event sourcing to ensure that each event is processed exactly once, even in the event of failures or restarts. It achieves this through its internal state management and the use of checkpoint offsets in the output sink, guaranteeing no duplicate or missed events.

Answer

Azure Event Hubs

Answer

Azure Data Lake Storage Gen2

Question 8

You are building a streaming pipeline in Azure Stream Analytics that reads from an Azure Event Hubs input with 10 partitions. The query performs a GROUP BY on a column that is not the partition key. To ensure consistency, which partitioning scheme should you use?

Accepted Answer

Use 'PartitionBy' with the GROUP BY column. Option B is correct because when performing a GROUP BY on a column that is not the partition key, you must use the PARTITION BY clause in the query to ensure that all rows with the same grouping value are processed by the same Stream Analytics node. This guarantees consistency and correctness of the aggregation, as it avoids data being split across multiple nodes without proper alignment.

Answer

Use 'Passthrough' partitioning

Answer

Increase the number of SUs to handle skew

Answer

Use 'INTO' with a 'PARTITION BY' clause

Question 9

Which TWO options are correct approaches to handle schema drift in Azure Data Factory Mapping Data Flows?

Accepted Answer

Enable 'Allow schema drift' in the source transformation.. Option D is correct because enabling 'Allow schema drift' in the source transformation is the primary mechanism in Mapping Data Flows to handle incoming columns that are not defined in the dataset schema. This setting allows the data flow to dynamically adapt to changes in the source data structure, such as new or missing columns, without requiring manual schema updates.

Answer

Use a conditional split to route rows with different schemas to separate sinks.

Answer

Define a rigid schema in the source dataset and reject rows that don't match.

Answer

Disable schema drift to improve performance.

Question 10

A company wants to ingest streaming data from IoT devices into Azure for real-time analytics. The data must be available for immediate querying and also stored long-term in a cost-effective format. Which Azure service should be used as the primary ingestion endpoint?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is the correct primary ingestion endpoint for streaming IoT data because it is a fully managed, real-time data ingestion service optimized for high-throughput, low-latency event streaming. It can ingest millions of events per second from IoT devices and integrates natively with Azure Stream Analytics and other analytics services for immediate querying, while also supporting long-term retention via Event Hubs Capture to cost-effective storage like Azure Blob Storage or Data Lake Storage.

Answer

Azure SQL Database

Answer

Azure Data Lake Storage Gen2

Answer

Azure Blob Storage

Question 11

A data engineer is designing a solution to store historical sales data for a retail company. The data is append-only and accessed infrequently for compliance reports. The solution must minimize storage costs while allowing retrieval within 24 hours. Which storage tier should be used for the data?

Accepted Answer

Cool tier. The Cool tier is the correct choice because it is optimized for data that is infrequently accessed and stored for at least 30 days, offering low storage costs with retrieval times in the range of seconds to hours, which meets the 24-hour retrieval requirement. The data is append-only and used for compliance, so the Cool tier balances cost and accessibility without the high retrieval costs or long rehydration delays of the Archive tier.

Answer

Hot tier

Answer

Archive tier

Answer

Premium tier

Question 12

You are designing a data storage solution for a healthcare organization that stores patient records. The solution must comply with HIPAA and support point-in-time restore (PITR) for the last 35 days. The data is frequently accessed for reporting. Which Azure data service should you use?

Accepted Answer

Azure SQL Database. Azure SQL Database is the correct choice because it natively supports point-in-time restore (PITR) for up to 35 days (configurable from 7 to 35 days) and is a HIPAA-eligible service when configured with encryption, auditing, and network security. It provides transactional consistency required for patient records and supports frequent reporting workloads with features like columnstore indexes and read replicas.

Answer

Azure Cosmos DB

Answer

Azure Blob Storage with point-in-time restore

Answer

Azure Data Lake Storage Gen2

Question 13

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 14

A data engineer needs to store semi-structured JSON logs for analysis using Azure Synapse Serverless SQL. Which file format should be used for optimal query performance?

Accepted Answer

Parquet. Parquet is correct because it is a columnar storage format that enables predicate pushdown and compression, significantly reducing the amount of data scanned by Azure Synapse Serverless SQL for analytical queries on semi-structured JSON logs. This format aligns with the engine's design for high-performance read operations on large datasets, unlike row-oriented formats that require full file scans.

Answer

Avro

Answer

CSV

Answer

JSON

Question 15

A data engineer is setting up Azure Data Lake Storage Gen2 for a new project. The security requirement is to prevent direct access to the storage account from the internet while allowing access from a specific virtual network. Which network security feature should be enabled?

Accepted Answer

Firewall and virtual network service endpoints. Option D is correct because Firewall and virtual network service endpoints allow you to restrict access to Azure Data Lake Storage Gen2 to only traffic originating from a specific virtual network, effectively blocking all internet-based access. This is achieved by configuring a service endpoint on the subnet and a firewall rule on the storage account that denies all traffic except that from the designated virtual network.

Answer

Azure Private Endpoint

Answer

Shared access signature (SAS)

Answer

Azure Defender for Storage