2. Problem Domain

Modern software systems continuously capture a large amount of valuable data, ranging from core business records (e.g., user accounts) to high-volume transactional streams (e.g., purchases and inventory changes). Data of this nature is necessary for operations core to the application. In addition, the data can be used to provide broad historical analysis of user behavior and a real-time analysis of user events

The problem is, operational databases that store this data are optimized for speed, concurrency, and safe writes. They are not designed for more complex analytical queries like trend forecasting or real-time aggregations. Attempting to run these computationally-heavy queries in a production environment risks degrading application performance.

To alleviate this, it is necessary to extract the relevant data from the operational database and transport it into a specialized data store designed for this purpose.

Operational vs. Analytical Databases

Firstly, we should have a clear understanding of the difference between an ‘operational database’ and an ‘analytical database’.

An operational database is a production database that handles the day-to-day operations of an application or system. It is specifically designed for Online Transaction Processing (OLTP), meaning it prioritizes fast, frequent, and highly efficient data updates.

An analytical database on the other hand, is intended for high-volume historical data. These databases are built for what is called Online Analytical Processing (OLAP). OLAP databases are designed to handle denormalized data, traditionally column-oriented, and generally do not prioritize response time. They are built to process queries involving millions, billions, or even trillions of rows. Some examples of OLAP systems are Snowflake, Redshift, and ClickHouse.

These analytical databases need to be kept up to date with accurate data at all times for modern use cases. This ranges from real-time user activity feeds and live dashboards to predictive AI pipelines, historical business analysis, and more.

Keeping up with the demands for consistent data pipelines in real time is a persistent engineering challenge. Historically, workflows moved data in scheduled blocks periodically. As the need for up-to-date data effectively forced these processing windows to shrink, the focus shifted from moving data in bulk to capturing changes in data as they happen.

Change Data Capture

Change data capture (CDC) is a design pattern that allows the tracking of modifications to a dataset through the use of ‘deltas’. A ‘delta’ is a description of a change. This means the actual message that is passed does not necessarily include the actual state of t/he data, but only how it has been changed. This allows multiple parts of a system to stay in sync with one another without having to replicate the entire dataset every time anything changes.

Polling-based CDC

The most straightforward approach is to continuously poll the production database for any changes. Changes are identified by a timestamp applied to a row with the time it is modified. There are a number of problems with this approach:

Undetectable Actions

This method struggles to capture certain actions, such as row deletions. Any actions that would remove the timestamps can cause the changes to go undetected.

Polling Overhead

Keeping the data up-to-date requires frequent polling, which adds additional strain on the operational database.

This forces you to choose between overloading production databases to get fresh data or reducing the load and settling for stale insights that break real-time use cases.

Race Conditions

If a change occurs immediately after a batch has been processed, it will not be captured until the next time the database is polled for changes. If the same row is modified again before this happens, then the first change may fail to be captured.

Application Dual Writes

Instead of waiting for a request for the changes, an alternative is to have the application write changes to both a database and utilize a message queue to send all events to an analytical database.

However, if either of these operations fails, this can result in an inconsistency between your production and your analytical databases: this is often referred to as the dual-write problem, and can cause downstream services to become out of sync.

The problem stems from the lack of a single, atomic transaction spanning both systems that ensures that both writes either succeed or fail together.

Log-Based Streaming

Log-based change data capture avoids the inherent problems with Poll based change data capture and the use of dual writes as it reads change events directly from the database’s internal transaction log. Because these internal logs are an append only structure separate from the main database files, they can be read at high speed without affecting the database tables that are being captured. In addition, because these events come directly from the internal transaction log, the application is never asked to coordinate the writes.

Transport

Capturing changes from a CDC system solves the first half of the problem. We now have a stream of change events. The second half is moving this captured data from an operational data source to an analytical one. We still need to deliver what was captured, securely, and ensure that every consumer that needs these captured changes receives them. Message Queues and Event Streams are two of the popular methods used for transport.

Message Queues

Message queues are a simple approach to solve this problem; they act as an asynchronous buffer to transmit data from one service to another (e.g., move data from an operational database to an analytical database).

They come with their own challenges, though. For example, when a consumer reads a message, the message queue will then delete that message. This means any other consumers will miss that piece of data. Message queues are transient by nature. They are not designed to do anything but decouple your producers from consumers. There are times when this simplicity is not desirable. For example, if you want to be able to ‘roll back’ the history of messages to a specific point in time, or replay a sequence of messages either for debugging or for graceful error handling, then you will need something more complex than a message queue.

Event Streaming

To enable both a replay of events for broad analysis and for wider flexibility in consumers, an event streaming platform is required. An event streaming platform acts as a distributed, append-only log of changes in data that persists for a configurable amount of time. Unlike message queues, these logs are append-only; a consumer that reads from a message will not be able to delete this information, allowing for an event to have multiple downstream consumers.

Managing and configuring event streaming platforms is an engineering challenge that necessitates a careful comparison of the pros and cons of different solutions when it comes time to build a CDC pipeline.