DP-203 Practice Questions

Question 1

You are working with Azure Synapse Analytics serverless SQL pool. You need to query a set of Parquet files located in ADLS Gen2. The files have nested columns (structs and arrays). Which function should you use to flatten the nested data?

Accepted Answer

OPENJSON. OPENJSON is the correct function because it parses JSON text and returns objects and properties from JSON input as rows and columns. In Azure Synapse serverless SQL pool, when Parquet files contain nested columns (structs and arrays), they are exposed as JSON strings, and OPENJSON can flatten these nested structures into a relational format. This allows you to query complex nested data directly without needing to pre-process the files.

Answer

PIVOT

Answer

UNNEST

Answer

CROSS APPLY

Question 2

You manage an Azure Synapse Analytics dedicated SQL pool that contains a large fact table 'Orders' with 500 million rows. The table is hash-distributed on 'OrderDate' and uses a clustered columnstore index. Query performance has degraded over time. You check the system DMVs and find that the columnstore segments have poor quality, with many deleted rows and compressed rowgroups below 1 million rows. You need to improve query performance without blocking writes to the table. What should you do?

Accepted Answer

Run ALTER INDEX REORGANIZE with COMPRESS_ALL_ROW_GROUPS = ON.. ALTER INDEX REORGANIZE with COMPRESS_ALL_ROW_GROUPS = ON is an online operation that compresses rowgroups with deleted rows without blocking writes. This directly addresses the poor columnstore segment quality. Option B is wrong because dropping and recreating the clustered columnstore index is an offline operation that blocks writes. Option C is wrong because changing the distribution key would require recreating the table, which is offline and does not specifically fix columnstore segment quality. Option D is wrong because ALTER INDEX REBUILD is also an offline operation that blocks writes.

Answer

Drop and recreate the clustered columnstore index.

Answer

Re-cluster the table using a different distribution key.

Answer

Run ALTER INDEX REBUILD on the clustered columnstore index.

Question 3

You are building a data processing solution in Azure Synapse Analytics. The solution requires creating a table that stores sales transactions. The table will be used for both point-of-sale lookups and large aggregation queries. The data is not updated frequently. Which table distribution should you recommend?

Accepted Answer

ROUND_ROBIN. ROUND_ROBIN is the correct choice because the table is used for both point-of-sale lookups (single-row queries) and large aggregation queries, and the data is not updated frequently. ROUND_ROBIN distributes data evenly across all distributions without a hash key, which provides the best overall performance for mixed workloads where no single distribution key optimizes both lookup and aggregation patterns. It avoids data skew and allows parallel processing for aggregations while still supporting efficient lookups when combined with appropriate indexes.

Answer

HASH on TransactionID

Answer

HASH on SalesDate

Answer

REPLICATE

Question 4

You have an Azure Data Lake Storage Gen2 account that stores large volumes of parquet files. A reporting application frequently queries a specific subset of data filtered by a 'region' column. To minimize query latency and cost, which optimization should you implement?

Accepted Answer

Partition the data by region in the folder structure.. Partitioning the data by region in the folder structure (e.g., /region=NorthAmerica/...) enables Azure Data Lake Storage Gen2 and query engines like Azure Synapse or PolyBase to perform partition pruning. This skips scanning irrelevant files entirely, reducing I/O and query latency while lowering cost by minimizing data processed.

Answer

Create a clustered index on the region column.

Answer

Compress the parquet files using gzip.

Answer

Enable hierarchical namespace on the storage account.

Question 5

You need to execute a T-SQL stored procedure in Azure Synapse dedicated SQL pool that performs a large data load. The stored procedure takes approximately 45 minutes to run. You want to monitor the progress and see the statement text currently being executed. Which dynamic management view (DMV) should you query?

Accepted Answer

sys.dm_pdw_exec_requests (with status='running'). B is correct because `sys.dm_pdw_exec_requests` with the filter `WHERE status = 'running'` returns the currently executing requests in an Azure Synapse dedicated SQL pool, including the full statement text in the `command` column. This allows you to see the exact T-SQL being executed during the 45-minute data load, enabling progress monitoring.

Answer

sys.dm_workload_management_workload_groups_stats

Answer

sys.dm_pdw_waits

Answer

sys.dm_pdw_exec_requests

Question 6

