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

Question 1

You are designing a data storage solution for a global e-commerce company. The company's analytics team uses Azure Synapse Serverless SQL to query Parquet files in ADLS Gen2. The data is partitioned by year, month, and day. The team frequently runs queries that aggregate sales by product category across the last 30 days. However, the queries are slow and scanning too much data. What should you do to improve performance?

Accepted Answer

Create a view that uses explicit partition pruning by filtering on year, month, and day in the WHERE clause.. Option C is correct because creating a view with explicit partition pruning by filtering on year, month, and day in the WHERE clause forces Synapse Serverless SQL to read only the relevant partitions (the last 30 days) instead of scanning the entire dataset. This reduces the amount of data scanned, directly improving query performance and lowering cost.

Answer

Increase the number of files per partition to improve parallelism.

Answer

Convert the Parquet files to CSV format to reduce file size.

Answer

Add a new partition by product category to the folder structure.

Question 2

Your company uses Azure Cosmos DB for NoSQL to store user profiles. The application frequently reads profiles by user ID (the partition key). Occasionally, the application needs to query by email address, which is not part of the partition key. What should you do to optimize the occasional queries by email?

Accepted Answer

Create a secondary (composite) index on the email field.. Creating a secondary index on the email field allows Azure Cosmos DB for NoSQL to efficiently serve queries filtering by email without scanning all partitions. Since email is not the partition key, a secondary index (specifically a composite index if needed for multi-field queries, or a single-field index) enables index-based lookup across all physical partitions, optimizing the occasional query without redesigning the data model.

Answer

Change the partition key to the email field.

Answer

Denormalize the data by storing a copy of the email in the partition key.

Answer

Use the Azure Cosmos DB change feed to maintain a separate container keyed by email.

Question 3

Which TWO factors should you consider when choosing between Azure SQL Database and Azure Cosmos DB for a transactional workload that requires low-latency reads and writes globally?

Accepted Answer

Consistency models (strong, bounded staleness, session, eventual, consistent prefix).. Option C is correct because Azure Cosmos DB offers multiple well-defined consistency models (strong, bounded staleness, session, consistent prefix, and eventual) that allow you to tune the trade-off between consistency and latency for globally distributed workloads. For a transactional workload requiring low-latency reads and writes globally, choosing the appropriate consistency model (e.g., session or eventual) can significantly reduce write latency by avoiding the overhead of synchronous replication required for strong consistency. This flexibility is a key factor when designing for global distribution with multi-region writes.

Answer

ACID transaction support across multiple documents.

Answer

Support for secondary indexes.

Answer

Ability to run stored procedures.

Question 4

Which THREE statements are true about partitioning in Azure Synapse Analytics dedicated SQL pool?

Accepted Answer

Partition switching can be used to quickly load data into a table.. Option A is correct because partition switching in Azure Synapse Analytics dedicated SQL pool allows you to quickly load data into a table by switching a partition from a staging table into the target table. This operation is metadata-only and does not require data movement, making it highly efficient for incremental data loads.

Answer

Partitions are automatically aligned with distributions.

Answer

Partitioning is only supported on tables with clustered rowstore indexes.

Question 5

Which TWO Azure services can be used to implement a data lakehouse architecture with Delta Lake?

Accepted Answer

Azure Databricks. Azure Databricks is correct because it provides a unified analytics platform with native Delta Lake support, enabling ACID transactions, schema enforcement, and time travel on data lakes. Delta Lake is an open-source storage layer that brings reliability to data lakes, and Azure Databricks is the primary service for running Delta Lake workloads at scale.

Answer

Azure Data Factory

Answer

Azure Cosmos DB

Answer

Azure SQL Database

Question 6

Refer to the exhibit. You are reviewing an Azure Cosmos DB for NoSQL container configuration. The container stores customer orders. The application frequently queries orders by orderId. However, these queries are consuming high RUs and are slow. What is the most likely cause?

Accepted Answer

