DP-203 Practice Test 14 — 15 Questions

Question 1

Your company uses Azure Cosmos DB for NoSQL to store user profiles. The application frequently reads profiles by user ID (the partition key). Occasionally, the application needs to query by email address, which is not part of the partition key. What should you do to optimize the occasional queries by email?

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

Accepted Answer

Create a secondary (composite) index on the email field.. Creating a secondary index on the email field allows Azure Cosmos DB for NoSQL to efficiently serve queries filtering by email without scanning all partitions. Since email is not the partition key, a secondary index (specifically a composite index if needed for multi-field queries, or a single-field index) enables index-based lookup across all physical partitions, optimizing the occasional query without redesigning the data model.

Answer

Change the partition key to the email field.

Answer

Denormalize the data by storing a copy of the email in the partition key.

Answer

Use the Azure Cosmos DB change feed to maintain a separate container keyed by email.

Question 2

You have an Azure Data Lake Storage Gen2 account that stores log files. You need to implement a data retention policy so that logs older than 90 days are automatically deleted. What should you use?

Accepted Answer

Lifecycle management policy. A lifecycle management policy can automatically delete blobs based on age. Option A is wrong because Azure Policy enforces compliance but does not delete. Option C is wrong because Azure Blob Storage inventory provides reports but does not delete. Option D is wrong because Azure Purview scopes metadata and data discovery, not lifecycle management.

Answer

Azure Policy

Answer

Azure Blob Storage inventory

Answer

Microsoft Purview

Question 3

You are examining a T-SQL script that creates an external table in Azure Synapse serverless SQL pool. The query SELECT * FROM dbo.Sales returns zero rows, but the folder /year=2024/ in ADLS Gen2 contains Parquet files. What is the most likely cause?

Accepted Answer

The credential used to access ADLS Gen2 does not have sufficient permissions.. The most common reason for SELECT * FROM an external table returning zero rows despite data existing in the underlying ADLS Gen2 folder is that the serverless SQL pool lacks the necessary permissions to read the Parquet files. The credential used in the external data source must have at least 'Storage Blob Data Reader' role on the storage account or the container, and the identity (e.g., SAS token, service principal, or managed identity) must be correctly configured. Without this, the query executes successfully but returns no rows because the pool cannot access the data.

Answer

The serverless SQL pool does not support reading Parquet files.

Answer

The external table definition is missing the SCHEMA_NAME parameter.

Answer

The DATA_COMPRESSION setting is incompatible with Parquet files.

Question 4

You are designing an ETL process in Azure Data Factory. You need to transform data using Mapping Data Flows. Which THREE of the following transformations are available in Mapping Data Flows?

Accepted Answer

Pivot. In Azure Data Factory Mapping Data Flows, the Pivot transformation (A) is a built-in transformation that reshapes data by turning unique row values into columns, which is why it is correct. The Derived Column transformation (B) is also available and is used to create new columns or modify existing ones using expression builder logic, making it correct. The Aggregate transformation (D) is likewise a native Mapping Data Flows transformation that performs group-by operations and aggregations such as SUM, AVG, COUNT, and MIN/MAX, so it is correct. Union All (C) is not a Mapping Data Flows transformation; combining multiple streams is done with the Union transformation, which behaves like a union (not specifically 'Union All' as a named transformation). Merge Join (E) is not a Mapping Data Flows transformation either; joins are performed with the Join transformation, and the term 'Merge Join' refers to a SQL Server/SSIS-style operator rather than an ADF Mapping Data Flows transformation.

Answer

Union All

Answer

Merge Join

Question 5

A data engineer monitors an Azure Stream Analytics job that processes real-time data. The job is falling behind, and the SU utilization is at 100%. Which action should be taken to improve performance?

Accepted Answer

Increase the number of Streaming Units (SU).. When SU utilization reaches 100%, the job is fully saturated and cannot process incoming data fast enough. Increasing the number of Streaming Units (SU) allocates more compute resources (CPU and memory) to the job, allowing it to handle higher throughput and reduce backlog. This is the direct and recommended action for resolving performance bottlenecks caused by insufficient SU capacity.

Answer

Reduce the number of Streaming Units.

Answer

Change the query compatibility level to 1.0.

Answer

Deploy a second Stream Analytics job and split the input.

Question 6

A manufacturing company uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled and Azure Databricks for analytics. The security team requires that all data stored in the 'raw' container be encrypted at rest using customer-managed keys. The data is ingested via Azure Data Factory. What should the data engineer configure to meet the requirement?

