DP-203 Practice Test 21 — 15 Questions

Question 1

You are designing a data lake for a manufacturing company that will store sensor readings in Parquet format. The data will be used by data scientists for batch training and by analysts for ad-hoc queries. Which Azure service should you use as the primary storage layer?

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

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct primary storage layer because it combines a hierarchical namespace with Azure Blob Storage's scalable object storage, providing POSIX-compliant access control and directory-level operations. This makes it ideal for storing Parquet files used by data scientists for batch training (via Spark or PolyBase) and by analysts for ad-hoc queries (via Synapse SQL or Azure Databricks), as it supports both high-throughput analytics and fine-grained security.

Answer

Azure Synapse SQL Pool

Answer

Azure Blob Storage

Answer

Azure SQL Database

Question 2

You are designing a data processing solution for a real-time dashboard. The source data is streaming from IoT devices at a rate of 10,000 events per second. Each event is under 1 KB. You need to store the raw data in a staging area before processing, and you must minimize storage costs. Which storage solution should you use?

Accepted Answer

Azure Blob Storage. Azure Blob Storage is cost-effective for large volumes of streaming data that will be processed later. Azure Data Lake Storage Gen2 is built on Blob Storage but adds hierarchical namespace, which is not required for simple staging. Azure SQL Database and Azure Cosmos DB are more expensive and suited for transactional or real-time query workloads.

Answer

Azure Data Lake Storage Gen2

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Question 3

A logistics company needs to store delivery tracking data that is updated frequently by multiple services. The solution must support transactions across multiple documents and provide real-time analytics. Which Azure service should you recommend?

Accepted Answer

Azure Cosmos DB with SQL API. Azure Cosmos DB with SQL API is the correct choice because it provides multi-document transaction support (ACID within a logical partition) and real-time analytics via its change feed and integrated analytical store. This meets the requirement for frequent updates from multiple services while enabling low-latency reads for analytics, unlike other Azure storage options that lack transactional guarantees across documents or real-time query capabilities.

Answer

Azure Table Storage

Answer

Azure Data Lake Storage Gen2

Answer

Azure Service Bus

Question 4

You run the above query on a table named 'visits' in a dedicated SQL pool. The table has 1 billion rows and is hash-distributed on user_id. The query takes a long time. What is the most likely reason?

Accepted Answer

COUNT(DISTINCT) operations are expensive because they require data movement across distributions.. In a dedicated SQL pool, COUNT(DISTINCT) is inherently expensive because it requires all distinct values to be gathered across distributions before counting. Since the table is hash-distributed on user_id, the distinct count on a different column (likely visit_date or another attribute) forces data shuffling across all distributions to ensure uniqueness, causing significant performance degradation.

Answer

The query uses a date filter which cannot be pushed down to the distribution.

Answer

The table is hash-distributed on user_id, but the query uses a different column for aggregation.

Answer

The table should use a replicated distribution instead of hash distribution.

Question 5

You have an Azure Data Factory pipeline that loads data from an on-premises SQL Server to an Azure Synapse Analytics dedicated SQL pool. The pipeline uses a staging Azure Blob Storage account. Recently, the pipeline has been failing with timeout errors. You need to ensure the pipeline completes successfully within the scheduled window. What should you do?

Accepted Answer

Increase the staging blob's copy timeout and enable parallel copy in the copy activity. The timeout errors occur because the default copy timeout for staging blob storage is insufficient for large data volumes or slow network conditions. Increasing the staging blob's copy timeout and enabling parallel copy in the copy activity directly addresses this by allowing more time for data transfer and leveraging multiple concurrent connections to improve throughput, ensuring the pipeline completes within the scheduled window.

Answer

Move the staging storage to Azure Data Lake Storage Gen2

Answer

Increase the dedicated SQL pool's data warehouse units (DWU)

Answer

Use PolyBase instead of staging for data loading

Question 6

You need to process a large dataset stored as CSV files in Azure Data Lake Storage Gen2 using Azure Databricks. The processing involves several transformations and aggregations. You want to minimize shuffle operations. Which approach should you use?

Accepted Answer

