Migration Test Framework (SQL Server <-> Snowflake)

Config-driven pytest framework for data migration validation and environment promotion validation.

Highlights

• Session-scoped source/target connections (prevents repeated Snowflake Okta browser prompts).
• Dynamic metadata discovery for tables, columns, and primary keys.
• Flow-based execution: migration (SQL Server -> Snowflake) and promotion (env -> env).
• Global check profiles + app-specific overrides.
• App-specific ETL/business rules per flow.
• JSON report artifact per run.

Project Layout

configs/
  shared/
    check_profiles.yaml
  apps/
    <app>.yaml

Quick Start

1. Install dependencies:

cd migration_test_framework
python3 -m venv .venv
source .venv/bin/activate
pip install -e .

2. Copy sample app config and update placeholders:

cp configs/apps/sample_app.yaml configs/apps/<your_app>.yaml

3. Run migration flow (single env):

pytest --app <your_app> --flow migrate_sql_to_snowflake --env dev

4. Run promotion flow (env to env):

pytest --app <your_app> --flow promote_snowflake --left-env dev --right-env test

Common Commands

• Migration smoke run over small subset:

pytest --app <your_app> --flow migrate_sql_to_snowflake --env dev --max-tables 10 -m smoke

• Promotion quick validation test -> stg:

pytest --app <your_app> --flow promote_snowflake --left-env test --right-env stg

• Override check profile at runtime:

pytest --app <your_app> --flow promote_snowflake --left-env stg --right-env prod --check-profile promotion_quick

Config Model

1) Shared check profiles (configs/shared/check_profiles.yaml)

Keep global test behavior for migration/promotion here.

2) App config (configs/apps/<app>.yaml)

Each app file defines:

• flows: how left/right systems are paired
• environments: endpoint details per env (dev/test/stg/prod)
• table_selection: include/exclude/mappings
• rulesets: app-specific ETL/business validations
• optional check_profiles: app override of shared defaults

Snowflake Okta Notes

• Use authenticator: externalbrowser in Snowflake connections.
• Connections are pytest session-scoped, so browser auth happens once per run.
• If using pytest -n N, each worker process may open its own auth session.

Reports

Each run writes JSON report to:

reports/<app>_<flow>_<left_env>_to_<right_env>_migration_report.json

Interactive Playground

A FastAPI-based browser UI for searching docs, generating app configs, and running tests.

New Machine Setup (run once)

macOS

1. Install playground dependencies:

cd migration_test_framework
source .venv/bin/activate
pip install -r mcp/requirements.txt

Windows

1. Install playground dependencies:

cd migration_test_framework
py -3.12 -m pip install -r mcp/requirements.txt

Run the Playground

macOS:

python3.12 mcp/playground.py

Windows:

py -3.12 mcp/playground.py

Then open: http://localhost:8000

First-Time Index

• Go to Index & Search tab → click Index Docs
• Expected: Indexed XXXX chunks from X files [sentence-transformers]
• Model (~90MB) downloads automatically and caches at ~/.cache/huggingface/hub/ — no re-download on subsequent runs

Notes

• Python 3.12 required
• Semantic search uses sentence-transformers/all-MiniLM-L6-v2 (understands meaning, not just keywords)

Pre-Push Security Checklist

• Do not commit real credentials in configs/apps/*.yaml.
• Keep real secrets in local-only files like configs/apps/<app>_local.yaml (already gitignored).
• Keep generated reports local (reports/ is gitignored).
• Prefer Snowflake externalbrowser locally until CI service auth is available.