Accepted Answer

Configure the storage account to use Azure Key Vault for customer-managed key encryption.. Azure Data Lake Storage Gen2 with hierarchical namespace supports encryption at rest using customer-managed keys (CMK) via Azure Key Vault. To meet the security requirement, the data engineer must configure the storage account's encryption settings to use a key from Azure Key Vault, which allows the organization to control and rotate the encryption keys independently of Azure.

Answer

Assign an Azure Policy that requires encryption at rest.

Answer

Enable Azure Information Protection on the storage account.

Answer

Enable the 'require secure transfer' setting on the storage account.

Question 7

You are building a data pipeline that uses Azure Data Factory to copy data from a REST API to Azure Blob Storage. The REST API returns JSON data in pages of 1000 records each. The total number of records is 50,000. Which activity or feature should you use to loop through the pages?

Accepted Answer

Use a Copy activity with pagination rules enabled in the source.. Azure Data Factory's Copy activity supports pagination rules that automatically handle paging through REST API responses. By configuring pagination rules in the source dataset, the Copy activity can iterate through pages until all data is copied, without needing explicit looping activities. This is the most efficient and recommended approach.

Answer

Use a ForEach activity to iterate over a fixed number of pages.

Answer

Use a Lookup activity to retrieve the total number of pages and then use a ForEach.

Answer

Use an Until activity to loop until the API returns no more pages.

Question 8

You are designing a data processing solution using Azure Databricks with Delta Lake. You need to ensure ACID transactions and schema enforcement. Which feature should you enable?

Accepted Answer

Delta Lake format. Delta Lake is the correct choice because it provides ACID transactions (atomicity, consistency, isolation, durability) and schema enforcement (schema-on-write) on top of cloud storage like Azure Data Lake Storage. These features are inherent to the Delta Lake format, which uses a transaction log to track changes and enforce data integrity, making it ideal for reliable data processing in Azure Databricks.

Answer

Auto Loader

Answer

Photon engine

Answer

Unity Catalog

Question 9

You are designing a near-real-time analytics pipeline for a retail company. Transaction data is generated in Azure SQL Database and must be replicated to Azure Synapse Analytics (dedicated SQL pool) with less than 5 minutes latency. The source table has 50 million rows and 200 columns, but only 30 columns are needed for analytics. Which approach should you recommend?

Accepted Answer

Enable Change Data Capture (CDC) on the source table and use Azure Data Factory with a 1-minute tumbling window to copy changes into Synapse.. Azure Data Factory (ADF) with Change Data Capture (CDC) on the source SQL database can incrementally copy only changed rows (inserts, updates, deletes) into Azure Synapse Analytics using a 1-minute tumbling window, meeting the sub-5-minute latency requirement while minimizing data volume. This approach efficiently handles 50 million rows by transferring only the 30 needed columns, avoiding full table scans and reducing network load.

Answer

Use Azure SQL Database Change Tracking and push changes to Azure Event Hubs, then use Azure Stream Analytics to write to Synapse.

Answer

Use Azure Synapse PolyBase to directly query the source SQL database every 5 minutes.

Answer

Schedule a full copy of the entire table every 5 minutes using Azure Data Factory.

Question 10

Which TWO of the following are supported sources for Azure Data Factory Copy activity? (Choose two.)

Accepted Answer

Amazon S3. Amazon S3 is correct (C) because Azure Data Factory provides a native Amazon S3 connector that the Copy activity can use as a source, reading objects via the S3 API with linked-service credentials. Azure Blob Storage is correct (D) because it is one of ADF's core supported stores, and the Copy activity can read blobs through the Azure Blob Storage connector. Power BI Dataset (A) is not a Copy activity source; ADF interacts with Power BI only for certain dataset-based scenarios, not as a copy source. Azure DevOps (B) is not a data store connector for Copy activity sources. Azure Analysis Services (E) is an analytical model, not a supported Copy activity source store.

Answer

Power BI Dataset

Answer

Azure DevOps

Answer

Azure Analysis Services

Question 11

Your team is developing a data processing solution using Azure Databricks. The data is stored in Delta Lake format in Azure Data Lake Storage Gen2. You need to ensure that when multiple jobs concurrently write to the same Delta table, the operations are atomic and consistent. Which Delta Lake feature should you use?

Accepted Answer

