DP-900 Practice Exam 57 — 20 Questions

Question 1

A company is planning to deploy a relational database on Azure. They need to ensure that the database supports automatic backups, point-in-time restore, and built-in high availability without requiring them to manage failover clustering or availability groups. Which Azure SQL deployment option should they use?

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

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed PaaS service that automatically handles high availability, backups, and point-in-time restore. It requires no management of failover clustering or availability groups. While Azure SQL Managed Instance also offers these benefits, it is intended for scenarios requiring instance-level features. For a straightforward relational database with automatic management, Azure SQL Database is the best fit.

Answer

Azure Database for PostgreSQL

Answer

Azure SQL Managed Instance

Answer

SQL Server on Azure Virtual Machines with Always On Availability Groups

Question 2

A financial services firm runs a securities trading platform. Trades must be recorded atomically, with either all parts of a trade committing or none, and the committed records must survive a database node failure. The firm also needs enforcement of referential integrity between trade and account tables. Which data store characteristic best matches these requirements?

Accepted Answer

Relational storage with ACID transactions and referential integrity constraints. The requirements describe ACID transaction guarantees plus referential integrity, which are core capabilities of relational storage such as Azure SQL Database. Eventual consistency, key-value stores, and columnar analytical engines each optimize for other priorities, such as availability, fast key lookups, or scan performance, and none provides the atomic, integrity-enforced writes the trading platform needs.

Answer

Key-value storage with tunable consistency and no schema enforcement

Answer

Columnar analytical storage optimized for large scans and aggregations

Answer

Schema-on-read storage with eventual consistency across replicas

Question 3

A social networking application needs to store and query relationships between users, such as 'friends of friends'. The application should be able to traverse these relationships efficiently to recommend new connections. Which Azure NoSQL data store and API should they choose?

Accepted Answer

Azure Cosmos DB with the Gremlin API. Azure Cosmos DB with the Gremlin API is the correct choice because Gremlin is a graph traversal language specifically designed for querying highly connected data, such as social network relationships. It allows efficient traversal of edges (e.g., 'friends of friends') using graph algorithms, which is exactly what the application needs for recommending new connections. Other APIs like Table API or services like Blob Storage lack native graph traversal capabilities.

Answer

Azure Cosmos DB with the Table API

Answer

Azure Cache for Redis

Answer

Azure Blob Storage

Question 4

Which TWO Azure services are primarily used for batch processing of large volumes of data? (Choose two.)

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics (A) is correct because it is a cloud-based analytics service that combines big data (Spark) and data warehousing (SQL pools) to process and analyze massive volumes of data in batch workloads. Azure Databricks (D) is correct because it is an Apache Spark-based analytics platform designed for large-scale batch data processing, machine learning, and ETL jobs. Azure SQL Database (B) is a transactional relational database service optimized for OLTP workloads, not large-scale batch processing. Azure Stream Analytics (C) is a real-time streaming analytics service for continuous event data, not batch processing. Azure Data Lake Storage (E) is a scalable storage layer for big data, but it stores data rather than performing batch processing itself.

Answer

Azure SQL Database

Answer

Azure Stream Analytics

Answer

Azure Data Lake Storage

Question 5

Which TWO data storage types are classified as structured data in Azure? (Choose two.)

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance (C) and Azure SQL Database (D) are both correct because they are relational database services built on the SQL Server engine that store data in tables with a fixed schema, defined columns, data types, and relationships enforced by primary/foreign keys, which is the definition of structured data. Azure SQL Database is a fully managed PaaS relational database, while Azure SQL Managed Instance offers near-100% SQL Server compatibility with features like SQL Agent and cross-database queries, but both use the relational (tabular) model. The unmarked options do not belong: Azure Cosmos DB (A) is a multi-model NoSQL service supporting document, key-value, graph, and column-family data, which is typically semi-structured or unstructured; Azure Data Lake Storage (B) is designed for storing raw files of any format (often unstructured or semi-structured) for big data analytics; and Azure Blob Storage (E) is object storage for unstructured data such as images, videos, and documents.

Answer

Azure Cosmos DB

Answer

Azure Data Lake Storage

Answer

Azure Blob Storage

Question 6

Your team needs to store unstructured data such as documents, images, and videos for a data lake analytics project. The data will be processed by Azure Data Lake Storage Gen2. Which storage account type should you create?

Accepted Answer

