DP-203 Practice Test 31 — 15 Questions

Question 1

A financial services company needs to store transactional data in Azure Cosmos DB. The data is accessed by multiple applications using different partition keys. The company requires strong consistency for financial transactions and wants to minimize latency for reads and writes. Which consistency level should they choose?

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

Accepted Answer

Strong. Strong consistency in Azure Cosmos DB ensures that all reads see the most recent committed write, which is essential for financial transactions. Although it may introduce higher latency compared to weaker consistency levels, it provides the required correctness. The scenario prioritizes strong consistency over minimal latency, making Strong the appropriate choice.

Answer

Bounded staleness

Answer

Eventual

Answer

Session

Question 2

You are optimizing an Azure Synapse Analytics dedicated SQL pool. You need to reduce the amount of data read from storage during queries that filter on a date column. The fact table is partitioned by month on the date column. What should you do to improve query performance?

Accepted Answer

Ensure that queries include a predicate on the partitioning column to enable partition elimination.. Partition elimination is a technique where the query optimizer skips partitions that do not match the query's filter criteria. By ensuring queries filter on the partitioning column, you minimize the data scanned, reducing I/O and improving performance. This is especially effective for large fact tables partitioned by date.

Answer

Increase the resource class of the queries to allocate more memory.

Answer

Create a nonclustered index on the date column.

Answer

Change the distribution to round-robin to evenly spread data across distributions.

Question 3

You are monitoring an Azure Data Factory pipeline that copies data from an Azure SQL Database to an Azure Data Lake Storage Gen2 account. The pipeline runs hourly. You notice that the copy activity sometimes takes much longer than expected. You need to identify the cause of the performance variability. Which action should you take first?

Accepted Answer

Enable Azure Monitor diagnostic settings for the Data Factory and analyze the Copy activity duration metrics in Azure Monitor Logs.. To diagnose performance variability in an Azure Data Factory copy activity, you need detailed telemetry. Enabling diagnostic settings and analyzing metrics and logs in Azure Monitor provides insights into activity duration, data read/written, and errors. This data helps pinpoint whether the issue is due to source throttling, network latency, or other factors.

Answer

Configure a self-hosted integration runtime on a virtual machine with more memory and CPU.

Answer

Increase the Data Integration Units (DIUs) for the copy activity to the maximum allowed.

Answer

Enable Azure SQL Database Auditing and review the audit logs for long-running queries.

Question 4

You are designing a storage layer for an Azure Synapse Analytics dedicated SQL pool that ingests 4 TB of CSV files daily into a fact table. The files are landed in Azure Data Lake Storage Gen2 by an external ETL process. You need to load the data with the highest possible throughput while minimizing the load window. What should you do?

Accepted Answer

Use the COPY statement with a shared access signature (SAS) token and split the source files into multiple evenly sized files.. The COPY statement is the preferred high-throughput ingestion mechanism for dedicated SQL pools, especially when reading from ADLS Gen2 with SAS authentication. Splitting the source files into evenly sized chunks enables parallel reads across distributions, which is essential for loading 4 TB within a tight window. Combining COPY with proper file sizing directly addresses the throughput and load-window requirements.

Answer

Use the COPY statement with a shared access signature (SAS) token and set MAXDOP to a value equal to the number of files.

Answer

Use PolyBase with an external table and enable REJECT_TYPE VALUE with a threshold of 0.

Answer

Use Azure Data Factory with a copy activity that has a single pipeline and a single parallel copy thread.

Question 5

You are designing a storage solution for a financial services company. The solution must store large volumes of semi-structured JSON data in Azure Data Lake Storage Gen2. The data is accessed by Azure Databricks for batch processing and by Azure Synapse Analytics for interactive queries. The data must be organized for efficient partition elimination and must support atomic operations. You need to choose the appropriate file format and partitioning strategy. What should you do?

Accepted Answer

Store the data as Parquet files partitioned by date, and register the folder as an external table in both Azure Databricks and Azure Synapse Analytics.. Parquet is the optimal format for analytical workloads due to its columnar storage, compression, and predicate pushdown capabilities. Partitioning by date enables partition elimination, which is critical for efficient querying. Registering the folder as an external table in both Azure Databricks and Azure Synapse Analytics allows both services to access the same data seamlessly. Other formats like CSV and Avro are not as efficient for these requirements.

Answer

Store the data as CSV files partitioned by date, and register the folder as an external table in both Azure Databricks and Azure Synapse Analytics.

Answer

Store the data as Parquet files without partitioning, and register the folder as an external table in both Azure Databricks and Azure Synapse Analytics.

