1

So I was discussing coding with an associate of mine at work, and was mentioning how I was working on a project where I'd need to transform the data that was provided into a standardized format before processing it (applying business rules, validation, etc). He suggested that the standardization of data should be a completely separate step, opr even a seperate program, where the information is processed into a standardized format, and then either saved or streamed to the next step.

While I'm looking at it as an unneeded separation, it may make sense to separate if I followed a micro service architecture? Wanted to see what's considered best practice

2

You combine two questions: shall standardisation performed before ETL and shoudl ETP steps be performed separately. Here some thoughts to help you:

  • Data standardisation in order to process data in a known format is part of the ETL (Extract Transform Load) process. So it is not needed to perform this before handling to an ETL framework, unless the standardisation is required for other purpose also.
  • If the standardisation can be performed together with the rest of the transformation in one pass, it might not be desirable to separate the transformation in two steps (in order to avoid extra writing overhead when the transformation operation can be done entirely in memory).
  • However, if the standardisation and the processing can not be implemented in a single step (e.g. if one is an aggregation, and the other a transformation of the aggregation rather than two processes on the same record), it could be useful to implement both as a separate step in the ETL chain: this would facilitate distribution for parallel processing (e.g. one node is doing the standardisation and passes intermediary data, as soon as it is ready, to the next node). The result is faster throughput (even if required processing power is the same). For examplen instead of having batches processed in sequence (2 hours for the first step + 1 hour for the second step = 3 hours overall), you may then have a data pipeline (2 hours for the first step, the first record for the next step is available after an overhead of 2 minutes = 2 hours + 2minutes overall).
1

I can think of two potential reasons why it could make sense to have the "transformation into a standardized format" a separate step:

  • this first step may fail because the quality of the input data is not good enough. So a user of the ETL process may want to run the transformation first, check the results, then correct the input data and repeat the former step until the input data can be transformed flawlessly

  • the output of the fist step might be used for other processes than the "standard business processing", and the users may decide "on the fly" what they are going to do with the result

Note this is the users point of view. If you implement the two steps in one or two programs is completely up to you. Using one program might be simpler in case you want the two steps to be executed always one after another in case the first step does not fail. Using two programs may be simpler in case you want them independently scale-up for parallel processing.

However, this is a grey area, sometimes just a matter of taste. You can always design one program with options to process just step one or just step two, and you can also design two programs with some additional control logic so they behave just "as one".

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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