Data Warehousing with AWS Redshift

With AWS Glue, the information that was initially in a flat model can now be represented with a more fitting star schema in a knowledge warehouse.

The cloud data warehouse for this data will likely be created with AWS Redshift Serverless. This entails making a namespace named flights-namespace in addition to a database named dev. As well as, it requires a workgroup named flights-workgroup, which will likely be used to jot down SQL queries.

Note: The workgroup has been configured to permit devices outside of the VPC to access the database. This will likely be useful when creating the visualization with Power BI

Now, we will open the query editor in Redshift and begin creating the actual fact and dimension tables within the dev database.

First, the 4 tables within the schema have to be created within the warehouse using the next commands:

The 4 tables are actually in the information warehouse, but they’re all empty for the reason that data remains to be within the flights-data-processed bucket.

The information could be copied into this data warehouse using the COPY command.

As an example, the information in flights.csv could be copied into the flights table using the next command syntax:

Note: the iam_role variable needs to be assigned whatever iam role is was chosen when creating the workgroup.

By executing the COPY command for every of the csv files within the flights-data-processed bucket, the 4 tables needs to be crammed with the obligatory data.

For example, here’s a preview of the airport table: