Companies data validation

[ukrainets]

validate_companies.py — CSV Schema & Data Quality Validator

Summary

Add a standalone helper script validate_companies.py that validates
companies.csv against a defined schema using Pandera. The script is
contributor-facing — run manually before submitting a PR to catch schema
violations, missing data, duplicates, and invalid values early.

---

Background

• Data source: data/companies.csv (primary input to the scanner)
• Validation is standalone and manual — never called from the scan loop
• Follows the existing helper script pattern (verify_no_click.py,
  populate_api_urls.py)
• Exits 0 on success, 1 on failure
• Uses Pandera lazy validation — collects ALL errors in a single pass
  before reporting, so contributors see everything at once

---

⚠️ Python Version Note

Pandera requires Python ≥ 3.10. The project's current README.md
states Python 3.9+. Update README.md prerequisites to reflect 3.10+
as part of this ticket.

---

New Dependencies

Add to requirements.txt:

pandera[pandas]>=0.20
pandas>=2.0

---

Validation Rules

Column	Required	Rules
id	Yes	UUID v4 format, unique across all rows
company_name	Yes	Non-empty string, unique across all rows
open_positions_url	Yes	Non-empty string, starts with http, unique across all rows
hr_platform	Yes	One of known values (case-insensitive)
no_click	Yes	TRUE or FALSE only
api_url	Conditional	If hr_platform == greenhouse → must be non-empty and start with https://boards-api.greenhouse.io/
rating	No	If present, numeric value between 1 and 5 inclusive
website	No	If present, starts with http

Known hr_platform values:
greenhouse, ashby, lever, workable, workday, rippling,
custom, icims, taleo, phenom, adp, dayforce, gem

---

Usage

python validate_companies.py
python validate_companies.py --input data/companies.csv

---

BDD Scenarios

Feature: companies.csv schema and data quality validation

---

Scenario: Valid file passes validation

Given a companies.csv with all rows conforming to the schema
When I run python validate_companies.py
Then the script exits with code 0
And prints "✅ Validation passed. N rows checked."

---

Scenario: Missing required column fails validation

Given a companies.csv that is missing the `hr_platform` column
When I run python validate_companies.py
Then the script exits with code 1
And prints an error identifying the missing column

---

Scenario: Invalid hr_platform value fails validation

Given a companies.csv with a row where hr_platform is "bamboohr"
When I run python validate_companies.py
Then the script exits with code 1
And prints an error identifying the row and the invalid value

---

Scenario: no_click value other than TRUE/FALSE fails validation

Given a companies.csv with a row where no_click is "Yes"
When I run python validate_companies.py
Then the script exits with code 1
And prints an error identifying the row and the invalid value

---

Scenario: Greenhouse company missing api_url fails validation

Given a companies.csv with a row where hr_platform is "greenhouse"
And the api_url column is empty for that row
When I run python validate_companies.py
Then the script exits with code 1
And prints an error identifying the company name and the missing api_url

---

Scenario: Greenhouse company with wrong api_url domain fails validation

Given a companies.csv with a greenhouse row where api_url does not start
with "https://boards-api.greenhouse.io/"
When I run python validate_companies.py
Then the script exits with code 1
And prints an error identifying the row and the invalid api_url

---

Scenario: Duplicate open_positions_url fails validation

Given a companies.csv with two rows sharing the same open_positions_url
When I run python validate_companies.py
Then the script exits with code 1
And prints an error identifying the duplicate URL

---

Scenario: Duplicate id fails validation

Given a companies.csv with two rows sharing the same id value
When I run python validate_companies.py
Then the script exits with code 1
And prints an error identifying the duplicate id

---

Scenario: Invalid UUID in id column fails validation

Given a companies.csv with a row where id is "not-a-uuid"
When I run python validate_companies.py
Then the script exits with code 1
And prints an error identifying the row and the malformed id

---

Scenario: Rating outside 1–5 range fails validation

Given a companies.csv with a row where rating is "6"
When I run python validate_companies.py
Then the script exits with code 1
And prints an error identifying the row and the out-of-range rating

---

Scenario: All errors reported in a single pass

Given a companies.csv with 3 rows containing different validation errors
When I run python validate_companies.py
Then the script exits with code 1
And prints all 3 errors before exiting
And does not stop at the first error

---

Scenario: Custom --input path is respected

Given a valid companies CSV at path "tests/test_data/companies.csv"
When I run python validate_companies.py --input tests/test_data/companies.csv
Then the script exits with code 0

---

Implementation Notes

• Use pandera.pandas (not top-level pandera — deprecated in v0.24+)
• Use DataFrameModel (class-based API) for readability
• Use lazy=True in .validate() to collect all errors before reporting
• The conditional api_url rule for greenhouse rows requires a
  dataframe-level check (@pa.dataframe_check), not a column-level check
• hr_platform comparison must be case-insensitive — normalize to
  lowercase before the isin check
• rating column: read as string from CSV, cast to float for range check,
  skip validation if empty string (field is optional)
• UUID check: use a regex pattern
  ^[0-9a-f]{8}-[0-9a-f]{4}-4[0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$
• Print a clean summary on success; on failure print Pandera's
  failure_cases table filtered to relevant columns
  (column, check, failure_case, index)

---

Files to Create / Modify

File	Action
validate_companies.py	Create — new standalone helper
requirements.txt	Modify — add pandera[pandas]>=0.20, pandas>=2.0
README.md	Modify — update Python prerequisite from 3.9+ to 3.10+; add validate_companies.py to Project Structure section

---

Out of Scope

• Validation of sqa_titles.csv or match.csv
• Running validation automatically inside the scan loop
• CI/CD integration