DP-900 Practice Test 45 — 15 Questions

Question 1

A company uses Azure SQL Database for an e-commerce platform. The 'Orders' table has millions of rows with columns OrderID (primary key), CustomerID, OrderDate, and TotalAmount. Queries often filter by CustomerID (equality) and OrderDate (range). Currently, these queries are slow. Which index should be created to improve performance?

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

Accepted Answer

A nonclustered index on (CustomerID, OrderDate). The query pattern filters by CustomerID (equality) and OrderDate (range). A composite nonclustered index on (CustomerID, OrderDate) allows SQL Database to seek directly to the matching CustomerID rows and then efficiently scan the ordered OrderDate range within that partition, avoiding a full table scan or key lookup. This index order leverages the index's B-tree structure for both equality and range predicates.

Answer

A nonclustered index on OrderID

Answer

A nonclustered index on OrderDate

Answer

A clustered index on CustomerID

Question 2

You are designing a data lake architecture for a large enterprise. You need to organize data into zones (raw, curated, and analytics) and enforce data lineage tracking. Which Azure service should you use to catalog and govern the data?

Accepted Answer

Microsoft Purview. Microsoft Purview is the correct choice because it is a unified data governance service designed specifically for cataloging data assets, tracking lineage across hybrid and multi-cloud environments, and enforcing data policies. Unlike the other options, Purview provides out-of-the-box lineage scanning, a business glossary, and automated classification, making it the appropriate tool for organizing data into zones and ensuring end-to-end lineage in a data lake architecture.

Answer

Azure Synapse Analytics

Answer

Azure Data Factory

Answer

Azure Databricks

Question 3

A company stores customer information in a table with columns CustomerID, Name, Address, and PhoneNumber. Every row has values for all these columns, and the data follows a fixed schema. Which type of data does this represent?

Accepted Answer

Structured data. Structured data conforms to a fixed schema where each row has the same columns and data types. The table with CustomerID, Name, Address, and PhoneNumber, where every row contains values for all columns, perfectly fits this definition. This is typical of relational database tables (e.g., in Azure SQL Database) where the schema is enforced at the table level.

Answer

Unstructured data

Answer

Semi-structured data

Answer

Streaming data

Question 4

Which THREE are benefits of using Azure SQL Database serverless compute tier?

Accepted Answer

Billing per second for compute usage. Option B is correct because the serverless compute tier bills per second of compute usage, so you only pay for the vCore-seconds actually consumed rather than a fixed provisioned capacity. Option C is correct because serverless automatically scales compute (vCores) up and down based on the workload's demand, within the configured min/max vCore range. Option D is correct because serverless supports auto-pause, where the database is paused during inactivity and resumes on the next connection, eliminating compute charges while paused. Option A is not a serverless-specific benefit, since the 99.99% availability SLA applies to Azure SQL Database generally, not uniquely to serverless. Option E is not correct because serverless is designed for intermittent, unpredictable, or low-to-moderate workloads, whereas high-throughput, latency-sensitive applications are better suited to the provisioned or hyperscale tiers due to cold-start delays after auto-pause.

Answer

Guaranteed high availability with 99.99% SLA

Answer

Ideal for high-throughput, latency-sensitive applications

Question 5

A company stores customer orders in a relational database that handles many small transactions (inserts, updates, deletes) throughout the day. Separately, they maintain a data warehouse that is used for complex aggregations and historical trend analysis. Which statement correctly describes these two workloads?

Accepted Answer

The first system is an OLTP workload; the second is an OLAP workload.. The first system handles many small, concurrent transactions (inserts, updates, deletes) typical of an Online Transaction Processing (OLTP) workload, optimized for ACID compliance and fast query response. The second system is an Online Analytical Processing (OLAP) workload, designed for complex aggregations and historical trend analysis using columnar storage and star schemas. This distinction is fundamental in data architecture, where OLTP systems prioritize write performance and OLAP systems prioritize read performance for large-scale analytics.

Answer

Both systems are OLTP workloads because they store customer orders.

Answer

