DP-900 Practice Test 10 — 10 Questions

Question 1

A global social media platform stores user profile images (JPEG) and activity logs in JSON format. The logs have varying structures based on the type of activity. The application requires low-latency reads of images from any region and the ability to query logs using SQL-like syntax. Which Azure data storage solution should they use for each data type?

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

Accepted Answer

Azure Blob Storage with a CDN for images and Azure Cosmos DB (SQL API) for logs. Azure Blob Storage is optimized for storing large binary objects like JPEG images, and integrating it with Azure CDN ensures low-latency reads globally by caching content at edge nodes. Azure Cosmos DB with the SQL API provides native support for querying JSON documents with varying schemas using SQL-like syntax, making it ideal for the activity logs.

Answer

Azure Table Storage for images and Azure Cosmos DB (Table API) for logs

Answer

Azure Files for images and Azure SQL Database for logs

Answer

Azure Disk Storage for images and Azure Cosmos DB (MongoDB API) for logs

Question 2

A data engineer is designing a data lake architecture in Azure. They plan to first ingest raw data from various sources into a landing zone in Azure Data Lake Storage Gen2. Then they will clean, validate, and deduplicate that data in a second zone. Finally, they will create aggregated, business-ready datasets in a third zone for analysts. This layered approach is known as which architecture?

Accepted Answer

Medallion architecture. The medallion architecture (bronze, silver, gold) is a layered data lake design pattern where raw data lands in the bronze zone, is cleaned and deduplicated in the silver zone, and aggregated into business-ready datasets in the gold zone. This directly matches the described three-zone ingestion, transformation, and aggregation pipeline in Azure Data Lake Storage Gen2.

Answer

Star schema

Answer

Snowflake schema

Answer

Lambda architecture

Question 3

A bank processes individual customer transactions in real-time to update account balances and also runs a nightly job that aggregates all daily transactions into summary reports for management. Which of the following best describes these two processing workloads?

Accepted Answer

OLTP for real-time transactions, OLAP for nightly reports. Real-time individual transaction processing is the hallmark of Online Transaction Processing (OLTP), which focuses on high-volume, low-latency inserts and updates to maintain current account balances. The nightly aggregation of daily transactions into summary reports is a classic Online Analytical Processing (OLAP) workload, which involves complex queries over large historical datasets for business intelligence. These two workloads have fundamentally different performance and design requirements, making OLTP and OLAP the appropriate classifications.

Answer

Batch processing for transactions, Stream processing for reports

Answer

OLAP for transactions, OLTP for reports

Answer

ETL for transactions, ELT for reports

Question 4

A company has multiple independent databases for different business units, each with low to moderate usage and varying workload patterns. They want to consolidate these databases into a single Azure SQL Database deployment option to share resources and reduce costs, while ensuring that databases do not starve each other of resources. Which Azure SQL Database deployment option should they choose?

Accepted Answer

Elastic pool. Elastic pools are designed for exactly this scenario: multiple databases with low to moderate usage and varying workload patterns. They allow databases to share a fixed pool of resources (eDTUs or vCores) while using built-in resource governance to prevent any single database from starving others, thus optimizing cost and performance.

Answer

Single database (DTU model)

Answer

Managed Instance

Answer

Database per server (hyperscale)

Question 5

A manufacturing company stores IoT sensor data as JSON documents in Azure Cosmos DB. Each document has fields: deviceId (high cardinality, many unique values), timestamp, temperature, and humidity. The most frequent query is: 'Retrieve all readings for a specific deviceId from the last hour.' To minimize Request Unit (RU) consumption, which combination of partition key and indexing policy should be chosen?

Accepted Answer

Partition key: deviceId, Indexing: automatic on all properties. DeviceId is the most frequently filtered attribute (in the WHERE clause), making it an ideal partition key that ensures queries are scoped to a single physical partition, minimizing cross-partition fan-out. Automatic indexing on all properties allows efficient filtering on timestamp within the partition, while the index on deviceId is not strictly needed since the partition key itself routes the query, but it does not harm RU consumption significantly. This combination balances query performance and RU cost for the described workload.

Answer

Partition key: timestamp, Indexing: automatic on all properties

Answer

Partition key: deviceId, Indexing: none

