Transactional & Analytical Workloads

A system that records many small, discrete units of work, such as debiting one bank account and crediting another, and that must handle very high volumes of read and write operations quickly, is described as which type of workload?

• AOnline Transactional Processing (OLTP)✓
• BBatch processing
• CExtract, transform, and load (ETL) pipelines
• DOnline Analytical Processing (OLAP) systems

Answer: Online Transactional Processing (OLTP) records transactions as small, discrete units of work and relies on a database optimized for both read and write operations, often handling millions of transactions a day. Online Analytical Processing (OLAP) instead works with read-only or read-mostly historical data for reporting, not high-volume live writes. Extract, transform, and load (ETL) describes how data moves into an analytical store, not a transactional workload itself. Batch processing runs work in scheduled groups rather than handling individual transactions immediately.

In ACID transaction semantics, the property that ensures a transaction is treated as a single unit which either succeeds completely or fails completely is called what?

• AAtomicity✓
• BConsistency
• CDurability
• DIsolation

Answer: Atomicity means each transaction is treated as a single unit, which succeeds completely or fails completely, so a fund transfer's debit and credit either both happen or neither does. Consistency instead ensures a transaction only moves the database from one valid state to another. Isolation ensures concurrent transactions cannot interfere with one another. Durability ensures a committed transaction remains committed even after a system restart.

Which ACID property ensures that a transaction can only move the data in a database from one valid state to another, so a completed fund transfer correctly reflects the movement of money between the two accounts?

• ADurability
• BAtomicity
• CConsistency✓
• DIsolation

Answer: Consistency ensures a transaction can only take the database from one valid state to another, so a completed transfer accurately reflects funds moving from one account to the other. Atomicity instead ensures the transaction succeeds or fails as a whole. Isolation ensures concurrent transactions don't interfere with each other's results. Durability ensures a committed transaction stays committed even if the system is switched off.

Which ACID property guarantees that while a fund transfer transaction is in progress, a separate transaction checking both account balances cannot see a value for one account from before the transfer and a value for the other account from after it?

• AIsolation✓
• BConsistency
• CDurability
• DAtomicity

Answer: Isolation ensures concurrent transactions can't interfere with one another and must result in a consistent database state, so a balance check during a transfer cannot see a mixed before-and-after result. Atomicity instead ensures the transfer's debit and credit both complete or both fail. Consistency ensures the database moves only between valid states. Durability ensures the transfer stays committed once it has completed.

Which ACID property ensures that once a fund transfer transaction has been committed, the revised account balances remain in place even if the database system is switched off and back on again?

• AAtomicity
• BIsolation
• CConsistency
• DDurability✓

Answer: Durability ensures that once a transaction has been committed, it remains committed, so the revised balances persist even through a system restart. Atomicity instead governs whether the debit and credit both succeed or both fail as one unit. Consistency ensures the transaction only moves the database between valid states. Isolation ensures concurrent transactions don't interfere with one another's results.

Systems that support live, business-critical applications processing day-to-day transactional data are often referred to using which term?

• ALine of business (LOB) applications✓
• BSemantic model applications
• CBusiness intelligence dashboard applications
• DData warehouse reporting applications

Answer: Line of business (LOB) applications is the term used for the live applications that OLTP systems support to process an organization's day-to-day transactional data. Data warehouse applications instead query a relational schema optimized for read-mostly reporting, not live transaction processing. Business intelligence dashboards present aggregated insights rather than handle discrete transactions. Semantic model applications query preaggregated OLAP models for analysis, not live business transactions.

A system that stores vast volumes of historical data or business metrics, is read-only or read-mostly, and can be based on a snapshot of the data at a given point in time, describes which kind of processing?

• AReal-time stream processing
• BTransactional data processing
• CConcurrent data processing
• DAnalytical data processing✓

Answer: Analytical data processing typically uses read-only or read-mostly systems that store vast volumes of historical data or business metrics, sometimes based on snapshots taken at a point in time. Transactional data processing instead optimizes a database for both read and write operations to support live business transactions. Real-time stream processing is not the term used in this description of historical, snapshot-based analysis. Concurrent data processing isn't the term used either; analytical systems are defined by being read-mostly and historical, not by concurrency.

An established way to store data in a relational schema that is optimized for read operations, primarily to support reporting and data visualization queries, is known as what?

• AA data lakehouse
• BAn OLTP database
• CA data lake
• DA data warehouse✓

Answer: A data warehouse is an established way to store data in a relational schema optimized for read operations, primarily queries that support reporting and data visualization. A data lake instead collects large volumes of file-based data without necessarily imposing a relational schema. A data lakehouse combines a data lake's flexible storage with a warehouse's relational querying, a more recent innovation than the warehouse alone. An OLTP database is optimized for both read and write transactional operations, not read-mostly reporting.

Which analytics architecture combines the flexible, scalable storage of a data lake with the relational querying semantics of a data warehouse, and may require some denormalization of OLTP source data to make queries perform faster?

• AA data lakehouse✓
• BAn OLAP model
• CA data warehouse
• DAn OLTP database

Answer: A data lakehouse combines the flexible and scalable storage of a data lake with the relational querying semantics of a data warehouse, and its table schema may introduce some denormalization of OLTP data to speed up queries. A data warehouse alone provides relational querying but not a data lake's flexible file-based storage. An OLTP database is optimized for transactional read and write operations, not analytical querying. An OLAP model is a preaggregated form of analytical storage, not a combined lake-and-warehouse architecture.

Compared with an OLTP system, which is optimized for fast reads and writes of current transactional records, an OLAP model is instead optimized for what?

• ARunning fast queries over preaggregated, historical data✓
• BHandling millions of individual CRUD operations per day
• CEnforcing ACID transactions for live, time-critical transfers
• DStoring customer records that are updated in real time

Answer: An OLAP model is a preaggregated type of data storage optimized for analytical workloads, so queries against the summaries it contains, drawn from historical data, can run quickly. Handling millions of individual CRUD operations per day describes an OLTP system's high-volume live workload, not OLAP. Enforcing ACID transactions for live, time-critical transfers is also a defining OLTP behaviour. Storing customer records that are updated in real time describes ongoing transactional writes, which OLAP's read-mostly historical model doesn't perform.