Let's take a look at a practical example of a transformation using our Airline Ontime Performance (OTP) project. Let's say that our transformation task is to get from a source stage table that we created in Chapter 5, Ingest and Organize Data Lake, (which is on the left-hand side) to aggregated (which is on the right-hand side) summary data by airline carrier, year, and month.
To achieve the preceding transformation, we need to perform the following key steps:
Clean the header line in each file that has the field names.
Update the flight month from the current "MM" to "YYYYMM" format.
Create an intermediate table with the refined data from the previous two steps.
Aggregate the data from the refined data to a summary table at flight year, flight month, and carrier levels.
We will use Pig to do a cleanup and then use Hive to preserve the results as shown in the following figure:
