Parents, children, double counting

Many publishers split a total into parts: a fuel into combustion and upstream, a food product into farm, transport and retail, a building product into EN 15804 modules. Each part is a real factor you can select, and each one says which total it belongs to. Two rules keep you safe: in any aggregate over many factors, keep only is_top_parent rows (with the default value filters beside them); and add parts only when they are the direct sums_to_parent children of one parent, never together with that parent.

How a decomposition is stored#

Everything lives on the child factor.

parent_relation takes three values:

None of the libraries these docs use for examples uses contained_by_parent. Other libraries in factor search do: a publisher that prints a narrower envelope inside a wider one (the combustion part of a well-to-use total, say) is loaded that way so the nested figure is never mistaken for a partition.

SELECT f.library, coalesce(f.parent_relation, '(top)') AS parent_relation, f.boundary_family, count(*) AS factors
FROM open_ef.factors_flat f
WHERE f.library IN ('ademe','agribalyse','desnz','okobaudat')
  AND f.is_default_indicator AND f.is_latest_activity_year
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3

AIB, EPA, EXIOBASE, MITECO, Open CEDA and USEEIO publish no decompositions: every factor is top-level.

Where a publisher prints the parts but no total, open_ef sums one and flags it is_derived: 491 of DESNZ's 1,132 top-level factors and 3,912 of ÖKOBAUDAT's 4,189 are totals open_ef computed.

SELECT f.library, count(*) AS top_level, count(*) FILTER (WHERE f.is_derived) AS derived
FROM open_ef.factors_flat f
WHERE f.library IN ('desnz','okobaudat')
  AND f.is_top_parent AND f.is_default_indicator AND f.is_latest_activity_year
GROUP BY 1
ORDER BY 1

A fuel: two parts of one total#

DESNZ prints diesel's combustion and its upstream (well-to-tank) figure, but no total. open_ef sums the two into a well_to_use parent and marks it is_derived:

SELECT system_boundary, parent_relation, is_top_parent, is_derived, value_co2e_native
FROM open_ef.factors_flat
WHERE library = 'desnz'
  AND root_slug = 'ef-desnz-liquid_fuels_diesel_100-gb-l-well_to_wheel-99d9d558'
  AND is_default_indicator
  AND is_latest_activity_year
ORDER BY is_top_parent DESC, system_boundary

2.66155 + 0.62409 = 3.28564. The parts exist because standards draw the line differently: the GHG Protocol reports combustion in Scope 1 and upstream in Scope 3 category 3, apart; ISO 14083 wants the sum. is_top_parent AND NOT is_derived gives the first view, and is_top_parent AND system_boundary = 'well_to_use' the second. Check that a decomposition sums to its parent runs the same check on ADEME's butane, where the publisher prints all three numbers.

Note. The slug still says well_to_wheel: the boundary code was renamed after the slug was minted, and a slug never changes. Read the boundary from system_boundary, not from the slug. Identifiers and stability explains why.

A food product: a chain of gates and stages#

AGRIBALYSE publishes each food product's footprint at the consumer and its contribution by life-cycle stage. open_ef stores it as a chain: each gate is the previous gate plus one stage.

SELECT f.system_boundary, f.boundary_family, f.parent_relation,
  p.system_boundary AS parent_boundary, f.value_co2e_native
FROM open_ef.factors_flat f
LEFT JOIN open_ef.emission_factor p ON p.id = f.parent_id
WHERE f.library = 'agribalyse'
  AND f.root_slug = 'ef-agribalyse-artichoke_globe_raw-fr-kg-cradle_to_consumer-7718cba3'
  AND f.is_default_indicator
  AND f.is_latest_activity_year
ORDER BY f.is_top_parent DESC, f.value_co2e_native DESC, f.system_boundary

Every parent is the sum of its direct children: cradle_to_consumer 2.56 is cradle_to_store_gate 1.266166 plus consumption 1.29, within the publisher's rounding. Here is what the other sums give:

SELECT round((sum(value_co2e_native) FILTER (WHERE is_top_parent))::numeric, 6) AS top_parent,
  round(sum(value_co2e_native)::numeric, 6) AS every_row,
  round((sum(value_co2e_native) FILTER (WHERE boundary_family = 'contribution'))::numeric, 6) AS contributions,
  round((sum(value_co2e_native) FILTER (WHERE boundary_family = 'lifecycle' AND NOT is_top_parent))::numeric, 6) AS gates