The first system is an OLAP workload; the second is an OLTP workload.

Answer

Both systems are OLAP workloads because they both involve data storage.

Question 6

A company stores terabytes of historical sales data as Parquet files in Azure Data Lake Storage Gen2. Business analysts need to run ad-hoc SQL queries that involve complex joins and aggregations over this data. They want to avoid provisioning a dedicated cluster or moving data into a separate database. The queries must be executed using standard T-SQL syntax. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics serverless SQL pool. Azure Synapse Analytics serverless SQL pool (B) is the correct choice because it allows you to query Parquet files directly in Azure Data Lake Storage Gen2 using standard T-SQL syntax without provisioning any dedicated cluster or moving data. It automatically scales compute resources to handle complex joins and aggregations on terabytes of data, charging only for the data processed. This matches the requirement for ad-hoc, serverless querying with familiar T-SQL.

Answer

Azure Synapse Analytics dedicated SQL pool

Answer

Azure Databricks

Answer

Azure HDInsight

Question 7

A gaming company stores player game scores in Azure Cosmos DB. Each document contains PlayerID, GameID, Score, Timestamp. The most common query is: 'Get all scores for a specific game ordered by score descending'. Which partition key should be chosen to minimize Request Unit (RU) consumption?

Accepted Answer

GameID. GameID is the correct partition key because the most common query filters on GameID, and Cosmos DB routes queries to the exact physical partition(s) containing that GameID. This avoids cross-partition fan-out, minimizing RU consumption. A partition key that matches the query filter ensures efficient index lookup and data retrieval.

Answer

PlayerID

Answer

Score

Answer

Timestamp

Question 8

A company uses Azure SQL Database and wants to ensure that a specific query always uses a particular index. What should they do?

Accepted Answer

Use a query hint to force the index. Query hints, specifically the INDEX hint, allow you to force the query optimizer to use a particular index for a specific query. In Azure SQL Database, this is done by adding `OPTION (TABLE HINT (table_name, INDEX (index_name)))` to the query, overriding the optimizer's default index selection.

Answer

Update statistics on the table

Answer

Rebuild the index

Answer

Enable Query Store

Question 9

Which THREE of the following are benefits of using a columnar storage format like Parquet for analytical workloads?

Accepted Answer

Reduced I/O when querying a subset of columns. Option B is correct because columnar formats like Parquet store each column contiguously, so a query that references only a subset of columns reads only those column chunks rather than full rows, dramatically reducing I/O. Option D is correct because values within a single column share the same data type and tend to be similar, which allows efficient encoding schemes (e.g., dictionary, run-length, delta encoding) and yields much better compression ratios than row-oriented storage. Option E is correct because Parquet stores per-row-group and per-column-chunk statistics (min/max, null counts), enabling predicate pushdown so the engine can skip row groups and column chunks that cannot satisfy the filter. Option A is not a benefit of Parquet: it is a file format, not a database, and it does not enforce referential integrity constraints such as foreign keys. Option C is also wrong because columnar layouts are optimized for bulk scans and aggregations, not frequent single-row updates; row-oriented stores handle point updates far better.

Answer

Enforced referential integrity constraints

Answer

Optimized for frequent row updates

Question 10

A social media application stores user posts as JSON documents in Azure Cosmos DB. Each post includes fields such as postId, userId, content, timestamp, and an array of tags. The development team wants to query posts by userId and timestamp range using a SQL-like syntax. Which Azure Cosmos DB API should they choose?

Accepted Answer

B. Azure Cosmos DB for NoSQL API (Core SQL API). The Azure Cosmos DB for NoSQL API (Core SQL API) is the correct choice because it natively supports SQL-like querying (SELECT, WHERE, ORDER BY) over JSON documents. The team's requirement to query posts by userId and timestamp range using SQL-like syntax is directly supported by this API, which treats each JSON document as an item and allows filtering on nested fields like userId and timestamp. Other APIs either lack native SQL-like syntax or are optimized for different data models (e.g., MongoDB uses a JSON-like query language, Table API uses OData, Cassandra uses CQL).

