# Database Schema: Columns, Data Types, and Meanings Based on the evidence gathered, I can provide a comprehensive overview of the database schema. The data shows **multiple tables** in what appears to be a beer rating system. Here's what the evidence reveals: ## Tables Identified The evidence shows at least **5 tables**: `availability`, `beer_ratings`, `beer_styles`, `beer_upcs`, and `beers`. Note that the evidence shows "50 rows returned, showing a bounded subset," indicating there are additional columns beyond what's displayed. --- ## **beer_ratings** Table This table contains user ratings of beers with detailed scoring metrics. | Column | Data Type | Meaning | |--------|-----------|---------| | `rating_id` | BIGINT/Int64 | Unique identifier for each rating (~14.9M distinct values, range 0-14344127) | | `user_id` | BIGINT/Int64 | Identifier for the user who submitted the rating (~198K distinct users) | | `beer_id` | BIGINT/Int64 | Identifier for the beer being rated (~1M distinct beers, 1,434 nulls) | | `aroma` | int64 | Rating score for aroma (0-10 scale, 12 distinct values) | | `flavor` | int64 | Rating score for flavor (0-10 scale, 12 distinct values) | | `mouthfeel` | int64 | Rating score for mouthfeel/texture (0-5 scale, 6 distinct values) | | `appearance` | int64 | Rating score for visual appearance (0-5 scale, 6 distinct values) | | `overall` | int64 | Overall rating score (0-20 scale, 22 distinct values) | | `total_score` | DOUBLE/float64 | Aggregate rating score (0.1-5.0 range, 1,434 nulls) | | `comments` | VARCHAR/object | Text comments from the reviewer (~11.4M distinct comments, 12,910 nulls) | | `created_at` | TIMESTAMP/datetime64 | When the rating was created (2000-04-12 to 2025-02-03) | | `updated_at` | TIMESTAMP/datetime64 | When the rating was last updated (2003-01-05 to 2025-02-03, 9.99M nulls) | | `description_score` | DOUBLE/float64 | Score related to description quality (-10495.0 to 500.0, 5.1M nulls) | | `language` | VARCHAR/object | Language of the rating (76 distinct languages, 2.96M nulls) | | `availability_id` | BIGINT/Int64 | Link to availability information (~106K distinct, 14.24M nulls) | **Metadata columns** (ingestion tracking): - `_ingestion_timestamp`: When data was ingested (2026-08-11) - `_batch_id`: Batch identifier (batch_9eb017c28acb) - `_source_file`: Source file path - `_source_system`: Source system (api) --- ## **availability** Table Tracks where and how beers are available. | Column | Data Type | Meaning | |--------|-----------|---------| | `avail_id` | Int64 | Unique identifier for availability record | | `beer_id` | Int64 | Identifier for the beer | | `user_id` | Int64 | User who reported availability | | `place_id` | Int64 | Location/venue identifier | | `country_id` | Int64 | Country identifier | | `state_id` | float64 | State/region identifier | | `postal_code` | object (text) | Postal/ZIP code | | `is_in_bottle` | bool | Whether available in bottles | | `is_on_tap` | bool | Whether available on tap | | `is_out` | bool | Whether currently out of stock | | `created_at` | datetime64 | When availability was recorded | --- ## **beer_styles** Table Defines beer style categories and characteristics. | Column | Data Type | Meaning | |--------|-----------|---------| | `style_id` | Int64 | Unique identifier for beer style | | `name` | object (text) | Name of the beer style | | `description` | object (text) | Description of the style | | `category` | int64 | Category classification | | `parent_style_id` | int64 | Parent style for hierarchical relationships | | `srm_range` | object (text) | Standard Reference Method color range | --- ## **beer_upcs** Table Links beers to their UPC barcodes. | Column | Data Type | Meaning | |--------|-----------|---------| | `beer_id` | Int64 | Identifier for the beer | | `upc` | object (text) | Universal Product Code | | `user_id` | float64 | User who submitted the UPC | --- ## **beers** Table (Partial) Master table for beer information. Evidence shows at least these columns: | Column | Data Type | Meaning | |--------|-----------|---------| | `beer_id` | Int64 | Unique identifier for each beer | | `brewer_id` | Int64 | Identifier for the brewery | | `alcohol_pct` | float64 | Alcohol by volume percentage | | `avg_rating` | float64 | Average rating score | | `ibu` | float64 | International Bitterness Units | | `description` | object (text) | Beer description | | `has_pic` | bool | Whether beer has a picture | | `is_alias` | bool | Whether this is an alias entry | | `is_one_off` | bool | Whether this is a one-time brew | | `is_retired` | bool | Whether beer is no longer produced | | `is_seasonal` | bool | Whether beer is seasonal | | `Unrateable` | float64 | Flag/score for unrateable status | | `admin_note` | object | Administrative notes | | `created_at` | datetime64 | When beer was added to database | | `created_by` | float64 | User who created the entry | --- ## Data Quality Notes - **beer_ratings** has ~14.9M records with comprehensive rating data - Significant nulls exist in `updated_at` (9.99M), `availability_id` (14.24M), and `description_score` (5.1M) - Date ranges span from 2000 to 2025, indicating 25+ years of data - The evidence shows "50 rows returned" suggesting **additional columns exist** in the beers table and potentially other tables not fully captured