DP-203 Practice Test 48 — 15 Questions

Question 1

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 2

Your company has an Azure Synapse Analytics dedicated SQL pool. You need to implement a solution that automatically moves data between the 'PRIMARY' filegroup and a secondary filegroup based on data age, without manual intervention. Which feature should you use?

Accepted Answer

Partition switching. Partition switching in Azure Synapse Analytics dedicated SQL pool allows you to efficiently move data between filegroups by switching partitions between tables. By aligning partitions with data age, you can automatically transfer older data to a secondary filegroup without manual intervention, using a scheduled process like a stored procedure or Azure Data Factory.

Answer

Materialized views

Answer

Azure Data Lake Storage tiering

Answer

PolyBase

Question 3

You are designing a data storage solution for a financial services company. The solution must meet the following requirements: store transaction data for 7 years for regulatory compliance, support point-in-time restore (PITR) for the last 30 days, and minimize storage costs for historical data. Which THREE actions should you take?

Accepted Answer

Use Azure Blob Storage with cool access tier for data older than 30 days. Option B is correct because Azure Blob Storage's cool access tier is designed for infrequently accessed data with lower storage costs, making it ideal for historical transaction data older than 30 days. This tier provides cost-effective storage while still allowing retrieval when needed, aligning with the requirement to minimize storage costs for historical data.

Answer

Store historical data in Azure Data Lake Storage Gen2

Answer

Use Azure Table Storage for archival

Question 4

You are implementing a data lake using Azure Data Lake Storage Gen2. Which THREE actions should you take to secure the data at rest and in transit?

Accepted Answer

Enable Azure Storage Service Encryption (SSE) for data at rest. Azure Storage Service Encryption (SSE) automatically encrypts data at rest using 256-bit AES encryption, which is transparent to applications and meets compliance requirements. This is a fundamental security control for protecting data stored in Azure Data Lake Storage Gen2.

Answer

Enable TLS 1.0 for compatibility with legacy clients

Answer

Configure firewall rules to allow only trusted IPs

Question 5

Which TWO Azure services can be used to implement a polyglot persistence architecture for an e-commerce application that requires both a relational database for orders and a document database for product catalogs?

Accepted Answer

Azure SQL Database. Azure SQL Database is a relational database service that supports ACID transactions and structured querying, making it ideal for storing and managing e-commerce order data with strong consistency and referential integrity. Azure Cosmos DB is a multi-model NoSQL database that provides document database capabilities with flexible schemas and low-latency access, perfectly suited for product catalogs that require high read throughput and schema evolution. Together, they enable a polyglot persistence architecture by using the best storage model for each workload.

Answer

Azure Cache for Redis

Answer

Azure Table Storage

Answer

Azure Data Lake Storage Gen2

Question 6

You are designing a data storage solution for an Azure Data Lake Storage Gen2 account that will store sensitive customer data. The solution must enforce that all data is encrypted at rest using customer-managed keys (CMK) stored in Azure Key Vault. Additionally, you need to prevent data from being accessed by any Azure service except Azure Synapse Analytics. Which combination of configurations should you implement?

Accepted Answer

Enable Azure Storage encryption with customer-managed keys stored in Azure Key Vault and configure a firewall with a service endpoint for Azure Synapse Analytics. Option A is correct because it combines two essential controls: Azure Storage encryption with customer-managed keys (CMK) stored in Azure Key Vault ensures data is encrypted at rest using keys you control, and a firewall with a service endpoint for Azure Synapse Analytics restricts network access so that only traffic from Azure Synapse Analytics can reach the storage account. This meets both the encryption and access restriction requirements.

Answer

Enable Azure Storage encryption with customer-managed keys and use a shared access signature (SAS) token for Azure Synapse Analytics

Answer

Enable Azure Storage encryption with customer-managed keys and assign an Azure Policy denying public network access

Answer

Enable Azure Storage encryption with Microsoft-managed keys and configure a private endpoint

Question 7

You need to transform data in Azure Databricks using Apache Spark. The data is stored in Delta Lake format in Azure Data Lake Storage Gen2. Which method should you use to read the data into a Spark DataFrame?

Accepted Answer

spark.read.format('delta').load('abfss://container@storage.dfs.core.windows.net/path'). Option B is correct because the data is stored in Delta Lake format, which requires using the 'delta' format reader in Spark to properly read the transaction log and schema. The `spark.read.format('delta').load()` method is the standard way to read Delta tables, leveraging the Delta Lake protocol for ACID transactions and time travel capabilities.

