Querying Data from Multiple Redshift Spectrum Tables

Scenario

You have been tasked with testing accessing data stored in S3 from a Redshift cluster. Your team is hoping to realize cost savings by moving infrequently accessed non-latency sensitive data outside of your company's production Redshift cluster. They've provided the required tables below with the requisite column headers, as well as test data in an S3 bucket. You will need to create Redshift Spectrum tables with the appropriate DDL and run the provided test query to ensure Redshift Spectrum will function for the planned use case.

Additional Resources

GitHub Repo

Redshift Create External Table

Required Tables

Names
id_name
id_value
gender
name_title
name_first
name_last

Location
id_name
id_value
location_street_number
location_street_name
location_city
location_state
location_country
location_postcode
location_coordinates_latitude
location_coordinates_longitude
location_timezone_offset
location_timezone_description
nat

Age
id_name
id_value
dob_date
dob_age
registered_date
registered_age

Contact
id_name
id_value
email
phone
cell

Picture
id_name
id_value
picture_large
picture_medium
picture_thumbnail

Test Query

select 
    names.name_first as first_name, 
    names.name_last as last_name, 
    location.location_state as state, 
    age.dob_age as age, 
    contact.cell as cell, 
    picture.picture_large as picture
from users_data.names
    join users_data.location on users_data.names.id_value = users_data.location.id_value 
    join users_data.age on users_data.names.id_value = users_data.age.id_value 
    join users_data.contact on users_data.names.id_value = users_data.contact.id_value
    join users_data.picture on users_data.names.id_value = users_data.picture.id_value
order by age
limit 10;