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

Question 1

A healthcare company stores patient records in an Azure Data Lake Storage Gen2 account. The data must be organized for efficient querying by a team of analysts who use Azure Synapse Analytics serverless SQL pools. You need to implement a folder structure that supports partition elimination and minimizes the amount of data scanned. Which two actions should you perform? (Choose two.)

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

Accepted Answer

Store files in a hierarchical folder structure using year/month/day segments.. To support partition elimination in serverless SQL pools, data should be organized into a hierarchical folder structure (e.g., year/month/day) and external tables should reference that structure in the LOCATION clause. This allows the query engine to skip irrelevant folders when filters are applied, reducing data scanned. Using a flat folder structure or storing files in the root container does not provide these benefits, and while Parquet is efficient, the key actions are folder organization and external table definition.

Answer

Use a single flat folder for all files and rely on file naming conventions to filter data.

Answer

Enable Azure Data Lake Storage Gen2 hierarchical namespace and store all files in the root container.

Answer

Use Apache Parquet file format and partition the data by year, month, and day using folder names.

Question 2

You are designing a storage solution for a financial services company that must retain transactional data for seven years in Azure Data Lake Storage Gen2. Regulatory requirements mandate that data cannot be modified or deleted during the retention period, and access must be auditable. You need to enforce immutability at the storage account level. What should you configure?

Accepted Answer

Enable a time-based retention policy on the container with a retention interval of seven years and lock the policy.. A locked time-based retention policy on the container is the only option that enforces immutability for a defined period and prevents the policy from being weakened. It ensures that blobs cannot be modified or deleted for seven years, and the lock provides tamper-proof compliance. This is the documented approach for WORM compliance in Azure Storage.

Answer

Enable soft delete for blobs with a retention period of seven years.

Answer

Configure a legal hold on the container and apply Azure RBAC to restrict write access.

Answer

Enable blob versioning and configure a lifecycle management policy to archive blobs after one year.

Question 3

A media company stores video metadata in Azure Cosmos DB for a global recommendation engine. The application performs many reads by a composite key of userId and contentId, and the data must remain available during regional outages. You need to choose a partitioning and consistency configuration that provides low-latency point reads and high availability. What should you do?

Accepted Answer

Use a synthetic partition key combining userId and contentId, and set consistency level to Eventual.. A synthetic partition key of userId and contentId ensures each composite key maps to a single logical partition, so point reads are served from one partition with minimal latency. Eventual consistency provides the highest availability and lowest latency across regions, meeting the requirement to stay available during outages while accepting slightly stale reads.

Answer

Use a partition key of contentId and set consistency level to Eventual.

Answer

Use a partition key of userId and set consistency level to Strong.

Answer

Use a partition key of userId and set consistency level to Session.

Question 4

You are designing a storage strategy for a healthcare analytics platform. The platform ingests structured claims data into Azure Synapse Analytics and also stores semi-structured JSON documents in Azure Cosmos DB. You need to choose two storage technologies that support serverless querying over the data lake without provisioning compute. Which two should you use? (Choose two.)

Accepted Answer

Azure Data Lake Storage Gen2 with a serverless SQL endpoint via Azure Synapse. Azure Synapse serverless SQL pool and the serverless SQL endpoint over ADLS Gen2 both allow querying data in the lake without provisioning dedicated compute. They charge per query and support on-demand exploration. Dedicated SQL pool, Databricks clusters, and Cosmos DB all require some form of provisioned or continuously running compute, making them unsuitable for the stated serverless requirement.

Answer

Azure Synapse dedicated SQL pool

Answer

Azure Cosmos DB with the Core (SQL) API

Answer

Azure Databricks with autoscaling clusters

Question 5

You are designing a data storage solution for a media company that uses Azure Data Lake Storage Gen2. The company needs to store video files and metadata. The video files are large (over 1 GB each) and are accessed infrequently after initial processing. The metadata is small JSON files that are queried frequently. You need to optimize storage costs while ensuring fast access to metadata. Which two actions should you take? (Choose two.)

Accepted Answer

Store metadata in the Hot access tier.. Storing frequently queried metadata in the Hot tier ensures fast access, while placing large, infrequently accessed video files in the Cool tier reduces storage costs without incurring the high latency and retrieval fees of the Archive tier. This combination balances performance and cost.