Answer

A. Azure Cosmos DB for MongoDB API

Answer

C. Azure Cosmos DB for Table API

Answer

D. Azure Cosmos DB for Apache Cassandra API

Question 11

A company stores JSON documents for a product catalog. Each document has a flexible schema because different product categories have different attributes. The catalog is read-heavy and requires low-latency lookups by product ID. The company expects to handle millions of products and needs to serve customers globally with low latency. Which Azure NoSQL data store should they choose?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it is a globally distributed, multi-model NoSQL database that natively supports JSON documents with flexible schemas, provides single-digit-millisecond latency for read-heavy workloads via automatic indexing, and offers turnkey global distribution across Azure regions to serve customers worldwide with low latency.

Answer

Azure Table Storage

Answer

Azure Blob Storage

Answer

Azure SQL Database

Question 12

A social media application uses Azure Cosmos DB to store user posts. When a user publishes a new post, they immediately refresh their feed and expect to see their own post right away. However, the application can tolerate temporary staleness for posts from other users. Which Azure Cosmos DB consistency level should the app use for the read operations that display the feed?

Accepted Answer

Session. Session consistency guarantees monotonic reads, writes, and read-your-writes within a single client session. Because the user expects to see their own post immediately after publishing, but can tolerate staleness for others' posts, Session consistency provides the exact guarantee needed: the user's own writes are immediately visible to them, while other users' posts may be slightly stale.

Answer

Strong

Answer

Bounded staleness

Answer

Eventual

Question 13

A gaming application stores player profiles as JSON documents. Each profile has standard fields like playerId, username, and email, but also optional fields such as achievements and gamePreferences. The application needs to query profiles by playerId with low latency and also run SQL-like queries to find players with specific achievements. Which Azure Cosmos DB API should they choose?

Accepted Answer

C: SQL (Core) API. The SQL (Core) API is the best choice because it natively supports JSON documents with flexible schemas (including optional fields like achievements and gamePreferences), provides low-latency point reads by playerId using the id field as the partition key, and enables SQL-like queries (e.g., SELECT * FROM c WHERE ARRAY_CONTAINS(c.achievements, 'specific_achievement')) without requiring a separate indexing or translation layer.

Answer

A: Table API

Answer

B: MongoDB API

Answer

D: Cassandra API

Question 14

A company maintains a database of customer orders that are updated frequently. They also store aggregated monthly sales reports that are generated once and then only read. Which statement correctly distinguishes these two types of data workloads?

Accepted Answer

Transactional data is optimized for write operations, and analytical data is optimized for read operations.. Transactional workloads (like the frequently updated customer orders) are optimized for write-heavy operations, ensuring ACID compliance and data integrity, while analytical workloads (like the read-only monthly sales reports) are optimized for read-heavy operations, often using columnar storage or pre-aggregated data to speed up queries. This distinction aligns with the core difference between OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) systems in Azure, such as Azure SQL Database for transactional data and Azure Synapse Analytics for analytical data.

Answer

Transactional data must always be stored in non-relational databases, and analytical data in relational databases.

Answer

Analytical data always requires real-time processing, whereas transactional data is batch-processed.

Answer

Transactional data is read-only and analytical data is frequently updated.

Question 15

A company runs a mission-critical SQL Server database on-premises. They plan to migrate to Azure SQL Database and need to choose the appropriate service tier. The database is currently 500 GB and is expected to grow to 8 TB within two years. The workload is read-heavy with many concurrent users, and they require fast scaling of compute resources without significant downtime. Which Azure SQL Database service tier should they choose?

Accepted Answer

Hyperscale. Hyperscale is the correct choice because it supports databases up to 100 TB, far exceeding the expected 8 TB growth, and provides fast scaling of compute resources without downtime by using a distributed architecture with separate compute and storage nodes. Its read-heavy workload with many concurrent users benefits from Hyperscale's multiple readable replicas and buffer pool extension, ensuring high performance and availability.

Answer

General Purpose

Answer

Business Critical

Answer

Serverless