«
»

dbt and the ECB f/x Rates – Part 1

2026-05-10, 17:47
This entry is Teil 1 von 3 in the series dbt - ECB FX Example

dbt - ECB FX Example

I have recently found the necessity to deal with dbt and Snowflake. As I learn best by practically doing something I decided to write a few practical use cases. Privately I will focus on Postgres SQL as I am not sure about Snowflake’s free tier. What is this dbt? dbt (data build tool) is a commandline tool based on Python that take care of the „T“ in „ETL“, the transformation of data in a data warehouse using SQL. dbt does not care about how you get the raw data into the (raw) DWH, this is not in its scope. dbt-core is available for free (Therefore I will focus on dbt-core, for production environments the paid tiers are most likely more suitable).

In this first example we will download the ECB fx rates from the European Central Bank and play a bit with them by transforming them and creating some data marts.

As mentioned I will use Postgres locally on my machine, so the installation of Postgres on my Windows machine was step 1. To deal with Postgres I can recommend HeidiSQL, does the trick.

Step 2 was the installation of dbt in Python. (Note that I had issues with Python 3.14, I downgraded to Python 3.12)

Besides dbt-core we need the postgres-Adapter dbt-postgres, so pip install dbt-core dbt-postgres helped. I usually do not need virtual environments, so I just installed it globally. We also need the Postgres library and lxml and requests for the ingestion of the XML file:

pip install requests psycopg2-binary lxml

As mentioned dbt does not care how you load the raw data, so with the help of some AI-tool I got the following Python Code to load the 90-days F/X rates file from the European Central Bank.

import requests
import xml.etree.ElementTree as ET
from datetime import datetime
import psycopg2
from psycopg2.extras import execute_values
 
ECB_URL = "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist-90d.xml"
 
DB_CONFIG = {
    "host": "localhost",
    "port": 5432,
    "dbname": "FX",
    "user": "postgres",
    "password": "<meingeheimespasswort>",
}
 
def fetch_ecb_fx():
    r = requests.get(ECB_URL, timeout=30)
    r.raise_for_status()
    root = ET.fromstring(r.content)
    ns = {
        "gesmes": "http://www.gesmes.org/xml/2002-08-01",
        "def": "http://www.ecb.int/vocabulary/2002-08-01/eurofxref",
    }
 
    rows = []
 
    # Find Cube with time attribute
    for cube in root.findall(".//def:Cube[@time]", ns):
        fx_date = cube.attrib["time"]
 
        for rate in cube.findall("def:Cube", ns):
            rows.append(
                (
                    fx_date,
                    rate.attrib["currency"],
                    float(rate.attrib["rate"]),
                )
            )
 
    return rows
 
 
def init_db(conn):
    with conn.cursor() as cur:
        cur.execute(
            """
            CREATE SCHEMA IF NOT EXISTS raw;
 
            CREATE TABLE IF NOT EXISTS raw.ecb_fx_rates (
                fx_date date NOT NULL,
                currency varchar(3) NOT NULL,
                rate numeric(20,10) NOT NULL,
                created_at timestamp default now(),
                PRIMARY KEY (fx_date, currency)
            );
            """
        )
    conn.commit()
 
 
def upsert_rates(conn, rows):
    sql = """
        INSERT INTO raw.ecb_fx_rates (fx_date, currency, rate)
        VALUES %s
        ON CONFLICT (fx_date, currency)
        DO UPDATE SET rate = EXCLUDED.rate;
    """
 
    with conn.cursor() as cur:
        execute_values(cur, sql, rows)
 
    conn.commit()
 
 
def main():
    rows = fetch_ecb_fx()
    print(f"Fetched {len(rows)} FX rates")
 
    conn = psycopg2.connect(**DB_CONFIG)
 
    try:
        init_db(conn)
        upsert_rates(conn, rows)
        print("Load complete")
    finally:
        conn.close()
 
 
if __name__ == "__main__":
    main()

When this code is run it should provide some output close to the following.


Fetched 1798 FX rates
Load complete

In the second part of this series we will take care of the dbt work.

dbt - ECB FX Example

dbt and the ECB f/x Rates – Part 2
Schlagwörter: , , , ,
Category: Allgemein