Use Delta Lake and apply Z-ordering on the columns used in filters and aggregations. Z-ordering in Delta Lake co-locates related data within files based on specified columns, which significantly reduces the amount of data scanned during filter and aggregation operations. By minimizing the data that needs to be read, Z-ordering inherently reduces shuffle operations because fewer partitions need to be exchanged across the cluster during transformations. This approach is specifically designed to optimize query performance on large datasets in Azure Databricks without increasing the number of shuffle stages.

Answer

Cache the data in memory after reading

Answer

Use bucketing with a fixed number of buckets

Answer

Partition the data by a high-cardinality column

Question 7

You are processing CSV files in Azure Synapse Analytics serverless SQL pool. The files contain newline characters within quoted fields, causing parsing errors. Which file format option should you specify to handle this correctly?

Accepted Answer

Use PARSER_VERSION = '2.0'. PARSER_VERSION = '2.0' in Azure Synapse serverless SQL pool supports quoted fields that contain embedded newline characters (CR/LF). The parser version 2.0 is designed to handle CSV files with complex quoting rules, including newlines within quoted fields, which the older parser version 1.0 cannot parse correctly.

Answer

Use ROWTERMINATOR = '0x0a'

Answer

Use FORMAT = 'CSV'

Answer

Use FIELDQUOTE = '"'

Question 8

You are designing a data processing solution for a financial services company. The solution must process sensitive customer data from multiple sources. You need to ensure that the data is encrypted at rest and in transit, and that access to the data is audited. Which combination of Azure services should you use?

Accepted Answer

Azure Data Lake Storage (encrypted at rest), Azure Synapse Analytics (TDE and SSL), and Microsoft Purview. Azure Data Lake Storage provides encryption at rest using Azure Storage Service Encryption (SSE) with 256-bit AES, and Azure Synapse Analytics supports Transparent Data Encryption (TDE) for at-rest encryption and SSL/TLS for in-transit encryption. Microsoft Purview enables data governance and auditing by capturing lineage, classification, and access activity logs, meeting the compliance requirements for sensitive financial data.

Answer

Azure Blob Storage (encrypted at rest), Azure HDInsight, and Azure Log Analytics

Answer

Azure SQL Database (TDE and SSL), Azure Analysis Services, and Microsoft Purview

Answer

Azure Data Lake Storage (encrypted at rest), Microsoft Fabric, and Azure Monitor

Question 9

A financial services company is migrating its data warehouse to Azure Synapse Analytics. They have a star schema with a 10-billion-row fact table and 50 dimension tables. Query performance is critical, and they need to minimize data movement during joins. Which distribution strategy should they use for the fact table?

Accepted Answer

Hash distribution on the most frequently joined dimension key. Hash distribution on the most frequently joined dimension key is correct because it co-locates matching rows from the fact and dimension tables on the same compute node, minimizing data movement during joins. For a 10-billion-row fact table, this distribution ensures that the most common join operation is performed locally without shuffling data across nodes, which is critical for query performance in Azure Synapse Analytics.

Answer

Replicated distribution

Answer

Partitioned distribution

Answer

Round-robin distribution

Question 10

You are reviewing a Mapping Data Flow in Azure Data Factory that copies data from a CSV file to an Azure Synapse table. The data flow uses 'allowSchemaDrift: true' and 'validateSchema: false'. After running the pipeline, you notice that the target table does not have the expected columns. The CSV file sometimes has extra columns. What is the most likely reason?

Accepted Answer

The sink mapping is explicitly defined and does not include auto-mapping for drifted columns.. When 'allowSchemaDrift' is enabled on the source, drifted columns are detected but will only be written to the sink if auto-mapping is used. If the sink mapping is explicitly defined (e.g., column-by-column mappings), it overrides auto-mapping and drifted columns are ignored. Since the target table is missing expected columns, the explicit mapping likely excludes the drifted columns.

Answer

Schema drift is not enabled on the source.

Answer

The sink dataset has a fixed schema that does not allow drift.

Answer

The source dataset has a fixed schema that does not include extra columns.

Question 11

Which TWO factors should you consider when choosing between Azure SQL Database and Azure Cosmos DB for a transactional workload that requires low-latency reads and writes globally?

