dinsdag 17 november 2026

Databricks series : reading an API (Part XI)

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?

  1. Python performs an HTTP GET request to the API.
  2. The API returns JSON data containing exchange rates.
  3. Python converts the JSON into a Spark DataFrame.
  4. We validate the data using an ODCS Data Contract.
  5. We write the data to a Delta table.
  6. A Databricks Job executes the pipeline using parameters.



Projectstructure  

We slightly change the project structure :

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

We use the Frankfurter API:

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?

  1. We read the API settings from the configuration.
  2. We perform an HTTP GET request.
  3. We check whether the API call was successful.
  4. We convert the JSON response into individual records.
  5. We return a Spark DataFrame.


Extending the configuration

Open config/entities.json and add a new entity : exchange_rates. Leave your existing customer configurations as they are.

{
  "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

maandag 16 november 2026

Databricks series : ODCS Data contracts in Databricks (Part X)

Introduction

In the previous blogpost I wrote about contracts. Now there is also an open standard available : Open Data Contract Standard (ODCS). An video on Youtube makes it a lot clearer. In this blogpost I'll investigate the data contracts according to the ODCS in more detail. 


In modern data environments, reliable data is essential. However, the transfer of data between producers and consumers often encounters issues due to unclear agreements or unexpected changes. 


An ODCS contract (based on the Open Data Contract Standard) provides a solution to this. It is a standardized, machine-readable data contract (typically drafted in YAML) that serves as a formal agreement between the team providing the data and the teams or systems consuming it. Rather than relying on disparate documentation, an ODCS contract establishes exactly what the consumer can expect, all in one central location. This includes details such as data structure (schema), quality requirements, service-level agreements (SLAs), ownership, and usage policies. Consequently, errors can be prevented early on, changes become easier to manage, and a shared standard is established for both human users and automated data pipelines.

We are building upon what we already have :


ODCS contracts

We are transitioning our existing Databricks solution to a formal ODCS (Open Data Contract Standard) version 3.2.0 contract.

We will retain the elements that are already functional:
  • The Databricks Job with the 'entity' parameter.
  • The CSV files stored in Unity Catalog Volumes.
  • The Python DataFrame validation.
  • The 'overwrite', 'append', and 'merge' loading strategies.
  • The validation check ensuring that mandatory fields cannot be empty.
We will replace our custom JSON-based contract with a YAML-based contract that adheres to the official ODCS structure.

The new project structure 

Let s change the project structure..

databricks-transfer/
├── databricks.yml
├── resources/
│   └── transfer_job.yml
├── src/
│   ├── main.py
│   └── transfer/
│       ├── config_loader.py
│       ├── contract_validator.py
│       └── processor.py
├── config/
│   └── entities.json
├── contracts/
│   └── customers.yaml
├── data/
│   ├── customers.csv
│   └── customers_update.csv
└── requirements.txt


From now on, we will use 'customers.yaml' instead of 'customers.json'.


2. Create the ODCS contract

First, we create a new file: contracts/customers.yaml and replace the content with:

apiVersion: v3.2.0
kind: DataContract

id: urn:datacontract:databricks:customers
name: customers
version: 1.0.0
status: active

description:
  purpose: Customer master data
  usage: Used by the Databricks data transfer pipeline
  limitations: Customer identifiers must be unique

team:
  name: Data Engineering

servers:
  - server: databricks_dev
    type: databricks
    description: Databricks development environment

schema:
  - name: customers
    physicalName: main.demo.customers
    physicalType: table
    description: Customer master data

    properties:
      - name: customer_id
        logicalType: string
        physicalType: STRING
        primaryKey: true
        required: true
        description: Unique customer identifier

      - name: first_name
        logicalType: string
        physicalType: STRING
        required: true
        description: Customer first name

      - name: last_name
        logicalType: string
        physicalType: STRING
        required: true
        description: Customer last name

      - name: country
        logicalType: string
        physicalType: STRING
        required: true
        description: Customer country


What is the meaning of the fields:




Modify config/entities.json

We do not need to change the source files or loading strategies. Only the reference to the contract changes. This is now the new content of the file: config/entities.json


{
  "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"
  }
}


