Quickstart: SQL

You turn 1,000 kWh of butane burned in 2018 into 267.48 kg CO2e with one row of open_ef.factors_flat. This page walks that row: how you find it, how you pick the value for your year, what it is made of, and which columns you read before you trust it.

Before you start#

Every query on this page runs against the open_ef schema and reads one view, open_ef.factors_flat. It is the everyday surface: one row per value, with the factor's activity, unit, region, boundary, year window, GWP basis and licence already joined in. The factors_flat column reference documents every column it serves, and the rest of the reference documents the tables behind it.

You do not need the tables to follow along. Each query below is copy-paste ready, once you have somewhere to paste it: Connect to factor search over SQL says how a copy of the open_ef schema is arranged and what arrives with it.

Step 1: find the factor#

Filter on what you know: the library, the activity, the unit. Add the default-row flags so each factor answers once.

SELECT slug, activity_full, region_code, unit_code, system_boundary, value_co2e_native
FROM open_ef.factors_flat
WHERE library = 'ademe'
  AND activity_full = 'Butane - maritime included'
  AND unit_code = 'kwh'
  AND is_top_parent
  AND is_default_indicator
  AND is_latest_activity_year
ORDER BY region_code

Two factors match, one per region. You take the European one. Its slug is the identifier you store: it never changes, and it is how you come back to this factor later. In factor search, the same search is butane in ADEME.

well_to_use is the whole chain for a fuel: extracting and delivering it, then burning it. is_top_parent keeps you on that total and away from its parts.

Step 2: pick the value for your year#

One factor can carry several values over time. This one has two rows: ADEME's older value, which applies up to 2018, and a newer one from 2019 on.

SELECT slug, applies_from_year, applies_to_year, reference_year, is_latest_activity_year, value_co2e_native
FROM open_ef.factors_flat
WHERE slug = 'ef-ademe-butane_maritime_included-europe-kwh-well_to_use-53be0cf1'
ORDER BY applies_from_year NULLS FIRST

A NULL bound is an open end, not a zero. For an activity in 2018, keep the row whose window covers 2018 and multiply:

SELECT
  1000 AS quantity_kwh,
  value_co2e_native AS kg_co2e_per_kwh,
  round(1000 * value_co2e_native::numeric, 2) AS kg_co2e,
  unit_code, applies_from_year, applies_to_year
FROM open_ef.factors_flat
WHERE slug = 'ef-ademe-butane_maritime_included-europe-kwh-well_to_use-53be0cf1'
  AND is_default_indicator
  AND (applies_from_year IS NULL OR applies_from_year <= 2018)
  AND (applies_to_year IS NULL OR applies_to_year >= 2018)

1,000 kWh × 0.26748 kg CO2e/kWh = 267.48 kg CO2e. For a current-year activity you would keep the is_latest_activity_year row instead, and the same 1,000 kWh gives 269.00 kg CO2e.

Note. The unit is part of the value. unit_code is kwh, so your quantity must be in kWh. The same fuel has separate factors per kg and per litre, with their own values.

Step 3: see what the total is made of#

The total has two children: burning the butane (combustion) and producing and delivering it (upstream_fuel). Every row names its parentless root in root_slug, so one filter finds the whole family.

SELECT slug, system_boundary, parent_relation, value_co2e_native,
  sum(value_co2e_native::numeric) OVER () AS children_total
FROM open_ef.factors_flat
WHERE root_slug = 'ef-ademe-butane_maritime_included-europe-kwh-well_to_use-53be0cf1'
  AND NOT is_top_parent
  AND is_default_indicator
  AND (applies_from_year IS NULL OR applies_from_year <= 2018)
  AND (applies_to_year IS NULL OR applies_to_year >= 2018)
ORDER BY system_boundary

0.22968 + 0.03780 = 0.26748, the parent's value. sums_to_parent says exactly that. Report either the parent or its children, never both: adding all three rows counts the butane twice. Parents, children, double counting covers the rules.

Step 4: read the columns that qualify the value#

A value is only usable with its context. Read these on the row you picked.

SELECT unit_code, system_boundary, region_code, applies_from_year, applies_to_year,
  gwp_method_native, gwp_normalization_basis, value_co2e_normalized,
  license_tier, license_code, attribution_text
FROM open_ef.factors_flat
WHERE slug = 'ef-ademe-butane_maritime_included-europe-kwh-well_to_use-53be0cf1'
  AND is_default_indicator
  AND is_latest_activity_year

The licence columns are on every row, so you never join to find out whether you may use a value.

The four rules#

1. Pick one row per factor. Filter is_default_indicator AND is_latest_activity_year for today's value, or the year-window predicate for a past year.
2. Never sum parents and children together. Filter is_top_parent in any aggregate.
3. There is no bare value_co2e. Read value_co2e_native for the publisher's number, value_co2e_normalized to compare libraries on AR6.
4. Carry the licence. license_tier, license_code and attribution_text travel with the number.

Making a query fast#

factors_flat is a view, not a table. Every query on it runs the joins behind it over a materialized index of the served rows, and two of its columns are assembled per row it returns. Four habits keep that cheap.

Start from an identifier when you have one. A filter on slug, source_key or value_key is an index lookup, and nothing else on the view comes close. root_slug looks like the same kind of column and is not: it is produced by a join, so the database cannot use it to find the family first and reads the served set instead. Step 3 above does exactly that, which is fine once and wasteful in a loop. Add library = 'ademe' beside it and the planner has somewhere to start.

Keep the row-selection flags in the WHERE clause. The indexes under the served rows are mostly partial, built only over the rows where is_default_indicator is true, and the flags cut the join fan-out before the rest of the query sees it. So they are not only about getting one row per factor. What they do not do is make an ORDER BY free: a sort over the whole of factor search is still a sort, so pair one with a filter.

Prefer a filter the query can start from. library, category, region_code, unit_code and system_boundary each have an index behind them, so the planner narrows on one before it joins the rest. A filter on how the value was made, such as data_type, has no index behind it: the database reads the value rows and applies it afterwards. One of each makes a good query; the second on its own makes a slow one.

Project the jsonb columns only when you read them. classifications is assembled per row, and boundary_scope merges the boundary's own defaults with the factor's profile per row, so SELECT * pays for both on every row it returns. For the same reason, do not filter on them: a predicate on classifications has to build the column for every candidate row before it can test it. Filter on the flat columns carrying the same facts, category_code and sub_category_code, and read the jsonb on the rows you keep.

While you are exploring, keep a LIMIT on the query. Before you schedule one to run on a timer, read its EXPLAIN.

Next#

• The model at a glance
• Emissions for a quantity
• Connect a client to the data