Answer

Store the data as Avro files partitioned by date, and register the folder as an external table in both Azure Databricks and Azure Synapse Analytics.

Question 6

You are designing a storage solution for a healthcare analytics platform. The platform ingests large volumes of structured patient records stored as Parquet files in Azure Data Lake Storage Gen2. Analysts query this data using Azure Synapse Analytics serverless SQL pools. To minimize query cost and improve performance, you need to choose an appropriate file organization and table type. What should you do?

Accepted Answer

Store data as Parquet files partitioned by date, and query them using an external table in a serverless SQL pool.. Storing data in Parquet format with date-based partitioning and querying via external tables in a serverless SQL pool minimizes data scanned and cost, while improving query performance. Parquet's columnar nature and compression reduce I/O, and partitioning enables partition elimination. This aligns with best practices for cost-effective analytics on large datasets in Azure Synapse Analytics.

Answer

Store data as CSV files partitioned by date, and query them using an external table in a serverless SQL pool.

Answer

Store data as JSON files and query them using OPENROWSET in a serverless SQL pool.

Answer

Store data as Parquet files in a dedicated SQL pool table with hash distribution on patient ID.

Question 7

You are monitoring an Azure Data Factory pipeline that copies data from an on-premises SQL Server to Azure Blob Storage. The pipeline occasionally fails with a timeout error. You need to identify the cause of the failures and receive proactive alerts when similar issues occur. What should you do?

Accepted Answer

Enable diagnostic settings to send logs to Azure Monitor and create an alert rule on the pipeline failure metric.. Diagnostic settings in Azure Data Factory can route detailed logs and metrics to Azure Monitor, enabling analysis of pipeline failures. Creating an alert rule on failure metrics ensures you are notified proactively. This combination provides both root-cause investigation and ongoing monitoring. Other options either mask failures, improve performance without visibility, or offer generic recommendations.

Answer

Configure a tumbling window trigger with retry policy.

Answer

Use Azure Advisor recommendations for Data Factory.

Answer

Increase the DIU count for the copy activity.

Question 8

You are designing a storage solution for a financial analytics platform. The data consists of large Parquet files stored in Azure Data Lake Storage Gen2. Analysts run complex queries that scan entire partitions, but only a subset of columns is needed for each query. You need to minimize the amount of data read from storage and improve query performance. What should you do?

Accepted Answer

Use Parquet format with column pruning and predicate pushdown enabled in the query engine.. Parquet is a columnar storage format that enables column pruning, so only the columns needed by a query are read from storage. This significantly reduces I/O and improves performance for analytical queries that access a subset of columns. Predicate pushdown further optimizes by filtering data at the source. Other formats like Avro or JSON are row-based and cannot provide these benefits.

Answer

Enable Azure Data Lake Storage Gen2 hierarchical namespace and organize files into folders by date.

Answer

Store the data in a row-based format such as Avro to enable faster full-row retrieval.

Answer

Convert the data to JSON and compress it with GZip to reduce storage size.

Question 9

You are designing a data storage solution in Azure Synapse Analytics. You need to store large volumes of semi-structured data in a dedicated SQL pool. The data will be used for analytical queries that often filter on a date column and join on a customer ID column. You want to optimize query performance. Which two actions should you perform? (Choose two.)

Accepted Answer

Use hash distribution on the customer ID column for large tables that are frequently joined.. Hash-distributing large tables on the customer ID column co-locates rows for joins, reducing data movement. Partitioning large fact tables on the date column enables partition elimination for date filters. Together, these actions optimize the most common query patterns. Other options either increase data movement or are not suitable for large-scale analytical workloads.

Answer

Use round-robin distribution for all tables to ensure even data spread.

Answer

Use replicated distribution for all dimension tables regardless of size.

Answer

Store the semi-structured data as JSON in a single column and use JSON functions for queries.

Question 10

A healthcare company stores patient records in Azure Data Lake Storage Gen2. The data must be organized for efficient querying by a Synapse Analytics serverless SQL pool. The data is currently stored as many small CSV files in a flat directory. You need to improve query performance and reduce the cost of scanning unnecessary data. What should you do?

Accepted Answer

Convert the data to Parquet format and partition it by admission date.. Converting to Parquet, a columnar format, enables column pruning and better compression, reducing the amount of data scanned. Partitioning by admission date allows the serverless SQL pool to prune partitions based on query filters. Together, these changes optimize query performance and lower cost.

Answer

Combine all CSV files into one large CSV file.

Answer

Create an external table in the serverless SQL pool that points to the CSV files.