Accepted Answer

Consistency models (strong, bounded staleness, session, eventual, consistent prefix).. Azure Cosmos DB offers multiple well-defined consistency models (strong, bounded staleness, session, consistent prefix, and eventual) that allow you to tune the trade-off between consistency and latency for globally distributed workloads. For a transactional workload requiring low-latency reads and writes globally, choosing the appropriate consistency model (e.g., session or eventual) can significantly reduce write latency by avoiding the overhead of synchronous replication required for strong consistency. This flexibility is a key factor when designing for global distribution with multi-region writes.

Answer

ACID transaction support across multiple documents.

Answer

Support for secondary indexes.

Answer

Ability to run stored procedures.

Question 12

You need to partition a large Azure SQL Database table by date to improve query performance and manageability. Which partitioning strategy should you use?

Accepted Answer

CREATE PARTITION FUNCTION myDateRange (datetime2) AS RANGE RIGHT FOR VALUES ('2023-01-01', '2023-02-01', ...). `RANGE RIGHT` ensures that each boundary value belongs to the right partition, which is the standard approach for date-based partitioning. This means values less than '2023-01-01' go into the first partition, values >= '2023-01-01' and < '2023-02-01' go into the second, and so on, aligning with typical date range queries and simplifying partition management (e.g., switching out old partitions).

Answer

CREATE PARTITION FUNCTION myDateRange (datetime2) AS RANGE LEFT FOR VALUES ('2023-01-01', '2023-02-01', ...)

Answer

Use Azure SQL Database automatic partitioning feature

Answer

Apply a clustered columnstore index with partitioning

Question 13

A company uses Azure Key Vault to store secrets for data pipelines. They need to rotate the secrets automatically every 90 days. What should they implement?

Accepted Answer

Configure Key Vault secret rotation with an expiration date of 90 days.. Azure Key Vault supports automatic secret rotation by configuring an expiration date and enabling rotation. Option A is incorrect because Azure Policy can enforce expiration but does not automatically rotate secrets. Option B is incorrect because RBAC roles manage access permissions, not secret rotation. Option C is incorrect because a manual process is not automatic and does not meet the requirement for automatic rotation every 90 days.

Answer

Use Azure Policy to enforce secret expiration.

Answer

Assign RBAC roles to a service principal to update the secret.

Answer

Create a manual process to update the secret in Key Vault.

Question 14

You are designing a data processing solution in Azure Databricks to transform streaming data from Azure Event Hubs. The data must be aggregated in 1-minute tumbling windows and written to Azure Synapse Analytics. Which Spark API should you use?

Accepted Answer

Structured Streaming. Structured Streaming is the correct choice because it provides native support for event-time-based aggregations, such as 1-minute tumbling windows, and integrates seamlessly with Azure Event Hubs as a streaming source and Azure Synapse Analytics as a streaming sink using the `foreachBatch` or `writeStream` API. It offers exactly-once semantics and automatic state management for windowed operations, which are essential for reliable streaming ETL.

Answer

RDD API

Answer

Spark Streaming (DStreams)

Answer

DataFrame API with batch processing

Question 15

You are building a real-time dashboard that displays sales data from an Azure SQL Database. The dashboard must refresh every 30 seconds with minimal latency. You need to choose the appropriate Azure service for data processing and visualization. Which service should you use?

Accepted Answer

Power BI with DirectQuery mode and configure automatic page refresh.. Power BI with DirectQuery mode is the correct choice because it allows real-time queries directly against Azure SQL Database, supporting automatic page refresh every 30 seconds with minimal latency. Azure Analysis Services requires data processing and is not designed for sub-minute refreshes. Azure Data Explorer is optimized for time-series data, not for direct connection to Azure SQL Database for real-time dashboards. Azure Synapse Serverless SQL pool is meant for querying data lakes, not for real-time visualization with frequent refreshes.

Answer

Azure Analysis Services with a tabular model and a scheduled refresh every 30 seconds.

Answer

Azure Data Explorer (ADX) with a continuous export to Power BI.

Answer

Azure Synapse Serverless SQL pool with Power BI import mode.