Answer

Partition key: temperature, Indexing: automatic on all properties

Question 6

A social media application allows users to post updates and like posts. After a user clicks the like button, the like count must update immediately and be exactly the same for all users viewing the post. Which data consistency model best fits this requirement?

Accepted Answer

Strong consistency. Strong consistency ensures that after a write operation (like clicking the like button) completes, any subsequent read operation returns the most recent write. This guarantees that all users viewing the post see the exact same, up-to-date like count immediately. This is required for the social media scenario where the like count must be identical for all viewers without any delay.

Answer

Eventual consistency

Answer

Session consistency

Answer

Bounded staleness consistency

Question 7

Your company, Contoso Ltd., operates a global e-commerce platform. The data engineering team ingests over 10 TB of raw clickstream data daily into Azure Data Lake Storage Gen2. The data is partitioned by date and hour. Business analysts need to query this data using Azure Synapse Serverless SQL to generate daily sales reports. However, the reports are taking over 30 minutes to run, and the team needs to improve query performance without moving data to a dedicated SQL pool. You are asked to recommend a solution. Which action should you take?

Accepted Answer

Create external tables using a partition elimination strategy and ensure the data is partitioned by date.. Azure Synapse Serverless SQL can use external tables with partition elimination to skip irrelevant partitions (e.g., date/hour folders) during query execution. This reduces the amount of data scanned, directly improving query performance without moving data. Partition elimination works by filtering on the partition column (e.g., date) in the WHERE clause, allowing the query engine to read only the necessary files.

Answer

Convert the data from JSON to Parquet format and apply Snappy compression.

Answer

Use Azure Data Factory to copy the data into Azure SQL Database and create indexes.

Answer

Create a dedicated SQL pool and distribute the data across 60 distributions.

Question 8

A company uses Azure Synapse Analytics dedicated SQL pool for its data warehouse. Every night, they need to load 500 GB of new sales data from CSV files stored in Azure Data Lake Storage Gen2. The loading process must be automated, scheduled, and include error handling (e.g., skip corrupt rows and log them). Which Azure service should be used to orchestrate this load pipeline?

Accepted Answer

Azure Data Factory. Azure Data Factory (ADF) is the correct choice because it is a cloud-based ETL and data orchestration service that supports scheduled execution, error handling (e.g., skipping corrupt rows via fault tolerance settings in the Copy activity), and native integration with Azure Data Lake Storage Gen2 and Azure Synapse dedicated SQL pool. ADF can automate the nightly 500 GB load using a trigger, and its mapping data flows or Copy activity can log errors to a separate file or table, meeting the requirement for automated, scheduled, and error-tolerant ingestion.

Answer

Azure Stream Analytics

Answer

Azure HDInsight

Answer

Azure Logic Apps

Question 9

A financial application stores transactions in an Azure SQL Database table with columns: TransactionID (clustered index), AccountID, TransactionDate, Amount. Queries frequently filter on AccountID and TransactionDate together. The table contains millions of rows. Which index strategy will most improve query performance for these filters?

Accepted Answer

Nonclustered index on (AccountID, TransactionDate). Creates a nonclustered index on (AccountID, TransactionDate) that acts as a covering index for queries filtering on both columns. SQL Server can seek directly to the matching rows using the composite key order, avoiding a full table scan or key lookup. This is the most efficient strategy because the index is sorted by AccountID first, then TransactionDate, matching the query predicate exactly.

Answer

Clustered index on (AccountID, TransactionDate)

Answer

Nonclustered index on (TransactionDate)

Answer

Nonclustered index on (AccountID) INCLUDE (TransactionDate)

Question 10

A company stores historical sensor data in Azure Blob Storage. The data is accessed only a few times per year for compliance audits, but when requested, it must be available for reading within 15 minutes. The company wants to minimize storage costs. Which blob access tier should they use?

Accepted Answer

Cool. The Cool tier is the optimal choice because it balances low storage cost with the ability to retrieve data within minutes, meeting the 15-minute availability requirement. Archive would incur a retrieval delay of up to 15 hours, which violates the compliance audit SLA. Hot and Premium tiers are more expensive and unnecessary for data accessed only a few times per year.

Answer

Hot

Answer