DP-203 Practice Test 37 — 15 Questions

Question 1

You are implementing a real-time analytics solution using Azure Stream Analytics. The job ingests data from Azure Event Hubs and must output to an Azure SQL Database. You need to ensure that the job can handle out-of-order events and produce accurate aggregations over 5-minute windows. Which setting should you configure?

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

Accepted Answer

Set the 'Out-of-order tolerance' to 5 minutes and use a Tumbling Window of 5 minutes.. To handle out-of-order events and aggregate over fixed 5-minute windows, configure the out-of-order tolerance to 5 minutes and use a Tumbling Window. The tolerance allows late events to be processed, and the Tumbling Window provides non-overlapping, fixed-duration aggregations. Other window types or settings do not meet both requirements simultaneously.

Answer

Set the 'Late arrival tolerance' to 5 minutes and use a Sliding Window of 5 minutes.

Answer

Set the 'Event ordering' to 'Adjust' and use a Session Window with a 5-minute timeout.

Answer

Set the 'Out-of-order tolerance' to 0 and use a Hopping Window of 5 minutes with a 1-minute hop.

Question 2

Your team runs Azure Data Factory pipelines that must copy files from an on-premises file share to Azure Data Lake Storage Gen2 on a nightly schedule. The on-premises network blocks inbound connections and the data must not be exposed to the public internet. You need to enable connectivity without opening firewall ports. What should you deploy?

Accepted Answer

A self-hosted integration runtime installed on a machine in the on-premises network.. A self-hosted integration runtime is installed inside the on-premises network and opens only outbound connections to Azure, so the firewall needs no inbound rules. It executes the copy from the file share and writes to Data Lake Storage Gen2, giving Data Factory a reachable execution host without exposing the internal share to the internet.

Answer

An Azure ExpressRoute circuit provisioned through a connectivity provider.

Answer

An Azure VPN Gateway with a site-to-site connection to the on-premises network.

Answer

An Azure integration runtime with a managed virtual network enabled.

Question 3

You are designing a data storage solution for a company that uses Azure Data Lake Storage Gen2. The company needs to store data in a hierarchical namespace and requires fine-grained access control at the folder and file level. The solution must also support POSIX-style permissions. Which feature should you enable?

Accepted Answer

Enable hierarchical namespace on the storage account.. Azure Data Lake Storage Gen2 is built on Blob Storage and adds a hierarchical namespace, which enables directory operations, POSIX-style permissions, and fine-grained ACLs. Enabling hierarchical namespace on the storage account is the required step to gain these capabilities. Other options are unrelated features that do not provide the needed functionality.

Answer

Enable Azure Defender for Storage.

Answer

Enable static website hosting on the storage account.

Answer

Enable blob soft delete on the storage account.

Question 4

You are optimizing an Azure Synapse Analytics dedicated SQL pool that contains a fact table with 10 billion rows. Queries frequently join this fact table to a dimension table on a column that is not the distribution column of either table. You need to reduce data movement during these joins. Which two actions should you take? (Choose two.)

Accepted Answer

Hash-distribute the fact table on the join column.. To reduce data movement during joins in a dedicated SQL pool, you can either replicate small dimension tables so they are available locally on all nodes, or hash-distribute the large fact table on the join column to colocate matching rows. Both actions minimize shuffle. Replicating the fact table is impractical due to its size, and other options do not directly address data movement.

Answer

Create a materialized view that pre-joins the fact and dimension tables.

Answer

Hash-distribute the fact table on a different column.

Answer

Use a replicated table for the fact table.

Question 5

You are building an Azure Stream Analytics job that reads from an Azure Event Hubs input and writes to an Azure Synapse Analytics dedicated SQL pool. You need to compute a 5-minute tumbling window aggregation that outputs only once per window after all events for that window have arrived. Which query construct should you use?

Accepted Answer

GROUP BY with TUMBLINGWINDOW and a 5-minute window, emitting results when the window closes. Tumbling windows are fixed-size, non-overlapping, and emit a single result when the window closes, which aligns exactly with the need to output once per 5-minute interval after all events arrive. Other window types or external scheduling mechanisms either emit too frequently or cannot guarantee completeness of the window before output.

Answer

A self-join on the input stream using DATEDIFF to group events into 5-minute buckets

Answer

A persistent SQL table in the dedicated SQL pool that stores events, with a scheduled stored procedure running every 5 minutes

Answer

GROUP BY with HOPPINGWINDOW and a 5-minute window and 1-minute hop

Question 6

