DP-900 Practice Test 24 — 15 Questions

Question 1

An e-commerce company runs a product inventory database on Azure SQL Database. During a flash sale, write transactions are slow because many read queries are running simultaneously and consuming resources. The company wants to isolate read workloads without modifying application code or database schemas. Which Azure SQL Database feature should they implement?

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

Accepted Answer

Read scale-out. Read scale-out (B) is correct because it allows read-only queries to be offloaded to a read-only replica of the database, freeing up the primary replica for write transactions. This feature is built into Azure SQL Database at the Business Critical and Premium service tiers, and it requires no application code changes—just a connection string modification to use the `ApplicationIntent=ReadOnly` parameter. It directly addresses the performance bottleneck caused by concurrent read queries during the flash sale.

Answer

Active geo-replication

Answer

Auto-failover groups

Answer

Elastic pools

Question 2

A company has an existing IoT application that uses Apache Cassandra for time-series sensor data. They want to migrate to Azure's fully managed NoSQL database service while continuing to use the Cassandra Query Language (CQL) and benefiting from global distribution and low latency. Which Azure Cosmos DB API should they use?

Accepted Answer

Cassandra API. The Cassandra API for Azure Cosmos DB is wire-protocol-compatible with Apache Cassandra, meaning you can use existing CQL (Cassandra Query Language) tools, drivers, and code with minimal changes. It provides a fully managed, globally distributed NoSQL database with low-latency reads and writes, which directly matches the company's requirement to migrate from self-managed Cassandra while preserving their CQL-based application logic.

Answer

Core (SQL) API

Answer

MongoDB API

Answer

Gremlin API

Question 3

A company is migrating their on-premises data warehouse, which is built on a Netezza appliance, to Azure. The data warehouse contains over 10 terabytes of data and supports complex BI queries with multiple joins and aggregations. The company requires a cloud-based solution that provides massively parallel processing (MPP) to handle large-scale queries efficiently. They also need to integrate with existing ETL tools like Azure Data Factory and provide native connectivity to Power BI. Which Azure service should they choose?

Accepted Answer

Azure Synapse Analytics dedicated SQL pool. Azure Synapse Analytics dedicated SQL pool is the correct choice because it provides massively parallel processing (MPP) architecture designed for petabyte-scale data warehousing, exactly matching the 10+ TB requirement. It natively integrates with Azure Data Factory for ETL and offers built-in Power BI connectivity via the T-SQL endpoint, supporting complex BI queries with multiple joins and aggregations.

Answer

Azure SQL Database

Answer

Azure Databricks

Answer

Azure HDInsight

Question 4

A company uses Azure Synapse Analytics dedicated SQL pool for a large data warehouse. The fact table contains billions of rows and is hash-distributed on ProductID. Frequent queries join this fact table with a small Store dimension table (10,000 rows) and a medium-sized Product dimension table (500,000 rows). The queries aggregate sales by store and product for recent months, but run slowly due to data movement during joins. Which design change will most reduce data movement and improve query performance?

Accepted Answer

Replicate the Store dimension table. Replicating the small Store dimension table (10,000 rows) across all compute nodes eliminates the need to shuffle data during joins with the fact table. In Azure Synapse dedicated SQL pool, replicated tables store a full copy on each distribution, so queries that join a replicated table with a distributed fact table avoid costly data movement, significantly improving performance for frequent aggregation queries.

Answer

Change the distribution of the fact table to round-robin

Answer

Change the distribution key of the fact table to StoreID

Answer

Add a nonclustered index on the StoreID column in the fact table

Question 5

Which TWO of the following are benefits of using Azure SQL Database over SQL Server on Azure Virtual Machines?

Accepted Answer

Built-in high availability with automatic failover. Options A and B are correct. Azure SQL Database provides built-in high availability with automatic failover, which is a key benefit over SQL Server on Azure VMs where you must configure HA manually. Additionally, Azure SQL Database automatically handles patching and updates, reducing administrative overhead. Option C is incorrect because while you can choose vCores in both services, cost comparison is more complex and typically Azure SQL Database might have different pricing, but the ability to choose any number of vCores is not a unique benefit. Option D is incorrect because you cannot install custom software on an Azure SQL Database server; that's a limitation of PaaS. Option E is incorrect because full control over the OS is a characteristic of IaaS (VMs), not a benefit of Azure SQL Database.

