DP-900 Practice Test 48 — 15 Questions

Question 1

A company stores customer data in a relational database. The database design includes a rule that every order must be associated with a valid customer ID that exists in the Customers table. This rule is an example of which data concept?

Accepted Answer

Referential integrity. Referential integrity ensures that relationships between tables remain consistent. In a relational database, a foreign key constraint enforces that every order's customer ID must match an existing customer ID in the Customers table, preventing orphaned records. This rule directly implements referential integrity as defined by the SQL standard (e.g., via FOREIGN KEY constraints).

Answer

Data normalization

Answer

Entity integrity

Answer

Data consistency

Question 2

A company runs a critical application on Azure SQL Database. They need to ensure that in the event of a regional disaster, the database can be failed over to a secondary region with minimal data loss. The solution should also allow the secondary region to be used for read-only queries. What should they configure?

Accepted Answer

Create a failover group with a readable secondary in the secondary region.. Option A is correct because a failover group with a readable secondary provides automatic failover and read access. Option B (Active Geo-Replication) requires manual failover. Option C (Azure Front Door) is for web traffic. Option D (Cosmos DB) is different.

Answer

Configure Active Geo-Replication and manually failover when needed.

Answer

Use Azure Front Door to route traffic to the secondary region.

Answer

Migrate to Azure Cosmos DB for multi-region writes.

Question 3

A company stores customer names, addresses, and order history. They need to perform complex queries that join customer and order data. Which type of data store is most appropriate for this scenario?

Accepted Answer

Relational database. A relational database (e.g., Azure SQL Database) is most appropriate because the scenario requires joining customer and order data via complex queries. Relational databases enforce a fixed schema with tables, primary keys, and foreign keys, enabling efficient JOIN operations using SQL. This structure ensures data integrity and supports ACID transactions, which are essential for accurate order history and customer records.

Answer

Key-value store

Answer

Document database

Answer

Graph database

Question 4

A data engineer is classifying data types collected from three sources for a data lake. Source 1: Customer records from a SQL database exported as CSV files with fixed columns (CustomerID, Name, Address). Source 2: Product reviews obtained via API as JSON documents with varying fields (e.g., some reviews include 'rating' and 'verified_purchase', others include 'comment'). Source 3: Scanned handwritten order forms saved as TIFF images. Which statement correctly categorizes these data by structure?

Accepted Answer

Source 1: Structured; Source 2: Semi-structured; Source 3: Unstructured. Option A is correct because Source 1 (CSV from SQL) has a fixed schema with defined columns, making it structured data. Source 2 (JSON from API) allows varying fields per document, which is the hallmark of semi-structured data. Source 3 (TIFF images) contains no inherent schema or machine-readable structure, classifying it as unstructured data.

Answer

Source 1: Structured; Source 2: Structured; Source 3: Unstructured

Answer

Source 1: Semi-structured; Source 2: Structured; Source 3: Unstructured

Answer

Source 1: Structured; Source 2: Unstructured; Source 3: Semi-structured

Question 5

A company stores customer order data in a relational database table with columns like OrderID, CustomerID, and OrderDate. They also store product images as JPEG files, and customer feedback as JSON documents with varying fields. Which of the following correctly orders these data types from most structured to least structured?

Accepted Answer

B: Relational table, JSON documents, JPEG files. Relational tables enforce a fixed schema with rows and columns, making them the most structured. JSON documents have a flexible schema with varying fields, placing them in the middle. JPEG files are binary blobs with no inherent structure for querying, making them the least structured. Option B correctly orders these from most structured (relational table) to least structured (JPEG files).

Answer

A: JSON documents, Relational table, JPEG files

Answer

C: JPEG files, Relational table, JSON documents

Answer

D: Relational table, JPEG files, JSON documents

Question 6

A company operates an online store where customers place orders and the system immediately updates inventory and records payments. This workload is best described as:

Accepted Answer

OLTP (Online Transaction Processing). This workload is best described as OLTP because it involves real-time, high-frequency transactions that immediately update inventory and record payments. OLTP systems are designed for concurrent, atomic operations that maintain data integrity, which is exactly what an online store's order processing requires.

