SQL Data Validation Suite (MySQL + PyTest)

A PyTest suite that validates data quality on a sample health-plan member/claims MySQL database: referential integrity, required-field checks, duplicate detection, and business rule validation (e.g. claim dates, status values, amounts).

The seed data intentionally plants five data quality issues so the suite has real problems to catch — see "About the seed data" below.

What's validated

Check	Rule
Orphaned claims (member)	Every claims.member_id must exist in members
Orphaned claims (provider)	Every claims.provider_id must exist in providers
Required fields	first_name, last_name, date_of_birth must not be null
Duplicate identities	No two members should share name + date of birth
Valid member status	status must be Active, Inactive, or Pending
Valid claim status	claim_status must be Approved, Denied, or Pending
Claim date vs enrollment	A claim can't predate the member's enrollment date
Non-negative amounts	claim_amount must be >= 0
Unique claim IDs	Sanity check on the primary key

Project structure

sql-data-validation/
├── sql/
│   ├── schema.sql              # Table definitions (members, providers, claims)
│   └── seed_data.sql           # Sample data with 5 intentional issues
├── tests/
│   ├── conftest.py             # PyTest fixture: MySQL connection
│   └── test_data_validation.py # The validation checks
├── .github/workflows/
│   └── sql-validation.yml      # CI: spins up MySQL, loads data, runs tests
├── docker-compose.yml          # Local MySQL for development
├── requirements.txt
├── .env.example
├── .gitignore
└── README.md

1. Local setup

1. Start a local MySQL instance with Docker (schema and seed data load automatically on first start):

   docker compose up -d
   

2. Create a virtual environment and install dependencies:

   python -m venv venv
   venv\Scripts\activate        # Windows
   source venv/bin/activate     # macOS/Linux
   python -m pip install -r requirements.txt
   

3. Copy .env.example to .env (defaults match docker-compose.yml, so no changes needed unless you're pointing at a different database):

   cp .env.example .env
   

4. Run the tests:

   pytest tests/ -v
   

Expected result: 4 passed, 5 xfailed. The 5 "xfailed" (expected failure) tests are the ones catching the planted data issues — see below.

About the seed data

sql/seed_data.sql deliberately includes 5 data quality issues so the suite demonstrates real detection, not just passing against clean fixtures:

• M005 / M006 — duplicate person (same name + date of birth, two member IDs)
• M007 — invalid status value ("Actve" instead of "Active")
• C006 — references a member (M999) that doesn't exist
• C007 — claim dated before the member's enrollment date
• C008 — negative claim amount

The corresponding tests are marked @pytest.mark.xfail with a reason explaining exactly which planted issue they catch — this keeps the build green in CI while still surfacing the full assertion detail (the actual violating row) in verbose test output. Point the suite at real, clean data and those markers can be removed — every test is written to pass against valid data.

2. Continuous Integration

.github/workflows/sql-validation.yml spins up a MySQL service container, loads the schema and seed data, and runs the full suite on every push/PR to main. No local setup needed — it runs automatically once pushed to GitHub.

3. Push to GitHub

1. Confirm nothing sensitive is staged:

   git status
   

2. Initialize and commit:

   git init
   git add .
   git commit -m "Project 6: SQL data validation suite (PyTest + MySQL)"
   

3. Create a new repo on GitHub, then connect and push:

   git remote add origin https://github.com/<your-username>/sql-data-validation.git
   git branch -M main
   git push -u origin main
   

4. Check the Actions tab — the workflow should run automatically and pass.