
W. Tamminga, Spring 2022

Q. I know it is not directly related to the class content, but I was wondering if you could explain a common ETL solution. Specifically, what might the basic process look like and what kind of tools might be implemented? Outside of database construction and querying, ETL seems like the most important process of maintaining the database with information, so I was just wondering what the process might look like and what tools might be used? For example, I would assume for something like transactional data that a customer's order might be entered on a website, sent to some repository where the information is tidied/cleaned, and then automatically inserted into the formal database.

A. In a perfect (business information) world, all data would be stored in a common, consistent database format, from which we could query the information we need to do our analyses. In the real world, this is rarely the case - data tends to have been collected by a variety of people over many years using different formats in disparate repositories, and these data repositories have "inertia", meaning that once stored in one format, it's hard to change formats.

Because of this, we often need to collect data from myriad repositories and wrangle/massage it into a consistent format for analyses. That's ETL. I'd probably call it a "necessary evil" rather than a "important process", but both are probably true.

Your example could require ETL or not, depending on the nature of the website. If the website is a modern, relational-database-driven site, then the data might already be stored in a common, relational format and wouldn't require ETL. If it's a Google Form that dumps its data into a spreadsheet that needs to be downloaded from GoogleDocs and imported into a relational database for join queries on other data, then you've got ETL.

It's scary how much of this needs to be done. My colleague Pat Bailey, in a study done about ten years ago (yes, dated, but probably still true to some degree), found an astonishing number of isolated, individually-maintained, inconsistently-formatted, and yet mission-critical nevertheless, databases stored in MS Access across a wide number organizations. 
  