> 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-a-dagster-pipeline.md).

# Test data quality in a Dagster pipeline

Verify data contracts as Dagster asset checks, so data that fails a check is never loaded or transformed.

This guide shows how to verify Soda data contracts as Dagster asset checks at two points in a pipeline: on incoming data before it is loaded, and on a dbt model after it is built. The checks are blocking, so when a check fails, Dagster doesn't materialize the assets downstream of it.

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

## Example pipeline

A retail company receives a file of new sales every day. The pipeline loads the file into PostgreSQL, and dbt builds a report of sales by product category on top of it.

| Asset or check                   | What it does                                                        |
| -------------------------------- | ------------------------------------------------------------------- |
| `incoming_sales`                 | Reads the file of new sales into a pandas DataFrame                 |
| `incoming_sales_contract`        | Verifies the DataFrame against a contract before anything is loaded |
| `fact_internet_sales`            | Appends the new sales to the `fact_internet_sales` table            |
| dbt models                       | Build `fact_product_category` and `report_category_sales`           |
| `report_category_sales_contract` | Verifies the report against its contract                            |

The project has this layout:

```
sales-pipeline/
├── data/
│   └── new_sales.csv
├── dbt/
│   ├── dbt_project.yml
│   ├── profiles.yml
│   └── models/
│       ├── sources.yml
│       ├── fact_product_category.sql
│       └── report_category_sales.sql
├── pyproject.toml
├── sales_pipeline/
│   ├── __init__.py
│   └── definitions.py
└── soda/
    ├── ds_config.yml
    └── contracts/
        ├── incoming_sales.yml
        └── report_category_sales.yml
```

## Prerequisites

* Python 3.10 or later
* A PostgreSQL data source with the source tables, and a dbt project that builds models in it

## Install Dagster, dbt, and Soda

Install Dagster with its `dg` command line and dbt integration, dbt, and the Soda packages for PostgreSQL and DuckDB. The DuckDB package lets Soda verify a pandas DataFrame in memory.

```bash
pip install dagster dagster-webserver dagster-dg-cli dagster-dbt dbt-core dbt-postgres pandas
pip install -i https://pypi.cloud.soda.io/simple "soda-postgres>4" "soda-duckdb>4"
```

> 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 and dbt to the data source

Create a data source configuration file in the `soda` folder. The values in `${env.…}` are read from environment variables.

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

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

{% endcode %}

dbt reads the same environment variables:

{% code title="dbt/profiles.yml" %}

```yaml
sales:
  target: dev
  outputs:
    dev:
      type: postgres
      host: "{{ env_var('POSTGRES_HOST') }}"
      port: "{{ env_var('POSTGRES_PORT') | int }}"
      user: "{{ env_var('POSTGRES_USER') }}"
      password: "{{ env_var('POSTGRES_PASSWORD') }}"
      dbname: adventureworks
      schema: public
      threads: 1
```

{% endcode %}

Set the environment variables before you run the pipeline:

```bash
export POSTGRES_HOST=<your_host>
export POSTGRES_PORT=5432
export POSTGRES_USER=<your_user>
export POSTGRES_PASSWORD=<your_password>
```

> 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

### Incoming data contract

This contract verifies the file of new sales while it is still a DataFrame. The pipeline registers the DataFrame as a DuckDB view named `incoming_sales` in a data source named `incoming`. A view registered this way belongs to DuckDB's `temp` catalog, so the dataset name is `incoming/temp/main/incoming_sales`.

{% code title="soda/contracts/incoming\_sales.yml" %}

```yaml
dataset: incoming/temp/main/incoming_sales

columns:
  - name: sales_order_number
    checks:
      - missing:
          name: All sales have an order number
      - duplicate:
          name: Order numbers are unique
  - name: product_key
    checks:
      - missing:
          name: All sales have a product
  - name: order_quantity
    checks:
      - aggregate:
          name: All sales have a non-zero order quantity
          function: min
          threshold:
            must_be_greater_than: 0
  - name: sales_amount
    checks:
      - failed_rows:
          name: All sales have a non-zero order amount
          expression: sales_amount <= 0

checks:
  - schema:
  - row_count:
      name: The file has sales
      threshold:
        must_be_greater_than: 0
```

{% endcode %}

