vrijdag 20 november 2026

Data modelling series : Cardinality

Introduction

Data modeling remains essential because it transforms raw data into reliable, usable, and scalable information, regardless of how fast technologies like cloud storage, NoSQL, or AI evolve. Without a solid data model, organizations end up with data chaos : leading to flawed analytics, slow systems, and untrustworthy AI outputs.

 

Reasons

Reasons why data modelling is still important : 

  • Foundation for AI and Machine Learning: AI models and LLMs are only as good as the data feeding them (garbage in, garbage out). A clear data model provides context, clean relationships, and consistent definitions, enabling AI systems to make accurate predictions and hallucinate less.
  • Single Source of Truth: Within a company, terms like "active customer" or "revenue" often mean different things to marketing than they do to finance. Data modeling forces organizations to agree on and enforce clear business rules and definitions before building reports or dashboards.
  • Preventing High Cloud Costs and Technical Debt: Modern cloud data platforms (such as Snowflake or BigQuery) make it easy to dump massive amounts of unstructured data. However, without a proper model, complex queries become painfully slow and cloud compute costs skyrocket.
  • Data Governance and Privacy Compliance (GDPR): Privacy laws and security standards require organizations to know exactly what personal data they hold, where it lives, and how it connects. A data model makes data lineage and access control transparent and auditable.
  • Bridge Between Business and IT: A data model (especially at the conceptual and logical levels) acts as a shared language. It aligns domain experts and software engineers on business processes and expectations before a single line of code is written.


Cardinality

In this blogpost I want to go more in depth about cardinality between entities. Below an example of a Customer, order and product. It's not a perfect example but assume it is ;-). I borrowed this example from a youtube video from Lucidchart.



We have a minimum and maximum number that an entity instance(s) can have related to another entitiy instance(s). Lets understand the maximum and minimum cardinality.


Maximum cardinality rules :

  • One-to-One (1:1): One instance of Entity A connects to at most one instance of Entity B, and vice versa (e.g., a person and a passport). 
  • One-to-Many (1:N) / Many-to-One (N:1): One instance of A connects to many instances of B, while each instance of B connects to at most one instance of A (e.g., one customer places many orders).
  • Many-to-Many (M:N): Multiple instances of A connect to multiple instances of B and vice versa (e.g., students and courses). We also call this in datamodelling an associative entity.

Minimum cardinality (optionality) rules :

  • Mandatory (Minimum = 1): An entity instance must participate in the relationship.
  • Optional (Minimum = 0): An entity instance may participate without a matching record on the other side (e.g., a customer may have 0 orders). 

There is a difference between : and .. . If you say it is 1:N then we reason from maximum cardinality and if we say 0..N. Use a double dot (..) when defining the exact rules for one specific direction (minimum..maximum). 


How they fit together? Take a standard One-to-Many (1:N) relationship between a Customer and an Order:
  • Overall (maximum) relationship: 1:N (Customer to Orders).
  • Customer → Order (0..N): "A Customer may place zero, one, or many Orders."
  • Order → Customer (1..1): "An Order must belong to one and only one Customer."

So it's a bit confusing, when we say a relation is 1:N, because this the MAXIMUM relationship, and this gives only a specific view of the reality. 






So we have four options :




Now let's investigate this in more depth. So what does this mean? How many combinations can we create? As it seems we can create 10 combinations.





One to one (3 variants)

In a One-to-One relationship, a single record in Entity A links to at most one record in Entity B, and vice versa. Here are the variants with examples:





Why It Matters: 1:1 relationships are often used to separate sensitive data (like user credentials or medical records) from general profile data for security and performance.


One to many (4 variants)

The One-to-Many relationship is the most common building block in relational databases. One record in the "parent" entity can link to multiple records in the "child" entity, while each child record belongs to only one parent.



Why It Matters: Getting the direction of 1:N right prevents catastrophic reporting bugs : such as accidentally multiplying revenue numbers when joining customer and order tables in BI dashboards.

Many to Many (3 variants)

In a Many-to-Many relationship, multiple records in Entity A can link to multiple records in Entity B, and vice versa.



Why It Matters: N:M relations are often used in conceptual modelling. Relational databases cannot store a direct M:N link cleanly without duplicating data. To solve this, data modelers introduce a bridge table (such as Enrollment), splitting one M:N relationship into two clean 1:N relationships.

Hennie

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