> For the complete documentation index, see [llms.txt](https://docs.soda.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.soda.io/guides-and-tutorials/data-owner-producer/test-data-quality-in-an-airflow-pipeline.md).

# Test data quality in an Airflow pipeline

Verify data contracts as tasks in an Airflow DAG, and stop the pipeline when data fails a check.

This guide shows how to verify Soda data contracts at each stage of an Apache Airflow pipeline: after ingestion, after a dbt transformation, and before the data reaches a report. When a check fails, the Airflow task fails and the tasks downstream of it don't run, so data that fails a check never reaches the report. Warnings are logged and published to Soda Cloud without stopping the pipeline.

Use the example as a starting point and adapt the data source, datasets, and checks to your own pipeline.

## Example pipeline

A data engineer builds a report of sales by product category from AdventureWorks data in PostgreSQL. dbt builds two models: `fact_product_category`, which joins products to their subcategories and categories, and `report_category_sales`, which sums sales per category.

The DAG verifies a set of contracts after each stage:

| Task               | What it does                                                                         |
| ------------------ | ------------------------------------------------------------------------------------ |
| `verify_ingest`    | Verifies the contracts for the source tables `dim_product` and `fact_internet_sales` |
| `dbt_transform`    | Builds `fact_product_category`                                                       |
| `verify_transform` | Verifies the contract for `fact_product_category`                                    |
| `dbt_reports`      | Builds `report_category_sales`                                                       |
| `verify_reports`   | Verifies the contract for `report_category_sales`                                    |

The project has this layout:

```
sales-pipeline/
├── dags/
│   └── sales_report.py
├── dbt/
│   ├── dbt_project.yml
│   ├── profiles.yml
│   └── models/
│       ├── transform/fact_product_category.sql
│       └── reports/report_category_sales.sql
└── soda/
    ├── ds_config.yml
    ├── sc_config.yml
    └── contracts/
        ├── ingest/
        │   ├── dim_product.yml
        │   └── fact_internet_sales.yml
        ├── transform/
        │   └── fact_product_category.yml
        └── reports/
            └── report_category_sales.yml
```

## Prerequisites

* An Airflow 3 environment, and access to its workers to install Python packages
* A PostgreSQL data source, and a dbt project that builds models in it
* A Soda Cloud account and an API key, to publish results. See [Generate API keys](https://docs.soda.io/apis/generate-api-keys.md).

## Install Soda in its own environment

Install Soda in a separate virtual environment on the Airflow workers, not in the Airflow environment itself. Soda Core pins versions of dependencies that Airflow also pins in its constraints file, such as `requests`, so the two can't always be installed together. The DAG calls Soda through Airflow's `@task.external_python` decorator, which runs a task in another Python environment.

```bash
python3 -m venv /opt/soda-venv
/opt/soda-venv/bin/pip install -i https://pypi.cloud.soda.io/simple "soda-postgres>4"
```

dbt has the same kind of conflict with Airflow, so this example installs it in its own environment as well and calls it from a `BashOperator`:

```bash
python3 -m venv /opt/dbt-venv
/opt/dbt-venv/bin/pip install dbt-core dbt-postgres
```

> For other data sources and the full installation options, see [Soda Core installation](https://docs.soda.io/soda-documentation/connect/deployment-options/soda-python-libraries.md).

## Connect Soda to the data source and Soda Cloud

Create a data source configuration file in the `soda` folder. The values in `${env.…}` are read from environment variables, which must be set on the Airflow workers.

{% code title="soda/ds\_config.yml" %}

```yaml
type: postgres
name: adventureworks
connection:
  host: ${env.POSTGRES_HOST}
  port: 5432
  user: ${env.POSTGRES_USER}
  password: ${env.POSTGRES_PASSWORD}
  database: adventureworks
```

{% endcode %}

Create a Soda Cloud configuration file next to it. Use `cloud.us.soda.io` or `cloud.soda.io`, depending on your Soda region.

{% code title="soda/sc\_config.yml" %}

```yaml
soda_cloud:
  host: cloud.us.soda.io
  api_key_id: ${env.SODA_CLOUD_API_KEY_ID}
  api_key_secret: ${env.SODA_CLOUD_API_KEY_SECRET}
```

{% endcode %}

Test both connections from the `soda` folder:

```bash
soda data-source test -ds ds_config.yml
soda cloud test -sc sc_config.yml
```

> For the configuration keys of each data source, see the [Data source reference for Soda Core](https://docs.soda.io/soda-documentation/developer-tools/data-source-reference-for-soda-core.md).

## Write the contracts

Each contract defines the checks for one dataset. The DAG verifies every contract in a stage folder, so to add a dataset to a stage, add its contract to that folder.

### Ingest contracts

The source tables come from upstream systems that the data engineer can't fix directly. These contracts catch problems as soon as the data lands, so the engineer can report them to the data owners before any model is built on top.

A product without a subcategory isn't an error on its own, but it ends up in the report as uncategorized sales. That check warns instead of failing.

{% code title="soda/contracts/ingest/dim\_product.yml" %}

```yaml
dataset: adventureworks/adventureworks/public/dim_product

columns:
  - name: product_key
    data_type: integer
    checks:
      - missing:
          name: All products have a key
      - duplicate:
          name: Product keys are unique
  - name: english_product_name
    data_type: varchar
    checks:
      - missing:
          name: All products have a name
  - name: product_subcategory_key
    checks:
      - missing:
          name: All products have a subcategory
          threshold:
            must_be: 0
            level: warn

checks:
  - schema:
  - row_count:
      name: Products are present
      threshold:
        must_be_greater_than: 0
```

{% endcode %}

{% code title="soda/contracts/ingest/fact\_internet\_sales.yml" %}

```yaml
dataset: adventureworks/adventureworks/public/fact_internet_sales

columns:
  - name: sales_order_number
    data_type: varchar
  - name: product_key
    data_type: integer
    checks:
      - missing:
          name: All sales have a product associated
  - name: order_quantity
    data_type: smallint
    checks:
      - aggregate:
          name: All sales have a non-zero order quantity
          function: min
          threshold:
            must_be_greater_than: 0
  - name: sales_amount
    data_type: numeric
    checks:
      - failed_rows:
          name: All sales have a non-zero order amount
          expression: sales_amount <= 0

checks:
  - schema:
  - row_count:
      name: Sales are within expected range
      threshold:
        must_be_between:
          greater_than_or_equal: 5
          less_than_or_equal: 5000
```

{% endcode %}

### Transform contract

The transformation joins every product to a subcategory and a category. A product that has no match keeps its row with empty category keys, so these checks warn when that happens.

{% code title="soda/contracts/transform/fact\_product\_category.yml" %}

```yaml
dataset: adventureworks/adventureworks/public/fact_product_category

columns:
  - name: product_key
    checks:
      - missing:
          name: All products have a key
  - name: category_key
    checks:
      - missing:
          name: All products have a category
          threshold:
            must_be: 0
            level: warn
  - name: subcategory_key
    checks:
      - missing:
          name: All products have a subcategory
          threshold:
            must_be: 0
            level: warn
```

{% endcode %}

### Report contract

The report contract fails if uncategorized sales reach 0.9% of the total, or if the report total doesn't match the total in `fact_internet_sales`. Both are metric checks on the same dataset, so each one needs a `qualifier` to give it a distinct identity.

{% code title="soda/contracts/reports/report\_category\_sales.yml" %}

```yaml
dataset: adventureworks/adventureworks/public/report_category_sales

columns:
  - name: category_key
  - name: sales_total

checks:
  - metric:
      qualifier: uncategorized_share
      name: Most sales are categorized
      query: |
        SELECT ROUND(100 * COALESCE(SUM(sales_total) FILTER (WHERE category_key IS NULL), 0)
                     / SUM(sales_total), 2)
        FROM public.report_category_sales
      threshold:
        must_be_less_than: 0.9
  - metric:
      qualifier: sales_total_diff
      name: Category sales total matches
      query: |
        SELECT (SELECT SUM(sales_amount) FROM public.fact_internet_sales)
             - (SELECT SUM(sales_total) FROM public.report_category_sales)
      threshold:
        must_be: 0
```

{% endcode %}

> For every check type and threshold option, see the [Contract Language reference](https://docs.soda.io/soda-documentation/developer-tools/contract-language-reference.md).

{% hint style="info" %}
Coming from Soda v3? Checks that compare a value with previous scans, such as `change for row_count` and `anomaly detection`, have no contract equivalent. Use [Metric Monitoring](https://docs.soda.io/soda-documentation/find/data-observability/metric-monitoring-dashboard.md) to detect anomalies on the same datasets. For the rest of the syntax changes, see [Migrate from v3 to v4](https://docs.soda.io/soda-documentation/govern/migrate-from-v3-to-v4.md).
{% endhint %}

## Verify a contract from the command line

Before you add the contracts to the DAG, validate and verify them from the `soda` folder:

```bash
soda contract test -c contracts/ingest/dim_product.yml
soda contract verify -ds ds_config.yml -c contracts/ingest/dim_product.yml
```

`soda contract test` checks the contract syntax without connecting to the data source. `soda contract verify` runs the checks and prints a table of results. Its exit code reports the outcome:

| Exit code | Meaning                                                                 |
| --------- | ----------------------------------------------------------------------- |
| `0`       | All checks passed                                                       |
| `1`       | One or more checks failed                                               |
| `2`       | One or more checks warned, and none failed                              |
| `3`       | An error occurred during execution, such as a parse or connection error |
| `4`       | Results could not be sent to Soda Cloud                                 |

In this example, `dim_product` has one product without a subcategory, so the command exits with `2`.

{% hint style="warning" %}
If you run `soda contract verify` in a `BashOperator` instead of the Python API, the task fails on every exit code from `1` to `4`, including `2` for warnings.
{% endhint %}

## Create the DAG

The DAG runs each verification stage with the same task. The task verifies every contract in the stage folder with `verify_contract_locally`, publishes the results to Soda Cloud, and raises an error if any contract fails.

`result.is_ok` is `False` when a check fails or the verification errors, and `True` when checks only warn. A warning therefore doesn't stop the pipeline.

Replace the paths at the top of the file with the locations of your project and virtual environments.

{% code title="dags/sales\_report.py" %}

```python
from datetime import datetime
from pathlib import Path

from airflow.providers.standard.operators.bash import BashOperator
from airflow.sdk import dag, task

PROJECT_ROOT = Path("/opt/sales-pipeline")
SODA_PYTHON = "/opt/soda-venv/bin/python"
DBT = "/opt/dbt-venv/bin/dbt"
DBT_FLAGS = f"--project-dir {PROJECT_ROOT}/dbt --profiles-dir {PROJECT_ROOT}/dbt"


@task.external_python(python=SODA_PYTHON, expect_airflow=False)
def verify_contracts(project_root: str, stage: str):
    from pathlib import Path

    from soda_core import configure_logging
    from soda_core.contracts import verify_contract_locally

    soda_dir = Path(project_root) / "soda"
    failed = []
    for contract in sorted((soda_dir / "contracts" / stage).glob("*.yml")):
        configure_logging(verbose=False)
        result = verify_contract_locally(
            data_source_file_path=str(soda_dir / "ds_config.yml"),
            contract_file_path=str(contract),
            soda_cloud_file_path=str(soda_dir / "sc_config.yml"),
            publish=True,
        )
        if not result.is_ok:
            failed.append(
                f"{contract.name}: {result.number_of_checks_failed} failed checks "
                f"{result.get_errors_str()}"
            )
    if failed:
        raise ValueError("Contract verification failed: " + "; ".join(failed))


@dag(schedule="@daily", start_date=datetime(2026, 1, 1), catchup=False)
def sales_report():
    ingest_checks = verify_contracts.override(task_id="verify_ingest")(
        str(PROJECT_ROOT), "ingest"
    )
    transform = BashOperator(
        task_id="dbt_transform",
        bash_command=f"{DBT} run --select transform {DBT_FLAGS}",
    )
    transform_checks = verify_contracts.override(task_id="verify_transform")(
        str(PROJECT_ROOT), "transform"
    )
    reports = BashOperator(
        task_id="dbt_reports",
        bash_command=f"{DBT} run --select reports {DBT_FLAGS}",
    )
    report_checks = verify_contracts.override(task_id="verify_reports")(
        str(PROJECT_ROOT), "reports"
    )

    ingest_checks >> transform >> transform_checks >> reports >> report_checks


sales_report()
```

{% endcode %}

A few details in the task:

* `expect_airflow=False` tells Airflow that the Soda environment doesn't include Airflow. Without it, Airflow logs a warning for every run.
* The imports are inside the function, because the function runs in the Soda environment.
* `configure_logging` is called before each verification in the loop. See [Python API](https://docs.soda.io/apis/python-api.md).

## Run the DAG

Run the DAG once from the command line to test it:

```bash
airflow dags test sales_report
```

In this example the run succeeds. `verify_ingest` and `verify_transform` log warnings for the product without a subcategory, and all five tasks complete.

When a check fails, the task that verified it fails, and the tasks after it don't run. For example, a sale with an amount of `0` fails the check **All sales have a non-zero order amount**. `verify_ingest` then fails with an error like this, and dbt never builds the models:

```
ValueError: Contract verification failed: fact_internet_sales.yml: 1 failed checks
```

<figure><img src="https://1928210810-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FJJOow5YZtrrxZJBNXV2D%2Fuploads%2Fgit-blob-ca645cb58061e50c66cea8cdc1b974c9c24da0f4%2Ftest-data-quality-in-an-airflow-pipeline-airflow-ingest-failed.png?alt=media" alt="The Checks tab of the fact_internet_sales dataset in Soda Cloud, with the check All sales have a non-zero order amount failed"><figcaption><p>The failed check on <code>fact_internet_sales</code> after the ingest task stopped the pipeline</p></figcaption></figure>

## Results in Soda Cloud

Each verification publishes its results to the dataset in Soda Cloud. Open **Datasets**, select a dataset, and open the **Checks** tab to see the latest result of each check and its result history across runs.

<figure><img src="https://1928210810-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FJJOow5YZtrrxZJBNXV2D%2Fuploads%2Fgit-blob-2be0738244a7411cff77967cf96b6d2c0383feec%2Ftest-data-quality-in-an-airflow-pipeline-airflow-transform-warnings.png?alt=media" alt="The Checks tab of the fact_product_category dataset in Soda Cloud, with one passing check and two warnings"><figcaption><p>The transform contract's results: the category and subcategory checks warn for the product without a subcategory</p></figcaption></figure>

> To get notified when a check fails, see [Notifications](https://docs.soda.io/soda-documentation/fix/notifications.md).

## Next steps

* Add [Metric Monitoring](https://docs.soda.io/soda-documentation/find/data-observability/metric-monitoring-dashboard.md) to the same datasets to detect anomalies over time.
* Verify contracts on every pull request with the [GitHub integration](https://docs.soda.io/soda-documentation/integrate/github.md).
* Run verifications on a [Soda Runner](https://docs.soda.io/soda-documentation/find/data-testing/git-managed-data-contracts/verify-a-contract.md#using-soda-runner) instead of the Airflow workers.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.soda.io/guides-and-tutorials/data-owner-producer/test-data-quality-in-an-airflow-pipeline.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
