General Explanation

What is this recipe?

This recipe shows how to sum all of the energy returned to the grid by site or by meter. Energy returned to the grid is referred to as “bidirectional out” in the Arcadia data model.

This recipe requires that all meters are mapped to sites in the Plug Dashboard. There is also a variation below that can sum by meter rather than by site.

Use case

What is this recipe for?

This recipe is for users who would like to see how much energy their sites generated and returned to the grid.

Snowflake SQL

What’s the snowflake SQL for this recipe?
What does the SQL for this recipe mean?

select site_id
    , year(period_end_date) as calendar_year
    , sum(standardized_bidirectional_out_total_consumption_usage_value) as total_bidirectional_out
		, any_value(standardized_bidirectional_in_total_consumption_usage_unit_of_measure) as unit_of_measure
from <YOUR_SNOWFLAKE_SHARE>.datahub_uc_meters_by_statement_current
group by site_id, calendar_year
order by total_bidirectional_out desc;

This query sums the bidrectional_out usage values by site by year.

This will return nulls if there is no bidirectional out usage for site, so to filter that out you can add a where clause like below.

select site_id
    , year(period_end_date) as calendar_year
    , sum(standardized_bidirectional_out_total_consumption_usage_value) as total_bidirectional_out
    , any_value(standardized_bidirectional_in_total_consumption_usage_unit_of_measure) as unit_of_measure
from <YOUR_SNOWFLAKE_SHARE>.datahub_uc_meters_by_statement_current
where standardized_bidirectional_out_total_consumption_usage_value is not null
group by site_id, calendar_year
order by total_bidirectional_out desc;

A variation to this recipe is to group by meter_id rather than by site_id. This variation will show all of the energy returned to the grid by logical meter_id by year.

select meter_id
    , year(period_end_date) as calendar_year
    , sum(standardized_bidirectional_out_total_consumption_usage_value) as total_bidirectional_out
    , any_value(standardized_bidirectional_in_total_consumption_usage_unit_of_measure) as unit_of_measure
from <YOUR_SNOWFLAKE_SHARE>.datahub_uc_meters_by_statement_current
where standardized_bidirectional_out_total_consumption_usage_value is not null
group by meter_id, calendar_year
order by total_bidirectional_out desc;

Return format

What’s the return format and an explanation of each returned field?

SITE_ID (or METER_ID)	CALENDAR_YEAR	TOTAL_BIDIRECTIONAL_OUT	UNIT_OF_MEASURE
1EE20EC7…	2023	342.0000000	kwh
1EE20EC7…	2024	565.8760000	kwh