Answer

Move the data to Azure Blob Storage and enable hierarchical namespace.

Question 11

You are designing a storage solution for a financial analytics platform that ingests CSV files into Azure Data Lake Storage Gen2. Analysts run complex T-SQL queries against the data using Azure Synapse Analytics serverless SQL pool. You need to minimize query cost and improve performance. What should you do?

Accepted Answer

Convert the CSV files to Parquet format and partition the data by date.. Serverless SQL pool charges based on data processed. Converting CSV to Parquet, a columnar format, allows queries to read only needed columns and push down filters, reducing data scanned. Partitioning by date further minimizes data read when queries filter on date. Together, these changes significantly lower cost and improve performance for analytical queries over file data in Data Lake Storage Gen2.

Answer

Store the CSV files in a single folder and create a view that selects all columns.

Answer

Use Azure Blob Storage instead of Azure Data Lake Storage Gen2.

Answer

Enable Azure Synapse Link for Azure Cosmos DB.

Question 12

A company uses Azure Synapse Analytics dedicated SQL pool to store sales data. The fact table is partitioned by date and distributed by product ID. Queries often join the fact table with a small dimension table on product ID. You notice that these joins cause significant data movement. You need to minimize data movement for these joins. What should you do?

Accepted Answer

Replicate the dimension table and distribute the fact table by product ID.. Replicating a small dimension table and hash-distributing the large fact table on the join key eliminates data movement because each compute node has a local copy of the dimension and only its subset of fact rows. This is a standard technique in Azure Synapse Analytics dedicated SQL pool to optimize join performance. Other options either increase data movement or do not address the distribution mismatch.

Answer

Change the distribution of the fact table to round-robin.

Answer

Partition the dimension table by product ID and use hash distribution on the fact table.

Answer

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

Question 13

You manage an Azure Synapse Analytics dedicated SQL pool that stores a 4 TB fact table named FactSales. The table is currently distributed using ROUND_ROBIN and has a clustered columnstore index. Most analytical queries join FactSales to a much smaller dimension table DimProduct on ProductKey and then filter by DateKey. You need to redesign the physical storage to minimize data movement during these joins and improve query performance. What should you do?

Accepted Answer

Change the distribution of FactSales to HASH(ProductKey) and ensure DimProduct is replicated.. For large fact tables in a dedicated SQL pool, hash distribution on the most frequently joined column minimizes data movement during joins. Replicating the smaller dimension table ensures its rows are present on every compute node, so the join can be performed locally. This design directly addresses the scenario's need to reduce data movement and improve query performance.

Answer

Keep ROUND_ROBIN distribution and add a nonclustered index on ProductKey in FactSales.

Answer

Change the distribution of FactSales to HASH(DateKey) and create a partition on ProductKey.

Answer

Recreate FactSales as a replicated table and create a hash distribution on DimProduct using ProductKey.

Question 14

You are designing a storage layer for a fraud detection system. The system writes millions of small JSON records per hour to Azure Data Lake Storage Gen2 and must support both batch analytics and interactive queries from Azure Databricks. You need to choose a storage format and layout that minimizes query latency for selective filters on customer ID while keeping storage costs predictable. What should you do?

Accepted Answer

Store the data as Delta Lake tables with Z-ORDER clustering on customer ID and optimize file sizes.. Delta Lake with Z-ORDER clustering on customer ID enables data skipping by storing min/max statistics per file and colocating similar values. This directly reduces the data read for selective filters. OPTIMIZE compacts small files, which is critical when millions of small records arrive hourly, improving both batch and interactive query performance while keeping storage costs controlled.

Answer

Store the data as Parquet files partitioned by customer ID and use partition pruning in Databricks.

Answer

Store the data as Avro files and use schema evolution to handle new fields.

Answer

Store the data as uncompressed JSON files and rely on Databricks caching to improve performance.

Question 15

You are designing a data storage solution in Azure Synapse Analytics. You need to store large fact tables that are frequently joined with dimension tables on a common column. The solution must minimize data movement during query execution and support high-concurrency queries. Which two actions should you take? (Choose two.)

Accepted Answer

Use hash distribution on the join column for the fact table.. Hash distributing the fact table on the join column and replicating the dimension tables minimizes data movement by colocating matching rows and eliminating shuffling of dimension data. These actions directly address the need for efficient joins and high concurrency in Azure Synapse Analytics dedicated SQL pools.

Answer

Use round-robin distribution for the fact table.

Answer

Use a columnstore index on the fact table.

Answer

Use hash distribution on a column with high cardinality for the fact table.