> 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/test-data-before-migration.md).

# Test data before migration

Check data quality in the source before a migration, then reconcile the target with the source to prove the data arrived intact.

This guide shows how to use Soda in a data migration. Before you migrate, a contract on the source table confirms that the data is fit to move. After you migrate, a contract on the target table uses reconciliation checks to compare it with the source: the schema, the row count, a total, and every row.

Run the reconciliation after each migration run in staging, fix what it reports, and run it again after the production migration.

{% hint style="info" %}
Reconciliation checks require the `soda-reconciliation` package from the private PyPI, which is available with an Enterprise license. Need access? [Contact us](mailto:support@soda.io).
{% endhint %}

## Example migration

A team moves the `fact_internet_sales` table from a legacy PostgreSQL database to a new PostgreSQL warehouse. Soda connects to both as two data sources, `legacy` and `warehouse`. The source and the target can also be different engines, for example a migration from PostgreSQL to Snowflake: give each data source its own configuration file, and use each engine's dataset name in the contracts.

The project has this layout:

```
soda/
├── legacy.yml
├── warehouse.yml
├── sc_config.yml
└── contracts/
    ├── legacy_fact_internet_sales.yml
    └── warehouse_fact_internet_sales.yml
```

## Install Soda

Install the Soda package for your data sources and the reconciliation extension from the private PyPI. Use your Soda Cloud API key as the credentials. This example uses the US region; for the EU region, use `enterprise.pypi.cloud.soda.io`.

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

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

## Connect to the source and the target

Create one data source configuration file for each database. The values in `${env.…}` are read from environment variables.

{% code title="soda/legacy.yml" %}

```yaml
type: postgres
name: legacy
connection:
  host: ${env.LEGACY_HOST}
  port: ${env.LEGACY_PORT}
  user: ${env.LEGACY_USER}
  password: ${env.LEGACY_PASSWORD}
  database: adventureworks
```

{% endcode %}

{% code title="soda/warehouse.yml" %}

```yaml
type: postgres
name: warehouse
connection:
  host: ${env.WAREHOUSE_HOST}
  port: ${env.WAREHOUSE_PORT}
  user: ${env.WAREHOUSE_USER}
  password: ${env.WAREHOUSE_PASSWORD}
  database: adventureworks
```

{% endcode %}

Create a Soda Cloud configuration file to publish the results. 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 the connections from the `soda` folder:

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

## Check the source before migrating

Data that is wrong in the source is wrong in the target. Verify the source table first, and fix what fails before you migrate.

{% code title="soda/contracts/legacy\_fact\_internet\_sales.yml" %}

```yaml
dataset: legacy/adventureworks/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 %}

```bash
soda contract verify -ds legacy.yml -c contracts/legacy_fact_internet_sales.yml -sc sc_config.yml --publish
```

> To create a first contract for an existing table, you can also use Contract Autopilot in Soda Cloud.

## Reconcile the target with the source

The target contract names the source table under `reconciliation.sources` and compares the two with four checks:

* `schema_diff` compares the column names and data types.
* `row_count_diff` compares the number of rows.
* `metric_diff` compares a total computed on each side.
* `rows_diff` matches the rows by key and compares the values in each row.

{% code title="soda/contracts/warehouse\_fact\_internet\_sales.yml" %}

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

columns:
  - name: sales_order_number
  - name: product_key
  - name: order_quantity
  - name: sales_amount

reconciliation:
  sources:
    - name: legacy
      dataset: legacy/adventureworks/public/fact_internet_sales
  checks:
    - schema_diff:
        source: legacy
        name: Schema matches the legacy table
    - row_count_diff:
        source: legacy
        name: Row count matches the legacy table
        threshold:
          must_be: 0
    - metric_diff:
        source: legacy
        name: Sales total matches the legacy table
        source_expression: SUM(sales_amount)
        target_expression: SUM(sales_amount)
        threshold:
          must_be: 0
    - rows_diff:
        source: legacy
        name: Every sale matches the legacy table
        source_key_columns: [sales_order_number]
        target_key_columns: [sales_order_number]
        source_columns: [product_key, order_quantity, sales_amount]
        target_columns: [product_key, order_quantity, sales_amount]
        threshold:
          must_be: 0
```

{% endcode %}

Pass both data source files to `--data-source`, so Soda can query the source and the target:

```bash
soda contract verify -ds legacy.yml warehouse.yml -c contracts/warehouse_fact_internet_sales.yml -sc sc_config.yml --publish
```

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

## Results

When the migration copies the table correctly, all four checks pass.

When it doesn't, the checks show what went wrong. For example, a migration that maps `sales_amount` to an integer column rounds every amount. All the rows arrive, so **Row count matches the legacy table** passes. **Schema matches the legacy table** fails on the data type, **Sales total matches the legacy table** fails on the rounded total, and **Every sale matches the legacy table** fails on the rows whose amounts changed.

<figure><img src="https://1928210810-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FJJOow5YZtrrxZJBNXV2D%2Fuploads%2Fgit-blob-c3d657bc4af39318e7c2709640e0e23ec8a4f93f%2Ftest-data-before-migration-migration-reconciliation-failed.png?alt=media" alt="The Checks tab of the warehouse fact_internet_sales dataset in Soda Cloud, with the row count check passed and the schema, sales total and row checks failed"><figcaption><p>Reconciliation results after a faulty migration</p></figcaption></figure>

## Next steps

* Learn the use cases and performance considerations of [Data reconciliation](https://docs.soda.io/soda-documentation/find/data-testing/data-reconciliation.md).
* Run the reconciliation as a task after the migration job. See [Test data quality in an Airflow pipeline](https://docs.soda.io/guides-and-tutorials/data-owner-producer/test-data-quality-in-an-airflow-pipeline.md).
* Keep verifying the migrated table after go-live on a [schedule](https://docs.soda.io/soda-documentation/developer-tools/contract-language-reference.md#schedule-a-contract-verification).


---

# 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/test-data-before-migration.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.
