How can I get currency exchange rates in Snowflake? (Historic and updated daily)

Question

I see the Snowflake Marketplace has multiple providers of Exchange Rates. Is there one free and updated daily?

Answer 1

A good source of data is Knoema's table "knoema_economy_data_atlas.economy.exratescc2018".

To get it into your Snowflake account, find it in the Snowflake Marketplace first:

• https://app.snowflake.com/marketplace/listing/GZSTZ491VXQ

Then you'll be ready to write queries like this:

select "Currency", "Units", "Currency Description", "Date", "Value"
from knoema_economy_data_atlas.economy.exratescc2018
where "Indicator Name"= 'Close'
and "Currency Exchange" = 'Real-time FX'
and "Frequency" = 'D'
and "Date" = '2022-08-08'

With that query you'll notice that there are 182 exchange rates listed by August 8th 2022 (from the source 'Real-time FX').

Note that:

• The dataset has multiple currencies, including cryptocoins (like Bitcoin) and commodities (like oil, gas, coin, silver).
• The source "Real-time FX" provides the most popular exchange rates, but there are other sources with hundreds more of crypto coins.
• You can get the Open/Close/Min/Max exchange rate for each day.
• You can find daily, weekly, and monthly exchange rates too.
• The above query filters for some sensible defaults.

Let's check history: There's a historic moment when 1 USD = 1 Euro = 1000 CLP (Chile), and in this chart you can see that it was a pretty singular moment.

You can also see that Knoema brought the Euro/USD exchange rate to Snowflake since 1975, and the Chilean/USD exchange rate since 1990. The surprising part is the Euro was launched on 1999 - I'm assuming the previous rates come from the historical ECU.

The query to produce above chart:

select "Currency", "Units", "Currency Description", "Date"
    , IFF("Currency"='USD/CLP', "Value"/1000, "Value") "Value"
from knoema_economy_data_atlas.economy.exratescc2018
where "Indicator Name"= 'High'
and "Currency Exchange" = 'Real-time FX'
and "Frequency" = 'M'
and "Currency" in ('USD/CLP', 'USD/EUR')