DP-900 Practice Test 48 — 15 Questions

Question 1

A global e-commerce company uses Azure SQL Database for its order management system. They need to ensure high availability with the ability to fail over to an Azure region in a different continent in case of a regional outage. They also want to use the secondary database for read-intensive reporting without affecting the primary's performance. Which Azure SQL Database feature should they enable?

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

Accepted Answer

Active geo-replication. Active geo-replication is the correct choice because it creates readable secondary replicas of an Azure SQL Database in a different Azure region (including a different continent). It supports manual failover to the secondary region during an outage, and the secondary can be used for read-only query workloads like reporting without impacting the primary database's performance.

Answer

Long-term backup retention

Answer

Automatic tuning

Answer

Connection pooling

Question 2

A company has a suite of 20 e-commerce applications, each with its own SQL Server database. The databases vary in size from 5 GB to 100 GB and have unpredictable usage patterns with bursty peaks. The company wants to migrate to Azure SQL Database to benefit from built-in high availability and automatic backups. They need to minimize costs by only paying for the resources each database actually uses, and they want to avoid over-provisioning for peak loads. Which Azure SQL Database deployment option should they choose?

Accepted Answer

Azure SQL Database Elastic Pool. Azure SQL Database Elastic Pool is the correct choice because it allows multiple databases to share a fixed pool of resources (DTUs or vCores), enabling cost efficiency by pooling and reallocating resources across databases with unpredictable, bursty usage patterns. This avoids over-provisioning for peak loads while still providing built-in high availability and automatic backups, as each database in the pool benefits from these features without needing individual resource reservations.

Answer

Azure SQL Database (single database) with Serverless compute tier

Answer

Azure SQL Managed Instance

Answer

Azure SQL Database Hyperscale

Question 3

A retail company is designing a product catalog for its e-commerce website. Each product has a unique ProductID, a name, a price, and a variable number of attributes (e.g., size, color, weight) that differ across product categories. The application requires ability to read a product's details by ProductID with single-digit millisecond latency from any Azure region globally. The schema must be flexible to accommodate new attributes without schema changes. Which Azure data store should the company choose?

Accepted Answer

Azure Cosmos DB using the NoSQL API. Azure Cosmos DB with the NoSQL API is correct because it provides a fully managed, globally distributed NoSQL database that supports flexible schemas (allowing variable product attributes without schema changes) and guarantees single-digit millisecond read latency at any scale from any Azure region via its multi-region write and read replicas. The unique ProductID serves as a natural partition key, enabling efficient point reads with consistent low latency.

Answer

Azure Table Storage

Answer

Azure SQL Database

Answer

Azure Blob Storage

Question 4

A retail company stores product inventory data in a fixed-schema table with columns for ProductID, ProductName, QuantityInStock, and ReorderLevel. How should this data be classified?

Accepted Answer

Structured data. This data is classified as structured data because it conforms to a fixed schema with clearly defined columns (ProductID, ProductName, QuantityInStock, ReorderLevel) and data types, stored in a relational table. Structured data is highly organized, easily queryable via SQL, and follows a rigid schema, which matches the description of the inventory table.

Answer

Semi-structured data

Answer

Unstructured data

Answer

Streaming data

Question 5

A company plans to migrate a 2-TB on-premises SQL Server database to Azure. The database uses SQL Server Agent jobs for scheduled maintenance and requires automatic failover across Azure regions. The company wants a fully managed service with minimal application changes. Which Azure SQL service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is correct because it provides near 100% compatibility with SQL Server, including support for SQL Server Agent jobs, and offers automatic failover across Azure regions via failover groups. It is a fully managed service that requires minimal application changes, unlike Azure SQL Database which lacks SQL Server Agent and has limited cross-region failover capabilities.

Answer

Azure SQL Database

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Synapse Analytics

Question 6

A developer is designing a new application that requires a relational database. The database must support complex queries and stored procedures. Which Azure data service should they choose?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed relational database platform (PaaS) built on the SQL Server engine, supporting T-SQL, complex joins, views, stored procedures, and transactions. It is the correct choice for applications requiring relational modeling with advanced query capabilities.

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Answer

Azure Blob Storage

Question 7

A social media application displays the number of posts each user has created. After a user submits a new post, the count must reflect the update across all servers within a few seconds. Which data consistency model best describes this requirement?

Accepted Answer

Eventual consistency. Eventual consistency is correct because the requirement allows a few seconds for the update to propagate across all servers, meaning the system does not guarantee immediate uniformity but will converge to the same count eventually. This is typical in distributed systems like social media applications where high availability and partition tolerance are prioritized over immediate consistency, often using techniques like asynchronous replication.

Answer

Strong consistency

Answer

Sequential consistency

Answer

Causal consistency

Question 8

A data engineering team needs to build a batch ETL pipeline that transforms large volumes of clickstream data stored as CSV files in Azure Data Lake Storage Gen2. The transformations require running distributed Python and Scala code using Apache Spark. The transformed data will be loaded into a data warehouse for reporting. The team wants a serverless compute environment that automatically scales and charges per second. Which Azure service should they use to run the Spark transformations?

Accepted Answer

Azure Synapse Analytics (Spark pools). Azure Synapse Analytics (Spark pools) is the correct choice because it provides a serverless Apache Spark compute environment that automatically scales and charges per second, perfectly matching the requirement for running distributed Python and Scala transformations on large volumes of clickstream data stored in Azure Data Lake Storage Gen2. The service integrates directly with the data lake and can load transformed results into a dedicated SQL pool for data warehouse reporting.

