Advanced: the relational model

open_ef.factors_flat, the view every factor page, export and API row is read from, is built from four core tables: publisher, release, emission_factor and emission_factor_value. The other tables give those four a shared vocabulary for boundaries, places, classifications, quality and suppliers, and keep the publisher's original line so every number can be traced back. You need this page when you join those tables yourself, or when you want to see what the view flattened; The model at a glance reads the same data as the factor page and the view serve it.

The four core tables#

emission_factor is the identity. emission_factor_value holds every number attached to it across releases, years and GWP characterizations. A factor can point at a parent factor through parent_id, which is how a total and its components, its contribution stages or its nested boundaries hang together (see Parents, children, double counting).

A published slug never stops resolving. When a factor is renamed, when two are merged, or when a mis-keyed lineage is repaired, the old slug stays in emission_factor_alias with a reason of renamed, merged or key_fix. Identifiers and stability covers the rules.

The full model in one picture#

The spine across the top is the four core tables. Everything below emission_factor annotates it: those tables say what a factor covers, where it applies, what it is called and whose it is, and none of them changes its identity. Behind the value sit the publisher's own line and the quality scheme its rating is on.

The reference columns behind each line, for when you write the join yourself:

Supporting tables#

system_boundary: what a factor includes#

Publishers describe life-cycle coverage in their own words and at their own precision. system_boundary turns those descriptions into one closed list of codes, such as:

• combustion: direct emissions from burning a fuel, normally Scope 1 for the buyer;
• upstream_fuel: extraction, refining and delivery before combustion, normally Scope 3 category 3;
• cradle_to_gate: raw-material extraction through production;
• cradle_to_consumer: a product through consumer preparation;
• transport: a contribution stage explaining part of a parent total.

Each code carries three groupings. role says whether a number on it may be reported alone (whole, part, credit, unknown); preset is the reader-facing coverage group the boundary filter offers; family records which taxonomy the code was defined in (lifecycle, energy_stage, asset_stage, contribution, unknown). An AGRIBALYSE transport stage and a transport-service factor both say "transport", but only the first has boundary_role = 'part', so you can exclude stages without reading labels. System boundaries explains all three.

Fit judgement: does this factor suit your line?#

Five tables answer what a factor's name cannot: what its boundary really counts, where its activity sits in a classification, and what the publisher's quality rating is worth. They are annotations: improving one never changes a factor's identity or its slug.

boundary_scope_term and system_boundary_scope. A name like cradle_to_gate does not say whether capital equipment is inside. boundary_scope_term is the closed list of 21 such questions (raw_materials, upstream_fuel, capital_goods, end_of_life and the rest), each tagged with a stage. system_boundary_scope answers them per boundary with a disposition of in, out or varies. For cradle_to_gate, raw_materials, manufacturing and packaging are in, capital_goods varies, and use_phase and end_of_life are out.

Read those answers with three rules:

• They are defaults. A factor overrides them in its own boundary_profile, and factors_flat.boundary_scope already serves the merged answer.
• The table is sparse on purpose. An absent term means not established, never excluded.
• varies means the answer depends on the publisher, so only the factor's boundary_profile settles it.

boundary_scope is a JSON object keyed by term code, so one term is read by name with ->> and a whole answer is matched with containment (@>). The value is in, out, varies, or NULL for a term nobody established. Do not answer the question by joining system_boundary_scope on boundary_code instead: that returns the boundary's defaults and misses the factor's own override.

SELECT f.system_boundary,
       sbs.disposition                         AS boundary_default,
       f.boundary_scope ->> 'recycling_credit' AS effective,
       f.boundary_scope @> '{"capital_goods":"out"}'::jsonb AS excludes_capital_goods
FROM open_ef.factors_flat f
LEFT JOIN open_ef.system_boundary_scope sbs
       ON sbs.boundary_code = f.system_boundary
      AND sbs.term_code = 'recycling_credit'
WHERE f.library = 'okobaudat'
  AND f.slug = 'ef-okobaudat-2201_eps_rigid_foam-de-l-epd_total-1be8a0b7'
  AND f.is_default_indicator
  AND f.is_latest_activity_year

