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

Question 1

A company is migrating an on-premises Hadoop cluster to Azure. The cluster uses Hive tables stored as Parquet files on HDFS. They want to minimize changes to existing Hive queries and continue using HiveQL. Which Azure storage solution should they choose?

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

Accepted Answer

Azure HDInsight with Hive and Azure Data Lake Storage Gen2. Azure HDInsight with Hive and Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it provides a fully managed Hadoop service that supports HiveQL with minimal changes. ADLS Gen2 offers a hierarchical namespace and is optimized for Hadoop workloads, allowing the existing Parquet files on HDFS to be directly mounted and queried without data movement or schema changes.

Answer

Azure SQL Database with PolyBase

Answer

Azure Databricks with Delta Lake

Answer

Azure Synapse Analytics with external tables

Question 2

Which TWO of the following are recommended practices for designing a data storage solution using Azure Data Lake Storage Gen2?

Accepted Answer

Enable soft delete to protect against accidental deletion. Option A is correct because enabling soft delete on Azure Data Lake Storage Gen2 protects against accidental deletion by retaining deleted blobs or directories for a configurable retention period, allowing recovery without restoring from backups. Option C is correct because a well-designed partition strategy that groups related data together (for example, by date, region, or category in the hierarchical namespace) improves query performance, enables efficient parallel processing, and reduces the amount of data scanned by analytics engines like Azure Synapse or Databricks. Option B is incorrect because Kerberos is not a supported authentication mechanism for Azure Storage accounts; ADLS Gen2 uses Microsoft Entra ID (OAuth 2.0), Shared Key, or SAS tokens. Option D is incorrect because storing all files in a single directory creates a hotspot, degrades listing and query performance, and defeats the purpose of the hierarchical namespace. Option E is incorrect because enabling anonymous public access exposes data to unauthorized users and violates security best practices; access should be controlled via Microsoft Entra ID, RBAC, and ACLs.

Answer

Use Kerberos authentication for the storage account

Answer

Store all files in a single directory for simplicity

Answer

Enable anonymous public access for ease of use

Question 3

Which THREE of the following are valid methods to load data into Azure Synapse Analytics?

Accepted Answer

Azure Data Factory pipeline. Azure Data Factory pipelines (A) are a first-class, supported orchestration method for ingesting data into Azure Synapse Analytics, using copy activities and connectors to move data from sources such as Blob Storage, SQL, or on-premises systems into dedicated SQL pools. The COPY INTO T-SQL command (B) is a native, high-throughput ingestion statement in Synapse dedicated SQL pools that loads data directly from external storage like Azure Data Lake Storage or Blob Storage. PolyBase from Azure Blob Storage (E) is also valid, as PolyBase lets Synapse query and load external data in Blob Storage via external tables and CREATE EXTERNAL TABLE / CTAS operations. BULK INSERT (C) is not a supported ingestion path in Azure Synapse dedicated SQL pools because it relies on local file paths and SQL Server-style bulk loading rather than Synapse's external data source model. An SSIS package (D) is not a direct native load method for Synapse; SSIS is a separate ETL tool and would typically be orchestrated through Azure Data Factory or Azure-SSIS Integration Runtime rather than being a valid Synapse load method by itself.

Answer

BULK INSERT with a data source from Azure Blob Storage

Answer

SQL Server Integration Services (SSIS) package

Question 4

You need to partition a large Azure SQL Database table by date to improve query performance and manageability. Which partitioning strategy should you use?

Accepted Answer

CREATE PARTITION FUNCTION myDateRange (datetime2) AS RANGE RIGHT FOR VALUES ('2023-01-01', '2023-02-01', ...). `RANGE RIGHT` ensures that each boundary value belongs to the right partition, which is the standard approach for date-based partitioning. This means values less than '2023-01-01' go into the first partition, values >= '2023-01-01' and < '2023-02-01' go into the second, and so on, aligning with typical date range queries and simplifying partition management (e.g., switching out old partitions).

Answer

