The model at a glance

Everything factor search serves, the factor page, the exports, the read API and the SQL access, reads one view: open_ef.factors_flat. One row is one factor on one methodology for one year window, with the value as the publisher printed it, the same value on a common basis, its gases, its boundary, its place, its licence and the identifiers that lead back to the publisher's own line. This page reads that row twice: as the factor page lays it out, and as the view's columns. The relational model behind the view is an advanced page you need only when you join the tables yourself.

The worked example throughout is ADEME's anthracite, Anthracite, France, per kWh, well to use. How to read a factor page walks a different factor through the same blocks with screenshots.

Five ideas behind every row#

A factor and its values are two things. A factor is the activity, its place, its unit and its boundary: "anthracite, France, per kWh, well to use". Its values are the numbers the publisher gave it, one per methodology and year. The factor has one slug, which is the address of its page; each value has its own source_key. The view holds one row per value, so a factor with two years on one methodology takes two rows that share a slug.

One of those rows is the default. The factor page's headline, the export and the API's default answer all show the same row: the one the publisher marks as its indicator, on the latest year window. Two flags name it, is_default_indicator and is_latest_activity_year, and Choosing a value explains how the others are ranked.

Every value is served twice, native and normalised. The native value is the publisher's number on the publisher's methodology (GWP). The normalised value is the same measurement restated on AR6 100-year, when the gases allow it, so two libraries can be read side by side. The row says which of the two it did, and why, in gwp_normalization_basis. GWP methodologies has the rules.

A total and its parts are separate factors. ADEME publishes the anthracite total and, beside it, its combustion and its upstream fuel. Each is a factor with its own slug and its own rows; parent_id, root_slug and parent_relation tie them together, and is_top_parent marks the total. Every count and every sum needs is_top_parent, or it counts a stage twice. Decomposition covers what adds up and what does not.

Nothing is guessed, everything is recorded. A missing description stays NULL, a publisher's default region is flagged rather than replaced, a value the publisher never printed is marked is_derived. How factors are cleaned lists what changes between the file and the row, and Guarantees what never does.

App mode: the factor page, block by block#

Each block of the factor page reads a handful of columns. The third column of each table is what the anthracite page shows.

The top line#

The licence tier is the same for every row of a library; the notice and attribution the licence asks for sit in license_notice and attribution_text, ready to paste.

Title and chips#

The category is the one axis shared by every library; the publisher's own tree, here ADEME's "Combustibles, Fossiles, Solides, Charbons", is kept in classifications. The row also carries data_quality_normalized and data_quality_reason_normalized, served in the exports and the API but not shown on the page.

The value#

The CO2e basis says whether biogenic CO2 is in the total, which is the first thing to check before comparing two libraries.

The provenance line#

The year window is a range: a row with applies_to_year set has been superseded by a later one, and only the open-ended row is the latest.

Values by year and methodology#

The matrix has one cell per row of the view: methodologies across, year windows down, the default cell marked. Anthracite has one methodology, AR6, and two windows, up to 2014 and from 2015, so two cells. A library that restates every value on several methodologies fills a wider matrix; EXIOBASE's animal products, France has 7 methodologies by 11 year windows, 77 rows, and still one default.

By gas#

Each gas the publisher reported is a column pair, value_<gas>_native and value_<gas>_normalized: co2, co2_fossil, co2_biogenic, co2_luluc, ch4, ch4_biogenic, n2o, hfcs, pfcs, sf6, nf3, removals and other. Anthracite reports three: 0.366 fossil CO2, 0.0187 CH4 and 0.00147 N2O, all as CO2e per kWh. A gas the publisher did not report is NULL, not zero; gas legs says what the normalised value can and cannot do with that.

Decomposition, or group#

On a total, the page lists the parts and says whether they add up to it: parent_relation on each part is sums_to_parent, contained_by_parent or reported_beside_parent. On a part, the page shows the group it belongs to. In the row, root_slug names the total, parent_id the immediate parent, and is_top_parent is true only on the total. Anthracite's two parts sum to it, and the SQL mode below shows the numbers.

SQL mode: the same factor from the view#

The queries below run as written against the public database (connect); every column is described in the factors_flat reference. library comes first in every WHERE: it is the column the view is indexed on, and the one every example filters on.

The default row#

SELECT activity_full, region_code, unit_code, system_boundary,
  value_co2e_native, gwp_method_native, applies_from_year, applies_to_year
FROM open_ef.factors_flat
WHERE library = 'ademe'
  AND slug = 'ef-ademe-anthracite-fr-kwh-well_to_use-a2e28919'
  AND is_default_indicator AND is_latest_activity_year

Drop the two flags and the view returns every row of the factor.

Every row of the factor#

SELECT applies_from_year, applies_to_year, is_latest_activity_year::text AS is_latest_activity_year, value_co2e_native
FROM open_ef.factors_flat
WHERE library = 'ademe'
  AND slug = 'ef-ademe-anthracite-fr-kwh-well_to_use-a2e28919'
  AND is_default_indicator
ORDER BY applies_from_year NULLS FIRST

The matrix on the page is this result laid out as a grid. The wider case:

SELECT count(*) AS rows_served,
  count(DISTINCT f.applies_from_year) AS year_windows,
  count(DISTINCT (f.gwp_method_native, f.gwp_horizon_years, v.characterization_model)) AS methodologies,
  count(*) FILTER (WHERE f.is_default_indicator AND f.is_latest_activity_year) AS default_rows
FROM open_ef.factors_flat f
JOIN open_ef.emission_factor_value v ON v.id = f.value_id
WHERE f.library = 'exiobase'
  AND f.slug = 'ef-exiobase-animal_products_nec-fr-eur-cradle_to_gate-e2b36c2f'

Native and normalised#

Anthracite is already on AR6, so its two values are equal and gwp_normalization_basis says native. A DESNZ factor published on AR5 shows the restatement:

SELECT value_co2e_native, gwp_method_native,
  round(value_co2e_normalized::numeric, 5) AS value_co2e_normalized, gwp_method_normalized,
  gwp_normalization_basis, is_derived
FROM open_ef.factors_flat
WHERE library = 'desnz'
  AND slug = 'ef-desnz-cars_by_market_segment-gb-km-grid_supplied-9330d83b'
  AND is_default_indicator AND is_latest_activity_year

The basis takes four values. native: the publisher used AR6, nothing to do. recharacterized: the gases were reported, so the total was rebuilt on AR6 factors. invariant: the value carries no methodology (a CO2-only figure, for instance) and is served as is. unconverted: the publisher gave a total without its gases, so the native value is served on both sides and gwp_method_normalized keeps the native methodology. GWP methodologies gives the counts per library.

Parent and child#

SELECT slug, system_boundary, parent_relation, is_top_parent, value_co2e_native
FROM open_ef.factors_flat
WHERE library = 'ademe'
  AND root_slug = 'ef-ademe-anthracite-fr-kwh-well_to_use-a2e28919'
  AND is_default_indicator AND is_latest_activity_year
ORDER BY is_top_parent DESC, slug

The parts add to 0.3865 against a published 0.387: ADEME rounded the total, and the page reports the gap rather than hiding it. Summing the three rows would count the fuel twice, which is why every aggregate filters on is_top_parent. Sum a decomposition is the recipe.

Next#

• How factors are cleaned: what changes between the publisher's file and the row, and what is only flagged.
• Guarantees you can rely on: what never changes, with the query that checks each promise.
• Choosing a value: how the default row is ranked when a factor has several.
• Advanced: the relational model: the tables behind the view, for readers who join them.
• factors_flat reference: every column, generated from the schema.