StorageV2 (general-purpose v2) with hierarchical namespace enabled. The correct option is A: StorageV2 (general-purpose v2) with hierarchical namespace enabled. Azure Data Lake Storage Gen2 is not a separate account type; it is implemented by enabling a hierarchical namespace on a general-purpose v2 (StorageV2) storage account, which supports Blob storage with POSIX-like directory semantics for analytics workloads. BlobStorage (B) is a legacy account type that does not support the hierarchical namespace required for ADLS Gen2. QueueStorage (C) is for message queues, and FileStorage (D) is for SMB file shares, neither of which provides the data lake capabilities needed here.

Answer

BlobStorage

Answer

QueueStorage

Answer

FileStorage

Question 7

A multinational corporation has Azure SQL Databases deployed in multiple Azure regions. They need to ensure that a failover to a secondary region happens automatically and with minimal data loss if the primary region goes down. Which deployment option should they use?

Accepted Answer

Active geo-replication with auto-failover groups. The correct option is A, Active geo-replication with auto-failover groups, because auto-failover groups provide a read-write listener endpoint and allow Azure SQL Database to automatically fail over to a secondary region if the primary region becomes unavailable, with asynchronous replication that minimizes data loss. This directly matches the requirement for automatic cross-region failover with minimal data loss. Option B, zone-redundant databases, only protects against availability zone failures within a single region and does not provide cross-region failover. Option C, Azure Traffic Manager with multiple databases, can route traffic but does not itself perform database replication or automatic database failover. Option D, locally redundant storage (LRS), only replicates data within a single datacenter/region and offers no cross-region disaster recovery.

Answer

Zone-redundant database

Answer

Azure Traffic Manager with multiple databases

Answer

Locally redundant storage (LRS)

Question 8

A company stores user profile data in Azure Blob Storage as JSON files. Each file represents one user. They need to provide real-time search capabilities on user attributes like name, email, and location. The search must support partial matches and return results within 500 ms. The data volume is 10 TB and grows by 1 GB daily. They have a limited budget and want to minimize operational overhead. Which Azure solution should they choose?

Accepted Answer

Use Azure Cognitive Search to index the JSON files and provide search capabilities.. Azure Cognitive Search is the correct choice because it is a fully managed search service that can index JSON documents stored in Azure Blob Storage and provide real-time, low-latency full-text search with support for partial matches (e.g., prefix, wildcard, and fuzzy queries), easily meeting the 500 ms requirement while minimizing operational overhead and cost. Cosmos DB (A) is a transactional database, not a search engine, and its SQL API does not natively support partial-match full-text search without additional indexing complexity and higher cost. Azure Data Lake Analytics with U-SQL (B) is a batch analytics service, not designed for real-time sub-second search, and it has significant operational overhead. Azure SQL Database full-text indexes (D) can support some text search, but loading 10 TB of JSON and scaling for low-latency partial-match search would be more expensive and operationally heavier than Cognitive Search.

Answer

Import the JSON files into Azure Cosmos DB and use the SQL API to search.

Answer

Use Azure Data Lake Analytics with U-SQL to query the files.

Answer

Load the data into Azure SQL Database and create full-text indexes.

Question 9

A retail company uses Azure SQL Database for its inventory system. The database stores sensitive customer information that must be encrypted at rest to comply with data protection regulations. Which feature should they enable?

Accepted Answer

Transparent Data Encryption (TDE). Transparent Data Encryption (TDE) is the correct choice because it encrypts Azure SQL Database data at rest at the page level, protecting the database files, backups, and log files without requiring application changes. This directly satisfies the regulatory requirement for encryption at rest of sensitive customer information. Dynamic Data Masking only obfuscates data in query results and does not encrypt stored data, so it does not meet the at-rest encryption requirement. Always Encrypted protects data in use and at rest but is designed for client-side encryption of specific columns and requires application changes, making it unnecessary here. Azure Information Protection is a data classification and labeling service for documents and emails, not a database at-rest encryption feature.

Answer

Dynamic Data Masking

Answer

Always Encrypted

Answer

Azure Information Protection

Question 10

A retail company operates an online store. When a customer places an order, the system immediately updates inventory and payment records. Separately, the company's business analysts run weekly reports that aggregate sales data to identify trends. Which two terms correctly describe these workloads?

Accepted Answer

Batch processing and real-time processing. Option A is correct because the order-placement workload that immediately updates inventory and payment records is real-time (transactional) processing, while the weekly aggregate sales reports run on a schedule over accumulated data, which is batch processing. Option B is correct because the immediate, row-level insert/update of inventory and payment records is classic OLTP (Online Transaction Processing), whereas the weekly aggregation of sales data for trend analysis is classic OLAP (Online Analytical Processing). Option C is not correct because the scenario describes operational and analytical processing patterns, not a contrast between structured and unstructured data formats. Option D is not correct because data ingestion and data transformation are pipeline stages (extract/load and convert/clean), not the workload categories being contrasted here.

