DP-900 Practice Test 49 — 15 Questions

Question 1

A company is choosing a non-relational data store for a new application that requires flexible schema, high availability, and low latency across multiple geographic regions. Which TWO Azure services meet these requirements?

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

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB (option D) offers multi-region replication, flexible schema, and low latency. Azure Table Storage (option E) is a NoSQL key-value store with global replication (read-access geo-redundant storage) and low latency. Option A (Azure Files) is file storage, not a non-relational data store meeting these needs. Option B (Azure SQL Database) is relational. Option C (Azure Cache for Redis) is an in-memory cache, not a primary data store.

Answer

Azure Files

Answer

Azure SQL Database

Answer

Azure Cache for Redis

Question 2

A retail company uses Power BI to create sales reports. The data source is an Azure SQL Database that updates every 15 minutes. The reports must reflect near real-time data without manual refresh. Which Power BI feature should the company use?

Accepted Answer

Use DirectQuery mode to connect to the Azure SQL Database.. DirectQuery mode allows Power BI to query the Azure SQL Database directly without importing data, ensuring that reports reflect the current state of the database each time a report is viewed. Since the database updates every 15 minutes, DirectQuery provides near real-time data without requiring manual or scheduled refresh operations.

Answer

Use the on-premises data gateway to connect to Azure SQL Database.

Answer

Import data with scheduled refresh every 15 minutes.

Answer

Create a Power BI dataflow to transform the data.

Question 3

A manufacturing company stores IoT sensor data as blobs in Azure Blob Storage. Each blob is named with a device ID and a timestamp, and they need to quickly find all blobs for a specific device within a date range. Which Azure Blob Storage feature should they use to query blobs based on custom metadata?

Accepted Answer

Blob index tags. Blob index tags allow you to apply custom key-value metadata to blobs and then query them using a filtered query across containers or storage accounts. This enables efficient retrieval of blobs by device ID and timestamp without scanning all blob names or maintaining a separate index.

Answer

Blob snapshots

Answer

Blob soft delete

Answer

Blob lifecycle management

Question 4

A data analyst is using Azure Databricks to transform streaming data from Event Hubs. They need to ensure that if a failure occurs, the streaming job can resume processing from the last committed offset. Which checkpointing mechanism should they configure?

Accepted Answer

Use Structured Streaming with checkpointing to Azure Data Lake Storage Gen2.. Structured Streaming in Azure Databricks uses checkpointing to store the current state and offsets of the streaming query in a reliable, external storage system. By configuring checkpointing to Azure Data Lake Storage Gen2, the job can recover from failures and resume processing exactly from the last committed offset, ensuring exactly-once semantics. This is the recommended approach for production streaming workloads on Azure.

Answer

Enable write-ahead logs on the Event Hubs namespace.

Answer

Use checkpointing to Hive metastore.

Answer

Use checkpointing to DBFS (Databricks File System).

Question 5

A gaming company stores player profiles as JSON documents. Each profile can have different attributes; for example, some profiles include an 'achievements' field while others include a 'purchaseHistory' field. The application must retrieve profiles by player ID with single-digit-millisecond latency and also support SQL-like queries on any attribute. Which Azure data store should the company use?

Accepted Answer

B. Azure Cosmos DB Core (SQL) API. Azure Cosmos DB Core (SQL) API is the correct choice because it natively stores JSON documents with flexible schemas, supports indexing on any attribute for SQL-like queries, and guarantees single-digit-millisecond latency for point reads by player ID. This meets the requirement for both fast key-based lookups and ad-hoc querying across varying profile attributes.

Answer

A. Azure Table Storage

Answer

C. Azure Blob Storage

Answer

D. Azure Database for PostgreSQL

Question 6

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?

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 7

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 8

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 9

A data engineering team needs to build a batch processing pipeline that transforms large volumes of sales data stored in Azure Data Lake Storage Gen2. The transformations include aggregations and joins, and the output should be stored back in the data lake as Parquet files. The team wants a serverless compute option that automatically scales and charges per second. Which Azure service should they use?

Accepted Answer

Azure Data Factory with mapping data flows. Azure Data Factory with mapping data flows is the correct choice because it is a serverless compute option that automatically scales and charges per second. It can perform complex transformations like aggregations and joins on data in Azure Data Lake Storage Gen2 and write output as Parquet files. In contrast, Azure Databricks requires cluster provisioning and is not serverless.

Answer

Azure Synapse Analytics dedicated SQL pool

Answer

Azure Databricks with auto-scaling clusters

Answer

Azure Stream Analytics

Question 10

Which TWO Azure services can be used to perform large-scale data transformation and processing in a serverless manner?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool (Option B) is correct because it allows you to run T-SQL queries over data stored in Azure Data Lake or Blob Storage without provisioning any dedicated compute resources, paying only for the data processed. Azure Data Factory (Option C) is correct because it provides a serverless orchestration and data integration service that can execute data transformation activities (like Mapping Data Flows) at scale without managing underlying infrastructure.

Answer

Azure Analysis Services

Answer

Azure Databricks

Answer

Azure SQL Database

Question 11

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 12

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 13

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 14

A global social media app uses Azure Cosmos DB (NoSQL API) to store user profile data. The app is read-heavy and requires the fastest possible read performance worldwide. The data is updated by users and eventual consistency is acceptable because immediate consistency is not critical for profile views. Which consistency level should they choose to minimize read latency?

Accepted Answer

Eventual. Eventual consistency offers the lowest read latency because it allows reads from any replica without waiting for confirmation that the data is the most recent version. Since the app is read-heavy and eventual consistency is acceptable, this consistency level minimizes latency by not requiring any synchronization or staleness bounds.

Answer

Strong

Answer

Bounded staleness

Answer

Session

Question 15

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