DP-900 Practice Test 12 — 15 Questions

Question 1

A social media company stores user session data. Each session record must be quickly looked up by user ID and must have strong consistency so that once a session is written, subsequent reads always return the latest data. The company expects billions of session records globally and needs low-latency reads/writes. Which Azure data store best meets these requirements?

Accepted Answer

Azure Cosmos DB (SQL API). Azure Cosmos DB with SQL API is the correct choice because it offers single-digit millisecond read/write latencies at any scale, global distribution, and tunable consistency levels including strong consistency. Strong consistency ensures that once a write is acknowledged, all subsequent reads return the latest data, which is critical for session state where stale reads could cause authentication or authorization failures. Cosmos DB also supports automatic indexing and partitioning by user ID, enabling fast lookups across billions of records.

Answer

Azure Blob Storage

Answer

Azure Table Storage

Answer

Azure Cache for Redis

Question 2

A social networking 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. However, the application can tolerate temporary staleness for posts from other users (e.g., a few seconds delay). Which Azure Cosmos DB consistency level should the application use for read operations that display the feed?

Accepted Answer

Session. Session consistency is the correct choice because it guarantees that the user who writes a post will read their own write within the same session, while allowing other users to see slightly stale data. This matches the requirement: the author immediately sees their new post, but the application can tolerate a few seconds of staleness for other users' posts. Session consistency uses a session token to ensure monotonic reads and writes for the same client, making it ideal for per-user feed scenarios.

Answer

Strong

Answer

Bounded staleness

Answer

Eventual

Question 3

A social media application stores user profiles in Azure Cosmos DB using the NoSQL API. Each profile includes UserID, Name, Email, and an array of Posts. The most common query retrieves a user's profile by UserID. The application requires strong consistency for writes so that once a profile is updated, all subsequent reads see the latest data. To minimize Request Unit (RU) consumption, which partition key should be chosen?

Accepted Answer

UserID. UserID is the correct partition key because it is the primary filter in the most common query (retrieving a profile by UserID), ensuring each query targets a single logical partition. This minimizes cross-partition queries and RU consumption. Additionally, UserID provides high cardinality and even distribution, which prevents hot partitions and supports the required strong consistency for writes.

Answer

Email

Answer

Name

Answer

A synthetic partition key combining UserID and Region

Question 4

A company stores customer information in a SQL database with fixed columns (CustomerID, Name, Email). They also store scanned PDF contracts and product images in a file storage system. Which statement correctly describes the types of data mentioned?

Accepted Answer

The customer information is structured, and the files are unstructured.. Customer information stored in fixed columns (CustomerID, Name, Email) follows a strict schema with defined data types and relationships, making it structured data. Scanned PDF contracts and product images are binary files with no inherent schema or organization, fitting the definition of unstructured data. Option C correctly pairs these classifications.

Answer

Both the customer information and the files are structured data.

Answer

The customer information is semi-structured, and the files are unstructured.

Answer

Both the customer information and the files are unstructured.

Question 5

A data engineering team is designing an ELT (Extract, Load, Transform) pipeline using Azure Data Lake Storage Gen2. They will ingest raw sales data from multiple sources, store it in the data lake, transform it using Azure Databricks, and finally store the transformed data in a curated zone for analytics. Place the steps in the correct order for an ELT pipeline.

Accepted Answer

Ingest raw data from sources. In an ELT pipeline, data is first extracted and loaded into the data lake, then transformed. This order follows the ELT paradigm: extract, load, transform.

Question 6

A company has 15 on-premises SQL Server databases, each 20–40 GB, running on a single instance. They rely on cross-database queries using three-part names (e.g., DB1.dbo.table) and SQL Server Agent for maintenance. They want to migrate to Azure with minimal application changes and reduce administrative overhead. Which Azure SQL deployment option should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is correct because it provides near-100% compatibility with on-premises SQL Server, including support for cross-database queries using three-part names and SQL Server Agent. This allows the company to migrate with minimal application changes while offloading administrative overhead like patching and backups, which are handled by Azure.

Answer

Azure SQL Database elastic pool

Answer

Azure SQL Database single database

Answer

SQL Server on Azure Virtual Machines

Question 7

A company stores customer support chat transcripts as plain text files in Azure Blob Storage. The files are accessed frequently for the first 30 days, then infrequently for the next 2 years, and after that must be retained for 7 years for compliance but are rarely accessed. The company wants to minimize storage costs by automatically moving data through appropriate access tiers. Which Azure Blob Storage lifecycle management policy should they implement?

Accepted Answer

Move blobs from Hot to Cool after 30 days, then to Archive after 2 years. Option A is correct because the lifecycle management policy matches the access pattern: move blobs from Hot (frequent access for first 30 days) to Cool (infrequent access for next 2 years) after 30 days, then to Archive (rare access for 7-year compliance) after 2 years. This minimizes storage costs by using the cheapest tier for each phase while retaining data for the required 7-year compliance period.

Answer

Store all data in Hot tier for the full retention period

Answer

Move blobs from Hot to Archive after 30 days and delete after 2 years

Answer

Store all data in Cool tier for the first 30 days, then move to Archive

Question 8

A company has an on-premises SQL Server database with a 1 TB 'Sales' table containing historical data. They want to move this table to Azure SQL Database with minimal downtime. The table is actively written to during business hours. Which approach should they use?

