From what volumes of data do data ingestion tools like apache nifi, flume, storm or tools like logstash become relevant?

Question

I'm working in a company that has two legacy data warehouses, which have evolved to unmaintainable monoliths throughout the time. Therefore, they are in dire need of re-form.

I'm investigating a reform of the current data architecture into an architecture that is more in line with the principles of a data mesh, like advocated in this influential article by Zhamak Dehghani (Probably well-known material to data professionals here).

The first Data warehouse, say DWH-A, mainly consists of data coming directly from the operational databases of the core company application. It it updated weekly through a FTP-dump from operational databases, and every update contains roughly 2GB of data. The DWH has grown to a respectable size of +-300GB over the course of 5 years.

The second Data warehouse, say DWH-B, consists of a wide variety of data coming from all kinds of API's and other data sources. It is updated continuously through API-calls, and has a size of +- 100GB.

Both data warehouses are built mainly with T-SQL and hosted on MS SQL Server. Currently, all data is either inserted from operational databases (through SSIS) or from API's (through SSIS i.c.w. ZappySys).

As I'm given the task to upgrade the current way of doing things, and since I believe that SSIS is a rather superfluous and cumbersome way of inserting data, I'm looking for other ways of ingesting data into some data storage that is more in line with the principles of a data mesh (so no monolith data warehouse).

To this end, I came across tools like Apache nifi, Flume, Storm, Kafka and Logstash. All these tools seem really powerful in their own regard, and suited to handle humongous amounts of data. However, given the volume of data I'm handling, I wonder whether such tools are truly relevant for my company. I don't want to kill a mosquito by firing a bazooka, and unnecessarily complicate things. I can also simply build some Python scripts that run in our K8S cluster, and periodically retrieve/write data into our data storage.

Summarizing the background into one question:

From what volumes of data do data ingestion tools like apache nifi, flume, storm or tools like logstash become relevant?

Any advice would be greatly appreciated.

Answer 1

First, you seem to be missing a few zeros in the numbers you mentioned before you should start seeing problems (IMO)

Second, I've only seen Kafka as part of a data loading solution for ingesting data from multiple IoT devices.

In these instances, Kafka was used to solve the IoT problem.

ACID Compliant databases have problems ingesting a bunch of single row inserts from multiple clients. This is due to the fact that COMMIT won't return until data is safely written to disk.

Insert into live_data value ( ..... );
commit;

The solution is to cache the request to save data then bulk load it into the DB.

This is where Kafka comes into play. (We're talking scales upto 1M IoT readings per second)

If you have problems loading 2GB of data per day, you need to investigating why.

The key to performance is Bulk Loading of data and not using slow-by-slow (row by row) methods.

I have found that database code (PL/SQL; T-SQL) works faster than ETL tools (eg Informatica) but the ETL Tools are easier to maintain over the long term.

Answer 2

Data volume is one of the last criteria for choosing the ingestion pipeline implementation. You choose the tool for what it can do that your current tool cannot, and then you test it to see if can handle the volume (spoiler alert: it can; the database will be the bottleneck in 99.9% of the cases).