Answer

Enable hierarchical namespace on the storage account.

Answer

Store video files in the Archive access tier.

Answer

Use a lifecycle management policy to move video files to Archive after 30 days.

Question 6

A company uses Azure Synapse Analytics dedicated SQL pool to store sales data. The data is loaded daily from Azure Data Lake Storage Gen2 using PolyBase. The company wants to improve query performance by distributing the fact table across distributions. The fact table contains columns: SaleID (unique), ProductID (foreign key), StoreID (foreign key), and SaleAmount. Queries frequently join the fact table with dimension tables on ProductID and StoreID, and also filter on SaleAmount. Which distribution type should you use for the fact table?

Accepted Answer

Hash distribution on StoreID. In a dedicated SQL pool, hash distribution on a column used in frequent joins colocates related rows, reducing data movement. Between ProductID and StoreID, the better choice depends on join frequency and data size. If StoreID is the primary join key or has higher cardinality, hash distribution on StoreID minimizes data movement for those joins. Round-robin or distribution on a non-join column like SaleID would cause more data movement.

Answer

Hash distribution on SaleID

Answer

Round-robin distribution

Answer

Hash distribution on ProductID

Question 7

You are designing a storage solution for a healthcare analytics platform. The platform stores patient records in Azure Data Lake Storage Gen2 and must enforce fine-grained access so that analysts can read only the columns and rows relevant to their department. You also need to audit access and prevent unauthorized changes to the data. Which feature should you implement?

Accepted Answer

Azure Synapse Analytics serverless SQL pool with row-level security and column-level security.. Serverless SQL pools in Azure Synapse Analytics can apply row-level security and column-level security over external data in Data Lake Storage Gen2. This lets you restrict rows and columns by department, and the query layer supports auditing and controlled access that prevents unauthorized modifications, satisfying the fine-grained access and audit requirements.

Answer

Azure Private Link with a private endpoint for the storage account.

Answer

Azure Data Lake Storage Gen2 POSIX-like ACLs on folders and files.

Answer

Azure Storage service-level SAS tokens scoped to the container.

Question 8

A company is designing a data lake solution on Azure Data Lake Storage Gen2. Data will be ingested from IoT devices at high frequency (every 5 seconds). Each device sends a JSON payload of 2 KB. The data must be stored in a hierarchical namespace and partitioned by date and device ID to optimize query performance. Which partition strategy should be used?

Accepted Answer

Organize folders as /YYYY/MM/DD/DeviceID/ in ADLS Gen2 and use file naming that includes timestamp.. ADLS Gen2 with a hierarchical namespace allows folder-based partitioning by date and device ID (e.g., /YYYY/MM/DD/DeviceID/), which directly maps to the query optimization requirement. This structure enables efficient partition pruning for time-range and device-specific queries, and the high-frequency 2 KB JSON payloads are well-suited for append-friendly file naming with timestamps.

Answer

Use Azure SQL Database with clustered columnstore index on date and device ID.

Answer

Use Azure Table Storage with PartitionKey set to date and RowKey set to device ID.

Answer

Use Azure Cosmos DB with partition key on (date, device ID) and TTL for data retention.

Question 9

You are designing a near-real-time analytics pipeline for a retail company. Transaction data is generated in Azure SQL Database and must be replicated to Azure Synapse Analytics (dedicated SQL pool) with less than 5 minutes latency. The source table has 50 million rows and 200 columns, but only 30 columns are needed for analytics. Which approach should you recommend?

Accepted Answer

Enable Change Data Capture (CDC) on the source table and use Azure Data Factory with a 1-minute tumbling window to copy changes into Synapse.. Azure Data Factory (ADF) with Change Data Capture (CDC) on the source SQL database can incrementally copy only changed rows (inserts, updates, deletes) into Azure Synapse Analytics using a 1-minute tumbling window, meeting the sub-5-minute latency requirement while minimizing data volume. This approach efficiently handles 50 million rows by transferring only the 30 needed columns, avoiding full table scans and reducing network load.

Answer

Use Azure SQL Database Change Tracking and push changes to Azure Event Hubs, then use Azure Stream Analytics to write to Synapse.

Answer

Use Azure Synapse PolyBase to directly query the source SQL database every 5 minutes.

Answer

