DP-203 Design and implement data storage Practice Test 8 — 15 Questions

Question 1

You are designing a data lake on Azure Data Lake Storage Gen2. The data includes customer PII that must be encrypted at rest using customer-managed keys. Which feature should you enable?

Accepted Answer

Configure Azure Storage encryption with customer-managed keys in Azure Key Vault. Option B is correct because Azure Storage encryption with customer-managed keys (CMK) in Azure Key Vault allows you to control the encryption keys used to encrypt data at rest in Azure Data Lake Storage Gen2. This meets the requirement for encrypting PII with customer-managed keys, as CMK provides an extra layer of security by letting you manage key rotation, revocation, and access policies.

Answer

Enable double encryption for Azure Storage

Answer

Enable infrastructure encryption

Answer

Use Azure Customer Lockbox for access control

Question 2

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 3

You are designing a data storage solution for real-time streaming data from IoT devices. The data must be stored in its original format for immediate processing and later transformed for analytics. Which Azure service should you use for raw data ingestion?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is a fully managed, real-time data ingestion service optimized for high-throughput streaming data from IoT devices. It can receive millions of events per second, store them in a partitioned, ordered log for immediate processing, and retain them for up to 7 days (or longer with Event Hubs Capture) for later transformation and analytics. This makes it the correct choice for raw data ingestion before any transformation occurs.

Answer

Azure Data Lake Storage Gen2

Answer

Azure Stream Analytics

Answer

Azure Data Factory

Question 4

Your team uses Azure Databricks to process data stored in Azure Data Lake Storage Gen2. You need to ensure that only authorized users can access the data and that access is audited. What should you implement?

Accepted Answer

Use Azure RBAC with Microsoft Entra ID and enable diagnostic settings for Azure Storage. Azure RBAC with Microsoft Entra ID (formerly Azure AD) provides fine-grained access control to Azure Data Lake Storage Gen2, ensuring only authorized users can access data. Enabling diagnostic settings for Azure Storage captures read, write, and delete operations, which are sent to a Log Analytics workspace, storage account, or event hub for auditing. This combination meets both the authorization and auditing requirements specified in the question.

Answer

Use Azure managed identities to access the storage account

Answer

Use shared access signatures (SAS) with stored access policies

Answer

Configure the storage account firewall to allow only Azure Databricks IP addresses

Question 5

You have an Azure Synapse Analytics dedicated SQL pool with a table that uses hash distribution on CustomerID. You notice that queries joining this table with another table on OrderDate are slow. What is the most likely cause?

Accepted Answer

The join columns are not aligned; data must be shuffled across distributions. D is correct because in Azure Synapse Analytics dedicated SQL pools, hash distribution distributes rows across distributions based on a hash of the distribution key (CustomerID). When joining on OrderDate, which is not the distribution key, the join columns are not aligned across distributions. This forces data movement (shuffling) where rows from one or both tables must be redistributed to match the join key, causing significant performance degradation.

Answer

The table is not partitioned by OrderDate

Answer

Statistics on the join columns are outdated

Answer

The table should use round-robin distribution instead

Question 6

You need to store JSON files from an external partner in Azure Blob Storage. The files contain sensitive financial data. Which access method provides the highest security while allowing the partner to upload files?

Accepted Answer

Generate a shared access signature (SAS) with write-only permission and expiry time. Option C is correct because a Shared Access Signature (SAS) with write-only permission and an expiry time provides delegated, time-limited access to Azure Blob Storage without exposing the storage account key. This ensures the partner can upload JSON files but cannot read, modify, or list existing blobs, and access automatically revokes after the expiry, meeting the highest security requirement for sensitive financial data.

Answer

Share the storage account access key with the partner

Answer

Configure a firewall to allow only the partner's IP address

Answer

Enable anonymous public access to a container

Question 7

Your company uses Azure Data Lake Storage Gen2 for a data lake. You need to implement a folder structure that separates data by sensitivity level. Which access control method should you use?

Accepted Answer

Use Azure RBAC at the resource group level and ACLs on directories. Option C is correct because Azure Data Lake Storage Gen2 supports both Azure RBAC at the resource group level for coarse-grained control and POSIX-like ACLs on directories for fine-grained, sensitivity-based access. This combination allows you to assign read/write/execute permissions per directory, enabling a folder structure that separates data by sensitivity level without compromising security.

Answer

Use a storage account firewall and virtual network service endpoints

Answer

Use storage account keys for all access

Answer

Use shared access signatures (SAS) for each folder

Question 8

You are troubleshooting slow COPY INTO performance in Azure Synapse Analytics dedicated SQL pool when loading Parquet files from Azure Data Lake Storage Gen2. The files are 1 GB each. What should you do to improve performance?

Accepted Answer

