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

Question 1

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 2

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 3

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. 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 4

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 5

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. Azure RBAC roles provide coarse-grained control (e.g., assigning Storage Blob Data Contributor at the storage account level), while ACLs (POSIX-style) on directories and files enable fine-grained permissions for specific users or groups. Enabling storage analytics logging (specifically the 'StorageRead', 'StorageWrite', and 'StorageDelete' logs) captures all authenticated requests, meeting the auditing requirement. This combination directly addresses the need for both authorized access control and auditability in Azure Data Lake Storage Gen2.

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

Question 6

You are migrating an on-premises SQL Server database to Azure. The database has a large fact table (500 GB) and several dimension tables (10 GB total). Reporting queries join the fact table with dimension tables and aggregate by date. Which Azure service and table design should you recommend to minimize query latency?

Accepted Answer

Azure Synapse SQL Pool with hash distribution on the fact table's foreign key and round-robin for dimension tables. Azure Synapse SQL Pool with hash distribution on the fact table's foreign key ensures that related rows from the fact and dimension tables are co-located on the same compute node, minimizing data movement during joins. Round-robin distribution for the small dimension tables is appropriate since they are under 1 GB each and can be broadcast to all nodes, further reducing shuffle overhead. This design optimizes parallel query execution for large fact table aggregations by date.

Answer

Azure Synapse SQL Pool with replicated tables for both fact and dimension tables

Answer

Azure SQL Database Hyperscale with columnstore indexes

Answer

Azure SQL Database with rowstore indexes and a single database

Question 7

A healthcare organization needs to store electronic health records (EHR) in a format that supports schema flexibility and complex nested data. The solution must allow fast queries by patient ID and enable analytics with Azure Synapse. Which data store should you choose?

Accepted Answer

Azure Cosmos DB with analytical store enabled. Azure Cosmos DB with analytical store enabled is the correct choice because it provides schema flexibility for complex nested EHR data, supports fast point reads by patient ID via its indexed partition key, and the analytical store enables efficient analytics with Azure Synapse through the Synapse Link feature, which automatically synchronizes operational data into a columnar format optimized for large-scale queries.

Answer

Azure Table Storage

Answer

Azure Data Lake Storage Gen2 with files in JSON format

Answer

Azure SQL Database with JSON columns

Question 8

You are designing a solution for a social media company that needs to store user profile data with strong consistency and low latency (under 10 ms) for reads and writes. The data model is simple key-value with occasional queries on secondary attributes. Which Azure data store meets these requirements?

Accepted Answer

Azure Cosmos DB with session consistency and secondary indexes. Azure Cosmos DB with session consistency and secondary indexes meets the requirements because it provides single-digit-millisecond latency for both reads and writes, supports strong consistency (session consistency offers monotonic reads and writes), and allows efficient queries on secondary attributes via indexing. This makes it ideal for a key-value store with occasional secondary attribute queries, unlike simpler stores that lack indexing or consistency guarantees.

Answer

Azure Table Storage

Answer

Azure SQL Database with clustered index on user ID

Answer

Azure Cache for Redis with persistence

Question 9

You are using Azure Synapse SQL Pool to store a large fact table partitioned by date. Queries frequently filter on a specific date range and aggregate by a column called 'product_id'. Which table distribution and indexing strategy will minimize query execution time?

Accepted Answer

Hash distribution on product_id with clustered columnstore index. Hash distribution on product_id ensures that rows with the same product_id are co-located on the same distribution, enabling efficient local aggregation without data movement. The clustered columnstore index provides high compression and fast scan performance for large fact tables, especially when queries filter on a date range and aggregate by product_id.

Answer

Hash distribution on product_id with clustered rowstore index

Answer

Replicated table with clustered columnstore index

Answer

Round-robin distribution with clustered columnstore index

Question 10

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 11

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 12

A data engineer needs to store semi-structured JSON logs from IoT devices. The data will be queried using SQL and must support high-throughput writes. Which Azure data store is most appropriate?

Accepted Answer

Azure Cosmos DB with Core (SQL) API. Azure Cosmos DB with Core (SQL) API is the most appropriate choice because it natively stores semi-structured JSON documents, supports high-throughput writes with single-digit millisecond latency, and allows querying the JSON data directly using SQL syntax. Its schema-agnostic design and automatic indexing make it ideal for IoT workloads where device telemetry arrives at high velocity and must be immediately queryable.

Answer

Azure Blob Storage with JSON blobs

Answer

Azure Data Lake Storage Gen2 with JSON files and PolyBase

Answer

Azure SQL Database with JSON columns

Question 13

A healthcare organization stores patient data in Azure SQL Database. They need to encrypt sensitive columns (e.g., SSN) such that only authorized users can decrypt the data at query time. Which feature should they use?

Accepted Answer

Always Encrypted (with secure enclaves). Always Encrypted with secure enclaves is the correct choice because it allows sensitive columns (e.g., SSN) to be encrypted at rest and in memory, with decryption occurring only on the client side using keys that are never exposed to the database engine. This ensures that only authorized users with the proper encryption keys can decrypt the data at query time, meeting the requirement for column-level encryption with client-side key management.

Answer

Transparent Data Encryption (TDE)

Answer

Azure Information Protection labels

Answer

Dynamic Data Masking

Question 14

Which TWO options are valid methods to load data from on-premises SQL Server into Azure Synapse Analytics?

Accepted Answer

SQL Server Integration Services (SSIS) package. Both SQL Server Integration Services (SSIS) and Azure Data Factory are fully capable of loading data from on-premises SQL Server into Azure Synapse Analytics. SSIS provides a mature, high-performance ETL tool that can directly target Synapse using the SQL Server Destination or the Azure Synapse Analytics Destination. Azure Data Factory, with its self-hosted integration runtime and incremental copy feature, offers a modern, cloud-native orchestration solution for scheduled and reliable data ingestion. PolyBase from external table, while useful for querying external data, is not primarily a data loading method; it requires additional steps to persist the data into Synapse tables and does not directly load from on-premises SQL Server without external staging.

Answer

PolyBase from external table

Question 15

Which THREE considerations should be evaluated when designing a partitioning strategy for a large fact table in Azure Synapse Dedicated SQL Pool?

Accepted Answer

Align partition boundaries with distribution key. Aligning partition boundaries with the distribution key in Azure Synapse Dedicated SQL Pool ensures that data movement during partition switching or merging is minimized, as each distribution contains its own set of partitions. This alignment avoids cross-distribution data transfers, which can significantly improve performance and maintainability of large fact tables.

Answer

Use hash distribution for all fact tables

Answer

Choose a partition column with high cardinality