Data Warehouse vs. Traditional



Operational Data Stores (OLTP)
The data warehouse differs from operational database systems in many ways. One of the main differences between these two types of systems is the data collected in each of them. In operational systems (OLTP - On-Line Transactional Processing systems), the data is called operational data and is constantly in a state of flux, while in the data warehouse, the data is usually referred to as decision support data and remains relatively static.
The data warehouse is subject-oriented, which means it is designed around the major subjects of the enterprise business. Traditional operational databases, on the other hand are usually process-oriented based on the functionality provided by the applications accessing them.
Operational databases also differ from the data warehouse in their structure. Operational systems are usually designed to allow a large number of small transactions (OLTP systems). This requirement makes it necessary to optimize the database for data inserts, updates, and deletions. The data warehouse, on the other hand, is not expected to be subject to updates and deletions; however, inserts may happen periodically when data is added to it. The data warehouse stores the collective transactions over time after selecting the important pieces of data to store, and performing some transformation on it.



OLTP systems are designed to be small in size to allow for efficient updates, inserts, and deletions. The data warehouse is designed to hold large amounts of data. The data recognized to have become static and not likely to change may be archived. While an OLTP system can rarely exceed the 100 GB limit, a data warehouse is likely to be measured in the TB ranges.
Data storage volume is not the only difference between operational OLTP systems and data warehouses. The volume of data processed when retrieval occurs is extremely different. With OLTP systems, small amounts of data are retrieved for every transaction. In data warehouses, a large set of data may have to be processed when queries are issued against the data store.
OLTP systems depict the current state of the business. For example, a banking OLTP system will hold records that show the current customer accounts, and the daily transactions that take place around them. The data warehouse for the same bank, on the other hand, will store historical information about bank customers, accounts, and activities over a long period of time. Data is retrieved from the data warehouse as snapshots in time.