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

Question 1

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 2

You are designing a data storage solution for a global IoT application that ingests millions of events per second. The data is write-heavy with occasional reads for real-time dashboards. Which Azure storage option and configuration would provide the lowest latency writes with high throughput?

Accepted Answer

Azure Cosmos DB with multi-region writes and eventual consistency. Azure Cosmos DB with multi-region writes and eventual consistency provides the lowest latency writes for a global IoT application because it allows each region to accept writes independently without cross-region coordination, and eventual consistency removes the need for quorum confirmations, reducing write latency. This configuration also offers high throughput by distributing write load across multiple regions, making it ideal for write-heavy, high-volume IoT scenarios where occasional reads for dashboards can tolerate stale data.

Answer

Azure Cosmos DB with single-region writes and strong consistency

Answer

Azure Data Lake Storage Gen2 with hierarchical namespace

Answer

Azure Blob Storage with hot tier and append blobs

Question 3

Which of the following are valid methods to secure data at rest in Azure Data Lake Storage Gen2? (Choose two.)

Accepted Answer

Azure Storage Service Encryption (SSE) with Microsoft-managed keys. Azure Storage Service Encryption (SSE) with Microsoft-managed keys encrypts data at rest automatically for Azure Data Lake Storage Gen2 using 256-bit AES encryption. This is enabled by default for all storage accounts, ensuring data written to disk is encrypted before being persisted, with no additional configuration required.

Answer

Azure Active Directory (Azure AD) authentication for storage accounts

Answer

Configure firewall rules to restrict IP access

Question 4

You are designing a data lake architecture using Azure Data Lake Storage Gen2. You need to optimize query performance for Azure Synapse Analytics serverless SQL. Which three design considerations should you follow? (Choose three.)

Accepted Answer

Store data in Parquet format. Parquet is a columnar storage format that reduces I/O by reading only the columns needed for a query, which significantly improves performance in Azure Synapse serverless SQL. It also supports efficient compression and encoding schemes, making it ideal for analytical workloads on Azure Data Lake Storage Gen2.

Answer

Use many small files (under 64 MB) to increase parallelism

Answer

Store data in nested folder structures for better organization

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 storage solution for IoT sensor data. The data is written thousands of times per second and requires low-latency reads for real-time dashboards. Which Azure storage solution should you use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it provides single-digit millisecond read and write latency at any scale, with automatic indexing and multi-region distribution. Its support for multiple APIs (SQL, MongoDB, Cassandra, etc.) and configurable consistency levels makes it ideal for IoT sensor data requiring high-throughput writes and low-latency reads for real-time dashboards.

Answer

Azure Blob Storage

Answer

Azure SQL Database

Answer

Azure Data Lake Storage Gen2

Question 7

Your company stores sensitive customer data in Azure SQL Database. You need to encrypt the data at rest and ensure that only your application can decrypt it, even from database administrators. What should you implement?

Accepted Answer

Always Encrypted. Always Encrypted is correct because it ensures that sensitive data is encrypted at rest and in use, and the encryption keys are stored client-side, so only the application can decrypt the data. Database administrators (DBAs) cannot access the plaintext data because they lack the column encryption keys, even though they have full administrative access to the database.

Answer

Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking

Answer

Azure Storage Service Encryption

Question 8

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 9

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 10

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 11

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 12

Which Azure storage solution is best suited for storing large volumes of unstructured data, such as log files and media files, and supports both hierarchical namespace and POSIX-like access control lists?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) combines a hierarchical namespace with POSIX-like access control lists (ACLs) on top of Azure Blob Storage. This makes it ideal for storing large volumes of unstructured data (e.g., log files, media files) while supporting fine-grained, POSIX-compliant permissions and directory-level operations that are essential for big data analytics workloads.

Answer

Azure Blob Storage

Answer

Azure Files

Answer

Azure SQL Database

Question 13

You are designing a data storage solution for a retail company that needs to store transaction data that is frequently updated and requires strong consistency. The solution must support complex queries and joins across multiple tables. Which Azure data service should you recommend?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed relational database service that provides strong consistency, supports complex queries and joins across multiple tables, and is optimized for frequently updated transaction data. It offers ACID compliance and built-in high availability, making it the ideal choice for this retail scenario.

Answer

Azure Cosmos DB

Answer

Azure Synapse Analytics

Answer

Azure Table Storage

Question 14

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 15

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.