Skip to content

TESTING.md

Latest commit

 

History

History
138 lines (97 loc) · 3.95 KB

TESTING.md

File metadata and controls

138 lines (97 loc) · 3.95 KB
Raw

Testing Guide

This document helps reviewers verify the project logic and output structure without needing to download or inspect the full 16,938-row dataset.

1. Data Validation Checks

Run the validation script before loading data:

python python/data_validation.py

Expected outcomes

  • The script should report the number of rows loaded.
  • It should confirm the expected columns are present.
  • It should flag any missing values, negative values, or rows with both brand and generic names missing.
  • If the file is structurally correct, the report should finish without unexpected errors.

Example validation summary

  • Rows loaded: 16,938
  • Columns detected: 36
  • Issues found: 0 (or a small number of warnings, if present)

2. SQL Load and Table Checks

After running the SQL load process, verify that the main table contains data:

SELECT COUNT(*) AS total_rows FROM drug_spending;

Expected row count

  • Approximately 16,938 rows

Additional spot checks

SELECT *
FROM drug_spending
LIMIT 5;

Expected result:

  • A small sample of rows showing populated brand, generic, manufacturer, and spending columns.

3. Query 1: Top 10 Drugs by 2023 Spending

Run the query from SQL/03_analysis_queries.sql.

Expected behavior

  • Returns 10 rows.
  • Rows should be ordered by total spending descending.
  • The first row should be the highest-spending drug in 2023.

Example output shape

Brnd_Name Gnrc_Name Mftr_Name total_spending_2023 pct_of_total_spending
Example Drug A Example Generic A Manufacturer X 1,500,000.00 8.50

4. Query 2: Total Spending by Year

Run the annual aggregation query.

Expected behavior

  • Returns 5 rows, one per year from 2019 to 2023.
  • Spending should generally increase over time if the dataset is loaded correctly.

Expected row count

  • 5 rows

Example output shape

year total_spending total_claims total_dosage_units
2019 100000000.00 500000 10000000
2020 105000000.00 510000 10100000
2021 110000000.00 520000 10200000
2022 115000000.00 530000 10300000
2023 120000000.00 540000 10400000

5. Query 3: Manufacturer Revenue Rankings

Run the manufacturer ranking query.

Expected behavior

  • Returns up to 5 rows.
  • Rows should be ordered by total revenue descending.
  • distinct_drugs should be a positive integer.

Expected row count

  • 5 rows, or fewer if fewer manufacturers are present in the dataset.

6. Query 4: Brand vs Generic Comparisons

Run the brand-versus-generic comparison query.

Expected behavior

  • Returns two rows: Brand and Generic.
  • The average cost per claim for Brand should typically be higher than Generic.

Expected row count

  • 2 rows

7. Query 5: High-Risk Drugs

Run the high-risk drug query.

Expected behavior

  • Returns a filtered list of drugs with large cost changes.
  • The results should be ordered by percentage increase descending.
  • pct_change_22_23 should be greater than 50 for each returned row.

Expected row count

  • Variable, but should usually be a small subset of the full dataset.

8. View Validation

If the view scripts are executed, verify that the views exist and return data:

SHOW FULL TABLES IN medicaid_spending WHERE TABLE_TYPE LIKE 'VIEW';

Expected outcome

  • Three views should appear:
    • yearly_drug_summaries
    • manufacturer_revenue_rankings
    • brand_vs_generic_comparisons

9. Reviewer Checklist

  • Validation script runs successfully.
  • Main table contains approximately 16,938 rows.
  • Key SQL queries return logically ordered results.
  • Brand vs generic analysis returns two categories.
  • Views are created successfully.

10. Notes

These checks are intended to confirm logic and output structure. They are not a replacement for full dataset inspection, but they provide a practical review path for contributors and reviewers.