Applying data analysis using SQL

Given all the preceding discussions, you are now familiar with the common data processing flow. This includes ingesting source files into staging areas, cleansing and transforming raw data, loading transformed data into the data warehouse, and retrieving results using complex queries for downstream analysis. All these can be achieved using SQL. In the following exercises, you will practice your knowledge by applying SQL to each step. The end goal of the exercises is to move the data from a source system file to a data warehouse that is usable for data analysis tools.

Exercise 14.1: Copying from a file into the staging table

The first step of data processing is to ingest the data from the source into the staging area. Source data usually comes in from transactional systems as text files. So, you will start by copying a file into a staging table in this exercise.

ZoomZoom has a smaller transactional system that runs in parallel with the main...