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

Question 1

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?

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

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

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 2

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

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

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 8

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 9

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. Option A is correct because a shard key (partition key) should have stable values that do not change; in Azure Cosmos DB, updating a partition key value requires deleting and reinserting the item, and immutable keys avoid cross-partition churn and RU overhead. Option C is correct because high cardinality ensures many distinct partition key values, which allows the logical partitions to be spread across many physical partitions and prevents any single partition from becoming a hotspot. Option E is correct because even distribution of request volume (and storage) across partitions is essential for optimal performance; uneven request volume concentrates RUs on a few physical partitions, causing throttling (429s) while others sit idle. Option B is not appropriate because frequent updates to the key value force item relocation across partitions, consuming extra RUs and degrading performance. Option D is not appropriate because low cardinality limits the number of logical partitions, so data and traffic concentrate on fewer physical partitions, creating hotspots and reducing scalability.

Answer

Frequent updates to the key value

Answer

Low cardinality of the key

Question 10

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 access tier is designed for infrequently accessed data that is still stored online, providing millisecond-level latency while offering lower storage costs than Hot for data accessed roughly once a month. Option B (Hot) is also correct because the Hot tier keeps data immediately available with millisecond latency and is fully suitable when infrequent access still requires instant retrieval, even though its storage cost is higher. Option C (Premium) is not correct here because it is intended for high-transaction, low-latency workloads using block blobs on SSD storage, not for infrequently accessed monthly data. Option D (Cold) is not correct because, although it is an online tier with millisecond latency, it targets data accessed rarely (around once every 90 days or less), making it a poorer fit than Cool for monthly access. Option E (Archive) is not correct because Archive is an offline tier requiring rehydration, which can take hours, so it cannot meet millisecond availability.

Answer

Premium

Answer

Cold

Answer

Archive

Question 11

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 through views that leverage the WHERE clause with user identity functions like SUSER_SNAME() to filter rows based on the user's department membership. This allows dynamic row filtering without duplicating data or managing separate files, and 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 12

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 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 ensures that rows sharing the same distribution value also fall into the same partition, which minimizes data movement during partition switching and improves join and aggregation performance in a Dedicated SQL Pool. Option C is correct because the primary benefit of partitioning is partition elimination; queries must filter on the partition column (typically a date) so the engine can prune irrelevant partitions and scan only the needed data. Option D is correct because each partition creates a separate distribution structure, and keeping the partition count below 60 (the number of distributions in a typical Dedicated SQL Pool) avoids excessive partition overhead and metadata pressure while maintaining effective partition elimination. Option B is not appropriate because hash distribution is not universally optimal for fact tables; some fact tables benefit from replicated or round-robin distribution depending on size and join patterns. Option E is not appropriate because partition columns should have low-to-moderate cardinality (such as date), since high cardinality produces too many small partitions and degrades performance.

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 (Configure firewall and virtual network rules) is correct because restricting network access to the storage account via firewall and VNet service endpoints or private endpoints limits who can reach the data, which is a core control for protecting data at rest in Azure Data Lake Storage Gen2. Option C (Enable customer-managed keys in Azure Key Vault) is correct because encryption at rest in ADLS Gen2 is performed with Azure Storage Service Encryption, and using customer-managed keys in Azure Key Vault gives you control over the key lifecycle that protects the data at rest. Option A (Enable soft delete for blobs) is incorrect here because soft delete is a data recovery/retention feature, not a data-at-rest security control. Option D (Assign storage blob data contributor roles to users) is incorrect because RBAC role assignment governs access to data operations, not encryption or protection of data at rest. Option E (Use client-side encryption with Azure Key Vault) is incorrect because client-side encryption is applied before data is sent to Azure and is not the mechanism for securing data at rest within ADLS Gen2.

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. A data engineer creates an external table in Azure Synapse Serverless SQL. Which statement about this table is correct?

Accepted Answer

The table is read-only. External tables in Azure Synapse Serverless SQL are read-only because they provide a relational abstraction over data stored externally (e.g., in Azure Data Lake Storage Gen2 or Blob Storage). You cannot perform INSERT, UPDATE, DELETE, or DDL modifications on the underlying data through the external table; it is designed solely for querying with T-SQL. This is a fundamental constraint of the serverless SQL pool architecture, which uses the OPENROWSET or CREATE EXTERNAL TABLE syntax to read files in place without a storage engine.

Answer

The table supports indexing for performance

Answer

The external data source TYPE must be 'HADOOP' for Azure Data Lake Storage Gen2

Answer

The table references a single Parquet file named 'Sales.parquet'