Both entities therefore use the same ODCS contract. This makes sense: they both describe the same dataset, even though they employ different loading strategies.


Adding YAML support

Since we are now reading YAML, we need PyYAML. We add an entry in the file: requirements.txt


PyYAML==6.0.2


The requirements.txt file defines the Python dependencies your project needs to run. In other words, it tells Python which external libraries must be installed in addition to the standard Python libraries


Execute the requirements.txt to make sure the packages are installed :


pip install -r requirements.txt


Also ensure that the dependency is available in the Job's serverless Python environment. Change the file: resources/transfer_job.yml and add dependencies under `spec`, if not already present:


environments:
  - environment_key: default
    spec:
      environment_version: "2"
      dependencies:
        - PyYAML==6.0.2


Keep the rest of your existing Job configuration.


Why both? Because installing PyYAML locally does not automatically install it in the Databricks serverless environment. The idea is to keep your dependencies explicit and reproducible, so someone else can set up the same Python project without guessing which libraries are needed.


Loading the contract in main.py

We are now changing the JSON parser to a YAML parser. replace the entire content of the file src/main.py with :


import argparse
from pathlib import Path

import yaml
from pyspark.sql import SparkSession

from transfer.config_loader import load_entity_config
from transfer.processor import transfer_data


def get_project_root():
    current_dir = Path.cwd()

    for candidate in [current_dir, *current_dir.parents]:
        if (
            (candidate / "config" / "entities.json").exists()
            and (candidate / "contracts").exists()
        ):
            return candidate

    raise FileNotFoundError(
        f"Could not locate project root. Current directory: {current_dir}"
    )


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--entity", required=True)
    args = parser.parse_args()

    spark = SparkSession.builder.getOrCreate()

    project_root = get_project_root()

    config_path = project_root / "config" / "entities.json"
    config = load_entity_config(config_path, args.entity)

    contract_path = project_root / config["contract"]

    with open(contract_path, "r", encoding="utf-8") as file:
        contract = yaml.safe_load(file)

    transfer_data(spark, config, contract)


if __name__ == "__main__":
    main()


Here, we once again deliberately use our 'get_project_root()' function, which we modified earlier. This allows us to avoid the issue with '__file__'.


Adapting the validator for ODCS

This is the most significant change. The previous validator expected:


contract["columns"]


But ODCS uses:


contract["schema"][0]["properties"]


Replace the content of the file: src/transfer/contract_validator.py with 


from pyspark.sql.functions import col, trim
from functools import reduce

TYPE_MAPPING = {
    "string": "string",
    "integer": "int",
    "long": "bigint",
    "number": "double",
    "boolean": "boolean",
    "date": "date",
    "timestamp": "timestamp"
}


