How to import exchange rates in your Data Warehouse. BigQuery vs Snowflake

Have you ever wondered how to integrate openexchangerates.org into your Data Warehouse ?

openexchangerates.org is a consistent, reliable and affordable exchange rate data and currency conversion which provides daily exchange rates in JSON format.

In this tutorial, we'll walk through setting up our own curency exchange rates pipeline to Data Warehouse . Our example will compare how it works in BigQuery and Snowflake .

We will see what it takes to handle openexchangerates.org JSON data, how to UNNEST and/or FLATTEN in order to represent in columnar view ready to be used in your tables .

Openexchangerates data

For example, I use Lambda Function to pull exchange rates on a daily basis and save it in my S3 bucket. This is how currency exchange rates look after you hit Openexchangerates API.

./your-google-project-12345.json

{
"disclaimer": "Usage subject to terms: https://openexchangerates.org/terms",
"license": "https://openexchangerates.org/license",
"timestamp": 1574046000,
"base": "USD",
"rates": {
    "AED": 3.6732,
    "AFN": 78.146055,
    "ALL": 111.19253,
    "AMD": 477.235224,
    ... ,
    "ZMW": 13.870346,
    "ZWL": 322.000001
}
}
                                

What I need to have in my Data Warehouse is this:

Let's do it!

So how easy is it to UNNEST / FLATTEN this exchange rates data in BigQuery or Snowflake.

BigQuery

After I uploaded currency echange rates data into BigQuery table I had it as a string :

Now we need to do something with this JSON. If we only could have openexchangerates.org data as a JSON with outer array brackets we could easily use UNNEST function.

"rates": [
    {
    "AED": 3.6732,
    "AFN": 78.146055,
    ... 
    }
]
                                

Unfortunatelly, this is not how it looks like in our case so we'll have to do someothing else.

I couldn't come up with anything easier rather than parsing a "rates" part of our JSON with currency exchange rates data using regex.

CREATE OR REPLACE VIEW `myproject.production.exchange_rates_v`
OPTIONS(
    friendly_name="exchange_rates_v",
    description="a view that represents myproject.production.exchange_rates in columnar view where date >= current_date() - 1",
    labels=[("exchange_rates", "production")]
)
AS

WITH object AS (
    SELECT  
        JSON_EXTRACT(src, '$.rates') as rates
    FROM `myproject.production.exchange_rates` er
    WHERE DATE(_PARTITIONTIME) = current_date() 
        AND 
        JSON_EXTRACT_SCALAR(src, '$.base') = 'USD'
)

, data as (
    SELECT 
        "USD" AS base_currency,
        REGEXP_EXTRACT_ALL(rates, r'"[^"]+":\d+\.?\d*') AS pair
    FROM object
)
, splits as (
    SELECT 
        base_currency, 
        pair, 
        SPLIT(pair, ':') AS positions 
    FROM data 
    CROSS JOIN UNNEST (pair) AS pair
)
SELECT 
    base_currency, pair,  positions[offset(0)] AS rate_currency,  positions[offset(1)] AS rate
FROM splits  
;
                                

Pretty simple right?

Not really. First I had to use regex. Yeah, it is powerful, widely applicable. Sometimes intimidating.

Only after that I was able to use UNNEST function to create the view I need.

Now let's see what it would be if we had to deal with this JSON data in Snowflake.

Snowflake

This is how my currency exchange rates look in Snowflake. Again we load it as a simple string which represents JSON:

Now let's parse it.

SELECT DISTINCT 
        'USD' as base_currency
        , CAST(ts AS DATE) AS exchange_dt
        , path AS currency_code
        , value AS exchange_rate
FROM (
    SELECT *
    FROM myproject.landing.exchange_rates_cpy
    ,lateral flatten( input => src:rates )
    )
WHERE parse_json($2):base = 'USD'
                                

So here we just used LATERAL FLATTEN which really makes things much easier.

Result:

Great! Now we know how to turn nested JSON currency exchange rates into columnar view ready to use in your data warehouse solution.

Thanks for reading

Please leave a comment if you'd like to know how to set up a Lamba/Cloud function to pull exchange rates from openexchangerates.org.