DP-203 Timed Practice Test 2 — 10 Questions

Question 1

You are designing a data storage solution for real-time streaming data from IoT devices. The data must be stored in its original format for immediate processing and later transformed for analytics. Which Azure service should you use for raw data ingestion?

10 minute time limit — choose an answer to begin.

Accepted Answer

Azure Event Hubs. Azure Event Hubs is a fully managed, real-time data ingestion service optimized for high-throughput streaming data from IoT devices. It can receive millions of events per second, store them in a partitioned, ordered log for immediate processing, and retain them for up to 7 days (or longer with Event Hubs Capture) for later transformation and analytics. This makes it the correct choice for raw data ingestion before any transformation occurs.

Answer

Azure Data Lake Storage Gen2

Answer

Azure Stream Analytics

Answer

Azure Data Factory

Question 2

A company is designing a data lake solution on Azure Data Lake Storage Gen2. Data will be ingested from IoT devices at high frequency (every 5 seconds). Each device sends a JSON payload of 2 KB. The data must be stored in a hierarchical namespace and partitioned by date and device ID to optimize query performance. Which partition strategy should be used?

Accepted Answer

Organize folders as /YYYY/MM/DD/DeviceID/ in ADLS Gen2 and use file naming that includes timestamp.. ADLS Gen2 with a hierarchical namespace allows folder-based partitioning by date and device ID (e.g., /YYYY/MM/DD/DeviceID/), which directly maps to the query optimization requirement. This structure enables efficient partition pruning for time-range and device-specific queries, and the high-frequency 2 KB JSON payloads are well-suited for append-friendly file naming with timestamps.

Answer

Use Azure SQL Database with clustered columnstore index on date and device ID.

Answer

Use Azure Table Storage with PartitionKey set to date and RowKey set to device ID.

Answer

Use Azure Cosmos DB with partition key on (date, device ID) and TTL for data retention.

Question 3

Your Azure Synapse Analytics workspace uses serverless SQL pools for ad-hoc querying. Users report that queries are slow. You examine the execution plan and see that the query scans multiple partitions in the openrowset. What is the best way to improve performance?

Accepted Answer

Add a WHERE clause on the partition column. In serverless SQL pools, partition elimination only occurs when the query includes a filter predicate on the partition column. Adding a WHERE clause on the partition column lets the engine skip irrelevant partitions in the OPENROWSET scan, dramatically reducing I/O and query time. Without that predicate, the engine must scan every partition even if the data is partitioned.

Answer

Increase the MAXDOP setting

Answer

Create materialized views on the external tables

Answer

Partition the underlying data by a frequently filtered column

Question 4

Which Azure service provides fully managed, serverless relational database capabilities for transactional workloads in a data storage solution?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed, serverless relational database service designed for transactional workloads. It provides built-in high availability, automatic scaling, and pay-per-use billing, making it ideal for OLTP scenarios without the need to manage underlying infrastructure.

Answer

Azure Cosmos DB

Answer

Azure Synapse Analytics

Answer

Azure Data Lake Storage Gen2

Question 5

Refer to the exhibit. You are creating a serverless SQL table in Azure Synapse Analytics that reads Parquet files from the specified location. The folder contains multiple Parquet files with different schemas. When querying the table, you get an error about schema mismatch. What is the most likely reason?

Accepted Answer

The serverless SQL pool infers schema from the first file and expects all files to have the same schema.. Azure Synapse serverless SQL pools infer the schema from the first Parquet file encountered in the specified location. When multiple Parquet files with different schemas exist, the pool expects all subsequent files to match that initial schema. If any file has a different schema (e.g., different column names, data types, or number of columns), a schema mismatch error is raised. This behavior is by design, as serverless SQL does not merge or reconcile disparate schemas across files.

Answer

The Parquet files are not using the .parquet extension.

Answer

The derivedModel option is set to false, which disables schema inference.

Answer

The recursive option is causing the table to include files from subfolders that have different schemas.

Question 6

A company uses Azure Synapse Analytics dedicated SQL pool. They need to ensure that only users with a specific Azure AD group can query a particular schema. Which approach should they use?

Accepted Answer

Use the GRANT statement to grant SELECT on the schema to the Azure AD group.. The GRANT statement in Azure Synapse dedicated SQL pool allows you to assign permissions directly to Microsoft Entra ID groups. By granting SELECT on the schema to the specific Microsoft Entra ID group, only members of that group can query objects within that schema, meeting the requirement precisely.

Answer

Configure a server-level firewall rule to block other users.

Answer

Create a row-level security policy on all tables in the schema.

Answer

Apply dynamic data masking to the schema.

Question 7

You are responsible for securing an Azure Synapse Analytics workspace. You need to ensure that only authorized users can query the serverless SQL pool. Which authentication method should you use?

Accepted Answer

Microsoft Entra ID authentication. Microsoft Entra ID authentication (formerly Azure AD) is the recommended authentication method for serverless SQL pools in Azure Synapse Analytics. It provides centralized identity management, supports conditional access policies, and allows individual user identities to be used for auditing and access control. Option B (managed identity) is designed for service-to-service authentication (e.g., Azure services), not for interactive user queries. Option C (SQL authentication) is less secure as it relies on static credentials, and Microsoft recommends against it for production workloads. Option D (Azure Key Vault) is a secret store, not an authentication method.

Answer

Managed identity authentication

Answer

SQL authentication

Answer

Azure Key Vault authentication

Question 8

You are designing a data processing solution using Azure Synapse Analytics serverless SQL pool. The solution will query data stored in Parquet files in Azure Data Lake Storage Gen2. You need to ensure that the queries are optimized for performance. Which action should you take?

Accepted Answer

Partition the Parquet files by date and use partition pruning in the query.. In Azure Synapse serverless SQL pool, performance is dominated by how much data must be read from storage, since there is no persistent compute or indexing. Partitioning Parquet files by date and filtering on the partition column enables partition pruning, so the engine reads only the relevant folders instead of scanning the entire dataset. This dramatically reduces I/O and query time, making D the correct optimization.

Answer

Increase the MAXDOP setting in the query.

Answer

Convert the Parquet files to CSV format for faster parsing.

Answer

Create materialized views on the external tables.

Question 9

Drag and drop the steps to implement incremental data loading using Azure Data Factory into the correct order.

Accepted Answer

Lookup the last watermark value from control table, then copy data from source where timestamp > watermark, then update watermark with max timestamp from copied data.. Incremental loading requires a watermark column. The pipeline retrieves the last watermark, copies data changed since then, then updates the watermark for next run.

Answer

Copy data from source, then lookup the last watermark value, then update watermark.

Answer

Update watermark with max timestamp from source, then lookup the last watermark, then copy data.

Answer

Lookup the last watermark value, then update watermark with max timestamp, then copy data.

Question 10

You are reviewing the ARM template snippet for an Azure Data Lake Storage Gen2 account. The template fails to deploy with an error that the encryption key cannot be accessed. What is the most likely cause?

Accepted Answer

The Data Lake Storage account does not have Get and Wrap Key permissions on the key vault.. When an ARM template configures customer-managed keys for Azure Data Lake Storage Gen2, the storage account's system-assigned or user-assigned managed identity must have Get, Wrap Key, and Unwrap Key permissions on the key vault. The error 'encryption key cannot be accessed' almost always indicates the access policy or RBAC role assignment is missing these permissions, so the storage service cannot retrieve the key. Soft-delete, key name/version, and URI formatting would produce different, more specific errors.

Answer

The key vault does not have soft-delete enabled.

Answer

The key name or version is incorrect.

Answer

The key vault URI is incorrectly formatted.