DP-900 Practice Test 26 — 15 Questions

Question 1

A company stores customer information in a SQL database table with columns: CustomerID, FirstName, LastName, Email, SignupDate. They also store product images as JPEG files in Azure Blob Storage. Which statement correctly describes the types of data involved?

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

Accepted Answer

Customer data is structured, product images are unstructured.. Customer data stored in a SQL database table with defined columns (CustomerID, FirstName, LastName, Email, SignupDate) is structured because it adheres to a fixed schema with rows and columns. Product images stored as JPEG files in Azure Blob Storage are unstructured because they lack a predefined data model and are stored as binary large objects (BLOBs) without a schema. Option B correctly identifies this distinction.

Answer

Customer data is unstructured, product images are semi-structured.

Answer

Both are structured.

Answer

Customer data is semi-structured, product images are unstructured.

Question 2

A retail company uploads daily sales data from all stores to Azure Blob Storage at midnight. They then run a series of data transformations using Azure Data Factory on a scheduled trigger at 2:00 AM. This processing pattern is best described as:

Accepted Answer

Batch processing. This pattern is batch processing because the sales data is collected in Azure Blob Storage over a period (daily) and then processed as a group at a scheduled time (2:00 AM) using Azure Data Factory. Batch processing is designed for high-volume, periodic data loads where latency is acceptable, and the transformation job runs on a complete dataset rather than individual records.

Answer

Stream processing

Answer

Transactional processing

Answer

Interactive query

Question 3

An e-commerce application uses Azure SQL Database. During flash sales, the database experiences high CPU usage and query timeouts. The team needs a solution that can handle sudden increases in demand without downtime. Which scaling option should they choose?

Accepted Answer

Hyperscale service tier. The Hyperscale service tier is designed for high-performance, rapidly growing workloads that require instant scalability. It separates compute from storage, allowing compute nodes to be added or scaled up in seconds without downtime, making it ideal for handling sudden spikes in demand like flash sales.

Answer

Read scale-out

Answer

Elastic Pool

Answer

Geo-replication

Question 4

A company stores customer names and addresses in a fixed-format file where each record has the same fields in the same order. This type of data is best described as:

Accepted Answer

Structured data. A fixed-format file where each record has the same fields in the same order is a classic example of structured data. Structured data conforms to a rigid schema, such as a table with defined columns and data types, making it easily searchable and processable by relational database systems like Azure SQL Database. The consistent field order and fixed format allow for direct parsing without interpretation.

Answer

Semi-structured data

Answer

Unstructured data

Answer

Streaming data

Question 5

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?

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 6

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 7

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 8

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 9

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 10

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 11

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 12

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 13

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 14

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 15

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