DP-900 Practice Test 27 — 15 Questions

Question 1

A library management system uses Azure SQL Database. The Books table has 500,000 rows with columns: BookID (primary key, clustered), Title, Author, ISBN, PublishedYear, CopiesAvailable. Queries frequently filter by Author and then sort results by PublishedYear in descending order. The queries also return the Title and CopiesAvailable columns. Which indexing strategy will most improve query performance for these operations?

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

Accepted Answer

Create a nonclustered index on (Author, PublishedYear DESC) and include (Title, CopiesAvailable). It creates a covering nonclustered index on (Author, PublishedYear DESC) that directly supports the filter (Author) and sort (PublishedYear DESC) operations. Including Title and CopiesAvailable as non-key columns makes the index covering, meaning all required columns are in the index leaf level, so SQL Server can satisfy the query entirely from the index without key lookups to the clustered index. This minimizes I/O and improves query performance.

Answer

Create a nonclustered index on Author only

Answer

Create a nonclustered index on PublishedYear DESC

Answer

Keep only the existing clustered index on BookID

Question 2

A company uses Azure SQL Database for an order management system. They have a table 'Orders' with columns: OrderID (PK), CustomerID, OrderDate, TotalAmount. Queries that filter on OrderDate are slow. They create a nonclustered index on OrderDate. However, after many inserts, the index becomes fragmented and page splits occur frequently. Which action should the DBA take to maintain query performance?

Accepted Answer

Rebuild the index online. Rebuilding the index online eliminates fragmentation and page splits without blocking concurrent queries, which is critical for a production order management system. The ALTER INDEX REBUILD operation reorganizes the index B-tree structure, consolidating pages and reducing logical fragmentation, thereby restoring query performance on OrderDate filters.

Answer

Drop and recreate the index

Answer

Add a clustered index on OrderDate

Answer

Change the index to a clustered columnstore index

Question 3

A data engineering team is designing a modern data warehouse on Azure. They have raw data landing in Azure Data Lake Storage Gen2 (ADLS Gen2) as Parquet files. They need to perform transformations using Apache Spark, and then load the transformed data into Azure Synapse Analytics for high-performance analytical queries. The team wants to use a single orchestration service to schedule, monitor, and manage the entire pipeline. Which Azure service should they choose for orchestration?

Accepted Answer

Azure Data Factory. Azure Data Factory (ADF) is the correct choice because it is a cloud-based ETL and orchestration service designed to schedule, monitor, and manage data pipelines at scale. It natively supports triggers (e.g., time-based, event-based) and can orchestrate Apache Spark transformations via Azure Databricks or HDInsight, then load the transformed data into Azure Synapse Analytics using built-in copy activities or pipelines. ADF provides a single pane of glass for end-to-end pipeline management, including dependency handling and error monitoring.

Answer

Azure Databricks

Answer

Azure Logic Apps

Answer

Azure Data Lake Analytics

Question 4

A company uses Azure SQL Database for its e-commerce platform. During a traffic spike, queries against the Orders table become slow. The table has 10 million rows and is clustered on OrderId. The most common query filters by CustomerId and OrderDate range. Which index change would most improve performance?

Accepted Answer

Create a nonclustered index on (CustomerId, OrderDate). The most common query filters by CustomerId and OrderDate, so a nonclustered index on (CustomerId, OrderDate) provides a covering index that allows the database engine to quickly locate rows without scanning the entire clustered index. This index order supports equality on CustomerId and range scans on OrderDate, which is optimal for the query pattern. In Azure SQL Database, this reduces I/O and improves response time during traffic spikes.

Answer

Create a clustered index on CustomerId

Answer

Partition the table by OrderId

Answer

Create a nonclustered index on (OrderDate, CustomerId)

Question 5

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 6

A mobile app stores user preferences in Azure Cosmos DB using the NoSQL API. The app frequently reads a single user's profile by user ID (the partition key). The development team wants the fastest possible read performance globally and is willing to accept that reads might not reflect the latest write immediately. Which consistency level should they choose to minimize read latency?

Accepted Answer

Eventual. Eventual consistency offers the lowest read latency because it allows reads to return data from any replica without waiting for confirmation that the write has been fully replicated. Since the app can tolerate stale reads (i.e., not reflecting the latest write immediately), Eventual consistency eliminates the synchronization overhead required by stronger models, making it the fastest choice for global read performance.

Answer

Strong

Answer

Bounded staleness

Answer

Session

Question 7

A logistics company needs to analyze GPS data from delivery trucks in real time to detect delays and reroute deliveries. The GPS data is streamed into Azure Event Hubs. They also need to combine this live data with static route information stored in Azure SQL Database. Which Azure service should they use for the real-time processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time stream processing, capable of ingesting data from Azure Event Hubs, applying SQL-like queries to detect delays, and joining live streams with static reference data from Azure SQL Database. This enables the logistics company to analyze GPS data as it arrives and trigger rerouting actions with minimal latency.

Answer

Azure Data Factory

Answer

Azure Analysis Services

Answer

Azure Synapse Pipelines

Question 8

A data analytics team stores sales transaction data in Parquet files in Azure Data Lake Storage Gen2. They want to run complex analytical queries that join this data with dimension tables stored in Azure Synapse Analytics dedicated SQL pool. The team prefers not to move or copy the data from the data lake. Which feature should they use to query the data lake data directly?

Accepted Answer