def validate_dataframe(df, contract):
    errors = []

    # Read ODCS metadata
    contract_name = contract.get("name")
    contract_version = contract.get("version")

    if not contract_name:
        raise ValueError(
            "Invalid ODCS contract: missing required field 'name'."
        )

    if not contract_version:
        raise ValueError(
            "Invalid ODCS contract: missing required field 'version'."
        )

    print(f"Validating ODCS contract: {contract_name}")
    print(f"ODCS version: {contract['apiVersion']}")
    print(f"Contract version: {contract_version}")

    # This example uses one schema object
    schema_objects = contract["schema"]

    if len(schema_objects) != 1:
        raise ValueError(
            "This example supports exactly one ODCS schema object"
        )

    schema_object = schema_objects[0]
    properties = schema_object["properties"]

    expected = {
        item["name"]: item
        for item in properties
    }

    # Check columns
    actual_columns = set(df.columns)
    expected_columns = set(expected.keys())

    missing = expected_columns - actual_columns
    unexpected = actual_columns - expected_columns

    if missing:
        errors.append(f"Missing columns: {sorted(missing)}")

    # Strict schema matching is a pipeline policy in this example
    if unexpected:
        errors.append(f"Unexpected columns: {sorted(unexpected)}")

    if errors:
        raise ValueError(
            "Data contract schema validation failed:\n"
            + "\n".join(errors)
        )

    validated_df = df

    # Validate required fields and datatypes
    for name, definition in expected.items():

        logical_type = definition["logicalType"]
        required = definition.get("required", False)

        if logical_type not in TYPE_MAPPING:
            errors.append(
                f"Unsupported logicalType '{logical_type}' "
                f"for column {name}"
            )
            continue

        source_col = col(name)

        if required:
            invalid = df.filter(
                source_col.isNull() |
                (trim(source_col) == "")
            ).limit(1).count()

            if invalid:
                errors.append(
                    f"Column {name} cannot be NULL or empty"
                )

        spark_type = TYPE_MAPPING[logical_type]

        if logical_type != "string":
            converted = source_col.cast(spark_type)

            invalid = df.filter(
                source_col.isNotNull() &
                (trim(source_col) != "") &
                converted.isNull()
            ).limit(1).count()

            if invalid:
                errors.append(
                    f"Invalid {logical_type} values in column {name}"
                )

            validated_df = validated_df.withColumn(
                name,
                converted
            )

    # Validate primary key
    primary_keys = [
        item["name"]
        for item in properties
        if item.get("primaryKey", False)
    ]

    if not primary_keys:
        errors.append("No primary key defined in ODCS contract")
    else:
        pk_invalid_condition = reduce(
            lambda left, right: left | right,
            [
                col(name).isNull() | (trim(col(name)) == "")
                for name in primary_keys
            ]
        )

        invalid_pk = validated_df.filter(
            pk_invalid_condition
        ).limit(1).count()

        if invalid_pk:
            errors.append(
                f"Primary key columns cannot be NULL or empty: "
                f"{primary_keys}"
            )

        duplicates = (
            validated_df
            .groupBy(*primary_keys)
            .count()
            .filter(col("count") > 1)
            .limit(1)
            .count()
        )

        if duplicates:
            errors.append(
                f"Duplicate primary key values found: {primary_keys}"
            )

    if errors:
        raise ValueError(
            "Data contract validation failed:\n"
            + "\n".join(errors)
        )

    print(
        f"ODCS contract {contract_name} "
        f"version {contract_version} PASSED"
    )

    return validated_df


The validator now performs the following steps:

1. Reads the ODCS metadata.

2. Retrieves the columns from 'schema.properties'.

3. Checks for missing or unexpected columns.

4. Validates 'required: true' constraints.

5. Verifies data types via 'logicalType'.

6. Checks the primary key for null values ​​and duplicates.

The strict check for additional columns is a deliberate design choice in our Python pipeline; these are distinct from ODCS fields.


processor.py does not need to change

At first I thought it was not necessary to change the processor.py. But after some debuging with AI I noticed that the merge didn't work well and I needed to replace the file.


from delta.tables import DeltaTable
from transfer.contract_validator import validate_dataframe

def transfer_data(spark, config, contract=None):

    source = config["source"]
    target = config["target"]
    file_format = config["format"]
    mode = config["mode"]

    print(f"Reading source: {source}")
    print(f"Target table: {target}")
    print(f"Loading mode: {mode}")

    # Read everything as strings first

    df = (
        spark.read
        .format(file_format)
        .option("header", "true")
        .option("inferSchema", "false")
        .load(source)
    )

    # 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"
    )


The processor does not need to know whether the contract originates from JSON or YAML; that is the responsibility of 'main.py'.


8. Testing in Databricks

In your VS Code terminal, run:


databricks bundle validate -t dev


Then

databricks bundle deploy -t dev


and :


databricks bundle run transfer_job -t dev --params entity=customers


Resulting in :




and execute :


databricks bundle run transfer_job -t dev --params entity=customers_update


Resulting in :



Hennie

vrijdag 13 november 2026

Databricks series : Debugging Python code in Visual Studio Code with arguments (Part IX)

Introduction

