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

Question 1

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

Accepted Answer

Azure Databricks. Azure Databricks (A) is correct because it natively supports Delta Lake as its default storage layer, providing ACID transactions, schema enforcement, and time travel on top of Parquet files in ADLS, which is the foundation of a lakehouse. Azure Synapse Analytics serverless SQL pool (D) is correct because it can query Delta Lake tables directly using OPENROWSET with FORMAT = 'DELTA', letting you build a lakehouse serving layer over the same Delta files without provisioning dedicated compute. Azure Data Factory (B) is a data integration/ETL orchestration service, not a Delta Lake compute or query engine, so it cannot itself implement a lakehouse. Azure Cosmos DB (C) is a globally distributed NoSQL database and does not store or process Delta Lake tables. Azure SQL Database (E) is a relational PaaS database engine and does not natively read or write Delta Lake format.

Answer

Azure Data Factory

Answer

Azure Cosmos DB

Answer

Azure SQL Database

Question 2

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

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

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

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 6

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 7

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 (B) is correct because it is designed for storing unstructured data such as medical images, supports server-side encryption at rest by default (AES-256) and encryption in transit via HTTPS/TLS, and can be configured with customer-managed keys and private endpoints to satisfy HIPAA safeguards. Azure SQL Database (E) is correct because it stores structured patient records in relational tables and provides Transparent Data Encryption (TDE) for data at rest plus TLS encryption for data in transit, with compliance features such as auditing, threat detection, and HIPAA/HITRUST alignment. Azure Redis Cache (A) is an in-memory cache, not a primary durable store for patient records or images, and its persistence options are not intended for compliance-grade long-term storage. Azure Table Storage (C) is a NoSQL key-attribute store that lacks the rich relational structure and compliance tooling needed for structured patient records and is not the best fit for large unstructured medical images. Azure Cosmos DB (D) is a globally distributed NoSQL database that can store structured data and encrypt at rest/in transit, but it is not the designated correct answer here because the scenario's combination of structured records plus unstructured images is best served by Azure SQL Database and Azure Blob Storage.

Answer

Azure Redis Cache

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Question 8

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 TWO actions should you take to improve query performance?

Accepted Answer

Implement partitioning on the fact table by date. Option E is correct because the Customer dimension is only 10 GB, which is well under the 2 GB recommendation per compute node for replicated tables (or the general guidance that small dimension tables should be replicated); replicating it places a full copy on every compute node, eliminating data movement when the fact table joins to it. Option D is correct because partitioning the large 500 GB fact table by date enables partition elimination, so queries filtering on date only scan relevant partitions, reducing I/O and improving performance. Option A is not appropriate because a heap index on a 500 GB fact table provides no ordering or statistics benefit for join performance and is typically used for staging tables, not large distributed fact tables. Option B is wrong because round-robin distribution spreads rows evenly but does not co-locate join keys, so it would actually increase data movement for joins on CustomerID. Option C is incorrect because the fact table is already distributed by hash on CustomerID, so changing it to hash on CustomerID is a no-op and would not address the data movement issue.

Answer

Use heap index on the fact table

Answer

Change the fact table distribution to round-robin

Answer

Change the fact table distribution to hash on CustomerID

Question 9

You are reviewing an Azure Data Factory dataset JSON definition for a data lake. The dataset is used in a copy activity that loads sales data into Azure Data Lake Storage Gen2. The pipeline runs successfully, but you notice that the output file always overwrites the previous file with the name 'sales.parquet' regardless of the folderPath parameter. What is the most likely cause?

Accepted Answer

The fileName property is hardcoded and not parameterized. The dataset's fileName property is hardcoded to 'sales.parquet', which means every pipeline run writes to the same file, overwriting it regardless of the folderPath parameter. In Azure Data Factory, if fileName is static and not parameterized, the copy activity will always target that exact file name, even if folderPath changes dynamically.

Answer

The folderPath parameter is not being evaluated correctly

Answer

The linkedServiceName is not correctly configured

Answer

The format type is incorrect; it should be 'Parquet' instead of 'ParquetFormat'

Question 10

Your company is building a real-time analytics solution for monitoring manufacturing equipment. Sensors send JSON data every second to an Azure Event Hubs instance. The data must be stored in Azure Data Lake Storage Gen2 in Parquet format, partitioned by date and hour. You use Azure Stream Analytics to read from Event Hubs and write to ADLS Gen2. Currently, the output is writing many small Parquet files (under 1 MB each), which is causing performance issues when reading the data. You need to optimize the output to produce fewer, larger files while maintaining low latency. What should you do?

Accepted Answer

Increase the 'Maximum events per batch' setting in the Stream Analytics output to ADLS Gen2. Increasing the 'Maximum events per batch' setting in the Stream Analytics output to ADLS Gen2 allows more events to be accumulated before writing a file, resulting in fewer, larger Parquet files. This directly addresses the small-file problem while maintaining low latency, as the batching is time-bound and does not introduce excessive delay.

Answer

Partition the output by minute instead of hour to distribute data more

Answer

Change the output format to Avro to improve compression

Answer

Decrease the 'Maximum events per batch' setting to reduce latency

Question 11

