DP-203 Practice Test 15 — 15 Questions

Question 1

You are designing a data processing solution for a global company. Data must be processed in near real-time and aggregated by region. You need to minimize latency for downstream consumers. Which Azure service should you use for stream processing?

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

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a fully managed stream processing engine designed for near real-time analytics on high-volume data streams. It can ingest data from sources like Azure Event Hubs or IoT Hub, apply SQL-based transformations, and output aggregated results to sinks such as Azure Synapse or Power BI with sub-second latency, meeting the requirement for minimal downstream latency.

Answer

Azure Batch

Answer

Azure Data Factory

Answer

Azure Synapse Pipelines

Question 2

You are designing a data storage solution for an Azure Data Lake Storage Gen2 account that will store sensitive customer data. The solution must enforce that all data is encrypted at rest using customer-managed keys (CMK) stored in Azure Key Vault. Additionally, you need to prevent data from being accessed by any Azure service except Azure Synapse Analytics. Which combination of configurations should you implement?

Accepted Answer

Enable Azure Storage encryption with customer-managed keys stored in Azure Key Vault and configure a firewall with a service endpoint for Azure Synapse Analytics. It combines two essential controls: Azure Storage encryption with customer-managed keys (CMK) stored in Azure Key Vault ensures data is encrypted at rest using keys you control, and a firewall with a service endpoint for Azure Synapse Analytics restricts network access so that only traffic from Azure Synapse Analytics can reach the storage account. This meets both the encryption and access restriction requirements.

Answer

Enable Azure Storage encryption with customer-managed keys and use a shared access signature (SAS) token for Azure Synapse Analytics

Answer

Enable Azure Storage encryption with customer-managed keys and assign an Azure Policy denying public network access

Answer

Enable Azure Storage encryption with Microsoft-managed keys and configure a private endpoint

Question 3

Refer to the exhibit. You have a managed identity that needs to read data from the 'data' container in Azure Data Lake Storage Gen2. The policy currently denies access. What is the most likely cause?

Accepted Answer

The condition on 'acs:RequestVersion' is preventing access because the request does not use the specified API version. The policy condition requires the request to include the API version '2021-06-08' via the `acs:RequestVersion` condition key. Managed identity requests to Azure Data Lake Storage Gen2 use a default API version that may not match this specific version, causing the deny. The condition explicitly checks the request's API version, and if it does not match, access is denied regardless of other permissions.

Answer

The resource path is malformed; it should include the blob path

Answer

The action 'Microsoft.Storage/storageAccounts/blobServices/containers/read' is incorrect; it should be 'Microsoft.Storage/storageAccounts/blobServices/containers/blobs/read'

Answer

The principal is a managed identity, but the policy requires a user-assigned identity

Question 4

You are a data engineer at a retail company. You need to design a batch processing solution that ingests daily sales data from multiple stores. Each store uploads a CSV file to a dedicated folder in Azure Data Lake Storage Gen2. The files have the same schema but may have minor variations in column order and include null values. After ingestion, you must clean the data by removing rows with null values in the 'SalesAmount' column, convert the 'Date' column from string to date type, and aggregate sales by product category. The output should be stored as Parquet files partitioned by year and month in the same Data Lake. You need to choose a compute service and implement the transformation with minimal coding effort. The solution must be cost-effective and require no cluster management. What should you do?

Accepted Answer

Use Azure Data Factory with a Mapping Data Flow. Configure the source to read all CSV files from the ADLS Gen2 folder. Add a Filter transformation to remove null SalesAmount, a Derived Column to parse the Date, and an Aggregate to sum sales by category. Sink to ADLS Gen2 as Parquet with partition by year and month.. Azure Data Factory Mapping Data Flows provide a code-free visual interface for transformations. They can read multiple CSV files from ADLS Gen2, handle schema variations, filter nulls, parse dates, aggregate, and write partitioned Parquet files. This meets the requirements of minimal coding, cost-effectiveness (pay-per-execution), and no cluster management. A is incorrect because while Azure Synapse Serverless SQL pool can query CSV files and use CETAS to write Parquet, it requires T-SQL coding, handling schema drift is more complex, and partitioning via CETAS can be cumbersome. Also, serverless SQL is optimized for ad-hoc querying rather than batch ETL. B is incorrect because Azure Synapse Dedicated SQL pool requires provisioning and managing a dedicated cluster, incurring ongoing costs even when idle. It also involves more coding with T-SQL and PolyBase, and does not align with 'no cluster management' requirement. D is incorrect because Azure Databricks requires creating and managing a cluster, and coding in PySpark or Scala, which contradicts 'minimal coding effort' and 'no cluster management'.

Answer