It took me a while to figure it out how to debug with Visual Studio Code when you have an argument in the code. These are the steps : First set a breakpoint,  go the "Run and Debug Pane" and then file in the parameter. 


We are building upon what we already have :


Breakpoint

Click on the screen left from the code window and a red appears



This is the breakpoint.


Run and Debug window

Next step is to go the 'Run and Debug' Pane. This is the Icon on the left with a play symbol and debug on top of that.




Click in Run and Debug:




Choose Python File with Arguments, and the following "Command Line Arguments" window appears




In my case,  I enter the word "customers" :




Hennie

Databricks series : Data contracts in Databricks (Part VIII)

Introduction

We are building upon what we already have:

Great. We are now going to fully integrate Data Contracts into our existing Databricks project, so that it’s no longer just a standalone example, but an integral part of our data ingestion pipeline. The new approach: every dataset has its own Data Contract. Before data is written to Delta Lake, we verify that it complies with the contract.





Moreover, we are making the solution somewhat more robust than our previous version: we will first read the data in as strings, validate the values, and then convert them into the agreed-upon data types.


Change the structure of the project

Open your project in Visual Studio Code. The new structure will be:

databricks-transfer/
│
├── databricks.yml
│
├── resources/
│   └── transfer_job.yml
│
├── src/
│   ├── __init__.py
│   ├── main.py
│   │
│   └── transfer/
│       ├── __init__.py
│       ├── config_loader.py
│       ├── contract_validator.py
│       └── processor.py
│
├── config/
│   └── entities.json
│
├── contracts/
│   ├── customers.json
│   └── transactions.json
│
├── data/
│   ├── customers.csv
│   ├── customers_update.csv
│   └── transactions.csv
│
├── requirements.txt
└── README.md


We are including two contract files and one Python module for validation.


Create a datacontract for customers

Open the 'contracts' directory and create 'customers.json'. Place the following inside it:

{
  "contract_name": "customers",
  "version": "1.0",
  "description": "Contract for customer master data",
  "owner": "Data Engineering",
  "allow_additional_columns": false,

  "columns": [
    {
      "name": "customer_id",
      "type": "string",
      "nullable": false
    },
    {
      "name": "first_name",
      "type": "string",
      "nullable": false
    },
    {
      "name": "last_name",
      "type": "string",
      "nullable": false
    },
    {
      "name": "country",
      "type": "string",
      "nullable": false
    }
  ],

  "primary_key": ["customer_id"],

  "rules": {
    "unique": ["customer_id"]
  }
}


This contract contains the agreements with the producer of the customer data. A few key properties:

  • version: allows us to manage changes to the contract.
  • owner: the party responsible for the contract.
  • allow_additional_columns: whether additional columns are permitted.
  • primary_key: the column(s) used to identify a record.
  • unique: which values ​​must be unique.

We intentionally use a strict contract: if someone unexpectedly adds an extra column, the dataset will be rejected.


Connect the contract with entities.json

Lets open config/entities.json and replace the content with:


{
  "customers": {
    "source": "/Volumes/main/demo/raw/customers.csv",
    "target": "main.demo.customers",
    "format": "csv",
    "mode": "overwrite",
    "contract": "contracts/customers.json"
  },

  "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.json"
  }
}


For now, we are focusing on 'customers' and 'customers_update'. Both use the same contract, as they share the same data structure.  We will add the 'transactions' contract once we have verified the exact columns in 'transactions.csv'. This ensures that we don’t define a contract that doesn’t match your actual file.


Build the contract validator

Now comes the most important part. Open the file  src/transfer/contract_validator.py and replace the entire content with:


from pyspark.sql.functions import col, trim


TYPE_MAPPING = {
    "string": "string",
    "integer": "int",
    "long": "bigint",
    "double": "double",
    "boolean": "boolean",
    "date": "date",
    "timestamp": "timestamp"
}


