Introduction
Next, lets create a pipeline that retrieves current exchange rates via a public API. We will use the free Frankfurter API. It provides both historical and recent exchange rates and does not require an API key for basic functionality.
What are we going to build?
- Python performs an HTTP GET request to the API.
- The API returns JSON data containing exchange rates.
- Python converts the JSON into a Spark DataFrame.
- We validate the data using an ODCS Data Contract.
- We write the data to a Delta table.
- A Databricks Job executes the pipeline using parameters.
We are building upon what we already have :
- Databricks series : Starting a new python project in Visual Studio Code (Part I)
- Databricks series : setting up a connection to Databricks from local IDE (Part II)
- Databricks series : Setting up a Databricks project in Visual Studio Code (part III)
- Databricks series : Deployment from Visual Studio Code (Part IV)
- Databricks series : Execute the job in Databricks (Part V)
- Databricks series : A configrable framework in Databricks (Part VI)
- Databricks series : Loading strategies in Databricks (Part VII)
- Databricks series : Data contracts in Databricks (Part VIII)
- Databricks series : Debugging Python code in Visual Studio Code with arguments (Part IX)
- Databricks series : ODCS Data contracts in Databricks (Part X)
Projectstructure
databricks-transfer/ │ ├── databricks.yml ├── resources/ │ └── transfer_job.yml │ ├── src/ │ ├── main.py │ └── transfer/ │ ├── config_loader.py │ ├── contract_validator.py │ ├── processor.py │ └── api_client.py <-- new │ ├── config/ │ └── entities.json │ ├── contracts/ │ ├── customers.yaml │ └── exchange_rates.yaml <-- new │ └── requirements.txt
Lets create the API client
https://api.frankfurter.app/latest?from=EUR&to=USD,GBP,CHF
The API returns JSON. We convert this into a Spark DataFrame with one record per exchange rate.
{
"amount":1.0,
"base":"EUR",
"date":"2026-11-16",
"rates":{
"CHF":0.9244,
"GBP":0.8345,
"USD":1.1014
}
}
New file: src/transfer/api_client.py
Next we need to create a new file api_client.py. Create this file with the following content:
import requests
from pyspark.sql import Row
def read_api_data(spark, config):
url = config["url"]
base_currency = config["base_currency"]
target_currencies = config["target_currencies"]
print(f"Calling API: {url}")
print(f"Base currency: {base_currency}")
print(f"Target currencies: {target_currencies}")
response = requests.get(
url,
params={
"from": base_currency,
"to": ",".join(target_currencies)
},
timeout=30
)
response.raise_for_status()
data = response.json()
exchange_date = data["date"]
rates = data["rates"]
rows = [
Row(
base_currency=base_currency,
target_currency=currency,
exchange_rate=float(rate),
exchange_date=exchange_date
)
for currency, rate in rates.items()
]
df = spark.createDataFrame(rows)
print(f"Retrieved {len(rows)} exchange rates")
return df
What is happening here?
- We read the API settings from the configuration.
- We perform an HTTP GET request.
- We check whether the API call was successful.
- We convert the JSON response into individual records.
- We return a Spark DataFrame.
Extending the configuration
{
"customers": {
"source": "/Volumes/main/demo/raw/customers.csv",
"target": "main.demo.customers",
"format": "csv",
"mode": "overwrite",
"contract": "contracts/customers.yaml"
},
"customers_update": {
"source": "/Volumes/main/demo/raw/customers_update.csv",
"target": "main.demo.customers",
"format": "csv",
"mode": "merge",
"merge_key": "customer_id",
"contract": "contracts/customers.yaml"
},
"exchange_rates": {
"source_type": "api",
"url": "https://api.frankfurter.app/latest",
"base_currency": "EUR",
"target_currencies": ["USD", "GBP", "CHF"],
"target": "main.demo.exchange_rates",
"mode": "append",
"contract": "contracts/exchange_rates.yaml"
}
}
Note: For the API, we use 'source_type' instead of 'format'. This allows our processor to distinguish between CSV and API.
Step 3 – Extending the processor
Open `src/transfer/processor.py` and replace the content:
from delta.tables import DeltaTable
from transfer.contract_validator import validate_dataframe
from transfer.api_client import read_api_data
def transfer_data(spark, config, contract=None):
target = config["target"]
mode = config["mode"]
source_type = config.get("source_type", "csv")
print(f"Source type: {source_type}")
print(f"Target table: {target}")
print(f"Loading mode: {mode}")
# Read everything as strings first
source_type = config.get("source_type", "csv")
if source_type == "csv":
source = config["source"]
file_format = config["format"]
df = (
spark.read
.format(file_format)
.option("header", "true")
.option("inferSchema", "false")
.load(source)
)
elif source_type == "api":
df = read_api_data(spark, config)
else:
raise ValueError(
f"Unsupported source type: {source_type}"
)
# Validate before writing
if contract is None:
raise ValueError(
"No data contract configured for this entity"
)
df = validate_dataframe(df, contract)
record_count = df.count()
print(f"Validated records: {record_count}")
# Apply loading strategy
if mode == "overwrite":
(
df.write
.format("delta")
.mode("overwrite")
.saveAsTable(target)
)
elif mode == "append":
(
df.write
.format("delta")
.mode("append")
.saveAsTable(target)
)
elif mode == "merge":
merge_key = config["merge_key"]
# Read primary keys from the ODCS contract
primary_keys = [
item["name"]
for item in contract["schema"][0]["properties"]
if item.get("primaryKey") is True
]
print(f"ODCS primary keys: {primary_keys}")
print(f"Configured merge key: {merge_key}")
if merge_key not in primary_keys:
raise ValueError(
f"Merge key {merge_key} is not defined "
f"as a contract primary key. Found: {primary_keys}"
)
if spark.catalog.tableExists(target):
delta_table = DeltaTable.forName(
spark, target
)
(
delta_table.alias("target")
.merge(
df.alias("source"),
f"target.{merge_key} = source.{merge_key}"
)
.whenMatchedUpdateAll()
.whenNotMatchedInsertAll()
.execute()
)
else:
(
df.write
.format("delta")
.mode("overwrite")
.saveAsTable(target)
)
else:
raise ValueError(
f"Unsupported loading mode: {mode}"
)
print(
f"Successfully processed {record_count} records"
)
As a result, the existing CSV pipelines continue to work.
Adding the ODCS contract
Create a new file 'contracts/exchange_rates.yaml' and insert the following code in the file.
apiVersion: v3.2.0
kind: DataContract
id: urn:datacontract:databricks:exchange-rates
name: exchange_rates
version: 1.0.0
status: active
description:
purpose: Currency exchange rates
usage: Used for financial reporting and analytics
limitations: Rates are published by the Frankfurter API
team:
name: Data Engineering
schema:
- name: exchange_rates
physicalName: main.demo.exchange_rates
physicalType: table
properties:
- name: base_currency
logicalType: string
physicalType: STRING
primaryKey: true
required: true
- name: target_currency
logicalType: string
physicalType: STRING
primaryKey: true
required: true
- name: exchange_rate
logicalType: number
physicalType: DOUBLE
required: true
- name: exchange_date
logicalType: date
physicalType: DATE
primaryKey: true
required: true
Expanding dependencies
Open requirements.txt and add requests:
PyYAML==6.0.2 requests==2.32.5
Since we are using Databricks Serverless, we also need to modify resources/transfer_job.yml. Add requests to the dependencies:
dependencies: - PyYAML==6.0.2 - requests==2.32.5
Running the Job
You can now use the same Databricks Job, but with a different parameter:
databricks bundle validate -t dev databricks bundle deploy -t dev databricks bundle run transfer_job -t dev --params entity=exchange_rates
Here is some of debug logging :
The expected table is main.demo.exchange_rates and we can check the data using:
SELECT * FROM main.demo.exchange_rates ORDER BY exchange_date, target_currency;
Resulting in :
Hennie










.png)






