DP-900 Describe core data concepts Practice Test 8 — 15 Questions

Question 1

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 2

A data file contains records for customer orders. Each record has fields for OrderID, CustomerID, and OrderDate that are present in every record. However, some records include an optional 'DiscountCode' field, and others include an optional 'GiftMessage' field. The file is stored in JSON format. Which type of data does this file represent?

Accepted Answer

Semi-structured data. The JSON file contains records with a fixed set of fields (OrderID, CustomerID, OrderDate) that are always present, but also includes optional fields (DiscountCode, GiftMessage) that may appear in some records but not others. This mix of a consistent schema with flexible, self-describing fields is the hallmark of semi-structured data. JSON itself is a semi-structured format because it uses key-value pairs and allows nested or optional attributes without requiring a rigid schema.

Answer

Structured data

Answer

Unstructured data

Answer

Transactional data

Question 3

A company maintains a database of customer orders that are updated frequently. They also store aggregated monthly sales reports that are generated once and then only read. Which statement correctly distinguishes these two types of data workloads?

Accepted Answer

Transactional data is optimized for write operations, and analytical data is optimized for read operations.. Option A is correct because transactional workloads (like the frequently updated customer orders) are optimized for write-heavy operations, ensuring ACID compliance and data integrity, while analytical workloads (like the read-only monthly sales reports) are optimized for read-heavy operations, often using columnar storage or pre-aggregated data to speed up queries. This distinction aligns with the core difference between OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) systems in Azure, such as Azure SQL Database for transactional data and Azure Synapse Analytics for analytical data.

Answer

Transactional data must always be stored in non-relational databases, and analytical data in relational databases.

Answer

Analytical data always requires real-time processing, whereas transactional data is batch-processed.

Answer

Transactional data is read-only and analytical data is frequently updated.

Question 4

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 5

A manufacturing company stores two types of data: (1) real-time sensor readings from production machines used to monitor current machine status, and (2) historical daily production summaries used by managers to identify trends over months. Which statement accurately describes these workloads?

Accepted Answer

Sensor readings are an OLTP workload; daily summaries are an OLAP workload.. Option B is correct because real-time sensor readings involve frequent, small inserts and point lookups (typical of an OLTP workload), while historical daily summaries are aggregated data used for trend analysis over months (typical of an OLAP workload). OLTP systems handle high-volume transactional operations, whereas OLAP systems support complex queries and aggregations on large historical datasets.

Answer

Sensor readings are an OLAP workload; daily summaries are an OLTP workload.

Answer

Sensor readings are a NoSQL workload; daily summaries are a relational workload.

Answer

Sensor readings are a batch workload; daily summaries are a real-time workload.

Question 6

A company stores customer orders in a database. Each order has an OrderID (integer), CustomerName (text), OrderDate (date), and a JSON column for order details that contains varying fields such as discount codes or gift messages. Which statement best describes the data types in this table?

Accepted Answer

The table stores both structured and semi-structured data.. The table includes structured columns (OrderID integer, CustomerName text, OrderDate date) and a JSON column for order details, which stores semi-structured data because JSON allows flexible schemas with varying fields like discount codes or gift messages. This combination of fixed-schema columns and a schema-less JSON column means the table holds both structured and semi-structured data, making option B correct.

Answer

The table stores only structured data.

Answer

The table stores only unstructured data.

Answer

The table stores only semi-structured data.

Question 7

A bank's online transaction processing system records every withdrawal and deposit in a database. The bank also runs a monthly report that summarizes total transactions per customer. Which statement correctly identifies these two workloads?

Accepted Answer

The transaction recording is OLTP, and the monthly report is OLAP.. The transaction recording system is an OLTP (Online Transaction Processing) workload because it handles individual, real-time transactions (withdrawals and deposits) with high concurrency and low latency. The monthly report summarizing total transactions per customer is an OLAP (Online Analytical Processing) workload because it aggregates historical data for reporting and analysis, typically using batch processing or columnar storage. Option B correctly pairs each workload with its appropriate processing type.

Answer

Both workloads are OLTP.

Answer

The transaction recording is OLAP, and the monthly report is OLTP.

Answer

Both workloads are OLAP.

Question 8

A company stores customer orders in a relational database that handles many small transactions (inserts, updates, deletes) throughout the day. Separately, they maintain a data warehouse that is used for complex aggregations and historical trend analysis. Which statement correctly describes these two workloads?

Accepted Answer

The first system is an OLTP workload; the second is an OLAP workload.. The first system handles many small, concurrent transactions (inserts, updates, deletes) typical of an Online Transaction Processing (OLTP) workload, optimized for ACID compliance and fast query response. The second system is an Online Analytical Processing (OLAP) workload, designed for complex aggregations and historical trend analysis using columnar storage and star schemas. This distinction is fundamental in data architecture, where OLTP systems prioritize write performance and OLAP systems prioritize read performance for large-scale analytics.

