The factors_flat view
The serving view every SQL query starts from: one row per factor, methodology and activity year.
factors_flat is a view, not a table. Defined in derived/sql/factors_flat.sql, it joins the model into one row per served number and drops the problematic quality tier, so a query against it never has to know the joins. This page is generated from that file's own column list, so it cannot drift from what is served.
It serves 110 columns, grouped below the way a reader uses them. The view filters on no flag of its own, so several rows per factor are normal: WHERE is_default_indicator AND is_latest_activity_year is the one-row-per-factor shape, and any aggregate also needs WHERE is_top_parent.
Fields#
Identity#
Identifiers answering different questions. slug names the factor and survives every restatement, so it is the one to cite; the last two group rows rather than name them.
| field | type | meaning |
|---|---|---|
library |
text | The publisher's short code, and the column every example filters on. |
slug |
text | The factor's public identifier, frozen when the factor was minted. |
source_key |
text | The published value's identity, hashed from the publisher, the release and the value's own identity parts, never from the number. A new release or a re-election serves a different source_key; a correction re-ingested into the same release keeps it and rewrites the row in place, which emission_factor_value.content_hash shows. |
value_key |
text | The row's identity, which is the number a consumer actually read. On a physical row it is source_key; on a monetary row it is source_key, an underscore and the activity year, because one value is served once per year at a different price level each time. |
root_slug |
text | The slug at the top of this factor's parent chain, its own when it has no parent: what a served list groups a part under. |
activity_src_id |
text | The publisher's key for the activity alone, carrying no place, no unit and no boundary. Every region, unit, boundary and supplier variant of one activity shares it, so grouping on it ALONE lists that activity once, and region_code, unit_code, system_boundary and supplier_id are the axes inside the family: pin one only to narrow a family to rows that compare. A publisher that keys each published row on its own identifier instead leaves every group a group of one. It names nothing on its own: a re-key can move it, and slug cannot. |
Join keys#
For joining inside the database and nothing else. A UUID is not stable across a rebuild of the catalogue, so it must never be published, cited by an inventory line or stored by a mirror.
| field | type | meaning |
|---|---|---|
factor_id |
uuid | The factor, and the way through to its aliases, texts and classifications. |
value_id |
uuid | The published value. The join for anything this view leaves off. |
publisher_id |
uuid | The publisher. |
release_id |
uuid | The release the value was published in. |
source_row_id |
uuid | The verbatim source line it was read from. |
supplier_id |
uuid | The named counterparty, NULL where the factor names none. |
parent_id |
uuid | The decomposition parent, NULL on a top-level factor. |
What the factor describes#
The prose a reader matches on. Both are in the publisher's own language.
| field | type | meaning |
|---|---|---|
activity_full |
text | The full activity name: the main activity, plus the detail where the publisher prints one. |
description |
text | The publisher's own prose about the factor, where the source has any. |
Category#
One tree over every library, so two publishers' own trees line up. A node says what the factor measures, never its lifecycle stage and never the buyer's sector. Every served factor sits on a branch; the leaf is assigned only where the source tells that branch's leaves apart, so the two sub_category columns are NULL together on a factor mapped at its branch alone. Every node is listed in the code lists.
| field | type | meaning |
|---|---|---|
category |
text | The level-1 branch, as its English label. The API and explorer filters take this label, not the code below. |
category_code |
text | The code behind category, and the join key back to activity_classification on scheme open_ef_category. Never NULL on a served row: a source value that maps to no node halts the load rather than landing here, so coverage of level 1 is total by construction. |
sub_category |
text | The level-2 leaf, as its English label. Filters take this label too. NULL where the factor is mapped at its branch alone. |
sub_category_code |
text | The code behind sub_category, NULL on the same rows. Read it as deliberate coarseness, a source that does not distinguish between that branch's leaves, never as an unmapped or unclassified factor. |
classifications |
jsonb | Every classification the factor carries, the publisher's own tree included, as objects of scheme, code, label, level and parent_code. An empty array when it carries none. |
Place#
A region is not always a claim, and the second column says when it is not.
| field | type | meaning |
|---|---|---|
region_code |
text | Where the factor applies, at whatever grain the publisher modelled: a country, a subdivision, a modelled aggregate. |
region_is_publisher_default |
boolean | True when the region was assigned at ingest by a rule about the publication rather than by anything the row printed. Read that as weak evidence about place, never as a scope error. |
Unit#
The value is per exactly one of these.
| field | type | meaning |
|---|---|---|
unit_code |
text | The canonical unit the value is expressed per. |
unit_family |
text | The family the unit belongs to, such as mass, energy, volume or monetary: what decides whether two units can be bridged at all. |
Boundary#
What the number counts, and the axis that decides whether two factors may be compared. The codes are in the code lists.
| field | type | meaning |
|---|---|---|
system_boundary |
text | What this factor counts: see the system boundary vocabulary. The single most important field for deciding whether two numbers may be compared. |
boundary_family |
text | The taxonomy the boundary code belongs to: a life-cycle boundary, an energy stage, an asset-stage module, a contribution to one of them. It says where the code comes from, not how much it covers, and it is not the column to guard an aggregate with. |
boundary_preset |
text | The reader-facing group of the boundary code: how much of the life cycle the number covers (cradle to gate, full life cycle, energy full chain, one slice to add up). Exactly one per code, and the value the boundary filter groups by. |
boundary_role |
text | Whether the number may be reported alone: whole stands by itself, part is one slice to add to its siblings, credit is a signed credit never summed into a total, unknown states no boundary. Guard an aggregate with boundary_role = 'whole'. |
boundary_scope |
jsonb | The effective includes and excludes for this factor, most specific layer first: the factor's own profile, the publisher's answer for the factor's sub-category, then its category, then the whole library, then the boundary code's defaults. A term that does not apply to what the factor measures is left out. not_stated means nobody says, and varies is never served. A missing key means the term does not apply or nothing answers it, never excluded. |
boundary_scope_source |
jsonb | Which layer answered each boundary_scope key: factor, factor_deduction, library_sub_category, library_category, library or boundary. Same keys as boundary_scope, always. factor_deduction is the one answer that is not a publisher's claim: no layer stated the term and open-climate.ai read the mark off the factor's own value, its activity name or the EN 15804 modules it sums, under a published rule. |
boundary_profile |
jsonb | This factor's own answers alone, before the boundary defaults are merged in. Read boundary_scope unless you need to know what the factor itself established. Terms only, like boundary_scope: where open-climate.ai deduced a mark, boundary_scope_source says so with factor_deduction. |
Decomposition#
A factor can be served with its parts beside it, so every aggregate needs WHERE is_top_parent. See decomposition.
| field | type | meaning |
|---|---|---|
parent_relation |
text | How this factor relates to its parent: an addend of the parent's total, a nested envelope, or grouped under it and never summed. |
is_top_parent |
boolean | parent_id IS NULL. Children are served for drill-down only, so summing them double-counts their parents. |
is_derived |
boolean | True on a headline this project computed rather than transcribed: a sum of an exact cover of the publisher's own slices, because that publisher prints the parts and not the total; a part the publisher never printed, which open-climate.ai estimated (is_estimated); or a total that includes such a part. It does not pick a reporting convention: the slices are reported apart under one standard and added under another, so read a summed total's parts (parent_relation) or the total itself (is_top_parent). False leaves out everything this project computed, estimated parts included; to leave out only the estimates, filter on is_estimated. |
is_estimated |
boolean | True where open-climate.ai estimated a part the publisher never printed (the loss or fuel-chain leg of an electricity mix, or the well-to-tank leg of a fuel) from published figures for the region, a model of it, a broader region or another country's supply chain, and on a total that includes such a part. Every estimated factor is also derived; is_derived alone does not tell a summed total from an estimate. |
Supplier and provenance#
Whether the source names who the number is for.
| field | type | meaning |
|---|---|---|
supplier_code |
text | The named counterparty's short code, NULL where there is none. |
supplier_name |
text | The named counterparty's name. |
is_epd |
boolean | True on (a module of) an Environmental Product Declaration: a third-party verified declaration published for one product or product group. Set by the adapter at ingest. |
has_supplier |
boolean | supplier_id IS NOT NULL. A declaration implies a named counterparty; a supplier-specific tariff has one without being a declaration. |
The two headline measures#
There is no column called value_co2e. Every value is served twice: _native is the number the publisher printed, _normalized is that number moved to the one target vintage wherever it could be. Sum across libraries only over _normalized, and only where gwp_normalization_basis is not unconverted. A pending row (value_status) carries neither: filter it out before you sum.
| field | type | meaning |
|---|---|---|
co2e_basis |
text | Which inclusion convention the headline counts, so a reader can tell which CO2e it is before comparing: fossil only, biogenic excluded, a net total including the biogenic and land-use pools, or an input-output allocated total. |
co2e_origin |
text | Where the headline came from: source is the publisher's own CO2e, computed_from_gases a sum of the gases the publisher printed, summed_from_children a total this project added up from the factor's parts (is_estimated says whether one of them is an estimate), estimated_from_reference a part the publisher never printed, which this project estimated by a stated rule from published figures, most often the publisher's own value times a reference ratio, differenced_from_parent a part the publisher never printed but whose total and every other part it did, so this project subtracted, apportioned_from_parent a part the publisher never printed but whose total and share of it it did, so this project multiplied. |
value_co2e_native |
double precision | The headline number, per one canonical unit, under the publisher's own global-warming-potential vintage. On all but a labelled few it is exactly as published: the publisher's printed total, or its printed per-gas vector summed under the stated GWP set, never a sum of the components below. The exceptions are a factor whose publisher prints the parts and not the whole, where open_ef summed an exact cover of that factor's own slices, and a part the publisher never printed, which open-climate.ai estimated (is_estimated), with any total that includes one, and a part left over when the publisher's printed parts are subtracted from its printed total. is_derived is true on exactly those rows, and it does not choose a reporting convention: under the GHG Protocol combustion and upstream fuel are reported apart, so read a summed total's parts (parent_relation sums_to_parent); under ISO 14083 they are added, so read the total (is_top_parent). An estimated part is a slice like a printed one, so is_derived = false leaves it out along with every summed total; to leave out only the estimates, filter on is_estimated. co2e_origin and is_estimated together tell the shapes apart: a summed total carries summed_from_children whether or not one of its parts is estimated. NULL on a pending row, whose value is withheld from every caller: not a zero. |
value_co2e_normalized |
double precision | The same headline, moved to the one target vintage so it can be added to another library's. Populated on every published row and NULL on a pending one (value_status): where the move is not exactly derivable the native number is copied through and labelled unconverted, so this is the only headline column a consumer needs to read. Sum it only across published rows where gwp_normalization_basis is not unconverted. |
value_status |
text | Whether the row carries the publisher's numbers (published) or only the factor, its release and everything that describes it (pending), its value withheld from every caller, entitled or not (ecoinvent today). On a pending row every value column is NULL: not a zero and not a gap. A published row always carries both headlines. The library coverage counts published rows only and pending ones apart; the browse values_total counts a pending row like a licence-withheld value, and it counts as a factor. |
The gas legs#
Each headline decomposed, on the same vintage and the same price base, so the legs add up to their own headline wherever the publisher printed a complete vector. Never mix the two sets in one sum. They are sparse: a NULL leg means the publisher did not print that gas, never zero. On a pending row every leg is NULL: its value is withheld from every caller.
| field | type | meaning |
|---|---|---|
value_co2_native |
double precision | CO2 component, native vintage. |
value_co2_fossil_native |
double precision | Fossil CO2 component, native vintage. |
value_ch4_native |
double precision | Methane component, native vintage. |
value_ch4_biogenic_native |
double precision | Biogenic methane component, native vintage. |
value_n2o_native |
double precision | Nitrous oxide component, native vintage. |
value_hfcs_native |
double precision | HFC basket component, native vintage. |
value_pfcs_native |
double precision | PFC basket component, native vintage. |
value_sf6_native |
double precision | Sulphur hexafluoride component, native vintage. |
value_nf3_native |
double precision | Nitrogen trifluoride component, native vintage. |
value_other_native |
double precision | Remaining greenhouse gases the publisher groups together, native vintage. |
value_co2_biogenic_native |
double precision | Biogenic CO2 component, native vintage. |
value_co2_luluc_native |
double precision | Land-use and land-use-change CO2 component, native vintage. |
value_removals_native |
double precision | Removals the publisher accounts for, native vintage. Negative by convention. |
value_co2_normalized |
double precision | CO2 component, moved to the target vintage. |
value_co2_fossil_normalized |
double precision | Fossil CO2 component, moved to the target vintage. |
value_ch4_normalized |
double precision | Methane component, moved to the target vintage. |
value_ch4_biogenic_normalized |
double precision | Biogenic methane component, moved to the target vintage. |
value_n2o_normalized |
double precision | Nitrous oxide component, moved to the target vintage. |
value_hfcs_normalized |
double precision | HFC basket component, moved to the target vintage. |
value_pfcs_normalized |
double precision | PFC basket component, moved to the target vintage. |
value_sf6_normalized |
double precision | Sulphur hexafluoride component, moved to the target vintage. |
value_nf3_normalized |
double precision | Nitrogen trifluoride component, moved to the target vintage. |
value_other_normalized |
double precision | Remaining greenhouse gases the publisher groups together, moved to the target vintage. |
value_co2_biogenic_normalized |
double precision | Biogenic CO2 component, moved to the target vintage. |
value_co2_luluc_normalized |
double precision | Land-use and land-use-change CO2 component, moved to the target vintage. |
value_removals_normalized |
double precision | Removals the publisher accounts for, moved to the target vintage. Negative by convention. |
Beside the headline#
A measure the publisher prints apart from the headline, on the same vintage and price base, and never one of its legs: do not add it to the headline or to the gas legs. NULL where the publisher prints no such part, and on a pending row.
| field | type | meaning |
|---|---|---|
value_non_kyoto_native |
double precision | The warming of the gases outside the Kyoto basket (CFCs, HCFCs, halons: the Montreal Protocol gases) that the publisher prints apart from a refrigerant's Kyoto headline, native vintage. A GHG Protocol inventory reports it outside its scope totals. A headline of 0 with data_quality_reason_normalized = outside_kyoto_basket_zero is a blend whose gases all sit outside the basket, its warming stated here. |
value_non_kyoto_normalized |
double precision | The same part, moved to the target vintage beside value_co2e_normalized. |
The FLAG split#
The same headline cut a second way, by activity rather than by gas, as the SBTi FLAG guidance asks: land-use change, land management and everything outside the land sector, with removals on value_removals_* (MOD-0113). Never add these to the gas legs, which cut the same total. Each leg is NULL on its own, meaning not stated, never zero; the three add up, with removals, to the carbon-origin pool only where all three are stated. value_qualifiers carries flag_split (published, or derived_from_gases where open-climate.ai assigned the publisher's own emission terms to the legs) exactly when a leg is stated, beside luc_method, luc_amortisation_years and luc_discounting. Only farm, food and forest factors carry a split: never an EPD or a spend factor.
| field | type | meaning |
|---|---|---|
value_flag_luc_native |
double precision | Land-use change emissions, net as published, on the method in luc_method (direct or statistical; indirect LUC never counts), amortised over luc_amortisation_years by luc_discounting (linear, as SBTi FLAG requires, or equal). Native vintage. |
value_flag_luc_normalized |
double precision | The land-use change leg, moved to the target vintage beside value_co2e_normalized. NULL on a recharacterized row: a split printed on an older vintage cannot be re-weighted leg by leg, so read the _native leg there. |
value_flag_land_management_native |
double precision | Land management emissions: farming and forestry on land that stays in its use, such as enteric methane, manure and soil N2O. Native vintage. |
value_flag_land_management_normalized |
double precision | The land management leg, moved to the target vintage beside value_co2e_normalized. NULL on a recharacterized row: a split printed on an older vintage cannot be re-weighted leg by leg, so read the _native leg there. |
value_non_flag_native |
double precision | Emissions outside the land sector, such as the fuel and the fertiliser manufacture behind a farm product. Native vintage. |
value_non_flag_normalized |
double precision | The non-FLAG leg, moved to the target vintage beside value_co2e_normalized. NULL on a recharacterized row: a split printed on an older vintage cannot be re-weighted leg by leg, so read the _native leg there. |
Global warming potentials#
Which vintage each measure is on, and whether the second one may be added to another library's. See GWP.
| field | type | meaning |
|---|---|---|
gwp_method_native |
text | The vintage the publisher printed the _native numbers on (on a pending row, the one its edition scores on), or none for a pure-CO2 value that no vintage can change. |
gwp_method_normalized |
text | The vintage the _normalized numbers are actually on: the target vintage wherever the row converted, and the publisher's own where it could not. |
gwp_horizon_years |
integer | The horizon both measures are on: 100 on every converted row, the publisher's own where the basis is unconverted. |
gwp_normalization_basis |
text | How the _normalized measure was obtained, and the one column that says whether it may be summed across libraries. native is a verbatim copy of a row already on the target vintage, invariant a value no vintage can change, recharacterized a genuine re-weighting, and unconverted a row that could not be moved and carries its native number under its native vintage. Everything but unconverted is comparable. A pending row reads unconverted and carries no number at all. |
Methodology#
The axes a publisher can print several answers along. The view filters on none of them: is_default_indicator marks the one the adapter elected and the siblings are still served.
| field | type | meaning |
|---|---|---|
calculation_approach |
text | Location based or market based, where the carrier publishes that duality. NULL where it does not. |
radiative_forcing |
text | Whether the value includes a radiative-forcing uplift, where the publisher prints both. NULL where the question does not apply. |
grid_mix |
text | The accounting mix refining the approach: production or consumption for location based, residual, supplier specific or green for market based. |
grid_mix_origin |
text | Whether grid_mix is the publisher's own (source) or assigned by this project (assigned: the green zero rule, which marks a generation-only zero as green). NULL exactly where grid_mix is. |
value_qualifiers |
jsonb | Discriminators belonging to one library family rather than to a cross-library axis, as a small bag of loader-validated keys. |
system_model |
text | The release's modelling approach, where it is recorded: consequential for a model of what a change in demand causes (marginal suppliers, marginal electricity, indirect land use change). NULL where none is recorded, which does not mean attributional. A consequential and an attributional footprint answer different questions and do not add up. |
Money and price level#
A monetary factor is served one row per year, each restated into that year's money; a physical factor is one row over its whole vintage window. See deflating spend factors.
| field | type | meaning |
|---|---|---|
data_type |
text | How specific the number is to the thing bought: specific is one product from one named plant, average a mean over several producers, generic a default standing in for a measurement. |
currency_year |
integer | The price year the published number was denominated in. NULL on every physical factor. |
price_basis |
text | Which price a monetary value is quoted at: producer, purchaser or basic. |
deflation_economy |
text | The economy whose price series restated this row. |
deflation_economy_hops |
integer | How far up the geography tree that series was found, 0 being the region's own. |
price_index_ratio |
double precision | What the published value was multiplied by to restate it into this row's year. Exactly 1.0 on every physical row, and on a monetary row read at its own currency_year. |
Years#
Three different years, none of them interchangeable. The view is served on the activity-year axis: pick a year with the range, or take is_latest_activity_year.
| field | type | meaning |
|---|---|---|
reference_year |
integer | The publisher's vintage: the year the number describes. Use it to judge how old a number is, and to pick one of a multi-year series. |
applies_from_year |
integer | The first activity year this row answers for. NULL-open, meaning it also answers for everything before itself. |
applies_to_year |
integer | The last activity year this row answers for. NULL-open, meaning nothing supersedes it yet. |
Row-selection flags#
The view filters on none of these, so several rows per factor are normal. WHERE is_default_indicator AND is_latest_activity_year is the one-row-per-factor shape; see choosing a value.
| field | type | meaning |
|---|---|---|
is_default_indicator |
boolean | The methodology election: of the values a factor carries for one release and reference year, exactly one is elected and the siblings are still served beside it. That is one value, not one row. The view fans the elected value out along the activity-year axis, so the flag reads true on every year-row of it: pair it with is_latest_activity_year, or with a year-window predicate on applies_from_year and applies_to_year, to land on one row. |
is_latest_activity_year |
boolean | Marks the row that is current: a physical row with no successor, a monetary row at the last year the price series reaches. |
is_route_default |
boolean | Marks the one row per waste stream that answers route unknown where a publisher fans a material out by treatment route and prints no such row. A projection, never a filter: every route row is still served, and a row outside any waste stream reads false. |
route_default_rule |
text | Why that row won: publisher_mix is the publisher's own average end-of-life row, highest_route the route with the highest headline, sole_route the only route there is. NULL outside a waste stream. |
Quality#
One ordinal axis across every library. The view already drops the problematic tier; see cleaning.
| field | type | meaning |
|---|---|---|
data_quality_normalized |
text | The comparable confidence tier, derived at ingest from whichever native quality signal the value carries. |
data_quality_reason_normalized |
text | Why the tier is what it is, or why a zero is a real zero. NULL where a native quality signal set the tier. |
Licence#
Licence and attribution ride with every row. Anything exporting, publishing or serving this view externally filters on license_tier, never on a hard-coded list of library codes.
| field | type | meaning |
|---|---|---|
license_tier |
text | The distribution gate: open and copyleft may leave the system, copyleft carrying ShareAlike, while restricted and prohibited may not. |
license_code |
text | The named licence the release is published under. Licence provenance is per release, because it varies by version and by artifact within one publisher. |
license_url |
text | Where that licence text lives. |
license_notice |
text | The publisher's own terms of use, verbatim, where the source states them in prose rather than by a named licence. NULL where the terms are the named licence. |
attribution_text |
text | The credit line the publisher requires, and nothing else: no terms, no note of ours. |
Pipeline bookkeeping#
Ours, not the publisher's. Useful for cache invalidation and for syncing what changed; neither says anything about the number itself.
| field | type | meaning |
|---|---|---|
last_ingest_version |
bigint | The ingest run that last wrote this value, so a mirror can sync with WHERE last_ingest_version > :last_seen and no diffing. |
updated_at |
timestamp with time zone | When we last wrote the row. Never when the publisher dated the number, which is reference_year, nor when the publisher shipped it, which is on the release. |
Related#
- The rules behind the flags: choosing a value and decomposition.
- Anything this view leaves off is reached through a link column, and there is one join per link: trace a value to its source line.
- Every closed code list a coded column can hold: code lists.
- The base tables behind the view: the other pages in this section.