DP-203 Practice Test 3 — 10 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.