You are reviewing a copy job configuration in Azure Data Factory that copies Parquet files from Azure Data Lake Storage Gen2 to Azure Synapse Analytics. The exhibit shows the job settings. If the source folder contains a file that is not in Parquet format (e.g., a CSV file), what will happen?

Accepted Answer

The copy job will skip the CSV file and continue copying other Parquet files.. When using Azure Data Factory's Copy Activity with a wildcard file path or a dataset that filters for Parquet files (e.g., *.parquet), the service evaluates the file pattern before attempting to read the file. If a CSV file is present in the same folder but does not match the Parquet filter, ADF simply ignores it and continues processing only the matching Parquet files. This behavior is by design to allow flexible file selection without causing failures.

Answer

The copy job will skip the CSV file and stop.

Answer

The copy job will fail with an error.

Answer

The copy job will attempt to read the CSV file as Parquet and may produce corrupt data.

Question 7

You are designing a data storage solution for a healthcare organization that stores patient records. The solution must comply with HIPAA and support point-in-time restore (PITR) for the last 35 days. The data is frequently accessed for reporting. Which Azure data service should you use?

Accepted Answer

Azure SQL Database. Azure SQL Database is the correct choice because it natively supports point-in-time restore (PITR) for up to 35 days (configurable from 7 to 35 days) and is a HIPAA-eligible service when configured with encryption, auditing, and network security. It provides transactional consistency required for patient records and supports frequent reporting workloads with features like columnstore indexes and read replicas.

Answer

Azure Cosmos DB

Answer

Azure Blob Storage with point-in-time restore

Answer

Azure Data Lake Storage Gen2

Question 8

A data engineer is designing a solution to store historical sales data for a retail company. The data is append-only and accessed infrequently for compliance reports. The solution must minimize storage costs while allowing retrieval within 24 hours. Which storage tier should be used for the data?

Accepted Answer

Cool tier. The Cool tier is the correct choice because it is optimized for data that is infrequently accessed and stored for at least 30 days, offering low storage costs with retrieval times in the range of seconds to hours, which meets the 24-hour retrieval requirement. The data is append-only and used for compliance, so the Cool tier balances cost and accessibility without the high retrieval costs or long rehydration delays of the Archive tier.

Answer

Hot tier

Answer

Archive tier

Answer

Premium tier

Question 9

Which TWO Azure services can be used to implement a data lake architecture for storing structured, semi-structured, and unstructured data?

Accepted Answer

Azure Blob Storage with hierarchical namespace. Azure Data Lake Storage Gen2 (ADLS Gen2) is built on top of Azure Blob Storage and adds a hierarchical namespace, enabling a true data lake architecture. It supports storing structured, semi-structured, and unstructured data at scale, with POSIX-like access control lists and directory-level operations. Azure Blob Storage with hierarchical namespace enabled is essentially the same service as ADLS Gen2, providing the same data lake capabilities.

Answer

Azure SQL Database

Answer

Azure Synapse Analytics

Answer

Azure Cosmos DB

Question 10

You are designing a delta lake architecture in Azure Synapse Analytics. Which TWO practices should you follow to ensure ACID transactions and data consistency?

Accepted Answer

Use the Delta Lake transaction log for all write operations. The Delta Lake transaction log is the core mechanism that enables ACID transactions. Every write operation (insert, update, delete, merge) is recorded as an atomic commit in the transaction log, ensuring that concurrent readers see a consistent snapshot and that partial writes are never visible. Without this log, Delta Lake cannot guarantee atomicity or isolation.

Answer

Disable schema evolution to prevent accidental schema changes

Answer

Run OPTIMIZE commands frequently to compact small files

Answer

Store data in CSV format to simplify schema enforcement

Question 11

Your company has an Azure Data Factory pipeline that ingests data from multiple sources into Azure Data Lake Storage Gen2. The pipeline uses a self-hosted integration runtime (IR) running on an on-premises Windows server. Recently, the pipeline started failing with 'Connection timed out' errors during peak hours. You suspect network congestion. You need to resolve this issue with minimal cost and without modifying the pipeline activities. What should you do?

Accepted Answer

