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

# Test data quality in an ADF pipeline

Verify a data contract with reconciliation checks in a Synapse notebook, and stop an Azure Data Factory pipeline when copied data doesn't match its source.

This guide shows how to verify a Soda data contract from an Azure Data Factory (ADF) pipeline. A Notebook activity runs a Synapse notebook that verifies the contract after a Copy activity: reconciliation checks compare the copied data with its source, and column checks test its completeness. When a check fails, the notebook raises an error, the Notebook activity fails, and the activities that depend on its success don't run.

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

## Example pipeline

A data engineer copies the `retail_customers` table from a PostgreSQL database to a dedicated SQL pool in Azure Synapse Analytics. Before the rest of the pipeline uses the copy, they want to know that it matches the source and that customer IDs and country codes are complete.

The pipeline has these activities:

| Activity         | What it does                                                                            |
| ---------------- | --------------------------------------------------------------------------------------- |
| Copy data        | Copies `retail_customers` from PostgreSQL to the dedicated SQL pool                     |
| Notebook         | Runs the `verify_contract` notebook, which verifies the contract for `retail_customers` |
| Later activities | Run only when the Notebook activity succeeds                                            |

The Soda files live in a folder in the workspace's Azure Data Lake Storage Gen2 account:

```
soda/
├── ds_synapse.yml
├── ds_postgres.yml
├── sc_config.yml
└── contracts/
    └── retail_customers.yml
```

## Prerequisites