Answer

Both systems are OLTP workloads because they store customer orders.

Answer

The first system is an OLAP workload; the second is an OLTP workload.

Answer

Both systems are OLAP workloads because they both involve data storage.

Question 9

A manufacturing company uses IoT sensors to monitor machine temperature. The data is analyzed immediately to trigger alerts if temperature exceeds a threshold. The same data is also stored and later analyzed to identify long-term trends. Which two terms best describe these data processing approaches?

Accepted Answer

Real-time processing for alerts, batch processing for trend analysis. Option A is correct because the scenario describes two distinct processing requirements: immediate alerting on temperature thresholds requires real-time (or stream) processing to minimize latency, while long-term trend analysis can be performed on stored data using batch processing, which is efficient for large historical datasets. Real-time processing handles data as it arrives with low latency, and batch processes data in bulk at scheduled intervals.

Answer

Batch processing for alerts, real-time processing for trend analysis

Answer

OLAP for alerts, OLTP for trend analysis

Question 10

A data scientist needs to analyze historical sales data to identify yearly trends. They run SQL queries that aggregate millions of rows. No new data is being added during analysis. Which type of data processing workload does this represent?

Accepted Answer

Online Analytical Processing (OLAP). This workload is Online Analytical Processing (OLAP) because the data scientist is running complex SQL queries that aggregate millions of rows of historical sales data to identify yearly trends. OLAP is designed for read-intensive, analytical queries that summarize large volumes of static data, which matches the scenario where no new data is being added during analysis.

Answer

Online Transaction Processing (OLTP)

Answer

Batch processing

Answer

Stream processing

Question 11

A car manufacturing company has two data processing systems: one system processes real-time sensor data from assembly lines to immediately detect equipment failures, and another system processes historical production records to generate monthly efficiency reports. Which two types of data processing workloads best describe these systems?

Accepted Answer

Stream processing and batch processing. Stream processing handles real-time sensor data to detect equipment failures immediately, as it processes data continuously with low latency. Batch processing is ideal for historical production records to generate monthly efficiency reports, as it processes large volumes of data at scheduled intervals. These two workloads directly match the definitions of stream and batch processing in Azure data services like Azure Stream Analytics and Azure Synapse Analytics.

Answer

OLTP and OLAP

Answer

Online processing and offline processing

Question 12

A company stores customer information in a table with columns CustomerID, Name, Address, and PhoneNumber. Every row has values for all these columns, and the data follows a fixed schema. Which type of data does this represent?

Accepted Answer

Structured data. Structured data conforms to a fixed schema where each row has the same columns and data types. The table with CustomerID, Name, Address, and PhoneNumber, where every row contains values for all columns, perfectly fits this definition. This is typical of relational database tables (e.g., in Azure SQL Database) where the schema is enforced at the table level.

Answer

Unstructured data

Answer

Semi-structured data

Answer

Streaming data

Question 13

A company is building a data lake and collects data from three sources: (1) a relational database exporting CSV files with fixed columns for customer records, (2) API responses stored as JSON files with varying fields for product reviews, and (3) scanned handwritten notes stored as TIFF images. Which statement correctly categorizes these data by structure type?

Accepted Answer

1: structured, 2: semi-structured, 3: unstructured. Option A is correct because CSV files from a relational database have a fixed schema (rows and columns), making them structured data. JSON files from API responses with varying fields are semi-structured, as they use tags/keys to organize data without a rigid schema. TIFF images of handwritten notes are unstructured, lacking a predefined data model or organization.

Answer

1: semi-structured, 2: structured, 3: unstructured

Answer

1: structured, 2: unstructured, 3: semi-structured

Answer

1: unstructured, 2: semi-structured, 3: structured

Question 14

A data engineering team at a logistics company handles two distinct data processing workloads. The first workload ingests GPS data from delivery trucks every 10 seconds and updates a dashboard showing real-time vehicle locations. The second workload processes monthly CSV files of completed deliveries to generate reports on delivery times and route efficiency. Which statement correctly identifies these workloads?

Accepted Answer

GPS data processing is a streaming workload; monthly report processing is a batch workload. C is correct because GPS data ingested every 10 seconds is a continuous, near-real-time stream, making it a streaming workload. Monthly CSV file processing is a classic batch workload, as data is collected over a period and processed in a single, scheduled job. This distinction is fundamental in Azure data services: streaming workloads use services like Azure Stream Analytics or Event Hubs, while batch workloads use Azure Synapse Pipelines or Azure Data Factory.

Answer

Both workloads are streaming workloads

Answer

GPS data processing is a batch workload; monthly report processing is a streaming workload

Answer

Both workloads are batch workloads

Question 15

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