# Data Quality Issues Analysis Based on the evidence gathered, I can identify several concrete data-quality problems in this beer ratings dataset: ## 1. **Impossible/Extreme Values** ### Description Score Anomalies (beer_ratings table) - **244,970 records** (1.7% of 14.3M ratings) have suspicious `description_score` values - **Range**: -10495.0 to 500.0 (median: 1.0) - **Issue**: Negative scores down to -10,495 are logically impossible for a rating/quality metric - **Impact**: Affects ~245K of 14.3M total ratings ### IBU (International Bitterness Units) Outlier (beers table) - **Maximum IBU**: 2,600.0 - **Issue**: Typical beer IBU ranges from 5-120; even extreme IPAs rarely exceed 150 IBU. A value of 2,600 is physically implausible - **Context**: Only 467,560 of 1.19M beers (39%) have IBU data populated ### Alcohol Percentage Range (beers table) - **Range**: 0.0% to 57.8% ABV - **Issue**: While 0% (non-alcoholic) and high-proof beers exist, 57.8% is exceptionally rare and warrants validation - **Data available**: 1,179,727 of 1.19M beers (99%) have alcohol data ## 2. **Critical Missing Data (Completeness Issues)** ### High-Impact Nulls - **`beer_ratings.availability_id`**: 14,236,547 nulls out of 14,344,128 records (**99.3% missing**) - Eliminates ability to track where beers were consumed/purchased - **`places.currency`**: **100% null** (confirmed in initial quality report) - Prevents international pricing analysis - **`beer_ratings.updated_at`**: 9,992,120 nulls (**69.7% missing**) - **`beer_ratings.description_score`**: 5,102,206 nulls (**35.6% missing**) - **`beer_ratings.language`**: 2,964,931 nulls (**20.7% missing**) ### Moderate Missing Data - **`beers.ibu`**: 724,054 nulls (60.8% of beers lack bitterness data) - **`beers.description`**: 332,474 nulls (27.9%) - **`beers.avg_rating`**: 76,743 nulls (6.4%) - **`users` beer rating fields**: 20,972-20,995 nulls (~8.9% of 237K users) ## 3. **Inconsistent Formats** ### Mixed Encoding in Text Fields - **`places.postal_code`**: Contains values ranging from "〒700-0827" (HTML entities) to "wijnbier@planet.nl" (email address) - Indicates data contamination and format inconsistency - **`places.phone`**: Range shows "020 8579 8144" to "Örebro" (a Swedish city name) - Phone field contains non-phone data ### Temporal Data Type Inconsistencies - **`users.beer_first_rating`** and **`users.beer_last_rating`**: Stored as VARCHAR instead of TIMESTAMP - Prevents proper temporal queries and comparisons - Confirmed by query failure when attempting timestamp comparison ## 4. **Data Contamination** ### Field Misuse - **`places.postal_code`**: Contains email addresses (e.g., "wijnbier@planet.nl") - **`places.phone`**: Contains city names (e.g., "Örebro") - Both indicate data entry errors or field mapping problems ## 5. **What Remains Uncertain** Due to step limits, I could **not** verify: - Whether future dates exist in timestamp fields (query failed on execution) - Brewery location data quality (latitude/longitude don't exist in `brewers` table) - Cross-table referential integrity beyond the 97% score mentioned - Unit consistency across rating scales (though individual rating components appear valid: aroma 0-10, flavor 0-10, mouthfeel 0-5, appearance 0-5, overall 0-20) ## Summary The dataset has **no invalid values within expected ranges** for core rating fields (aroma, flavor, mouthfeel, appearance, overall), but suffers from: - **Extreme outliers**: description_score (-10,495), IBU (2,600) - **Critical missingness**: 99% of availability_id, 100% of currency - **Format inconsistency**: VARCHAR timestamps, HTML entities in postal codes - **Data contamination**: Emails in postal_code, city names in phone fields