Answer

Lower cost because you can choose any number of vCores

Answer

Ability to install custom software on the database server

Answer

Full control over the operating system

Question 6

Which TWO of the following are true about Azure Database for PostgreSQL? (Select TWO.)

Accepted Answer

It supports read replicas to offload read traffic.. Azure Database for PostgreSQL is a managed relational database service that provides automated backups with point-in-time restore (option E) and supports read replicas to offload read traffic (option C). Option A is incorrect because it supports both the community edition and the Hyperscale (Citus) edition, not just the community edition. Option B is incorrect because Azure Database for PostgreSQL is a relational database, not a NoSQL database. Option D is incorrect because cross-database queries across multiple servers are not supported; each server is isolated.

Answer

It only supports the open-source community edition of PostgreSQL.

Answer

It is a NoSQL database service.

Answer

It allows cross-database queries across multiple servers.

Question 7

A company runs an e-commerce application backed by an on-premises SQL Server database. They plan to migrate to Azure SQL Database and require automatic failover across two Azure regions for disaster recovery. The application must continue to connect using the same connection string after a failover, with no code changes. Which feature should they implement?

Accepted Answer

Failover groups. Failover groups (Option C) enable automatic, geo-redundant failover across two Azure regions while providing a single read-write listener endpoint that remains unchanged after failover. This ensures the application can continue using the same connection string without any code modifications, meeting the stated requirement for disaster recovery with zero application changes.

Answer

Active Geo-Replication

Answer

Elastic pools

Answer

SQL Server on Azure Virtual Machine with Always On Availability Groups

Question 8

Refer to the exhibit. An administrator creates a storage account with the Hot tier and then creates a container with the Cool tier. Data is uploaded to the container. Which access tier applies to the uploaded blobs by default?

Accepted Answer

Cool, because the container's default tier is Cool.. In Azure Blob Storage, when a container has a default access tier set, blobs uploaded without explicitly specifying a tier inherit the container's default tier. In the exhibit, the container's access tier is Cool, so blobs uploaded to that container will be Cool by default. Option A is incorrect because the container-level tier overrides the storage account tier for new blobs. Option B is incorrect because blobs are charged based on their assigned tier. Option C is incorrect because the Archive tier is not automatically applied; the container's default tier is Cool. Therefore, Option D is correct.

Answer

Hot, because the storage account tier is Hot.

Answer

No tier; blobs are not charged until accessed.

Answer

Archive, because no tier is set.

Question 9

A company has an Azure SQL Database that stores sensitive financial data. They need to ensure that database administrators (DBAs) cannot view the actual data but can still perform administrative tasks. Which feature should they implement?

Accepted Answer

Dynamic data masking. Dynamic data masking hides sensitive data from non-privileged users, including DBAs if they are not exempted. Option A is wrong because Azure RBAC controls access but doesn't mask data. Option C is wrong because TDE encrypts at rest but does not hide data from DBAs. Option D is wrong because Azure SQL Database auditing logs actions but doesn't prevent viewing.

Answer

Azure role-based access control (RBAC)

Answer

Transparent data encryption (TDE)

Answer

Azure SQL Database auditing

Question 10

A manufacturing company collects sensor readings from thousands of IoT devices. Each reading consists of a device ID, a timestamp, and a numeric value. The data is stored as key-value pairs and must support low-latency reads and writes at a global scale. The company also needs to query the data by device ID and time range. Which Azure Cosmos DB API should they choose?

Accepted Answer

Table API. The Table API is the correct choice because it is designed for key-value workloads with a schema-less design, supporting low-latency reads and writes at global scale. It allows querying by partition key (device ID) and row key (timestamp) to efficiently retrieve data by device ID and time range, matching the IoT sensor data requirements.

Answer

Core (SQL) API

Answer

MongoDB API

Answer

Gremlin API

Question 11

A company is migrating a 500 GB financial database to Azure. The database requires low read/write latency, supports a high number of concurrent transactions, and must have a Recovery Point Objective (RPO) of less than 5 seconds and a Recovery Time Objective (RTO) of less than 30 minutes. The company is willing to pay more for these guarantees. Which Azure SQL Database service tier should they choose?

Accepted Answer

