> 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-platform/route-failed-rows-to-your-warehouse.md).

# Route failed rows to your warehouse

Store the rows that fail your checks in your own PostgreSQL database with the Diagnostics Warehouse, so Soda Cloud receives the check results but not the rows.

When a check fails, the rows behind the failure show you what to fix. If the data is sensitive, you may want those rows to stay in your own infrastructure. With the Diagnostics Warehouse, Soda Core writes the failed rows to a database you control, and Soda Cloud receives only the check results.

In this guide, a contract checks the `fact_internet_sales` table in a PostgreSQL database, `sales`. The failed rows go to a second database on the same server, `quality`. You run the verification with Soda Core, then query the failed rows with SQL.

{% hint style="info" %}
The Diagnostics Warehouse and the private PyPI are available on license plans. Need access? [Contact us](mailto:support@soda.io).
{% endhint %}

## Prerequisites

* Python 3.10 or later, and `psql` to query the results.
* A Soda Cloud account and an API key. See [Generate API keys](https://docs.soda.io/apis/generate-api-keys.md).
* To publish results for a new data source, the global permission **Create new datasets and data sources with Core/Library**. To turn on the Diagnostics Warehouse, the **Manage datasource settings** permission on the data source and **Configure dataset** on the dataset. See [Global and dataset roles](https://docs.soda.io/soda-documentation/govern/organization-and-admin-settings/global-and-dataset-roles.md).
* A PostgreSQL user that reads the `sales` database, and a user that can create schemas, tables, and views in the `quality` database and write to them. See [Diagnostics Warehouse required permissions](broken://spaces/A2PmHkO5cBgeRPdiPPOG/pages/dWoYeagXP1e77Kf8TS5K#diagnostics-warehouse-required-permissions).

## Install Soda

The Diagnostics Warehouse is part of the `soda` package on the private PyPI. Install it with the package for PostgreSQL. The index takes your Soda Cloud API key as the credentials, from the environment variables `SODA_CLOUD_API_KEY_ID` and `SODA_CLOUD_API_KEY_SECRET`. This example uses the US region; for the EU region, use `enterprise.pypi.cloud.soda.io`.

```bash
pip install "soda>=4" "soda-postgres>4" \
  -i "https://${SODA_CLOUD_API_KEY_ID}:${SODA_CLOUD_API_KEY_SECRET}@enterprise.pypi.us.soda.io" \
  --extra-index-url=https://pypi.cloud.soda.io/simple
```

> For the index of your license and region, see [Soda Python libraries](https://docs.soda.io/soda-documentation/connect/deployment-options/soda-python-libraries.md#private-pypi-installation-flow).

## Connect Soda Core

Create a data source configuration file for the data that the contract checks. The `name` becomes the name of the data source in Soda Cloud. The values in `${env.…}` are read from environment variables.

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

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

{% endcode %}

Create a second configuration file for the Diagnostics Warehouse. It connects to the `quality` database.

{% code title="ddwh.yml" %}

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

{% endcode %}

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

{% code title="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 the three connections:

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

> For every connection parameter, see [PostgreSQL](https://docs.soda.io/soda-documentation/developer-tools/data-source-reference-for-soda-core/postgresql.md).

## Write the contract

The contract checks that every sale has an order number, a product, and an amount above zero. The `missing`, `duplicate`, and `failed_rows` checks evaluate the table row by row, so their failed rows can be stored. Aggregate checks, such as `row_count`, have no failed rows.

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

```yaml
dataset: orders_postgres/sales/public/fact_internet_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
  - name: sales_amount
    checks:
      - failed_rows:
          name: All sales have a non-zero order amount
          expression: sales_amount <= 0

checks:
  - row_count:
      name: Sales are present
      threshold:
        must_be_greater_than: 0
```

{% endcode %}

Check that the contract is valid:

```bash
soda contract test -c contracts/fact_internet_sales.yml
```

> For the check types that store failed rows, see [Which check types produce failed rows?](broken://spaces/A2PmHkO5cBgeRPdiPPOG/pages/E1KTiEL4c6zytpedbEZg#which-check-types-produce-failed-rows)

## Publish the first results

Soda Core reads the Diagnostics Warehouse settings of the dataset from Soda Cloud, so the data source and the dataset must exist there first. Verify the contract and publish the results:

```bash
soda contract verify -ds ds_config.yml -c contracts/fact_internet_sales.yml -sc sc_config.yml --publish -dw ddwh.yml
```

`-dw` points to the Diagnostics Warehouse configuration file. Soda Cloud creates the data source `orders_postgres` and the dataset `fact_internet_sales`. The Diagnostics Warehouse is not on yet, so the output ends with `Skipping failed rows extraction: Diagnostics not enabled`.

## Turn on the Diagnostics Warehouse

The settings live in Soda Cloud: the data source decides where Soda writes and whether it collects failed rows, and the dataset decides how to store them. The following script sets both through the Soda Cloud API. It reads the API key from the same environment variables as `sc_config.yml`, and uses the `requests` package, which Soda installs. For the EU region, set `API` to `https://cloud.soda.io/api/v1`.

{% code title="configure\_dwh.py" %}

```python
"""Store the failed rows of fact_internet_sales in the Diagnostics Warehouse."""
import json
import os

import requests

API = "https://cloud.us.soda.io/api/v1"
AUTH = (os.environ["SODA_CLOUD_API_KEY_ID"], os.environ["SODA_CLOUD_API_KEY_SECRET"])


def call(method, path, body=None):
    response = requests.request(method, f"{API}/{path}", auth=AUTH, json=body)
    response.raise_for_status()
    return response.json()


datasource = next(d for d in call("GET", "datasources?size=100")["content"]
                  if d["name"] == "orders_postgres")
dataset = next(d for d in call("GET", "datasets?datasourceName=orders_postgres&size=100")["content"]
               if d["datasource"]["name"] == "orders_postgres" and d["name"] == "fact_internet_sales")

# Data source: write diagnostics through the connection in ddwh.yml.
with open("ddwh.yml") as f:
    connection = f.read()
call("POST", f"datasources/{datasource['id']}/diagnosticsWarehouse", {
    "enabled": True,
    "reuseDatasource": False,
    "configurationFileContents": connection,
    "scanAndResultsConfiguration": {"enabled": True},
    "failedRowsConfiguration": {
        "enabled": True,
        "exposeQueries": True,
        "locationMessage": "Failed rows are stored in the quality database.",
    },
})

# Dataset: store the full failed rows, identified by the order number.
call("POST", f"datasets/{dataset['id']}", {
    "diagnosticsWarehouse": {
        "failedRowsConfiguration": {
            "enabled": True,
            "storageStrategy": "fullFailedRows",
            "uniqueKeyColumnNames": ["sales_order_number"],
        }
    }
})

effective = call("GET", f"datasets/{dataset['id']}/diagnosticsWarehouse")["effectiveConfiguration"]
print("Dataset ID:", dataset["id"])
print(json.dumps(effective["failedRowsConfiguration"], indent=2))
```

{% endcode %}

```bash
python configure_dwh.py
```

The script makes these settings:

| Setting                               | Value                    | Effect                                                                                                                                                               |
| ------------------------------------- | ------------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `reuseDatasource`                     | `false`                  | Soda writes to a different connection, the `quality` database, instead of the database it checks. Soda Cloud requires the connection in `configurationFileContents`. |
| `scanAndResultsConfiguration.enabled` | `true`                   | Soda stores the scans and check results.                                                                                                                             |
| `failedRowsConfiguration.enabled`     | `true`                   | Soda collects failed rows for the datasets of the data source that have it on.                                                                                       |
| `exposeQueries`                       | `true`                   | The **Failed Rows** tab of a check in Soda Cloud offers the SQL queries that read its failed rows.                                                                   |
| `locationMessage`                     | Text                     | The message the **Failed Rows** tab shows instead of the rows.                                                                                                       |
| `storageStrategy`                     | `fullFailedRows`         | Soda stores the keys of the failed rows and full copies of the rows. With `uniqueKeys`, Soda stores only the keys.                                                   |
| `uniqueKeyColumnNames`                | `["sales_order_number"]` | The columns that identify a row. Without unique keys, Soda does not store failed rows.                                                                               |

The data source request replaces the stored configuration, so a field you leave out is cleared.

The script prints the configuration that applies to the dataset. `failedRowSamples` is off, so Soda sends no sample of the failed rows to Soda Cloud:

```json
{
  "enabled": true,
  "storageStrategy": "fullFailedRows",
  "uniqueKeyColumnNames": [
    "sales_order_number"
  ],
  "failedRowSamples": {
    "enabled": false,
    "sampleSize": 100
  }
}
```

{% hint style="info" %}
Failed row samples are an opt-in feature that copies a sample of the failed rows to Soda Cloud. When your organization has them, leave **Allow sending failed row samples to Soda Cloud** cleared in the Diagnostics Warehouse settings of the data source. See [Failed row samples](broken://spaces/A2PmHkO5cBgeRPdiPPOG/pages/r1iLFOJA0mD4a674X3en).
{% endhint %}

In Soda Cloud, the dataset's **Settings** tab shows the result under **Diagnostics Warehouse Configuration**: **Collect failed rows** is on, with the storage strategy **Store full failed rows** and the unique key `sales_order_number`. You can change these settings there and click **Save diagnostics warehouse**.

<figure><img src="https://1928210810-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FJJOow5YZtrrxZJBNXV2D%2Fuploads%2Fgit-blob-2b32e208bf3e64a730737e12a3347bbe4c8af5b7%2Froute-failed-rows-to-your-warehouse-dataset-diagnostics-warehouse.png?alt=media" alt="The Diagnostics Warehouse Configuration of fact_internet_sales, with Collect check results and scans and Collect failed rows on, the storage strategy Store full failed rows, and the unique key sales_order_number"><figcaption><p>Diagnostics Warehouse settings of the dataset</p></figcaption></figure>

> To make the same settings in the Soda Cloud interface, or to use the same connection as the data source, see [Configure Diagnostics Warehouse](broken://spaces/A2PmHkO5cBgeRPdiPPOG/pages/E1KTiEL4c6zytpedbEZg).

## Store failed rows

A sale with an amount of zero arrives in `fact_internet_sales`. Run the same command again:

```bash
soda contract verify -ds ds_config.yml -c contracts/fact_internet_sales.yml -sc sc_config.yml --publish -dw ddwh.yml
```

The check `All sales have a non-zero order amount` fails, and the command exits with code `1`. After it sends the results to Soda Cloud, Soda Core connects to the `quality` database, creates its default schema, `soda_diagnostics`, and stores the failed row:

```
👌 Results sent to Soda Cloud (1 file)
Starting to extract failed rows for dataset orders_postgres/sales/public/fact_internet_sales
...
Inserted 1 keys into "quality"."soda_diagnostics"."fk_5d1bb51bac864bb19e8f7c8b8cb20481_v0000" for check 'All sales have a non-zero order amount'
...
Inserted 1 failed rows into the "quality"."soda_diagnostics"."fr_5d1bb51bac864bb19e8f7c8b8cb20481_v0000" table.
...
Updated post processing stage 'diagnosticWarehouse' to state 'completed' for scan 0a8b6e13-2a7e-4d02-bc39-7002134142cd
```

Soda writes the Diagnostics Warehouse only when you publish the results with `--publish`.

## Query the failed rows

Soda stores the failed rows of each dataset in two tables, named after the dataset ID without hyphens:

* `fk_<dataset_id>`: the unique keys of the failed rows, one row per failed row and check.
* `fr_<dataset_id>`: a full copy of each failed row, once per scan.

Each table has a view with the same name, without the version suffix `_v0000`. The `check_results` table lists the views of each check. List the checks that failed:

{% code title="failed\_checks.sql" %}

```sql
SELECT check_name, failed_rows_count, failed_keys_view_name, failed_rows_view_name
FROM soda_diagnostics.check_results
WHERE outcome = 'fail';
```

{% endcode %}

```bash
psql -h "$POSTGRES_HOST" -p "$POSTGRES_PORT" -U "$POSTGRES_USER" -d quality -f failed_checks.sql
```

```
               check_name               | failed_rows_count |        failed_keys_view_name        |        failed_rows_view_name
----------------------------------------+-------------------+-------------------------------------+-------------------------------------
 All sales have a non-zero order amount |                 1 | fk_5d1bb51bac864bb19e8f7c8b8cb20481 | fr_5d1bb51bac864bb19e8f7c8b8cb20481
(1 row)
```

To read the rows, join the two views with the check results and the scans. Replace `<dataset_id>` with the ID from the view names.

{% code title="failed\_rows.sql" %}

```sql
SELECT s.execution_time,
       cr.check_name,
       fr.sales_order_number,
       fr.product_key,
       fr.order_quantity,
       fr.sales_amount
FROM soda_diagnostics.fk_<dataset_id> AS fk
JOIN soda_diagnostics.fr_<dataset_id> AS fr
  ON fr.__soda_row_id = fk.__soda_row_id
 AND fr.__soda_scan_id = fk.__soda_scan_id
JOIN soda_diagnostics.check_results AS cr
  ON cr.check_id = fk.__soda_check_id
 AND cr.scan_id = fk.__soda_scan_id
JOIN soda_diagnostics.scans AS s
  ON s.scan_id = fk.__soda_scan_id
ORDER BY s.execution_time DESC;
```

{% endcode %}

```bash
psql -h "$POSTGRES_HOST" -p "$POSTGRES_PORT" -U "$POSTGRES_USER" -d quality -f failed_rows.sql
```

```
      execution_time       |               check_name               | sales_order_number | product_key | order_quantity | sales_amount
---------------------------+----------------------------------------+--------------------+-------------+----------------+--------------
 2026-10-23T10:13:24+00:00 | All sales have a non-zero order amount | SO49999            |         101 |              1 |         0.00
(1 row)
```

Later scans add their failed rows to the same tables as long as the table's columns and the unique keys stay the same, so the query also shows when a row failed before.

> For every table and column, see [Diagnostics Warehouse data model](broken://spaces/A2PmHkO5cBgeRPdiPPOG/pages/x7jNHrVrxhXlQ9WFF0Qa).

## Check results in Soda Cloud

In Soda Cloud, open the dataset's **Checks** tab and click the failed check. The **Failed Rows** tab shows **Failed rows are stored externally** with the location message instead of the rows. **View Failed Rows Queries** shows the SQL queries that read the rows from the `quality` database.

<figure><img src="https://1928210810-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FJJOow5YZtrrxZJBNXV2D%2Fuploads%2Fgit-blob-67af93a5de3aea0bc6b3f1681fca931d99a54636%2Froute-failed-rows-to-your-warehouse-check-failed-rows.png?alt=media" alt="The Failed Rows tab of the check All sales have a non-zero order amount, with the message Failed rows are stored externally, the location message, and the button View Failed Rows Queries"><figcaption><p>The Failed Rows tab of the failed check</p></figcaption></figure>

## Next steps

* Find patterns in failed rows and fix them at the source. See [Operational data quality](broken://spaces/A2PmHkO5cBgeRPdiPPOG/pages/vLjYDVzFlMjsjmjOcQI5).
* Limit how many failed rows a check stores. See [Configure Diagnostics Warehouse](broken://spaces/A2PmHkO5cBgeRPdiPPOG/pages/E1KTiEL4c6zytpedbEZg).
* Build reports on the check results. See [Data quality reporting](broken://spaces/A2PmHkO5cBgeRPdiPPOG/pages/b7114fd9415b44128d58c6f572b99d73bb1bfd36).


---

# 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-platform/route-failed-rows-to-your-warehouse.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.