Answer

OLAP (Online Analytical Processing)

Answer

Batch processing

Answer

Data warehousing

Question 7

An organization stores sensitive customer data in Azure SQL Database. They need to encrypt the data at rest and ensure that only authorized applications can decrypt it. Which combination of features should they implement?

Accepted Answer

Transparent Data Encryption (TDE) and Always Encrypted. Option C is correct because TDE encrypts the database at rest, and Always Encrypted ensures that only authorized applications with the column encryption key can decrypt sensitive columns. Option A is wrong because row-level security controls access but does not encrypt data. Option B is wrong because dynamic data masking obfuscates data but does not encrypt. Option D is wrong because auditing logs access but does not encrypt.

Answer

Row-Level Security (RLS) and Transparent Data Encryption (TDE)

Answer

Azure SQL Database Auditing and Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking and Always Encrypted

Question 8

A retail company receives real-time data from IoT sensors in its warehouses. Each sensor sends a JSON payload containing a device ID, timestamp, and temperature reading. A data engineer needs to classify this data for storage planning. Which data type best describes the JSON payload?

Accepted Answer

Semi-structured data. The JSON payload is considered semi-structured data because it has organizational properties (key-value pairs, nested structure) that provide a schema, but it does not conform to a rigid tabular schema like a relational database. JSON allows flexible fields and varying data types, which is characteristic of semi-structured data.

Answer

Structured data

Answer

Unstructured data

Answer

Relational data

Question 9

Match each Azure data migration tool to its use case.

Question 10

A company stores customer records in a relational table with columns like CustomerID, Name, and Email. Product reviews are stored as JSON documents, and marketing images are stored as PNG files. Which of the following correctly orders these data types from most structured to least structured?

Accepted Answer

B. Customer records, Product reviews, Marketing images. Customer records in a relational table have a fixed schema with defined columns (e.g., CustomerID, Name, Email), making them the most structured. Product reviews stored as JSON documents are semi-structured because they have a flexible schema with key-value pairs but no fixed columns. Marketing images as PNG files are unstructured binary data with no inherent schema. Option B correctly orders these from most to least structured.

Answer

A. Product reviews, Customer records, Marketing images

Answer

C. Marketing images, Customer records, Product reviews

Answer

D. Customer records, Marketing images, Product reviews

Question 11

A company updates a customer's address in a database. The update must ensure that all existing orders still reference a valid customer ID. The database checks the foreign key constraint and rejects the update if it would violate referential integrity. Which ACID property does this enforcement represent?

Accepted Answer

Consistency. Consistency ensures that any database transaction brings the database from one valid state to another, preserving all defined rules, including constraints like foreign keys. In this scenario, the foreign key constraint enforcement prevents an update that would leave orphaned order records, directly upholding the consistency property by rejecting the transaction if it violates referential integrity.

Answer

Atomicity

Answer

Isolation

Answer

Durability

Question 12

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 13

Drag and drop the steps to create an Azure SQL Database in the correct order.

Question 14

A company stores product information such as product ID, name, price, and category in a relational database with rows and columns. This data is best described as:

Accepted Answer

Structured data. Structured data conforms to a predefined schema with rows and columns, making it easily searchable and queryable via SQL. The product information (ID, name, price, category) fits this model exactly, as each attribute has a fixed data type and is stored in a relational database table.

Answer

Semi-structured data

Answer

Unstructured data

Answer

Transactional data

Question 15

A company stores employee records in a database. Each employee record contains an EmployeeID (unique), Name, Department, and HireDate. The EmployeeID is used to uniquely identify each employee. Which data concept does the EmployeeID represent?

Accepted Answer

Primary key. The EmployeeID is used to uniquely identify each employee record, which is the defining characteristic of a primary key. In relational databases, a primary key enforces entity integrity by ensuring each row has a unique, non-null identifier. This aligns with the core data concept of a primary key as the unique identifier for a table.

Answer

Index

Answer

Foreign key

Answer

Unique constraint