We live in a data-driven world. Some say data is the new oil, while others say it is the new soil. It is up to businesses to make quicker and smarter decisions to beat their competitors using the data they have.

There are typically two types of data processing engines: Online Transactional Processing (OLTP) and Online Analytical Processing (OLAP).

OLTP vs. OLAP

We often use OLTP for real-time processing of many small database transactions of events and people via client applications.

Some examples include the ecommerce, internet banking, ATM transactions, hotel bookings, and so on.

OLAP is often used for high speed multi-dimensional analysis on large volumes of historical data from a centralised data store such as data warehouse, data mart, or any centralised database management system. New OLAP engines like ByteHouse, ClickHouse*, Google Big Query, Azure Analytics Services, and so on, allow for real-time data analysis of streaming sources.

OLAP engines usually focus on use cases such as business intelligence reporting, trend analysis, and advanced data mining, while OLTP engines are good at ecommerce payments, banking transactions, hotel bookings, etc.

There are several distinguishing characteristics of OLAP engines. They tend to have complicated queries involving aggregation over multiple dimensions. And, typical OLAP engines prefer read (select) queries than write (insert) operations.

OLTP for reporting?

Yes, you read it correctly. In the past, reports used to be generated from OLTP systems, even before the evolution of the OLAP and Data Warehouse systems.

These reports used to be more static in nature. The evolution of OLAP systems gave more flexibility to build multidimensional models and to perform more complex data analytics. This comprises, but is not limited to, roll up, drilldown, slice, and dice of data, exploratory, explanatory, and descriptive analysis using various analytical tools.

OLTP and OLAP Systems in a typical Data Architecture Design

Key differences between OLTP and OLAP

The following table illustrates key feature comparisons between OLTP and OLAP systems.

OLTP vs OLAP

ClickHouse is an open source column-oriented database management system designed primarily for the use of OLAP. Read more on "What is ClickHouse" or download our eBook Reimagine ClickHouse.

Start your free trial of ByteHouse cloud data warehouse and get US$400 credit for free.

About the author: Sudarsan is a customer solution architect at ByteDance; and, he is a CDMP (DAMA) certified data architect with prior experiences with National University of Singapore, Development Bank of Singapore, Standard Chartered Bank and BNP Paribas.

*ClickHouse is a trademark of ClickHouse, Inc.