Explore the results 🔎¶

Interactive dashboard¶

For an interactive view of SPRUCE-enriched Parquet output, use the Streamlit example described in Build a simple dashboard. It reads the same inputs as report.py and runs DuckDB queries locally.

Automated report¶

The easiest way to explore SPRUCE output is report.py, a Python script that reads enriched Parquet files, runs all the analyses described on this page automatically, and writes a formatted report — no SQL required. Its queries use the FOCUS columns emitted by all SPRUCE pipelines, so it works on the enriched output of every supported input format (AWS CUR, AWS FOCUS, Azure cost details, Azure FOCUS). If the input is not partitioned by BILLING_PERIOD, the billing periods are inferred from the FOCUS ChargePeriodStart column.

Installation¶

pip install -r reporting/requirements-report.txt   # duckdb (+ markdown, weasyprint for html/pdf)

Usage¶

# Markdown to stdout
python reporting/report.py -i output/

# Write to a file — format is inferred from the suffix
python reporting/report.py -i output/ -o report.md
python reporting/report.py -i output/ -o report.html
python reporting/report.py -i output/ -o report.pdf

# Read directly from S3 (uses ambient AWS credentials)
python reporting/report.py -i s3://my-bucket/spruced/ -o report.html

Docker¶

Instead of installing Python and its dependencies, you can simply use the Docker image to generate the reports:

docker run -it --rm -v $(pwd):/workspace -w /workspace \
ghcr.io/digitalpebble/spruce \
report -i output -o report.html

What it produces¶

The report covers the following sections, drawn from the queries documented below:

After the fixed sections, the script scans the Tags column and presents an interactive menu of the most consistently used tag keys, ordered by the percentage of line items that carry a non-empty value. Select a tag to see the emissions breakdown by tag value; press Enter when done. If no tags are found, the report states this clearly.

Recommendations are generated automatically from the data:

Manual queries¶

As an alternative to the script above, you can also write equivalent SQL queries using DuckDB locally (or Athena if the output was written to S3). The examples below use the native AWS CUR columns, which pass through into the enriched output alongside the FOCUS ones; on the output of another pipeline, substitute the corresponding provider or FOCUS columns (e.g. ServiceName, BilledCost):

Breakdown by billing period¶

create table enriched_curs as select * from 'output/**/*.parquet';

select 
       BILLING_PERIOD,
       round(sum(operational_emissions_co2eq_g) / 1000, 2) as co2_usage_kg,
       round(sum(embodied_emissions_co2eq_g) / 1000, 2) as co2_embodied_kg,
       round(sum(operational_energy_kwh),2) as energy_usage_kwh
       from enriched_curs
       group by BILLING_PERIOD
       order by BILLING_PERIOD;

This should give an output similar to

Breakdown per product, service and operation¶

select line_item_product_code, product_servicecode, line_item_operation,
       round(sum(operational_emissions_co2eq_g)/1000,2) as co2_usage_kg,
       round(sum(embodied_emissions_co2eq_g)/1000, 2) as co2_embodied_kg,
       round(sum(operational_energy_kwh),2) as energy_usage_kwh
       from enriched_curs where operational_emissions_co2eq_g > 0.01
       group by all
       order by 4 desc, 5 desc, 6 desc, 2;

This should give an output similar to

Cost coverage¶

To measure the proportion of the costs for which emissions were calculated

select
  round(covered * 100 / "total costs", 2) as percentage_costs_covered
from (
  select
    sum(line_item_unblended_cost) as "total costs",
    sum(line_item_unblended_cost) filter (where operational_emissions_co2eq_g is not null) as covered
  from
    enriched_curs
  where
    line_item_line_item_type like '%Usage'
);

The figure will vary depending on the services you use. We have measured up to 77% coverage for some users.

Breakdown per region¶

with agg as (
    select
        region as region_code,
        sum(operational_emissions_co2eq_g) as operational_emissions_g,
        sum(embodied_emissions_co2eq_g) as embodied_emissions_g,
        sum(operational_energy_kwh) as energy_kwh,
        sum(pricing_public_on_demand_cost) as public_cost,
        avg(carbon_intensity) as avg_carbon_intensity,
        avg(power_usage_effectiveness) as pue
    from enriched_curs
    where operational_emissions_co2eq_g is not null
    group by 1
)
select
    region_code,
    round(operational_emissions_g / 1000, 2) as co2_usage_kg,
    round(energy_kwh, 2) as energy_usage_kwh,
    round(avg_carbon_intensity, 2) as carbon_intensity,
    round(pue,2) as pue,
    round((operational_emissions_g + embodied_emissions_g) / public_cost, 2) as g_co2_per_dollar
from agg
order by energy_usage_kwh desc, co2_usage_kg desc, region_code desc;

g_co2_per_dollar being the total emissions (usage + embodied) divided by the public on demand cost.

Below is an example of what the results might look like.

Breakdown per user tag¶

User tags are how environmental impacts can be allocated to a business unit, team, product, environment etc... It is as fundamental for a GreenOps practice as it is for FinOps. By enriching data at the finest possible level, SPRUCE allows to aggregate the impacts by the tags that are relevant for a given organisation. The syntax to do so for a tag cost_category_top_level would be for instance

select resource_tags['cost_category_top_level'],
       round(sum(operational_energy_kwh),2) as energie_kwh,
       round(sum(operational_emissions_co2eq_g) / 1000, 2) as operational_kg,
       round(sum(embodied_emissions_co2eq_g) / 1000, 2)    as embodied_kg
       from enriched_curs
       group by 1
       order by 3 desc;