The unique key on orderId does not index the field; queries by orderId are cross-partition.. Option A is correct because the unique key constraint on `orderId` does not automatically create an index for that field; it only enforces uniqueness. Without an explicit indexing policy that includes `orderId`, queries filtering by `orderId` must perform a cross-partition scan, which consumes high RUs and is slow. In Azure Cosmos DB for NoSQL, queries that cannot be served from a single physical partition due to missing index or partition key mismatch result in fan-out across all partitions, dramatically increasing RU consumption.

Answer

The _etag field is excluded from indexing, causing high RU for queries that include it.

Answer

The indexing mode is set to consistent, which causes high RU usage.

Answer

The container is provisioned with only 400 RU/s, which is too low for the workload.

Question 7

You are a data engineer at a healthcare analytics company. The company stores patient records in an Azure Data Lake Storage Gen2 account organized by /patient/{patientId}/year={yyyy}/month={MM}/day={dd}/*.parquet. There are 10,000 patients, and each patient has about 1 GB of data per year. The data is used by data scientists who run ad-hoc queries using Azure Synapse Serverless SQL. They complain that queries scanning multiple patients over the last year take too long and consume too much data. They often need to filter by patientId and a date range. You need to improve query performance and reduce the amount of data scanned. You cannot change the folder structure because it is used by other processes. What should you do?

Accepted Answer

Create external tables in Synapse Serverless SQL that use the folder structure as partitions, and ensure queries filter on year, month, and day.. Option D is correct because creating external tables in Synapse Serverless SQL with the existing folder structure as partitions allows the query engine to perform partition elimination. When queries filter on year, month, and day, Synapse Serverless SQL will only scan the relevant folders, drastically reducing data scanned and improving performance. This approach does not require changing the folder structure, which is used by other processes.

Answer

Reorganize the folder structure to /year={yyyy}/month={MM}/day={dd}/patientId={patientId}/*.parquet.

Answer

Convert the Parquet files to CSV format to improve compression and reduce file size.

Answer

Create views that aggregate data by patient and date, and instruct data scientists to query the views.

Question 8

You are designing a near real-time analytics solution for a retail company. The company has a transactional database in Azure SQL Database that records sales transactions. The data must be available in Azure Synapse Analytics dedicated SQL pool for reporting with less than 15 minutes of latency. The data volume is about 10 GB per day. You need to design the data ingestion pipeline. You also need to ensure that the pipeline can handle schema changes (e.g., new columns added to the source table) without manual intervention. Which approach should you use?

Accepted Answer

Use Azure Data Factory with a change data capture (CDC) mapping data flow to read changes from SQL Database and write to Synapse dedicated SQL pool. Enable schema drift to handle new columns.. Option B is correct because Azure Data Factory's mapping data flows support Change Data Capture (CDC) to incrementally load only changed rows from Azure SQL Database, meeting the <15-minute latency requirement for 10 GB/day. The 'Enable schema drift' option in mapping data flows automatically handles new columns added to the source table without manual intervention, which is essential for schema evolution.

Answer

Use Azure Databricks with Auto Loader to read from SQL Database and write to Synapse using Delta Lake.

Answer

Use Azure Data Share to share the SQL Database tables with Synapse and schedule snapshots every 15 minutes.

Answer

Use Azure Synapse Pipelines with a copy activity to perform a full load every 15 minutes.

Question 9

You are a data engineer at a financial services company. The company uses Azure Cosmos DB for NoSQL to store customer transaction data. The data is partitioned by customerId. The application team needs to run analytical queries that aggregate transactions by date across all customers. These queries are currently slow and consume high RUs. You need to enable faster analytical queries without impacting the transactional workload. What should you do?

Accepted Answer

Enable the Azure Cosmos DB analytical store (Synapse Link) and query the data using Azure Synapse Serverless SQL.. Option D is correct because enabling the Azure Cosmos DB analytical store (Synapse Link) creates a separate column-oriented store optimized for large-scale analytical queries without consuming RUs from the transactional workload. By querying this analytical store using Azure Synapse Serverless SQL, you can run fast aggregations across all customers by date while the transactional container remains unaffected.

Answer

Increase the provisioned RU/s on the container to handle both transactional and analytical queries.

Answer

Change the partition key to /date to optimize for analytical queries.

Answer

Create a materialized view using the change feed and store aggregated data in a separate container.

Question 10

You are designing a data storage solution for a retail company that needs to store semi-structured JSON data from IoT sensors. The data is ingested continuously and must support both real-time analytics and batch processing. Which Azure data store should you recommend?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical file system with the scalability and low cost of Azure Blob Storage, making it ideal for storing semi-structured JSON data from IoT sensors. It supports both real-time analytics (via services like Azure Stream Analytics or Apache Spark) and batch processing (via tools like Azure Data Factory or PolyBase) without data movement, and it natively handles JSON files with schema-on-read capabilities.

Answer

Azure Blob Storage

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Question 11

Your team is migrating an on-premises SQL Server data warehouse to Azure Synapse Analytics. The source data includes fact tables and dimension tables with complex relationships. You need to design the storage in Azure Synapse to minimize query latency for star schema queries. Which distribution and index strategy should you use for the fact table?

Accepted Answer

Hash distribution on the most joined dimension key with clustered columnstore index. Hash distribution on the most joined dimension key ensures that rows with the same key value are co-located on the same distribution, minimizing data movement during star schema joins. A clustered columnstore index provides high compression and batch-mode processing, which significantly reduces query latency for analytical workloads in Azure Synapse.

Answer

Round-robin distribution with clustered columnstore index

Answer

Replicated distribution with clustered columnstore index

Answer

Hash distribution on a dimension key with heap index

Question 12

You need to store log data from multiple Azure services in a single location for long-term retention and cost-effective querying. The data is append-only and rarely modified. Which storage solution should you use?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is optimized for append-only, rarely modified data like logs, offering hierarchical namespace, POSIX-like ACLs, and cost-effective tiered storage (hot/cool/archive). It supports high-throughput querying via Azure Synapse, Athena, or Spark, making it ideal for long-term retention and analytics on immutable log data.

Answer

Azure SQL Database

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Question 13

You are designing a data storage solution for a healthcare company that must comply with HIPAA. The solution needs to store structured patient records and unstructured medical images. Data must be encrypted at rest and in transit. Which TWO storage solutions meet these requirements?

Accepted Answer

Azure Blob Storage. Azure Blob Storage supports storing unstructured data like medical images and offers encryption at rest via Storage Service Encryption (SSE) and in transit via HTTPS/TLS. It is HIPAA-eligible when configured with appropriate access controls and logging, making it suitable for the unstructured image component of the solution.

Answer

Azure Redis Cache

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Question 14

Your company uses Azure Synapse Analytics for a data warehouse. The fact table is 500 GB and distributed by hash on CustomerID. You notice that queries joining the fact table with the Customer dimension table are slow due to data movement. The Customer dimension table is 10 GB. Which THREE actions should you take to improve query performance?

Accepted Answer

Change the fact table distribution to hash on CustomerID. Option C is correct because the fact table is already distributed by hash on CustomerID, which is the join key with the Customer dimension table. This co-locates matching rows on the same compute node, eliminating data movement during joins. Keeping this distribution is essential for performance; changing it would break co-location and increase shuffle overhead.

Answer

Use heap index on the fact table

Answer

Change the fact table distribution to round-robin

Question 15

You need to design a storage solution for IoT device telemetry data that will be queried by time range. The data is append-only and arrives at high velocity. Which TWO features should you use to optimize query performance and reduce costs?

Accepted Answer

Store data in columnar format (e.g., Parquet). Columnar formats like Parquet store data by column rather than by row, which significantly reduces I/O when querying only a subset of columns (common in time-range queries). This compression and column pruning directly lowers storage costs and speeds up scan-heavy analytical queries on append-only IoT telemetry data.

Answer

Create indexes on all columns

Answer

Enable row-level security

Answer

Enable geo-redundant storage