Accepted Answer

Use Azure Data Migration Service with continuous sync from on-premises to Azure SQL Database, then cut over. Azure Data Migration Service (DMS) with continuous sync is the correct approach because it supports online migration with minimal downtime. It uses transactional replication to keep the on-premises SQL Server database synchronized with Azure SQL Database while the source remains fully operational, allowing a controlled cutover with only seconds of downtime.

Answer

Use the Azure SQL Database migration wizard to perform an offline migration over the weekend

Answer

Export the table as a .bacpac file and import it into Azure SQL Database during off-hours

Answer

Use SQL Server Management Studio's 'Deploy Database to Azure SQL Database' wizard

Question 9

A company has a legacy application that requires SMB (Server Message Block) file shares to store and access configuration files. They want to migrate this data to Azure without modifying the application. Which Azure storage solution should they use?

Accepted Answer

Azure Files. Azure Files provides fully managed SMB (Server Message Block) file shares in the cloud, supporting the SMB 3.0 protocol. This allows the legacy application to access configuration files over the network using standard file share paths without any code changes, making it the ideal migration target for lift-and-shift scenarios.

Answer

Azure Blob Storage

Answer

Azure Queue Storage

Answer

Azure Disk Storage

Question 10

A company has an on-premises SQL Server 2016 database that uses SQL Server Agent jobs and cross-database queries across multiple databases. They want to migrate to Azure with minimal application changes, requiring native VNet integration and near 100% compatibility with the on-premises SQL Server instance. Which Azure deployment option should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is correct because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs and cross-database queries, while offering native VNet integration. This minimizes application changes during migration, unlike other Azure SQL options that lack these features.

Answer

Azure SQL Database single database

Answer

Azure SQL Database elastic pool

Answer

SQL Server on Azure Virtual Machines

Question 11

A company has an Azure SQL Database with an 'Orders' table containing millions of rows. The table has a clustered index on OrderID (primary key). Queries frequently filter by CustomerID (equality) and OrderDate (range). These queries are slow and cause high logical reads. Which index strategy will most improve performance for these specific queries?

Accepted Answer

Create a non-clustered index on (CustomerID, OrderDate).. A non-clustered index on (CustomerID, OrderDate) is a covering index for queries filtering by CustomerID (equality) and OrderDate (range). It allows SQL Server to perform an index seek on CustomerID, then a range scan on OrderDate, retrieving all needed columns without touching the clustered index (if the query is covered). This dramatically reduces logical reads compared to a full clustered index scan or a key lookup.

Answer

Rebuild the clustered index on (OrderDate, CustomerID).

Answer

Create a non-clustered index on OrderDate.

Answer

Create a filtered index on OrderDate for recent dates.

Question 12

A company is developing a web application that stores user profiles as JSON documents. The application needs to query these documents using SQL-like queries, and must support automatic indexing of all properties. They want a fully managed, globally distributed NoSQL database with low latency. Which Azure Cosmos DB API should they use?

Accepted Answer

SQL API. The SQL API (formerly DocumentDB API) is the correct choice because it natively supports querying JSON documents with SQL-like syntax (SELECT * FROM c WHERE c.property = value). It automatically indexes all properties by default, provides a fully managed, globally distributed NoSQL database with low-latency reads and writes, and is designed specifically for document-based workloads like user profiles.

Answer

Table API

Answer

Cassandra API

Answer

Gremlin API

Question 13

A company is migrating a 1.5 TB on-premises SQL Server database to Azure. The database relies on SQL Server Agent jobs for daily ETL processes and uses linked servers to query data from another on-premises SQL Server database. The company wants a fully managed PaaS service that requires minimal application changes. Which Azure SQL service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs and linked servers, while being a fully managed PaaS service. This minimizes application changes, as the migration can leverage the existing database code and features without significant rework.

Answer

Azure SQL Database

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Synapse Analytics

Question 14

A company runs an online booking system on Azure SQL Database. The system handles many concurrent transactions (OLTP). The business team runs complex reporting queries on the same database during business hours, which slows down the booking transactions. The company needs a solution to separate the analytical workload from the transactional workload without duplicating data manually. Which Azure SQL Database feature should they use?

Accepted Answer

Read Scale-out (readable secondary replica). Read Scale-out (readable secondary replica) is the correct choice because it allows the company to offload complex reporting queries to a read-only replica of the primary database, thereby isolating the analytical workload from the OLTP transactions. This feature is built into Azure SQL Database and does not require manual data duplication or ETL processes, directly addressing the requirement to separate workloads without manual effort.

Answer

Active Geo-Replication

Answer

Elastic pools

Answer

Hyperscale service tier

Question 15

A banking system processes a money transfer between two accounts. The system is designed so that after the transaction is committed, the results are permanently saved and survive any subsequent system failure, such as a power outage. Which ACID property ensures this behavior?

Accepted Answer

Durability. Durability ensures that once a transaction is committed, its changes are permanently stored and survive system failures, such as power outages or crashes. In this banking scenario, the money transfer results are written to non-volatile storage (e.g., disk) via a write-ahead log, guaranteeing that the committed state is recoverable even after a restart.

Answer

Atomicity

Answer

Consistency

Answer

Isolation