DP-900 Practice Test 47 — 15 Questions

Question 1

In a banking application, a transaction transfers $100 from Account A to Account B. The system deducts $100 from Account A successfully, but due to a network error, the credit to Account B fails. The application rolls back the deduction from Account A, ensuring that neither account is affected. Which ACID property is being enforced?

Accepted Answer

Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. In this scenario, the deduction from Account A and the credit to Account B must both succeed or both fail entirely. When the credit to Account B fails, the system rolls back the deduction from Account A, preserving the all-or-nothing nature of the transaction. This is the core behavior of atomicity in ACID-compliant database systems like Azure SQL Database or SQL Server.

Answer

Consistency

Answer

Isolation

Answer

Durability

Question 2

Match each ACID property with its correct description. Properties: - Atomicity - Consistency - Isolation - Durability Descriptions: 1. Transactions appear to execute one after the other, even if they are concurrent. 2. Once a transaction is committed, the changes are permanently saved and survive failures. 3. A transaction either completes fully or is rolled back entirely. 4. A transaction brings the database from one valid state to another, obeying all rules. Which option correctly maps each property to its description?

Accepted Answer

Atomicity → 3, Consistency → 4, Isolation → 1, Durability → 2. Option A is correct because it accurately maps each ACID property to its definition. Atomicity ensures a transaction is all-or-nothing (3), Consistency guarantees the database moves from one valid state to another (4), Isolation makes concurrent transactions appear serial (1), and Durability ensures committed changes persist even after a failure (2). These are the standard definitions used in Azure SQL Database and other relational database systems.

Answer

Atomicity → 4, Consistency → 3, Isolation → 2, Durability → 1

Answer

Atomicity → 2, Consistency → 1, Isolation → 3, Durability → 4

Answer

Atomicity → 1, Consistency → 2, Isolation → 4, Durability → 3

Question 3

Which TWO Azure services can be used to store relational data?

Accepted Answer

Azure SQL Database. Option A (Azure SQL Database) and Option D (Azure Database for PostgreSQL) are relational database services. Option B is wrong because Azure Cosmos DB is NoSQL. Option C is wrong because Azure Blob Storage is object storage. Option E is wrong because Azure Table Storage is NoSQL.

Answer

Azure Cosmos DB

Answer

Azure Blob Storage

Answer

Azure Table Storage

Question 4

A bank processes a fund transfer that involves deducting money from one account and crediting it to another. The system ensures that both operations succeed together or, if any part fails, the entire transaction is rolled back, leaving both accounts unchanged. Which ACID property does this scenario primarily guarantee?

Accepted Answer

Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. In this fund transfer scenario, both the debit and credit operations must complete successfully, or the entire transaction is rolled back, leaving the accounts unchanged. This all-or-nothing behavior is the defining characteristic of atomicity in ACID transactions.

Answer

Consistency

Answer

Isolation

Answer

Durability

Question 5

A bank processes a fund transfer transaction. The system debits $100 from Account A and then credits $100 to Account B. If the system crashes after debiting Account A but before crediting Account B, the database automatically reverts the debit. Which ACID property ensures this behavior?

Accepted Answer

Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. If any part of the transaction fails (e.g., a crash after debiting Account A but before crediting Account B), the entire transaction is rolled back, reverting any partial changes like the debit. This all-or-nothing behavior is the core of atomicity in database systems.

Answer

Consistency

Answer

Isolation

Answer

Durability

Question 6

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?

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 7

A data engineering team is designing a data warehouse in Azure Synapse Analytics. They need to load data from an on-premises SQL Server database daily. The data volume is about 500 GB per load. They want to minimize data transfer costs and use the fastest possible transfer method. Which approach should they use?

Accepted Answer

Use Azure Data Factory with PolyBase. Option B is correct because Azure Data Factory with PolyBase allows high-speed parallel loading into Azure Synapse. Option A is wrong because using the Azure portal upload is not practical for 500 GB. Option C is wrong because BACPAC export/import is not optimized for large volumes. Option D is wrong because SSIS with Azure Blob is slower and more complex.

Answer

Use the Azure portal to upload a CSV file

Answer

Use SQL Server Integration Services (SSIS) to load to Azure Blob and then into Synapse

Answer

Export to BACPAC and import to Azure SQL Database

Question 8

A company stores customer data in a relational table with columns CustomerID, FullName, and Email. They also store product descriptions as JSON documents with varying fields, and product images as JPEG files. Which of the following correctly classifies these data types from most structured to least structured?

Accepted Answer

