DP-203 Practice Test 2 — 25 Questions

Question 1

You need to store semi-structured JSON data from a web application that requires low-latency reads and writes at a global scale. The data must be indexed automatically and support SQL-like queries. Which Azure data store should you use?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Azure Cosmos DB (NoSQL API). Azure Cosmos DB with the NoSQL API is the correct choice because it natively stores semi-structured JSON documents, provides automatic indexing of all properties, supports SQL-like queries via its query engine, and offers low-latency reads and writes at global scale through multi-region replication and configurable consistency levels. This combination directly matches the requirements for a globally distributed web application needing fast, queryable JSON storage.

Answer

Azure SQL Database

Answer

Azure Blob Storage

Answer

Azure Table Storage

Question 2

You are monitoring an Azure Data Factory pipeline that runs hourly. You notice that the pipeline has been failing intermittently with an error indicating 'Activity timeout'. Which Azure Monitor metric should you set an alert on to proactively detect such failures?

Accepted Answer

Failed pipeline runs metric. The 'Failed pipeline runs' metric in Azure Monitor directly tracks the number of pipeline runs that have failed, including failures caused by activity timeouts. Setting an alert on this metric allows you to be notified immediately when a pipeline run fails, enabling proactive detection. Other metrics like queue depth or duration may indicate potential issues but do not directly signal a failure. Therefore, the correct metric to alert on for detecting failures is 'Failed pipeline runs'.

Answer

Integration runtime queue depth metric

Answer

Pipeline duration metric

Answer

Data read and data written metrics

Question 3

You have an Azure Synapse Analytics dedicated SQL pool. You notice that some queries are taking longer than expected due to excessive data movement operations. You need to minimize data movement without changing the distribution columns. Which table design approach should you recommend?

Accepted Answer

Use replicated tables for small dimension tables. Replicated tables are recommended for small dimension tables because they are copied to all compute nodes, avoiding data movement during joins. This reduces excessive data movement without changing distribution columns. Option B is incorrect because round-robin distribution distributes data evenly but does not reduce data movement for joins; it is typically used for staging tables. Option C is incorrect because using hash distribution for all tables can lead to data movement when joining on different columns, and it is not a one-size-fits-all solution. Option D is incorrect because partitioning alone does not reduce data movement; it is used for data management and pruning, not for minimizing shuffle operations.

Answer

Use round-robin distribution for dimension tables

Answer

Use hash distribution for all tables

Answer

Use partitioning on join columns

Question 4

You are a data engineer for a retail company. The company uses Azure Data Lake Storage Gen2 to store raw transaction data partitioned by date. Each day, a folder is created with the format 'YYYY/MM/DD' containing thousands of small JSON files (each ~10 KB). An Azure Databricks job runs daily to read the previous day's folder, transform the data, and write to a Delta table for reporting. Over time, the job's execution time has increased from 15 minutes to over 2 hours. The job uses a cluster with 4 nodes (each 16 GB memory). Monitoring shows that the job spends most of its time in the 'listing files' stage. Which optimization should you implement to reduce the job duration?

Accepted Answer

Pre-process the raw data to coalesce small JSON files into larger parquet files (e.g., 256 MB each).. The job spends most of its time in the 'listing files' stage because reading thousands of small JSON files (each ~10 KB) from Azure Data Lake Storage Gen2 incurs high metadata operation overhead. Coalescing these small files into larger Parquet files (e.g., 256 MB each) reduces the number of files that Spark must list and process, dramatically cutting down the listing stage time and improving overall throughput.

Answer

Increase the number of nodes in the cluster to 16.

Answer

Change the output format from JSON to Delta and enable Delta caching.

Answer

Use Azure Data Factory instead of Databricks to copy the raw data.

Question 5

Match each Azure service to its primary purpose in a data engineering pipeline.

Accepted Answer

