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

Question 1

A company stores IoT sensor data in Azure Blob Storage. The data is appended every minute and must be queried in near real-time using a SQL interface. Which Azure service should be used to enable this?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines Blob Storage's scalable, append-friendly architecture with a hierarchical namespace and full POSIX-like ACLs, enabling SQL-based querying via Azure Synapse Serverless SQL or PolyBase. The append-blob pattern (every-minute writes) is natively supported, and the SQL interface (e.g., OPENROWSET) can query the data in near real-time without moving it.

Answer

Azure Cosmos DB

Answer

Azure SQL Database

Answer

Azure Synapse SQL Pool

Question 2

A company is designing a data lake on Azure Data Lake Storage Gen2. Data comes from multiple sources with varying schemas. The team must minimize storage costs while keeping all data available for future processing. Which storage tier should they use for the raw ingested data?

Accepted Answer

Cool tier. The Cool tier is the optimal choice for raw ingested data in a data lake because it offers low storage costs while maintaining low-latency access for future processing. Unlike the Archive tier, Cool tier data is immediately available without the multi-hour rehydration delay, and it is significantly cheaper than the Hot tier for data that is infrequently accessed but must remain online for ETL or batch processing.

Answer

Premium tier

Answer

Archive tier

Answer

Hot tier

Question 3

You are designing a solution to store telemetry data from millions of devices. Each device sends a JSON payload every 5 seconds. The data must be partitioned by device ID and time for efficient querying and must support real-time streaming ingestion. Which Azure storage solution should you recommend?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it offers a multi-model, globally distributed database service with native support for real-time streaming ingestion via the Change Feed and automatic indexing. Its partition key design (device ID + time) enables efficient querying across millions of devices, and it guarantees single-digit millisecond read/write latencies essential for telemetry data arriving every 5 seconds.

Answer

Azure SQL Database

Answer

Azure Blob Storage with Azure Event Hubs

Answer

Azure Table Storage

Question 4

A data engineer needs to store CSV files containing customer data in Azure Blob Storage. The files must be encrypted at rest using a customer-managed key stored in Azure Key Vault. What should they configure?

Accepted Answer

Azure Storage Service Encryption (SSE) with customer-managed keys. Azure Storage Service Encryption (SSE) for Blob Storage encrypts data at rest automatically. By choosing customer-managed keys (CMK) stored in Azure Key Vault, the customer retains control over the encryption keys, meeting the requirement for customer-managed key encryption at rest. SSE with CMK is the correct service for encrypting blobs with a key the customer manages.

Answer

Azure Disk Encryption

Answer

Azure Storage Firewall

Answer

Azure Information Protection

Question 5

A company uses Azure SQL Database for an OLTP application. They need to run complex analytical queries without impacting OLTP performance. Which solution should they implement?

Accepted Answer

Create a readable secondary replica. Creating a readable secondary replica (Option C) offloads read-only analytical queries to a synchronized copy of the database, isolating them from the primary OLTP workload. Azure SQL Database supports this via Active Geo-Replication or Hyperscale named replicas, ensuring the primary remains unaffected by heavy analytical processing.

Answer

Enable automatic tuning

Answer

Enable geo-replication

Answer

Create a failover group

Question 6

Which TWO options are valid ways to load data into Azure Synapse SQL Pool? (Choose two.)

Accepted Answer

Using the COPY statement. The COPY statement (Option C) is a valid and recommended method for loading data into Azure Synapse SQL Pool because it provides a high-throughput, fully managed ingestion path from Azure Data Lake Storage or Azure Blob Storage. It supports parallel loading, automatic schema inference, and built-in error handling, making it the preferred choice over older methods like PolyBase for many scenarios.

Answer

Using INSERT INTO VALUES

Answer

Using BCP utility

Answer

Using SQL Server Integration Services (SSIS)

Question 7

Which THREE security features are available for Azure SQL Database? (Choose three.)

Accepted Answer

Auditing. Auditing is a built-in security feature for Azure SQL Database that tracks database events and writes them to an audit log in your Azure Storage account, Log Analytics workspace, or Event Hubs. It helps maintain regulatory compliance, understand database activity, and gain insights into discrepancies and anomalies that could indicate security concerns.