Structured, semi-structured, unstructured. The customer data in a relational table with fixed columns (CustomerID, FullName, Email) is structured because it has a rigid schema and defined data types. The JSON documents for product descriptions are semi-structured because they use key-value pairs with flexible fields but still have metadata (tags, keys) that provide organization. The JPEG product images are unstructured because they are binary blobs with no inherent schema or metadata that the database can query directly. This ordering from most to least structured matches option A.

Answer

Unstructured, semi-structured, structured

Answer

Semi-structured, structured, unstructured

Answer

Structured, unstructured, semi-structured

Question 9

A company has a database that processes millions of small credit card transactions per second for payment authorization. They also need to run complex reports that aggregate transaction data over months to detect fraud patterns. Which type of workload describes the payment authorization process?

Accepted Answer

OLTP (Online Transaction Processing). The payment authorization process involves high-volume, low-latency transactions that read, insert, and update individual records in real time. This is the classic definition of OLTP (Online Transaction Processing), which is optimized for ACID-compliant, row-based operations on current data. The scenario explicitly states 'millions of small credit card transactions per second,' which aligns with OLTP workloads like order entry or banking.

Answer

OLAP (Online Analytical Processing)

Answer

HTAP (Hybrid Transactional/Analytical Processing)

Answer

ETL (Extract, Transform, Load)

Question 10

A banking application processes a funds transfer transaction consisting of two steps: debit $100 from Account A and credit $100 to Account B. If the system crashes after debiting Account A but before crediting Account B, the database automatically reverts the debit, restoring Account A to its original balance. Which ACID property guarantees this behavior?

Accepted Answer

Atomicity. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. In this scenario, the debit and credit are part of one transaction; if the system crashes after the debit but before the credit, the database management system (DBMS) automatically rolls back the entire transaction, undoing the debit to restore Account A's original balance. This all-or-nothing behavior is the defining characteristic of atomicity.

Answer

Consistency

Answer

Isolation

Answer

Durability

Question 11

A company collects customer feedback in three forms: a structured table with customer ID and rating (1-5), free-text comments, and audio recordings of phone calls. Which of the following correctly orders these data from least structured to most structured?

Accepted Answer

Audio recordings, free-text comments, structured table. Option A is correct because data structure ranges from unstructured (audio recordings with no schema), through semi-structured (free-text comments with no fixed format), to structured (a table with defined columns and data types). This ordering aligns with the DP-900 core data concept of data classification based on schema rigidity.

Answer

Structured table, free-text comments, audio recordings

Answer

Free-text comments, structured table, audio recordings

Answer

Audio recordings, structured table, free-text comments

Question 12

Which THREE of the following are valid considerations when choosing between Azure SQL Database and Azure SQL Managed Instance?

Accepted Answer

Azure SQL Managed Instance supports SQL Server Agent for job scheduling.. Options A, C, and E are correct. Azure SQL Database supports elastic pools, while Managed Instance supports instance-level features like SQL Agent and cross-database queries. Option B is incorrect because both support VNet integration, but Managed Instance is always placed in a VNet. Option D is incorrect because Managed Instance supports larger databases.

Answer

Azure SQL Database supports larger database sizes than Azure SQL Managed Instance.

Answer

Azure SQL Managed Instance does not support virtual network integration.

Question 13

A company collects data from multiple sources: IoT sensor streams, social media feeds, and CSV files from legacy systems. They want to store all this data in its original format without any transformation, so that data scientists can later apply machine learning models or run ad-hoc queries. Which data storage pattern best describes this approach?

Accepted Answer

Data lake. A data lake is designed to store vast amounts of raw data in its native format (structured, semi-structured, or unstructured) without requiring upfront schema or transformation. This aligns perfectly with the scenario of ingesting IoT streams, social media feeds, and CSV files as-is, enabling data scientists to later apply machine learning or run ad-hoc queries directly against the raw data.

Answer

Data warehouse

Answer

Relational database

Answer

Data mart

Question 14

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 15

A bank processes individual customer transactions in real-time to update account balances and also runs a nightly job that aggregates all daily transactions into summary reports for management. Which of the following best describes these two processing workloads?

Accepted Answer

OLTP for real-time transactions, OLAP for nightly reports. Option A is correct because real-time individual transaction processing is the hallmark of Online Transaction Processing (OLTP), which focuses on high-volume, low-latency inserts and updates to maintain current account balances. The nightly aggregation of daily transactions into summary reports is a classic Online Analytical Processing (OLAP) workload, which involves complex queries over large historical datasets for business intelligence. These two workloads have fundamentally different performance and design requirements, making OLTP and OLAP the appropriate classifications.

Answer

Batch processing for transactions, Stream processing for reports

Answer

OLAP for transactions, OLTP for reports

Answer

ETL for transactions, ELT for reports