> To learn more about verifying DataFrames, see [DuckDB advanced usage](https://docs.soda.io/soda-documentation/developer-tools/data-source-reference-for-soda-core/duckdb/duckdb-advanced-usage.md).

### 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/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).

## Define the pipeline

The `pyproject.toml` file at the root of the project tells the `dg` command line where the definitions are:

{% code title="pyproject.toml" %}

```toml
[project]
name = "sales_pipeline"
version = "0.1.0"
requires-python = ">=3.10"

[tool.dg]
directory_type = "project"

[tool.dg.project]
root_module = "sales_pipeline"
code_location_target_module = "sales_pipeline.definitions"
```

{% endcode %}

The definitions file creates the assets, the dbt assets, and one asset check per contract. Each check calls `verify_contract_locally` and returns an `AssetCheckResult`. `result.is_ok` is `False` when a check in the contract fails or the verification errors, and `True` when checks only warn.

{% code title="sales\_pipeline/definitions.py" %}

```python
import os
from pathlib import Path

import duckdb
import pandas as pd
import psycopg
from dagster import (
    AssetCheckResult,
    AssetCheckSeverity,
    AssetExecutionContext,
    AssetKey,
    Definitions,
    asset,
    asset_check,
    define_asset_job,
)
from dagster_dbt import DbtCliResource, DbtProject, dbt_assets
from soda_core.contracts import verify_contract_locally
from soda_duckdb import DuckDBDataSource

PROJECT_DIR = Path(__file__).parent.parent
CONTRACTS = PROJECT_DIR / "soda" / "contracts"

dbt_project = DbtProject(project_dir=PROJECT_DIR / "dbt")
dbt_project.prepare_if_dev()


def to_check_result(result) -> AssetCheckResult:
    return AssetCheckResult(
        passed=result.is_ok,
        severity=AssetCheckSeverity.ERROR,
        metadata={
            "checks": result.number_of_checks,
            "failed_checks": result.number_of_checks_failed,
        },
    )


@asset
def incoming_sales() -> pd.DataFrame:
    return pd.read_csv(PROJECT_DIR / "data" / "new_sales.csv")


@asset_check(asset=incoming_sales, blocking=True)
def incoming_sales_contract(incoming_sales: pd.DataFrame) -> AssetCheckResult:
    cursor = duckdb.connect(database=":memory:").cursor()
    cursor.register(view_name="incoming_sales", python_object=incoming_sales)
    result = verify_contract_locally(
        data_sources=[DuckDBDataSource.from_existing_cursor(cursor, name="incoming")],
        contract_file_path=str(CONTRACTS / "incoming_sales.yml"),
    )
    return to_check_result(result)


@asset(key_prefix="adventureworks")
def fact_internet_sales(incoming_sales: pd.DataFrame) -> None:
    with psycopg.connect(
        host=os.environ["POSTGRES_HOST"],
        port=os.environ["POSTGRES_PORT"],
        user=os.environ["POSTGRES_USER"],
        password=os.environ["POSTGRES_PASSWORD"],
        dbname="adventureworks",
    ) as conn:
        conn.cursor().executemany(
            "INSERT INTO fact_internet_sales "
            "(sales_order_number, product_key, order_quantity, sales_amount) "
            "VALUES (%s, %s, %s, %s)",
            incoming_sales.itertuples(index=False),
        )


@dbt_assets(manifest=dbt_project.manifest_path)
def sales_models(context: AssetExecutionContext, dbt: DbtCliResource):
    yield from dbt.cli(["build"], context=context).stream()


@asset_check(asset=AssetKey("report_category_sales"), blocking=True)
def report_category_sales_contract() -> AssetCheckResult:
    result = verify_contract_locally(
        data_source_file_path=str(PROJECT_DIR / "soda" / "ds_config.yml"),
        contract_file_path=str(CONTRACTS / "report_category_sales.yml"),
    )
    return to_check_result(result)


defs = Definitions(
    assets=[incoming_sales, fact_internet_sales, sales_models],
    asset_checks=[incoming_sales_contract, report_category_sales_contract],
    jobs=[define_asset_job("sales_pipeline", selection="*")],
    resources={"dbt": DbtCliResource(project_dir=dbt_project)},
)
```

{% endcode %}

A few details in the definitions:

* `blocking=True` makes Dagster stop the run when the check fails, so `fact_internet_sales` and the dbt models aren't materialized after a failed `incoming_sales_contract`.
* `key_prefix="adventureworks"` gives `fact_internet_sales` the same asset key as the dbt source `adventureworks.fact_internet_sales`, so Dagster runs the dbt models after the load.
* `verify_contract_locally` accepts DuckDB data sources created from an existing cursor. See [Python API](https://docs.soda.io/apis/python-api.md).

## Run the pipeline

Create the dbt manifest that `dagster-dbt` reads, then run the job:

```bash
dbt parse --project-dir dbt --profiles-dir dbt
dg launch --job sales_pipeline
```

With clean data, both checks pass, the new sales load, and dbt builds the report.

When a check fails, the run fails at that check. For example, a file with a sale of amount `0` fails the check **All sales have a non-zero order amount**. `incoming_sales_contract` doesn't pass, Dagster doesn't load the file, and dbt doesn't run.

To see the results in the Dagster UI, start it from the project folder:

```bash
dg dev
```

Open the `incoming_sales` asset and its **Checks** tab to see the latest result of the check and the metadata it reports.

<figure><img src="https://1928210810-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FJJOow5YZtrrxZJBNXV2D%2Fuploads%2Fgit-blob-2662b6aa06b10b305df9064db1860cfca245e6b8%2Ftest-data-quality-in-a-dagster-pipeline-dagster-incoming-check-failed.png?alt=media" alt="The Checks tab of the incoming_sales asset in the Dagster UI, with the blocking check incoming_sales_contract failed and one failed check in its metadata"><figcaption><p>The failed blocking check on <code>incoming_sales</code></p></figcaption></figure>

## Next steps

* Publish the results to Soda Cloud: pass `soda_cloud_file_path` and `publish=True` to `verify_contract_locally`. See [Verify a contract](https://docs.soda.io/soda-documentation/find/data-testing/git-managed-data-contracts/verify-a-contract.md).
* Add [Metric Monitoring](https://docs.soda.io/soda-documentation/find/data-observability/metric-monitoring-dashboard.md) to the loaded tables to detect anomalies over time.
* Verify the same contracts on every pull request. See [Test data during development](https://app.gitbook.com/s/JJOow5YZtrrxZJBNXV2D/data-owner/test-data-during-development).


---

# 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-a-dagster-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.