def validate_dataframe(df, contract):

    errors = []

    contract_name = contract["contract_name"]
    version = contract["version"]

    print(f"Validating contract: {contract_name}")
    print(f"Contract version: {version}")

    expected = {
        item["name"]: item
        for item in contract["columns"]
    }

    actual_columns = set(df.columns)
    expected_columns = set(expected.keys())

    # 1. Check schema

    missing = expected_columns - actual_columns
    unexpected = actual_columns - expected_columns

    if missing:
        errors.append(
            f"Missing columns: {sorted(missing)}"
        )

    if unexpected and not contract.get(
        "allow_additional_columns", False
    ):
        errors.append(
            f"Unexpected columns: {sorted(unexpected)}"
        )

    if errors:
        raise ValueError(
            "Schema validation failed:\n"
            + "\n".join(errors)
        )

    # 2. Check required fields and datatypes

    validated_df = df

    for name, definition in expected.items():

        expected_type = definition["type"]
        nullable = definition["nullable"]

        if expected_type not in TYPE_MAPPING:
            raise ValueError(
                f"Unsupported datatype: {expected_type}"
            )

        source_col = col(name)

        # Required fields cannot be NULL or empty

        if not nullable:

            invalid = df.filter(
                source_col.isNull() |
                (trim(source_col) == "")
            ).limit(1).count()

            if invalid:
                errors.append(
                    f"Column {name} cannot be NULL or empty"
                )

        # Validate and convert datatype

        spark_type = TYPE_MAPPING[expected_type]

        if expected_type != "string":

            converted = source_col.cast(spark_type)

            invalid = df.filter(
                source_col.isNotNull() &
                (trim(source_col) != "") &
                converted.isNull()
            ).limit(1).count()

            if invalid:
                errors.append(
                    f"Invalid {expected_type} values "
                    f"in column {name}"
                )

            validated_df = validated_df.withColumn(
                name, converted
            )

    # 3. Check primary key

    for name in contract.get("primary_key", []):

        if name not in expected:
            errors.append(
                f"Primary key column {name} "
                "is not defined in contract"
            )

    # 4. Check unique constraints

    unique_columns = set(
        contract.get("rules", {}).get("unique", [])
    )

    unique_columns.update(
        contract.get("primary_key", [])
    )

    for name in unique_columns:

        if name not in expected:
            errors.append(
                f"Unique column {name} "
                "is not defined in contract"
            )
            continue

        duplicates = (
            validated_df
            .groupBy(name)
            .count()
            .filter(col("count") > 1)
            .limit(1)
            .count()
        )

        if duplicates:
            errors.append(
                f"Duplicate values found in {name}"
            )

    # 5. Final result

    if errors:
        raise ValueError(
            "Data contract validation failed:\n"
            + "\n".join(errors)
        )

    print(
        f"Contract {contract_name} "
        f"version {version} PASSED"
    )

    return validated_df


What do we have now?

Validation         Example

Schema                 Is `customer_id` missing?

Extra columns         Is `phone_number` being added unexpectedly?

Null check         Is `first_name` empty?

Data type                 Can an amount be converted to a `double`?

Primary key         Is `customer_id` unique?


The validator returns an error message if a constraint is violated.


Integrate the validator in processor.py

The next step is to open: src/transfer/processor.py. Here, we ensure that validation always takes place before the loading strategy.


from delta.tables import DeltaTable

from transfer.contract_validator import validate_dataframe


def transfer_data(spark, config, contract=None):

    source = config["source"]
    target = config["target"]
    file_format = config["format"]
    mode = config["mode"]

    print(f"Reading source: {source}")
    print(f"Target table: {target}")
    print(f"Loading mode: {mode}")

    # Read everything as strings first

    df = (
        spark.read
        .format(file_format)
        .option("header", "true")
        .option("inferSchema", "false")
        .load(source)
    )

    # 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"]

        if merge_key not in contract.get(
            "primary_key", []
        ):
            raise ValueError(
                f"Merge key {merge_key} is not "
                "defined as a contract primary key"
            )

        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"
    )


An important difference from our previous implementation: a MERGE can now only be performed if the merge key is also defined as the primary key in the Data Contract. Furthermore, duplicate keys in the source data are rejected before the MERGE is executed.


Modify main.py

Next step is to modify the main.py file.