Use Azure Synapse Serverless SQL pool. Create external tables over the CSV files, write a T-SQL query to filter, cast, aggregate, and use CETAS to write Parquet partitions.

Answer

Use Azure Synapse Dedicated SQL pool. Load CSV files via PolyBase, transform with T-SQL, and use CREATE TABLE AS SELECT to output partitioned Parquet.

Answer

Use Azure Databricks with a PySpark notebook. Mount the ADLS Gen2 storage, read CSV files with schema inference, filter, cast, aggregate, and write partitioned Parquet.

Question 5

A company wants to ingest streaming data from IoT devices into Azure for real-time analytics. The data must be available for immediate querying and also stored long-term in a cost-effective format. Which Azure service should be used as the primary ingestion endpoint?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is the correct primary ingestion endpoint for streaming IoT data because it is a fully managed, real-time data ingestion service optimized for high-throughput, low-latency event streaming. It can ingest millions of events per second from IoT devices and integrates natively with Azure Stream Analytics and other analytics services for immediate querying, while also supporting long-term retention via Event Hubs Capture to cost-effective storage like Azure Blob Storage or Data Lake Storage.

Answer

Azure SQL Database

Answer

Azure Data Lake Storage Gen2

Answer

Azure Blob Storage

Question 6

Refer to the exhibit. You are deploying an Azure Synapse Analytics dedicated SQL pool using the provided ARM template snippet. After deployment, you need to adjust the performance level to DW200c to handle increased workload. Which parameter should you modify?

Accepted Answer

sku.name. Sku.name. In an Azure Synapse Analytics dedicated SQL pool, the performance level (e.g., DW200c) is defined by the 'sku.name' parameter in the ARM template. Option A (storageAccountType) controls data redundancy, not performance. Option B (maxSizeBytes) sets the maximum storage size. Option C (collation) determines the default collation for the database, which affects language support. Therefore, to adjust performance to DW200c, modify the sku.name parameter.

Answer

storageAccountType

Answer

maxSizeBytes

Answer

collation

Question 7

Your company uses Azure Synapse Analytics dedicated SQL pool for a data warehouse. You notice that queries on a large fact table are slow. The table is hash-distributed on CustomerID and has 60 distributions. After reviewing the query plan, you see that many queries filter on OrderDate. You want to improve query performance without redesigning the table. What should you do?

Accepted Answer

Create a clustered columnstore index on the table and order by OrderDate. Creating a clustered columnstore index with ORDER BY OrderDate on the fact table improves query performance by physically ordering the data by OrderDate within each columnstore segment. This allows segment elimination during queries that filter on OrderDate, reducing the amount of data scanned. Since the table is already hash-distributed on CustomerID, this approach does not require redesigning the table and directly addresses the slow queries without changing the distribution strategy.

Answer

Change the distribution key to OrderDate

Answer

Recreate the table with round-robin distribution

Answer

Update statistics only on the OrderDate column

Question 8

You are designing a data pipeline in Azure Data Factory (ADF) that copies data from an on-premises SQL Server database to Azure Synapse Analytics dedicated SQL pool. The pipeline must run daily and handle incremental loads efficiently. Which sink dataset type and copy method should you use?

Accepted Answer

Use Azure Synapse Analytics dedicated SQL pool as the sink dataset and use Stored Procedure with staging table and PolyBase.. It uses a staging table and PolyBase to efficiently load incremental data into Azure Synapse Analytics dedicated SQL pool. PolyBase provides high-throughput parallel loading, and the stored procedure handles the merge logic (upsert) to manage incremental changes. This approach is recommended for large-scale, daily incremental loads to Synapse.

Answer

Use Azure Synapse Analytics dedicated SQL pool as the sink dataset and use the Copy activity with PolyBase enabled.

Answer

Use Azure Synapse Analytics dedicated SQL pool as the sink dataset and enable the built-in Upsert option.

Answer

Use Azure Blob Storage as the sink dataset, then use PolyBase to load into the dedicated SQL pool.

Question 9

Which Azure storage solution is best suited for storing large volumes of unstructured data, such as log files and media files, and supports both hierarchical namespace and POSIX-like access control lists?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) combines a hierarchical namespace with POSIX-like access control lists (ACLs) on top of Azure Blob Storage. This makes it ideal for storing large volumes of unstructured data (e.g., log files, media files) while supporting fine-grained, POSIX-compliant permissions and directory-level operations that are essential for big data analytics workloads.

Answer

Azure Blob Storage

Answer

Azure Files

Answer

Azure SQL Database

Question 10

You are building a data transformation in Azure Databricks using PySpark. The data includes a column 'timestamp' in string format 'yyyy-MM-dd HH:mm:ss'. You need to convert this to a timestamp type and extract the date part for partitioning. Which code snippet should you use?

Accepted Answer

