Vol #8 | What is OLTP & OLAP

and how these are implemented in the modern data ecosystems.

It’s weekend and I’m back with another topic today. This is one of the very first concepts that I learned some 17-18 years ago when I started my journey in the data world.

Recently, in one of my lectures, one curious data engineer asked me - Can we use Snowflake to implement OLTP workloads? My view at that time was it might not make much sense to do that as a simple RDBMS should do the job.

However, with the recent launch of Unistore from Snowflake, I might need to change my views.

Today’s topic is related to this - implementing OLTPs and OLAPs in today’s times.

Let’s get started!

What are OLTP systems?

OLTP stands for Online Transaction Processing.

OLTP systems are implemented using Relational databases for building application backends. Eg - The application database of a library management system or a railway ticket booking system.

These are implemented using RDBMS systems that can support granular transactions, quick updates and searches. These mainly cater to the processing of single records corresponding to transactions happening in the real world.

What are OLAP systems?

OLAP stands for Online Analytical Processing.

OLAP systems are implemented using Data-Warehouse technologies like Teradata, Exadata and Snowflake/BigQuery in the modern world. These systems mainly support analytical workloads like summarising or aggregating data based on certain conditions.

Some of the examples include workloads like Management Information System (MIS) reports for banks, customer churn analysis for Telecom companies etc.

OLAP-based systems mainly cater to the analysis of the group of records and not a single record. These systems perform better for such analytical workloads and are not suited for transactional workloads

How are these used in the Modern Data Ecosystems?

Like the traditional systems, Relational Databases are still widely used for implementing OLTP systems.

However, some of the modern, architectures are now being built using NoSQL databases like MongoDB which can perform better and can also support unstructured data.

OLAP systems, in the modern world, have seen dominance from Cloud warehouses and SaaS-based systems. The new age OLAP systems are based on software that supports columnar storage, scalable compute decoupled with storage and less or zero manual maintenance.

Also, with lakehouse architectures gaining a lot of attention, the analytical workloads have started shifting to data lakes along with open table formats like Apache Iceberg, Apache Hudi and Delta lake can provide warehouse-like capabilities.

So, what’s next?

Like any other tools and platforms in the modern data world, these tools related to OLTP and OLAP are also going through a lot of changes and paradigm shifts to deliver a modern approach for building data ecosystems

Snowflake recently announced a new feature called Unistore which provides a single platform for both OLTP and OLAP workloads.

I was really excited about Unistore and so immediately tweeted about it and that’s when I got a response from Singlestore’s marketing team. They also offer similar capabilities to bring OLTP and OLAP workloads into one single platform and have been supporting this feature for the last 3 years!

Seems like “unified platform” is the way forward for all data use cases - be it transactional, analytical, streaming or machine learning.

We will have to wait and see how things shape up with these single-platform products. This might be the future of modern data ecosystems where the transactional applications, analytical workloads, streaming use cases or ML models - all would reside together to provide faster, quicker, real-time analytics, insights and predictions.

That’s it for today, hope you have enjoyed reading this. Please do comment with your views and thoughts.

Happy Weekend!