Azure Data Lake Storage Gen2: Scalable data lake for big data analytics.. The correct matches are: Azure Data Lake Storage Gen2 as a scalable data lake, Azure Synapse Analytics as a unified data warehousing platform, Azure Data Factory for data integration and orchestration, and Azure Databricks for Spark-based analytics. Common confusions include swapping storage and integration services, or mistaking data lakes for data warehousing.

Answer

Azure Data Lake Storage Gen2: Cloud data integration service for orchestrating data movement.

Answer

Azure Synapse Analytics: Scalable data lake for big data analytics.

Question 6

Match each Azure Synapse Analytics component to its function.

Accepted Answer

Dedicated SQL pool: Provisioned compute resources for high-performance querying of relational data.. In Azure Synapse Analytics, Dedicated SQL pool provides provisioned compute for relational data, Serverless SQL pool queries data lake files on-demand, Apache Spark pool handles distributed processing with Spark, and Pipeline orchestrates workflows. Common confusions include mixing serverless and provisioned capabilities or attributing Spark functionality to SQL pools.

Answer

Dedicated SQL pool: Serverless querying of data in data lake.

Answer

Serverless SQL pool: Managed Spark clusters for big data analytics.

Question 7

Match each Azure security feature to its description.

Accepted Answer

Azure Active Directory: A cloud-based identity and access management service that provides authentication and authorization.. The correct matches are: Microsoft Entra ID for identity and access management, Key Vault for secrets management, RBAC for fine-grained access control, and Managed Identity for automatically managed identities. Common confusions include swapping Microsoft Entra ID with Managed Identity and Key Vault with RBAC.

Answer

Azure Active Directory: An automatically managed identity in Azure AD for applications to connect to resources without managing credentials.

Answer

Azure Key Vault: A service that provides role-based access control for Azure resources.

Question 8

You need to configure encryption for an Azure SQL Database to protect data at rest. Which Azure service or feature should you enable?

Accepted Answer

Transparent Data Encryption (TDE). Transparent Data Encryption (TDE) is the correct choice because it performs real-time I/O encryption and decryption of the data and log files at rest, protecting against unauthorized access to the physical storage media. TDE uses an AES-256 encryption algorithm and is fully transparent to the application, requiring no changes to the database schema or queries.

Answer

Dynamic Data Masking

Answer

Always Encrypted

Answer

Azure Information Protection

Question 9

Refer to the exhibit. You are reviewing an ARM template for an Azure Data Lake Storage Gen2 account. Which of the following security best practices is violated in this template?

Accepted Answer

The account allows HTTP traffic and uses an outdated TLS version. The template sets supportsHttpsTrafficOnly to false, allowing HTTP traffic, which is insecure. Additionally, minimumTlsVersion is set to TLS1_0, an outdated and insecure version. Option A is incorrect because the location uses resourceGroup().location, which is a common and acceptable practice for flexibility. Option B is incorrect because Standard_LRS is a valid SKU; Geo-redundant storage is not a security best practice requirement. Option C is incorrect because the template does enable hierarchical namespace (isHnsEnabled: true), so that is not a violation.

Answer

The location should be fixed instead of using resourceGroup().location

Answer

The SKU should be Standard_GRS for disaster recovery

Answer

The account does not enable hierarchical namespace (HNS)

Question 10

You have a streaming pipeline using Azure Stream Analytics that ingests data from Event Hubs and outputs to Azure Synapse Analytics. The job has a high watermark delay and is falling behind. You need to reduce the latency. Which action should you take?

Accepted Answer

Increase the number of Streaming Units (SUs) for the Stream Analytics job.. Increasing the number of Streaming Units (SUs) for the Stream Analytics job allocates more compute resources, reducing latency. Adding more Event Hubs partitions may improve throughput but not directly reduce latency if the job is already bottlenecked. Switching to reference data input does not help. Using Azure Functions for output may add overhead.

Answer

Add more partitions to the Event Hubs.

Answer

Replace the output with Azure Functions for each event.

Answer

Change the input to a reference data input.

Question 11

You are monitoring an Azure Data Factory pipeline that copies data from Azure Blob Storage to Azure SQL Database. The pipeline fails intermittently with the error: 'Operation on target SQL table failed: String or binary data would be truncated.' Which action should you take to resolve this issue?