Schedule a full copy of the entire table every 5 minutes using Azure Data Factory.

Question 10

A data engineer needs to store semi-structured JSON log files from a web application. Each log entry is about 1 KB. The logs are rarely queried (once a month) and must be retained for 7 years for compliance. The solution must minimize storage cost. Which storage option should be used?

Accepted Answer

Store the logs in Azure Blob Storage with cool access tier.. Azure Blob Storage with the cool access tier is the correct choice because it is optimized for storing large amounts of semi-structured data (like JSON logs) at low cost, with infrequent access (once a month) and long retention (7 years). The cool tier offers lower storage costs than hot or premium tiers, while still providing high durability and the ability to query logs using tools like Azure Data Lake Storage or serverless SQL. This meets the compliance requirement without the high compute or transaction costs of a database solution.

Answer

Store the logs in Azure SQL Database as a table.

Answer

Store the logs in Azure Files share.

Answer

Store the logs in Azure Cosmos DB with a JSON container.

Question 11

Which TWO of the following are supported storage options for use as a source in Azure Synapse Pipeline Copy Activity?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 is a supported source for Azure Synapse Pipeline Copy Activity because it combines a hierarchical file system with Azure Blob Storage APIs, enabling efficient data ingestion. The Copy Activity can read data from ADLS Gen2 using the AzureBlobFS linked service, which supports both file and folder-level reads for structured and unstructured data.

Answer

Azure Analysis Services

Answer

Azure Cognitive Search

Answer

Azure Purview

Question 12

You are an administrator for an Azure Synapse Analytics dedicated SQL pool. You execute the T-SQL statements shown in the exhibit. The external table 'dbo.Orders' is created. Which statement about querying this external table is true?

Accepted Answer

You can query the external table using standard T-SQL SELECT statements.. An external table in Azure Synapse Analytics dedicated SQL pool is a read-only abstraction over data stored externally (e.g., in Azure Blob Storage or Azure Data Lake Store). You can query it directly using standard T-SQL SELECT statements without importing data into the pool, leveraging PolyBase to push down predicate filtering and read only the required data.

Answer

Querying the external table automatically imports data into a round-robin distribution.

Answer

The table cannot be queried until the data is imported into the dedicated SQL pool.

Answer

You must first create a PolyBase external table before querying.

Question 13

A company is designing a data storage solution for IoT device telemetry. Each device sends a JSON payload every second. The data must be stored in a way that supports real-time dashboards and long-term analytics with low latency. Which Azure data store should be used for the ingestion layer?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is the correct choice for the ingestion layer because it is a fully managed, real-time data streaming platform designed to ingest millions of events per second with low latency. It supports the capture of JSON telemetry from IoT devices and integrates directly with downstream analytics services like Azure Stream Analytics for real-time dashboards and long-term storage in Azure Data Lake or Blob Storage. Its partitioned throughput model ensures scalable, durable ingestion without blocking producers.

Answer

Azure SQL Database

Answer

Azure Blob Storage

Answer

Azure Data Lake Storage

Question 14

A data engineer needs to store JSON documents that are frequently updated by multiple users concurrently. The solution must support optimistic concurrency control and have built-in indexing on all fields. Which Azure data store should be used?

Accepted Answer

Azure Cosmos DB (SQL API). Azure Cosmos DB (SQL API) is the correct choice because it natively supports optimistic concurrency control via ETags (HTTP entity tags) and provides automatic indexing of all fields without requiring manual index management. This makes it ideal for storing JSON documents that are frequently updated by multiple concurrent users, as it ensures conflict detection and resolution while maintaining high performance.

Answer

Azure Blob Storage

Answer

Azure SQL Database

Answer

Azure Table Storage

Question 15

A company is designing a data storage solution for a global application that requires low-latency reads and writes for user session data. The solution must support automatic failover across multiple Azure regions. Which TWO Azure services meet these requirements?

Accepted Answer

Azure Cache for Redis. Azure Cache for Redis is correct because it provides an in-memory data store with sub-millisecond latency for both reads and writes, making it ideal for user session data. It supports automatic failover across Azure regions through geo-replication, where data from a primary cache is asynchronously replicated to a secondary cache in a paired region, ensuring high availability and disaster recovery.

Answer

Azure Table Storage

Answer

Azure Blob Storage

Answer

Azure SQL Database