some of the key differences between a data warehouse's model and an OLTP system's.
One major difference between the types of system is that data warehouses are not usually in third-normal form.
Data warehouses and OLTP systems have vastly different requirements. Here are some examples of the notable differences between typical data warehouses and OLTP systems:
Data warehouses are designed to accommodate ad hoc queries. The workload of a data warehouse may not be completely understood in advance, and the data warehouse is optimized to perform well for a wide variety of possible query operations.
OLTP systems support only predefined operations. The application may be specifically tuned or designed to support only these operations.
The data in a data warehouse is updated on a regular basis by the ETT process (often, every night or every week) using bulk data-modification techniques. The end users of a data warehouse do not directly update the data warehouse.
In an OLTP system, end users routinely issue individual data-modification statements in the database. The OLTP database is always up-to-date, and reflects the current state of each business transaction.
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 guarantee data consistency.
A typical data warehouse query may scan thousands or millions of rows. For example, "Find the total sales for all customers last month."
A typical OLTP operation may access only a handful of records. For example, "Retrieve the current order for a given customer."
Data warehouses usually store many months or years of historical data. This is to support historical analysis of business data.
OLTP systems usually store only a few weeks' or months' worth of data. The OLTP system only stores as much historical data as is necessary to successfully meet the current transactional requirements.