Accepted Answer

Increase the length of the destination columns in the SQL table to accommodate the source data.. The error indicates that source data length exceeds destination column length. Increasing column size resolves it. Option B is incorrect because the table already exists. Option C is incorrect because the error is not about connection. Option D is incorrect because the error is not about identity insert.

Answer

Set 'enable identity insert' to true.

Answer

Use auto-create table option in the copy activity.

Answer

Enable staging copy to use PolyBase.

Question 12

Refer to the exhibit. You are reviewing an Azure Stream Analytics job query. The job has a stream input and a reference data input. The job is failing with the error 'Reference data input must be of type Reference, not Stream'. What is the cause of the error?

Accepted Answer

The input type is Stream; it should be Reference.. The error 'Reference data input must be of type Reference, not Stream' explicitly indicates that the input configured as Stream should be set to Reference type. In Azure Stream Analytics, reference data inputs are used for static lookup data, and they must be declared as Reference input type. Setting the input type to Stream when it should be Reference causes this error. Therefore, option C is correct. Option D is incorrect because the error message does not relate to output configuration; it is strictly about input type mismatch.

Answer

The input alias is incorrect.

Answer

The JOIN syntax is incorrect for reference data.

Answer

The output type is set to ReferenceData; it should be a different type.

Question 13

You are designing a data processing solution for a retail company that uses Azure Synapse Analytics. The solution must process point-of-sale (POS) data from multiple stores. The data arrives in CSV files in Azure Data Lake Storage Gen2. Each store sends a file every hour. You need to process the files as they arrive and load the data into a dedicated SQL pool. The solution must handle late-arriving files (files that arrive after the scheduled processing time) and ensure that the data is consistent. Which approach should you use?

Accepted Answer

Use Azure Data Factory with a Copy activity to load data into a staging table in the dedicated SQL pool, then use a Stored Procedure activity to merge the data into the final table.. The recommended approach for handling late-arriving files and ensuring data consistency when loading into a dedicated SQL pool is to use Azure Data Factory with a Copy activity to load data into a staging table, then use a Stored Procedure activity to merge the data into the final table. This pattern allows for upserts and handles late-arriving data by merging based on business keys.

Answer

Use Azure Data Factory with a Copy activity to load data into a staging table, then use a Data Flow activity to perform upserts.

Answer

Use Azure Databricks to read the CSV files, perform upserts, and write to the dedicated SQL pool using JDBC.

Answer

Use PolyBase to create external tables on the CSV files and then use CREATE TABLE AS SELECT to load into the dedicated SQL pool.

Question 14

You are building an Azure Stream Analytics job that reads JSON events from an Azure Event Hub and writes aggregated results to an Azure Synapse Analytics dedicated SQL pool. The events include a field named `EventTime` that is sometimes missing or malformed. You need the job to process only events with a valid `EventTime` and route invalid events to a separate output for later inspection. What should you do?

Accepted Answer

In the Stream Analytics query, use a WITH clause to define a filtered stream that selects events where TRY_CAST(EventTime AS datetime) IS NOT NULL, and write the excluded events to a separate output using a second query.. Stream Analytics queries can filter and split streams using standard SQL expressions. TRY_CAST safely converts values and returns NULL for malformed input, allowing a filtered stream of valid events. A second query selecting the complement routes invalid events to a separate output. This approach keeps the job running without failures and provides a mechanism to inspect bad data later.

Answer

Configure the Event Hub input to use the JSON serialization format with the `EventTime` field marked as required, so the job automatically drops events missing that field.

Answer

Use a JavaScript user-defined function in the query to validate `EventTime` and throw an exception for invalid events, which Stream Analytics will automatically redirect to the job's error log.

Answer

Add a second output to the job that writes to Azure Blob Storage, and configure the Event Hub input to send malformed events to that output.

Question 15

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 16