Answer

Azure Data Factory

Answer

Azure Stream Analytics

Answer

Azure Analysis Services

Question 9

A company is migrating a SQL Server database to Azure SQL Database. The database uses CLR (Common Language Runtime) integration for business logic and has database mail configured. The company needs full instance-level functionality while still benefiting from the platform-as-a-service model. Which Azure SQL deployment option should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with SQL Server on-premises, including support for CLR integration and Database Mail, while still offering a platform-as-a-service (PaaS) model. Single databases and elastic pools lack these instance-scoped features, and Hyperscale is a scaling option for single databases, not a separate deployment type that adds instance-level functionality.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

Azure SQL Database Hyperscale

Question 10

A data analyst needs to run ad-hoc SQL queries on petabytes of log data stored as Parquet files in Azure Data Lake Storage Gen2. The queries join multiple tables and require high concurrency from multiple analysts. The solution should minimize cost by only paying for queries executed. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is the correct choice because it allows running ad-hoc T-SQL queries directly on Parquet files in Azure Data Lake Storage Gen2 without provisioning any infrastructure. It uses a pay-per-query billing model, making it cost-effective for sporadic, high-concurrency workloads where you only want to pay for the compute resources consumed during query execution.

Answer

Azure Synapse Dedicated SQL pool

Answer

Azure HDInsight with Spark

Answer

Azure Databricks

Question 11

A company uses Azure SQL Database for an e-commerce application. The Orders table contains columns: OrderID (int, primary key), CustomerID (int), OrderDate (datetime), TotalAmount (decimal). Queries frequently filter by both CustomerID and OrderDate to retrieve orders for a specific customer within a date range. Which indexing strategy will most improve the performance of these queries?

Accepted Answer

Create a nonclustered index on (CustomerID, OrderDate).. A nonclustered index on (CustomerID, OrderDate) directly supports the query filter that uses both columns. The index is ordered by CustomerID first, enabling SQL Server to quickly locate all rows for a specific customer, and then within that customer, the OrderDate column is ordered to efficiently scan the date range. This index covers the query's WHERE clause without needing to scan the entire table.

Answer

Create a clustered index on OrderID.

Answer

Create a nonclustered index on (OrderDate, CustomerID).

Answer

Create a nonclustered index on TotalAmount.

Question 12

A company needs to run complex SQL queries on petabytes of data stored in Azure Data Lake Storage Gen2. They want to pay only for the queries they run and do not want to manage any infrastructure. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is the correct choice because it enables running complex SQL queries directly against data in Azure Data Lake Storage Gen2 without provisioning any infrastructure. It uses a pay-per-query billing model, charging only for the amount of data processed, which aligns with the requirement to pay only for queries run and avoid infrastructure management.

Answer

Azure Synapse dedicated SQL pool

Answer

Azure SQL Database

Answer

Azure HDInsight

Question 13

A financial services company stores transaction data in Azure Data Lake Storage Gen2 as Parquet files, partitioned by date. The data volume is 5 TB per day. The analytics team runs ad-hoc SQL queries to detect fraudulent patterns. Queries are highly selective (filtering on AccountID and date range). The team also needs to create external tables and views for use in Power BI. They want to pay only for the data processed by each query and avoid provisioning any compute resources. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is the correct choice because it allows querying data directly from Azure Data Lake Storage Gen2 using T-SQL without provisioning any compute resources. It charges per terabyte of data processed, aligning with the requirement to pay only for data scanned by each query. It also supports creating external tables and views for Power BI, making it ideal for ad-hoc, selective queries on partitioned Parquet files.

Answer

Azure Databricks with interactive clusters

Answer

Azure Stream Analytics

Answer

Azure HDInsight with Spark

Question 14

A company uses Azure Synapse Analytics dedicated SQL pool for large-scale data warehousing. They have a fact table with billions of rows and frequently run queries that filter by a date range and join with a product dimension table. Which table distribution and partitioning strategy will minimize data movement and improve query performance?

Accepted Answer

Hash-distribute on ProductID with partitioning on Date. Hash-distributing the fact table on ProductID ensures that rows for the same product are co-located on the same distribution, minimizing data movement when joining with the product dimension table. Partitioning on Date allows partition elimination for date-range filters, reducing the amount of data scanned. This combination directly addresses the query pattern of date-range filtering and product joins.

Answer

Round-robin distribution with no partitioning

Answer

Replicate the fact table on all distributions and partition on ProductID

Answer

Hash-distribute on Date with partitioning on ProductID

Question 15

A company is migrating a 3-TB on-premises SQL Server database to Azure. The database heavily uses cross-database queries with three-part names (e.g., db.schema.table) and relies on SQL Server Agent for scheduled maintenance jobs. They want a fully managed PaaS service with automatic backups and patching, while minimizing application code 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 cross-database queries using three-part names (db.schema.table) and SQL Server Agent for scheduled maintenance jobs. As a fully managed PaaS service, it offers automatic backups, patching, and high availability while minimizing application code changes, unlike Azure SQL Database which lacks cross-database query support and SQL Agent.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database (elastic pool)

Answer

Azure Synapse Analytics dedicated SQL pool