Answer

Structured and Unstructured data

Answer

Data ingestion and data transformation

Question 11

A company stores log files in Azure Blob Storage. Each log file is a CSV file ranging from 100 MB to 1 GB. They need to query the logs using SQL queries and plan to use Azure Synapse Serverless SQL. The logs are stored in a container named 'logs' with a folder structure like 'year=2023/month=01/day=01/logfile.csv'. They want to optimize query performance and reduce data scanned. Which partitioning strategy should they implement?

Accepted Answer

Use the existing folder structure with year/month/day partitions.. Option B is correct because Synapse Serverless SQL can use the existing Hive-style folder hierarchy (year=2023/month=01/day=01/) as partition columns, so queries that filter on year, month, or day can prune irrelevant folders and scan far less data. This directly reduces the amount of data scanned and improves performance without moving or rewriting the logs. Option A is wrong because a single folder removes the partition pruning benefit, and while Parquet is more efficient than CSV, the question asks for a partitioning strategy. Option C is wrong because one large CSV file eliminates all partition granularity and forces a full scan. Option D is wrong because random file names provide no queryable partition structure and do not reduce data scanned.

Answer

Convert logs to Parquet format and store in a single folder.

Answer

Consolidate all logs into a single large CSV file.

Answer

Use random file names to distribute load.

Question 12

A company needs to migrate a large on-premises SQL Server database to Azure with minimal downtime. Which Azure service should they use for the migration?

Accepted Answer

Azure Database Migration Service. Azure Database Migration Service (D) is correct because it is purpose-built to migrate on-premises SQL Server databases to Azure with minimal downtime, using online migration mode that continuously replicates changes until cutover. Azure SQL Database (A) is a target platform, not a migration tool, so it does not perform the migration itself. Azure Backup (B) only backs up and restores data and does not support live database migration with minimal downtime. Azure Data Factory (C) is an orchestration and data-integration service suited to data movement and ETL, not seamless SQL Server database migration with minimal downtime.

Answer

Azure SQL Database

Answer

Azure Backup

Answer

Azure Data Factory

Question 13

A company uses Azure Table Storage to store customer session data. Each session has a PartitionKey (CustomerId) and RowKey (SessionId). They need to retrieve all sessions for a specific customer quickly. Which query design will be most efficient?

Accepted Answer

Query with PartitionKey equal to the specific CustomerId.. Option D is correct because in Azure Table Storage the PartitionKey defines a contiguous, indexed partition, so querying with PartitionKey equal to the specific CustomerId retrieves all of that customer's sessions in a single efficient partition scan without crossing partitions. Since CustomerId is the PartitionKey, this query targets exactly the data needed and is the fastest supported pattern. Option A is wrong because retrieving all entities and filtering client-side forces a full table scan and transfers unnecessary data. Option B is wrong because Azure Table Storage does not support secondary indexes on RowKey. Option C is wrong because querying by a specific RowKey (SessionId) returns only one session, not all sessions for the customer.

Answer

Select all entities and filter client-side.

Answer

Create a secondary index on RowKey.

Answer

Query with RowKey equal to a specific SessionId.

Question 14

A logistics company ingests millions of telemetry events per minute from vehicles into Azure. They need a service that can capture and store these events for later processing by multiple downstream analytics applications. The solution must support massive scale and retain data for up to seven days by default. Which Azure service should they use?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is built for high-throughput event ingestion and provides a durable buffer that multiple downstream applications can read from independently. Its ability to scale to millions of events per second and retain data for a configurable period makes it the right choice for capturing vehicle telemetry for later analysis.

Answer

Azure Data Factory

Answer

Azure Service Bus

Answer

Azure Blob Storage

Question 15

A media company uses Azure Stream Analytics to process live video streaming telemetry from Azure Event Hubs. They need to write the processed data to a destination that supports fast, random read/write access for a custom application that performs lookups. The solution must minimize latency. Which output should they configure?

Accepted Answer

Azure SQL Database. Azure SQL Database is designed for transactional workloads with fast, random read/write access and low latency. As an output for Azure Stream Analytics, it enables real-time updates that a custom application can query with minimal delay. Other outputs like Blob Storage or Data Lake are optimized for batch and sequential access, not low-latency lookups.

Answer

Power BI

Answer

Azure Data Lake Storage Gen2

Answer

Azure Blob Storage

Question 16

