Connect to factor search over SQL
Numbers checked against the live data on
The SQL on these pages runs against the open_ef schema, and getting a copy of it to run against is arranged rather than self-service. This page says what to ask for, what arrives with it, and how to tell whether the copy you are querying still matches what the site publishes.
Three ways to take the data#
| What you take | What it is for |
|---|---|
| A library file | One whole library, as a workbook or a CSV |
| An export of a filtered slice | The rows behind one search, as a spreadsheet |
The open_ef model in a database |
Joins, audits, and everything the guides on this site do |
The first two are self-service and documented already: Download the open data and Export to a spreadsheet. What each of them asks of you, an account or nothing at all, is one table on What is free, and what needs an account. The third row is what this page is about.
None of the three is the matching agent. Uploading your own purchase lines and having them matched is a separate surface with its own key, the /v1 endpoints in the API reference.
Ask for the data#
Direct SQL is not self-service, and there is no standing endpoint to point a client at. Write to hello@open-climate.ai and say which libraries you need, how often your copy has to move, and where you want the data to land.
What is on offer is a sync into a database you run: the same open_ef model, pushed into a schema you control as editions are published, with the licence columns on every row. It costs nothing. The Open data page carries the same offer under Get the free sync, and open-climate.ai/pricing is where the price of zero is stated and kept current. If the factors have to live somewhere else, say so in the same email; what you get is settled in the reply rather than on this page.
Either way the guides on this site run unchanged, because what lands is the open_ef model they are written against.
What arrives with the credential#
A database you run, you already know how to reach. If instead you are issued a login to something we run, the reply is where its details live, and these are the things to have in hand before you try to connect. Ask for any that are missing rather than guessing at them.
- Host, port and database name. Your client needs outbound TCP to that port, which for PostgreSQL is usually 5432 and is worth checking against your egress rules before you blame a password.
- The role and its password. Read-only, and one schema.
- The TLS mode to use. Whether the certificate is one your client can verify is what decides between
sslmode=requireandverify-caorverify-full.requireencrypts the session but checks nothing about who answered, so a policy that needs the check needs a certificate it can chain. A connection rejected before any password is tried is usually the TLS mode or the firewall, not the password. - Which objects the role may read. A hand-issued grant is a list, and it is not the same list as the documented surface below.
- How often the copy is refreshed, which is what Your copy is a copy below is about.
psql "postgresql://<role>:<password>@<host>:5432/<database>?sslmode=<mode>"
postgresql+psycopg2://<role>:<password>@<host>:5432/<database>?sslmode=<mode>
What you may read#
Whichever shape you get, the guides read one schema, open_ef, and mostly one surface inside it. What is in the contract is the list the docs stand behind: the view open_ef.factors_flat that nearly every query on this site starts from, every table with a page in the reference, and license_terms, price_index and fx_annual named beside them.
That list is a promise about what is documented and what will keep its shape. It is not a statement about privileges, and it is not a claim that nothing else is ever queried: the rest of the schema is ours rather than yours, free to change shape without notice, and a few pages here do query it to show something about the model rather than about a factor. Guarantees you can rely on reads the load ledger and the update sweep that way, to date an edition and to say whether a newer one exists.
Your grant is the separate thing, and it comes with the credential. This query prints the one you actually hold.
Show the SQL
SELECT table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'open_ef'
AND grantee = current_user
AND privilege_type = 'SELECT'
ORDER BY table_name
No result table for that one: what it lists is your grant, not ours. It reads privileges granted to your own role by name, so if yours inherits them from a group role, ask about one object at a time with has_table_privilege(current_user, 'open_ef.factors_flat', 'SELECT') instead.
A permission denied is then answerable rather than mysterious. On an object with no page in the reference, it is the grant doing its job. On factors_flat, on a table with a reference page, or on a query published on this site, it is a fault worth reporting to hello@open-climate.ai: a grant narrower than the documented surface is our mistake, not a rule you were meant to infer. Nothing about accounts, uploads or mapping jobs lives in this schema at all, so those are not a grant question.
Your copy is a copy#
What you query is not the database the site serves from. It is refreshed on its own cadence, so it can sit behind the website, and between refreshes a figure you compute can disagree with the same factor's page. The site is the one to believe, and the copy's own as-of date is what you state beside anything you publish from it.
One query settles both halves of that, and it is already written: How fresh is this returns, per library, the edition, the publisher's date, the last write and last_ingest_version, the load-run counter that stamped the row. Run it against your copy, then against the same libraries on the site, and the two answers are how far behind you are. An older edition is fixed by a refresh, not by a different query, and What a refresh changes for you says what moves when one lands. When two surfaces disagree says which stamp each surface hands back, and ends on this exact case.
Two things that look like a freshness check and are not.
max(updated_at)does not date your copy's newest change. It is re-stamped when a row's serving flags flip without its value changing, and it stays put when a pass recomputesvalue_co2e_normalizedfrom the GWP tables, or when a re-seed of the price and exchange-rate series moves every monetary value. SoWHERE updated_at > <your last check>both misses real movement and returns rows that did not move. The blind spots are written out under How fresh is this, with the fix beside them: diffvalue_co2e_normalizedandprice_index_ratioas well as the stamp.- A matching edition label is not a matching copy. A correction to a value already loaded, and a rebuild after a price re-seed, both happen under an edition that keeps its name. What a source_key does not pin is the list of what can move under one.
Watch out. A copy taken before
factors_flatexisted answersrelation "open_ef.factors_flat" does not exist, and an older one may still carry a view namedfactor_current. That view was replaced rather than renamed, so its columns are not the ones the guides select. Neither is a query to work around: ask for a refresh.
The licence travels with the rows#
Holding a connection changes nothing about what you may do with a value. Every row of factors_flat carries license_tier, license_code, attribution_text and license_url, so the answer rides on the row instead of sitting in a separate agreement. Filter before you publish anything: Keep only rows you may redistribute is the query, Licences is the reasoning, and The data terms covers open-climate.ai's own layer over the publisher's.
A copyleft library puts anything you publish from it under the same licence, and one of the libraries these pages use in their examples is on that tier: EXIOBASE. That is a property of the data, not of how you took it. The four tiers is where a tier is defined.
Your first query#
Quickstart: SQL walks one ADEME factor from finding the row to a CO2e total, and ends on the four rules every SQL page here assumes: one row per factor, never sum a parent with its parts, no bare value_co2e, and the licence travels with the number.
Underneath all four sits an assumption it does not spell out. factors_flat is the everyday surface: the tables behind it are for auditing a number back to its source row, not for reads you make every day. The factors_flat column reference documents every column it serves, and Pin a factor value to a release is how you keep a number stable while your copy moves under it.