* An Azure Data Factory pipeline that copies data from PostgreSQL to a dedicated SQL pool in an Azure Synapse Analytics workspace
* An Apache Spark pool in the same workspace, on [Azure Synapse Runtime for Apache Spark 3.5](https://learn.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-35-runtime), that can reach the dedicated SQL pool and PostgreSQL. Runtime 3.4 reached end of support on March 31, 2026.
* An Azure Data Lake Storage Gen2 account that the Spark pool can read
* An Azure Key Vault with a linked service in the Synapse workspace
* A login on the dedicated SQL pool and on PostgreSQL with read access to the tables. See [Synapse](https://docs.soda.io/soda-documentation/developer-tools/data-source-reference-for-soda-core/synapse.md) and [PostgreSQL](https://docs.soda.io/soda-documentation/developer-tools/data-source-reference-for-soda-core/postgresql.md) for the permissions Soda needs.
* A Soda Cloud account and an API key. See [Generate API keys](https://docs.soda.io/apis/generate-api-keys.md).

## Add the Soda packages to the Spark pool

When a pipeline runs a notebook, Synapse disables the `%pip` and `%conda` commands, so install Soda on the Spark pool itself. The Spark 3.5 runtime includes Python 3.11 and ODBC Driver 18 for SQL Server, which `soda-synapse` uses by default.

{% hint style="info" %}
Reconciliation checks require the `soda-reconciliation` package from the [private PyPI](https://docs.soda.io/soda-documentation/connect/deployment-options/soda-python-libraries.md#private-pypi-installation-flow), with an **Enterprise** license. Need access to the private PyPI? [Contact us](mailto:support@soda.io).
{% endhint %}

Create a `requirements.txt` file with the Soda packages for the two data sources. Both are on PyPI.

{% code title="requirements.txt" %}

```
soda-synapse==4.27.0
soda-postgres==4.27.0
```

{% endcode %}

Upload the file to the Spark pool: in Synapse Studio, go to **Manage** > **Apache Spark pools**, open the **Packages** section of the pool, and upload `requirements.txt`. See [Manage libraries for Apache Spark pools](https://learn.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-manage-pool-packages).

On a computer with Python and pip, download the `soda-reconciliation` wheel from the private PyPI, with your Soda Cloud API key:

```bash
export SODA_API_KEY_ID="your_key_id"
export SODA_API_KEY_SECRET="your_key_secret"
pip download "soda-reconciliation==4.27.0" --no-deps -d soda-wheels \
  -i "https://${SODA_API_KEY_ID}:${SODA_API_KEY_SECRET}@enterprise.pypi.us.soda.io"
```

This command uses the US repository. For the EU region, use `enterprise.pypi.cloud.soda.io`.

Upload `soda-wheels/soda_reconciliation-4.27.0-py3-none-any.whl` as a workspace package in **Manage** > **Workspace packages**, then add it to the Spark pool's packages. See [Workspace packages](https://learn.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-manage-workspace-packages).

After you save the pool's packages, Synapse runs a job that installs them. New notebook sessions on the pool use the installed packages.

## Connect Soda to the data sources and Soda Cloud

Create one data source configuration file for the dedicated SQL pool and one for PostgreSQL. The values in `${env.…}` are read from environment variables, which the notebook sets from Key Vault.

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

```yaml
type: synapse
name: synapse_dwh
connection:
  host: ${env.SYNAPSE_HOST}
  port: 1433
  database: salesdwh
  authentication: sql
  user: ${env.SYNAPSE_USER}
  password: ${env.SYNAPSE_PASSWORD}
```

{% endcode %}

Set `SYNAPSE_HOST` to the endpoint of the dedicated SQL pool, `<workspace-name>.sql.azuresynapse.net`, and `database` to the name of the pool. The connection has no schema: each contract names the database and schema in its dataset name.

To connect with a Microsoft Entra service principal instead of a SQL login, replace the last three lines of the connection:

```yaml
  authentication: activedirectoryserviceprincipal
  client_id: ${env.SYNAPSE_CLIENT_ID}
  client_secret: ${env.SYNAPSE_CLIENT_SECRET}
```

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

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

{% endcode %}

Create a Soda Cloud configuration file next to them. 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 %}

> For all connection options, including encryption and other authentication methods, see [Synapse](https://docs.soda.io/soda-documentation/developer-tools/data-source-reference-for-soda-core/synapse.md).

## Write the contract

The contract defines the checks for the copied table in the dedicated SQL pool, the target dataset. The `columns` section tests completeness and uniqueness, and the `reconciliation` section compares the table with its source in PostgreSQL.

{% code title="soda/contracts/retail\_customers.yml" %}

```yaml
dataset: synapse_dwh/salesdwh/sales/retail_customers

columns:
  - name: customer_id
    checks:
      - missing:
          name: Customer IDs are complete
          threshold:
            metric: percent
            must_be_less_than_or_equal: 5
      - duplicate:
          name: Customer IDs are unique
          threshold:
            metric: percent
            must_be_less_than_or_equal: 5
  - name: country_code
    checks:
      - missing:
          name: Country codes are complete
          threshold:
            metric: percent
            must_be_less_than_or_equal: 5

reconciliation:
  sources:
    - name: crm
      dataset: crm_postgres/crm/public/retail_customers
  checks:
    - row_count_diff:
        name: Row count matches the source
        source: crm
    - duplicate_diff:
        name: Duplicate customer IDs match the source
        source: crm
        source_columns: [customer_id]
        target_columns: [customer_id]
    - metric_diff:
        name: Missing customer IDs match the source
        source: crm
        source_expression: SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END)
        target_expression: SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END)
    - metric_diff:
        name: Missing country codes match the source
        qualifier: country_code
        source: crm
        source_expression: SUM(CASE WHEN country_code IS NULL THEN 1 ELSE 0 END)
        target_expression: SUM(CASE WHEN country_code IS NULL THEN 1 ELSE 0 END)
```

{% endcode %}

A few details in the contract:

* Each source has a `name`, which the checks refer to with `source:`, and a `dataset` in the source data source.
* Without a `threshold`, a reconciliation check passes only when the difference between source and target is `0`.
* `metric_diff` compares the result of a SQL expression on each side. Here it compares the number of missing values in a column.
* Row-level `rows_diff` checks are not supported in Synapse.
* Both `metric_diff` checks are the same check type, so the second one needs a `qualifier` to give it a distinct identity. Without it, the verification fails with a `Duplicate identity` error.

If you don't have the `soda-reconciliation` package, remove the `reconciliation` section. The column checks don't need it.

> For every reconciliation check type, see [Reconciliation checks](https://docs.soda.io/soda-documentation/developer-tools/contract-language-reference/reconciliation-checks.md).

{% hint style="info" %}
Coming from Soda v3? `row_count diff` becomes `row_count_diff`, `duplicate_count(...)` with `diff` becomes `duplicate_diff`, and `missing_count(...)` with `diff` becomes a `metric_diff` with a source and a target expression. In the data source configuration, `username` becomes `user`, and the schema moves from the connection to the dataset name. For the rest of the changes, see [Migrate from v3 to v4](https://docs.soda.io/soda-documentation/govern/migrate-from-v3-to-v4.md).
{% endhint %}

## Test the contract from the command line

Before you add the contract to the pipeline, test it from a computer that can reach both databases. The computer needs Python 3.10, 3.11, or 3.12, and the [Microsoft ODBC driver for SQL Server](https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver17).

Install the same packages as the Spark pool in a virtual environment, from the folder that holds `requirements.txt` and `soda-wheels`:

```bash
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt soda-wheels/soda_reconciliation-4.27.0-py3-none-any.whl
```

Set the environment variables that the configuration files reference, then run these commands from the `soda` folder:

```bash
soda data-source test -ds ds_synapse.yml
soda data-source test -ds ds_postgres.yml
soda contract test -c contracts/retail_customers.yml
soda contract verify -ds ds_synapse.yml ds_postgres.yml -c contracts/retail_customers.yml
```

`soda contract test` checks the contract syntax without connecting to the data sources. `soda contract verify` takes both data source files, because the reconciliation checks read from both. It prints a table of results and exits with `0` when all checks pass, or `1` when a check fails. For the other exit codes, see [Exit codes](https://docs.soda.io/soda-documentation/developer-tools/cli-reference.md#exit-codes).

## Upload the Soda files

Upload the `soda` folder to a container in the storage account, for example a container named `soda`. The notebook copies the files from there to the local disk of the Spark pool, because Soda reads its configuration and contracts from local files.

## Create the notebook

The notebook reads the connection values from Key Vault, copies the Soda files to the local disk, and verifies one contract. It raises an error if the contract fails, which fails the Notebook activity.

In Synapse Studio, create a notebook named `verify_contract` and attach it to the Spark pool. Add two cells.

The first cell holds the notebook's parameters. Select **...** > **Toggle parameter cell** on the cell to make it the parameters cell. Replace `<storage-account>` with the name of your storage account.

{% code title="verify\_contract/parameters.py" %}

```python
contract = "retail_customers.yml"
soda_folder = "abfss://soda@<storage-account>.dfs.core.windows.net/soda"
publish = True
```

{% endcode %}

The second cell verifies the contract. Replace `<key-vault-name>` and `<key-vault-linked-service>` with the name of your Key Vault and of its linked service, and the secret names with the names of your secrets.

{% code title="verify\_contract/verify.py" %}

```python
import os

from notebookutils import mssparkutils
from soda_core import configure_logging
from soda_core.contracts import verify_contract_locally

KEY_VAULT = "<key-vault-name>"
KEY_VAULT_LINKED_SERVICE = "<key-vault-linked-service>"

# Read the connection values from Key Vault into the environment variables
# that the Soda configuration files reference.
secrets = {
    "SYNAPSE_HOST": "synapse-host",
    "SYNAPSE_USER": "synapse-user",
    "SYNAPSE_PASSWORD": "synapse-password",
    "POSTGRES_HOST": "postgres-host",
    "POSTGRES_USER": "postgres-user",
    "POSTGRES_PASSWORD": "postgres-password",
    "SODA_CLOUD_API_KEY_ID": "soda-cloud-api-key-id",
    "SODA_CLOUD_API_KEY_SECRET": "soda-cloud-api-key-secret",
}
for env_var, secret_name in secrets.items():
    os.environ[env_var] = mssparkutils.credentials.getSecret(
        KEY_VAULT, secret_name, KEY_VAULT_LINKED_SERVICE
    )

# Copy the Soda files from the storage account to the local disk.
local_folder = "/tmp/soda"
for path in ["ds_synapse.yml", "ds_postgres.yml", "sc_config.yml", f"contracts/{contract}"]:
    mssparkutils.fs.cp(f"{soda_folder}/{path}", f"file:{local_folder}/{path}")

configure_logging(verbose=False)
result = verify_contract_locally(
    data_source_file_paths=[
        f"{local_folder}/ds_synapse.yml",
        f"{local_folder}/ds_postgres.yml",
    ],
    contract_file_path=f"{local_folder}/contracts/{contract}",
    soda_cloud_file_path=f"{local_folder}/sc_config.yml",
    publish=publish,
)

if not result.is_ok:
    raise ValueError(
        f"Contract verification failed: {contract}: "
        f"{result.number_of_checks_failed} failed checks {result.get_errors_str()}"
    )
print(f"Contract verification passed: {contract}")
```

{% endcode %}

A few details in the notebook:

* `mssparkutils.credentials.getSecret` with a linked service reads the secret with the workspace identity. In a pipeline run, Synapse uses the workspace identity to access Key Vault, so grant it access to the secrets. To also run the notebook yourself, grant your own Microsoft Entra account access too. See [Microsoft Spark Utilities](https://learn.microsoft.com/en-us/azure/synapse-analytics/spark/microsoft-spark-utilities).
* `verify_contract_locally` gets both data source files in `data_source_file_paths`, because the reconciliation checks read from both data sources.
* `result.is_ok` is `False` when a check fails or the verification errors. See [Python API](https://docs.soda.io/apis/python-api.md).
* With `publish = False`, the notebook verifies the contract without sending results to Soda Cloud. It still needs the Soda Cloud API key, because Soda reads the dataset's configuration from Soda Cloud whenever it gets a Soda Cloud configuration file.

## Add the notebook to the pipeline

Add a Notebook activity that runs the notebook after the Copy activity:

1. In the pipeline, drag a **Notebook** activity from the **Synapse** group of the **Activities** pane to the canvas, and connect the Copy activity's success output to it.
2. On the **Azure Synapse Analytics (Artifacts)** tab, select or create the linked service for the Synapse workspace.
3. On the **Settings** tab, select the `verify_contract` notebook and the Spark pool. Add a base parameter named `contract` with the value `retail_customers.yml`. The name must match the variable in the parameters cell.
4. Connect the Notebook activity's success output to the activities that should run only when the data passes its checks.

When the pipeline runs, Data Factory adds a cell below the parameters cell that sets the base parameters, so the notebook verifies the contract you pass. To verify another dataset, add its contract to the `contracts` folder and add a Notebook activity that passes its file name.

> For every setting of the activity, see [Transform data by running a Synapse notebook](https://learn.microsoft.com/en-us/azure/data-factory/transform-data-synapse-notebook).

## Run the pipeline

When all checks pass, the notebook ends normally, the Notebook activity succeeds, and the pipeline continues.

When a check fails, the notebook raises an error and the Notebook activity fails. For example, if the copy loses a row, the check **Row count matches the source** fails, and the notebook stops with:

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

The activities connected to the Notebook activity's success output don't run. To see the error, open the pipeline run in the **Monitor** tab and select the output of the Notebook activity.

## Results in Soda Cloud

With `publish = True`, each run publishes its results to the `retail_customers` dataset in Soda Cloud. Open **Datasets**, select the dataset, and open the **Checks** tab to see the latest result of each check and its result history across runs.

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

## Next steps

* Learn when to compare metrics and when to compare rows in [Data reconciliation](https://docs.soda.io/soda-documentation/find/data-testing/data-reconciliation.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 Spark pool.


---

# 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-adf-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.