A company wants to analyze data from multiple Azure SQL Databases using a single query. Which Azure service should they use?

Accepted Answer

Azure SQL Database elastic query. Azure SQL Database elastic query is correct because it enables cross-database queries across multiple Azure SQL Databases from a single T-SQL query, using an external data source and external table to reference the other databases. This directly matches the requirement to analyze data from multiple Azure SQL Databases with one query. Failover groups are for high availability and geo-replication, not for querying across databases. Azure Synapse Link provides near-real-time analytics replication from operational stores into Synapse, and Azure Stream Analytics is for real-time stream processing, so neither fits this cross-database query scenario.

Answer

Azure SQL Database failover groups

Answer

Azure Synapse Link

Answer

Azure Stream Analytics

Question 17

Which TWO Azure services can be used to store non-relational data? (Choose two.)

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB (A) is correct because it is Microsoft's globally distributed, multi-model NoSQL database service that natively stores non-relational data such as documents, key-value pairs, graphs, and column-family data. Azure Table Storage (E) is also correct because it is a NoSQL key-attribute store designed for storing large amounts of non-relational structured data (entities with partition key and row key) at low cost. Azure SQL Database (B) is a relational PaaS database engine using T-SQL, so it stores relational data, not non-relational. Azure Synapse Analytics (C) is an analytics service built on relational MPP (massively parallel processing) SQL pools and Spark, primarily for data warehousing and analytics rather than non-relational storage. Azure Database for MySQL (D) is a managed relational MySQL database, so it stores relational data in tables with schemas and foreign keys.

Answer

Azure SQL Database

Answer

Azure Synapse Analytics

Answer

Azure Database for MySQL

Question 18

Your company runs a global e-commerce platform on Azure SQL Database. The platform experiences heavy read traffic on product catalog and inventory tables. You need to reduce read latency for users in different geographic regions while keeping write latency low. The solution must be cost-effective and require minimal application changes. Current architecture: a single Azure SQL Database in West US. You have budget for additional Azure resources. What should you implement?

Accepted Answer

Configure Active Geo-Replication to create readable secondaries in regions where users are located.. Active Geo-Replication (option A) is correct because it creates readable secondary databases in multiple Azure regions, allowing read queries to be served locally in each user's region while writes continue to the primary, reducing read latency without changing the application's write path. It is cost-effective relative to a full re-platforming and requires minimal application changes—just directing read-only connections to the regional secondary endpoints. Option B does not fit because migrating to Azure Cosmos DB with multi-region writes is a major architectural change and typically more expensive, and it changes the data model and consistency semantics. Option C is wrong because Azure Traffic Manager only routes traffic to endpoints and cannot make a single West US database serve reads faster for users in other regions. Option D is incorrect because Read Scale-Out uses a read-only replica in the same region as the primary, so it does not reduce cross-region read latency for geographically distributed users.

Answer

Migrate the database to Azure Cosmos DB with multi-region writes.

Answer

Use Azure Traffic Manager to route read requests to the primary database in West US.

Answer

Enable Read Scale-Out on the existing database and configure application to use the read-only endpoint.

Question 19

A healthcare organization needs to build a modern data warehouse on Azure to combine structured patient data from an on-premises SQL Server with semi-structured JSON logs from Azure Blob Storage. They require a solution that supports both T-SQL and Spark workloads and integrates with Power BI. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics provides a unified experience for data warehousing and big data analytics, supporting both T-SQL and Spark natively. It can ingest structured and semi-structured data, and its integration with Power BI enables seamless reporting. This makes it the most suitable service for building a modern data warehouse on Azure.

Answer

Azure HDInsight

Answer

Azure Databricks

Answer

Azure SQL Database

Question 20

Your company needs to store large amounts of data that will be accessed only a few times a year for compliance audits. The data must be retained for 7 years. Which Azure Blob Storage access tier should you choose?

Accepted Answer

Archive. The Archive access tier (option D) is correct because it is designed for data that is rarely accessed and stored for at least 180 days, with the lowest storage cost and highest retrieval latency and cost, which fits compliance data accessed only a few times a year and retained for 7 years. Cool (option A) is for infrequent access but still requires data to be stored for at least 30 days and has higher storage cost than Archive, so it is not optimal for this long-term, rarely accessed scenario. Hot (option C) is for frequently accessed data and has the highest storage cost, making it unsuitable for data accessed only a few times a year. Premium (option B) is for high-performance scenarios with low latency and high transaction rates, and it is not a cost-effective choice for long-term archival compliance data.

Answer

Cool

Answer

Premium

Answer

Hot