Why transactional databases and data warehouses do different jobs

The same data can serve two very different kinds of work

I used to think of a database mainly as the place an application stores its data. This section made the split much clearer: the same company needs one system to respond to a customer right now and another system to answer questions about months or years of history.

A transaction is simply a logical unit of reads and writes. The term came from commercial actions such as sales and payments, but it also fits saving a profile, adding a game action, or posting a comment. Transaction processing here means low-latency interactive reads and writes. It does not automatically mean that every operation has full ACID guarantees.

TWO JOBS

Application request
-> find or change a few records quickly

Business question
-> scan history and calculate a result

OLTP is built around the user in front of the screen

Online Transaction Processing, or OLTP, handles the ordinary work of an application. A user looks up a small set of records, changes something, and expects a quick response. An indexed lookup by key is the common path.

An order page, an inventory update, a seat reservation, and a change to account settings all fit this shape. The data usually represents the latest state of the system, and writes arrive in small random pieces as people use the product.

OLTP

User input
-> index lookup
-> read or update a few records
-> low-latency response

OLAP is built around patterns in history

Online Analytical Processing, or OLAP, has almost the opposite read pattern. An analyst may scan a huge number of events, read only a few columns from each one, and calculate a count, sum, average, or grouped result. The goal is usually a report or an exploratory question, not a record to show directly to a customer.

Questions such as revenue by store, the effect of a promotion, or products often bought together require historical data and broad scans. Business intelligence tools often generate SQL for this kind of exploration, but the SQL interface hides a very different storage and query engine underneath.

OLTP                         OLAP
few records by key           aggregate many records
small user writes            bulk load or event stream
current state                event history
customer or application      internal analyst
GB to TB                     TB to PB

Production analytics can make production slower

A large company often has many operational databases: one for the customer-facing site, another for checkout, inventory, delivery routes, suppliers, employees, and more. Those systems need high availability and predictable low latency because the business depends on them.

An ad hoc analytical query can scan a large part of a dataset. If it runs against an operational database, it can consume the same resources that live transactions need. That is why database administrators are cautious about allowing broad analytical queries on production systems.

EXPENSIVE ANALYTIC QUERY

large scan
-> competes for CPU, memory, and I/O
-> slows concurrent customer transactions

A data warehouse gives analysis its own workspace

A data warehouse is a separate, read-oriented database that contains data copied from the company's operational systems. Analysts can query it heavily without putting checkout, inventory, or customer-facing requests at risk.

Data reaches the warehouse through ETL. The company extracts it from operational databases through periodic dumps or a stream of updates, transforms it into a consistent analysis-friendly shape, cleans it, and then loads it into the warehouse.

Small companies may not need this machinery yet. If there are only a few systems and limited data, a normal SQL database or spreadsheet may be enough. Warehousing becomes useful when data sources, history, and analytical demand grow.

OLTP SYSTEMS
website | store | inventory | suppliers
             |
          Extract
             |
    Transform and clean
             |
           Load
             v
      DATA WAREHOUSE
             |
   reports and exploration

Fact tables keep the events, dimensions explain them

Most warehouses use a relational model because SQL fits analytical questions well. A common layout is the star schema. The center is a fact table, where each row describes an event. In a grocery business, one row may represent one product sale. For a website, it may represent a page view or click.

Fact rows contain measurable values such as quantity, sale price, or supplier cost. They also contain foreign keys to dimension tables. Dimensions describe the event: who bought it, what product was involved, where it happened, when it happened, and other context needed for analysis.

Dates are often dimensions too. A date table can include month, weekday, or public-holiday information, which makes comparisons such as holiday sales versus ordinary sales much easier.

                     dim_product
                          |
dim_customer --- fact_sales --- dim_store
                          |
                       dim_date

fact_sales = one event
dimensions  = context for that event

Star schemas favor clarity, snowflakes favor normalization

A warehouse usually stores facts as individual events because this leaves more options open for later analysis. The cost is size. Fact tables can grow to tens of petabytes in a large company and often have more than a hundred columns, even though a query might read only four or five.

In a star schema, dimensions sit directly around the fact table and are straightforward for analysts to query. A snowflake schema normalizes dimensions further. For example, a product may reference a separate brand and category table. That reduces duplication but adds joins, so star schemas are often easier to work with.

STAR
fact_sales -> dim_product

SNOWFLAKE
fact_sales -> dim_product -> dim_brand
                         -> dim_category

Fewer copied values can mean more joins.

What I will carry forward

OLTP and OLAP are not competing labels for the same database. They describe different access patterns. An operational system optimizes for a small, fast interaction. A warehouse optimizes for questions that need to inspect a great deal of history.

The next design choice follows from that split. Analytical queries usually touch only a few columns from very wide fact tables, so storing rows in the usual way wastes work. That is why the next section moves to column-oriented storage.

USER ACTION
-> OLTP
-> fast point reads and small writes

HISTORICAL QUESTION
-> OLAP
-> broad scan, selected columns, aggregates

These are my personal learning notes from Designing Data-Intensive Applications by Martin Kleppmann.