CREATE PARTITION FUNCTION myDateRange (datetime2) AS RANGE LEFT FOR VALUES ('2023-01-01', '2023-02-01', ...)

Answer

Use Azure SQL Database automatic partitioning feature

Answer

Apply a clustered columnstore index with partitioning

Question 5

Match each Azure data storage service to its primary use case.

Accepted Answer

Azure Blob Storage. All Azure data storage services listed are correctly matched to their primary use cases: Azure Blob Storage for unstructured object storage, Azure Cosmos DB for globally distributed NoSQL data, Azure Data Lake Storage Gen2 for big data analytics, and Azure SQL Database for relational OLTP workloads.

Question 6

You are designing a data lake on Azure Data Lake Storage Gen2. The data will be used by both batch processing (Spark) and interactive querying (Azure Synapse Serverless SQL). The data is partitioned by date and stored as Parquet. What is the optimal folder structure to minimize cross-partition scans for both workloads?

Accepted Answer

/year/month/day/ (e.g., /2023/12/25/). (/year/month/day/) is optimal because it aligns with Hive-style partitioning, which both Spark and Azure Synapse Serverless SQL can leverage for partition pruning. Spark uses partition discovery to read only relevant directories, and Synapse Serverless SQL uses the file path metadata to filter partitions, minimizing cross-partition scans and reducing data read overhead.

Answer

All files in a single folder

Answer

/yyyy-mm-dd/ (e.g., /2023-12-25/)

Answer

Files named by date (e.g., data_20231225.parquet)

Question 7

You are designing a hybrid data storage architecture for a global e-commerce platform. Which two Azure services should you combine to achieve low-latency read access for users worldwide and durable archival storage for compliance?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is correct because it provides globally distributed, multi-region write and read capabilities with turnkey global distribution, enabling low-latency access for users worldwide via its multi-homing API. Azure Blob Storage is correct because it offers durable, tiered archival storage (e.g., Cool, Archive access tiers) at low cost, meeting compliance requirements for long-term retention of data such as transaction logs or invoices.

Answer

Azure Table Storage

Answer

Azure SQL Database

Answer

Azure Files

Question 8

Your company stores JSON documents in Azure Cosmos DB Core (SQL) API. You need to improve query performance for a common filter on the 'status' field and a sort on 'timestamp'. Which three actions should you take?

Accepted Answer

Create a composite index on (status, timestamp). A composite index on (status, timestamp) is correct because Azure Cosmos DB Core (SQL) API uses composite indexes to efficiently support queries with multiple filter and sort conditions. This index allows the query engine to first filter on the 'status' field and then sort by 'timestamp' in a single index seek, avoiding a full scan or in-memory sort. Without this composite index, the query would require a cross-partition scan or an expensive sort operation, degrading performance.

Answer

Denormalize the status field into a separate container

Answer

Disable indexing on the timestamp field

Question 9

Match each Azure storage service to its primary use case. Select the Azure storage services from the options below.

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 is used for big data analytics, Azure Cosmos DB for globally distributed, low-latency applications, Azure SQL Database for relational OLTP, and Azure Table Storage for NoSQL key-value stores. Options A through D describe the use cases, not the services, so they are not selected. The correct answers are E, F, G, and H.

Answer

Big data analytics

Answer

Globally distributed, low-latency applications

Answer

Relational OLTP

Answer

NoSQL key-value store

Question 10

A multinational corporation is designing a data lake on Azure Data Lake Storage Gen2. The data must be accessible from multiple regions with low latency, but only one region needs writable access. The solution must also comply with data residency requirements. Which two features or configurations should be implemented? (Choose two.)

Accepted Answer

Use read-access geo-redundant storage (RA-GRS). B is correct because read-access geo-redundant storage (RA-GRS) provides a secondary read-only endpoint in a paired region, enabling low-latency reads from multiple regions while maintaining a single writable primary region. This meets the data residency requirement because the secondary region is within the same geography, and RA-GRS ensures data is replicated asynchronously without allowing writes to the secondary.

Answer

Enable hierarchical namespace on the storage account

Answer