Here the factor's own boundary_profile settles what the boundary leaves as varies, and a join on the defaults alone would have answered varies and hidden that. How factors are cleaned counts the factors in each library that carry a profile of their own.

activity_classification and classification_scheme. The open_ef axis reaches the view as four columns, a code and a label at each of two levels: category_code with category, and sub_category_code with sub_category. The code is the stable half, frozen when the leaf is minted and listed in the code lists and in categories.xlsx; the label is display text and may be translated, so key a saved report or a stored filter on the code. The axis says what a factor measures, never which life-cycle stage it covers (how factors are cleaned). activity_classification keeps the whole tree behind those columns: one row per factor, scheme and code, with level, parent_code, the native label, and an origin of source (the publisher stated it) or mapped (open_ef assigned it). classification_scheme registers each scheme with its authority, version and is_standard flag (classification-schemes.xlsx lists them all).

Watch out. In SQL, key on category_code = 'food_agriculture'. A code passed to the category column matches nothing and raises no error: an empty result that reads as a coverage gap rather than as a typo. The search page's cat parameter takes the label (Food and agriculture) instead, because it filters that label column.

is_standard is the join rule. A standard code means the same thing in every library, so you can join on it. A publisher's own tree only means something inside that library. The revision is part of the scheme code for the same reason: hs_2017 and hs_2022 are not interchangeable.

SELECT ac.scheme, cs.is_standard, ac.level, ac.code, ac.label, ac.parent_code, ac.origin
FROM open_ef.factors_flat f
JOIN open_ef.activity_classification ac ON ac.factor_id = f.factor_id
JOIN open_ef.classification_scheme cs ON cs.code = ac.scheme
WHERE f.library = 'useeio'
  AND f.slug = 'ef-useeio-cheese_manufacturing-us-usd-cradle_to_gate-9c6d0a5a'
  AND f.is_default_indicator
  AND f.is_latest_activity_year
ORDER BY ac.scheme, ac.level

Those same five rows are already on the view, as classifications: a JSON array of objects, one per node, ordered by scheme, then level, then code. Every element has the same five fields, whoever the scheme belongs to, so a publisher's own tree and open_ef's category axis are read the same way.

{"scheme": "naics_2017", "code": "311513", "label": "Cheese Manufacturing", "level": 6, "parent_code": "31"}

jsonb_array_elements reads a factor's code for a given scheme without a join:

SELECT n ->> 'scheme'      AS scheme,
       n ->> 'code'        AS code,
       n ->> 'label'       AS label,
       n ->> 'parent_code' AS parent_code
FROM open_ef.factors_flat f
CROSS JOIN LATERAL jsonb_array_elements(f.classifications) AS n
WHERE f.library = 'useeio'
  AND f.slug = 'ef-useeio-cheese_manufacturing-us-usd-cradle_to_gate-9c6d0a5a'
  AND f.is_default_indicator
  AND f.is_latest_activity_year
  AND n ->> 'scheme' = 'naics_2017'
  AND (n ->> 'level')::int = 6

Which of the two you use follows the direction of the query. Reading the codes of a factor you already have, go to classifications: the row is in hand and the join is done. Selecting factors by a code, go to activity_classification, where (scheme, code) is indexed and the jsonb is not.

The schemes each of the public libraries carries. waste_stream is an open_ef tree rather than a publisher's, carried by the libraries that fan a waste material out by treatment route, and it is not a published standard either.

data_quality_scheme. One publisher's rating of 3.2 is not another's. The value's data_quality_rating_native points at its scheme through data_quality_scheme_native, and the scheme gives scale_min, scale_max, lower_is_better, the publisher's caution_threshold and the axes the rating aggregates. The model refuses a rating without a scheme. ADEME's ademe_dqr runs 1 to 5, lower is better, caution from 3, over six axes. To compare quality across libraries use data_quality_normalized instead (see data quality).

Rolling up by a classification code#

"Every factor under NACE section D" is a join on activity_classification, not a prefix match on the leaf code. The ancestor rung is a row of its own, so you match it directly.

