OLTP vs. OLAP Databases: The Challenges of ETL

ETL (extract, transform, load) is the backbone of data processing for modern enterprises, helping you get the valuable insights you rely on day in and day out.

 
Yet despite ETL being a critical business function, too many users aren’t familiar with what’s causing the issues when they encounter ETL challenges. Below, we’ll discuss what you need to know about one of the most important distinctions in ETL: OLTP and OLAP databases.

OLTP vs. OLAP: What’s the Difference?

There are two main types of relational databases: OLTP and OLAP. While both are heavily used for a wide variety of business applications, they’re quite different in terms of their primary use case.

OLTP (online transactional processing) databases are primarily designed to handle large quantities of transactions. As such, OLTP databases need to comply with the ACID (atomic, consistent, isolated, and durable) properties. This ensures that each transaction will still be valid in the case of unexpected failures or errors. OLTP databases emphasize accuracy and integrity, which means that redundant and duplicate data should be kept to a minimum.

The classic example of an OLTP database is a financial system that needs to process user transactions on a day-in, day-out basis (e.g. bank account deposits, withdrawals, and transfers). Under the hood, these transactions are represented as INSERT, UPDATE, and DELETE statements in an OLTP database.

OLAP (online analytical processing) databases, on the other hand, prioritize quick, efficient reporting and analysis of large quantities of data. These databases focus on joining and aggregating data and crunching the numbers. As such, they’re generally used for BI and analytics workloads, and are not exposed to the end user. Data warehouses and data marts (specialized data warehouses for the use of a single team or department) are the most common types of OLAP databases.

This different use case means that in practice, OLAP databases look very different from OLTP databases. OLAP databases have more redundant information, and the data is more likely to be denormalized. They also typically have fewer (but larger) tables than OLTP databases.

OLTP and OLAP Databases in ETL

ETL commonly features both OLTP and OLAP databases. Data is extracted from one or more OLTP sources, then transformed and loaded into an OLAP system. Effective ETL workflows depend on this OLTP-OLAP symbiosis.

The most common type of OLAP system in ETL is the data warehouse: a database for storing large quantities of data that has been optimized for business intelligence and analytics. Data marts are smaller data warehouses that are intended for the exclusive use of one team or department (e.g. sales or finance).

Once it’s inside your OLAP system, data needs to be organized efficiently to promote ease and speed of analysis. Data inside an OLAP database is often collected in multidimensional arrays of data known as data cubes, also known as OLAP cubes or hypercubes. Each “dimension” of the cube corresponds to a different attribute of the data: for example, product, sales, or location.

Note, however, that OLTP and OLAP databases aren’t necessarily the only types of data repositories involved in the ETL process. Data lakes are unstructured repositories (not databases) that store raw, unprocessed data. Much of the contents of a data lake is stored “just in case,” and may or may not eventually be used for BI and analytics. If information in a data lake needs to flow into your data warehouse, you’ll need to account for this connection when building your ETL architecture.

OLTP and OLAP databases are just scratching the surface of ETL. Want to know how to solve some of the most pressing and common issues with your ETL workflow? I’ve written just the guide for you. Check out my white paper “The Top 5 Challenges of ETL (And How to Solve Them).”