What makes a data pipeline scalable? Best practices for scalable design?

Question

I have been searching about this topic for a few days and have not yet found anything on books, courses or tutorials.

What is a way to make data pipelines more scalable, that doesn't involve NoSql or major investments like hadoop clusters?

Most of our pipelines are currently made with Python. They're pretty simple in nature: The application connects to a SQL database, fetches raw data into a dataframe, transforms it, then feeds it to a production application.

My question is: where in this design is scalability defined? Or rather, if we scale up the volume of data that goes in and out, which part of this design would start giving issues? I've read about how a big reason why NoSql is more efficient than SQL at scaling is that SQL constraints take a lot of resources to enforce.

So, in my example, would this already be scalable? since SQL only participates in the raw data ingestion process, while computations are being performed outside of SQL through python/pandas (so to my knowledge, no constraints are being checked or enforced) then finally the results are fed to another app.

Answer 1

Its hard to point at one part of the data pipeline as the possible bottle neck that will prevent it from scaling.

I am not sure if this is already being done, but running the Python application as a FaaS where it can scale up and down based on load could help. Or having multiple instances of the python application running to process the data more quickly.

Its hard to say if your example will be scalable. If there is no issue writing to the database, I would check the Python application and see about scaling that. If SQL database is being taxed, then the database will need to be scaled.

Answer 2

Based on your comment on DFord's answer:

I was thinking of splitting records in chunks of X size if the total amount exceeds a certain threshold, thenhanding the chunks to different threads

It seems you're running 1 process of your app, on-premise, not being able to benefit from the cloud FaaS approach (which I think it would be the best for cost-effective scalability).

In this case, I would add to DFord's answer an additional "Producer" app/process, that would:

• check for new records on initial DB;
• read the first X records and send them to be processed by 1 idle process of your Python app (e.g.: passing the IDs of the records on the DB, to be fully read by your app);
• in case there are massive amounts of data, it could automatically spawn new processes (or scale down, by killing idle Python processes too);

As an insight, your processes could even be running on different computers, if necessary -- again, if you're on the Cloud, serverless services such as FaaS (like AWS Lambda, for instance) would automatically take care of scaling up or down, like mentioned by DFord.

Answer 3

My question is: where in this design is scalability defined? Or rather, if we scale up the volume of data that goes in and out, which part of this design would start giving issues?

Scalability is usually separated into horizontal or vertical scaling.

"Horizontal scaling" for a data pipeline usually refers to the ability of partitioning the input data into chunks which can be processed independently from each other in parallel. If that's possible in your case depends heavily on the nature of the data and the specific transformation steps. One also has to evaluate whether the input and output stages of the pipeline may become a bottleneck. As mentioned here, several NoSQL databases support horizontal scaling through sharding.

"Vertical scaling" may either refer to introducing more intermediate processing steps into the pipeline, which only brings an improvement when those steps can run interleaved (but often for the price of a higher latency). Or it can refer to increasing the computing power of the hardware (or software) for the individual processing steps (for example, by using CPUs with higher clock speeds, systems with higher memory or network speed, or an improved compiler/interpreter). However, since the maximum affordable CPU clock speed hasn't really increased any more over the last decade for the major CPU types, this option has lost it's practical relevance to some degree. Today, improvements in hardware come mostly through parallelization.

In essence, vertical scaling is a restricted approach - it offers you a finite number of options, but when you took them, you may easily reach their limits. Horizontal scaling, however, allows you to scale way more dynamically up and down (under the precondition you will be able to split up the data into independently processable pieces.)