Differences between typical data warehouses and OLTP systems

Workload

Data warehouses are designed to accommodate ad
hoc queries. You might not know the workload of
your data warehouse in advance, so a data
warehouse should be optimized to perform well for
a wide variety of possible query operations.

OLTP systems support only predefined operations.
Your applications might be specifically tuned or
designed to support only these operations.

#

Data modifications

A data warehouse is updated on a regular basis by
the ETL process (run nightly or weekly) using bulk
data modification techniques. The end users of a
data warehouse do not directly update the data
warehouse.

In OLTP systems, end users routinely issue individual
data modification statements to the database. The
OLTP database is always up to date, and reflects
the current state of each business transaction.

#

Schema design

Data warehouses often use denormalized or partially
denormalized schemas (such as a star schema) to
optimize query performance.

OLTP systems often use fully normalized schemas to
optimize update/insert/delete performance, and to
guarantee data consistency.

#

Typical operations

A typical data warehouse query scans thousands or
millions of rows. For example, “Find the total sales
for all customers last month.”

A typical OLTP operation accesses only a handful of
records. For example, “Retrieve the current order
for this customer.”

#

Historical data

Data warehouses usually store many months or years
of data. This is to support historical analysis.

OLTP systems usually store data from only a few
weeks or months. The OLTP system stores only
historical data as needed to successfully meet the
requirements of the current transaction.

#

Time-Variant

Data is stored as a series of snapshots, each

representing a period of time

Non-Volatile

Nonvolatile means that, once
entered into the warehouse,
data should not change. This
is logical because the purpose
of a warehouse is to enable
you to analyze what has
occurred.