1

I am tasked to design a system that should receive data either as files or via an API and perform ETL functions. The end result is put into an RDBMS.

For the sake of example, imagine a system that handles billing for electricity companies. Some of the customers provide their usage as files on periodical basis, while others hook up the consumer meters directly and the meters send usage data every few minutes. The processing of both input is same.

So my system hast both, batch and data streaming use cases. One of my colleagues suggested to design the system for data streaming and when a file comes in, read the file and feed the data stream with lines from the file. I do not like this design but am struggling to find reasons why.

I searched this site and found this relevant question, but still cannot tell how my system should be designed.

2
  • 1
    Well, what benefits does streaming have in the context of a batch job? What benefits does reading all the data at once have? Are you able to reuse parts of your ecosystem if utilizing streaming? Can you edit your question to include more information? Commented May 28 at 11:33
  • 1
    I think the key question here is around SLAs. How fast does the streaming data need to be processed? In my experience, if you have both 'real-time' processing and batch processing, the lowest common denominator (batching) tends to be the limiting factor. That is, unless the consumers of this data are able to use partial data, they end up waiting for the batched data to do anything with any of the data.
    – JimmyJames
    Commented May 28 at 16:27

3 Answers 3

1

The kind of design I would recommend against is a design where you have to implement the same logic of your ETL processing twice, once for the file use case and once for the streaming use case. Hence, I think your colleague is on the right track, and it is definitely possible to implement the system that way.

Still, both use cases have some specifics which probably need to be dealt differently with. What I would try is a design where you have

  • a high-speed (maybe stateful) common API directly on the application server which connects to the RDBMS and can be used to process data of both use cases

  • a file-processing step, which validates files as a whole, sends warnings back to an uploader on a per-file granularity and uses the former API to import the data in case the file is ok

  • a network-based API (for example, a stateless REST API) which receives the data stream as events, preprocesses them and uses the common API as well.

That way, you can optimize the code for both use cases in regards to their specific performance and latency requirements, their concurrency requirements as well as to their specific error handling requirements.

0

A reason not to stream the file is that you may have to undo the whole batch if it errors part way through.

Say for example line 50 of 100 is badly formatted. You run the batch and process the first 49 lines before hitting the error.

You go back to the customer, "hey line 49 is weird, can you correct it?", "Sure thing!" they say. "here is the corrected file" They then send you a new file, which differs in several ways.

Here you want to be able to cancel the whole batch and just run the new file through. A use case that might just not be present in the "device streams data" requirements.

Obviously if you have already fully processed the first 49 lines you could find yourself in trouble.

-1

do not like

Sorry, I’m not finding any reasons in that. When grappling with design trade-offs, we need to write down the pros and cons. This will help future maintainers understand the design rationale, and evaluate whether conditions have changed to the point that a revised design is appropriate.

Follow your colleague’s sage advice. Treat each line of the input CSV file as a separate API data push transaction. The streaming use case is the one with more challenging requirements. Turning batch into streaming is straightforward.

Do take care to validate that each CSV file conforms to the same requirements as the online API. If even a single line is noncompliant, fail the entire file and report a diagnostic to the customer so they will better understand the requirements on future submissions.

Sometimes batch processing has a distinct performance advantage over online streaming. However, the OP question did not describe any such considerations. Streaming data pushes certainly can be put into hourly batches and processed on a deferred basis. This comes with the very large downside that error reporting shall be delayed, impacting the usability of the client’s streaming interface.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.