You are a data engineer at a healthcare company. Your Azure Data Factory pipeline ingests sensitive patient records from an on-premises SQL Server into an Azure Data Lake Storage Gen2 account. The compliance team requires that all data be encrypted at rest with a customer-managed key (CMK) and that key rotation be audited. You need to configure the storage account to meet these requirements. What should you do?

Accepted Answer

Create an Azure Key Vault, generate a customer-managed key, and configure the storage account to use that key for encryption. Enable Key Vault logging and diagnostic settings to audit key rotation.. To encrypt data at rest in Azure Data Lake Storage Gen2 with a customer-managed key, you must use Azure Key Vault to store and manage the key, then associate that key with the storage account. Auditing key rotation requires enabling logging on Key Vault. This ensures both encryption control and compliance auditing.

Answer

Enable Azure Disk Encryption on the virtual machines that run the self-hosted integration runtime, and use BitLocker to encrypt the data before it is uploaded.

Answer

Enable Azure Storage Service Encryption with Microsoft-managed keys and configure Azure Monitor to log key rotation events.

Answer

Configure Azure SQL Database Transparent Data Encryption (TDE) with a customer-managed key and use Azure SQL Auditing to track key rotations.

Question 7

You are monitoring an Azure Data Factory pipeline that copies data from Azure SQL Database to Azure Synapse Analytics. The pipeline occasionally fails with transient errors such as 'Cannot connect to SQL Database' during peak hours. You need to make the pipeline more resilient without manual intervention. What should you configure?

Accepted Answer

Set the retry policy on the Copy activity with a count and interval.. The Copy activity in Azure Data Factory supports a retry policy that automatically re-executes the activity after a failure, with configurable count and interval. This handles transient network or service errors without manual intervention. Other options either address security, throughput, or scheduling but do not provide automatic retry for transient failures within a run.

Answer

Enable Azure Defender for Azure SQL Database.

Answer

Increase the data integration unit (DIU) setting for the Copy activity.

Answer

Configure a trigger with a recurrence schedule that runs more frequently.

Question 8

You are building an Azure Data Factory pipeline that must copy data from an on-premises SQL Server to Azure Blob Storage. The pipeline runs on a schedule every hour. You need to ensure that the copy activity can securely access the on-premises SQL Server. What should you configure?

Accepted Answer

A self-hosted integration runtime installed on a machine in the on-premises network.. To copy data from an on-premises SQL Server, Azure Data Factory requires a self-hosted integration runtime. This component is installed on a machine in the on-premises network and handles the data movement securely. Other options either do not provide on-premises connectivity or are meant for different Azure services.

Answer

A VPN gateway between the on-premises network and Azure.

Answer

An Azure Integration Runtime with a managed virtual network.

Answer

An Azure SQL Database linked service with a private endpoint.

Question 9

You are processing a large dataset in Azure Synapse Analytics using a dedicated SQL pool. You need to load data from Parquet files in Azure Data Lake Storage Gen2 into a staging table, then transform and load into a fact table. The fact table is partitioned by date. You want to maximize query performance and minimize data movement. Which technique should you use?

Accepted Answer

Use PolyBase to load data into a staging table, then use CREATE TABLE AS SELECT (CTAS) to transform and load into the fact table. PolyBase is the most efficient way to load Parquet files into a staging table in a dedicated SQL pool. CTAS then transforms and loads the data into a new table, allowing you to define the distribution and partitioning to optimize query performance. This minimizes data movement because the transformation and load happen within the SQL pool. The other options either use unsupported features, cause inefficient updates, or add unnecessary data movement.

Answer

Use Azure Data Factory Copy activity to load data into the fact table, then run an UPDATE statement to transform

Answer

Use CETAS to export the transformed data to Azure Data Lake Storage Gen2, then use PolyBase to load into the fact table

Answer

Use BULK INSERT to load data directly into the fact table

Question 10

A logistics company needs to store shipment tracking events in Azure Cosmos DB. Events are written continuously throughout the day, and the most common query pattern retrieves all events for a specific shipment ID ordered by timestamp. The workload is write-heavy and must scale horizontally across partitions. Which partition key should you choose?

Accepted Answer

Use the shipment ID as the partition key.. The partition key should align with the dominant query pattern while distributing writes. Using shipment ID places all events for a shipment in the same logical partition, so the common query executes as a single-partition operation, and the high cardinality of shipment IDs spreads write traffic across physical partitions. Timestamp, event type, or random synthetic keys either cause cross-partition fan-out or create hot partitions.

Answer