FROM open_ef.factors_flat
WHERE library = 'agribalyse'
  AND root_slug = 'ef-agribalyse-artichoke_globe_raw-fr-kg-cradle_to_consumer-7718cba3'
  AND is_default_indicator
  AND is_latest_activity_year

Adding every row of the decomposition gives 9.44, 3.7 times the real footprint. The six stages add up to 2.556166, the total of 2.560000 within the publisher's rounding, because in this chain they are its finest partition. The five intermediate gates add up to nothing meaningful: each already contains the ones below it.

Watch out. A stage such as transport carries a real number, 0.596 kg CO2e per kg here, and it matches a query for "artichoke". Returned in place of the product's total, it under-reports by three quarters. Use a contribution row only when you want that stage on purpose.

A building product: modules and module D#

An ÖKOBAUDAT declaration is loaded as one top-level factor per product, with its EN 15804 modules below. Where the publisher prints no whole-life total, open_ef sums one; module D sits beside it.

SELECT p.system_boundary AS parent_boundary, c.parent_relation,
  string_agg(c.system_boundary, ', ' ORDER BY c.system_boundary) AS children,
  round(sum(c.value_co2e_native)::numeric, 5) AS children_sum,
  round(max(pf.value_co2e_native)::numeric, 5) AS parent_value
FROM open_ef.factors_flat c
JOIN open_ef.emission_factor p ON p.id = c.parent_id
JOIN open_ef.factors_flat pf ON pf.factor_id = p.id AND pf.is_default_indicator AND pf.is_latest_activity_year
WHERE c.library = 'okobaudat'
  AND c.root_slug = 'ef-okobaudat-brick_slips-de-m2-cradle_to_grave_embodied-29abe7f2'
  AND c.is_default_indicator
  AND c.is_latest_activity_year
GROUP BY 1, 2
ORDER BY 1, 2

The whole-life total is the cradle-to-gate modules, transport and installation (A4, A5) and end of life; end of life is itself C1 to C4. Module D, a credit of -0.0448 kg CO2e per m², is reported beside the total and never folded into it. System boundaries explains the modules.

The is_default_indicator on those modules does a second job: where the EPD prints several EN 15804 scenarios for a module, it elects one of them, so each row above carries a single scenario rather than a blend of the alternatives (EPD scenarios and other qualifiers).

Two filters, not the same#

Neither filter is enough on its own.

is_top_parent chooses one factor per decomposition, not one row per factor. A factor keeps a row for every GWP characterization in every activity year, so an aggregate needs the two default filters beside it:

SELECT count(DISTINCT factor_id) AS top_level_factors,
  count(*) AS rows_under_is_top_parent,
  count(*) / count(DISTINCT factor_id) AS rows_per_factor,
  count(*) FILTER (WHERE is_default_indicator AND is_latest_activity_year) AS rows_with_the_default_filters
FROM open_ef.factors_flat
WHERE library = 'exiobase'
  AND is_top_parent

A sum over is_top_parent alone counts each EXIOBASE sector 77 times. The query above omits the two conditions so that the count is visible; every worked example earlier on this page carries is_default_indicator AND is_latest_activity_year, and so should any aggregate of your own. Choosing a value: the axes explains what the extra rows are.

boundary_role = 'whole' is not a double-counting guard either. On the artichoke above it keeps the five intermediate gates, and an aggregate over them counts the same farm emissions five times: they are each a whole answer, at five different gates. It says whether one number stands alone, not whether two of them overlap. For totals, reach for is_top_parent, and use both together.

Do not reach for boundary_family <> 'contribution', which is the filter this page used to teach. boundary_family records which taxonomy a boundary code comes from, so that filter keeps A1-A3 and combustion, parts of a total that nobody reports alone, while dropping module D, whose 3,706 ÖKOBAUDAT rows are the reported_beside_parent contributions in the census above, and dropping a top-level factor whose own boundary happens to be a contribution code. boundary_role answers the question that filter was reached for.

Related#

• Check that a decomposition sums to its parent
• System boundaries
• Guarantees you can rely on
• Choosing a value: the axes