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

Question 1

You are designing a storage solution for a global application that requires low-latency reads and writes of JSON documents. The data model includes nested properties, and you need to query these properties efficiently. You also need to ensure the data is available in multiple regions with automatic failover. Which Azure service should you use?

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

Accepted Answer

Azure Cosmos DB with Core (SQL) API and multi-region writes enabled.. Azure Cosmos DB with Core (SQL) API is designed for JSON documents, supports querying nested properties, and offers multi-region writes with automatic failover, ensuring low latency globally. Other services either lack native JSON support, do not support multi-region writes, or are not optimized for document queries.

Answer

Azure SQL Database with active geo-replication.

Answer

Azure Blob Storage with RA-GRS replication.

Answer

Azure Table Storage with a partition key and row key.

Question 2

You are designing a storage layer for a fraud detection system. The system writes millions of small JSON records per hour to Azure Data Lake Storage Gen2 and must support both batch analytics and interactive queries from Azure Databricks. You need to choose a storage format and layout that minimizes query latency for selective filters on customer ID while keeping storage costs predictable. What should you do?

Accepted Answer

Store the data as Delta Lake tables with Z-ORDER clustering on customer ID and optimize file sizes.. Delta Lake with Z-ORDER clustering on customer ID enables data skipping by storing min/max statistics per file and colocating similar values. This directly reduces the data read for selective filters. OPTIMIZE compacts small files, which is critical when millions of small records arrive hourly, improving both batch and interactive query performance while keeping storage costs controlled.

Answer

Store the data as Parquet files partitioned by customer ID and use partition pruning in Databricks.

Answer

Store the data as Avro files and use schema evolution to handle new fields.

Answer

Store the data as uncompressed JSON files and rely on Databricks caching to improve performance.

Question 3

You are designing a data storage solution for a media company that ingests video files from various sources into Azure Data Lake Storage Gen2. The files are uploaded continuously and must be processed by Azure Databricks. You need to ensure that the data is organized efficiently for query performance and that access is secure. Which two actions should you include in your design? (Choose two.)

Accepted Answer

Configure Azure Private Endpoints for the storage account to restrict access to the virtual network.. Partitioning data by upload date in a hierarchical folder structure enables efficient querying through partition pruning, which is essential for large-scale video processing in Databricks. Configuring Azure Private Endpoints ensures that access to the storage account is secure and restricted to the virtual network, meeting the security requirement. Together, these actions optimize performance and security.

Answer

Use Azure Blob Storage with a flat namespace to simplify file management.

Answer

Store all video files in a single container without subfolders to reduce complexity.

Answer

Enable Azure Defender for Storage to protect against malicious file uploads.

Question 4

You are designing a data storage solution in Azure Synapse Analytics. You need to store large volumes of semi-structured data in a dedicated SQL pool. The data will be used for analytical queries that often filter on a date column and join on a customer ID column. You want to optimize query performance. Which two actions should you perform? (Choose two.)

Accepted Answer

Use hash distribution on the customer ID column for large tables that are frequently joined.. Hash-distributing large tables on the customer ID column co-locates rows for joins, reducing data movement. Partitioning large fact tables on the date column enables partition elimination for date filters. Together, these actions optimize the most common query patterns. Other options either increase data movement or are not suitable for large-scale analytical workloads.

Answer

Use round-robin distribution for all tables to ensure even data spread.

Answer

Use replicated distribution for all dimension tables regardless of size.

Answer

Store the semi-structured data as JSON in a single column and use JSON functions for queries.

Question 5

You are designing a storage layer in Azure Data Lake Storage Gen2 for a data engineering pipeline. You need to store Parquet files that will be queried by Azure Synapse Analytics serverless SQL pools and Azure Databricks. You must optimize for query performance and minimize data scanned. Which two actions should you perform? (Choose two.)

Accepted Answer

Partition the data by a commonly filtered column such as TransactionDate.. Using Parquet with a columnar layout enables column pruning and compression, reducing the data scanned. Partitioning by a commonly filtered column such as TransactionDate allows partition elimination, further reducing the amount of data read. Together, these actions optimize query performance for both Synapse serverless SQL pools and Databricks.

Answer

Store the data in CSV format to ensure compatibility with all query engines.

Answer

Partition the data by a high-cardinality column such as TransactionId.

Answer

Enable hierarchical namespace and store all files in a single directory.

Question 6