PolyBase external tables. PolyBase external tables in Azure Synapse Analytics dedicated SQL pool allow you to query data stored in Azure Data Lake Storage Gen2 (ADLS Gen2) directly using T-SQL, without moving or copying the data. This is the correct feature because it enables complex analytical joins between the Parquet files in the data lake and the dimension tables in the dedicated SQL pool, leveraging the external table's ability to read Parquet format natively.

Answer

Azure Data Factory pipelines

Answer

Azure Stream Analytics

Answer

Azure Databricks notebooks

Question 9

A company stores customer data in a SQL Server table with fixed columns (CustomerID, Name, Email, SignupDate). The company also stores application logs as JSON documents and marketing images as JPEG files. Which data type describes the customer data?

Accepted Answer

Structured data. Customer data stored in a SQL Server table with fixed columns (CustomerID, Name, Email, SignupDate) follows a rigid schema where each row has the same set of columns with defined data types. This conforms to the relational model, making it structured data. Structured data is organized into rows and columns with a fixed schema, enabling efficient querying via SQL.

Answer

Semi-structured data

Answer

Unstructured data

Answer

Relational data

Question 10

Your company is designing a data solution for IoT sensor data that arrives in high volume and must be stored for long-term analytics. The data is append-only and rarely updated. You need to choose a storage solution that balances cost and query performance for historical analysis. Which Azure data store should you recommend?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 is the correct choice because it combines a hierarchical namespace with Azure Blob Storage, offering scalable, cost-effective storage for high-volume append-only data like IoT sensor logs. It supports both structured and unstructured data, integrates with analytics engines like Azure Synapse and Spark, and provides POSIX-compliant access control, making it ideal for long-term historical analysis at low cost.

Answer

Azure Cosmos DB

Answer

Azure Table Storage

Answer

Azure SQL Database

Question 11

Refer to the exhibit. You are reviewing an ARM template for a new storage account. The storage account will store data that must be accessible from any Azure region and must be highly durable. Which change should you make to the template?

Accepted Answer

Change the SKU name to Standard_GRS. Standard_GRS (Geo-Redundant Storage) is the correct SKU because it replicates data synchronously three times within a primary region and asynchronously to a secondary region hundreds of miles away, ensuring high durability (11 nines) and accessibility from any Azure region via read-access (RA-GRS). The requirement for data to be accessible from any Azure region and highly durable aligns with GRS's geo-replication, whereas LRS only replicates within a single datacenter and Premium_LRS is for low-latency workloads, not geo-accessibility.

Answer

Set supportsHttpsTrafficOnly to false

Answer

Change the SKU name to Premium_LRS

Answer

Change the kind to BlobStorage

Question 12

A company is migrating an on-premises SQL Server database to Azure. The database uses SQL Server Integration Services (SSIS) packages for daily ETL processes. The company wants to minimize administrative overhead for patching and backup management, but needs to retain full control over instance-level configurations and support for SSIS. 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 on-premises SQL Server, including full support for SQL Server Integration Services (SSIS) via Azure-SSIS Integration Runtime, while offloading patching and backup management to the platform. It also allows full control over instance-level configurations such as collation, CLR, and SQL Agent jobs, which are not available in Azure SQL Database.

Answer

Azure SQL Database

Answer

Azure Synapse Analytics

Answer

Azure SQL Server on Azure Virtual Machines

Question 13

A data analyst receives a dataset containing customer order details stored in a CSV file, a JSON file with product reviews, and a folder of JPEG images of products. Which of the following correctly categorizes these data types from most structured to least structured?

Accepted Answer

CSV → JSON → JPEG. CSV files are highly structured with rows and columns defined by a schema, making them the most structured. JSON files are semi-structured, using key-value pairs and nested objects that allow flexibility but lack a fixed schema. JPEG images are unstructured binary data with no inherent schema, so the correct order from most to least structured is CSV → JSON → JPEG, making option C correct.

Answer

CSV → JPEG → JSON

Answer

JSON → CSV → JPEG

Answer

JPEG → JSON → CSV

Question 14

A multinational corporation uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage. The security team requires that access to specific columns containing personally identifiable information (PII) be restricted based on the user's role. Which feature should be implemented?

Accepted Answer

Column-level security. Column-level security (CLS) in Azure Synapse Analytics serverless SQL pool allows you to restrict access to specific columns containing PII based on the user's role or identity. By granting or denying SELECT permissions on individual columns, you can ensure that only authorized users see sensitive data while others see NULL or an error. This directly meets the requirement to restrict column access by role.

Answer

Row-level security (RLS)

Answer

Azure Purview data classification

Answer

Dynamic data masking

Question 15

Your company runs a sales analytics dashboard on Power BI that refreshes every hour from Azure Synapse Analytics. During peak hours, the dashboard refresh fails with a 'timeout' error. Which action should you take FIRST to resolve the issue?

Accepted Answer

Implement incremental refresh in Power BI to refresh only changed data.. Implementing incremental refresh in Power BI reduces the amount of data loaded during each refresh cycle, which directly addresses timeout errors by limiting the refresh to only changed or new data rather than the entire dataset. This is the most efficient first step to reduce refresh duration without changing the underlying architecture or data source connection mode.

Answer

Scale up the Azure Synapse dedicated SQL pool to handle more concurrent queries.

Answer

Configure the dashboard to use DirectQuery instead of Import mode.

Answer

Export the data to CSV files and load into Power BI from Azure Blob Storage.