When working with databases, there are usually scenarios where a single database alone can't meet all our needs, or where using the database directly creates problems. Here are a few examples to better understand the potential issues:

Load Separation

When the primary database is used for application transactions, running heavy reporting or analytics on it at the same time causes slowdowns and heavy resource consumption. In this case, the data is moved to a different database, such as ClickHouse or Snowflake, that's suited to running data analysis queries.

Collecting and Consolidating Data From Different Databases (Data Lake or Data Warehouse)

Depending on each country's regulations, companies usually keep data for several months. For example, if your service is a messaging application and a user deletes a message, that message is kept in another database for a period of time, in accordance with regulations. 

Even changes to data are collected, aggregated, and kept for a while. This data can be used to analyze user behavior and forecast the business's future.

Updating Downstream Systems

In some architectures, such as microservices, our application is split into several smaller services, each with its own database, operating independently. In such cases, one of our services might need data that's stored in another service's database! One solution is to store and synchronize that data in both services — meaning ownership of the data (the right to add new data and edit it) belongs to one service, and the other service keeps a copy of that data, so that the load on the database of the service that owns the data doesn't increase.

Monitoring and Auditing Changes

Almost every large company monitors data changes in near real time. For example, by tracking the number of inserts/updates/deletes at any given moment, you can check how much load is on the database and react to changes in it. For instance, if the rate of deletes in a given time window doubles compared to that same window previously, it could indicate a security problem! 

Or if the number of inserts (data being added to the database) changes drastically (for example, doubling), it could indicate a type of DDoS attack.

What Is CDC?

The primary database is usually designed for the application's day-to-day work (placing orders, user management, financial transactions, and so on), and when that same database is also used for other needs like reporting, analytics, or data transfer, we run into several serious problems.