Exercise 5

Create Table for Aggregate Country Temperatures

The objective of this exercise is to aggregate temperature measurements per country on an annual basis using the data from the stg_temperatures_by_country source. Additionally, you will need to clean up the "Country" column by removing leading/trailing spaces and converting the country names to the proper format.

To create a table called aggregate_country_temperatures with the desired columns in the global_temperatures schema:

1. Open your dbt project and navigate to the appropriate schema directory (e.g., models/global_temperatures).
2. Create a new file named aggregate_country_temperatures.sql (or any desired name) in the schema directory.
3. In the aggregate_country_temperatures.sql file, write the dbt model definition for the table.
4. Define the aggregate_country_temperatures model using the table materialization type.
5. Investigate the data in the stg_temperatures_by_country source for any data quality issues and address them as necessary.
6. Clean up the "Country" column by removing leading/trailing spaces and converting the country names to the proper format. You can use the initcap function for this purpose.
7. Remove any occurrences of the Lenny face '( ͡° ͜ʖ ͡°)' from the AverageTemperature column.
8. Aggregate the temperature measurements per country on an annual basis.
9. Ignore any 'Uncertainty' columns for this project.
10. Select the following columns for the output table:
    • Year: Integer
    • Country: String
    • AverageTemperature: Float

By following these instructions and executing the appropriate dbt commands, you will create a table called aggregate_country_temperatures in the global_temperatures schema. The table will contain the aggregated temperature measurements per country on an annual basis, with the specified columns as outlined above.