DP-203 Exam Simulation 1 — 100 Questions

Question 1

Drag and drop the steps to set up Azure Purview for data cataloging and lineage tracking into the correct order.

Accepted Answer

Create Azure Purview account, then Register data sources, then Scan data sources, then Set classifications, then Explore catalog. First create the Purview account, then register sources, scan them, set classifications, and finally explore the catalog.

Answer

Create Azure Purview account, then Register data sources, then Set classifications, then Scan data sources, then Explore catalog

Answer

Register data sources, then Create Azure Purview account, then Scan data sources, then Set classifications, then Explore catalog

Answer

Create Azure Purview account, then Scan data sources, then Register data sources, then Set classifications, then Explore catalog

Question 2

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 3

You are designing a batch processing solution using Azure Databricks. The data source is a large Parquet dataset stored in Azure Data Lake Storage Gen2 (ADLS Gen2). The processing requires joining two datasets: one with 10 billion rows and another with 1 million rows. The cluster uses Photon runtime. Which optimization should you apply to minimize shuffle?

Accepted Answer

Broadcast the smaller table (1 million rows) to all worker nodes.. Broadcasting the smaller table (1 million rows) to all worker nodes is the correct optimization because it eliminates the need for a full shuffle during the join. With Photon runtime, broadcast joins are highly efficient as they replicate the small table to each executor, allowing map-side joins that avoid costly data movement across the network. Given the 10:1 row ratio, the 1-million-row table is well within the default broadcast threshold (10 MB compressed, configurable via spark.sql.autoBroadcastJoinThreshold), making this the most effective shuffle-minimization technique.

Answer

Increase the cluster size to reduce shuffle overhead.

Answer

Create bucketed tables on the join key for both datasets.

Answer

Use Delta Lake and optimize file layout with OPTIMIZE command.

Question 4

You are developing a data processing solution in Azure Synapse Analytics. The solution must use a serverless SQL pool to query Parquet files stored in Azure Data Lake Storage Gen2. Which authentication method should you use to ensure that the queries use the identity of the caller and adhere to Azure role-based access control (RBAC) permissions?

Accepted Answer

Microsoft Entra ID pass-through authentication.. Microsoft Entra ID pass-through authentication (option A) is correct because it allows the serverless SQL pool to use the caller's identity when accessing Azure Data Lake Storage Gen2. This ensures that Azure RBAC permissions (e.g., Storage Blob Data Reader) assigned to the user are evaluated for each query, providing fine-grained access control without exposing storage account keys or tokens.

Answer

Storage account key.

Answer

Shared access signature (SAS) token.

Answer

Service principal with a secret.

Question 5

You need to transform semi-structured JSON data into a tabular format for analysis in Azure Synapse Analytics. The data is stored in ADLS Gen2. Which feature should you use to query the JSON data directly without loading it into a table?

Accepted Answer

Use the OPENROWSET function in a serverless SQL pool.. OPENROWSET in Synapse serverless SQL can query JSON files directly. Option B (Azure Data Factory) flattens JSON but is an orchestration tool, not a direct query method. Option C (PolyBase) requires external tables. Option D (COPY INTO) loads data into a table, not direct query.

Answer

Use Azure Data Factory to flatten the JSON and store as Parquet.

Answer

Create an external table using PolyBase.

Answer

Use the COPY INTO command in a dedicated SQL pool.

Question 6

A company uses Azure Databricks for data processing. They want to monitor the performance of Spark jobs and set up alerts for job failures. Which Azure service should they use?

Accepted Answer

Azure Monitor. Azure Monitor is the central service for collecting metrics and logs from Azure Databricks, enabling performance monitoring and alerting on Spark jobs. Option A is incorrect because Azure Advisor provides recommendations but not real-time monitoring or alerts. Option B is incorrect because Azure Sentinel is a SIEM solution for security incidents, not for job performance monitoring. Option C is incorrect because Azure Log Analytics is a component of Azure Monitor used for log analysis, but the overarching service for monitoring and alerts is Azure Monitor.

Answer

Azure Advisor

Answer

Azure Sentinel

Answer

Azure Log Analytics

Question 7

A company is designing a data storage solution for IoT device telemetry. Each device sends a JSON payload every second. The data must be stored in a way that supports real-time dashboards and long-term analytics with low latency. Which Azure data store should be used for the ingestion layer?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is the correct choice for the ingestion layer because it is a fully managed, real-time data streaming platform designed to ingest millions of events per second with low latency. It supports the capture of JSON telemetry from IoT devices and integrates directly with downstream analytics services like Azure Stream Analytics for real-time dashboards and long-term storage in Azure Data Lake or Blob Storage. Its partitioned throughput model ensures scalable, durable ingestion without blocking producers.

Answer

Azure SQL Database

Answer

Azure Blob Storage

Answer

Azure Data Lake Storage

Question 8

Match each Azure data integration tool to its typical use case.

Accepted Answer

Azure Data Factory: Orchestrate and automate data movement and transformation at scale.. In this matching exercise, the correct pairs are: Azure Data Factory for orchestration, Azure Synapse Analytics for data warehousing, Azure Databricks for data engineering/ML, and Azure Stream Analytics for real-time streaming. Common confusions involve swapping orchestration and streaming roles.

Answer

Azure Data Factory: Process real-time streaming data from IoT devices.

Answer

Azure Synapse Analytics: Orchestrate data movement at scale.

Question 9

You are building a real-time dashboard to monitor user activity on a website. The data is ingested via Azure Event Hubs and must be aggregated every minute with a 30-second late-arrival tolerance. The aggregated results should be stored in Azure Cosmos DB for low-latency reads. Which Azure service should you use to perform the windowed aggregation?

Accepted Answer

Azure Stream Analytics with a tumbling window of 1 minute and a late-arrival policy of 30 seconds.. Azure Stream Analytics is the correct choice because it natively supports windowed aggregations (tumbling, hopping, sliding, session) and allows you to define a late-arrival policy to handle out-of-order events. A tumbling window of 1 minute with a late-arrival tolerance of 30 seconds meets the requirement exactly, and the output can be directly written to Azure Cosmos DB for low-latency reads.

Answer

Azure Functions triggered by Event Hubs to aggregate data and write to Cosmos DB.

Answer

Azure Databricks with structured streaming and a sliding window.

Answer

Azure Analysis Services to process streaming data directly from Event Hubs.

Question 10

Which TWO options are recommended bulk loading methods for Azure Synapse SQL Pool? (Choose two.)

Accepted Answer

Using the COPY statement. For Azure Synapse SQL Pool, the recommended bulk loading methods are the COPY statement and PolyBase. BCP and SSIS are supported for some scenarios, but they are not the recommended bulk loading methods for large-scale data loads. INSERT INTO VALUES is row-by-row and is not recommended for bulk loading.

Answer

Using INSERT INTO VALUES

Answer

Using BCP utility

Answer

Using SQL Server Integration Services (SSIS)

Question 11

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?

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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.