df.withColumn('date', to_date('timestamp', 'yyyy-MM-dd HH:mm:ss')). `to_date` with the format string 'yyyy-MM-dd HH:mm:ss' converts the string column to a date type, extracting only the date part (year, month, day) as required for partitioning. This matches the requirement to convert the timestamp string to a date for partitioning, not a full timestamp.

Answer

df.withColumn('date', col('timestamp').cast('date'))

Answer

df.withColumn('date', to_timestamp('timestamp', 'yyyy-MM-dd HH:mm:ss'))

Answer

df.withColumn('date', to_date('timestamp'))

Question 11

You are monitoring an Azure Data Factory pipeline that runs every hour. The pipeline uses a Copy activity to copy data from Azure SQL Database to Azure Blob Storage. Recently, the pipeline has been failing with a 'Timeout' error. The source SQL database has a large number of records. What should you do to resolve the timeout?

Accepted Answer

Enable staging and use PolyBase or COPY statement for the copy activity.. Enabling staging with PolyBase or the COPY statement offloads the data transfer to Azure Data Lake or Blob Storage, bypassing the bottleneck of the Copy activity's default data movement. This approach is specifically designed for large-scale data loads from Azure SQL Database, as it uses the database's bulk export capabilities and avoids the timeout by not relying on the Copy activity's internal query execution.

Answer

Decrease the 'writeBatchSize' to 1000.

Answer

Increase the 'timeout' value in the copy activity settings.

Answer

Use a query with 'queryTimeout' set to 7200 seconds.

Question 12

You have an Azure Data Factory pipeline that uses a Self-Hosted Integration Runtime (SHIR) to copy data from an on-premises Oracle database to Azure Blob Storage. The pipeline is failing with a connectivity error. You have verified that the SHIR is running and the network firewall allows outbound traffic to Azure. What is the most likely cause of the failure?

Accepted Answer

The SHIR cannot reach the Oracle database due to a network firewall.. The most likely cause is that the SHIR cannot reach the on-premises Oracle database due to a network firewall. While the SHIR can connect to Azure outbound, the connection from the SHIR to the Oracle database must also be allowed through any on-premises firewalls. Option A is incorrect because if the SHIR were not registered, you would see a different error. Option C is incorrect because the SHIR only requires outbound connectivity to Azure, not inbound. Option D is incorrect because Azure Key Vault is not required for basic connectivity; it is used for storing credentials.

Answer

The SHIR is not registered with Azure Data Factory.

Answer

The SHIR requires inbound port 443 from Azure to on-premises.

Answer

The SHIR does not have access to Azure Key Vault.

Question 13

Which TWO features of Azure Databricks help manage data governance and security for sensitive data?

Accepted Answer

Secret Scopes. Secret Scopes (B) allow secure storage and referencing of sensitive credentials (e.g., API keys, database passwords) in Azure Databricks, preventing hardcoding in notebooks. Unity Catalog (E) provides fine-grained access control, data lineage, and centralized metadata management across workspaces, enabling governance of sensitive data through policies and auditing.

Answer

Structured Streaming

Answer

Auto Loader

Answer

Delta Live Tables

Question 14

Which TWO Azure services can be used to implement a polyglot persistence architecture for an e-commerce application that requires both a relational database for orders and a document database for product catalogs?

Accepted Answer

Azure SQL Database. Azure SQL Database is a relational database service that supports ACID transactions and structured querying, making it ideal for storing and managing e-commerce order data with strong consistency and referential integrity. Azure Cosmos DB is a multi-model NoSQL database that provides document database capabilities with flexible schemas and low-latency access, perfectly suited for product catalogs that require high read throughput and schema evolution. Together, they enable a polyglot persistence architecture by using the best storage model for each workload.

Answer

Azure Cache for Redis

Answer

Azure Table Storage

Answer

Azure Data Lake Storage Gen2

Question 15

You are designing a real-time data processing solution using Azure Stream Analytics. The input is from Azure Event Hubs, and the output is to Azure Synapse Analytics. The solution must guarantee exactly-once delivery to Synapse. Which THREE configurations are required? (Choose three.)

Accepted Answer

Define a watermark strategy in the query to handle late-arriving events.. For exactly-once delivery to Azure Synapse Analytics from Stream Analytics, you need: a unique identifier column in the output table to enable deduplication (D), a watermark strategy to handle late-arriving events (B), and the output table must have a primary key to support upsert operations (E). Batch mode (A) is not supported for exactly-once delivery; instead, the output should use row-level insert/upsert. Setting the late arrival tolerance to zero (C) is not required; it is used to discard events that arrive after the tolerance, but it does not contribute to exactly-once semantics.

Answer

Configure the output to use batch mode for writing.

Answer

Set the late arrival tolerance window to zero.