DP-900 Practice Test 29 — 15 Questions

Question 1

A company stores customer orders in a relational database. The database enforces rules that every order must have a unique order number and must be linked to an existing customer record. This enforcement of rules to ensure accuracy and consistency is an example of which data concept?

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

Accepted Answer

Data integrity. Data integrity refers to the enforcement of rules that ensure the accuracy, consistency, and reliability of data throughout its lifecycle. In this scenario, the relational database enforces entity integrity (unique order numbers) and referential integrity (linking orders to existing customer records), which are core mechanisms for maintaining data correctness.

Answer

Data schema

Answer

Data redundancy

Answer

Data latency

Question 2

A social media company stores user posts in Azure Cosmos DB. Posts are frequently queried by user ID and creation timestamp. To minimize Request Units (RU) per query, which property should be chosen as the partition key?

Accepted Answer

User ID. User ID is the correct partition key because it evenly distributes writes and reads across physical partitions, ensuring that queries filtering by user ID and timestamp are scoped to a single partition. This minimizes cross-partition queries, which consume more Request Units (RU) than single-partition queries. Azure Cosmos DB routes each query to the partition containing the matching partition key value, so choosing User ID keeps most queries efficient.

Answer

Timestamp

Answer

Post content

Answer

A composite key of user ID and timestamp

Question 3

A healthcare application stores patient medical history in a relational database. The system must ensure that after a transaction updates multiple records (e.g., diagnosis and medication), all changes are saved or none are saved. This property is best described as:

Accepted Answer

Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. In the context of a relational database storing patient medical history, if a transaction updates both the diagnosis and medication records, atomicity guarantees that either both updates are committed or both are rolled back, preventing partial updates that could leave the data in an inconsistent state.

Answer

Consistency

Answer

Durability

Answer

Isolation

Question 4

A banking application processes fund transfers. When a transfer is executed, the system must either successfully debit one account and credit the other, or if any step fails, the entire operation must be rolled back so no partial changes remain. Which ACID property directly enforces this behavior?

Accepted Answer

A) Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. In this banking scenario, the debit and credit operations are part of one transaction; if either step fails, the entire transaction is rolled back, leaving no partial changes. This is the core property that enforces the 'all-or-nothing' behavior described.

Answer

B) Consistency

Answer

C) Isolation

Answer

D) Durability

Question 5

A healthcare application stores patient vital signs readings. Each reading is a JSON document with fields: PatientID, Timestamp, HeartRate, BloodPressure (systolic and diastolic). The application frequently queries for all readings of a specific patient within a time range, and the schema varies occasionally (e.g., new optional fields are added). How should this data be classified?

Accepted Answer

Semi-structured. The data is semi-structured because it is stored as JSON documents, which have a flexible schema that can vary between records (e.g., new optional fields can be added). JSON documents are self-describing and do not require a fixed schema like relational tables, but they still have organizational properties (fields like PatientID, Timestamp) that distinguish them from unstructured data like plain text or images. The application's queries on specific fields (PatientID, Timestamp) further confirm the data has structure, but the schema flexibility rules out structured or relational classifications.

Answer

Structured

Answer

Unstructured

Answer

Relational

Question 6

A data engineering team needs to transform large datasets stored in Azure Data Lake Storage Gen2 using Apache Spark with Python code. They want a fully managed service that provides serverless Spark pools, meaning no clusters to manage and automatic scaling. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics with serverless Spark pools. Azure Synapse Analytics with serverless Spark pools is the correct choice because it provides a fully managed, serverless Apache Spark environment that automatically scales and eliminates the need to manage clusters. This service directly supports transforming large datasets in Azure Data Lake Storage Gen2 using Python code with Spark, meeting the team's requirement for a no-cluster-management, auto-scaling solution.

Answer

Azure HDInsight

Answer

Azure Databricks

Answer

Azure Machine Learning

Question 7