Business Critical. Business Critical is the correct choice because it provides the lowest read/write latency through always-on secondary replicas and uses local SSD storage, which is essential for high-concurrency transactional workloads. It also guarantees an RPO of less than 5 seconds via synchronous data replication and an RTO of under 30 minutes, meeting the strict recovery requirements.

Answer

General Purpose

Answer

Hyperscale

Answer

Serverless (General Purpose)

Question 12

A retail company collects raw clickstream data from its website as JSON files. Data scientists need to run exploratory analytics on this raw data without a predefined schema. BI analysts also need to generate weekly sales reports from aggregated transactional data stored in a relational format. Which combination of data storage approaches best meets these needs?

Accepted Answer

Store raw data in Azure Data Lake Storage and aggregated data in Azure SQL Database. Azure Data Lake Storage (ADLS) is optimized for storing raw, schema-on-read data like JSON files, enabling data scientists to run exploratory analytics without a predefined schema. Azure SQL Database provides a relational structure with ACID compliance, ideal for BI analysts generating weekly sales reports from aggregated transactional data. This combination directly addresses both unstructured raw data and structured reporting needs.

Answer

Store raw data in Azure Blob Storage and aggregated data in Azure Cosmos DB

Answer

Store raw data in Azure Table Storage and aggregated data in Azure Data Lake Storage

Answer

Store raw data in Azure SQL Database and aggregated data in Azure Blob Storage

Question 13

A logistics company ingests real-time GPS data from delivery vehicles via Azure Event Hubs. The data includes vehicle ID, latitude, longitude, and timestamp. The company also has historical route plan data stored as CSV files in Azure Data Lake Storage Gen2. Data analysts need to combine the live stream with the historical data in near real-time to create a dashboard showing if vehicles are on schedule. They also need to run complex T-SQL queries on the combined dataset for ad-hoc reporting. Which Azure service should they use as the primary analytics platform?

Accepted Answer

C: Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it provides a unified analytics platform that can ingest real-time data from Azure Event Hubs via its built-in streaming capabilities (e.g., using Synapse Pipelines or Spark Structured Streaming) and combine it with historical data stored in Azure Data Lake Storage Gen2. It supports complex T-SQL queries through its dedicated SQL pool (formerly SQL Data Warehouse) for ad-hoc reporting, enabling near real-time dashboards and interactive analytics on the combined dataset.

Answer

A: Azure Stream Analytics

Answer

B: Azure Data Lake Analytics

Answer

D: Azure Analysis Services

Question 14

A hospital uses Azure SQL Database to store patient appointment records. The 'Appointments' table has columns: AppointmentID (primary key), PatientID, DoctorID, AppointmentDate, and Status. Queries frequently filter by DoctorID (equality) and AppointmentDate (range) to retrieve a doctor's schedule. Currently, these queries are slow. Which index strategy will most improve performance for these queries?

Accepted Answer

Add a nonclustered index on (DoctorID, AppointmentDate).. A nonclustered index on (DoctorID, AppointmentDate) supports both equality filtering on DoctorID and range filtering on AppointmentDate. This index structure allows SQL Server to perform a single index seek for the doctor, then a range scan within that doctor's appointments, avoiding a full table scan. The order of columns matters: the leading column (DoctorID) handles the equality predicate, and the second column (AppointmentDate) handles the range predicate efficiently.

Answer

Add a clustered index on AppointmentID.

Answer

Add a columnstore index on the Status column.

Answer

Add a nonclustered index on (AppointmentDate, DoctorID).

Question 15

A smart home company stores sensor readings from thousands of devices in Azure Cosmos DB. Each reading includes a deviceID, timestamp (ISO format), sensor type, and value. The most common query retrieves all readings for a specific device within a time range. To minimize Request Units (RU) consumption and ensure even data distribution, which property should be chosen as the partition key?

Accepted Answer

A) deviceID. DeviceID is the correct partition key because it is the primary filter in the most common query (all readings for a specific device within a time range). Partitioning by deviceID ensures that all readings for a single device are stored in the same logical partition, making queries highly efficient by targeting a single partition. It also provides even data distribution across physical partitions, as thousands of devices will have roughly equal numbers of readings, minimizing RU consumption.

Answer

B) timestamp

Answer

C) sensor type

Answer

D) value