Configure geo-replication with manual failover

Answer

Use Azure Data Lake Storage Gen2 with locally redundant storage (LRS)

Question 11

Drag and drop the steps to configure Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2 into the correct order.

Accepted Answer

Set up the storage account and data, then create the serverless SQL pool, then create a database, then create an external data source referencing the storage, and finally create an external file format and external table.. First, set up the storage account and data. Then create the serverless SQL pool, a database, external data source referencing the storage, and finally external file format and external table to query.

Answer

Create the serverless SQL pool, then set up the storage account and data, then create a database, then create an external data source, and finally create an external table.

Answer

Create a database, then create an external data source, then create an external file format, then create an external table, and finally set up the storage account and data.

Answer

Set up the storage account and data, then create a database, then create an external data source, then create an external file format, then create an external table, and finally create the serverless SQL pool.

Question 12

Drag and drop the steps to configure Azure Databricks auto-scaling cluster for ETL workloads into the correct order.

Accepted Answer

Create Azure Databricks workspace, then configure cluster auto-scaling settings, then select runtime version, then attach libraries, then set cluster policies.. First create the workspace, then the cluster with auto-scaling settings, choose runtime, attach libraries, and set policies.

Answer

Create Azure Databricks workspace, then select runtime version, then configure cluster auto-scaling settings, then attach libraries, then set cluster policies.

Answer

Configure cluster auto-scaling settings, then create Azure Databricks workspace, then select runtime version, then attach libraries, then set cluster policies.

Answer

Create Azure Databricks workspace, then configure cluster auto-scaling settings, then attach libraries, then select runtime version, then set cluster policies.

Question 13

Drag and drop the steps to implement Azure Data Lake Storage Gen2 lifecycle management to move data to cool and archive tiers into the correct order.

Accepted Answer

Create Azure Data Lake Storage Gen2 account, then define a lifecycle management rule with conditions and actions, then apply the rule to the account, then verify data movement.. After creating the account, define a lifecycle rule with conditions and actions, then apply it.

Answer

Define a lifecycle management rule, then create an Azure Data Lake Storage Gen2 account, then apply the rule, then verify data movement.

Answer

Create Azure Data Lake Storage Gen2 account, then apply a lifecycle management rule, then define the rule conditions and actions, then verify data movement.

Answer

Define a lifecycle management rule, then apply it to the account, then create the Azure Data Lake Storage Gen2 account, then verify data movement.

Question 14

Match each data storage format to its characteristic.

Accepted Answer

Parquet: Columnar storage format, efficient for analytics and compression. In Azure data engineering, Parquet and ORC are columnar formats ideal for analytics, while Avro is row-based with embedded schema, and JSON is a semi-structured text format. Common confusions involve mixing columnar and row-oriented characteristics.

Answer

JSON: Columnar storage optimized for compression

Answer

ORC: Row-oriented format with schema in each file

Question 15

Which TWO Azure services can be used to implement a data lake architecture for storing structured, semi-structured, and unstructured data?

Accepted Answer

Azure Blob Storage with hierarchical namespace. Azure Data Lake Storage Gen2 (option E) is Microsoft's purpose-built data lake solution, built on Blob Storage with a hierarchical namespace, and it natively stores structured, semi-structured, and unstructured data at petabyte scale, making it the canonical answer for a data lake architecture. Azure Blob Storage with hierarchical namespace (option A) is technically the same underlying capability as ADLS Gen2 — enabling a true directory/file hierarchy, POSIX-like ACLs, and efficient rename/delete operations — so it also satisfies the requirement to serve as a data lake store. Azure SQL Database (B) is a relational engine for structured data only and cannot natively hold unstructured or semi-structured content as a lake store. Azure Synapse Analytics (C) is an analytics workspace and query engine that reads from a lake rather than being the storage layer itself. Azure Cosmos DB (D) is a globally distributed NoSQL database for operational, mostly structured/semi-structured data, not a data lake storage service.

Answer

Azure SQL Database

Answer

Azure Synapse Analytics

Answer

Azure Cosmos DB