Write SQL and Python, run instantly in your browser, and track your progress.
You are a Data Engineer at Amazon. You are building a new ETL (Extract, Transform, Load) pipeline to populate a new Dim_Product dimension table in the company's data warehouse. The source products table has data in various formats, but the dimension table requires a single, standardized format for all product information.
Your task is to write a SQL transformation query that cleans and standardizes the data from the source products table. The query must produce a list with the following standardized fields:
normalized_sku (all uppercase, with hyphens removed).product_name in all uppercase.category in all uppercase.price converted from a decimal value to an integer representing cents.The final output should be sorted by the .
You are a Data Engineer at Amazon. You are building a new ETL (Extract, Transform, Load) pipeline to populate a new Dim_Product dimension table in the company's data warehouse. The source products table has data in various formats, but the dimension table requires a single, standardized format for all product information.
Your task is to write a SQL transformation query that cleans and standardizes the data from the source products table. The query must produce a list with the following standardized fields:
normalized_sku (all uppercase, with hyphens removed).product_name in all uppercase.category in all uppercase.price converted from a decimal value to an integer representing cents.The final output should be sorted by the .
normalized_skunormalized_sku| Column Name | Type |
|---|---|
| product_id | INTEGER |
| sku | TEXT |
| product_name | TEXT |
| category | TEXT |
| price | REAL |
| currency | TEXT |
| Column Name | Type |
|---|---|
| product_id | INTEGER |
| sku | TEXT |
| product_name | TEXT |
| category | TEXT |
| price | REAL |
| currency | TEXT |
| product_id | sku | product_name | category | price | currency | is_active | weight_kg | created_at |
|---|---|---|---|---|---|---|---|---|
| 1 | SKU-10001 | Casper Original Mattress Queen | Home | 1095.0 | USD |
| product_id | sku | product_name | category | price | currency | is_active | weight_kg | created_at |
|---|---|---|---|---|---|---|---|---|
| 1 | SKU-10001 | Casper Original Mattress Queen | Home | 1095.0 | USD |
| product_id | normalized_sku | name_upper | category_upper | price_cents |
|---|---|---|---|---|
| 1 | SKU10001 | CASPER ORIGINAL MATTRESS QUEEN | HOME | 109500 |
| 2 | SKU10002 | DYSON SUPERSONIC HAIR DRYER | BEAUTY | 42999 |
| 3 | SKU10003 | CALDIGIT TS4 THUNDERBOLT DOCK | OFFICE | 39999 |
| product_id | normalized_sku | name_upper | category_upper | price_cents |
|---|---|---|---|---|
| 1 | SKU10001 | CASPER ORIGINAL MATTRESS QUEEN | HOME | 109500 |
| 2 | SKU10002 | DYSON SUPERSONIC HAIR DRYER | BEAUTY | 42999 |
| 3 | SKU10003 | CALDIGIT TS4 THUNDERBOLT DOCK | OFFICE | 39999 |
Showing first 5 of 50 rows. Products with normalized SKUs, uppercased names/categories, and prices in cents.
Showing first 5 of 50 rows. Products with normalized SKUs, uppercased names/categories, and prices in cents.
Your final output should be a result set that meets the following requirements:
1. Transformations:
normalized_sku by taking the sku, removing all hyphens, and converting it to uppercase.name_upper column by converting product_name to uppercase.category_upper column by converting category to uppercase.price_cents column by converting the (a decimal value) into an integer representing the total number of cents (e.g., a price of should become ).Your final output should be a result set that meets the following requirements:
1. Transformations:
normalized_sku by taking the sku, removing all hyphens, and converting it to uppercase.name_upper column by converting product_name to uppercase.category_upper column by converting category to uppercase.price_cents column by converting the (a decimal value) into an integer representing the total number of cents (e.g., a price of should become ).| is_active | INTEGER |
| is_active | INTEGER |
| weight_kg | REAL |
| weight_kg | REAL |
| created_at | TEXT |
| created_at | TEXT |
| 1 |
| 1 |
| 31.75 |
| 31.75 |
| 2025-02-14 10:57:38 |
| 2025-02-14 10:57:38 |
| 2 | SKU-10002 | Dyson Supersonic Hair Dryer | Beauty | 429.99 | USD | 1 | 0.66 | 2025-02-27 07:20:29 |
| 2 | SKU-10002 | Dyson Supersonic Hair Dryer | Beauty | 429.99 | USD | 1 | 0.66 | 2025-02-27 07:20:29 |
| 3 | SKU-10003 | CalDigit TS4 Thunderbolt Dock | Office | 399.99 | USD | 1 | 0.5 | 2024-12-11 00:49:57 |
| 3 | SKU-10003 | CalDigit TS4 Thunderbolt Dock | Office | 399.99 | USD | 1 | 0.5 | 2024-12-11 00:49:57 |
| 4 | SKU-10004 | Under Armour Tech 2.0 T-Shirt | Sports | 25.0 | USD | 1 | 0.15 | 2024-11-22 04:49:00 |
| 4 | SKU-10004 | Under Armour Tech 2.0 T-Shirt | Sports | 25.0 | USD | 1 | 0.15 | 2024-11-22 04:49:00 |
| 5 | SKU-10005 | Staples TRU RED Copy Paper 10-Ream | Office | 54.99 | USD | 1 | 22.68 | 2025-01-10 01:43:59 |
| 5 | SKU-10005 | Staples TRU RED Copy Paper 10-Ream | Office | 54.99 | USD | 1 | 22.68 | 2025-01-10 01:43:59 |
| 4 | SKU10004 | UNDER ARMOUR TECH 2.0 T-SHIRT | SPORTS | 2500 |
| 4 | SKU10004 | UNDER ARMOUR TECH 2.0 T-SHIRT | SPORTS | 2500 |
| 5 | SKU10005 | STAPLES TRU RED COPY PAPER 10-REAM | OFFICE | 5499 |
| 5 | SKU10005 | STAPLES TRU RED COPY PAPER 10-REAM | OFFICE | 5499 |
priceprice115.08115.0811508115082. Columns:
2. Columns:
product_idnormalized_skuname_uppercategory_upperprice_centsproduct_idnormalized_skuname_uppercategory_upperprice_cents3. Ordering:
3. Ordering:
normalized_sku in ascending alphabetical order.product_id in ascending order.normalized_sku in ascending alphabetical order.product_id in ascending order.