Rely on Delta Lake's built-in ACID transactions.. Delta Lake provides built-in ACID (Atomicity, Consistency, Isolation, Durability) transactions that guarantee atomic and consistent concurrent writes. When multiple jobs write to the same Delta table, Delta Lake uses a transaction log (stored as JSON files in the `_delta_log` directory) to serialize writes, ensuring that each write is either fully committed or rolled back, preventing partial updates or data corruption.

Answer

Enable Optimized Write on the Delta table.

Answer

Enable Auto Optimize on the Delta table.

Answer

Use Dynamic Partition Pruning in your Spark jobs.

Question 12

Which THREE are best practices for optimizing query performance in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Use materialized views for complex aggregations. Option A is correct because materialized views in a dedicated SQL pool precompute and persist the results of complex aggregations (such as SUM, COUNT, AVG over large fact tables), letting the optimizer rewrite queries to read the smaller materialized result instead of rescanning and re-aggregating the base tables, which dramatically reduces I/O and CPU. Option C is correct because clustered columnstore indexes are the default and recommended storage format for dedicated SQL pools, providing high compression and batch-mode vectorized execution that speeds up large-scale analytical scans and aggregations on fact tables. Option D is correct because hash distribution on columns frequently used in JOINs co-locates matching rows on the same distribution, enabling collocated joins that avoid costly data movement (shuffle) across the 60 distributions. Option B is not a best practice because assigning the largest resource class to every query consumes excessive memory and concurrency slots, reducing overall workload throughput; resource classes should be sized to each query's needs. Option E is not a best practice because round-robin distribution is suited to staging or temporary tables, whereas large fact tables benefit from hash distribution on a join/filter column to minimize data movement during queries.

Answer

Use the largest resource class for all queries

Answer

Use round-robin distribution for large fact tables

Question 13

A data engineer needs to store CSV files containing customer data in Azure Blob Storage. The files must be encrypted at rest using a customer-managed key stored in Azure Key Vault. What should they configure?

Accepted Answer

Azure Storage Service Encryption (SSE) with customer-managed keys. Azure Storage Service Encryption (SSE) for Blob Storage encrypts data at rest automatically. By choosing customer-managed keys (CMK) stored in Azure Key Vault, the customer retains control over the encryption keys, meeting the requirement for customer-managed key encryption at rest. SSE with CMK is the correct service for encrypting blobs with a key the customer manages.

Answer

Azure Disk Encryption

Answer

Azure Storage Firewall

Answer

Azure Information Protection

Question 14

You are optimizing a data pipeline in Azure Synapse Analytics that loads data from a CSV file in ADLS Gen2 into a dedicated SQL pool using PolyBase. The load is slow and you need to improve performance. Which action would be MOST effective?

Accepted Answer

Combine the CSV files into fewer, larger files before loading.. Combining many small CSV files into fewer, larger files reduces the number of file open/close operations and minimizes the overhead of PolyBase's external file enumeration and parallel split logic. PolyBase performs best when each file is at least 256 MB, as it can then assign a full file to each distribution, avoiding the overhead of splitting tiny files across multiple threads.

Answer

Increase the service level (DWU) of the dedicated SQL pool.

Answer

Change the file format from CSV to Avro.

Answer

Use Azure Data Factory to stage the data in Azure Blob Storage before loading.

Question 15

Which THREE of the following are best practices for optimizing performance of Delta Lake tables in Azure Synapse Analytics? (Choose three.)

Accepted Answer

Run the OPTIMIZE command to compact small files and improve read performance.. Option A is correct because the OPTIMIZE command compacts many small Parquet files into fewer, larger files, reducing per-file overhead and metadata work so that Delta Lake scans and reads run faster. Option C is correct because partitioning on columns commonly used in WHERE clauses lets the engine perform partition pruning, skipping entire directories of data that cannot match the filter and cutting I/O. Option E is correct because Z-ordering (for example, OPTIMIZE ... ZORDER BY (col)) co-locates related data within files, so data-skipping statistics let queries with frequent filter predicates read far fewer rows. Option B is not a performance optimization: VACUUM deletes unreferenced old files to reclaim storage and control cost, and running it too aggressively can break time travel and concurrent readers. Option D is wrong because partitioning on high-cardinality columns such as UserID creates a huge number of tiny partitions and files, which increases metadata overhead and degrades performance rather than maximizing parallelism.

Answer

Periodically run VACUUM to remove old versions of files that are no longer needed.

Answer

Partition on high-cardinality columns like UserID to maximize parallelism.