ETL
When we have a data-driven app, we really have just two options: 1) build forms and code to store our data in exactly the shape we're going to use it or 2) use ETL (Extract, Transform, Load) tools and techniques to shape our data into the necessary output formats.
The popular visual, gui-based ETL tools that I know of in wide use today are Alteryx, Tableau Prep Builder and Microsoft SSIS. There are many more. ETL tools (this could be code like C#, Python, R, etc. but here I'm only talking about visual tools that to me are more approachable and usable) are used to connect to data sources (extract), twist and turn the data into the shapes and outputs you want (transform) and save them off for access later (load).
 |
| The Tableau Prep Builder user interface that in the top pane, left side, we connect to some data, twist and turn it into our output shapes, join it and all that visually, then finally output it to a final source. |
MVC
MVC is the Model View Controller software pattern. There are many frameworks that implement this pattern. My favorite is Microsoft ASP.NET MVC, which I have been using since 2009. Something that goes really well with MVC in ASP.NET is Entity Framework, which makes it easy to connect to and work with data in a language like C#. Another powerful aspect of the .NET Framework is LINQ to SQL which makes it easy to work with data within code.
 |
| What the folder structure of an ASP.NET MVC web application looks like. Note that there are folders for the Models, Views and Controllers. This makes it easier for developers to separate and utilize these three aspects. |
The Model in MVC is the database, and perhaps the one we created above in ETL. There may be additional database tables we need to use and all the "logic" of the data and its shape and form live within this module.
The Controllers in MVC are those things that help us get and put the data. From controllers, we connect to, work with, surface and store data. Some transformations can take place here but for the purposes of this article, I am handling all the "heavy lifting" data transformations in the ETL flow described above. The benefits of this approach are the we can separate our concerns between MVC which is great for making software applications and ETL, which is great for turning data into the shapes we need for our apps.
The Views in MVC are the actual screens that users see and interact with. Screens can be created for viewing or entering data. The views might have ViewModels that go into them that make it easy for the logic of the view to present the data to the user.
MVC and ETL Together: PB&J
By combining the power of MVC to make applications with the power of ETL to turn your data into easily-usable shapes, developers today can expand their reach, improve the quality of their apps and time to market.