Scale out the self-hosted IR by adding more nodes to the cluster.. Scaling out the self-hosted IR by adding more nodes distributes the load and reduces timeout issues. Option A is wrong because Azure ExpressRoute provides dedicated bandwidth but is costly and overkill for this scenario. Option B is wrong because increasing the polling interval does not fix network timeouts; it only changes how often the activity checks for data. Option D is wrong because migrating to Azure-SSIS IR is expensive and unnecessary for this pipeline.

Answer

Implement Azure ExpressRoute to provide dedicated bandwidth.

Answer

Increase the 'Polling Interval' setting in the copy activity.

Answer

Migrate the self-hosted IR to Azure-SSIS IR.

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. Materialized views precompute and store the results of complex aggregations, such as SUM, COUNT, AVG, or GROUP BY operations, in Azure Synapse dedicated SQL pool. When a query references the same aggregation pattern, the optimizer can automatically substitute the materialized view, significantly reducing compute and I/O overhead by avoiding full table scans and recomputation. This is a best practice for improving performance on repetitive analytical workloads.

Answer

Use the largest resource class for all queries

Answer

Use round-robin distribution for large fact tables

Question 13

You are designing a data storage solution for a retail company. The data includes transactional data that requires low-latency queries (under 10 milliseconds) and large historical data for analytics. The solution must minimize storage costs. Which approach should you recommend?

Accepted Answer

Use Azure Cosmos DB for transactional data and Azure Blob Storage for historical data. Azure Cosmos DB provides single-digit millisecond latency for transactional workloads, meeting the under-10ms requirement, while Azure Blob Storage offers low-cost storage for large historical data. This combination minimizes storage costs by using the most cost-effective service for each workload type.

Answer

Use Azure Data Lake Storage Gen2 for both transactional and historical data

Answer

Use Azure Cache for Redis for transactional data and Azure SQL Database for historical data

Answer

Use Azure SQL Database with Hyperscale tier for both transactional and historical data

Question 14

You are designing a batch processing solution using Azure Databricks. The data source is a large Parquet dataset stored in Azure Data Lake Storage Gen2 (ADLS Gen2). The processing requires joining two datasets: one with 10 billion rows and another with 1 million rows. The cluster uses Photon runtime. Which optimization should you apply to minimize shuffle?

Accepted Answer

Broadcast the smaller table (1 million rows) to all worker nodes.. Broadcasting the smaller table (1 million rows) to all worker nodes is the correct optimization because it eliminates the need for a full shuffle during the join. With Photon runtime, broadcast joins are highly efficient as they replicate the small table to each executor, allowing map-side joins that avoid costly data movement across the network. Given the 10:1 row ratio, the 1-million-row table is well within the default broadcast threshold (10 MB compressed, configurable via spark.sql.autoBroadcastJoinThreshold), making this the most effective shuffle-minimization technique.

Answer

Increase the cluster size to reduce shuffle overhead.

Answer

Create bucketed tables on the join key for both datasets.

Answer

Use Delta Lake and optimize file layout with OPTIMIZE command.

Question 15

You are troubleshooting a slow-running query in Azure Synapse Analytics dedicated SQL pool. The query joins a large fact table (hash-distributed on ProductID) with a small dimension table (replicated). Upon reviewing the query plan, you see a 'ShuffleMove' operation. What is the most likely cause of the slow performance?

Accepted Answer

The dimension table is not actually replicated due to its size exceeding the replication threshold. The 'ShuffleMove' operation in a Synapse dedicated SQL pool query plan indicates that data is being moved between distributions to complete the join. If the small dimension table is supposed to be replicated but exceeds the replication threshold (default 60 GB compressed), it will not be replicated and instead remains hash-distributed. This forces a shuffle of the large fact table's data to align with the dimension table's distribution, causing significant data movement and slow performance.

Answer

The query is running with a low resource class

Answer

Result set caching is enabled

Answer

Statistics are outdated on the dimension table

Microsoft Azure Data Engineer Associate DP-203 practice test

Three ways to study

Study DP-203 by domain

Study DP-203 by topic

Secure, monitor, and optimize data storage and data processing practice questions

Design and implement data storage practice questions

Develop data processing practice questions

DP-203 fundamentals practice questions

DP-203 scenario practice questions

DP-203 troubleshooting practice questions

Top DP-203 questions

Microsoft Azure Data Engineer Associate DP-203 practice questions

How to use these DP-203 questions

Quick answer