At my shop we do exactly this, we do ELT as opposed to ETL. (E=Extract, L=Load, T=Transform).
We put our input files (think of JSON documents) in the database, and then the data processing is a materialised SQL query.
This has a few benefits:
- The SQL dumps are very light as they will consist only of the input files, the materialisation is just a query, no need to store the transformed data.
- Some time there is an error in the business logic, how do we backfill? That is easy, we update the body of the materialised SQL query and then refresh the materialisation.
- Transactions are very hard.
We find this great for batch use cases.
Exciting to see progress in this space in the last years:
- materialize for incremental maintenance of materialised views
- postgres has a patch to start support incremental maintenance of materialised views in the works .
Lot of interesting ideas to move computation close to where the data reside can be found in this paper: http://www.cs.ox.ac.uk/dan.olteanu/papers/soc-sigmod16.pdf this is an example of how linear regression can be implemented which is intimately related to the article.