Your company uses Azure Synapse Analytics for its enterprise data warehouse. The main fact table, OrdersFact, is distributed using hash on OrderID. It has 10 billion rows. The table is partitioned by month. Recently, the data engineering team added a new column 'OrderStatus' that is used in many queries with filters like 'WHERE OrderStatus = 'Shipped''. These queries are scanning the entire table because the partition pruning is not effective. You need to improve query performance for these status-based queries without redesigning the entire table. What should you do?

Accepted Answer

Create a non-clustered columnstore index on OrderStatus. Creating a non-clustered columnstore index on OrderStatus allows SQL Server to perform batch mode processing and predicate pushdown, enabling efficient rowgroup elimination without requiring a full table scan. This index stores OrderStatus in a compressed, columnar format that can be quickly filtered, and it does not require redesigning the existing hash distribution or monthly partitioning.

Answer

Repartition the table by OrderStatus

Answer

Change the distribution key to include OrderStatus

Answer

Create a materialized view that aggregates by OrderStatus and month

Question 12

Your organization is implementing a data lake using Azure Data Lake Storage Gen2. You have a folder structure like '/data/landing/' for raw data and '/data/curated/' for cleaned data. The data is ingested daily from various sources. You need to ensure that data in the curated zone is immutable and cannot be modified or deleted by anyone, including administrators, for compliance reasons. However, data in the landing zone should be modifiable. What should you do?

Accepted Answer

Enable immutable storage with a time-based retention policy on the curated zone container. Azure Data Lake Storage Gen2 supports immutable storage at the container level, which enforces a time-based retention policy that prevents any data from being modified or deleted—even by administrators—until the retention period expires. This directly meets the compliance requirement for the curated zone, while leaving the landing zone container unaffected and modifiable.

Answer

Remove the 'Delete' permission from the storage account key

Answer

Set ACLs on the curated zone folder to deny write and delete for all users

Answer

Use Azure RBAC to deny delete and write permissions for all users on the curated zone folder

Question 13

You are a data engineer for a gaming company that uses Azure Data Lake Storage Gen2. The data lake stores player event data in JSON format. The data is organized by date and event type. The analytics team frequently runs queries that filter by player ID to analyze individual player behavior. These queries are slow because they scan entire daily partitions. You need to improve the performance of queries that filter by player ID without restructuring the entire data lake. The data is stored as JSON files. What should you do?

Accepted Answer

Convert the data from JSON to Parquet format and partition by player ID. Converting JSON to Parquet enables columnar storage, which significantly reduces I/O by reading only the columns needed for queries. Partitioning by player ID further improves performance by allowing partition elimination, so queries filter only the relevant partitions instead of scanning entire daily partitions. This approach directly addresses the slow queries without restructuring the entire data lake.

Answer

Compress the JSON files using gzip to reduce I/O

Answer

Create indexes on the player ID field in the data lake

Answer

Repartition the data by hour to reduce the data scanned per partition

Question 14

You are designing a data storage solution for a retail company that needs to store semi-structured IoT sensor data from thousands of devices. The data is ingested in near real-time, and queries will involve filtering by device ID and timestamp. The solution must minimize storage costs while supporting interactive queries. Which TWO Azure data storage options are most appropriate?

Accepted Answer

Azure Blob Storage. Azure Blob Storage (C) is correct because it is a low-cost object store ideal for large volumes of semi-structured IoT data, and its append blobs and tiering (hot/cool/archive) support near-real-time ingestion while minimizing storage costs for interactive queries. Azure Data Lake Storage Gen2 (D) is correct because it builds hierarchical namespace and POSIX-like semantics on Blob Storage, enabling efficient filtering by device ID and timestamp partitions at very low cost while remaining compatible with interactive analytics engines. Azure Table Storage (A) is not appropriate because it is a NoSQL key-value store with limited query flexibility and no native timestamp-range optimization for this scale. Azure Cosmos DB (B) is not appropriate because, although it supports fast queries, its RU-based pricing is significantly more expensive than Blob or ADLS Gen2 for cost-sensitive IoT storage. Azure SQL Database (E) is not appropriate because it is a relational database with higher cost per GB and is not optimized for massive semi-structured IoT ingestion.

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Answer

Azure SQL Database

Question 15

You are a data engineer at a financial services company. The company uses Azure Synapse Analytics with a dedicated SQL pool for its data warehouse. The current table 'FactTransactions' is 2 TB and uses round-robin distribution. Query performance is poor for queries that frequently filter on 'CustomerID' and join with a 'DimCustomer' table (10 GB, replicated). You need to redesign the table to improve query performance while minimizing data movement during queries. The solution must also support incremental data loading with minimal overhead. You cannot change the storage size limit or add more DWU. What should you do?

Accepted Answer

Recreate the table using hash distribution on CustomerID and use CTAS for incremental loads.. Hash distribution on CustomerID ensures that rows with the same CustomerID are co-located on the same distribution, which eliminates data movement during joins with the replicated DimCustomer table. Using CTAS (CREATE TABLE AS SELECT) for incremental loads allows you to efficiently rebuild the table with minimal overhead by loading only new data into a staging table, then swapping partitions or using CTAS to replace the target table without blocking reads.

Answer

Replicate the FactTransactions table to all distributions.

Answer

Keep round-robin distribution but add indexes on CustomerID and TransactionDate.

Answer

Change distribution to hash on TransactionDate and partition by month.