A company is planning to migrate an on-premises data warehouse to Azure Synapse Analytics dedicated SQL pool. The data warehouse contains a large fact table with billions of rows and several dimension tables. The company wants to optimize query performance and minimize data movement during joins. They need to choose an appropriate distribution type for the fact table. The fact table is frequently joined with dimension tables on a column that has high cardinality and is evenly distributed. What distribution type should they use?

Accepted Answer

Hash distribution on the join column. Hash distribution on the join column aligns the data layout with the most common join operation, ensuring that matching rows are colocated and eliminating the need for data movement. Since the column has high cardinality and even distribution, it also provides balanced data across distributions, which is critical for query performance in a dedicated SQL pool.

Answer

Replicated distribution

Answer

Round-robin distribution

Answer

Hash distribution on a column with low cardinality

Question 7

A healthcare company stores patient records in Azure Data Lake Storage Gen2. The data must be encrypted at rest using customer-managed keys (CMK) stored in Azure Key Vault. The company also requires that the encryption keys are automatically rotated every 90 days. You need to configure the storage account to meet these requirements. What should you do?

Accepted Answer

Configure the storage account to use customer-managed keys from Azure Key Vault and set a key rotation policy in Azure Key Vault.. To use customer-managed keys with Azure Data Lake Storage Gen2, you configure the storage account to reference a key in Azure Key Vault. Azure Key Vault supports automatic key rotation policies, which can rotate the key every 90 days. The storage account automatically uses the new key version. This meets both encryption at rest with CMK and automatic rotation requirements.

Answer

Use Azure Disk Encryption with customer-managed keys and schedule a runbook to rotate the keys every 90 days.

Answer

Enable Azure Storage Service Encryption with Microsoft-managed keys and configure a key rotation policy in Azure Key Vault.

Answer

Enable infrastructure encryption on the storage account and use Microsoft-managed keys for automatic rotation.

Question 8

You are designing a data storage solution in Azure Synapse Analytics dedicated SQL pool. The solution must support efficient loading of large volumes of data from external sources and provide high query performance for reporting. You need to choose two table distribution types that are most appropriate for large fact tables and dimension tables respectively. (Choose two.)

Accepted Answer

Replicated table. For a dedicated SQL pool, large fact tables should be hash-distributed on a column frequently used in joins to minimize data movement. Small dimension tables should be replicated so that joins with fact tables avoid data movement. Round-robin is for staging, and external and temporary tables are not distribution types. This combination optimizes query performance for star schema workloads.

Answer

External table

Answer

Temporary table

Answer

Round-robin table

Question 9

A financial services firm stores trade records in an Azure Data Lake Storage Gen2 account. Regulatory requirements mandate that all data at rest be encrypted with a customer-managed key (CMK) stored in Azure Key Vault, and that the key be rotated every 90 days without re-uploading any data. The storage account currently uses Microsoft-managed keys. What should you do to meet these requirements with the least administrative effort?

Accepted Answer

Configure the storage account to use customer-managed keys stored in Azure Key Vault, and create a new key version in Key Vault every 90 days.. Customer-managed keys in Azure Key Vault allow the storage account to use an encryption key controlled by the organization. Rotating the key is achieved by creating a new key version in Key Vault; Azure Storage automatically picks up the latest version, so no data re-upload or re-encryption job is needed. This satisfies both the CMK mandate and the 90-day rotation requirement with minimal administrative effort.

Answer

Deploy Azure Disk Encryption on the storage account and schedule a Key Vault key rotation task using Azure Automation.

Answer

Create a new storage account with customer-managed keys and migrate all trade records using Azure Data Factory, then delete the original account.

Answer

Enable Azure Storage Service Encryption with Microsoft-managed keys and configure a lifecycle management policy to re-encrypt blobs every 90 days.

Question 10

You are designing a storage layer for an Azure Synapse Analytics dedicated SQL pool that ingests 4 TB of CSV files daily into a fact table. The files are landed in Azure Data Lake Storage Gen2 by an external ETL process. You need to load the data with the highest possible throughput while minimizing the load window. What should you do?

Accepted Answer

Use the COPY statement with a shared access signature (SAS) token and split the source files into multiple evenly sized files.. The COPY statement is the preferred high-throughput ingestion mechanism for dedicated SQL pools, especially when reading from ADLS Gen2 with SAS authentication. Splitting the source files into evenly sized chunks enables parallel reads across distributions, which is essential for loading 4 TB within a tight window. Combining COPY with proper file sizing directly addresses the throughput and load-window requirements.

Answer

Use the COPY statement with a shared access signature (SAS) token and set MAXDOP to a value equal to the number of files.

Answer

Use PolyBase with an external table and enable REJECT_TYPE VALUE with a threshold of 0.

