Enrich a Cloudability recommendations file

This guide adds Greenpixie's energy, carbon and water figures to your Cloudability rightsizing recommendations by joining the rate card onto them. It covers AWS compute (AmazonEC2) in us-east-1, for the two recommendation types rightsize and terminate. Autoscaling recommendations are out of scope.

Step 1. Get your rightsizing recommendations

Export your EC2 rightsizing recommendations from Cloudability. You can pull them from the Rightsizing Explorer UI, schedule them as a report, or fetch them from the Cloudability API. The Cloudability documentation covers each route:

• AWS EC2 rightsizing recommendations and row fields
• Rightsizing Explorer, for viewing, filtering and exporting
• Rightsizing overview
• Rightsizing API endpoints

The export has many fields, but only a handful matter for adding environmental figures. Exact column names can vary a little by export configuration and API version, so check them against the AWS EC2 doc above.

Call this dataset rightsizing. The joins below run in any query engine, so use whatever you already work in. At Greenpixie we are big fans of Databricks.

Step 2. Get your Greenpixie rate card

One call returns the us-east-1 compute rate card. Rightsizing recommendations do not carry a rate code, so use the usage granularity. It aggregates across rate codes and returns one row per instance type, where the _mean fields are the mean across the underlying rate codes. The full granularity returns a row per rate code, which you would have no key to join on.

curl -G "https://api.greenpixie.com/v1/ratecards/methodology/aws/usage" \
  --data-urlencode "line_item_product_code=AmazonEC2" \
  --data-urlencode "pricing_unit=Hours,Hrs" \
  --data-urlencode "region=us-east-1" \
  -H "Authorization: Bearer $GPX_KEY"

A row looks like the one below.

{
    "version": "meth-v5.2.0",
    "line_item_product_code": "AmazonEC2",
    "line_item_usage_type": "ATL1-BoxUsage:c6gn.12xlarge",
    "instance_type": "c6gn.12xlarge",
    "pricing_unit": "Hours",
    "region": "us-east-1",
    "estimate_category": "compute",
    "rate_code_count": 5,
    "usage_electricity_consumption_kwh_per_pricing_unit_mean": 0.0576613467911706,
    "total_tonnes_co2e_per_pricing_unit_mean": 0.0000279676373351656,
    "total_water_litres_per_pricing_unit_mean": 0.143528158031148
}

The three figures you will use are all per pricing unit, and the pricing unit is Hours, so each is the footprint of running one instance for one hour in us-east-1, with the region's PUE, water and carbon-intensity factors already applied.

The join key is instance_type.

The total_tonnes_co2e_per_pricing_unit_mean figure is already in metric tonnes. Multiply by 1,000 only if you need kilograms.

Call this dataset greenpixie_ratecard.

Step 3. Join on instance type

Join the rate card onto the recommendations twice on instance_type, once for the current instance and once for the recommended instance. No region join is needed here, because the region is fixed at us-east-1 and its factors are already inside the numbers.

FROM rightsizing r
LEFT JOIN greenpixie_ratecard cur   -- Current instance footprint
  ON lower(cur.instance_type) = lower(r."Instance Type - Current")
LEFT JOIN greenpixie_ratecard rec   -- Recommended instance footprint
  ON lower(rec.instance_type) = lower(r."Instance Type - Recommended")

cur gives the current intensities and rec the recommended ones. Lower-case both sides so casing differences between Cloudability and the rate card do not cause misses. For a terminate row, Instance Type - Recommended has nothing to match, so rec comes back with no row. That is fine, because the recommended footprint is zeroed for terminations anyway in the next step.

Identifier quoting depends on your SQL engine, so use double quotes, backticks or brackets as appropriate, or alias the columns on load to remove the spaces.

Step 4. Calculate the footprint and savings

Multiply each per-hour intensity by Hours Running. The current and recommended instances both run for the same hours, so Hours Running applies to both. A terminate removes the instance entirely, so its recommended footprint is zero.

SELECT
  r.*,

  -- CURRENT footprint = per-hour intensity * hours the instance ran
  cur.total_tonnes_co2e_per_pricing_unit_mean                 * r."Hours Running" AS current_co2e_tonnes,
  cur.usage_electricity_consumption_kwh_per_pricing_unit_mean * r."Hours Running" AS current_kwh,
  cur.total_water_litres_per_pricing_unit_mean                * r."Hours Running" AS current_water_litres,

  -- RECOMMENDED footprint = per-hour intensity * same hours; zero if terminated
  CASE WHEN r."Recommendation" = 'Terminate' THEN 0
       ELSE rec.total_tonnes_co2e_per_pricing_unit_mean       * r."Hours Running" END AS recommended_co2e_tonnes,
  CASE WHEN r."Recommendation" = 'Terminate' THEN 0
       ELSE rec.usage_electricity_consumption_kwh_per_pricing_unit_mean * r."Hours Running" END AS recommended_kwh,
  CASE WHEN r."Recommendation" = 'Terminate' THEN 0
       ELSE rec.total_water_litres_per_pricing_unit_mean      * r."Hours Running" END AS recommended_water_litres

FROM rightsizing r
LEFT JOIN greenpixie_ratecard cur
  ON lower(cur.instance_type) = lower(r."Instance Type - Current")
LEFT JOIN greenpixie_ratecard rec
  ON lower(rec.instance_type) = lower(r."Instance Type - Recommended");

Wrap that in an outer query, or a second CTE, to turn current minus recommended into savings.

SELECT
  *,
  current_co2e_tonnes  - recommended_co2e_tonnes  AS carbon_savings_tonnes,
  current_kwh          - recommended_kwh          AS energy_savings_kwh,
  current_water_litres - recommended_water_litres AS water_savings_litres
FROM enriched
ORDER BY carbon_savings_tonnes DESC;

Why this works

An instance's footprint is its per-hour intensity multiplied by how long it runs, and Greenpixie has already done the physical calculation behind that per-hour figure, covering per-vCPU power, vCPU count, PUE, grid emissions factor and water factors, region-adjusted for us-east-1. So all you supply is the hours:

• Rightsize. current = current per-hour intensity * Hours Running, and recommended = recommended per-hour intensity * Hours Running. The workload runs the same hours, and only the instance type, and therefore the per-hour intensity, changes.
• Terminate. current is calculated the same way, and recommended is zero because the instance goes away entirely.
• Savings for both is current - recommended.

Edge cases to keep in mind

(multiple) current instance types. Some rows report Instance Type - Current as (multiple). The usage-granularity card is keyed only on instance_type, so these do not match cur and their current-side metrics come back empty. Decide how to treat them, whether to drop, flag, or request a family-level breakdown. For a clean example, filter them out up front.

Unmatched instance types. Both joins are LEFT JOIN, so any instance type missing from the rate card yields empty intensities and empty footprints, usually what you want. Add a check that counts unmatched rows with WHERE cur.instance_type IS NULL so coverage gaps are visible.

Region. This example assumes everything is us-east-1 and pulls a single-region card. If your recommendations span regions, pull a card per region and add region to both join keys, for example AND cur.region = r."Region", because the per-hour footprint changes with the region's PUE, water and grid factors. Do not reuse a us-east-1 card for another region.

Next steps