title: Need advise on designing/architecting a Spring Batch ETL job
body: <p>I need opinions on how to go about designing the ETL process for a destination Summary table in Spring Batch.</p>
  
  <p><strong>Situation</strong></p>
  
  <p>I do have a (monster) query that its a combination of multiple queries joined together that produces the grid that I intent as my final summary table. The "shape" of the query is as follows:</p>
  
  <pre><code>select A.student_id,
         A.anoterField,
         B.anotherField,
         B.anotherField,
         C.anotherField,
         C.anotherField,
         D.anotherField
  from(               
            (select s.student_id         
             from table_name
             inner join another_table
                 on
             where     
             and
             group by s.student_id
             )A
  inner join (select s.student_id
                     , ...
                     , ...
              from table_name
              inner join another_table
              where    
              and
              group by s.student_id
              )B
      on a.student_id = b.student_id            
  inner join (select s.student_id
                     , ...
                     , ...
              from table_name
              inner join another_table
              where    
              and
              group by s.student_id
              )C  
      on a.student_id = c.student_id              
  .
  .
  .
  .
  .
  .
  </code></pre>
  
  <p><strong>Problem</strong></p>
  
  <p>This query is ran against a Kimball Based datawarehouse for one school only and it already is a fairly intense query after all the sums,having,grouping etc that each of the inner queries do.</p>
  
  <p><strong>Solution</strong></p>
  
  <p>I decided I want to do an ETL job that will produce a summary table.</p>
  
  <p>I decided that I am not going to do any Math at the database level. I just want to select the data it's more granular form and have the ETL do the grouping and summing for 
  me.</p>
  
  <p><strong>Design Questions</strong></p>
  
  <p>I have managed to get familiar with Spring Batch by reading, processing, and writing items but I am still learning the framework. </p>
  
  <p>So how should I design this job...</p>
  
  <ol>
  <li><p>I was thinking in creating a as many readers as inner queries I have, then send each one of those outputs(A List of Objects) be sent to <strong>one and only processor</strong> this processor would have all the business logic(sums, having, grouping that the query above had) and would create one object (database row) per student. Finally, send the list of created objects to a writer, which would populate the summary table. Is that even possible since is my understanding that each step has a reader, processor, and writer?</p></li>
  <li><p>Given that each step has a processor and writer, should I have each of my inner queries in a step, have its own processor which does the summary of that particular data, and write it to a file. And finally have a step that grabs all those files, the processor for that merges the data creating one Object, ending up with a List of objects and send it to the writer that populates the summary table?.</p></li>
  </ol>
  
  <p>Which one would you choose?</p>
  
  <p>In case of the 2nd option, Can the reader process of a step read from multiple sources? In other words can I have the final step  reads 5 or 6 files and send a Map of Lists to one processor only?</p>
  
  <p>I hope it's clear let me know if not. I am just trying get my mind around designing batch jobs with Spring Batch.</p>