# Enrich a Cloudability recommendations file
Source: https://greenpixie.com/docs/guides/cloudability-rightsizing

How to add energy, carbon and water to Cloudability rightsizing recommendations.

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, product code `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 [#step-1-get-your-rightsizing-recommendations]

You can pull your EC2 rightsizing recommendations 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](https://www.ibm.com/docs/en/cloudability-commercial/cloudability-standard/SSVCLNQ/cloudability/product/rightsizing-for-aws-ec2.html)
* [Rightsizing Explorer, for viewing, filtering and exporting](https://www.ibm.com/docs/en/cloudability-commercial/cloudability-standard/SSVCLNQ/cloudability/product/rightsizing-explorer.html)
* [Rightsizing overview](https://www.ibm.com/docs/en/cloudability-commercial/cloudability-standard/SSVCLNQ/cloudability/product/get-recommendations-for-scaling-your-cloud-resources-with-rightsizing.html)
* [Rightsizing API endpoints](https://www.ibm.com/docs/en/cloudability-commercial/cloudability-standard/SSVCLNQ/cloudability/api-v3/rightsizing_end_points.html)

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.

| Field                         | Why you need it                                                                                               |
| ----------------------------- | ------------------------------------------------------------------------------------------------------------- |
| `Instance Type - Current`     | Join key for the current instance's footprint                                                                 |
| `Instance Type - Recommended` | Join key for the recommended instance's footprint, ignored when the recommendation is a termination           |
| `Hours Running`               | Hours the instance ran over the period, which multiplies both the current and recommended per-hour footprints |
| `Recommendation`              | Whether the row is a rightsize or a terminate                                                                 |

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 [#step-2-get-your-greenpixie-rate-card]

Rightsizing recommendations do not carry a rate code, so request the `us-east-1` compute rate card at `usage` granularity, which returns the whole card in one call. 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.

```bash
curl -G "https://api.greenpixie.com/v1/ratecards/cloud/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.

```json
{
    "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`. Each is therefore the footprint of running one instance for one hour in `us-east-1`, with the region's power usage effectiveness (PUE), water and carbon-intensity factors already applied by Greenpixie.

| Field                                                     | Meaning                  | Unit               |
| --------------------------------------------------------- | ------------------------ | ------------------ |
| `usage_electricity_consumption_kwh_per_pricing_unit_mean` | Energy per instance-hour | kWh                |
| `total_tonnes_co2e_per_pricing_unit_mean`                 | Carbon per instance-hour | metric tonnes CO2e |
| `total_water_litres_per_pricing_unit_mean`                | Water per instance-hour  | litres             |

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 [#step-3-join-on-instance-type]

Match the rate card to 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 Greenpixie has already applied that region's factors to the rate card figures.

```sql
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 [#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, so its recommended footprint is zero.

```sql
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 the query above in an outer query, or a second CTE, to turn current minus recommended into savings.

```sql
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 [#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:

* For a 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.
* For a terminate, `current` is calculated the same way, and `recommended` is zero because the instance no longer runs.
* Savings for both is `current - recommended`.

## Edge cases [#edge-cases]

### `(multiple)` current instance types [#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 these rows, whether to drop, flag, or request a family-level breakdown. For a clean example, filter these rows out up front.

### Unmatched instance types [#unmatched-instance-types]

The current and recommended joins from Step 3 are both `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 [#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 [#next-steps]

- [Load Greenpixie data into Cloudability](https://greenpixie.com/docs/guides/cloudability-integration): Integrate your enriched cloud usage report data into Cloudability.
- [Getting started with the Greenpixie API](https://greenpixie.com/docs/guides/getting-started-api): Generate a key and make your first call.