DP-900 Practice Test 12 — 15 Questions

Question 1

A retail company stores product inventory data in a SQL database, customer reviews as JSON files, and product images as JPEG files. Which of the following accurately describes the types of data stored?

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

Accepted Answer

D. Structured, semi-structured, and unstructured data are stored.. The company stores product inventory data in a SQL database, which enforces a fixed schema (tables, rows, columns) and is therefore structured data. Customer reviews stored as JSON files are semi-structured because they have a flexible schema (key-value pairs) but no rigid table structure. Product images as JPEG files are unstructured because they lack any predefined schema or organization. Option D correctly identifies that all three data types are present.

Answer

A. Only structured data is stored because the SQL database contains the primary records.

Answer

B. Only semi-structured and unstructured data is stored because JSON and images are not purely structured.

Answer

C. Only unstructured data is stored because images have no predefined schema.

Question 2

A startup has an application with unpredictable usage patterns on Azure SQL Database. They want to minimize cost by paying only for the compute they use and the database should automatically pause during idle periods. Which Azure SQL Database option should they choose?

Accepted Answer

Serverless. Azure SQL Database Serverless is designed for applications with unpredictable usage patterns, as it automatically scales compute resources based on demand and pauses the database during idle periods to eliminate compute costs. This model charges per second for the compute used, making it the most cost-effective choice for workloads that have periods of inactivity.

Answer

Provisioned (DTU or vCore)

Answer

Hyperscale

Answer

Business Critical

Question 3

A company operates an online store that processes customer orders. When a customer places an order, the system must immediately reduce the inventory count for the purchased items and record the order details. At the end of each month, the company runs reports that aggregate sales data over the past month to analyze trends. Which type of data processing workload best describes the order placement activity?

Accepted Answer

Transactional processing. Order placement requires immediate inventory reduction and order recording, which demands ACID (Atomicity, Consistency, Isolation, Durability) guarantees. This is a classic transactional processing workload, typically handled by OLTP (Online Transaction Processing) systems like SQL Server or Azure SQL Database, ensuring data integrity even under concurrent access.

Answer

Analytical processing

Answer

Batch processing

Answer

Stream processing

Question 4

A company plans to migrate an on-premises SQL Server database to Azure. The database uses SQL Server Agent jobs for scheduled maintenance and relies on linked servers to query data from another SQL Server instance. It also performs cross-database queries within the same instance. The company wants a fully managed PaaS service that requires minimal application changes and provides automated backups and patching. Which Azure SQL service should they choose?

Accepted Answer

B. 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 SQL Server Agent jobs, linked servers, and cross-database queries within the same instance. It is a fully managed PaaS service that offers automated backups and patching, minimizing application changes during migration.

Answer

A. Azure SQL Database (single database)

Answer

C. SQL Server on Azure Virtual Machines

Answer

D. Azure SQL Database elastic pool

Question 5

A company uses Azure Synapse Analytics dedicated SQL pool to store a large fact table containing 5 TB of sales transactions. New data arrives continuously and is loaded daily. The company needs to load 500 GB of new data each day while allowing concurrent read queries on the most recent data without performance degradation. Which loading strategy optimizes both load speed and query performance?

Accepted Answer

Load data into a staging table, then use partition switching to swap the latest partition. Partition switching allows you to load new data into a staging table, then instantly swap the staging partition with the target table's latest partition using ALTER TABLE SWITCH. This minimizes metadata changes and avoids data movement, enabling fast loads while keeping the fact table online for concurrent read queries without blocking or performance degradation.

Answer

Use INSERT statements to add rows incrementally

Answer

Use CREATE TABLE AS SELECT (CTAS) to build a new table and rename it

Answer

Use PolyBase to load data directly into the fact table

Question 6

A mobile gaming company stores player data in Azure Cosmos DB using the Core (SQL) API. Each document contains fields: playerId, nickname, score, level, and an inventory array of item objects (each with name and type). The company wants to query all players whose score is above 5000 and who have a specific item (e.g., a sword) in their inventory. Which query clause should they use?

Accepted Answer

B) WHERE c.score > 5000 AND ARRAY_CONTAINS(c.inventory, {name: 'sword'}, true). ARRAY_CONTAINS with the third parameter set to 'true' performs a partial match, checking if any element in the inventory array has a 'name' property equal to 'sword'. This is the standard way to query for an item within an array of objects in Azure Cosmos DB's SQL API, as it correctly handles the nested structure without requiring a JOIN or subquery.

Answer

A) WHERE c.score > 5000 AND c.inventory.some(item => item.name == 'sword')

Answer

C) WHERE c.score > 5000 AND c.inventory.name == 'sword'

Answer

D) WHERE c.score > 5000 AND 'sword' IN c.inventory

Question 7

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

Question 8