Use the event timestamp as the partition key.

Answer

Use the event type as the partition key.

Answer

Use a synthetic key formed by concatenating the event timestamp and a random GUID.

Question 11

You are developing an Azure Databricks notebook that processes streaming data from Azure Event Hubs. The notebook must write the processed data to a Delta table. You need to ensure that the stream can handle late data and update previously written records. Which Delta Lake feature should you use?

Accepted Answer

Use the MERGE INTO statement to upsert records into the Delta table.. To handle late data and update existing records in a Delta table from a stream, you should use MERGE INTO within foreachBatch. This allows upserts based on a unique key, ensuring that late-arriving events update the correct records. Append, overwrite, and ignore modes do not provide the required update capability.

Answer

Use the append mode to write new records to the Delta table.

Answer

Use the ignore mode to skip writing if the table already exists.

Answer

Use the overwrite mode to replace the entire Delta table with the new batch.

Question 12

You are optimizing an Azure Synapse Analytics dedicated SQL pool. You need to reduce query execution time for large fact tables that are frequently joined with dimension tables. Which two actions should you perform? (Choose two.)

Accepted Answer

Create a clustered columnstore index on the fact tables.. To reduce query execution time for large fact tables frequently joined with dimension tables in a dedicated SQL pool, you should ensure a clustered columnstore index is used and hash distribute the fact tables on the join key. Clustered columnstore indexes optimize analytical queries through compression and batch processing, while hash distribution on the join key minimizes data movement during joins. The other options either use less efficient indexing, distribution methods that cause shuffling, or caching that does not address join performance.

Answer

Enable result set caching on the dedicated SQL pool.

Answer

Use round-robin distribution for the fact tables.

Answer

Create a clustered index on the fact tables.

Question 13

You manage an Azure Data Lake Storage Gen2 account that stores sensitive financial data. The data must be encrypted at rest, and access must be audited. You need to ensure that encryption keys are managed by your organization and that all access attempts are logged. Which TWO actions should you take? (Choose two.)

Accepted Answer

Enable diagnostic logging for the storage account and send logs to Azure Monitor.. To meet the requirements, you need customer-managed keys for organizational control over encryption keys, and diagnostic logging to audit all access attempts. Customer-managed keys are configured in Azure Key Vault and used by the storage account for encryption at rest. Diagnostic logs capture detailed access information and can be routed to Azure Monitor for analysis and retention.

Answer

Enable Azure Storage Service Encryption with Microsoft-managed keys.

Answer

Use Azure Private Link to restrict access to the storage account.

Answer

Enable Azure Defender for Storage.

Question 14

You have an Azure Data Lake Storage Gen2 account that contains a container named raw. The container has a folder hierarchy with millions of small files. You need to optimize read performance for an Azure Databricks job that reads these files. You also need to minimize storage costs. What should you do?

Accepted Answer

Compact the small files into larger files using a tool like Azure Data Factory or Databricks, and store them in a partitioned folder structure.. The presence of millions of small files creates significant overhead in listing, opening, and reading each file, which degrades performance and increases costs. Compacting these files into larger, partitioned files reduces the number of files and the amount of data scanned, improving read performance and lowering storage costs. The other options either do not address the root cause or increase costs.

Answer

Move the files to a premium storage tier to reduce latency.

Answer

Increase the number of partitions in the Databricks cluster to parallelize reads across more nodes.

Answer

Enable hierarchical namespace and use Azure Blob Storage APIs to read the files.

Question 15

A healthcare company stores patient records in Azure Data Lake Storage Gen2. The data must be encrypted at rest using customer-managed keys (CMK) stored in Azure Key Vault. The company also requires that the encryption keys are automatically rotated every 90 days. You need to configure the storage account to meet these requirements. What should you do?

Accepted Answer

Configure the storage account to use customer-managed keys from Azure Key Vault and set a key rotation policy in Azure Key Vault.. To use customer-managed keys with Azure Data Lake Storage Gen2, you configure the storage account to reference a key in Azure Key Vault. Azure Key Vault supports automatic key rotation policies, which can rotate the key every 90 days. The storage account automatically uses the new key version. This meets both encryption at rest with CMK and automatic rotation requirements.

Answer

Use Azure Disk Encryption with customer-managed keys and schedule a runbook to rotate the keys every 90 days.

Answer

Enable Azure Storage Service Encryption with Microsoft-managed keys and configure a key rotation policy in Azure Key Vault.

Answer

Enable infrastructure encryption on the storage account and use Microsoft-managed keys for automatic rotation.