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

Question 1

You need to store historical sales data for 10 years with infrequent queries. The storage cost must be minimized while retaining the ability to query using Azure Synapse serverless SQL pool. Which storage tier should you use?

Accepted Answer

Azure Storage Cool tier.. The Cool tier is the correct choice because it provides low-cost storage for data that is infrequently accessed (e.g., historical sales data spanning 10 years) while still supporting immediate read access via Azure Synapse serverless SQL pool. Unlike the Archive tier, Cool tier data is online and can be queried without the need for time-consuming rehydration, making it suitable for infrequent but on-demand analytical queries.

Answer

Azure Storage Archive tier.

Answer

Azure Storage Premium tier.

Answer

Azure Storage Hot tier.

Question 2

A financial services company needs to store transaction data for audit purposes. The data must be immutable and cannot be modified or deleted for 7 years. Which Azure storage feature should be used?

Accepted Answer

Azure Blob Storage immutability policy (time-based retention).. Azure Blob Storage immutability policy with time-based retention ensures that blobs cannot be modified or deleted for a specified retention period (e.g., 7 years). This meets the audit requirement for immutable storage by locking the data at the storage level, preventing any writes or deletes until the retention interval expires. The policy is enforced at the container level and applies to all blobs within, making it the correct choice for regulatory compliance.

Answer

Azure Blob Storage soft delete.

Answer

Azure Blob Storage versioning.

Answer

Azure Files share snapshots.

Question 3

Which TWO actions should you take to optimize query performance in Azure Synapse Analytics dedicated SQL pool when working with large fact tables?

Accepted Answer

Create statistics on columns used in WHERE clauses.. Option C is correct because creating statistics on columns used in WHERE clauses enables the Azure Synapse Analytics dedicated SQL pool query optimizer to generate more accurate cardinality estimates, leading to better join strategies and index selections. Without up-to-date statistics, the optimizer may choose suboptimal plans, especially for large fact tables where data distribution skew is common.

Answer

Use replicated distribution for the fact table.

Answer

Use round-robin distribution to evenly distribute data.

Answer

Use clustered index instead of columnstore index.

Question 4

Which THREE components are required to implement a modern data warehouse architecture on Microsoft Azure using Azure Synapse Analytics?

Accepted Answer

Microsoft Purview for data governance and lineage.. Options A, C, and D are correct because a modern data warehouse typically uses a data lake for storage (ADLS Gen2), a compute engine for analytics (Synapse SQL pool), and a metadata catalog (Purview). Option B is wrong because Power BI is a visualization tool, not required. Option E is wrong because Azure Analysis Services is optional for semantic modeling.

Answer

Power BI as the data visualization layer.

Answer

Azure Analysis Services for semantic modeling.

Question 5

Which TWO Azure services can be used to ingest streaming data into Azure Synapse Analytics?

Accepted Answer

Azure Stream Analytics.. Azure Stream Analytics is correct because it is a fully managed stream processing engine that can ingest streaming data from sources like Azure Event Hubs and output directly to Azure Synapse Analytics (via SQL pool or dedicated SQL pool). It enables real-time analytics on streaming data before landing it in Synapse for further analysis.

Answer

Azure Databricks Auto Loader.

Answer

Azure Data Factory.

Answer

Azure Logic Apps.

Question 6

You are reviewing an ARM template snippet for an Azure Blob Storage container. What is the effect of this configuration?

Accepted Answer

It enforces a 7-year immutable retention policy allowing appends.. The ARM template snippet configures a time-based retention policy on the container with a retention period of 2555 days (7 years) and sets `allowProtectedAppendWrites` to true. This enables an immutable storage policy that prevents deletion or modification of existing blobs but allows new append blocks to be added to append blobs, making option C correct.

Answer

It enables legal hold on the container for 2555 days.

Answer

It blocks append writes to blobs in the container.

Answer

It sets a 3-year retention policy blocking any modifications.

Question 7

You are examining a T-SQL script that creates an external table in Azure Synapse serverless SQL pool. The query SELECT * FROM dbo.Sales returns zero rows, but the folder /year=2024/ in ADLS Gen2 contains Parquet files. What is the most likely cause?

Accepted Answer