Answer

Always On availability groups

Answer

Firewall rules

Question 8

A data engineer runs the Azure CLI command shown in the exhibit. The blob is stored in Azure Blob Storage. The team previously set a lifecycle management rule to move blobs to the Archive tier after 30 days. The blob was created 45 days ago. What is the most likely reason the blob is still in the Cool tier?

Accepted Answer

The lifecycle management rule was configured after the blob was created, and it can take up to 24 hours for the rule to be evaluated.. The lifecycle management rule is evaluated by Azure Storage once per day. If the rule was configured after the blob was created, it may not have been evaluated yet, and it can take up to 24 hours for the rule to apply. Since the blob is 45 days old and still in Cool tier, the most likely reason is that the rule has not yet been evaluated after its configuration.

Answer

The lifecycle management rule only applies to blobs in the Hot tier.

Answer

The blob is in a container that is excluded from the lifecycle rule.

Answer

The blob must be in the Hot tier for the rule to move it to Archive.

Question 9

A company is designing a data storage solution for streaming IoT telemetry data. The data is JSON-formatted, arrives at up to 10,000 events per second, and must be stored for at least 30 days for real-time dashboards and ad-hoc querying. The solution must minimize operational overhead and query latency. Which Azure service should they use?

Accepted Answer

Azure Data Explorer (ADX). Azure Data Explorer (ADX) is purpose-built for high-velocity telemetry and time-series data, ingesting up to 10,000 events per second with low latency. Its columnar storage and indexing enable sub-second queries on JSON data for real-time dashboards, while the 30-day retention is natively configurable via caching and soft-delete policies. This minimizes operational overhead by eliminating the need for manual partitioning or index tuning.

Answer

Azure Blob Storage with Azure Data Lake Storage Gen2

Answer

Azure Cosmos DB with analytical store

Answer

Azure SQL Database with elastic query

Question 10

You are a data engineer for a financial services company. The company stores sensitive transaction data in Azure Data Lake Storage Gen2. The data is partitioned by date and loaded daily via Azure Data Factory. Recently, an audit found that the storage account allows public network access, and some containers have anonymous read access enabled. You need to secure the storage account according to the principle of least privilege while ensuring that Azure Data Factory can still load data. You must also ensure that data can be accessed by Azure Databricks for analytics. The solution must minimize administrative overhead. Which course of action should you take?

Accepted Answer

Disable public network access. Set the storage account firewall to allow access only from Azure services. Configure Azure Data Factory and Azure Databricks to use managed identities. Grant the managed identities the 'Storage Blob Data Contributor' role at the container level. Remove any anonymous access.. Option D is correct because disabling public network access and using managed identities with RBAC (Storage Blob Data Contributor) aligns with the principle of least privilege while minimizing administrative overhead. Azure Data Factory and Azure Databricks can authenticate via managed identities without managing keys or IP ranges, and removing anonymous access eliminates the security gap. The firewall rule allowing access only from Azure services ensures that only Azure-internal traffic can reach the storage account, which is sufficient for these services when they are in the same region.

Answer

Keep public network access enabled but restrict it to specific IP addresses. Use storage account keys for Azure Data Factory and Azure Databricks.

Answer

Disable public network access. Create private endpoints for the storage account and configure Azure Data Factory and Azure Databricks to use the private endpoints. Use RBAC to assign 'Storage Blob Data Contributor' to the managed identities.

Answer

Enable public network access with a firewall rule to allow only the Azure Data Factory and Azure Databricks IP ranges. Keep anonymous access enabled but set the containers to private.

Question 11

A retail company uses Azure Synapse Analytics dedicated SQL pool to store sales data. The data is loaded nightly from Azure Data Lake Storage Gen2 using PolyBase. Recently, the load process started failing with the error 'External table 'sales' is not accessible because the location does not exist or is used by another process.' You verify that the storage account, container, and file path are correct. The file is a CSV file named 'sales_20250301.csv' and it exists. Other files in the same container load successfully. What is the most likely cause of the error?

Accepted Answer

