DP-203 Practice Exam 2 — 20 Questions

Question 1

You are designing a batch processing solution in Azure Synapse Analytics using pipelines. The solution must load data from multiple sources (Azure Blob Storage, Azure SQL Database, and REST API) into a dedicated SQL pool. After loading, you need to run a stored procedure to aggregate the data. Which two activities should you include in the pipeline? (Choose two.)

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

Accepted Answer

Copy activity. The Copy activity (B) is correct because it is the native Azure Synapse Analytics/Data Factory pipeline activity designed to ingest data from disparate sources such as Azure Blob Storage, Azure SQL Database, and REST API connectors into a dedicated SQL pool sink, handling schema mapping and parallel loading. The Stored procedure activity (D) is correct because it invokes a stored procedure in the dedicated SQL pool after the load completes, which is exactly what is needed to run the aggregation logic on the loaded data. The Execute Pipeline activity (A) only calls another pipeline and does not itself move or transform data, so it does not satisfy the load or aggregation requirement. The Azure Function activity (C) runs custom code in an Azure Function, which is unnecessary here since built-in Copy and Stored procedure activities cover the scenario. The Data Flow activity (E) performs code-free transformations in a Spark-based execution engine, but the requirement is to load data and then aggregate via a stored procedure, not to transform data in a mapping data flow.

Answer

Execute Pipeline activity

Answer

Azure Function activity

Answer

Data Flow activity

Question 2

Refer to the exhibit. An Azure Policy is defined to enforce network security on storage accounts. What does this policy do?

Accepted Answer

Denies storage accounts that allow public network access from all networks. The Azure Policy in the exhibit uses the 'Deny' effect with a condition that checks if the 'networkAcls.defaultAction' property is set to 'Allow'. When 'defaultAction' is 'Allow', the storage account permits traffic from all networks, including the internet. The policy denies such configurations to enforce network security by requiring that public network access be restricted.

Answer

Denies storage accounts that do not have any IP rules defined

Answer

Denies storage accounts that have firewall rules configured

Answer

Denies storage accounts that have public network access disabled

Question 3

A data engineer needs to store semi-structured JSON logs from multiple sources in Azure. The logs must be queryable using T-SQL and support schema-on-read. Which Azure service should be used?

Accepted Answer

Azure Synapse serverless SQL pool with JSON files in ADLS Gen2.. Azure Synapse serverless SQL pool can query JSON files stored in ADLS Gen2 using T-SQL, supporting schema-on-read by inferring the schema from the file content at query time. This makes it ideal for semi-structured logs that need to be queried without predefined schema.

Answer

Azure Data Factory mapping data flows.

Answer

Azure Cosmos DB Core (SQL) API.

Answer

Azure SQL Database with JSON columns.

Question 4

You are designing a solution to store large amounts of log data that is written once and accessed rarely. The data must be retained for 7 years for compliance. After 30 days, the data should be moved to a lower-cost storage tier. After 1 year, the data should be archived. Which Azure Storage lifecycle management policy should you implement for an Azure Data Lake Storage Gen2 account?

Accepted Answer

Transition to cool tier after 30 days; transition to archive tier after 365 days; delete after 2555 days (7 years).. It aligns with the specified lifecycle requirements: transition to cool tier after 30 days for cost savings, transition to archive tier after 365 days for long-term retention, and delete after 2555 days (7 years) for compliance. Azure Data Lake Storage Gen2 supports lifecycle management policies that automate tier transitions and deletion based on age, ensuring data is moved to lower-cost storage as access patterns change.

Answer

Transition to cool tier after 30 days; delete after 7 years.

Answer

Transition to archive tier after 30 days; delete after 7 years.

Answer

Transition to cool tier after 30 days; transition to cool tier again after 365 days.

Question 5

A data engineer is designing a monitoring solution for Azure Data Factory pipelines. They need to be alerted when a pipeline run fails or when the duration exceeds a threshold. The solution must minimize cost and operational overhead. Which approach should they use?

Accepted Answer

Use Azure Monitor metrics and activity logs to create alert rules for pipeline failures and duration.. Azure Monitor provides native, cost-effective alerting for Azure Data Factory pipelines using metrics (e.g., pipeline run duration) and activity logs (e.g., pipeline run failures). This approach requires no additional compute or log ingestion costs, as alerts are configured directly on the resource's monitoring data, minimizing both cost and operational overhead.

Answer

Configure Azure Event Grid to send pipeline run events to Azure Functions for alerting.

Answer

Send all pipeline run logs to Log Analytics and create alert rules based on custom log searches.

Answer

Create an Azure Logic App that runs every minute to check pipeline run status via REST API.

Question 6

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?

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 7

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 8

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 9

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 10

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 11

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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