DP-203 Timed Practice Test 1 — 10 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?

10 minute time limit — choose an answer to begin.

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