SELECT ac.code, ac.level, ac.label, ac.origin, count(DISTINCT f.factor_id) AS factors
FROM open_ef.factors_flat f
JOIN open_ef.activity_classification ac ON ac.factor_id = f.factor_id
WHERE f.library = 'exiobase'
  AND ac.scheme = 'nace_rev1_1'
  AND ac.code IN ('D', '15')
  AND f.is_default_indicator
  AND f.is_latest_activity_year
  AND f.is_top_parent
GROUP BY ac.code, ac.level, ac.label, ac.origin
ORDER BY ac.level

A prefix match could not have found the section at all: NACE writes it as a letter, and D is no prefix of 15. Both rungs read origin = 'mapped' because EXIOBASE ships neither of them: its products carry a code of its own (p15.t), and reading those two digits as the NACE Rev. 1.1 division, then the section letter above it, is open_ef's. The labels are Eurostat's own published NACE names, which is why these mapped rungs carry one.

Nothing enforces that chain. It is there because the EXIOBASE map emits both rungs, and a scheme whose ancestry cannot be read off the code is free to ship only the depth it has. The chain is enforced for one family of schemes: where every level is a strict digit prefix of the level below (HS 2/4/6, CN, CPC), the leaf code already states its chapter and its heading, so an adapter holding one emits the whole parented chain and a gating check refuses the load when a rung is missing. Those truncated rungs carry origin = 'mapped' and, unlike the NACE rungs above, no label: the truncation is ours, and only the scheme itself may name a code it did not ship. None of the libraries these examples are drawn from carries such a scheme, so the enforced shape is one you can read about here but not run.

Why. If one library stored the chain and another only the leaf, the same code would answer a roll-up completely in the first and not at all in the second, and neither library's rows would look wrong on their own. That is what makes the derivable case an invariant rather than a convention.

Everywhere else the depth is whatever the source and its map together establish, and three gaps are correct rather than missing:

• a published crosswalk instead of a prefix rule: bea_2017 rolls its Detail commodities up to Summary through BEA's own table, so no BEA node carries a parent_code and a roll-up on a Summary code returns nothing;
• a letter section that is not part of the numeric code (isic_rev4, nace_rev1_1): present when the source states it, or when open_ef can derive it from something the source does ship, as above; absent when neither holds;
• a source that publishes only the leaf depth, such as a bare naics_2017 level 6.

Only a parent_code naming a row that is not there is a defect.

A derived total inherits a scheme only where its children are unanimous. A minted parent is not a new activity, so it may not claim one child's specificity: the fold keeps a scheme only where every child carries the identical nodes. open_ef_category survives that on every derived total, because the parts of a total measure the same thing. A publisher's own tree often does not. DESNZ files a fuel's combustion child under one activity heading and its well-to-tank child under another, so the total inherits neither.

SELECT f.is_derived,
       count(DISTINCT f.factor_id) AS factors,
       count(DISTINCT f.factor_id) FILTER (WHERE ac.scheme = 'open_ef_category') AS keep_open_ef_category,
       count(DISTINCT f.factor_id) FILTER (WHERE ac.scheme = 'desnz_activity') AS keep_desnz_activity
FROM open_ef.factors_flat f
LEFT JOIN open_ef.activity_classification ac ON ac.factor_id = f.factor_id
WHERE f.library = 'desnz'
  AND f.is_default_indicator
  AND f.is_latest_activity_year
GROUP BY f.is_derived
ORDER BY f.is_derived

Watch out. Every aggregate needs WHERE is_top_parent (sum a decomposition). Combine it with a filter on a publisher's own scheme and a whole fuel drops out with no error: the total carries no node, and its children are not top parents. Roll up on open_ef_category or on a scheme marked is_standard, or count the derived totals your filter keeps before you trust the total.

One last shape rule. open_ef_category cannot fan a factor out, because a unique index allows it one node per level per factor. Every other scheme may carry several codes at one level, so count DISTINCT factor_id rather than rows, as the queries above do.

geography: one region system at every grain#

geography holds one place or region per row, and region_code points at it: FR, FR-34 (Hérault), EUROPE, GLOBAL, EU27, EXIOBASE_ROW_EUROPE (Europe without the countries EXIOBASE models separately). parent_code builds the hierarchy, so a factor tagged FR-34 can answer a query for France or Europe by walking up FR-34 → FR → EUROPE → GLOBAL. Both tables are one download, geography.xlsx.