Answer

Use Azure Data Factory with a copy activity that has a single pipeline and a single parallel copy thread.

Question 11

You are designing a data storage solution for a company that uses Azure Data Lake Storage Gen2. The company needs to store data in a hierarchical namespace and requires fine-grained access control at the folder and file level. The solution must also support POSIX-style permissions. Which feature should you enable?

Accepted Answer

Enable hierarchical namespace on the storage account.. Azure Data Lake Storage Gen2 is built on Blob Storage and adds a hierarchical namespace, which enables directory operations, POSIX-style permissions, and fine-grained ACLs. Enabling hierarchical namespace on the storage account is the required step to gain these capabilities. Other options are unrelated features that do not provide the needed functionality.

Answer

Enable Azure Defender for Storage.

Answer

Enable static website hosting on the storage account.

Answer

Enable blob soft delete on the storage account.

Question 12

You are designing a data storage solution in Azure Synapse Analytics. You need to store large fact tables that are frequently joined with dimension tables on a common column. The solution must minimize data movement during query execution and support high-concurrency queries. Which two actions should you take? (Choose two.)

Accepted Answer

Use hash distribution on the join column for the fact table.. Hash distributing the fact table on the join column and replicating the dimension tables minimizes data movement by colocating matching rows and eliminating shuffling of dimension data. These actions directly address the need for efficient joins and high concurrency in Azure Synapse Analytics dedicated SQL pools.

Answer

Use round-robin distribution for the fact table.

Answer

Use a columnstore index on the fact table.

Answer

Use hash distribution on a column with high cardinality for the fact table.

Question 13

You are designing a storage solution for a global application that requires low-latency reads and writes of JSON documents. The data model includes nested properties, and you need to query these properties efficiently. You also need to ensure the data is available in multiple regions with automatic failover. Which Azure service should you use?

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

Accepted Answer

Always Encrypted with column master keys stored in Azure Key Vault.. Always Encrypted encrypts sensitive columns on the client side, ensuring data is protected both at rest and in use. By storing column master keys in Azure Key Vault, key management is centralized and access is restricted to authorized applications. This satisfies the requirements for encryption in use and authorized decryption, which other options do not provide.

Answer

Row-level security (RLS) with a security policy on the customer table.

Answer

Dynamic data masking on the credit card number column.

Answer

Transparent Data Encryption (TDE) with service-managed keys.

Question 14

A data engineering team is designing a storage solution for a retail company that receives point-of-sale (POS) transaction data from thousands of stores. The data arrives as JSON files in Azure Data Lake Storage Gen2. The team needs to query the data using Azure Synapse Analytics serverless SQL pool and optimize for performance and cost. The data is partitioned by year, month, and day. They want to minimize the amount of data scanned per query. What should they do?

Accepted Answer

Convert the JSON files to Parquet format and create an external table with partition columns (year, month, day) and use partition elimination in queries.. Converting JSON to Parquet and creating an external table with partition columns enables partition elimination in Synapse serverless SQL pool. This means queries that filter on year, month, or day will only scan the relevant partitions, significantly reducing the amount of data processed and lowering costs. Parquet's columnar format also improves performance by allowing column pruning.

Answer

Use Azure Data Explorer to ingest the JSON data and query it with KQL.

Answer

Convert the JSON files to Parquet format and use OPENROWSET with explicit file paths for each partition.

Answer

Keep the JSON files and use OPENROWSET with wildcards to read all files, then filter by date in the query.

Question 15

You are designing a data storage solution in Azure Synapse Analytics. You need to load data incrementally from an Azure Data Lake Storage Gen2 source into a dedicated SQL pool. The source files are appended daily with new data, and you must ensure that only new records are loaded without duplicating existing records. The target table has a column named LoadDate that records when each row was inserted. Which approach should you use?

Accepted Answer

Use PolyBase to load all source files into a staging table, then use a MERGE statement to insert only rows that do not exist in the target.. Loading into a staging table with PolyBase and then using MERGE to insert only new rows is a standard pattern for incremental loads in a dedicated SQL pool. It avoids reloading historical data and prevents duplicates by comparing keys. The LoadDate column can be used to filter or track inserted rows, ensuring that only new records are added to the target.

Answer

Use the COPY statement to load all source files directly into the target table, relying on the primary key to reject duplicates.

Answer

Use Azure Data Factory to copy all source files to the target table with a daily schedule, and use a tumbling window trigger to avoid duplicates.

Answer

Use CREATE TABLE AS SELECT (CTAS) to create a new table from the source each day, replacing the target table.