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

Question 1

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. Option C is correct because 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 2

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 3

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 4

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. Option D is correct because 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 5

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 6

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 7

Which THREE factors should you consider when choosing a shard key for Azure Cosmos DB to ensure even distribution and optimal performance?

Accepted Answer

Stable key values that do not change. Stable key values that do not change (Option A) are correct because Azure Cosmos DB uses the shard key to determine the physical partition for each document. If the key value changes after insertion, the document cannot be moved to a different partition, leading to cross-partition queries and degraded performance. A stable key ensures that the partition assignment remains consistent throughout the document's lifetime, which is essential for optimal query routing and throughput management.

Answer

Frequent updates to the key value

Answer

Low cardinality of the key

Question 8

Which TWO Azure Blob Storage access tiers are suitable for data that must be available within milliseconds but is accessed infrequently (e.g., once per month)?

Accepted Answer

Cool. The Cool tier is designed for data that is accessed infrequently (e.g., once per month) but still requires low latency (milliseconds) on access. It offers a lower storage cost than Hot but with a higher access cost, making it ideal for infrequent reads where immediate availability is needed.

Answer

Premium

Answer

Cold

Answer

Archive

Question 9

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 10

A company is designing a data lake on Azure Data Lake Storage Gen2. They need to enforce row-level security on the data for different departments. Which approach should they use?

Accepted Answer

Create views in Azure Synapse Serverless SQL with security predicates. Azure Synapse Serverless SQL supports row-level security (RLS) through the creation of views that use the `SECURITY_POLICY` and `FILTER_PREDICATE` functions. This allows you to filter rows based on the user's identity (e.g., department membership) without duplicating data or managing separate files. It is the only native Azure service that provides declarative row-level filtering directly on data stored in Azure Data Lake Storage Gen2.

Answer

Assign Azure RBAC roles for each department on the storage account

Answer

Implement Azure Purview data policies for row-level security

Answer

Use Azure Data Lake Storage Gen2 access control lists (ACLs) at the file level

Question 11

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 12

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

Accepted Answer

Azure Data Factory with incremental copy. Azure Data Factory with incremental copy is a valid method because it can connect to on-premises SQL Server via self-hosted integration runtime, and then use change tracking or watermark columns to copy only new or updated rows into Azure Synapse Analytics. This approach minimizes data transfer and supports scheduled, reliable loading.

Answer

SQL Server Integration Services (SSIS) package

Question 13

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. Option A is correct because 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

Question 14

Which TWO actions should be taken to secure data at rest in Azure Data Lake Storage Gen2?

Accepted Answer

Configure firewall and virtual network rules. Option B is correct because configuring firewall and virtual network rules restricts network-level access to the storage account, preventing unauthorized traffic from reaching the data at rest. Option C is correct because enabling customer-managed keys (CMK) in Azure Key Vault provides an additional encryption layer for data at rest, allowing you to control and rotate encryption keys independently of Azure-managed keys.

Answer

Enable soft delete for blobs

Answer

Assign storage blob data contributor roles to users

Answer

Use client-side encryption with Azure Key Vault

Question 15

Refer to the exhibit. An Azure Policy is defined to enforce network security on storage accounts. What does this policy do?

Accepted Answer

Denies storage accounts that allow public network access from all networks. The Azure Policy in the exhibit uses the 'Deny' effect with a condition that checks if the 'networkAcls.defaultAction' property is set to 'Allow'. When 'defaultAction' is 'Allow', the storage account permits traffic from all networks, including the internet. The policy denies such configurations to enforce network security by requiring that public network access be restricted.

Answer

Denies storage accounts that do not have any IP rules defined

Answer

Denies storage accounts that have firewall rules configured

Answer

Denies storage accounts that have public network access disabled