import argparse
from pathlib import Path

from pyspark.sql import SparkSession

from transfer.config_loader import load_entity_config
from transfer.contract_validator import validate_dataframe
from transfer.processor import transfer_data


def main():

    parser = argparse.ArgumentParser()

    parser.add_argument(
        "--entity",
        required=True
    )

    args = parser.parse_args()

    spark = SparkSession.builder.getOrCreate()

    # Determine project root
    project_root = Path(__file__).resolve().parents[1]

    # Load entity configuration
    config_path = project_root / "config" / "entities.json"

    config = load_entity_config(
        config_path,
        args.entity
    )

    # Load associated data contract
    contract_path = (
        project_root / config["contract"]
    )

    import json

    with open(contract_path, "r") as file:
        contract = json.load(file)

    # Execute transfer with contract
    transfer_data(
        spark,
        config,
        contract
    )


if __name__ == "__main__":
    main()


This completes the chain. The Job only passes the entity; the rest is automatically retrieved from the configuration files.


Adjust the DAB configuration

We need to ensure that the contract files are included in the deployment. Open: databricks.yml and check whether your configuration looks like this:


bundle:
  name: databricks-transfer

include:
  - resources/*.yml

sync:
  include:
    - src/**
    - config/**
    - contracts/**
    - requirements.txt

targets:
  dev:
    default: true
    mode: development


This synchronizes the JSON contracts with the rest of your project. The existing resources/transfer_job.yml file does not need to be modified. The job will continue to receive only the 'entity' parameter.


Testing: first, a successful validation

Now, we will check whether everything is working. Open the terminal in Visual Studio Code.


Validate the Bundle

Now we can deploy the project. Open the terminal in Visual Studio Code and navigate to the root of your project.  Execute:


databricks bundle validate -t dev


Resulting in : 




And if everything is right : 


databricks bundle deploy -t dev


Next, you can start the job:


databricks bundle run transfer_job -t dev --params entity=customers


You can expect logging roughly like this:

Working directory: /Workspace/Users/xxxx@xxxx.com/.bundle/databricks-transfer/dev/files/src
Project root: /Workspace/Users/xxxx@xxxx.com/.bundle/databricks-transfer/dev/files
Reading source: /Volumes/main/demo/raw/customers.csv Target table: main.demo.customers Loading mode: overwrite Validating contract: customers Contract version: 1.0 Contract customers version 1.0 PASSED Validated records: 4 Successfully processed 4 records


The exact logging may vary slightly, but contract validation must succeed before the table is updated.


databricks bundle run transfer_job -t dev --params entity=customers_update


This dataset must also comply with the same contract. Next, check:


SELECT *
FROM main.demo.customers
ORDER BY customer_id;


You are expecting five customers.





Testing an incorrect Data Contract

Now, we will intentionally introduce an error. This is important to demonstrate that our Data Contract integration is actually working. In Visual Studio Code, create: data/customers_invalid.csv


With the following content:


customer_id,first_name,last_name,country
1001,Jan,Jansen,Netherlands
1002,,Peeters,Belgium
1003,Anna,Smith,United Kingdom


Upload het bestand naar:/Volumes/main/demo/raw/customers_invalid.csv


Next, temporarily add this entity to config/entities.json:

"customers_invalid": {
  "source": "/Volumes/main/demo/raw/customers_invalid.csv",
  "target": "main.demo.customers",
  "format": "csv",
  "mode": "overwrite",
  "contract": "contracts/customers.json"
}


Execute the folowing again :

databricks bundle deploy -t dev


And test the invalid schema :

databricks bundle run transfer_job -t dev --params entity=customers_invalid

Now the expected result is that the validation is failed


Task transfer_data FAILED:

ValueError: Data contract validation failed:

Column first_name cannot be NULL or empt


The job stops before the overwrite takes place. Consequently, your existing 'customers' table remains intact. This is a key advantage of this architecture: we validate before writing any data.


Now, this contract implementation is a custom solution. Next step is have a look at the Open Data Contract Standard (ODCS).


Hennie

dinsdag 10 november 2026

Databricks series : Loading strategies in Databricks (Part VII)

Introduction

This blogpost is the seventh in a series of blogposts about Databricks. In these series I showed and will show several features of Databricks with easy to understand examples. In this blogpost we'll describe a more configurable ingestion framework.


Currently, we have a Python script that reads a CSV file and writes it to a Delta table. Now we will take the next step in building a data engine.

We support three loading strategies:
  • Overwrite. The existing table is replaced by the new dataset. Suitable for full snapshots.
  • Append. New records are added to the existing table. Suitable for transactional data.
  • Merge. Existing records are updated, and new records are added. Suitable for changes to customer data.
For this, we will modify only one file: src/transfer/processor.py.


Extend the loading strategies

from delta.tables import DeltaTable

def transfer_data(spark, config):

    source = config["source"]
    target = config["target"]
    file_format = config["format"]
    mode = config["mode"]

    df = (
        spark.read
        .format(file_format)
        .option("header", "true")
        .option("inferSchema", "true")
        .load(source)
    )

    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"]

        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 mode: {mode}")

    print(f"Successfully processed {df.count()} records")


Note: For a Merge, we add a 'merge_key' to the configuration. We must also ensure that the source data does not contain duplicate keys; otherwise, the Merge may fail.


Open `config/entities.json` and modify it as follows:


{
  "customers": {
    "source": "/Volumes/main/demo/raw/customers.csv",
    "target": "main.demo.customers",
    "format": "csv",
    "mode": "overwrite"
  },
  "customers_update": {
    "source": "/Volumes/main/demo/raw/customers_update.csv",
    "target": "main.demo.customers",
    "format": "csv",
    "mode": "merge",
    "merge_key": "customer_id"
  },
  "transactions": {
    "source": "/Volumes/main/demo/raw/transactions.csv",
    "target": "main.demo.transactions",
    "format": "csv",
    "mode": "append"
  }
}


The configuration now determines how the data is processed. You no longer need to modify the Python code when you want to use a different loading strategy for an entity.


Testing with modified customer data

To properly demonstrate the Merge functionality, we will create a second customers file containing modified data. We name the file : customers_update.csv


customer_id,first_name,last_name,country
1001,Jan,Jansen,Netherlands
1002,Pieter,Peeters,Belgium
1005,Sarah,Johnson,USA


We use two files: customers.csv


customer_id First name Last name    Country

1001         Jan                Jansen             Netherlands

1002         Piet                  Peeters            Belgium

1003         Anna          Smith              United Kingdom

1004         Marie          Dubois             France



And we have customers_update.csv :


customer_id First name Last name        Country

1001         Jan                 Jansen              Netherlands

1002         Pieter         Peeters               Belgium

1005         Sarah         Johnson            USA


After a MERGE, we expect the following:

  • 1001 remains unchanged.
  • 1002 is updated from Piet to Pieter.
  • 1003 remains.
  • 1004 remains.
  • 1005 is added.


Upload the file to Databricks





Deploying to Databricks

Now we can deploy the project. Open the terminal in Visual Studio Code and navigate to the root of your project.  Execute:


databricks bundle validate -t dev


Resulting in : 




And if everything is right : 


databricks bundle deploy -t dev


Next, you can start the job:


databricks bundle run transfer_job -t dev --params entity=customers


Check in Databricks SQL 


SELECT *
FROM main.demo.customers
ORDER BY customer_id


This will give the following results :


Second run: customers_update

Now for the interesting part.  We run the same Job again, but with a different parameter:


databricks bundle run transfer_job -t dev --params entity=customers_update


Check in Databricks SQL :

SELECT *
FROM main.demo.customers
ORDER BY customer_id


Resulting in :



And so it succeeds quite well. Piet has been changed to Pieter and we have a new record. 


Now to be complete, lets test the append.



databricks bundle run transfer_job -t dev --params entity=transactions 




and again : 


databricks bundle run transfer_job -t dev --params entity=transactions 

And we test the table we have duplicate records in the table





Thats it!


Hennie