(inspiring music)
- [Instructor] I've now downloaded my Arabesque S-Ray data,
and you can see that in the data catalog here
on the left in Amazon Athena.
If we click on one of these tables, this small triangle,
we can expose the fields and the datatypes
within that table.
From here, we're looking to package our data
to make it useful to our consumers.
But before we do that,
we're going to want to understand the shape of our data.
So here I've created a query
that will aggregate a number of different fields
from within that dataset.
And firstly I'm going to count the number of records,
I'll then count the number of distinct FactSet
or company identifiers within the dataset.
I'll do the same for the sector
and industry to understand their makeup,
and then I'll take an average scope one emissions score
across all of those organizations
and sum those scope one emissions.
I'll do that from the emissions_data table, and I'll do that
where the scope one emissions data is not null.
If we query that data and run that query,
we can now see that we have over 700,000 records,
2,928 companies, 20 sectors,
128 individual industries represented,
and you can see the average
and total emissions on the screen.
Regularly querying over 700,000 records isn't efficient.
So we'll want to package our data views for our consumers.
And we'll do that with aggregate functions like the sum,
the average, and the count that you can see on the screen.
But we'll also use advanced functions
like the histogram, and that will allow us
to maintain the underlying data
within the dataset when it is aggregated.
And as you can see here, we can now see the distribution
within individual sectors within this specific data.
Thinking about practicalities again for a second,
it's likely we actually only really
want the most recent data from the dataset
as it goes back quite a few years,
and we tend to care more about today
than this time last year.
So to do that, again we can use aggregate functions.
So in this example, again I'm going to select the dates,
the sector, the industry,
I'm going to sum my scope one, two, and three emissions,
and I'm going to conduct a count
of the distinct FactSet or company identifiers
and name that the sample size.
Here, we'll use histograms to maintain both the country
and the region within this output dataset,
and we'll use a map aggregate
that'll allow us to map key values of individual companies,
and their individual scope one emissions,
so that that data isn't lost.
We're going to get that data from the emissions_data table,
but this time we're going to conduct an inner join,
and that inner join can be seen here
and what this will do, we'll select only the max date
within that dataset and return only the maximum date
from the primary emissions dataset.
You'll now see that that query is successful
and we can see a latest_sector_industry_aggregate view
over here on the left.
If we wish to see the content of that view,
we can select the three dots and click Preview.
Now let's do the same, except this time,
instead of emissions data,
we're going to want to do the same with the ESG
and GC scores data.
So here, I'm going to again, create or replace a view,
this time, an ESG_GC view,
and I'm going to use the same date sector
and industry information.
But this time I'm going to conduct averages
across these GC total scores
and all of the underlying dimensions.
Again, I will seek to understand the sample size
and I'll use histograms and map aggregates
to maintain the data that we have
at the lowest possible level.
We run that query.
You'll now see that I have a second view available
to me on the left.
At the moment we are using views to query the original data
and those 700,000 original records,
and that makes our query a little slow
and a little inefficient.
So to make queries more efficient,
we might want to create a persistent table
that our users can consume instead.
So in this next example,
what we will do is instead of creating a view,
we will create a table and we'll create that table based
on our two new views, and from that,
we will pull through all of the various dimensions
of each table, where the industry matches
on both the ESG and GC table and the emissions table.
And if you run that query,
you should see a new table appear in the data catalog
on the left is entitled latest_industry_aggregate.
And we're also going to do the same
for not just the industry, but also the sector.
And because our sectors are made up of multiple industries,
again, we're going to have to use these aggregate functions
like sums to add together all of the scope one emissions,
as an example, across all of this industries
within a specific sector,
we'll also use map aggregate functions to maintain
that useful information such as the countries
or regions at the individual companies within this dataset.
As you can see, we are joining our two industry
and sector aggregates again,
and we're joining where the sector
and industry match on both sides of this join.
If we run that query,
you should be able to see a second table appear
on the left-hand side in our data catalog,
entitled latest_sector_aggregate.
Now that we've got latest sector and industry aggregates,
we might actually want to join the fuller datasets
for emissions and ESG scores.
To do that, we can use a conditional join.
So here, again we're going to create a separate table
from the original table,
so that our users can query that direct.
We're going to include many and various fields
from the emissions data and the ESG scores data.
And we're going to include them where the FactSet
or company identifier in table A is the same
as the identifier in table B.
In addition to that,
we're going to add another aggregate function,
and this time we're going to create 10 percentile buckets
that are partitioned by date
and will provide us the overall ranking
of the individual record
within a date for scope one emissions
and GC and ESG total scores.
If we run that query,
that will take a few seconds to run, but once complete,
we'll have a new table entitled full_gc_esg_emission_data
that we can then begin to visualize
in a visualization tool like Amazon QuickSight,
as an example.
So we'll just let this query complete and that's it.
Our query has been successful.
Now that we have a series of curated tables and views,
we can present this data
to our end users via a visualization tool.
So here, in this example,
I've provided a visualization of all of the data
within the dataset.
So we can see overall performance indicators
with the speedometers.
We can see a chronology over time
and a forecast of any future PSG scores.
And we can see the ESG and GC scores over time,
alongside the running total
of cut scope one carbon emissions.
In the middle,
we can see the trends where the ESG
or GC scores are changing over time
and the date periods that they're changing between.
And we can see a heat map of the individual organizations,
so that we can get a very visual indicator of the strong
and poorly performing organizations within this dataset.
In terms of how you might use this data
or this visualization,
as an organization, you may wish to look at industries
or sectors for due diligence,
for maybe preparing tender lists.
Alternatively, you may have a portfolio of organizations
that you wish to monitor,
and you could filter back to those organizations
to actively monitor their ESG carbon emissions
and GC scores over a longer time period.
Imagining that we've already applied a filter
to this dataset, maybe for organizations
within our portfolio
or organizations that are part of a tender list.
What we can then do is look at these organizations
in a visual manner using the ESG and GC scatterplot.
The scatterplot provides us with a very visual indicator
at the organizations who are performing well
in both ESG scores and GC scores.
The size of the individual data point
represents the scope one emissions,
and if we move our mouse onto the data sheet,
we're now able to drill down into those high
or low performing organizations
to understand not just their ESG and GC total scores,
but also industry and sector,
and the metrics and dimensions
that underpin those top level scores.