Use Official Exchange Rates in Snowflake

1Create a network rule

Allow outbound traffic to the API host:

CREATE OR REPLACE NETWORK RULE ger_api_rule
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('api.globalexchangerates.org');

2Store your API key as a secret

CREATE OR REPLACE SECRET ger_api_key
  TYPE = GENERIC_STRING
  SECRET_STRING = 'YOUR_API_KEY';

3Create the external access integration

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION ger_integration
  ALLOWED_NETWORK_RULES = (ger_api_rule)
  ALLOWED_AUTHENTICATION_SECRETS = (ger_api_key)
  ENABLED = TRUE;

4Create a Python UDF

This function calls the API using your secret and returns the response as a VARIANT. It takes one parameter, provider: the official source to pull from (for example ECB; the available source codes are listed on the coverage page and through the /providers endpoint). Each source publishes its rates against its own currency, so no base currency is passed. The raise_for_status() call surfaces an authentication or quota error instead of returning a confusing non-JSON result:

CREATE OR REPLACE FUNCTION get_latest_rates(provider STRING)
  RETURNS VARIANT
  LANGUAGE PYTHON
  RUNTIME_VERSION = 3.11
  HANDLER = 'main'
  EXTERNAL_ACCESS_INTEGRATIONS = (ger_integration)
  SECRETS = ('key' = ger_api_key)
  PACKAGES = ('requests')
AS $$
import _snowflake
import requests

def main(provider):
    key = _snowflake.get_generic_secret_string('key')
    resp = requests.get(
        "https://api.globalexchangerates.org/v1/latest",
        params={"subscription-key": key, "provider": provider},
    )
    resp.raise_for_status()
    return resp.json()
$$;

5Query the rates

Call the function, then flatten the result into one row per currency. Pass the provider, for example the rates published by the European Central Bank:

SELECT key AS currency, value::float AS rate
FROM TABLE(FLATTEN(input => get_latest_rates('ECB'):exchangeRates));

Call the API once, not once per row. If you reference the function directly in a query that runs across a table, Snowflake may invoke it for every row, which consumes your API quota and your Snowflake compute. Fetch the rates once, materialize them into a table (next section), and join your data against that table.

Keeping the rates up to date

Materialize the rates into a table with a scheduled TASK, so downstream models always read the latest official figures and the API is called once per day rather than once per query. First create the target table:

CREATE OR REPLACE TABLE official_rates (
  currency  STRING,
  rate      FLOAT,
  provider  STRING,
  loaded_at TIMESTAMP_NTZ
);

Then schedule the refresh. This example loads the European Central Bank’s rates every business day at 16:00 UTC, shortly after they are published. INSERT OVERWRITE keeps only the latest set; use INSERT INTO instead if you want to retain history:

CREATE OR REPLACE TASK refresh_official_rates
  WAREHOUSE = my_wh
  SCHEDULE  = 'USING CRON 0 16 * * MON-FRI UTC'
AS
INSERT OVERWRITE INTO official_rates
SELECT key AS currency,
       value::float AS rate,
       'ECB' AS provider,
       CURRENT_TIMESTAMP()
FROM TABLE(FLATTEN(input => get_latest_rates('ECB'):exchangeRates));

ALTER TASK refresh_official_rates RESUME;

For date-specific values, add a /historical function the same way.

Join the rates with your data

With the rates in a table, converting your own figures is a single join. Each source publishes against its own currency (EUR for the European Central Bank), and the rate column holds the units of each currency per one unit of that base, so an amount held in a given currency is brought back to the base by dividing it by the rate. For example, to restate invoice amounts in the base currency:

SELECT i.invoice_id,
       i.amount,
       i.currency,
       r.rate,
       i.amount / r.rate AS amount_in_base
FROM invoices i
JOIN official_rates r
  ON i.currency = r.currency;

Conclusions

With an external access integration and a small UDF, official central bank exchange rates are queryable directly in Snowflake, ready to join with your own data.

If you don’t have an API key yet, you can get one on the get-started page, with official rates from central banks and tax authorities. The full endpoint reference is in the API documentation.