Increase the number of files to match the number of distributions. Option C is correct because COPY INTO in Azure Synapse dedicated SQL pool distributes data across 60 distributions. To maximize parallelism, the number of input files should match or exceed the number of distributions. With 1 GB files, you have too few files to fully utilize all distributions, causing some distributions to remain idle. Increasing the number of files to at least 60 ensures each distribution gets work, improving throughput.

Answer

Reduce the file size to 100 MB to increase parallelism

Answer

Use PolyBase instead of COPY INTO

Answer

Disable parallel processing in the COPY command

Question 9

Which TWO factors should you consider when choosing between Azure SQL Database and Azure SQL Managed Instance for migrating a legacy application? (Choose two.)

Accepted Answer

Need for SQL Server Agent jobs. Option C is correct because SQL Managed Instance includes full support for SQL Server Agent, which is essential for legacy applications that rely on scheduled jobs, alerts, or automation. Azure SQL Database does not support SQL Server Agent, making Managed Instance the appropriate choice when this feature is required.

Answer

Support for active geo-replication

Answer

Authentication using Microsoft Entra ID

Answer

Integration with Azure VNet

Question 10

Which THREE considerations are important when designing a table distribution strategy for an Azure Synapse Analytics dedicated SQL pool? (Choose three.)

Accepted Answer

Align distribution keys on tables that are frequently joined together. Option A is correct because aligning distribution keys on tables that are frequently joined together ensures that the join columns are hash-distributed on the same key, enabling collocated joins. This avoids data movement across distributions during query execution, which significantly improves performance in Azure Synapse Analytics dedicated SQL pools.

Answer

Use round-robin distribution for large fact tables to distribute data evenly

Answer

Use replicated tables for large tables to avoid data movement

Question 11

Which TWO features are available in Azure Data Lake Storage Gen2 but not in Azure Blob Storage? (Choose two.)

Accepted Answer

Hierarchical namespace. Azure Data Lake Storage Gen2 (ADLS Gen2) extends Azure Blob Storage by adding a hierarchical namespace, which organizes objects into a directory structure similar to a file system. This enables efficient directory-level operations (e.g., renaming or deleting a directory in O(1) time) and supports POSIX-compliant access control lists (ACLs) for fine-grained permissions. These two features are not available in standard Azure Blob Storage, which uses a flat namespace and only supports container-level access policies.

Answer

Immutable storage

Answer

Soft delete for blobs

Answer

Lifecycle management policies

Question 12

You are designing a data storage solution for a retail company that expects high volumes of small, time-series sensor data from thousands of IoT devices. The data must be stored cost-effectively and queried by time range with low latency. Which Azure data store should you recommend?

Accepted Answer

Azure Data Explorer (ADX). Azure Data Explorer (ADX) is optimized for high-volume, time-series data ingestion and low-latency queries over time ranges. It uses a columnar storage engine and automatic indexing, making it cost-effective for sensor data from thousands of IoT devices.

Answer

Azure Cosmos DB with analytical store

Answer

Azure SQL Database with columnstore indexes

Answer

Azure Blob Storage with Azure Data Lake Storage Gen2

Question 13

A multinational bank needs to store customer transaction records for 10 years to meet regulatory compliance. The data is rarely accessed after the first year. The solution must minimize storage costs while allowing queries on recent data with low latency. Which tiering strategy should you implement?

Accepted Answer

Use Azure Blob Storage with lifecycle management to transition from Hot to Cool to Archive tiers. Option D is correct because Azure Blob Storage lifecycle management automatically transitions blobs from Hot to Cool to Archive tiers based on age, minimizing storage costs for rarely accessed data after the first year while keeping recent data in Hot tier for low-latency queries. This aligns with the 10-year retention requirement and cost optimization goal without manual intervention.

Answer

Store all data in Azure SQL Database with partitioning and drop older partitions

Answer

Use Azure Data Lake Storage Gen2 with a single storage tier

Answer

Store data in Azure Cosmos DB with time-to-live (TTL) and use Azure Blob Storage for backups

Question 14

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?

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 15

Your company stores sensitive customer data in Azure Data Lake Storage Gen2. You need to ensure that only authorized users can access the data, and that access is audited. Which approach should you use to control access to the data lake?

Accepted Answer

Use Azure RBAC roles and ACLs on directories/files, and enable storage analytics logging. Option A is correct because RBAC combined with ACLs provides fine-grained access control and Azure Storage analytics logs enable auditing. Option B is wrong because SAS tokens are less granular and harder to manage for many users. Option C is wrong because firewall rules control network access, not user authorization. Option D is wrong because managed identities are for service-to-service authentication, not user access.

Answer

Use Azure managed identities for all user access

Answer

Use shared access signatures (SAS) with stored access policies

Answer

Configure a virtual network service endpoint and firewall rules