geography_member lists explicit members of a group. EU27 contains its member countries, and a rest-of-world complement lists the countries it leaves out with role = 'excluded'.

geography_resolution records why a publisher's raw label became a code: one row per publisher and raw text, with resolved_by (rule, gazetteer, llm or human) and the evidence.

text_term and emission_factor_text: labels in several languages#

text_term stores each unique string once per language. emission_factor_text links a factor to its terms and says which field the term fills, in which lang, and whether its origin is source, translated or generated. The AGRIBALYSE artichoke factor carries:

The publisher's own French and English wording is kept with an origin of source, and the German line is marked translated, which is how a reader tells a publisher's label from one we added. Search can still run on a normalized label in each language.

Counterparty: which supplier a factor describes#

Most factors describe a generic activity and name nobody. Some describe one named counterparty: an electricity retailer's tariff, the owner of an environmental product declaration. supplier_id points at it and is NULL on the generic majority; the view serves supplier_code, supplier_name and has_supplier.

supplier has one row per company, shared across publishers, with a frozen code minted from the resolved name. The supplier is an annotation, never identity: a legal-name change does not re-key a factor. The name fold keeps legal-form suffixes on purpose, because a wrong merge silently gives one company another's emissions, while a missed merge stays fixable. MITECO's ENDESA ENERGÍA S.A.U. and ENDESA ENERGÍA, S.A. are two supplier rows for that reason.

supplier_resolution is the twin of geography_resolution: each raw handle a source shipped, its raw_kind (name, or an id such as an ÖKOBAUDAT contact UUID), and the supplier_id it resolved to. A respelled supplier gets a new row pointing at the same supplier_id, so no factor is re-keyed.

source_row: the untouched archive#

source_row stores each line of a publisher's file exactly as retrieved: the artifact, the row_index, every original field in raw, a content_hash and an ingest_status. Lines that were deliberately not imported are archived too, with a skip_reason: an all-zero structural EXIOBASE cell, an archived ADEME record, a unit that cannot be converted safely. source_row_id on each value links back to it. Rows not imported shows how to list them.

From a number back to its source#

Every value keeps what the publisher sent:

• the publisher's own fields on the value: activity_raw, region_raw, unit_raw, source_native_id and a provenance record source_record;
• the archived line through source_row_id: which file, which line, every original field;
• unit_conversion_factor, so value_co2e_native / unit_conversion_factor gives back the number in the published unit;
• *_origin columns that say whether the publisher stated a field or open_ef assigned it.

Take the combustion part of ADEME's butane value for 2018:

SELECT f.value_co2e_native, v.unit_conversion_factor,
  round((f.value_co2e_native / v.unit_conversion_factor)::numeric, 2) AS published_value,
  v.unit_raw, v.source_native_id, sr.artifact, sr.row_index, sr.ingest_status
FROM open_ef.factors_flat f
JOIN open_ef.emission_factor_value v ON v.id = f.value_id
JOIN open_ef.source_row sr ON sr.id = f.source_row_id
WHERE f.library = 'ademe'
  AND f.slug = 'ef-ademe-butane_maritime_included-europe-kwh-combustion-896083c5'
  AND f.is_default_indicator
  AND (f.applies_from_year IS NULL OR f.applies_from_year <= 2018)
  AND (f.applies_to_year IS NULL OR f.applies_to_year >= 2018)

ADEME published 63.8 kg CO2e/GJ. open_ef stores it per kWh: 63.8 × 0.0036 = 0.22968. Add sr.raw to the select list to read the original line. Trace a value to its source walks a full trace.

Raw, source, native, origin: what the suffixes mean#

Column names carry one of four suffixes or prefixes. Each answers a different question, so you can use harmonized data without losing what the publisher sent.

On the AGRIBALYSE artichoke factor, region_raw is NULL, region_origin is assigned and the view's region_is_publisher_default is true: you know FR is the library's default, not a fact the row stated.

Next#

• The model at a glance: the same data as the factor page and the view show it.
• Identifiers and stability: every identifier, how it is built and what keeps it stable.
• Trace a value to its source: the full trace as a recipe.
• Reference: every table and code list, generated from the schema.