Describe your approach to handling missing values or data quality issues when joining disparate datasets at scale for commodity data.
For this hands-on SQL assessment, write a query that preserves every source observation, standardizes available commodity and market values, and assigns one data-quality status based on the first applicable issue.
observation_id ascending.observation_id, source_system, standardized_commodity, market_name, observation_date, price, currency, and quality_status.UNKNOWN for unmatched or blank commodity and market values. Statuses must identify missing dates, missing prices, non-positive prices, missing currencies, missing or unmatched commodity codes, missing or unmatched market codes, or VALID.| Column | Type | Description |
|---|---|---|
| observation_idPK | INT | Unique source observation identifier |
| source_system | VARCHAR(30) | System that supplied the observation |
| source_commodity_code | VARCHAR(20) | Commodity code supplied by the source |
| source_market_code | VARCHAR(20) | Market code supplied by the source |
| observation_date | DATE | Date associated with the commodity observation |
| price | NUMERIC(12,2) | Reported commodity price |
| currency | VARCHAR(3) | Reported ISO currency code |
| Column | Type | Description |
|---|---|---|
| source_systemPK | VARCHAR(30) | Source system for the code |
| source_commodity_codePK | VARCHAR(20) | Source-specific commodity code |
| commodity_name | VARCHAR(80) | Standardized commodity name |
| commodity_group | VARCHAR(40) | Standardized commodity group |
| Column | Type | Description |
|---|---|---|
| source_market_codePK | VARCHAR(20) | Canonical market code |
| market_name | VARCHAR(80) | Standardized market name |
| region | VARCHAR(40) | Market geographic region |