Answer

spark.read.parquet('abfss://container@storage.dfs.core.windows.net/path')

Answer

spark.read.csv('abfss://container@storage.dfs.core.windows.net/path')

Answer

spark.read.json('abfss://container@storage.dfs.core.windows.net/path')

Question 8

You need to design a storage solution for a data lake that will be used by multiple teams for analytics. The solution must support fine-grained access control, versioning of files, and integration with Azure Purview for data cataloging. Which THREE features should you enable in Azure Data Lake Storage Gen2?

Accepted Answer

Integration with Microsoft Purview. Option A is correct because Microsoft Purview integration is required for data cataloging, enabling automated scanning, classification, and lineage tracking of data stored in Azure Data Lake Storage Gen2. This directly supports the requirement for data cataloging in the scenario.

Answer

Managed identity authentication

Answer

Soft delete

Question 9

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 10

You have an Azure Synapse Analytics dedicated SQL pool with a large fact table partitioned by date. As data grows, query performance on recent data degrades. You need to improve performance for queries filtering on the current month without affecting queries on older data. What should you do?

Accepted Answer

Implement partition switching to move older partitions to a different filegroup with slower storage. Partition switching allows you to efficiently move older partitions to a different filegroup (e.g., slower or compressed storage) without affecting queries on recent data. This reduces the active data volume in the fact table, improving scan performance for queries filtering on the current month. The operation is metadata-only and does not require data movement, so it does not impact ongoing queries on the remaining partitions.

Answer

Increase the service level objective (SLO) of the dedicated SQL pool

Answer

Change the table distribution to round-robin

Answer

Update statistics on the table for the current month partition

Question 11

Which THREE of the following are best practices for designing tables in a dedicated SQL pool in Azure Synapse Analytics?

Accepted Answer

Avoid data skew by choosing a good distribution key.. Option B is correct because a good distribution key minimizes data skew, ensuring that data is evenly distributed across all distributions. This prevents performance bottlenecks where some distributions handle a disproportionate amount of data or queries, which is critical for parallel processing in a dedicated SQL pool.

Answer

Avoid using clustered columnstore indexes on large tables.

Answer

Use round-robin distribution for all large fact tables.

Question 12

Which TWO Azure services can be used to perform real-time data processing on streaming data?

Accepted Answer

Azure Synapse Analytics (dedicated SQL pool). Azure Synapse Analytics (dedicated SQL pool) can ingest and process streaming data using PolyBase or the COPY INTO command to load data from Azure Stream Analytics or Event Hubs into staging tables, then perform real-time analytics via materialized views or incremental statistics. This enables near-real-time processing on high-velocity data by combining batch and streaming ingestion patterns.

Answer

Azure Data Factory

Answer

Azure Logic Apps

Answer

Azure Databricks

Question 13

You are using Azure Synapse Analytics serverless SQL pool to query data in Parquet files stored in Azure Data Lake Storage Gen2. The queries are slow when filtering on a date column. You need to improve query performance without changing the data structure. What should you do?

Accepted Answer

Partition the Parquet files into folders by date. D is correct because partitioning Parquet files into folders by date enables partition elimination in Azure Synapse serverless SQL pool. When queries filter on the date column, the engine can prune entire folders from the scan, reading only the relevant Parquet files. This reduces I/O and improves performance without altering the data structure or format.

Answer

Create materialized views in the serverless SQL pool

Answer

Increase the service level objective (SLO) of the serverless SQL pool

Answer

Convert the Parquet files to CSV format

Question 14

You are migrating a large on-premises SQL Server database to Azure Synapse Analytics. The database includes tables with up to 500 million rows and frequent updates. You need to minimize data movement during the migration while ensuring optimal query performance in the dedicated SQL pool. Which table design strategy should you use?

Accepted Answer

Use round-robin tables for staging tables and hash-distributed tables for large fact tables on a key column.. Option D is correct because it uses round-robin tables for staging to minimize data movement during the initial load, then hash-distributes large fact tables on a key column to optimize query performance by collocating rows with the same distribution key on the same compute node. This balances the need for fast ingestion with efficient parallel query execution in Azure Synapse Analytics dedicated SQL pools.

Answer

Use hash-distributed tables for all tables and clustered columnstore indexes.

Answer

Use replicated tables for all fact tables and hash-distributed tables for dimension tables.

Answer

Use round-robin tables for all tables to simplify the migration.

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