A university's enrollment system stores data in a single table with columns: EnrollmentID, StudentID, StudentName, CourseID, CourseName, and Grade. Students can take multiple courses, and each course has multiple students. The team notices data redundancy: StudentName is repeated for each enrollment of the same student, and CourseName is repeated for each enrollment in the same course. They want to reduce redundancy while preserving the ability to query all enrollments with student and course details. What is the most appropriate design approach?

Accepted Answer

Normalize the schema by creating separate Students, Courses, and Enrollments tables with foreign keys. Normalizing the schema into separate Students, Courses, and Enrollments tables eliminates data redundancy by storing each student's name and each course's name only once, while using foreign keys to maintain relationships. This preserves the ability to query all enrollments with student and course details via JOIN operations, which is the standard relational database design principle for reducing anomalies and storage overhead.

Answer

Keep the single table but use compression to reduce storage

Answer

Create a view that mirrors the single table but physically store data in separate normalized tables

Answer

Denormalize by adding more columns to the single table

Question 8

A company uses Azure Synapse Analytics dedicated SQL pool to store sales data. They frequently run queries that aggregate sales by product and region over the past month. The queries are slow because they scan the entire table. Which index type should they implement on the fact table to improve query performance for these aggregations?

Accepted Answer

Clustered columnstore index. A clustered columnstore index is ideal for large fact tables in Azure Synapse Analytics dedicated SQL pool because it stores data column-wise, enabling high compression and eliminating the need to scan irrelevant columns. For aggregation queries that sum sales by product and region over the past month, the columnstore index significantly reduces I/O by reading only the necessary columns and applying batch-mode processing, which accelerates scan and aggregation operations.

Answer

Clustered index on the primary key

Answer

Hash-distributed table on SalesID

Answer

Non-clustered index on (ProductID, Region)

Question 9

A company stores customer records in a relational database table with fixed columns (CustomerID, Name, Email). They also store product reviews as JSON documents that may contain varying fields such as Rating, Comment, and optional Tags. Additionally, they store product images as JPEG files. Which of the following correctly orders these data types from most structured to least structured?

Accepted Answer

Relational table, JSON documents, image files. Relational tables enforce a fixed schema with predefined columns and data types, making them the most structured. JSON documents have a flexible schema where fields like Tags are optional, placing them in the middle. Image files are binary blobs with no inherent structure, making them the least structured. Option B correctly orders these from most structured (relational table) to least structured (image files).

Answer

JSON documents, relational table, image files

Answer

Image files, relational table, JSON documents

Answer

Relational table, image files, JSON documents

Question 10

A global social media app uses Azure Cosmos DB (NoSQL API) to store user profile data. The app is read-heavy and must serve content with the lowest possible latency to users worldwide. The data is updated by users, and the business has determined that eventual consistency is acceptable because immediate consistency after a write is not critical for profile views. Which consistency level should they choose to minimize read latency?

Accepted Answer

Eventual. Eventual consistency is the correct choice because it offers the lowest read latency by allowing reads to return data immediately without waiting for replication to complete. Since the app is read-heavy, global, and can tolerate eventual consistency for profile views, this consistency level minimizes the time to serve content by not imposing any ordering or staleness guarantees on replicas.

Answer

Strong

Answer

Bounded staleness

Answer

Session

Question 11

A company stores customer data in a SQL Server database table with columns: CustomerID (integer), Name (varchar), Email (varchar), SignupDate (date). All rows adhere to this schema. Which type of data does this represent?

Accepted Answer

Structured data. This data is structured because it conforms to a fixed schema with clearly defined columns (CustomerID, Name, Email, SignupDate) and data types (integer, varchar, date). In SQL Server, structured data is stored in tables with rows and columns, enabling efficient querying via T-SQL and indexing. The consistent adherence to the schema across all rows is the hallmark of structured data.

Answer

Unstructured data

Answer

Semi-structured data

Answer

Transactional data

Question 12

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?

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 13

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 14

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 15

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)