Enrich an Azure cost export using the API

The Greenpixie API lets you enrich your own Azure cost export in your own environment. You pull the rate card, join it onto your cost data, and multiply, so your data never leaves your control. This is the same enrichment Greenpixie's managed service performs, done on your side.

Before you start, you need an API key (see Getting started with the Greenpixie API) and your exported cost data (see Getting your cloud usage data).

How enrichment works

Pull the rate card at full granularity, where each row is one Azure meter with its exact per-unit factors. Join those rows onto your cost export, then multiply each factor by the quantity on the line to get energy, carbon and water for it.

Step 1. Discover what you can read

Call GET /v1/ratecards first. It returns the (provider, granularity) combinations your key can read, the columns each one returns, the grid granularities and date ranges available, and every published version. Build your next request from these values so nothing is hard-coded.

curl "https://api.greenpixie.com/v1/ratecards" \
  -H "Authorization: Bearer $GPX_KEY"

Step 2. Pull the rate card

Use the full granularity. Pull the whole rate card and page through results with the cursor in links.next until it is null. Filter only when you are sure your export needs just that subset: a region filter, for example, drops the region-less rows the join below falls back to. Each page holds up to 1,000 records.

curl -G "https://api.greenpixie.com/v1/ratecards/cloud/azure/full" \
  --data-urlencode "limit=1000" \
  -H "Authorization: Bearer $GPX_KEY"

Each row carries the join columns meter_id, meter_name, unit_of_measure and region, the SKU columns sku_name and arm_sku_name, and the per-unit factors usage_electricity_consumption_kwh_per_pricing_unit, total_tonnes_co2e_per_pricing_unit and total_water_litres_per_pricing_unit.

One row is unique on meter_id, unit_of_measure, region, sku_name and arm_sku_name. Where Microsoft's price list leaves one of these blank, the rate card serves unspecified; this is common for arm_sku_name, which only VM meters fill. One meter can have several rows, one per SKU name and VM size. Today those rows carry the same factors.

Step 3. Prepare your export

You need four columns from your cost export, plus the quantity.

Two things to prepare:

• Region. The rate card's region is the ARM region code in lowercase, for example westeurope. A FOCUS RegionId is already the code. An EA / MCA ResourceLocation is a display name such as West Europe, so map it to the code first. Store the result in a column called arm_region_name.
• Unit. Use the columns in the table. Do not use the FOCUS PricingUnit or ConsumedUnit columns, which are normalised units and match nothing.

Some meters have no region in Microsoft's price list. The rate card serves a blank region as unspecified, and Microsoft's Global as global. The query below does the same on your side: a blank or missing region on a billing line is treated as unspecified before the join. Where your billing line still carries the region of the resource, an exact join on region finds nothing for these meters, so the query falls back to the region-less row in that case.

Step 4. Join onto your cost export

A cost export carries the meter but not the SKU name, so the join key is meter_id, meter_name, unit_of_measure and region. The rate card can hold several rows for that key, one per sku_name and arm_sku_name. If you join them directly, each one duplicates your billing line and your totals come out too high. The query first collapses the rate card to one row per join key, then joins on the exact region, then falls back to the region-less row.

The collapse uses max, so it picks one value, not an average. Today the rows under one key carry the same factors. If a future rate card gives different VM sizes under one meter different factors, max keeps the highest one.

Do not add arm_sku_name to the join. Microsoft bills some VM pairs under one shared meter name, for example a Standard_D2s_v3 line shows D2 v3/D2s v3, but its price list pairs that name only with Standard_D2_v3. The rate card follows the price list, so those lines would not match. Use arm_sku_name to look up or filter rate card rows, not to join. Cost exports do not carry sku_name, and carry arm_sku_name only for virtual machines inside AdditionalInfo, so joining on the full key is not an option for most exports. The four-column join with the collapse is the one to use.

WITH ratecard AS (
    -- one row per join key
    SELECT meter_id, meter_name, unit_of_measure, region,
           max(usage_electricity_consumption_kwh_per_pricing_unit) AS kwh_per_unit,
           max(total_tonnes_co2e_per_pricing_unit)                 AS co2e_per_unit,
           max(total_water_litres_per_pricing_unit)                AS water_per_unit
    FROM api_output
    GROUP BY meter_id, meter_name, unit_of_measure, region
),
fallback AS (
    -- meters Microsoft prices without a region
    SELECT * FROM ratecard WHERE region IN ('unspecified', 'global')
)
SELECT
    b.*,
    b.quantity * coalesce(a.kwh_per_unit,   f.kwh_per_unit)   AS usage_electricity_consumption_kwh,
    b.quantity * coalesce(a.co2e_per_unit,  f.co2e_per_unit)  AS total_tonnes_co2e,
    b.quantity * coalesce(a.water_per_unit, f.water_per_unit) AS total_water_litres
FROM azure_cost_export b
LEFT JOIN ratecard a
       ON b.meter_id        = a.meter_id
      AND b.meter_name      = a.meter_name
      AND b.unit_of_measure = a.unit_of_measure
      AND coalesce(nullif(lower(b.arm_region_name), ''), 'unspecified') = a.region
LEFT JOIN fallback f
       ON a.meter_id IS NULL
      AND b.meter_id        = f.meter_id
      AND b.meter_name      = f.meter_name
      AND b.unit_of_measure = f.unit_of_measure;

Here azure_cost_export is your cost export and api_output is the rate card you pulled in step 2. Lines with no match, such as Marketplace purchases, keep null factors.

Step 5. Check the result

Count the rows. The enriched table must have exactly as many rows as your export. If it has more, a billing line matched more than one rate card row and every total is too high. Go back to step 4 and make sure the GROUP BY lists every column you join on.

If you join several months of usage to several months of rate cards, add the billing period to the join so each month uses the rate card for that month.

Data periods, refresh, and caching

By default, the API enriches using the carbon intensity from the previous full calendar month. This refreshes two days into each new month, once the complete carbon data for the previous month is available.

Whether you can get a past month's rate card back depends on your plan:

• Historic data is in your plan. You can request any past month with the grid_period parameter, at any time. To reproduce an old run exactly, also pin the version and grid_version it used.
• Historic data is not in your plan. Your key only serves the current rate card, built on the last complete month. Requesting an earlier grid_period returns a 403. Once the monthly refresh happens, that month's rate card is gone for you, so save the API output before the refresh and store it alongside your results.

Call GET /v1/ratecards to see which periods your key can read.

If you want to enrich the current month's usage before the month is over, you can use the previous month's rate card as an estimate. Re-enrich once the rate card refreshes on the 3rd of the following month, so the month uses its own completed carbon data.

To pin a run to a specific dataset, set the version, grid_version and grid_period parameters. See the API documentation for the full parameter reference.

Next steps