open-climate.aiDocs

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 migration f7c3e8a52d91_factors_flat_v27_estimated_legs, 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 revision's own column list, so it cannot drift from what is served.

It serves 99 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. It names the exact number this row serves, so it changes the moment a different value is elected: a new release, a re-election, a corrected figure.
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: the boundary code's defaults, overridden by the factor's own profile. A missing key was not established, which never means excluded.
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.

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 by summing an exact cover of the publisher's own slices, because that publisher prints the parts and not the total. Filter on it to pick a reporting convention: the slices are reported apart under one standard and added under another.
is_estimated boolean True where open-climate.ai estimated the number from a regional statistic because the publisher never printed this leg (the well-to-tank or the loss leg of an electricity mix). 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.

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 publisher's slices, estimated_from_reference a leg this project estimated from a regional statistic.
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 exception is a factor whose publisher prints the parts and not the whole, where open_ef summed an exact cover of that factor's own slices. is_derived is true on exactly those rows, so filter on it to choose a reporting convention: under the GHG Protocol combustion and upstream fuel are reported apart, under ISO 14083 they are added. Which of the two transcribed shapes a published row came from is co2e_origin on emission_factor_value, one of the columns this view leaves off.
value_co2e_normalized double precision The same headline, moved to the one target vintage so it can be added to another library's. Always populated: 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 rows where gwp_normalization_basis is not unconverted.

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.

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.

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, 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.

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.

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.