The credential used to access ADLS Gen2 does not have sufficient permissions.. Option A is correct because the most common reason for SELECT * FROM an external table returning zero rows despite data existing in the underlying ADLS Gen2 folder is that the serverless SQL pool lacks the necessary permissions to read the Parquet files. The credential used in the external data source must have at least 'Storage Blob Data Reader' role on the storage account or the container, and the identity (e.g., SAS token, service principal, or managed identity) must be correctly configured. Without this, the query executes successfully but returns no rows because the pool cannot access the data.

Answer

The serverless SQL pool does not support reading Parquet files.

Answer

The external table definition is missing the SCHEMA_NAME parameter.

Answer

The DATA_COMPRESSION setting is incompatible with Parquet files.

Question 8

You are a data engineer for a large e-commerce company. The company uses Azure Synapse Analytics dedicated SQL pool as its enterprise data warehouse. A new business requirement mandates that the Sales fact table, which contains 2 billion rows, must support real-time analytics with a maximum query latency of 1 second for aggregations on the most recent 24 hours of data. The table is currently hash-distributed on CustomerID and partitioned monthly by SaleDate. The current query performance for recent data is slow due to full partition scans. The data is ingested via Azure Event Hubs and processed by Azure Stream Analytics, which writes to staging tables every minute. You need to redesign the storage to meet the latency requirement while minimizing cost and maintaining data integrity. Which approach should you take?

Accepted Answer

Create a separate staging table for the last 24 hours of data with round-robin distribution and a clustered columnstore index. After each batch load, merge the staging table into the main partitioned Sales table. Queries for recent data should target the staging table.. Option C is correct because it isolates the hot (recent 24-hour) data into a separate staging table with a clustered columnstore index, which is optimized for fast aggregations and high compression. This avoids full partition scans on the 2-billion-row main table, and merging into the partitioned table after each batch load maintains data integrity without requiring expensive repartitioning or additional dedicated SQL pools.

Answer

Increase partition granularity to hourly partitions for the Sales table.

Answer

Change the Sales table to use a clustered index on SaleDate and a round-robin distribution.

Answer

Provision a second dedicated SQL pool optimized for real-time queries and replicate the recent data there.

Question 9

A company is designing a data lake in Azure Data Lake Storage Gen2 (ADLS Gen2) to store IoT sensor data from millions of devices. The data is ingested in Parquet format, partitioned by date and device ID. The analytics team frequently queries the last 30 days of data for specific device types. Which partition strategy minimizes query cost and optimizes performance?

Accepted Answer

Partition by date (yyyy/MM/dd) first, then by device type (e.g., sensor_type=temp).. Option C is correct because partitioning by date first enables efficient partition pruning for the common query pattern (last 30 days), and then by device type further filters the data within those date partitions. In ADLS Gen2, queries using partition elimination skip entire directories, reducing the amount of data scanned and minimizing query cost. This strategy aligns with the typical query workload, where date-range filtering is the most selective predicate.

Answer

Partition by device ID first, then by date.

Answer

Partition by device ID only, with a separate directory for each device.

Answer

Partition by device type only, with a directory for each type.

Question 10

You are designing a solution to store semi-structured JSON logs from a web application in Azure Cosmos DB. The logs are written once and rarely read. The application writes up to 10,000 documents per second, and each document is about 2 KB. You need to minimize RU/s cost. Which API and indexing policy should you choose?

Accepted Answer

Azure Cosmos DB for MongoDB with a custom indexing policy that only includes a wildcard index on _id and disables automatic indexing.. Option D is correct because Azure Cosmos DB for MongoDB with a custom indexing policy that disables automatic indexing and only includes a wildcard index on _id minimizes RU/s cost for write-heavy, rarely read workloads. Each write operation consumes RUs proportional to the number of indexed paths; by eliminating automatic indexing of all fields, you drastically reduce the per-document write RU charge. The MongoDB API supports this fine-grained indexing control, making it ideal for high-throughput ingestion of semi-structured JSON logs.

Answer

Azure Cosmos DB for Cassandra with default indexing policy.

Answer

Azure Cosmos DB for Table with default indexing policy.

Answer

Azure Cosmos DB for NoSQL with default indexing policy (automatic indexing of all fields).

Question 11

Your company uses Azure Synapse Analytics dedicated SQL pool to store a fact table with 2 billion rows. You need to improve query performance for a workload that frequently aggregates sales by date and product category. Which distribution and index type should you use?

Accepted Answer