Your company runs a streaming job in Azure Stream Analytics that ingests data from Event Hubs and outputs to Azure Synapse Analytics. The job is failing with a 'Watermark delay' alert and the output to Synapse is delayed by over 30 minutes. The input rate is 5,000 events per second. The job uses a 1-minute tumbling window. What is the most likely cause of the delay?

Accepted Answer

The Stream Analytics job is under-provisioned in terms of Streaming Units (SUs).. A watermark delay alert in Azure Stream Analytics indicates that the job is falling behind in processing incoming data. With an input rate of 5,000 events per second and a 1-minute tumbling window, the job requires sufficient Streaming Units (SUs) to keep up. Under-provisioned SUs cause backpressure, leading to output delays exceeding 30 minutes.

Answer

The output schema in Synapse does not match the Stream Analytics output.

Answer

The Event Hubs has a large number of late-arriving events.

Answer

The tumbling window size is too large.

Question 17

Which TWO are valid ways to process data in Azure Synapse Analytics?

Accepted Answer

Use Synapse SQL pool to run T-SQL queries.. Option C is correct because a Synapse SQL pool (dedicated or serverless) is a core Synapse Analytics engine that executes T-SQL queries directly against data in the workspace, making it a valid data-processing method. Option E is correct because Synapse Spark notebooks run Apache Spark code, including Scala, Python, SQL, and R, and are a first-class way to transform and process data in Azure Synapse Analytics. Option A is not appropriate because Logic Apps is a workflow/automation service for orchestrating triggers and connectors, not a data transformation engine. Option B is not the intended Synapse processing method because Azure Functions is a separate serverless compute service outside Synapse's built-in SQL and Spark engines. Option D is not correct because Power BI is a visualization and reporting tool; its data transformation (Power Query) is for modeling/reporting, not a Synapse data-processing workload.

Answer

Use Logic Apps to run data transformations.

Answer

Use Azure Functions to process data in a serverless manner.

Answer

Use Power BI to transform data.

Question 18

You are designing a data processing solution in Azure Synapse Analytics. The solution must support both batch and streaming data ingestion. Which Azure service should you use to ingest streaming data into Synapse Analytics?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is Microsoft's managed, real-time event-ingestion service designed for high-throughput streaming scenarios, and it integrates natively with Azure Synapse Analytics (via the Synapse Event Hubs connector or Spark Structured Streaming) to land streaming data into dedicated SQL pools, Spark pools, or the lake. It is the canonical answer for streaming ingestion into Synapse.

Answer

Azure Data Factory

Answer

Azure Blob Storage

Answer

Azure Analysis Services

Question 19

Your organization has an Azure Synapse Analytics dedicated SQL pool that stores sensitive customer data. You need to ensure that only authorized users can access the data, and auditing must be enabled to track all access attempts. What should you do first?

Accepted Answer

Configure Microsoft Entra ID authentication and use RBAC to grant only necessary permissions.. Before implementing column-level security, dynamic data masking, or auditing, you must first establish who the authorized users are by configuring Microsoft Entra ID authentication and applying RBAC to grant only the necessary permissions. This foundational identity and access control step ensures that subsequent security features (masking, column-level security, auditing) operate against a properly authenticated and authorized user base.

Answer

Implement column-level security to restrict sensitive columns.

Answer

Enable auditing on the SQL pool and configure a storage account for audit logs.

Answer

Apply dynamic data masking to the sensitive columns.

Question 20

You are monitoring Azure Stream Analytics job performance. The job is falling behind in processing real-time data. You notice that the SU (Streaming Unit) utilization is consistently at 90% or higher. What is the most appropriate action to improve throughput?

Accepted Answer

Increase the number of Streaming Units (SUs). Streaming Unit (SU) utilization consistently at 90% or higher indicates the job is resource-constrained. Increasing the number of SUs allocates more compute resources, which can improve throughput and reduce backlog. This is the most direct and appropriate action to scale the job.

Answer

Change the output to use a partition scheme

Answer

Reduce the window duration in the query

Answer

Decrease the event ordering tolerance