The file is being written to or is locked by another process during the PolyBase read.. The error 'location does not exist or is used by another process' specifically indicates that the file is locked by another process. In Azure Data Lake Storage Gen2, when a file is being written or modified, it can be locked by the writing process (e.g., a data ingestion pipeline or another ETL job). PolyBase attempts to read the file while it is still being written, causing the error. Option D correctly identifies this concurrency issue.

Answer

The network connectivity between Synapse and the storage account is intermittent.

Answer

The CSV file has an incorrect number of columns or contains a header row that mismatches the schema.

Answer

The storage account key used in the external data source has expired.

Question 12

A healthcare company stores patient records in Azure Blob Storage. The compliance team requires that all data be encrypted at rest using customer-managed keys (CMK) stored in Azure Key Vault. Additionally, the storage account must be accessible only from a specific virtual network (VNet) and must support versioning to protect against accidental deletion. The storage account is currently using Microsoft-managed keys and has public network access enabled. You need to implement the required changes with minimal downtime. Which course of action should you take?

Accepted Answer

Create a new storage account in the same region with infrastructure encryption enabled and customer-managed keys configured. Enable versioning and VNet access. Use AzCopy to copy data from the old account to the new one. Update applications to use the new storage account. Delete the old account.. Option A is correct because creating a new storage account with infrastructure encryption and customer-managed keys (CMK) is the only way to enable infrastructure encryption, which cannot be enabled on an existing account. Using AzCopy ensures minimal downtime by copying data in the background while applications continue to use the old account until the cutover. Enabling versioning and VNet access on the new account meets all compliance requirements without disrupting the existing environment.

Answer

Modify the existing storage account to use customer-managed keys by updating the encryption settings in the Azure portal. Then, enable versioning and configure firewall rules to allow access from the VNet.

Answer

Delete the existing storage account and recreate it with the same name, enabling customer-managed keys, versioning, and VNet access. Restore data from backups.

Answer

Create a new storage account with the same name in a different region. Enable infrastructure encryption and customer-managed keys. Use Azure Data Factory to copy data from the old account to the new one. Then, delete the old account and update applications to point to the new account.

Question 13

A company is designing a data storage solution for IoT device telemetry data. The data is append-only, needs to be stored cost-effectively for long-term analytics, and must support querying by device ID and timestamp. Which Azure storage solution should they use?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines the cost-effective, append-only blob storage of Azure Blob Storage with a hierarchical namespace that enables directory-level operations and POSIX-like access control. This makes it ideal for storing large volumes of IoT telemetry data at low cost while supporting efficient querying by device ID and timestamp through partition pruning in tools like Azure Synapse Analytics or Apache Spark.

Answer

Azure Cosmos DB

Answer

Azure SQL Database

Answer

Azure Blob Storage with hot access tier

Question 14

A data engineering team is designing a batch processing pipeline that reads from Azure Data Lake Storage Gen2, transforms data using Azure Databricks, and writes to Azure Synapse Analytics. The pipeline must process data incrementally and handle late-arriving data up to 2 hours. Which approach should they use to track processed files?

Accepted Answer

Store processed file names in a Delta table and compare with source folder listing. Storing processed file names in a Delta table allows the pipeline to track which files have been processed and compare against the source folder to identify new or late-arriving files. This is a common pattern for incremental processing.

Answer

Use Blob Storage event triggers to invoke Azure Functions

Answer

Use Azure Synapse Pipelines with a schedule and full load each time

Answer

Use Azure Data Factory with watermark columns in the source

Question 15

A company is migrating an on-premises Hadoop cluster to Azure. The cluster uses Hive tables stored as Parquet files on HDFS. They want to minimize changes to existing Hive queries and continue using HiveQL. Which Azure storage solution should they choose?

Accepted Answer

Azure HDInsight with Hive and Azure Data Lake Storage Gen2. Azure HDInsight with Hive and Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it provides a fully managed Hadoop service that supports HiveQL with minimal changes. ADLS Gen2 offers a hierarchical namespace and is optimized for Hadoop workloads, allowing the existing Parquet files on HDFS to be directly mounted and queried without data movement or schema changes.

Answer

Azure SQL Database with PolyBase

Answer

Azure Databricks with Delta Lake

Answer

Azure Synapse Analytics with external tables