Hash-distribute on product_category and use a clustered columnstore index.. Hash-distributing on product_category ensures that rows with the same product category are co-located on the same distribution, enabling local aggregation without data movement. A clustered columnstore index provides high compression and batch-mode processing, which is ideal for large fact tables and analytical workloads that aggregate millions of rows by columns like date and product_category.

Answer

Replicate the table and use a clustered index.

Answer

Round-robin distribution and a clustered columnstore index.

Answer

Hash-distribute on date and use a clustered index.

Question 12

You are designing a change data capture (CDC) pipeline to ingest incremental changes from an on-premises SQL Server database into Azure Data Lake Storage Gen2. The pipeline must run every 5 minutes and handle high-volume DML changes. Which Azure service should you use to capture the changes with low latency?

Accepted Answer

Azure Data Factory with a change data capture (CDC) source in the mapping data flow.. Azure Data Factory's mapping data flow includes a native CDC source that can connect to SQL Server and capture incremental DML changes (inserts, updates, deletes) with low latency. This approach uses change tracking or change data capture features in SQL Server to identify changes, and the pipeline can run every 5 minutes to meet the high-volume requirement without custom coding.

Answer

Azure Data Share to share the SQL Server data and capture changes.

Answer

Azure Synapse Pipelines with a copy activity that uses a query to capture changes.

Answer

Azure Databricks with Auto Loader and Delta Live Tables to capture changes.

Question 13

You have an Azure Synapse Analytics dedicated SQL pool with a large fact table partitioned by month. You notice that queries filtering on a specific month still scan all partitions. The table has a clustered columnstore index. What is the most likely cause?

Accepted Answer

The columnstore index has a large number of open rowgroups that are not compressed.. Option C is correct because when a clustered columnstore index has a large number of open rowgroups (i.e., rowgroups that have not yet been compressed into columnstore segments), queries may fall back to the delta store, which stores data in a B-tree-like structure. This bypasses partition elimination because the delta store does not maintain the same metadata for partition pruning, causing all partitions to be scanned. Compressing open rowgroups into closed, compressed rowgroups restores proper partition elimination behavior.

Answer

The table is hash-distributed on a column other than date, preventing partition elimination.

Answer

The statistics on the date column are outdated or missing.

Answer

The table is replicated, so partition elimination does not apply.

Question 14

You need to store streaming data from Azure Event Hubs into Azure Data Lake Storage Gen2 in near real-time. The data should be stored in Avro format with a folder structure: /raw/{eventhub}/{yyyy}/{MM}/{dd}/{HH}/{mm}. Which Azure service should you use to ingest the data?

Accepted Answer

Azure Stream Analytics with a job that reads from Event Hubs and writes to ADLS Gen2.. Azure Stream Analytics is the correct choice because it natively supports reading from Event Hubs and writing to ADLS Gen2 with built-in time-based partitioning into the exact folder structure /raw/{eventhub}/{yyyy}/{MM}/{dd}/{HH}/{mm}. It provides near real-time processing with sub-minute latency and can output data in Avro format directly, meeting all requirements without additional code or orchestration.

Answer

Event Hubs Capture feature to automatically capture events to ADLS Gen2.

Answer

Azure Data Factory with a tumbling window trigger to copy data from Event Hubs every 5 minutes.

Answer

Azure Databricks with Auto Loader to read from Event Hubs and write to ADLS Gen2.

Question 15

You are designing a solution to store large amounts of log data that is written once and accessed rarely. The data must be retained for 7 years for compliance. After 30 days, the data should be moved to a lower-cost storage tier. After 1 year, the data should be archived. Which Azure Storage lifecycle management policy should you implement for an Azure Data Lake Storage Gen2 account?

Accepted Answer

Transition to cool tier after 30 days; transition to archive tier after 365 days; delete after 2555 days (7 years).. Option B is correct because it aligns with the specified lifecycle requirements: transition to cool tier after 30 days for cost savings, transition to archive tier after 365 days for long-term retention, and delete after 2555 days (7 years) for compliance. Azure Data Lake Storage Gen2 supports lifecycle management policies that automate tier transitions and deletion based on age, ensuring data is moved to lower-cost storage as access patterns change.

Answer

Transition to cool tier after 30 days; delete after 7 years.

Answer

Transition to archive tier after 30 days; delete after 7 years.

Answer

Transition to cool tier after 30 days; transition to cool tier again after 365 days.