# Data Quality Issues Found Based on the evidence gathered, I've identified several significant data quality issues: ## 1. **Referential Integrity Problems** ### Missing Product Reference - **1,480 deals** in `sales_pipeline` reference product **"GTXPro"** which does not exist in the `products` table - The `products` table only contains 7 products: GTK 500, GTX Plus Pro, GTX Plus Basic, GTX Basic, MG Advanced, MG Special (step-9, step-10) - This represents a critical data integrity issue affecting deal value calculations ### Missing Account References - **1,425 deals** in `sales_pipeline` reference accounts that don't exist in the `accounts` table (step-13) - This is ~17% of the 8,283 total opportunities ## 2. **Null Value Patterns** From step-0 and step-4, nulls follow logical business rules but are extensive: | Field | Null Count | Pattern | |-------|-----------|---------| | `engage_date` | 500 | All in "Prospecting" stage (500 deals) | | `close_date` | 2,089 | All in "Engaging" (1,589) + "Prospecting" (500) stages | | `close_value` | 2,089 | Same pattern as close_date | **Assessment**: This appears **consistent** with business logic (deals not yet closed have no close data), but represents 25% of all deals. ## 3. **Extreme Outliers** ### Accounts Table (step-14) Two accounts show extreme values: **Kan-code** (high outliers): - Revenue: **$11,698.03M** (max is 2.6x the 99th percentile of $8,734.80) - Employees: **34,288** (max is 1.5x the 99th percentile of 22,532) **Condax** (low outliers): - Revenue: **$4.54M** (min is 7.7x below 1st percentile of $34.99) - Employees: **9** (min is 9.1x below 1st percentile of 82) - Year established: **2017** (suspiciously recent) ## 4. **Close Value Anomalies** From step-5: - **2,473 deals** have `close_value = 0` (all "Lost" deals per step-4) - Close values range from **$0 to $30,288** - Standard deviation ($2,320.67) is 1.56x the mean ($1,490.92), indicating high variability ### Product Pricing Consistency (step-11) Close values generally align with product `sales_price`, but show variation: - GTK 500: sales_price $26,768, actual close values $23,746-$30,288 (avg $26,707) - Variation suggests discounting or bundling, which is normal ## 5. **No Impossible Values Detected** ✓ No negative revenues or employees (step-6) ✓ No negative close values (step-5) ✓ No impossible date ordering (close_date before engage_date): 0 cases (step-4) ✓ Year established range 1979-2017 is plausible (step-1) ## 6. **Uncertain: Format Consistency** I attempted to check text field formatting (step-15) but the query failed. **Cannot confirm** whether there are: - Mixed case inconsistencies in categorical fields - Leading/trailing whitespace issues - The `account` field shows range starting with ".." suggesting possible empty string or formatting issue (step-0) ## Summary **Critical issues**: Missing product "GTXPro" (1,480 deals), missing account references (1,425 deals) **Moderate issues**: Extreme outliers in 2 accounts, high null rates (25% of deals) **Minor issues**: Zero values in lost deals (expected) **Unverified**: Text formatting consistency