A company is designing an enterprise analytics solution. They store raw data in its original format in a scalable repository, apply schema and transformations at read time, and also maintain a curated layer that enforces ACID transactions for data reliability. This architecture combines the flexibility of a data lake with the reliability of a data warehouse. Which term best describes this modern data architecture?

Accepted Answer

Data lakehouse. The data lakehouse architecture combines the flexibility of a data lake (storing raw data in its original format in a scalable repository) with the reliability of a data warehouse (enforcing ACID transactions in a curated layer). This allows schema-on-read transformations while maintaining data integrity, making it the correct term for the described design.

Answer

Data mart

Answer

Operational database

Answer

Data pipeline

Question 9

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 10

A company operates a high-volume order processing system on Azure SQL Database. During peak hours, many concurrent transactions try to insert and update rows in the same table, causing contention on page latches. Indexing and query optimization are already tuned. Which feature should the company implement to reduce write contention while preserving ACID properties?

Accepted Answer

In-Memory OLTP. In-Memory OLTP is correct because it uses memory-optimized tables and natively compiled stored procedures to reduce latch contention by eliminating the need for page latches entirely. Transactions operate directly on in-memory data structures, using optimistic multi-version concurrency control (MVCC) to detect conflicts without blocking, which preserves ACID properties while allowing high concurrency.

Answer

Read Scale-out

Answer

Elastic Database Query

Answer

Transparent Data Encryption (TDE)

Question 11

A retail company stores customer transaction data in a relational database. Each transaction is recorded with a fixed schema including TransactionID, CustomerID, ProductID, Quantity, and TotalAmount. Which type of data does this represent?

Accepted Answer

Structured data. The data conforms to a fixed schema with defined columns (TransactionID, CustomerID, ProductID, Quantity, TotalAmount) and data types, which is the defining characteristic of structured data. In a relational database, this schema enforces consistency and allows for efficient querying using SQL, making it a classic example of structured data.

Answer

Unstructured data

Answer

Semi-structured data

Answer

Binary data

Question 12

A company stores customer data in a relational table with fixed columns: CustomerID (integer), FirstName (string), LastName (string), Email (string). They also store product images as JPEG files in Azure Blob Storage, and customer feedback as JSON documents where each document may contain fields such as rating, comment, and optional metadata. Which of the following correctly classifies these data types?

Accepted Answer

Relational table – structured, JPEG – unstructured, JSON – semi-structured. A relational table with fixed columns and data types (CustomerID, FirstName, LastName, Email) stores structured data with a rigid schema. JPEG files in Azure Blob Storage are binary blobs with no internal structure that a database can interpret, making them unstructured. JSON documents with optional fields (like rating, comment, metadata) have a flexible schema that can vary per document, which is the definition of semi-structured data.

Answer

Relational table – structured, JPEG – semi-structured, JSON – unstructured

Answer

Relational table – semi-structured, JPEG – unstructured, JSON – structured

Answer

Relational table – unstructured, JPEG – structured, JSON – semi-structured

Question 13

A data engineer needs to build an analytics solution to transform large volumes of streaming data from IoT devices. The transformations involve complex Python and Spark code, and the results will be stored in Azure Data Lake Storage Gen2 for further analysis. Which Azure service is best suited for executing these transformations?

Accepted Answer

Azure Databricks. Azure Databricks is best suited because it provides an Apache Spark-based analytics platform that can execute complex Python and Spark code on large-scale streaming data. It integrates natively with Azure Data Lake Storage Gen2 for reading streaming IoT data and writing transformed results, offering optimized performance for big data transformations.

Answer

Azure Data Factory

Answer

Azure Synapse Pipelines

Answer

Azure Analysis Services

Question 14

A gaming application requires a high-performance leaderboard that stores player scores and retrieves the top 10 scores quickly. The data does not require complex queries or a fixed schema. The leaderboard must support updates as new scores are submitted. Which Azure data store is most appropriate for this scenario?

Accepted Answer

Azure Cache for Redis. Azure Cache for Redis is the most appropriate choice because it provides an in-memory data structure store with native support for sorted sets (via the ZADD and ZRANGE commands), which are ideal for maintaining a real-time leaderboard. It can handle high-throughput score updates and retrieve the top 10 scores in O(log(N)) time, meeting the low-latency and performance requirements without needing a fixed schema.

Answer

Azure Cosmos DB with SQL API

Answer

Azure Table storage

Answer

Azure Blob Storage

Question 15

A data analyst needs to run ad-hoc SQL queries on large datasets stored as Parquet files in Azure Data Lake Storage Gen2. The queries are infrequent and the data volume varies. The analyst wants to pay only for the amount of data processed per query and does not want to manage any infrastructure. They also need to create views in T-SQL to simplify queries for Power BI reports. 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 model, charging only for the amount of data processed, and supports creating T-SQL views that can be used directly by Power BI for simplified reporting.

Answer

Azure Data Lake Analytics

Answer

Azure HDInsight

Answer

Azure Databricks